13

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?

emesday
  • 6,078
  • 3
  • 29
  • 46
Nikita
  • 429
  • 2
  • 6
  • 14
  • I also tried to change the format afterwords however this seems to be not possible right now: http://stackoverflow.com/questions/22352907/apply-format-to-a-cell-after-being-written-in-xlsxwriter/22353696#22353696 – Nikita Jun 05 '14 at 16:34

4 Answers4

12

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.

jmcnamara
  • 38,196
  • 6
  • 90
  • 108
  • 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](http://stackoverflow.com/questions/41447606/xlsxwriter-not-applying-format-to-header-row-of-dataframe-python-pandas)? – MaxU - stand with Ukraine Jan 03 '17 at 18:02
9

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

Tim Hoffmann
  • 1,325
  • 9
  • 28
  • Tim, this answer was helpful. I tried to call you out in [my related question](http://stackoverflow.com/questions/35397783/applying-styles-to-the-headers-of-pandas-generated-excel-files) it but couldn't figure out how to do that. – HaPsantran Feb 14 '16 at 20:52
  • 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? – MaxU - stand with Ukraine Jan 03 '17 at 17:20
5

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)
chrisp
  • 51
  • 1
  • 6
  • 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. – chrisp May 12 '16 at 00:18
1

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

joeln
  • 3,563
  • 25
  • 31