XLSX Writer Format Row Borders for First n Columns

Question

I want to add bottom borders to the first 4 rows of a workbook, but only up to the 7th column. How can I restrict the range of columns to which this format will apply? Here's what I tried so far:

import xlsxwriter
import numpy

format = workbook.add_format()
format.set_bottom(7)

for r in np.arange(4):
    worksheet.set_row(r,15,format)

This works great for formatting all columns for those rows, but I need to either delete all columns after the 7th column or find a way to restrict the range of columns to which the row format is applied.

Thanks in advance!

Answer 1

Currently, you cannot format a specified range of cells, but, as a workaround, you can apply conditional formatting to a range based on a condition - you just need to set the condition to be always evaluated to true:

format = workbook.add_format()
format.set_bottom(7)

worksheet.conditional_format('A1:G4', {'type': 'cell',
                                       'criteria': '!=',
                                       'value': 'None',
                                       'format': format})

The A1:G4 should cover the "first 4 rows, first 7 columns only" range.

---

The complete working code I've used for testing the solution:

import xlsxwriter

workbook = xlsxwriter.Workbook('hello.xlsx')
worksheet = workbook.add_worksheet()

for row in range(11):
    for col in range(11):
        worksheet.write(row, col, row + col)

format = workbook.add_format()
format.set_bottom(7)

worksheet.conditional_format('A1:D5',
                             {'type': 'cell',
                              'criteria': '!=',
                              'value': 'None',
                              'format': format})

workbook.close()

Here is what I get in hello.xlsx: