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)