简介：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 这个方向下，常见有三种落地形态，选哪种取决于你的模板复杂度和团队习惯。

占位符替换是最容易上手的一种：在模板里把要填的位置写成{{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_only=False 保证公式不被求值覆盖 wb = openpyxl.load_workbook(template_path, data_only=False) 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, start=1): ws.cell(row=start_row + i, column=j, value=value) wb.save(output_path) print(f"saved: {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_only=False是关键参数，它保证模板里已有的公式以公式文本形式保留，而不是被上一次计算的值替换。占位符替换阶段遍历全部单元格，用字符串包含判断而不是精确匹配，是为了兼容「本月合计：{{total}} 元」这种标记嵌在句子里的情况。区域追加阶段用ws.max_row + 1定位起始行，前提是模板里没有多余的空行或备注行，否则会写到奇怪的位置。

参数说明：header_map的键要和模板里的占位符名一致，值会被str()转换，所以日期对象、Decimal 这类类型需要提前格式化。rows是二维列表，每个子列表对应一行，列顺序必须和模板表头一致。如果模板有多个 sheet，wb.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="", encoding="utf-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(f"header 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(row=total_row, column=4).value = f"=SUM(D4:D{last_row})"动态重写公式。前者简单但整列求和会包含表头，需要表头是文本才不出错；后者精确但要求脚本知道合计行位置。

4.3 写入后文件打不开或提示修复

现象：脚本跑完没报错，双击文件提示「发现不可读取的内容」，点修复后数据还在但格式乱了。

原因：常见于手动构造了非法的单元格引用，或者写入的值类型 openpyxl 不支持（比如自定义对象），或者模板本身是从其他工具导出、结构不规范。

解决：先用openpyxl.load_workbook打开模板并立即save到一个新文件，看是否报错，排除模板自身问题。写入的值统一过一遍normalize函数。如果仍然出现，改用xlsxwriter从零生成，放弃模板文件，把版式定义写成代码。

4.4 大数据量下内存爆掉

现象：数据量到几万行时，脚本占用内存飙升，机器开始卡。

原因：load_workbook默认把整个工作簿读进内存，append模式虽然省内存，但和模板加载不兼容。

解决：数据量超过一万行时，考虑拆成两个阶段：先用 openpyxl 读模板拿到版式信息，再用openpyxl.Workbook(write_only=True)新建工作簿，手动写表头和格式，逐行append。代价是模板里的复杂格式要代码重建，适合版式简单的场景。另一个选择是xlsxwriter的constant_memory模式，但它同样不支持读模板。

4.5 输出文件被占用导致保存失败

现象：脚本报PermissionError，提示文件被另一个程序占用。

原因：上一次生成的输出文件还在 Excel 里开着，Windows 下文件锁未释放。

解决：输出文件名带时间戳，避免覆盖正在查看的文件。或者在保存前用try/except捕获，提示用户关闭文件后重试。更稳妥的做法是写到临时目录，成功后再移动到目标位置，移动失败时给出明确提示。

5. 进阶技巧：把模板脚本变成可配置的出稿工具

走到这一步，脚本已经能跑通单次填充。但真实团队里，同一个模板往往要出不同维度的报表：按月、按区域、按产品线。如果每次都改代码，脚本就失去了工具的意义。我的习惯是加一层配置，把「用哪个模板、取哪个数据源、输出到哪」全部外置。

import yaml def load_config(path): with open(path, encoding="utf-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 month='2025-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_index=0): df_out = pd.read_excel(output_path, header=None, skiprows=3) 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")

逻辑说明：skiprows=3要按实际模板的表头行数调整。用主键集合比对，能发现漏写和多写。如果主键不唯一，改用行数加合计金额比对。

参数说明：pd.read_excel读大文件慢，验证脚本可以只跑抽样。astype(str)是为了避免数字和文本类型导致的假不匹配。

这套东西做到最后，最大的体会是：模板填充的难点从来不在写单元格，而在「模板会变、数据会脏、人会忘」。我现在的习惯是每接一个新报表需求，先花十分钟确认三件事——模板里有没有合并单元格、公式是整列还是固定区域、数据源的主键是什么。这三件事问清楚，后面基本不会返工。希望帮到你。

本文还有配套的精品资源，点击获取