xlsxwriter does not apply format to the header row in xlsx file instead applies formatting to empty cells

Question

I have a xlsx file, for all of its sheet I need to change the formatting of the header row to apply background color to it.

But when I am adding fomratting to the row, instead of the columns which contain my data, it gets applied to all other empty columns.

Here is what I have tried :

for sheet_name in xlwriter.sheets:
    ws = xlwriter.sheets[sheet_name]
    ws.freeze_panes(1, 0) # Freeze the first row.
    
    cell_format = workbook.add_format({'bg_color': 'yellow'})
    cell_format.set_bold()
    cell_format.set_font_color('red')
    cell_format.set_border(1)
    ws.set_row(0, cell_format = cell_format)

P.S : I have tried solution from other question which I was getting as suggestion for this question but none of that works for me.

There is a section in the XlsxWriter docs that explains how to do this: https://xlsxwriter.readthedocs.io/working_with_pandas.html#formatting-of-the-dataframe-headers — jmcnamara, Jul 02 '22 at 18:55

score 0 · Answer 1 · answered Jul 02 '22 at 15:46

I think it is better to loop through the column names and write each cell separately, instead of set_row:

for col, val in enumerate(df.columns):
    ws.write(0, col, val, cell_format)

There is another option using styler, but I think there's a bug with the border.

xlsxwriter does not apply format to the header row in xlsx file instead applies formatting to empty cells

1 Answers1