夜雨聆风Python 玩转 Excel 案例【第 25 期】：Excel模板自动填充神器！公式自动调整
📌 案例说明
上一期我们实现了数据追加功能，但有一个局限：模板中的公式不会被复制到新行。
比如模板中有一条示例数据，F列有公式 =D2*E2。追加新数据后，第3行、第4行...的F列是空的，需要手动拖拽填充，非常麻烦。
本案例解决这个问题：自动识别模板中最后一条数据行的公式，根据行号偏移量动态调整，填充到每个新数据行。
模板文件（模板.xlsx）：
数据文件（销售数据.xlsx）：
（共44行数据，此处省略中间部分）
需求：
1. 将44行新数据追加到模板末尾 2. 自动为每一行填充公式（总价 = 单价 × 数量） 3. 公式中的行号自动调整（如第3行公式为 =D3*E3，第4行为=D4*E4...）
核心操作：
• 从模板中最后一条数据行获取公式作为模板 • 计算行号偏移量 (目标行号 - 公式来源行号)• 用正则表达式替换公式中的所有行号
关键库：pandas、openpyxl、re
📜 完整代码
"""
Excel模板填充工具 - 进阶版（自动填充公式）
功能：追加数据，并自动填充调整行号后的公式
"""
import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
import re
# 配置
TEMPLATE = "模板.xlsx"
DATA_FILE = "销售数据.xlsx"
OUTPUT = "结果_进阶版.xlsx"
def adjust_formula(formula, row_offset):
"""调整公式中的行号（如 D2*E2 → D(2+offset)*E(2+offset)）"""
def replace(match):
col = match.group(1)
row = int(match.group(2))
return f"{col}{row + row_offset}"
return re.sub(r'([A-Z]+)(\d+)', replace, formula)
# 1. 读取数据
df = pd.read_excel(DATA_FILE)
rows = len(df)
print(f"读取数据: {rows}行")
# 2. 加载模板
wb = load_workbook(TEMPLATE)
ws = wb.active
# 3. 从下往上找最后一个有数据的行
START_ROW = 2
for row in range(ws.max_row, START_ROW - 1, -1):
if any(cell.value is not None for cell in ws[row]):
last_row = row
break
else:
last_row = START_ROW - 1
print(f"模板现有数据: 最后一行是第{last_row}行")
# 4. 确定数据写入的起始行
write_start_row = last_row + 1
print(f"新数据将从第{write_start_row}行开始写入")
# 5. 获取列映射（根据表头找到对应的列）
headers = [cell.value for cell in ws[1]]
col_map = {}
for idx, header in enumerate(headers, start=1):
if header in df.columns:
col_map[header] = idx
print(f"列映射: {col_map}")
# 6. 保存模板行的公式（从最后一条数据行获取）
template_formulas = {}
if last_row >= START_ROW:
for j in range(1, len(headers) + 1):
cell = ws.cell(row=last_row, column=j)
if isinstance(cell.value, str) and cell.value.startswith('='):
template_formulas[j] = cell.value
if template_formulas:
print(f"找到公式列: {[get_column_letter(col) for col in template_formulas.keys()]}")
# 7. 追加写入新数据
print("追加写入新数据...")
for i, row in df.iterrows():
target_row = write_start_row + i
for col_name, col_idx in col_map.items():
value = row[col_name]
if not pd.isna(value):
ws.cell(row=target_row, column=col_idx, value=value)
print(f"数据写入完成，共 {rows} 行")
# 8. 填充公式
if template_formulas:
print("填充公式...")
for i in range(rows):
target_row = write_start_row + i
row_offset = target_row - last_row
for col_idx, base_formula in template_formulas.items():
target_cell = ws.cell(row=target_row, column=col_idx)
if target_cell.value is None:
adjusted = adjust_formula(base_formula, row_offset)
target_cell.value = adjusted
print(f"公式填充完成")
# 9. 保存
wb.save(OUTPUT)
print(f"完成！→ {OUTPUT}")
print(f"数据范围: 第{write_start_row}行 到 第{write_start_row + rows - 1}行")运行结果（结果_进阶版.xlsx）：
（共45行数据：原有1行 + 新追加44行，每行F列都有正确的公式）
第一步：导入所需库
import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
import re• pandas：读取数据文件• openpyxl：操作 Excel 文件• get_column_letter：将列号转为字母（如 6→"F"），用于打印调试信息• re：正则表达式，用于匹配和替换公式中的行号
第二步：定义公式调整函数
def adjust_formula(formula, row_offset):
def replace(match):
col = match.group(1)
row = int(match.group(2))
return f"{col}{row + row_offset}"
return re.sub(r'([A-Z]+)(\d+)', replace, formula)作用：将公式中所有的行号增加 row_offset。
执行示例：
adjust_formula("=D2*E2", 1) # 返回 "=D3*E3"
adjust_formula("=D2*E2", 8) # 返回 "=D10*E10"逐行解析
第1行：def adjust_formula(formula, row_offset):
• 定义函数，接收两个参数 • formula：原始公式字符串，如"=D2*E2"• row_offset：行号偏移量，整数，如1
第2行：def replace(match):
• 在函数内部定义另一个函数 replace• 这个函数会被 re.sub调用，处理每个匹配到的结果
第3行：col = match.group(1)
• match是正则匹配到的对象• group(1)返回第一个捕获组的内容，即([A-Z]+)匹配到的列字母• 例如匹配 "D2"时，col = "D"
第4行：row = int(match.group(2))
• group(2)返回第二个捕获组的内容，即(\d+)匹配到的行号• 例如匹配 "D2"时，match.group(2) = "2"• int()将字符串转为整数，方便做加法运算
第5行：return f"{col}{row + row_offset}"
• 返回调整后的单元格引用 • 例如 col="D",row=2,row_offset=1→ 返回"D3"
第6行：return re.sub(r'([A-Z]+)(\d+)', replace, formula)
• re.sub在公式字符串formula中查找所有匹配r'([A-Z]+)(\d+)'的子串• 每找到一个匹配，就创建一个 match对象• 将这个 match对象作为参数，调用replace函数• 用函数的返回值替换原匹配内容
re.sub 函数详解
re.sub 的作用：在一个字符串中查找匹配正则表达式的所有子串，并用指定的内容替换它们。
基本语法：
re.sub(pattern, repl, string)pattern | |
repl | |
string |
repl 参数的两种用法
用法1：字符串替换（固定替换）
import re
result = re.sub(r'\d+', '99', '=D2*E2')
print(result) # "=D99*E99"所有数字都被替换成 "99"。
用法2：函数替换（动态计算）
def add_one(match):
return str(int(match.group(0)) + 1)
result = re.sub(r'\d+', add_one, '=D2*E2')
print(result) # "=D3*E3"每匹配到一个数字，就调用 add_one 函数，用返回值替换原内容。
match 参数的来源
疑问：def replace(match): 中的 match 参数是怎么传进去的？
答案：re.sub 内部自动创建并传入，你不需要手动传参。
执行流程：
1. re.sub在formula中查找所有匹配([A-Z]+)(\d+)的子串2. 每找到一个匹配，就创建一个 match对象3. 自动调用 replace(match)，将match对象作为参数传入4. 用 replace的返回值替换原匹配内容
示意图：
re.sub 扫描 "=D2*E2"
↓
找到 "D2" → 创建 match 对象 → 调用 replace(match) → 返回 "D3"
↓
找到 "E2" → 创建 match 对象 → 调用 replace(match) → 返回 "E3"
↓
返回 "=D3*E3"正则表达式参数详解
re.sub(r'([A-Z]+)(\d+)', replace, formula)正则表达式模式：r'([A-Z]+)(\d+)'
正则表达式各符号含义
• r''：原始字符串前缀，告诉 Python 不要转义字符串中的反斜杠。没有这个前缀，\d中的反斜杠会被 Python 解释为转义字符。• ()：捕获组（Capturing Group）。括号内的匹配结果会被单独保存，可以通过match.group(1)、match.group(2)等获取。• [A-Z]：字符集，匹配一个大写字母（A 到 Z 之间的任意一个）。• +：量词，表示前面的元素出现一次或多次。[A-Z]+表示匹配一个或多个连续的大写字母。• \d：元字符，匹配任意一个数字（0-9），等价于[0-9]。• \d+：匹配一个或多个连续的数字。
捕获组详解
捕获组是什么？
捕获组是用小括号 () 包裹的部分，正则引擎会将括号内匹配到的内容单独保存下来，方便后续提取或使用。
如何区分捕获组？
从左到右数左括号 ( 的位置，第1个 ( 就是第1组，第2个 ( 就是第2组，以此类推。
在 ([A-Z]+)(\d+) 中：
• 第1个 (对应([A-Z]+)→ 第1捕获组，匹配列字母• 第2个 (对应(\d+)→ 第2捕获组，匹配行号
通过 match.group(n) 获取捕获组内容：
match.group(0) | "D2" | |
match.group(1) | "D" | |
match.group(2) | "2" |
示例：当匹配到 "D2" 时
match.group(0) # "D2"
match.group(1) # "D"
match.group(2) # "2"各参数含义
• r'([A-Z]+)(\d+)'：正则表达式模式，用于匹配公式中的单元格引用• replace：替换函数，接收 match 对象，返回替换后的字符串• formula：原始公式字符串，如"=D2*E2"
完整匹配示例
正则表达式: ([A-Z]+)(\d+)
目标字符串: "=D2*E2"
匹配过程:
第1次扫描 → 找到 "D2"
group(1) = "D" (大写字母)
group(2) = "2" (数字)
第2次扫描 → 找到 "E2"
group(1) = "E"
group(2) = "2"完整执行流程图
调用: adjust_formula("=D2*E2", 1)
↓
进入 adjust_formula，定义 replace 函数（此时还未执行）
↓
执行 re.sub(r'([A-Z]+)(\d+)', replace, "=D2*E2")
↓
re.sub 扫描字符串 "=D2*E2"
↓
找到 "D2" → 创建 match 对象（包含 "D2", "D", "2"）
↓
调用 replace(match) ← match 在这里被传入
↓
replace 返回 "D3"
↓
re.sub 将 "D2" 替换为 "D3"，得到 "=D3*E2"
↓
继续扫描，找到 "E2" → 创建 match 对象（包含 "E2", "E", "2"）
↓
调用 replace(match) ← 再次传入
↓
replace 返回 "E3"
↓
re.sub 将 "E2" 替换为 "E3"，得到 "=D3*E3"
↓
返回最终结果一句话总结：你只需要定义 replace(match) 函数接收参数，re.sub 会在每次匹配时自动创建 match 对象并传给它。
第三步：读取数据 & 加载模板
df = pd.read_excel(DATA_FILE)
rows = len(df)
wb = load_workbook(TEMPLATE)
ws = wb.active数据文件结构（44行）：
模板文件结构：
第四步：查找最后一条数据行
START_ROW = 2
for row in range(ws.max_row, START_ROW - 1, -1):
if any(cell.value is not None for cell in ws[row]):
last_row = row
break
else:
last_row = START_ROW - 1
write_start_row = last_row + 1逻辑说明：
• 从最后一行向上查找，找到第一个非空行（即最后一条数据） • last_row = 2（模板第2行有数据）• write_start_row = 3（新数据从第3行开始写入）
输出：
模板现有数据: 最后一行是第2行
新数据将从第3行开始写入第五步：建立列映射
headers = [cell.value for cell in ws[1]]
col_map = {}
for idx, header in enumerate(headers, start=1):
if header in df.columns:
col_map[header] = idx模板表头：["日期", "产品名称", "销售员", "单价", "数量", "总价"]
数据文件列名：["产品名称", "日期", "单价", "销售员", "数量"]
生成的 col_map：
{
"日期": 1, # 模板A列
"产品名称": 2, # 模板B列
"销售员": 3, # 模板C列
"单价": 4, # 模板D列
"数量": 5 # 模板E列
}注意："总价"列不在数据文件中，不会出现在映射中，该列由公式填充。
输出：
列映射: {'日期': 1, '产品名称': 2, '销售员': 3, '单价': 4, '数量': 5}第六步：保存公式模板
template_formulas = {}
if last_row >= START_ROW:
for j in range(1, len(headers) + 1):
cell = ws.cell(row=last_row, column=j)
if isinstance(cell.value, str) and cell.value.startswith('='):
template_formulas[j] = cell.value逐行解析
第1行：template_formulas = {}
• 创建一个空字典，用于存储公式 • 键（key）：列号（整数，如 6 代表 F 列） • 值（value）：公式字符串（如 "=D2*E2"）
第2行：if last_row >= START_ROW:
• 判断是否存在有效的数据行 • last_row：模板中最后一条数据的行号• START_ROW = 2：数据起始行（第1行是表头）• 只有当 last_row >= 2时才执行，即模板至少有一条数据• 如果模板只有表头（ last_row = 1），条件为False，跳过整个代码块
第3行：for j in range(1, len(headers) + 1):
• 遍历所有列， j从 1 到总列数• len(headers)：表头列表的长度，即列数（本例为 6）• 例如 range(1, 7)生成[1, 2, 3, 4, 5, 6]
第4行：cell = ws.cell(row=last_row, column=j)
• 获取指定单元格 • row=last_row：最后一条数据行（本例第2行）• column=j：当前遍历到的列号• 第1次循环：获取 A2 单元格 • 第2次循环：获取 B2 单元格 • ... 第6次循环：获取 F2 单元格
第5行：if isinstance(cell.value, str) and cell.value.startswith('='):
• 判断单元格是否为公式 • isinstance(cell.value, str)：先检查值是否为字符串类型• 数字（如 5000）不是字符串，直接返回 False• 公式（如 "=D2*E2"）是字符串，返回True• cell.value.startswith('=')：再检查字符串是否以=开头（公式一定以=开头）• 两个条件同时满足才是公式
第6行：template_formulas[j] = cell.value
• 将公式存入字典 • 键为列号 j（如 6）• 值为公式字符串（如 "=D2*E2"）
执行过程示例（本例模板）
2024/1/1 | ||||||
手机 | ||||||
张三 | ||||||
5000 | ||||||
5 | ||||||
=D2*E2 |
template_formulas 最终内容
{
6: "=D2*E2"
}输出示例
if template_formulas:
print(f"找到公式列: {[get_column_letter(col) for col in template_formulas.keys()]}")输出：
找到公式列: ['F']第七步：追加写入新数据
for i, row in df.iterrows():
target_row = write_start_row + i
for col_name, col_idx in col_map.items():
value = row[col_name]
if not pd.isna(value):
ws.cell(row=target_row, column=col_idx, value=value)执行过程：
• 第1条数据（充电器）→ target_row=3→ 写入 A3~E3• 第2条数据（智能手表）→ target_row=4→ 写入 A4~E4• ... • 第44条数据 → target_row=46→ 写入 A46~E46
注意：F列（公式列）不在 col_map 中，不会被写入，保持空白。
输出：
追加写入新数据...
数据写入完成，共 44 行第八步：填充公式到新数据行
if template_formulas:
for i in range(rows):
target_row = write_start_row + i
row_offset = target_row - last_row
for col_idx, base_formula in template_formulas.items():
target_cell = ws.cell(row=target_row, column=col_idx)
if target_cell.value is None:
adjusted = adjust_formula(base_formula, row_offset)
target_cell.value = adjusted逐行解析
第1行：if template_formulas:
• 判断字典是否为空 • 如果模板中没有找到任何公式， template_formulas为空字典{}• 空字典在布尔判断中为 False，跳过整个公式填充步骤• 避免无公式时报错或做无意义的操作
第2行：for i in range(rows):
• 遍历每一行新数据 • rows是数据文件的行数（本例为 44）• range(44)生成0, 1, 2, ..., 43• i表示当前处理的是第几条新数据（从0开始）
第3行：target_row = write_start_row + i
• 计算目标行号（公式要写入的行） • write_start_row是新数据写入的起始行（本例为 3）
第4行：row_offset = target_row - last_row
• 计算行号偏移量 • last_row是模板中最后一条数据行的行号（本例为 2）• target_row=3→3 - 2 = 1• target_row=4→4 - 2 = 2• target_row=46→46 - 2 = 44• 这个偏移量表示：目标行比模板行靠后多少行
第5行：for col_idx, base_formula in template_formulas.items():
• 遍历所有需要填充公式的列（本例只有1列） • template_formulas是一个字典，如{6: "=D2*E2"}• .items()返回键值对，每次循环得到(键, 值)• 第1次（也是唯一一次）： col_idx=6，base_formula="=D2*E2"
第6行：target_cell = ws.cell(row=target_row, column=col_idx)
• 定位到目标单元格 • 例如 target_row=3，col_idx=6→ 定位到 F3 单元格• 这个单元格目前是空的（因为写入数据时跳过了公式列）
第7行：if target_cell.value is None:
• 判断目标单元格是否为空 • is None判断值是否为None（空单元格）• 只有空单元格才写入公式 • 避免覆盖已经存在的数据（安全措施）
第8行：adjusted = adjust_formula(base_formula, row_offset)
• 调用公式调整函数 • base_formula是原始公式，如"=D2*E2"• row_offset是计算好的偏移量• 返回调整后的公式，如 "=D3*E3"
第9行：target_cell.value = adjusted
• 将调整后的公式写入目标单元格 • 例如 F3 单元格的值变为 "=D3*E3"
完整执行示例（前3条数据）
=D2*E2 | =D3*E3 | |||||
=D2*E2 | =D4*E4 | |||||
=D2*E2 | =D5*E5 |
第九步：保存文件
wb.save(OUTPUT)
print(f"完成！→ {OUTPUT}")
print(f"数据范围: 第{write_start_row}行 到 第{write_start_row + rows - 1}行")输出：
完成！→ 结果_进阶版.xlsx
数据范围: 第3行 到 第46行📚 核心知识点总结
1. 正则表达式调整公式行号
re.sub(r'([A-Z]+)(\d+)', replace, formula)• ([A-Z]+)匹配列字母• (\d+)匹配行号• 配合替换函数，实现公式中所有行号的批量调整
2. 偏移量计算公式
row_offset = target_row - last_row• last_row是公式来源行（模板中最后一条数据行）• 目标行号与来源行号的差值就是偏移量
3. 公式来源行的选择
if last_row >= START_ROW:
# 从 last_row 获取公式• 从最后一条数据行获取公式，而非固定第2行 • 更灵活：模板可能有多个数据行，以最新的为准 • 更健壮：模板无数据时不会尝试获取公式
4. 只填充空单元格
if target_cell.value is None:
target_cell.value = adjusted• 避免覆盖已写入的数据 • 某些列可能同时有数值需求和公式需求
🔄 执行流程图
读取数据文件 → df, rows
加载模板文件 → wb, ws
查找最后数据行 → last_row
计算写入起始行 → write_start_row = last_row + 1
读取模板表头，建立列映射 → col_map
从 last_row 获取公式 → template_formulas
↓
┌─────────────────────────────────────┐
│ 追加写入新数据 │
│ for 每条数据: │
│ 根据 col_map 写入各列数值 │
│ (公式列保持空白) │
└─────────────────────────────────────┘
↓
┌─────────────────────────────────────┐
│ 填充公式 │
│ for 每个新行: │
│ offset = 当前行号 - last_row │
│ 将原公式中的行号 + offset │
│ 写入公式列 │
└─────────────────────────────────────┘
↓
保存文件 → OUTPUT🗳️ 点单时间到！ 🗳️
下期写什么？
你来定，我来写。
评论区见！👇
📦 资源获取提示
关注「码农自习室」，后台回复关键词 PythonExcel案例，即可获取本文完整代码及配套练习数据集，一起动手掌握高效数据操作的核心技巧！
❤️ 支持我们
如果本文对你有帮助，欢迎点赞 + 关注 + 点亮小红心，你的支持就是我们持续创作的最大动力～
