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?

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.

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