Excel Styler for pandas

Styling individual cells in Excel output files created with Python’s Data Analysis Library pandas.

DEPRECATION NOTICE: A new feature for styling Excel output was introduced with pandas v0.20, so this extension here is only needed in case you’re using an older version of pandas or need to export to .xls files instead of .xlsx.

Description

This small addition to pandas allows to specify styles for individual cells in Excel exports from pandas DataFrames,
which is not possible with the existing API methods. With this you can for example highlight certain cells by
specifying font styles, font colors or background patterns and colors.

Of course it is also possible to add things like conditional formatting and other advanced functions to Excel files
with XlsxWriter (see also
Improving Pandas’ Excel Output). However, sometimes it is
necessary set styles like font or background colors on individual cells on the “Python side”. In this scenario,
XlsxWriter won’t work, since
“XlsxWriter and Pandas provide very little support for formatting the output data from a dataframe apart from default formatting such as the header and index cells and any cells that contain dates of datetimes.”
In this case, you’re better off with this tool, for example when you are running complicated data validation
routines (which you probably don’t want to implement in VBA) and want to highlight the validation results by coloring
individual cells in the output Excel sheets.

Example

import numpy as np
import pandas as pd

from pandas_excel_styler import DataFrameExcelStyler

# create a data frame and work with it
df = ...

# wrap it as DataFrameExcelStyler
# this will not copy the data from the original data frame (unless you set copy=True)!
df = DataFrameExcelStyler(df)

# define a style

red_font_style = {"font": {"color": "red"}}

# create a cell_styles matrix
# it must have the same number of rows and columns as your DataFrame
cell_styles = np.empty(df.shape, dtype='object')
cell_styles.fill(None)  # filling it with None means that no styling is applied to any cell

# set a style to the top left cell
cell_styles[0, 0] = red_font_style

# write output, use the specified cell style matrix
df.to_excel('test.xls', cell_styles=cell_styles)

See examples.py for more examples and usage cases.

Styling the cells

The styling definitions of individiual cells can be done by creating a nested dictionary of style options for
xlwt. The dictionary will be converted to “easyxf” directives by pandas so
that {"font": {"color": "red"}} will become "font: color red;". See the
Python Excel Tutorial, page 28 for available
options.

Colors in Excel

For an overview of possible colors, see the
Python Excel Tutorial, page 33. But be aware
that “Colours in Excel files are a confusing mess” (ibid.).

Requirements

  • tested with pandas 0.24
    • only works with “xls” files via xlwt engine (“xlsx” support via OpenPyXL is not yet available)
  • NumPy is required

GitHub

View Github