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)