def test_write_cells_merge_styled(ext): from pandas.io.formats.excel import ExcelCell sheet_name = "merge_styled" sty_b1 = {"font": {"color": "00FF0000"}} sty_a2 = {"font": {"color": "0000FF00"}} initial_cells = [ ExcelCell(col=1, row=0, val=42, style=sty_b1), ExcelCell(col=0, row=1, val=99, style=sty_a2), ] sty_merged = {"font": {"color": "000000FF", "bold": True}} sty_kwargs = _OpenpyxlWriter._convert_to_style_kwargs(sty_merged) openpyxl_sty_merged = sty_kwargs["font"] merge_cells = [ ExcelCell(col=0, row=0, val="pandas", mergestart=1, mergeend=1, style=sty_merged) ] with tm.ensure_clean(ext) as path: with _OpenpyxlWriter(path) as writer: writer._write_cells(initial_cells, sheet_name=sheet_name) writer._write_cells(merge_cells, sheet_name=sheet_name) wks = writer.sheets[sheet_name] xcell_b1 = wks["B1"] xcell_a2 = wks["A2"] assert xcell_b1.font == openpyxl_sty_merged assert xcell_a2.font == openpyxl_sty_merged
def test_write_cells_merge_styled(ext): from pandas.io.formats.excel import ExcelCell sheet_name = 'merge_styled' sty_b1 = {'font': {'color': '00FF0000'}} sty_a2 = {'font': {'color': '0000FF00'}} initial_cells = [ ExcelCell(col=1, row=0, val=42, style=sty_b1), ExcelCell(col=0, row=1, val=99, style=sty_a2), ] sty_merged = {'font': {'color': '000000FF', 'bold': True}} sty_kwargs = _OpenpyxlWriter._convert_to_style_kwargs(sty_merged) openpyxl_sty_merged = sty_kwargs['font'] merge_cells = [ ExcelCell(col=0, row=0, val='pandas', mergestart=1, mergeend=1, style=sty_merged), ] with ensure_clean(ext) as path: writer = _OpenpyxlWriter(path) writer.write_cells(initial_cells, sheet_name=sheet_name) writer.write_cells(merge_cells, sheet_name=sheet_name) wks = writer.sheets[sheet_name] xcell_b1 = wks['B1'] xcell_a2 = wks['A2'] assert xcell_b1.font == openpyxl_sty_merged assert xcell_a2.font == openpyxl_sty_merged
def _format_header_mi(self) -> Iterable[ExcelCell]: """Currently, as of pandas 1.3.4, still cannot write to Excel with MultiIndex columns and no index ('index'=False). Added a few hacks to make it work until hopefully it gets implemented soon. TODO: Implement a better solution. """ if not (self._has_aliases or self.header): return columns = self.columns level_strs = columns.format(sparsify=self.merge_cells, adjoin=False, names=False) level_lengths = get_level_lengths(level_strs) coloffset = 0 lnum = 0 if self.index and isinstance(self.df.index, MultiIndex): coloffset = len(self.df.index[0]) - 1 if not self.index: # hack 1/2 coloffset = coloffset - 1 if self.merge_cells: # Format multi-index as a merged cells. if self.index: # hack 2/2 # if not self.index, skip this to avoid negative col index for lnum, name in enumerate(columns.names): yield ExcelCell( row=lnum, col=coloffset, val=name, style=self.header_style, ) for lnum, (spans, levels, level_codes) in enumerate( zip(level_lengths, columns.levels, columns.codes) ): values = levels.take(level_codes) for i, span_val in spans.items(): spans_multiple_cells = span_val > 1 yield ExcelCell( row=lnum, col=coloffset + i + 1, val=values[i], style=self.header_style, mergestart=lnum if spans_multiple_cells else None, mergeend=(coloffset + i + span_val if spans_multiple_cells else None), ) else: # Format in legacy format with dots to indicate levels. for i, values in enumerate(zip(*level_strs)): v = ".".join(map(pprint_thing, values)) yield ExcelCell(lnum, coloffset + i + 1, v, self.header_style) self.rowcounter = lnum
def _generate_body(self, coloffset: int) -> Iterable[ExcelCell]: if self.row_styler: for rowidx in range(len(self.df.index)): series = self.df.iloc[rowidx] xlstyle = self.row_styler(series) for i, val in enumerate(series): yield ExcelCell(self.rowcounter + rowidx, i + coloffset, val, xlstyle) else: yield from super()._generate_body(coloffset)
def write_excel_by_rows(self, coloffset: int): if self.styler is None: styles = None else: styles = self.styler._compute().ctx if not styles: styles = None xlstyle = None for rowidx in range(self.df.shape[0]): for colidx in range(len(self.columns)): if styles is not None: xlstyle = self.style_converter(";".join(styles[rowidx, colidx])) yield ExcelCell(self.rowcounter + rowidx, colidx + coloffset, self.df.iloc[rowidx, colidx], xlstyle)
def _generate_body_rowwise(self, coloffset: int) -> Iterable[ExcelCell]: # useful if you want to generate the body row-wise, instead of # the default, which is column-wise if self.styler is None: styles = None else: styles = self.styler._compute().ctx if not styles: styles = None xlstyle = None for rowidx in range(len(self.df.index)): series = self.df.iloc[rowidx] for i, val in enumerate(series): if styles is not None: css = ";".join([a + ":" + str(v) for (a, v) in styles[rowidx, i]]) xlstyle = self.style_converter(css) yield ExcelCell(self.rowcounter + rowidx, i + coloffset, val, xlstyle)
for sheet_name, mon_rr_dic in sheet_mon_rr_dic.items(): start_row = 1 for name, monthly_rr_df in mon_rr_dic.items(): year_set = { trade_date.year for trade_date in monthly_rr_df.index } monthly_rr_matrix_df = pd.DataFrame(index=year_set, columns=range(1, 13)) for trade_date, rr_s in monthly_rr_df.T.items(): monthly_rr_matrix_df.loc[ trade_date.year, trade_date.month] = '%2.2f%%' % (rr_s[0] * 100) # 写 excel # sheet.write(start_row, 0, name) writer.write_cells([ExcelCell(0, 0, name)], sheet_name, startrow=start_row - 1) monthly_rr_matrix_df.to_excel(writer, sheet_name, startrow=start_row) start_row += len(year_set) + 3 finally: writer.close() # 基金净值合并 # file_list = [ # {'file_path': r"d:\Works\F复华投资\L路演、访谈、评估报告\新萌\复华1号历史净值180105(1).xls"}, # {'file_path': r"d:\Works\F复华投资\L路演、访谈、评估报告\新萌\历史净值171017.xls", # 'date_colum_name': '净值日期', 'nav_colum_name_list': ['最新净值']}, # {'file_path': r"d:\Works\F复华投资\L路演、访谈、评估报告\新萌\新萌拟合后净值.xlsx",
def _format_hierarchical_rows(self) -> Iterable[ExcelCell]: """Kludgey fix for: https://github.com/pandas-dev/pandas/issues/27772 Code is verbatim from parent EXCEPT for the 'pass' line and the following commented out line """ if self._has_aliases or self.header: self.rowcounter += 1 gcolidx = 0 if self.index: index_labels = self.df.index.names # check for aliases if self.index_label and isinstance(self.index_label, (list, tuple, np.ndarray, Index)): index_labels = self.index_label # MultiIndex columns require an extra row # with index names (blank if None) for # unambiguous round-trip, unless not merging, # in which case the names all go on one row Issue #11328 if isinstance(self.columns, MultiIndex) and self.merge_cells: pass # self.rowcounter += 1 # if index labels are not empty go ahead and dump if com.any_not_none(*index_labels) and self.header is not False: for cidx, name in enumerate(index_labels): yield ExcelCell(self.rowcounter - 1, cidx, name, self.header_style) if self.merge_cells: # Format hierarchical rows as merged cells. level_strs = self.df.index.format(sparsify=True, adjoin=False, names=False) level_lengths = get_level_lengths(level_strs) for spans, levels, level_codes in zip( level_lengths, self.df.index.levels, self.df.index.codes ): values = levels.take( level_codes, allow_fill=levels._can_hold_na, fill_value=levels._na_value, ) for i, span_val in spans.items(): spans_multiple_cells = span_val > 1 yield ExcelCell( row=self.rowcounter + i, col=gcolidx, val=values[i], style=self.header_style, mergestart=( self.rowcounter + i + span_val - 1 if spans_multiple_cells else None ), mergeend=gcolidx if spans_multiple_cells else None, ) gcolidx += 1 else: # Format hierarchical rows with non-merged values. for indexcolvals in zip(*self.df.index): for idx, indexcolval in enumerate(indexcolvals): yield ExcelCell( row=self.rowcounter + idx, col=gcolidx, val=indexcolval, style=self.header_style, ) gcolidx += 1 yield from self._generate_body(gcolidx)