ARTICLE · 1350543

Excel模板+数据:用 Python 把原始数据自动“填”进 Excel 模板,自动导出漂亮报表

发布时间：2026-11-13 13:23:38 最近访问：2026-11-13 13:23:38

现在，自动化办公越来越流行，尤其AI的爆火，更促进了自动化办公的长足发展。在日常的自动化报表中，填写数据不难，但又要填写数据，又要把表格做的好看，还是有一定难度的。有的模板里，常常放好了 Logo、标题、合并单元格、边框、颜色、公式、打印区域，甚至多 Sheet 结构。如果直接用 df.to_excel() 覆盖，就只有 DataFrame 的默认的样式。更稳的思路是：

Pandas 负责数据处理，openpyxl 负责打开模板并精确填充数据。今天我们来学习和探讨结合数据和模板的方式，自动输出漂亮的 Excel 文档

一、为什么推荐“模板 + 原始数据”方案？

如果从零生成 Excel，可以用 XlsxWriter 或 openpyxl 自己写样式；但如果公司已经有固定模板，就不建议再用代码硬写格式。模板方案的好处很明显：

格式由 Excel 维护：标题、颜色、边框、合并单元格都在模板里；
公式不会被覆盖：汇总、比率、小计可以留在模板中；
多 Sheet 更稳定：封面、说明页、明细表、汇总表可以提前设计；
业务人员也能改模板：调整版式不需要改 Python 代码。

需要注意的是，openpyxl 保存模板时，字体、边框、底色、合并单元格、列宽行高、打印区域通常能保留；但图表、图片、数据透视表、切片器、VBA 宏等高级元素不一定稳定。所以模板越“表格化”，自动化效果越好。

二、模板怎么设计更合适？

假设我们要生成一份《月度内容运营报表》，模板文件叫：月度内容报表模板.xlsx。模板里可以提前放 3 个 Sheet：

Sheet	用途	模板里放什么
报表信息	报表抬头	月份、生成时间、数据周期
渠道汇总	汇总数据	表头 + 若干空行
内容明细	原始数据	表头 + 预留空行
设计时建议遵循几个原则：

给关键单元格定义名称比如把“月份”单元格命名为 month，把“生成时间”命名为 report_time。这样即使模板位置调整，代码也不需要改。
明细区预留足够空行不要依赖代码频繁插入行。插入行容易破坏合并单元格和样式，预留空行更稳。
表头和数据区分开模板只放表头，Python 从第二行开始填数据。
汇总公式留在模板里比如 SUM、AVERAGE、占比公式都放在模板中，Python 只负责填原始数据。

三、完整代码示例

安装依赖：

pip install pandas openpyxl

完整脚本如下：

from datetime import datetimefrom pathlib import Pathimport pandas as pdfrom openpyxl import load_workbook# =========================# 1. 准备原始数据# =========================data = [    {        "日期": "2026-11-02",        "渠道": "公众号",        "内容类型": "图文",        "阅读量": 3200,        "点赞": 180,        "分享": 95,        "转化": 42,    },    {        "日期": "2026-11-03",        "渠道": "公众号",        "内容类型": "视频",        "阅读量": 5100,        "点赞": 310,        "分享": 160,        "转化": 78,    },    {        "日期": "2026-11-04",        "渠道": "小红书",        "内容类型": "图文",        "阅读量": 2800,        "点赞": 220,        "分享": 130,        "转化": 55,    },    {        "日期": "2026-11-05",        "渠道": "小红书",        "内容类型": "视频",        "阅读量": 4600,        "点赞": 390,        "分享": 210,        "转化": 91,    },    {        "日期": "2026-11-06",        "渠道": "知乎",        "内容类型": "图文",        "阅读量": 1900,        "点赞": 150,        "分享": 88,        "转化": 37,    },    {        "日期": "2026-11-07",        "渠道": "知乎",        "内容类型": "视频",        "阅读量": 2300,        "点赞": 170,        "分享": 92,        "转化": 44,    },    {        "日期": "2026-11-08",        "渠道": "公众号",        "内容类型": "图文",        "阅读量": 3600,        "点赞": 210,        "分享": 110,        "转化": 60,    },    {        "日期": "2026-11-09",        "渠道": "公众号",        "内容类型": "视频",        "阅读量": 5400,        "点赞": 340,        "分享": 175,        "转化": 85,    },    {        "日期": "2026-11-10",        "渠道": "小红书",        "内容类型": "图文",        "阅读量": 3100,        "点赞": 260,        "分享": 140,        "转化": 62,    },    {        "日期": "2026-11-11",        "渠道": "小红书",        "内容类型": "视频",        "阅读量": 4900,        "点赞": 410,        "分享": 230,        "转化": 97,    },]df = pd.DataFrame(data)df["日期"] = pd.to_datetime(df["日期"])# =========================# 2. 用 Pandas 做汇总# =========================channel_summary = (    df.groupby("渠道", as_index=False)    .agg(        总阅读量=("阅读量", "sum"),        总点赞=("点赞", "sum"),        总分享=("分享", "sum"),        总转化=("转化", "sum"),        内容数量=("内容类型", "count"),    )    .sort_values("总阅读量", ascending=False)    .reset_index(drop=True))# =========================# 3. 打开 Excel 模板# =========================template_path = Path("月度内容报表模板.xlsx")output_path = Path(f"月度内容报表_{datetime.now().strftime('%Y%m%d')}.xlsx")wb = load_workbook(template_path)# 如果模板里使用了命名区域，可以用命名区域定位# 例如：wb.defined_names["month"]def get_named_cell(workbook, name: str):    """根据定义名称获取单元格，适合模板位置变化时使用。"""    defined = workbook.defined_names.get(name)    if defined is None:        raise ValueError(f"模板中未找到定义名称：{name}")    dest = list(defined.destinations)[0]    ws_title, ref = dest    ws = workbook[ws_title]    return ws[ref.replace("$", "")]# =========================# 4. 填充“报表信息”Sheet# =========================ws_info = wb["报表信息"]try:    get_named_cell(wb, "month").value = "2026年11月"    get_named_cell(wb, "report_time").value = datetime.now().strftime("%Y-%m-%d %H:%M")except ValueError:    # 如果模板没有定义名称，就退回固定单元格位置    ws_info["B2"].value = "2026年11月"    ws_info["B3"].value = datetime.now().strftime("%Y-%m-%d %H:%M")# =========================# 5. 填充“渠道汇总”Sheet# =========================ws_channel = wb["渠道汇总"]CHANNEL_START_ROW = 2CHANNEL_START_COL = 1for idx, row in enumerate(    channel_summary.itertuples(index=False), start=CHANNEL_START_ROW):    for col_idx, value in enumerate(row, start=CHANNEL_START_COL):        ws_channel.cell(row=idx, column=col_idx).value = value# =========================# 6. 填充“内容明细”Sheet# =========================ws_detail = wb["内容明细"]DETAIL_START_ROW = 2DETAIL_START_COL = 1for idx, row in enumerate(df.itertuples(index=False), start=DETAIL_START_ROW):    for col_idx, value in enumerate(row, start=DETAIL_START_COL):        ws_detail.cell(row=idx, column=col_idx).value = value# =========================# 7. 保存报表# =========================wb.save(output_path)print(f"报表已生成：{output_path}")

四、这段代码的关键点

不要直接覆盖模板整张表只修改需要填充的单元格，模板里的边框、颜色、合并单元格、公式才会保留。
优先使用定义名称定位单元格模板里的 month、report_time 如果通过 Excel “定义名称”设置，代码会更稳定。
汇总表和明细表从固定起始行开始填比如汇总表从第 2 行开始，明细表也从第 2 行开始，这样模板表头不会被覆盖。
日期字段保持真实日期类型df["日期"] = pd.to_datetime(df["日期"]) 之后写入 Excel，Excel 才能正确识别为日期。
模板里保留公式更省心例如合计行、占比、平均值可以放在模板中，Python 只填数据，Excel 打开后自动计算。

五、如果想进一步美化，可以这样扩展

如果模板本身样式已经比较完整，代码里不需要再写大量样式。需要增强时，可以追加少量 openpyxl 美化逻辑：

from openpyxl.styles import Font, PatternFill, Alignmentheader_fill = PatternFill("solid", fgColor="D9EAF7")header_font = Font(bold=True, color="1F4E78")for cell in ws_channel[1]:    cell.fill = header_fill    cell.font = header_font    cell.alignment = Alignment(horizontal="center")