Apply styles while exporting to 'xlsx' in pandas with XlsxWriter

Question

I use the .to_excel method of pandas to write a DataFrame as an Excel workbook. This works nice even for multi-index DataFrames as index cells become merged. When using the pure XlsxWriter I can apply formats to cells what also works nice.

However I couldn't find a way to do the same with the pandas method. Just passing a dict with column names and styles would be most intuitive.

Is there any way to do so?

Artilleryman

I also tried to change the format afterwords however this seems to be not possible right now: #22353407 – Artilleryman

Answer 1

Is there any way to do so

Currently no. There isn't a formatting mechanism like that in Pandas for formatting the Excel output (apart from a few hard-coded formats).

However, even if it was XlsxWriter doesn't currently support formatting cells after data is added. It is on TODO list.

Update:

As a workaround I recommend getting a reference to the underlying workbook and worksheet and overwriting any cells that you wish to be formatted with the same data from the Pandas dataframe and a XlsxWriter format.

See Working with Python Pandas and XlsxWriter.

Rhoden

Thank you for the documentation! Could you please add an example of how can one set font color for the header_style in Pandas or answer this question? – Epagoge

Answer 2

If you just want to style the header, you can modify pandas.io.formats.excel.header_style. Of course, this is no general solution, but is an easy workaround for a common use-case.

import pandas.core.format
header_style_backup = pandas.io.formats.excel.header_style
try:
    pandas.io.formats.excel.header_style = {"font": {"bold": True},
                                       "borders": {"top": "thin", "right": "thin", "bottom": "thin", "left": "thin"},
                                       "pattern": {"pattern": "solid", "fore_colour": 26},
                                       "alignment": {"horizontal": "center", "vertical": "top"}}
    df.to_excel(writer, sheet_name=sheetname, startrow=table_startrow)
finally:
    pandas.formats.format.header_style = header_style_backup

Note: The location of header_style has been changing multiple times in prior pandas versions. Use the following for older versions:

version < 0.20.0 pandas.formats.format.header_style

version < 0.18.0 pandas.core.format.header_style

Ensile

Tim, this answer was helpful. I tried to call you out in my related question it but couldn't figure out how to do that. – Hagiarchy

I guess starting from Pandas 0.18.1 the header_style has been moved to pandas.formats.format.header_style - could you please update your answer accordingly? – Epagoge

Answer 3

The following approach allows me to use xlsxwriter formatting on the dataframe index and column names (though I can't guarantee it's validity):

import pandas as pd
import xlsxwriter as xl

# remove pandas header styles
# this avoids the restriction that xlsxwriter cannot
# format cells where formatting was already applied
pd.core.format.header_style = None

# write dataframe to worksheet
writer = pd.ExcelWriter(sumfile, engine='xlsxwriter')
df.to_excel(writer, sheet_name='test')

# create desired xlsxwriter formats
workbook  = writer.book
worksheet = writer.sheets['test']
header = workbook.add_format({'bold': True})
index = workbook.add_format({'align': 'left'})

# apply formats to header and index
worksheet.set_row(0, None, header)
worksheet.set_column(0,0, None, index)

Lynelllynelle

It looks like things moved around a bit in recent pandas builds. You can now use "pd.formats.format.header_style = None" to clear the default header styles. – Lynelllynelle

Answer 4

The next version of Pandas (2.0) will include experimental support for exporting styled DataFrames direct to Excel using openpyxl: http://pandas-docs.github.io/pandas-docs-travis/style.html#Export-to-Excel

Engaged