pandas 导出 Excel 美化实战:多 Sheet、颜色与条件格式
- 2026-11-08 02:33:15
pandas 导出 Excel 美化实战:多 Sheet、颜色与条件格式
DataFrame.to_excel() 能把数据写入 Excel，但它给出的东西本质上是一张"裸表"：没有列宽、没有千分位、负数不会标红等等，总之没有做其他更多的处理。作为报表，这是不够的。
这篇文章从工程实践角度，把 pandas 导出 Excel 的美化拆成四层：多 Sheet 组织 → 静态样式 → 动态条件格式 → 性能与踩坑。所有代码都可直接复制运行。
一、使用的技术
pandas 的 ExcelWriter 支持两种引擎，能力边界完全不同：
openpyxl | ||
xlsxwriter | 只能新建，不能读取已有文件 |
结论：要"精雕细琢 + 追加写入"用 openpyxl；要"百万行 + 花式条件格式"用 xlsxwriter。 下面两条路线都会给完整代码。
依赖安装：
pip install pandas numpy openpyxl xlsxwriter二、多 Sheet 写入的三种方式
方式 1：一次 writer，多次 to_excel（最常用）
import pandas as pdwith pd.ExcelWriter("out.xlsx", engine="openpyxl")as writer:summary.to_excel(writer, sheet_name="区域汇总", index=False)detail.to_excel(writer, sheet_name="销售明细", index=False)
注意 with 是必须的——不退出上下文，文件可能不会完整写入磁盘。
方式 2：追加到已有文件（pandas ≥ 1.3）
with pd.ExcelWriter("out.xlsx", engine="openpyxl",mode="a",# 追加模式if_sheet_exists="replace",# 同名 sheet 覆盖，可选 "new" / "overlay") as writer:new_df.to_excel(writer, sheet_name="本周新增", index=False)
mode="a" 时 writer.book 是已加载的旧工作簿，你照样可以在上面做样式加工。
方式 3：同一 Sheet 内分区摆放（做 Dashboard 很好用）
with pd.ExcelWriter("dashboard.xlsx", engine="openpyxl") as writer:summary.to_excel(writer, sheet_name="看板",index=False,startrow=1, startcol=0)detail.to_excel(writer, sheet_name="看板",index=False,startrow=len(summary)+4, startcol=0)
startrow / startcol 是 0-based 偏移，配合后面的合并单元格、标题行，就能拼出一个像样的看板页。⚠️ Sheet 命名规则：不超过 31 字符，且不能包含 [ ] : * ? / \。中文没问题，但带斜杠的部门名（如"华东/上海"）会直接抛异常，写个 str.replace() 清洗一下。
三、openpyxl 路线：完整可运行脚本
下面这份脚本包含：造数据 → 多 Sheet 写入 → 表头主题色 → 斑马纹 → 数字格式 → 冻结窗格 → 自动筛选 → 列宽自适应 → 四类条件格式。
# -*- coding: utf-8 -*-"""pandas → Excel 美化导出（openpyxl 引擎）依赖：pandas numpy openpyxl>=3.0运行：python make_report.py"""from __future__ import annotationsimport numpy as npimport pandas as pdfrom openpyxl.formatting.rule import (CellIsRule, ColorScaleRule, DataBarRule, FormulaRule,)from openpyxl.styles import Alignment, Border, Font, PatternFill, Sidefrom openpyxl.utils import get_column_letterOUT_PATH = "销售报表.xlsx"# ---------------- 0. 主题常量（改这里就能换肤） ----------------FONT_NAME = "微软雅黑"C_HEADER_BG, C_HEADER_FG = "1F4E78", "FFFFFF"C_BAND = "F2F7FB" # 斑马纹C_LINE = "D9D9D9" # 边框线C_BAD_BG, C_BAD_FG = "FFC7CE", "9C0006"C_GOOD_BG, C_GOOD_FG = "C6EFCE", "006100"_SIDE = Side(style="thin", color=C_LINE)BORDER = Border(left=_SIDE, right=_SIDE, top=_SIDE, bottom=_SIDE)FONT_HEADER = Font(name=FONT_NAME, size=10, bold=True, color=C_HEADER_FG)FONT_BODY = Font(name=FONT_NAME, size=10)FILL_HEADER = PatternFill("solid", fgColor=C_HEADER_BG)FILL_BAND = PatternFill("solid", fgColor=C_BAND)FILL_BAD = PatternFill("solid", fgColor=C_BAD_BG)FILL_GOOD = PatternFill("solid", fgColor=C_GOOD_BG)# ---------------- 1. 造一份带亏损的数据 ----------------def make_data(seed: int = 42):rng = np.random.default_rng(seed)n = 300base = pd.Timestamp("2025-01-01")sales = rng.normal(12000, 4000, n).clip(1500).round(2)cost = (sales * rng.normal(0.88, 0.16, n)).round(2) # 会有成本 > 销售额的情况detail = pd.DataFrame({"日期": (base + pd.to_timedelta(rng.integers(0, 120, n), unit="D")).date,"区域": rng.choice(["华东", "华北", "华南", "西南"], n),"渠道": rng.choice(["线上", "门店", "分销"], n),"销售额": sales,"成本": cost,"订单数": rng.integers(10, 200, n),})detail["毛利"] = (detail["销售额"] - detail["成本"]).round(2)detail["毛利率"] = (detail["毛利"] / detail["销售额"]).round(4)detail = detail.sort_values("日期").reset_index(drop=True)summary = (detail.groupby("区域", as_index=False).agg(销售额=("销售额", "sum"), 成本=("成本", "sum"),订单数=("订单数", "sum"), 毛利=("毛利", "sum")))summary["毛利率"] = (summary["毛利"] / summary["销售额"]).round(4)summary = summary.sort_values("销售额", ascending=False).reset_index(drop=True)return detail, summary# ---------------- 2. 静态样式 ----------------def _text_width(value) -> int:"""按 Excel 列宽单位估算显示宽度：CJK 字符算 2"""return sum(2 if ord(ch) > 0x2E80 else 1 for ch in str(value))def autofit_columns(ws, df, min_width=10, max_width=32, sample=300):"""列宽自适应（Excel 不支持真正的 auto-fit，只能估算）"""for idx, col in enumerate(df.columns, start=1):width = _text_width(col)for v in df[col].head(sample):width = max(width, _text_width(v))ws.column_dimensions[get_column_letter(idx)].width = min(max(width + 3, min_width), max_width)def style_sheet(ws, df, *, number_formats=None, align_map=None,freeze="A2", autofilter=True, banded=True,header_fill=None):"""给一张已经写完数据的 sheet 上样式"""n_rows, n_cols = len(df), len(df.columns)number_formats = number_formats or {}align_map = align_map or {}cols = list(df.columns)h_fill = PatternFill("solid", fgColor=header_fill) if header_fill else FILL_HEADER# --- 表头 ---for j in range(1, n_cols + 1):c = ws.cell(row=1, column=j)c.font, c.fill, c.border = FONT_HEADER, h_fill, BORDERc.alignment = Alignment(horizontal="center", vertical="center",wrap_text=True)ws.row_dimensions[1].height = 26# --- 数据区 ---for i in range(2, n_rows + 2):ws.row_dimensions[i].height = 17is_band = banded and i % 2 == 0for j in range(1, n_cols + 1):c = ws.cell(row=i, column=j)c.font, c.border = FONT_BODY, BORDERc.alignment = Alignment(horizontal=align_map.get(cols[j - 1], "left"),vertical="center",)if is_band:c.fill = FILL_BAND# --- 数字格式 ---for name, fmt in number_formats.items():if name not in cols:continuej = cols.index(name) + 1for i in range(2, n_rows + 2):ws.cell(row=i, column=j).number_format = fmtautofit_columns(ws, df)if freeze:ws.freeze_panes = freeze # "A2" 冻结首行，"B2" 再冻结首列if autofilter and n_rows:ws.auto_filter.ref = f"A1:{get_column_letter(n_cols)}{n_rows + 1}"ws.sheet_view.showGridLines = False # 关掉网格线，报表立刻"高级"起来# ---------------- 3. 条件格式 ----------------def _col_range(df, name) -> str:letter = get_column_letter(list(df.columns).index(name) + 1)return f"{letter}2:{letter}{len(df) + 1}"def cf_data_bar(ws, df, name, color="638EC6"):"""数据条：一眼看出量级差异"""ws.conditional_formatting.add(_col_range(df, name),DataBarRule(start_type="num", start_value=0, end_type="max",color=color, showValue=True),)def cf_color_scale(ws, df, name, reverse=False):"""三色阶：低红-中黄-高绿"""lo, mid, hi = "F8696B", "FFEB84", "63BE7B"if reverse:lo, hi = hi, lows.conditional_formatting.add(_col_range(df, name),ColorScaleRule(start_type="min", start_color=lo,mid_type="percentile", mid_value=50, mid_color=mid,end_type="max", end_color=hi,),)def cf_cell_rule(ws, df, name, operator, value, *, good=False):"""单元格规则：如 毛利 < 0 标红"""fill = FILL_GOOD if good else FILL_BADfg = C_GOOD_FG if good else C_BAD_FGws.conditional_formatting.add(_col_range(df, name),CellIsRule(operator=operator, formula=[str(value)], fill=fill,font=Font(name=FONT_NAME, color=fg, bold=True)),)def cf_row_formula(ws, df, formula, *, good=False):"""公式规则：整行高亮。formula 相对区域左上角 A2 求值，例如 '$G2<0'"""last = get_column_letter(len(df.columns))ws.conditional_formatting.add(f"A2:{last}{len(df) + 1}",FormulaRule(formula=[formula],fill=FILL_GOOD if good else FILL_BAD,stopIfTrue=False),)# ---------------- 4. 主流程 ----------------def build():detail, summary = make_data()MONEY = "#,##0.00"PCT = "0.00%"with pd.ExcelWriter(OUT_PATH, engine="openpyxl") as writer:detail.to_excel(writer, sheet_name="销售明细", index=False)summary.to_excel(writer, sheet_name="区域汇总", index=False)# ===== 明细页 =====ws = writer.sheets["销售明细"]style_sheet(ws, detail,number_formats={"销售额": MONEY, "成本": MONEY,"毛利": MONEY, "毛利率": PCT},align_map={"日期": "center", "区域": "center", "渠道": "center","订单数": "right", "销售额": "right", "成本": "right","毛利": "right", "毛利率": "right"},)cf_color_scale(ws, detail, "销售额") # 销售额三色阶cf_data_bar(ws, detail, "订单数", color="7FB3D5") # 订单数数据条cf_cell_rule(ws, detail, "毛利", "lessThan", 0) # 负毛利标红# 整行预警：毛利为负 → 整行淡红loss_letter = get_column_letter(list(detail.columns).index("毛利") + 1)cf_row_formula(ws, detail, f"${loss_letter}2<0")# ===== 汇总页（换个表头色，视觉上区分层级）=====ws2 = writer.sheets["区域汇总"]style_sheet(ws2, summary,number_formats={"销售额": MONEY, "成本": MONEY,"毛利": MONEY, "毛利率": PCT},align_map={"区域": "center", "订单数": "right", "销售额": "right","成本": "right", "毛利": "right", "毛利率": "right"},header_fill="2E75B6",)cf_data_bar(ws2, summary, "销售额")cf_color_scale(ws2, summary, "毛利率")# 把汇总页挪到第一个并设为默认打开页writer.book.move_sheet("区域汇总", offset=-1)writer.book.active = 0print(f"✅ 已生成 {OUT_PATH}")if __name__ == "__main__":build()
跑完打开文件，你会看到：汇总页在最前、表头深蓝白字、隔行浅蓝、销售额带渐变数据条、毛利率红黄绿色阶、明细页里凡是毛利为负的整行都会泛红。
几个细节注意：
① number_format 是作用在"显示层"上的。0.00% 让 0.1234 显示成 12.34%，单元格里存的仍然是 0.1234，后续在 Excel 里做透视表、算公式完全不受影响。同理 #,##0.00 加千分位。
② 条件格式的颜色必须写 start_color / end_color。 openpyxl 里用 PatternFill("solid", fgColor=...) 也可以，但用 start_color 更保险，因为 Excel 在渲染 differential format 时读的是它。
③ 公式规则里的引用是相对区域左上角。 我们给 A2:H301 加 $G2<0，Excel 会对每一行按行号偏移求值，所以能实现"整行高亮"。列名用 $ 锁死，行号不能锁。
④ 列宽只能估算。 Excel 的列宽单位是"标准字体的字符数"，中文字符约占 2 个宽度，所以 _text_width 里用 ord(ch) > 0x2E80 判断。真·auto-fit 只有 Excel 客户端能做到（或者用 xlsxwriter 的 autofit()，它内部也是估算）。
四、xlsxwriter 路线：条件格式更爽、更快
如果你的数据量上了十万行，或者条件格式规则复杂到十几条，换 xlsxwriter。这段代码中的 make_data()是使用前面已经给出的代码中的 make_data()方法。
import pandas as pddetail, summary = make_data()n = len(summary)with pd.ExcelWriter("销售报表_xw.xlsx", engine="xlsxwriter") as writer:summary.to_excel(writer, sheet_name="区域汇总", index=False)wb = writer.bookws = writer.sheets["区域汇总"]fmt_money = wb.add_format({"num_format": "#,##0.00","font_name": "微软雅黑", "font_size": 10})fmt_pct = wb.add_format({"num_format": "0.00%","font_name": "微软雅黑", "font_size": 10})fmt_bad = wb.add_format({"bg_color": "#FFC7CE", "font_color": "#9C0006"})# 列宽 + 列级数字格式（单元格没格式时自动继承）ws.set_column("A:A", 10)ws.set_column("B:C", 16, fmt_money)ws.set_column("D:D", 10)ws.set_column("E:E", 16, fmt_money)ws.set_column("F:F", 12, fmt_pct)ws.freeze_panes(1, 0)ws.autofilter(0, 0, n, len(summary.columns) - 1)# 条件格式：行列均 0-based，(首行, 首列, 末行, 末列)ws.conditional_format(1, 1, n, 1, { # 销售额：数据条"type": "data_bar", "bar_color": "#638EC6", "bar_solid": True,})ws.conditional_format(1, 5, n, 5, { # 毛利率：三色阶"type": "3_color_scale","min_color": "#F8696B", "mid_color": "#FFEB84", "max_color": "#63BE7B",})ws.conditional_format(1, 4, n, 4, { # 毛利 < 0 标红"type": "cell", "criteria": "<", "value": 0, "format": fmt_bad,})ws.conditional_format(1, 0, n, 5, { # 公式规则：整行高亮"type": "formula","criteria": "=$E2<0","format": wb.add_format({"bg_color": "#FFF2CC"}),})# xlsxwriter 还白送一堆 openpyxl 要手写的花样ws.conditional_format(1, 3, n, 3, {"type": "icon_set", "icon_style": "3_arrows"})
xlsxwriter 的优势集中在三点：
规则用 dict 描述，几十种
type（data_bar/3_color_scale/icon_set/cell/formula/top10/duplicate/text…），比 openpyxl 的类体系直观太多；icon_set图标集、add_sparkline迷你图、add_chart原生图表，openpyxl 要么不支持要么很难用；{'constant_memory': True}常量内存模式能把百万行写入的内存占用压到几十 MB：
with pd.ExcelWriter("big.xlsx", engine="xlsxwriter",engine_kwargs={"options":{"constant_memory":True}},) as writer:big_df.to_excel(writer, sheet_name="明细", index=False)
代价是它不能读取和修改已有文件，且 constant_memory 模式下必须按行顺序写、不能回头改样式。五、踩坑清单
写到生产里，下面这些几乎一定会遇到：
sheet_name非法字符。带/、:的名称直接抛ValueError。统一清洗：re.sub(r"[\[\]:*?/\\]", "-", name)[:31]。index=False忘写。默认会把 DataFrame 索引也导出去，多出一列Unnamed: 0。日期列变成文本。如果列里混了
None和datetime，pandas 会推成object，Excel 里就是左对齐的字符串，没法按日期排序。写之前pd.to_datetime(df["日期"], errors="coerce")强制一下。时区感知的 datetime 无法写入。
tz-aware的Timestamp会报错，先.dt.tz_localize(None)。条件格式区域写错。
A2:A301写成A2:A300会漏掉最后一行，而且不报错，只是效果不对。用len(df) + 1动态算，别硬编码。openpyxl 写大文件极慢。十万行 × 20 列，openpyxl 逐格设样式可能要几分钟。这种场景要么换 xlsxwriter，要么只给表头加样式、数据区交给 Excel 自带的"套用表格格式"。
文件被 Excel 占用。
PermissionError: [Errno 13]十有八九是你自己开着那个 xlsx。写入前先检测，或者用时间戳命名输出文件。样式对象不要在每个循环里 new。上面代码把
Font、PatternFill提到模块级复用，十万次循环下能省下可观的 GC 开销。
六、小结
总体上看，其实只有三件事：
结构：一个
ExcelWriter管多 Sheet，mode="a"管追加，startrow/startcol管同页分区；静态样式：表头色块 + 斑马纹 + 数字格式 + 冻结窗格 + 隐藏网格线，这五件套做完，观感就能从"数据导出"跃迁到"报表"；
动态条件格式：色阶看分布，数据条看量级，单元格规则和公式规则做异常预警——注意条件格式是"显示层"，不改数据，这一点在做审计留痕时非常重要。
最后一句经验：别一次性把所有美化逻辑写死在业务代码里。 把上面的 style_sheet / cf_* 抽成独立的 excel_style.py，业务侧只负责 df 和"这一页用哪套主题"，后面加个新报表就是三行代码的事。
本文来自网友投稿或网络内容，如有侵犯您的权益请联系我们删除，联系邮箱：wyl860211@qq.com 。
