import numpy as np, pandas as pd
from pandas import compat
import pandas.core.format as fmt
def to_excel_with_style(self, excel_writer, style_df, sheet_name='Sheet1', na_rep='',
float_format=None, columns=None, header=True, index=True,
index_label=None, startrow=0, startcol=0, engine='openpyxl2',
merge_cells=True, encoding=None, inf_rep='inf'):
"""
Write DataFrame to a excel sheet with specified cell styles
Parameters
----------
excel_writer : string or ExcelWriter object
File path or existing ExcelWriter
style_df : DataFrame of style dicts
style parameters to apply to each cell
sheet_name : string, default 'Sheet1'
Name of sheet which will contain DataFrame
na_rep : string, default ''
Missing data representation
float_format : string, default None
Format string for floating point numbers
columns : sequence, optional
Columns to write
header : boolean or list of string, default True
Write out column names. If a list of string is given it is
assumed to be aliases for the column names
index : boolean, default True
Write row names (index)
index_label : string or sequence, default None
Column label for index column(s) if desired. If None is given, and
`header` and `index` are True, then the index names are used. A
sequence should be given if the DataFrame uses MultiIndex.
startrow :
upper left cell row to dump data frame
startcol :
upper left cell column to dump data frame
engine : string, default ``openpyxl2``
write engine to use - currently must be ``openpyxl2``.
merge_cells : boolean, default True
Write MultiIndex and Hierarchical Rows as merged cells.
encoding: string, default None
encoding of the resulting excel file. Only necessary for xlwt,
other writers support unicode natively.
cols : kwarg only alias of columns [deprecated]
inf_rep : string, default 'inf'
Representation for infinity (there is no native representation for
infinity in Excel)
Notes
-----
The not-yet-documented style dict supports things like this:
>>> df = pd.DataFrame({'A':[10,20,30], 'B':[40,50,60]})
>>> red_text = {'font': {'color': '00FF0000'}}
>>> dashed_blue = {'style': 'dashed', 'color': '000000FF'}
>>> dashed_blue = {'border': {s: dashed_blue for s in ['left', 'right', 'top', 'bottom']}}
>>> style_df = pd.DataFrame(index=df.index, columns=df.columns, dtype=object)
>>> style_df.iloc[:,0] = red_text
>>> style_df.iloc[1,:] = dashed_blue
>>> to_excel_with_style(df, 't.xlsx', style_df)
"""
from pandas.io.excel import ExcelWriter
need_save = False
if encoding == None:
encoding = 'ascii'
if isinstance(excel_writer, compat.string_types):
excel_writer = ExcelWriter(excel_writer, engine=engine)
need_save = True
formatter = fmt.ExcelFormatter(self,
na_rep=na_rep,
cols=columns,
header=header,
float_format=float_format,
index=index,
index_label=index_label,
merge_cells=merge_cells,
inf_rep=inf_rep)
formatted_cells = formatter.get_formatted_cells()
assert engine == 'openpyxl2', 'Only openpyxl2 engine is supported'
assert np.allclose(self.shape, style_df.shape)
style_df = style_df.fillna('')
# merge style information into cells
def styled_formatted_cells(formatted_cells):
for cell in formatted_cells:
# cell.row, cell.col may not correspond to style_df row and columns
# because of header and index (FIXME: many cases to consider)
new_style = {}
if cell.row > 0 and cell.col > 0:
new_style = dict(style_df.iloc[cell.row-1, cell.col-1])
# merge new style on top of any existing style
if cell.style == None:
cell.style = {}
cell.style.update(new_style)
yield cell
excel_writer.write_cells(styled_formatted_cells(formatted_cells), sheet_name,
startrow=startrow, startcol=startcol)
if need_save:
excel_writer.save()