CODEWITHSUNDEEP
pythonpandasxlsxwriterpython-3.7
Asyraf Dayan
Asyraf Dayan

Reputation: 3071

Xlsxwriter header format not appearing when executing with Linux

My current project involves writing a script that makes an Excel file using Pandas and Xlsxwriter and sends this file through an email API on a schedule with cron. When generating the excel file on Windows during development, the format I initially set for the header works just fine.

However, when running the script through Linux during testing, the Header formatting disappears while the rest of the cells maintain their intended format.

I have included the code below to remove the initial header to use my own custom one

pandas.io.formats.excel_header = None

The custom header format (Which works on Windows):

header_format = workbook.add_format({
       'font_name': 'ARIAL',
       'font_size': 10,
       'bold': True,
       'align': 'center',
       'border': 1,
       'fg_color': '#00D2EB'
   })

And this is code that I use to format the Header

worksheet.set_row(row, None, header_format)

From what I've tried on windows, when I comment out the line to remove the header format and then apply my own, it doesn't use my customised formats. On linux, even with the code to remove the header format first and then my customised format, it executes without my custom format. It doesn't return any error either.

Header Result:

This is what I get in Windows Expected

This is what I get in Linux What I Got

Should the code be written differently when executing on Linux and Windows? If yes, what is the difference or where can I find material to find this difference?

Upvotes: 1

Views: 696

Answers (1)

jmcnamara
jmcnamara

Reputation: 41644

However, when running the script through Linux during testing, the Header formatting disappears while the rest of the cells maintain their intended format.

The difference between Windows and Linux is probably in the Pandas version. The pandas.io.formats.excel_header changed in one of the Pandas versions.

I would recommend avoiding unsetting the header format like this, since it isn't really a documented feature, and as you have seen it isn't consistent in Pandas versions.

Instead, I recommend turning off the header and explicitly setting the header format like this example from the XlsxWriter docs:

# Turn off the default header and skip one row to allow us to insert a
# user defined header.
df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False)

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Add a header format.
header_format = workbook.add_format({
    'bold': True,
    'text_wrap': True,
    'valign': 'top',
    'fg_color': '#D7E4BC',
    'border': 1})

# Write the column headers with the defined format.
for col_num, value in enumerate(df.columns.values):
    worksheet.write(0, col_num + 1, value, header_format)

Upvotes: 2

Related Questions