简介Python-Excel-Template 是一份面向数据处理与报表自动化开发者的实战型资源包适合已掌握 Python 基础、希望系统学习 Excel 读写、模板填充与自动化报告生成的初中级开发者。资源围绕 Pandas、OpenPyXL、XlsxWriter、xlrd/xlwt 及 jinja2 等主流库展开覆盖数据清洗、聚合排序、条件格式化、图表生成与自动化报告等典型场景。压缩包共 19 个文件约 357KB包含 10 个 py 脚本、5 个 xlsx 与 3 个 xlsm 模板文件及 1 个 md 说明脚本与模板配套便于对照理解模板填充与 VBA 交互逻辑。目前已有 543 人学习下载。读者可从中获得可直接运行的示例代码、预置 Excel 模板结构以及模板引擎替换占位符的实践思路快速搭建属于自己的自动化报表流程。1. 用 Python-Excel-Template 把报表生成从手工活变成流水线每月初财务、运营、供应链的同事都会收到同一类需求把数据库里的数据填进一份格式固定的 Excel 模板加粗表头、合并单元格、冻结首行、按部门拆 Sheet、最后发邮件。手工做一次两小时做十次就是二十小时而且每次都会有人忘记改标题里的月份。Python-Excel-Template 这个方向要解决的正是这件事用一份可复用的模板文件加一段 Python 脚本把「填数据」和「排版」彻底分开让程序只负责往指定坐标写值格式全部由模板预先定义好。它适合两类人一类是每周都要出固定格式报表的运营和财务另一类是给业务部门做数据交付的后端或数据工程师。核心思路不复杂但真正落地时会遇到合并单元格写不进去、日期变成数字、公式被覆盖这些坑下面按实际做一遍的顺序拆开讲。2. 模板与数据分离openpyxl 和 xlwings 到底怎么选2.1 先想清楚「模板」到底由谁维护做这类工具最容易翻车的地方是一开始就把排版逻辑写进代码。比如用 openpyxl 新建一个 Workbook然后逐行设置字体、边框、列宽。这样写出来的脚本业务同事想改一下表头颜色都得来找你改代码。正确的做法是让业务方用 Excel 自己做好一份 .xlsx 模板里面该合并的合并、该设公式的设公式、该冻结的冻结Python 只负责往预留好的单元格里填数据。模板文件是「格式的唯一真相」代码是「数据的搬运工」。这个边界一旦立住后面维护成本会低一个数量级。选型上openpyxl 和 xlwings 是两条主要路线。openpyxl 纯 Python 实现不依赖本机装 Excel适合跑在 Linux 服务器或容器里做定时任务缺点是它只能处理 .xlsx对宏、图表、复杂条件格式的支持有限而且读写大文件时内存占用明显。xlwings 则是通过 COM 接口驱动本机安装的 Excel能调用 Excel 的全部能力公式重算、透视表刷新、宏都能跑代价是必须装在 Windows 或 macOS 且有 Excel 的机器上没法在无桌面的服务器上跑。我一般的判断标准是如果模板里只有静态格式和简单公式选 openpyxl如果模板里有需要重算的复杂公式、数据透视表或者 VBA选 xlwings。2.2 用 openpyxl 跑通最小填充流程先装依赖然后准备一份模板。假设模板文件叫 report_template.xlsxSheet 名叫「月报」B2 是标题B4:F4 是表头第 5 行开始留空给数据。pip install openpyxlfrom openpyxl import load_workbook from datetime import date # 加载模板keep_vbaFalse 表示不保留宏如果模板带宏要改成 True 并另存为 xlsm wb load_workbook(report_template.xlsx, keep_vbaFalse) ws wb[月报] # 往预留单元格写值标题、日期、数据区 ws[B2] 2025年3月销售月报 ws[B3] date(2025, 3, 1) # 写 date 对象配合单元格格式显示为日期 ws[B3].number_format yyyy-mm-dd rows [ (华东, 128000, 320, 400.0, 0.08), (华南, 96000, 240, 400.0, 0.06), (华北, 154000, 385, 400.0, 0.09), ] # 从第5行开始逐行写入列顺序与模板表头一致 for i, row in enumerate(rows, start5): for j, value in enumerate(row, start2): # 从 B 列开始 ws.cell(rowi, columnj, valuevalue) wb.save(report_202503.xlsx)这段代码的关键点有三个。第一load_workbook默认会保留模板里的样式所以不需要重新设字体边框。第二写日期时传date对象而不是字符串再配合number_formatExcel 里才能正常参与日期计算如果直接写字符串「2025-03-01」Excel 会当成文本后续做月份筛选就会出问题。第三ws.cell(rowi, columnj)的 row 和 column 都是从 1 开始计数的和 Excel 界面一致但和 Python 列表的 0 起始不同写循环时容易差一行建议先用两三条数据验证坐标再批量跑。2.3 合并单元格的写入规则模板里最常见的格式就是合并单元格比如 B2:F2 合并成一个大标题。openpyxl 对合并单元格的处理有个硬规则值只能写在合并区域的左上角单元格写其他位置虽然不报错但打开 Excel 后看不到。所以如果模板里 B2:F2 是合并的代码里必须写ws[B2]写ws[C2]就白写了。读取时也一样只有左上角有值其他返回 None。这个规则在调试时经常让人怀疑人生明明赋值了却显示空白八成就是写到了合并区域的非左上角。# 查看模板里所有合并区域确认该往哪个坐标写 for merged_range in ws.merged_cells.ranges: print(merged_range) # 输出如 B2:F2说明值要写到 B22.4 用 xlwings 处理需要重算的模板如果模板里有 SUMIF、VLOOKUP 这类公式且依赖填入的数据重算openpyxl 保存后公式不会自动计算下次用 Excel 打开才会算。如果下游程序要直接读计算结果就会读到旧值或 None。这时候用 xlwings 更稳因为它驱动的是真实 Excel 进程写入后可以主动触发重算。import xlwings as xw # visibleFalse 后台运行不弹窗add_bookFalse 不新建空工作簿 app xw.App(visibleFalse, add_bookFalse) wb app.books.open(report_template.xlsx) ws wb.sheets[月报] ws.range(B2).value 2025年3月销售月报 ws.range(B5).value [[华东, 128000, 320, 400.0, 0.08], [华南, 96000, 240, 400.0, 0.06]] # 触发全量重算确保公式结果是最新的 app.calculate() wb.save(report_202503.xlsx) wb.close() app.quit()app.calculate()是 xlwings 相比 openpyxl 的核心优势它让 Excel 引擎真正算一遍。注意app.quit()一定要放在 finally 里否则脚本异常退出时会留下后台 Excel 进程跑几次就把内存吃满了。另外 xlwings 写入二维列表时ws.range(B5).value [[...]]会自动向右向下扩展不需要手动指定结束单元格这一点比逐格写方便。3. 批量生成与按维度拆分 Sheet 的完整脚本3.1 从数据库或 CSV 拿到数据实际场景里数据很少是硬编码的通常来自数据库查询或 CSV。这里用 pandas 读 CSV 做示例因为 pandas 和 openpyxl 配合最顺。假设源文件 sales.csv 有部门、区域、金额、单量、客单价、增长率六列。import pandas as pd df pd.read_csv(sales.csv, dtype{部门: str, 区域: str}) # 按部门分组后续每个部门生成一个 Sheet groups df.groupby(部门) print(df.dtypes) # 确认金额是数值型日期列已 parse 成 datetime读 CSV 时两个参数最容易被忽略。dtype用来强制指定某些列为字符串防止「001」这种编号被自动转成数字 1。如果 CSV 里有日期列加parse_dates[日期]让 pandas 直接解析成 datetime写进 Excel 时再配合 number_format比在 Excel 里手动分列省事得多。3.2 复制模板 Sheet 并逐部门填充openpyxl 没有直接的「复制 Sheet」API常见做法是用copy_worksheet但它只复制值和样式不复制列宽行高和部分条件格式。更稳的做法是每个部门都从模板文件重新 load 一次或者用wb.copy_worksheet(ws)后手动补列宽。from openpyxl import load_workbook from openpyxl.utils import get_column_letter import pandas as pd df pd.read_csv(sales.csv) groups df.groupby(部门) wb load_workbook(report_template.xlsx) template_ws wb[月报] first True for dept, sub in groups: if first: ws template_ws # 第一个部门复用模板 Sheet first False else: ws wb.copy_worksheet(template_ws) ws.title dept # Sheet 名用部门名 # 填标题和表头下方数据 ws[B2] f{dept} 销售明细 for i, (_, row) in enumerate(sub.iterrows(), start5): ws.cell(rowi, column2, valuerow[区域]) ws.cell(rowi, column3, valuefloat(row[金额])) ws.cell(rowi, column4, valueint(row[单量])) ws.cell(rowi, column5, valuefloat(row[客单价])) ws.cell(rowi, column6, valuefloat(row[增长率])) # copy_worksheet 不保留列宽手动补一遍 for col_idx in range(2, 7): ws.column_dimensions[get_column_letter(col_idx)].width 14 wb.save(report_by_dept.xlsx)copy_worksheet的行为需要特别注意它复制单元格的值、样式、合并区域但列宽、行高、打印设置、部分条件格式不会带过来。所以上面手动补了列宽。如果模板里列宽很重要更省心的办法是每个部门都load_workbook一次模板虽然多几次 IO但格式绝对一致不会出现「第一个 Sheet 好看、后面几个列宽全乱」的情况。数据量大时优先保证格式一致IO 开销可以接受。3.3 写入时的类型与格式对应表Excel 单元格最终显示成什么样取决于写入的 Python 类型和 number_format 的组合。下面这张表是实际项目里总结出来的对应关系照着设基本不会出显示问题。Python 类型number_format 设置Excel 显示效果常见误用int / float默认 General1234.5金额不加千分位可读性差float#,##0.001,234.50写成字符串导致无法求和datetime.dateyyyy-mm-dd2025-03-01写字符串被当文本无法按月筛选float比率0.00%8.00%直接写 8 会显示 800%str文本001不设文本格式前导零丢失比率那一行是高频翻车点。数据库里存的增长率如果是 0.08 表示 8%写进 Excel 后设0.00%会显示 8.00%但如果数据库存的就是 8那要么在 Python 里除以 100要么 number_format 用0.00%把百分号当文本拼上去。两种做法都行关键是全表统一不能一半除一半不除。3.4 用命名区域让坐标不再硬编码模板改版是常态今天数据从 B5 开始明天业务在表头上面加了一行说明所有坐标全废。缓解办法是在模板里定义命名区域比如把数据起始单元格命名为 data_start代码里通过名称拿坐标模板挪动时只改名称定义代码不动。# 读取模板里定义的命名区域 defined wb.defined_names if data_start in defined: # 解析出目标 Sheet 和坐标 for sheet_name, coord in defined[data_start].destinations: print(sheet_name, coord) # 如 月报 B5openpyxl 对命名区域的支持在版本间有差异读取destinations时返回的是生成器遍历一次就空了需要多次使用就先转成 list。这个技巧不是必须但在模板会频繁调整的项目里能省下大量改坐标的时间。4. 避坑与排查那些让脚本白跑的细节4.1 保存后打开提示「文件已损坏」现象脚本正常跑完用 Excel 打开生成的 xlsx 却提示文件损坏或需要修复。原因通常是往合并单元格的非左上角写了值或者对同一个文件同时用 openpyxl 和 xlwings 读写造成结构冲突。解决先遍历ws.merged_cells.ranges确认写入坐标再检查是否在同一个脚本里混用了两个库操作同一文件。混用时务必一个写完保存关闭另一个再打开。4.2 日期列变成 45000 这样的数字现象明明写的是日期Excel 里显示成一串五位数。原因写入了 datetime 但单元格 number_format 还是 GeneralExcel 按序列号显示。解决写入后立即设cell.number_format yyyy-mm-dd或者写入前把整列格式预设好。注意 1900 和 1904 两种日期系统差异跨平台交换文件时可能差 4 年openpyxl 默认用 1900 系统和 Windows Excel 一致。4.3 公式列被数据覆盖现象模板里 F 列是公式脚本跑完公式没了变成纯数值。原因代码里循环写到了 F 列把公式单元格当数据格覆盖了。解决明确数据区和公式区的边界数据只写到 E 列F 列公式保留。如果确实需要公式结果用 xlwings 写入后app.calculate()再读值而不是用值覆盖公式。4.4 大文件写入内存暴涨现象几万行数据写入时内存占用几个 G甚至被系统杀掉。原因openpyxl 默认把整个工作簿加载到内存且每个 Cell 对象开销不小。解决数据量超过五万行时改用write_onlyTrue模式它按行流式写入内存占用恒定代价是不能随机访问单元格、不能读已有文件。或者把数据拆成多个文件每个文件控制在两万行以内。from openpyxl import Workbook # 只写模式适合大数据量导出内存友好 wb Workbook(write_onlyTrue) ws wb.create_sheet(数据) ws.append([区域, 金额, 单量]) # 表头 for row in big_data_iter: ws.append(row) # 逐行追加不占内存 wb.save(big_report.xlsx)4.5 定时任务里 Excel 进程残留现象用 xlwings 的脚本跑在定时任务里跑几天后服务器上堆了几十个 Excel 进程。原因脚本异常时app.quit()没执行到。解决把app.quit()放进finally块并且给整个任务加超时超时后强制结束进程。更彻底的做法是定时任务用 openpyxl 跑只在必须重算公式时才用 xlwings减少对 Excel 进程的依赖。5. 让模板可验证用断言和快照守住格式底线模板类脚本最怕的不是跑不通而是跑通了但格式悄悄变了人工检查又看不出来。我的习惯是在脚本末尾加一段自检把关键格式属性读出来断言一旦模板被误改脚本直接报错而不是生成一份「看起来对但格式错」的文件。from openpyxl import load_workbook def verify_report(path): wb load_workbook(path) ws wb[月报] # 断言标题单元格有值且字体加粗 assert ws[B2].value, 标题为空 assert ws[B2].font.bold, 标题未加粗 # 断言数据区第一行有值 assert ws[B5].value is not None, 数据区首行为空 # 断言日期格式正确 assert yy in (ws[B3].number_format or ), 日期格式丢失 # 断言合并区域还在 merged [str(r) for r in ws.merged_cells.ranges] assert B2:F2 in merged, 标题合并区域丢失 print(格式自检通过) verify_report(report_202503.xlsx)这段断言的价值在于把「格式要求」变成可执行的检查。业务方改了模板导致合并区域消失脚本会在生成后立刻报错而不是等报表发出去被领导发现。断言不用多覆盖标题、数据起始、日期格式、关键合并区域这四项就够挡住大部分意外。再进一步可以把每次生成的报表关键属性存成 JSON 快照下次生成时对比差异超过阈值就告警。比如记录每个 Sheet 的行数、列数、标题文本、数据区首行值形成一份「报表指纹」。这样即使没人改模板数据源结构变化导致列错位也能通过指纹对比发现。这个做法在数据交付链路里特别有用因为上游改字段名是常事下游往往最后一个知道。最后说个我自己的教训早期做这类脚本时总想一步到位把模板设计得特别复杂结果业务方根本不敢改所有调整都压到我这里。后来改成模板尽量简单、预留区域尽量宽、命名区域标清楚业务方自己就能调格式我只维护数据填充逻辑。模板工具的生命力不在于代码多精巧而在于业务方愿不愿意用、敢不敢改。把边界划清楚比多写两百行格式化代码有用得多。希望帮到你。本文还有配套的精品资源点击获取