def to_excel(self,
excel_writer,
sheet_name='Sheet1',
na_rep='',
float_format=None,
columns=None,
header=True,
index=True,
index_label=None,
startrow=0,
startcol=0,
engine=None,
merge_cells=True,
encoding=None,
inf_rep='inf',
verbose=True,
freeze_panes=None):
from pandas.io.formats.excel import ExcelFormatter
formatter = 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)
formatter.write(excel_writer,
sheet_name=sheet_name,
startrow=startrow,
startcol=startcol,
freeze_panes=freeze_panes,
engine=engine)
def to_excel(self, excel_writer, sheet_name='Sheet1', na_rep='',
float_format=None, columns=None, header=True, index=True,
index_label=None, startrow=0, startcol=0, engine=None,
merge_cells=True, encoding=None, inf_rep='inf', verbose=True,
freeze_panes=None):
from pandas.io.formats.excel import ExcelFormatter
formatter = 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)
formatter.write(excel_writer, sheet_name=sheet_name, startrow=startrow,
startcol=startcol, freeze_panes=freeze_panes,
engine=engine)
def test_styler_custom_converter():
openpyxl = pytest.importorskip("openpyxl")
def custom_converter(css):
return {"font": {"color": {"rgb": "111222"}}}
df = DataFrame(np.random.randn(1, 1))
styler = df.style.applymap(lambda x: "color: #888999")
with tm.ensure_clean(".xlsx") as path:
with ExcelWriter(path, engine="openpyxl") as writer:
ExcelFormatter(styler, style_converter=custom_converter).write(
writer, sheet_name="custom")
with contextlib.closing(openpyxl.load_workbook(path)) as wb:
assert wb["custom"].cell(2, 2).font.color.value == "00111222"
def test_styler_to_excel(engine):
def style(df):
# XXX: RGB colors not supported in xlwt
return DataFrame(
[['font-weight: bold', '', ''], ['', 'color: blue', ''],
['', '', 'text-decoration: underline'],
['border-style: solid', '', ''], ['', 'font-style: italic', ''],
['', '', 'text-align: right'], ['background-color: red', '', ''],
['number-format: 0%', '', ''], ['', '', ''], ['', '', ''],
['', '', '']],
index=df.index,
columns=df.columns)
def assert_equal_style(cell1, cell2, engine):
if engine in ['xlsxwriter', 'openpyxl']:
pytest.xfail(reason=("GH25351: failing on some attribute "
"comparisons in {}".format(engine)))
# XXX: should find a better way to check equality
assert cell1.alignment.__dict__ == cell2.alignment.__dict__
assert cell1.border.__dict__ == cell2.border.__dict__
assert cell1.fill.__dict__ == cell2.fill.__dict__
assert cell1.font.__dict__ == cell2.font.__dict__
assert cell1.number_format == cell2.number_format
assert cell1.protection.__dict__ == cell2.protection.__dict__
def custom_converter(css):
# use bold iff there is custom style attached to the cell
if css.strip(' \n;'):
return {'font': {'bold': True}}
return {}
pytest.importorskip('jinja2')
pytest.importorskip(engine)
# Prepare spreadsheets
df = DataFrame(np.random.randn(11, 3))
with ensure_clean('.xlsx' if engine != 'xlwt' else '.xls') as path:
writer = ExcelWriter(path, engine=engine)
df.to_excel(writer, sheet_name='frame')
df.style.to_excel(writer, sheet_name='unstyled')
styled = df.style.apply(style, axis=None)
styled.to_excel(writer, sheet_name='styled')
ExcelFormatter(styled, style_converter=custom_converter).write(
writer, sheet_name='custom')
writer.save()
if engine not in ('openpyxl', 'xlsxwriter'):
# For other engines, we only smoke test
return
openpyxl = pytest.importorskip('openpyxl')
wb = openpyxl.load_workbook(path)
# (1) compare DataFrame.to_excel and Styler.to_excel when unstyled
n_cells = 0
for col1, col2 in zip(wb['frame'].columns, wb['unstyled'].columns):
assert len(col1) == len(col2)
for cell1, cell2 in zip(col1, col2):
assert cell1.value == cell2.value
assert_equal_style(cell1, cell2, engine)
n_cells += 1
# ensure iteration actually happened:
assert n_cells == (11 + 1) * (3 + 1)
# (2) check styling with default converter
# XXX: openpyxl (as at 2.4) prefixes colors with 00, xlsxwriter with FF
alpha = '00' if engine == 'openpyxl' else 'FF'
n_cells = 0
for col1, col2 in zip(wb['frame'].columns, wb['styled'].columns):
assert len(col1) == len(col2)
for cell1, cell2 in zip(col1, col2):
ref = '%s%d' % (cell2.column, cell2.row)
# XXX: this isn't as strong a test as ideal; we should
# confirm that differences are exclusive
if ref == 'B2':
assert not cell1.font.bold
assert cell2.font.bold
elif ref == 'C3':
assert cell1.font.color.rgb != cell2.font.color.rgb
assert cell2.font.color.rgb == alpha + '0000FF'
elif ref == 'D4':
# This fails with engine=xlsxwriter due to
# https://bitbucket.org/openpyxl/openpyxl/issues/800
if engine == 'xlsxwriter' \
and (LooseVersion(openpyxl.__version__) <
LooseVersion('2.4.6')):
pass
else:
assert cell1.font.underline != cell2.font.underline
assert cell2.font.underline == 'single'
elif ref == 'B5':
assert not cell1.border.left.style
assert (cell2.border.top.style == cell2.border.right.style
== cell2.border.bottom.style ==
cell2.border.left.style == 'medium')
elif ref == 'C6':
assert not cell1.font.italic
assert cell2.font.italic
elif ref == 'D7':
assert (cell1.alignment.horizontal !=
cell2.alignment.horizontal)
assert cell2.alignment.horizontal == 'right'
elif ref == 'B8':
assert cell1.fill.fgColor.rgb != cell2.fill.fgColor.rgb
assert cell1.fill.patternType != cell2.fill.patternType
assert cell2.fill.fgColor.rgb == alpha + 'FF0000'
assert cell2.fill.patternType == 'solid'
elif ref == 'B9':
assert cell1.number_format == 'General'
assert cell2.number_format == '0%'
else:
assert_equal_style(cell1, cell2, engine)
assert cell1.value == cell2.value
n_cells += 1
assert n_cells == (11 + 1) * (3 + 1)
# (3) check styling with custom converter
n_cells = 0
for col1, col2 in zip(wb['frame'].columns, wb['custom'].columns):
assert len(col1) == len(col2)
for cell1, cell2 in zip(col1, col2):
ref = '%s%d' % (cell2.column, cell2.row)
if ref in ('B2', 'C3', 'D4', 'B5', 'C6', 'D7', 'B8', 'B9'):
assert not cell1.font.bold
assert cell2.font.bold
else:
assert_equal_style(cell1, cell2, engine)
assert cell1.value == cell2.value
n_cells += 1
assert n_cells == (11 + 1) * (3 + 1)
def get_xl_ranges(frame_index,
frame_columns,
sheet_name='Sheet1',
columns=None,
header=True,
index=True,
index_label=None,
startrow=0,
startcol=0,
merge_cells=True):
"""
Deduces location of data_range, index_range and col_range within excel spreadsheet, given the parameters provided.
Does not require an actual DataFrame, which could be useful!
Parameters
----------
frame_index: Pandas Index or Array-like
to determine location of index within spreadsheet.
frame_columns: Pandas Index or Array-like
used to determine location of column within spreadsheet.
excel_writer : string or ExcelWriter
sheet_name : str
default ‘Sheet1’, Name of sheet which will contain DataFrame
columns : sequence
optional, Columns to write
header : bool or list of strings,
default True Write out the column names. If a list of strings is given it is assumed to be aliases for the column names
index : bool
default True. Write row names (index)
index_label : str 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 : int
upper left cell row to dump data frame
startcol : int
upper left cell column to dump data frame
merge_cells : bool
default True. Write MultiIndex and Hierarchical Rows as merged cells.
Returns
-------
data_range, index_range, col_range : XLRange
Each range represents where the data, index and columns can be found on the spreadsheet
empty_f : DatFrame
an empty DataFrame with matching Indices.
"""
empty_f = pd.DataFrame(index=frame_index, columns=frame_columns)
formatter = ExcelFormatter(empty_f,
cols=columns,
header=header,
index=index,
index_label=index_label,
merge_cells=merge_cells)
excel_header = list(formatter._format_header())
col_start, col_stop = excel_header[0], excel_header[-1]
col_start_cell = XLCell(col_stop.row + startrow, col_start.col + startcol,
sheet_name)
col_stop_cell = XLCell(col_stop.row + startrow, col_stop.col + startcol,
sheet_name)
if isinstance(empty_f.index, pd.MultiIndex):
col_start_cell = col_start_cell.translate(0, 1)
col_range = col_start_cell - col_stop_cell
body = list(formatter._format_body())
if empty_f.index.name or index_label:
body.pop(0) # gets rid of index label cell that comes first!
index_start_cell = XLCell(
body[0].row + startrow,
body[0].col + startcol + empty_f.index.nlevels - 1, sheet_name)
index_stop_cell = XLCell(
body[-1].row + startrow,
body[0].col + startcol + empty_f.index.nlevels - 1, sheet_name)
index_range = index_start_cell - index_stop_cell
data_start_cell = XLCell(index_start_cell.row, col_start_cell.col,
sheet_name)
data_stop_cell = XLCell(index_stop_cell.row, col_stop_cell.col, sheet_name)
data_range = data_start_cell - data_stop_cell
return data_range, index_range, col_range, empty_f
def test_styler_to_excel(engine):
def style(df):
# TODO: RGB colors not supported in xlwt
return DataFrame(
[
["font-weight: bold", "", ""],
["", "color: blue", ""],
["", "", "text-decoration: underline"],
["border-style: solid", "", ""],
["", "font-style: italic", ""],
["", "", "text-align: right"],
["background-color: red", "", ""],
["number-format: 0%", "", ""],
["", "", ""],
["", "", ""],
["", "", ""],
],
index=df.index,
columns=df.columns,
)
def assert_equal_style(cell1, cell2, engine):
if engine in ["xlsxwriter", "openpyxl"]:
pytest.xfail(reason=(
f"GH25351: failing on some attribute comparisons in {engine}"))
# TODO: should find a better way to check equality
assert cell1.alignment.__dict__ == cell2.alignment.__dict__
assert cell1.border.__dict__ == cell2.border.__dict__
assert cell1.fill.__dict__ == cell2.fill.__dict__
assert cell1.font.__dict__ == cell2.font.__dict__
assert cell1.number_format == cell2.number_format
assert cell1.protection.__dict__ == cell2.protection.__dict__
def custom_converter(css):
# use bold iff there is custom style attached to the cell
if css.strip(" \n;"):
return {"font": {"bold": True}}
return {}
pytest.importorskip("jinja2")
pytest.importorskip(engine)
# Prepare spreadsheets
df = DataFrame(np.random.randn(11, 3))
with tm.ensure_clean(".xlsx" if engine != "xlwt" else ".xls") as path:
writer = ExcelWriter(path, engine=engine)
df.to_excel(writer, sheet_name="frame")
df.style.to_excel(writer, sheet_name="unstyled")
styled = df.style.apply(style, axis=None)
styled.to_excel(writer, sheet_name="styled")
ExcelFormatter(styled, style_converter=custom_converter).write(
writer, sheet_name="custom")
writer.save()
if engine not in ("openpyxl", "xlsxwriter"):
# For other engines, we only smoke test
return
openpyxl = pytest.importorskip("openpyxl")
wb = openpyxl.load_workbook(path)
# (1) compare DataFrame.to_excel and Styler.to_excel when unstyled
n_cells = 0
for col1, col2 in zip(wb["frame"].columns, wb["unstyled"].columns):
assert len(col1) == len(col2)
for cell1, cell2 in zip(col1, col2):
assert cell1.value == cell2.value
assert_equal_style(cell1, cell2, engine)
n_cells += 1
# ensure iteration actually happened:
assert n_cells == (11 + 1) * (3 + 1)
# (2) check styling with default converter
# TODO: openpyxl (as at 2.4) prefixes colors with 00, xlsxwriter with FF
alpha = "00" if engine == "openpyxl" else "FF"
n_cells = 0
for col1, col2 in zip(wb["frame"].columns, wb["styled"].columns):
assert len(col1) == len(col2)
for cell1, cell2 in zip(col1, col2):
ref = f"{cell2.column}{cell2.row:d}"
# TODO: this isn't as strong a test as ideal; we should
# confirm that differences are exclusive
if ref == "B2":
assert not cell1.font.bold
assert cell2.font.bold
elif ref == "C3":
assert cell1.font.color.rgb != cell2.font.color.rgb
assert cell2.font.color.rgb == alpha + "0000FF"
elif ref == "D4":
assert cell1.font.underline != cell2.font.underline
assert cell2.font.underline == "single"
elif ref == "B5":
assert not cell1.border.left.style
assert (cell2.border.top.style == cell2.border.right.style
== cell2.border.bottom.style ==
cell2.border.left.style == "medium")
elif ref == "C6":
assert not cell1.font.italic
assert cell2.font.italic
elif ref == "D7":
assert cell1.alignment.horizontal != cell2.alignment.horizontal
assert cell2.alignment.horizontal == "right"
elif ref == "B8":
assert cell1.fill.fgColor.rgb != cell2.fill.fgColor.rgb
assert cell1.fill.patternType != cell2.fill.patternType
assert cell2.fill.fgColor.rgb == alpha + "FF0000"
assert cell2.fill.patternType == "solid"
elif ref == "B9":
assert cell1.number_format == "General"
assert cell2.number_format == "0%"
else:
assert_equal_style(cell1, cell2, engine)
assert cell1.value == cell2.value
n_cells += 1
assert n_cells == (11 + 1) * (3 + 1)
# (3) check styling with custom converter
n_cells = 0
for col1, col2 in zip(wb["frame"].columns, wb["custom"].columns):
assert len(col1) == len(col2)
for cell1, cell2 in zip(col1, col2):
ref = f"{cell2.column}{cell2.row:d}"
if ref in ("B2", "C3", "D4", "B5", "C6", "D7", "B8", "B9"):
assert not cell1.font.bold
assert cell2.font.bold
else:
assert_equal_style(cell1, cell2, engine)
assert cell1.value == cell2.value
n_cells += 1
assert n_cells == (11 + 1) * (3 + 1)
