Python 玩转 Excel 案例【第 23 期】：Excel模板自动填充神器！保留公式，一键生成报表
📌 案例说明
模板文件（模板.xlsx）：
数据文件（销售数据.xlsx）：
需求：把新数据填入模板，同时保留模板中的所有公式（E列和合计行）。
核心操作：判断单元格是否为公式 → 只覆盖普通单元格 → 公式单元格跳过
关键库：pandas、openpyxl
📜 完整代码
"""
Excel模板填充工具 - 基础版
功能：向模板写入数据，保留原有公式
适用：数据行数不超过模板已有的行数
"""
import pandas as pd
from openpyxl import load_workbook
# 配置
TEMPLATE = "模板.xlsx"
DATA_FILE = "销售数据.xlsx"
OUTPUT = "结果_基础版.xlsx"
# 1. 读取数据
df = pd.read_excel(DATA_FILE)
rows, cols = len(df), len(df.columns)
print(f"读取数据: {rows}行")
# 2. 加载模板
wb = load_workbook(TEMPLATE)
ws = wb.active
# 3. 清理数据区（保留公式单元格）
print("清理数据区（保留公式）...")
for i in range(rows):
for j in range(cols):
cell = ws.cell(row=3+i, column=2+j)
# 只有不是公式的单元格才清空
if not (isinstance(cell.value, str) and cell.value.startswith('=')):
cell.value = None
# 4. 写入新数据
print("写入新数据...")
for i, row in enumerate(df.values):
for j, value in enumerate(row):
cell = ws.cell(row=3+i, column=2+j)
# 再次检查：不覆盖公式
if not (isinstance(cell.value, str) and cell.value.startswith('=')):
if not pd.isna(value):
cell.value = value
# 5. 保存
wb.save(OUTPUT)
print(f"完成！公式已保留 → {OUTPUT}")
运行结果（结果_基础版.xlsx）：
💡 结果：模板中的公式全部保留，新数据填入后 Excel 会自动重新计算。
第一步：导入所需库
import pandas as pd
from openpyxl import load_workbook
库说明：
第二步：读取数据
df = pd.read_excel(DATA_FILE)
rows, cols = len(df), len(df.columns)
功能说明：
- •
pd.read_excel()
读取 Excel 文件，返回 DataFrame 对象 - •
len(df)
获取 DataFrame 的行数（数据条数） - •
len(df.columns)
获取 DataFrame 的列数（字段数量）
输出结果：
数据行数: 5
数据列数: 3
参数默认值解析
pd.read_excel() 的 header 参数默认值为 0：
# 以下两行代码等价
df = pd.read_excel("销售数据.xlsx")
df = pd.read_excel("销售数据.xlsx", header=0)
header=0 表示将 Excel 文件的第1行（索引0）作为 DataFrame 的列名，该行不纳入数据统计。
参数变更的影响
若需将 Excel 第1行也作为数据处理，可设置 header=None：
df = pd.read_excel("销售数据.xlsx", header=None)
print(len(df)) # 输出：6
此时 header=None 表示"没有表头行"，Excel 的第1行就会变成 df 的第0行数据。
第三步：加载模板
wb = load_workbook(TEMPLATE)
ws = wb.active
对象说明：
- •
wb
Workbook 对象，代表整个 Excel 文件（工作簿），包含所有 Sheet 工作表 - •
ws
Worksheet 对象，代表当前激活的工作表（可通过 wb.active 获取）
第四步：清理数据区（保留公式）
for i in range(rows):
for j in range(cols):
cell = ws.cell(row=3+i, column=2+j)
if not (isinstance(cell.value, str) and cell.value.startswith('=')):
cell.value = None
为什么要定位到 row=3+i, column=2+j？
一是两个库的索引规则存在差异：
- •
pandas
索引起始值为 0，df.iloc[0, 0] 表示第一行第一列 - •
openpyxl
索引起始值为 1，ws.cell(row=1, column=1) 表示 A1 单元格
这意味着：pandas 的索引 i=0, j=0 需要转换成 openpyxl 的行列号。
二是由模板结构决定的：
数据填充的起始位置是：第3行、第2列（B3）。
转换公式：
openpyxl行号 = 起始行号 + pandas行索引
openpyxl列号 = 起始列号 + pandas列索引
row = 3 + i
column = 2 + j
完整映射示例：
pandas (i=0, j=0) → openpyxl (row=3, col=2) → B3 单元格 → 产品名称
pandas (i=0, j=1) → openpyxl (row=3, col=3) → C3 单元格 → 销量
pandas (i=0, j=2) → openpyxl (row=3, col=4) → D3 单元格 → 单价
pandas (i=1, j=0) → openpyxl (row=4, col=2) → B4 单元格 → 产品名称
pandas (i=1, j=1) → openpyxl (row=4, col=3) → C4 单元格 → 销量
pandas (i=1, j=2) → openpyxl (row=4, col=4) → D4 单元格 → 单价
如何判断公式所在单元格？
isinstance(cell.value, str) and cell.value.startswith('=')
为什么这样写？
直接调用 cell.value.startswith('=') 不安全，因为数字类型的值没有 startswith 方法，会报错。Python 的 and 具有短路逻辑：先判断左边，只有当左边为 True 时才会执行右边。
执行顺序：
- • 先执行
isinstance(cell.value, str)，判断是否为字符串 - • 如果值为数字或空（左边为
False），表达式直接返回 False，右边的 startswith 不会执行，从而避免报错 - • 只有当值为字符串时，才继续执行
cell.value.startswith('=')，判断是否以 = 开头
判断结果：
4.3 not 取反
if not (是公式的条件):
cell.value = None # 不是公式就清空
第五步：写入新数据
for i, row in enumerate(df.values):
for j, value in enumerate(row):
cell = ws.cell(row=3+i, column=2+j)
if not (isinstance(cell.value, str) and cell.value.startswith('=')):
if not pd.isna(value):
cell.value = value
df.values 只取数据
print(df.values)
# [['产品A' 120 55]
# ['产品B' 180 28]
# ...]
不包含列名，只返回纯数据。
enumerate() 获取索引
for i, row in enumerate(df.values):
for j, value in enumerate(row):
# 处理每个单元格
enumerate() 的作用：遍历时自动生成索引，无需手动维护计数器。
第一层循环 for i, row in enumerate(df.values)：
df.values 的内容：
[['产品A', 120, 55],
['产品B', 180, 28],
['产品C', 95, 110]]
循环过程：
- • 第1次：
i = 0，row = ['产品A', 120, 55] - • 第2次：
i = 1，row = ['产品B', 180, 28] - • 第3次：
i = 2，row = ['产品C', 95, 110]
第二层循环 for j, value in enumerate(row)：
当 row = ['产品A', 120, 55] 时：
- • 第1次：
j = 0，value = '产品A'
总结：
| | |
|---|
i | | |
row | | ['产品A', 120, 55] |
j | | |
value | | '产品A' |
为什么再次检查公式？
双重保险，确保公式单元格绝对不被覆盖。
pd.isna() 判断空值
if not pd.isna(value): # 不是空值才写入
cell.value = value
Excel 空单元格读取后是 NaN，需要特殊处理。
第六步：保存文件
wb.save(OUTPUT)
print(f"完成！公式已保留 → {OUTPUT}")
wb.save() 的作用：
将 Workbook 对象写入磁盘文件。执行后，所有对模板的修改（清理旧数据、写入新数据）都会被保存到输出文件中。
注意：openpyxl 保存时：
- • 公式以字符串形式保留（如
=C3*D3），不会自动计算 - • 打开 Excel 文件时，软件会自动重新计算公式并显示结果
📚 核心知识点总结
1. 判断公式单元格
def is_formula(cell):
return isinstance(cell.value, str) and cell.value.startswith('=')
- •
isinstance(cell.value, str)：先确认值是字符串类型，避免数字调用 startswith 报错 - •
cell.value.startswith('=')：再确认字符串以 = 开头（Excel 公式的特征） - • Python 的
and 具有短路逻辑：左边为 False 时右边不执行，天然安全
2. 行列索引对齐
- • pandas 索引从
0 开始，openpyxl 行列从 1 开始 - • 数据从模板第3行开始填充 →
row = 3 + i - • 数据从模板第2列（B列）开始填充 →
column = 2 + j - • 原因：第1行是标题，第2行是表头，第1列是序号（数据文件中没有）
3. pandas 读取规则
- •
header=0（默认值）：Excel 第1行作为列名，不计入数据行 - •
header=None：不设列名，Excel 第1行也作为数据，列名变为 0, 1, 2...
4. df.values 的作用
- •
df.values：返回纯数据数组（numpy.ndarray），不包含列名和行索引 - • 模板填充只需要纯数据，所以用
df.values
5. 空值判断
pd.isna(value) # 判断是否为 NaN（空值）
- • 为什么不用
if value:？因为数字 0 会被误判为空值 - •
pd.isna() 能正确识别 None、NaN、NaT 等空值类型，且不会误判 0
⚠️ 注意事项
- 1. 数据行数 ≤ 模板预留行数：本案例不处理超出范围的情况
- 2. 公式单元格不会被覆盖：这是通过判断
startswith('=') 实现的 - 3. openpyxl 不会自动计算公式：保存后打开 Excel 才会自动计算
🔄 核心流程图
读取数据 (pandas)
↓
加载模板 (openpyxl)
↓
定位数据区 (第3行, 第2列)
↓
清理阶段：是公式？→ 保留 / 不是公式？→ 清空
↓
写入阶段：遍历新数据，再次检查公式，非空值写入
↓
保存文件
↓
打开 Excel，公式自动计算 ✓
🗳️ 点单时间到！ 🗳️
下期写什么？
你来定，我来写。
📦 资源获取提示
关注「码农自习室」，后台回复关键词 PythonExcel案例，即可获取本文完整代码及配套练习数据集，一起动手掌握高效数据操作的核心技巧！
❤️ 支持我们
如果本文对你有帮助，欢迎点赞 + 关注 + 点亮小红心，你的支持就是我们持续创作的最大动力～
