简介Python-Excel-Template 是一份面向 Python 开发者与数据分析初学者的 Excel 自动化实践资料聚焦用代码高效读写、填充与生成表格模板适合需要批量处理报表、搭建自动化报告流程的中级学习者。资源包共 19 个文件以 10 个 py 脚本为核心搭配 5 个 xlsx 与 3 个 xlsm 模板文件另有 1 个 md 说明文档压缩包约 357KB体积轻便却覆盖了从基础读写到模板复用的完整链路。内容涉及 Pandas 的 DataFrame 读写、OpenPyXL 与 XlsxWriter 的样式和图表控制、xlrd/xlwt 对旧版 xls 的兼容处理以及 jinja2 模板引擎替换占位符生成结构化表格等知识点并延伸至数据清洗、聚合排序、条件格式化与自动化报告等场景。目前已有 543 人学习读者可借助现成脚本与模板快速理解 Python 操作 Excel 的多种技术路线并直接迁移到自己的数据处理任务中。1. 用 Python-Excel-Template 把重复报表做成流水线从手工填表到一键出稿每个月末财务、运营、测试团队里总有人在做同一件事打开一个固定格式的 Excel 模板把数据库或接口里的数据一行行贴进去调格式、改公式、另存为再发给下一环。这种活干一次两次还行干到第十次就会开始怀疑人生。Python-Excel-Template 这个方向要解决的正是这件事——把「模板文件」和「数据填充逻辑」拆开让 Python 负责取数、算数、写数让 Excel 只负责它最擅长的展示和公式。它适合两类人一类是手里已经有稳定格式的周报月报、对账单、测试用例表想用脚本替代手工另一类是刚学完 openpyxl 或 pandas 基础却不知道怎么把零散代码组织成可复用模板的开发者。核心思路不复杂模板是壳数据是肉脚本是刀三者解耦之后换数据不用改模板改模板不用动脚本。2. 模板与数据分离Python-Excel-Template 的三种落地形态2.1 为什么不能直接把数据写进模板文件最常见的翻车方式是拿一个已经填好数据的 Excel 当模板用脚本打开、清空、再写入。看起来省事实际上埋了三个雷。第一清空范围难界定合并单元格、隐藏行、条件格式的残留会让下一次输出出现幽灵数据。第二模板里往往有公式引用固定区域一旦行数变化公式要么错位要么失效。第三多人协作时模板文件被反复覆盖版本管理基本靠文件名加日期回滚成本极高。正确的做法是把模板当成「只读的版式定义」数据写入发生在内存或临时副本里。Python-Excel-Template 这个方向下常见有三种落地形态选哪种取决于你的模板复杂度和团队习惯。形态适用场景代表工具模板是否含公式占位符替换表头固定、行数不定openpyxl 自定义标记少量区域追加明细表、流水账openpyxl 的 append通常无模板引擎渲染多 sheet、复杂版式xlwings / xlsxwriter较多占位符替换是最容易上手的一种在模板里把要填的位置写成{{name}}、{{total}}这样的标记脚本读模板、找标记、替换值。它的边界是只能处理「一个萝卜一个坑」的场景遇到需要动态增行的明细就力不从心。区域追加适合明细但要求模板的表头和数据区严格分离。模板引擎渲染最灵活代价是引入额外依赖且对 Excel 公式的保留不如 openpyxl 直接。2.2 用 openpyxl 搭一个最小可用的模板填充脚本下面这段代码演示占位符替换加区域追加的组合用法。假设模板文件template.xlsx的 A1 是标题占位符第 3 行是明细表头从第 4 行开始写数据。import openpyxl from openpyxl.utils import get_column_letter def fill_template(template_path, output_path, header_map, rows): # 加载模板data_onlyFalse 保证公式不被求值覆盖 wb openpyxl.load_workbook(template_path, data_onlyFalse) ws wb.active # 第一步占位符替换遍历所有单元格找 {{key}} for row in ws.iter_rows(): for cell in row: if isinstance(cell.value, str) and {{ in cell.value: for key, val in header_map.items(): placeholder {{ key }} if placeholder in cell.value: cell.value cell.value.replace(placeholder, str(val)) # 第二步定位明细起始行从表头下一行开始追加 start_row ws.max_row 1 for i, record in enumerate(rows): for j, value in enumerate(record, start1): ws.cell(rowstart_row i, columnj, valuevalue) wb.save(output_path) print(fsaved: {output_path}) if __name__ __main__: header {title: 月度对账单, date: 2025-01} data [ [A001, 服务费, 1200.00], [A002, 维护费, 800.50], ] fill_template(template.xlsx, output.xlsx, header, data)逻辑说明load_workbook的data_onlyFalse是关键参数它保证模板里已有的公式以公式文本形式保留而不是被上一次计算的值替换。占位符替换阶段遍历全部单元格用字符串包含判断而不是精确匹配是为了兼容「本月合计{{total}} 元」这种标记嵌在句子里的情况。区域追加阶段用ws.max_row 1定位起始行前提是模板里没有多余的空行或备注行否则会写到奇怪的位置。参数说明header_map的键要和模板里的占位符名一致值会被str()转换所以日期对象、Decimal 这类类型需要提前格式化。rows是二维列表每个子列表对应一行列顺序必须和模板表头一致。如果模板有多个 sheetwb.active只取第一个多 sheet 场景要显式指定wb[SheetName]。2.3 模板文件该放哪、怎么管版本模板文件不要和脚本混在同一个目录里靠文件名区分。我一般会建一个templates/目录里面按业务线分子目录文件名带版本号比如recon_v1.xlsx、recon_v2.xlsx。脚本里通过配置项指定用哪个版本而不是硬编码路径。这样改版式时新增文件即可旧脚本不受影响。版本管理上Excel 是二进制文件git diff 看不出改了什么。一个实用习惯是每次改模板在文件里加一个隐藏 sheet 叫_changelog记录日期和改动点。脚本读取模板时不碰这个 sheet但人翻模板时能一眼看到历史。这个做法不优雅但比在群里问「谁改了模板」高效得多。3. 数据源接入与类型处理让脚本吃得下真实数据3.1 从 CSV、数据库、接口取数的统一入口模板填充脚本写好后下一个问题是数据从哪来。真实场景里数据源至少有三类CSV 文件、数据库查询结果、HTTP 接口返回的 JSON。如果每个数据源都写一套填充逻辑脚本会迅速膨胀。常见做法是加一层适配器把不同来源统一转成「表头 二维列表」的结构再交给填充函数。import csv import json import sqlite3 def from_csv(path): with open(path, newline, encodingutf-8) as f: reader csv.reader(f) rows list(reader) return rows[0], rows[1:] def from_sqlite(db_path, sql): conn sqlite3.connect(db_path) cur conn.execute(sql) columns [d[0] for d in cur.description] rows [list(r) for r in cur.fetchall()] conn.close() return columns, rows def from_json_api(payload): # 假设接口返回 {columns: [...], data: [[...], ...]} obj json.loads(payload) return obj[columns], obj[data]逻辑说明三个函数返回的都是(columns, rows)二元组columns 用于校验模板表头是否匹配rows 直接喂给填充函数。from_csv用csv.reader而不是 pandas是为了避免引入重依赖小数据量下够用。from_sqlite用cur.description拿列名这是 DB-API 的标准行为换 MySQL、PostgreSQL 的驱动也适用。from_json_api里假设了固定的返回结构实际接口字段名不同改这一处即可。参数说明CSV 的encoding要按实际文件调整Windows 导出的 CSV 常见gbk。SQLite 的 SQL 里如果带参数要用占位符而不是字符串拼接。JSON 解析失败时json.loads会抛异常生产脚本里应该包一层 try 并记录原始 payload。3.2 日期、金额、空值的处理边界数据从数据库出来是datetime对象写进 Excel 后可能变成一串数字金额从接口来是字符串1,200.00直接写会变成文本左对齐空值None写进去是空白但NaN写进去可能报错。这些类型问题不处理输出文件看着能用一算就错。日期建议统一转成datetime.date或datetime.datetime再写入openpyxl 会自动识别为日期类型配合单元格的number_format显示成yyyy-mm-dd。金额用Decimal或float写入后设置number_format #,##0.00。空值统一转成空字符串或None不要留NaN。from datetime import datetime from decimal import Decimal def normalize(value): if value is None: return if isinstance(value, float) and value ! value: # NaN 判断 return if isinstance(value, str): stripped value.strip().replace(,, ) try: return Decimal(stripped) except Exception: return value return value逻辑说明value ! value是判断NaN的经典写法因为NaN不等于自身。字符串先去掉千分位逗号再尝试转Decimal转失败就原样返回避免把「待确认」这种文本误转。这个函数在写入每个单元格前调用一次成本可接受。参数说明Decimal比float更适合金额但 openpyxl 写入Decimal时部分版本会报警告可以先转float再写精度损失在两位小数场景下可忽略。如果对精度要求极高改用xlsxwriter的write_number并显式指定格式。3.3 表头校验别让列错位悄悄发生数据源的列顺序和模板表头不一致是比类型错误更隐蔽的坑。脚本跑完不报错打开文件发现「金额」列里是日期「备注」列里是编号。解决办法是在填充前做一次表头比对不一致就中止并打印差异。def check_header(template_header, source_header): t [h.strip() for h in template_header] s [h.strip() for h in source_header] if t ! s: missing set(t) - set(s) extra set(s) - set(t) raise ValueError(fheader mismatch, missing{missing}, extra{extra})逻辑说明用集合差集给出缺失列和多余列比单纯报「不匹配」更有助于排查。如果模板表头是中文、数据源是英文需要维护一张映射表在调用前把 source_header 转成中文再比对。参数说明strip()是为了容忍表头里的空格差异。如果模板表头有合并单元格iter_rows读到的可能是None需要单独处理合并区域的取值逻辑这部分在下一章的避坑里展开。4. 避坑与排查模板填充脚本最常见的五类翻车4.1 合并单元格导致占位符读不到现象模板里 A1:C1 合并占位符写在合并区域脚本遍历时只有 A1 有值B1、C1 是None替换逻辑正常但后续定位出错。原因openpyxl 读取合并单元格时只有左上角单元格持有值其余为None。ws.max_row和ws.max_column也会受合并区域影响。解决替换阶段用ws.merged_cells.ranges拿到所有合并区域对每个区域取左上角单元格的值做替换写回时也只写左上角。定位明细起始行时不要依赖max_row改为显式配置一个data_start_row参数由模板维护者指定。4.2 公式被覆盖成静态值现象模板里合计行是SUM(D4:D10)脚本追加数据后公式还在但求和范围没变新数据没被算进去。原因openpyxl 不会自动扩展公式范围它只保留公式文本。追加行后公式引用的区域还是旧的。解决两种思路。一是模板里公式用整列引用比如SUM(D:D)追加多少行都能覆盖。二是脚本在追加后用ws.cell(rowtotal_row, column4).value fSUM(D4:D{last_row})动态重写公式。前者简单但整列求和会包含表头需要表头是文本才不出错后者精确但要求脚本知道合计行位置。4.3 写入后文件打不开或提示修复现象脚本跑完没报错双击文件提示「发现不可读取的内容」点修复后数据还在但格式乱了。原因常见于手动构造了非法的单元格引用或者写入的值类型 openpyxl 不支持比如自定义对象或者模板本身是从其他工具导出、结构不规范。解决先用openpyxl.load_workbook打开模板并立即save到一个新文件看是否报错排除模板自身问题。写入的值统一过一遍normalize函数。如果仍然出现改用xlsxwriter从零生成放弃模板文件把版式定义写成代码。4.4 大数据量下内存爆掉现象数据量到几万行时脚本占用内存飙升机器开始卡。原因load_workbook默认把整个工作簿读进内存append模式虽然省内存但和模板加载不兼容。解决数据量超过一万行时考虑拆成两个阶段先用 openpyxl 读模板拿到版式信息再用openpyxl.Workbook(write_onlyTrue)新建工作簿手动写表头和格式逐行append。代价是模板里的复杂格式要代码重建适合版式简单的场景。另一个选择是xlsxwriter的constant_memory模式但它同样不支持读模板。4.5 输出文件被占用导致保存失败现象脚本报PermissionError提示文件被另一个程序占用。原因上一次生成的输出文件还在 Excel 里开着Windows 下文件锁未释放。解决输出文件名带时间戳避免覆盖正在查看的文件。或者在保存前用try/except捕获提示用户关闭文件后重试。更稳妥的做法是写到临时目录成功后再移动到目标位置移动失败时给出明确提示。5. 进阶技巧把模板脚本变成可配置的出稿工具走到这一步脚本已经能跑通单次填充。但真实团队里同一个模板往往要出不同维度的报表按月、按区域、按产品线。如果每次都改代码脚本就失去了工具的意义。我的习惯是加一层配置把「用哪个模板、取哪个数据源、输出到哪」全部外置。import yaml def load_config(path): with open(path, encodingutf-8) as f: return yaml.safe_load(f) # config.yaml 示例 # template: templates/recon_v2.xlsx # source: # type: sqlite # path: data.db # sql: SELECT code, item, amount FROM bills WHERE month2025-01 # output: out/recon_2025-01.xlsx逻辑说明配置用 YAML 而不是 JSON因为 YAML 支持注释模板维护者能写备注。source.type决定调用哪个适配器函数用一个字典做分发即可。输出路径支持日期占位符比如out/recon_{month}.xlsx运行时替换。参数说明YAML 的缩进敏感建议用编辑器校验。SQL 写在配置里方便改但要注意不要拼接用户输入避免注入。如果配置里需要引用环境变量用${VAR}形式并在加载后替换。验证输出是否正确我一般用三步第一步打开文件看行数是否和源数据一致第二步抽查合计行公式是否覆盖了全部明细第三步用 pandas 读回输出文件和源数据做一次merge比对确认没有错位。第三步最费事但最可靠尤其是列多的时候。import pandas as pd def verify(source_rows, output_path, key_index0): df_out pd.read_excel(output_path, headerNone, skiprows3) out_keys set(df_out.iloc[:, key_index].astype(str)) src_keys set(str(r[key_index]) for r in source_rows) if out_keys ! src_keys: print(mismatch:, src_keys - out_keys, out_keys - src_keys) else: print(verify passed)逻辑说明skiprows3要按实际模板的表头行数调整。用主键集合比对能发现漏写和多写。如果主键不唯一改用行数加合计金额比对。参数说明pd.read_excel读大文件慢验证脚本可以只跑抽样。astype(str)是为了避免数字和文本类型导致的假不匹配。这套东西做到最后最大的体会是模板填充的难点从来不在写单元格而在「模板会变、数据会脏、人会忘」。我现在的习惯是每接一个新报表需求先花十分钟确认三件事——模板里有没有合并单元格、公式是整列还是固定区域、数据源的主键是什么。这三件事问清楚后面基本不会返工。希望帮到你。本文还有配套的精品资源点击获取