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

python io pandas xlsx xlsxwriter
Nikita picture Nikita · Jun 5, 2014 · Viewed 15.5k times · Source

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

jmcnamara picture jmcnamara · Jun 5, 2014

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.

Related questions

Load data from txt with pandas

I am loading a txt file containig a mix of float and string data. I want to store them in an array where I can access each element. Now I am just doing import pandas as pd data = pd.read_…

Read More
Read a small random sample from a big CSV file into a Python data frame

The CSV file that I want to read does not fit into main memory. How can I read a few (~10K) random lines of it and do some simple statistics on the selected data frame?

Read More
Iteratively writing to HDF5 Stores in Pandas

Pandas has the following examples for how to store Series, DataFrames and Panelsin HDF5 files: Prepare some data: In [1142]: store = HDFStore('store.h5') In [1143]: index = date_range('1/1/2000', periods=8) In [1144]: s = Series(randn(5), index=['a', 'b', 'c', 'd', …

Read More