Python 玩转 Excel 案例【第 25 期】：Excel模板自动填充神器！公式自动调整

案例说明

上一期我们实现了数据追加功能，但有一个局限：模板中的公式不会被复制到新行。

比如模板中有一条示例数据，F列有公式 =D2*E2。追加新数据后，第3行、第4行...的F列是空的，需要手动拖拽填充，非常麻烦。

本案例解决这个问题：自动识别模板中最后一条数据行的公式，根据行号偏移量动态调整，填充到每个新数据行。

模板文件（模板.xlsx）：

数据文件（销售数据.xlsx）：

（共44行数据，此处省略中间部分）

需求：

1. 1. 将44行新数据追加到模板末尾
2. 2. 自动为每一行填充公式（总价 = 单价 × 数量）
3. 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)

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. 1. re.sub 在 formula 中查找所有匹配 ([A-Z]+)(\d+) 的子串
2. 2. 每找到一个匹配，就创建一个 match 对象
3. 3. 自动调用 replace(match)，将 match 对象作为参数传入
4. 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) 获取捕获组内容：

示例：当匹配到 "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"）

执行过程示例（本例模板）

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条数据）

第九步：保存文件

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案例，即可获取本文完整代|码|及配套练习数据集，一起动手掌握高效数据操作的核心技巧！

❤️ 支持我们

如果本文对你有帮助，欢迎点赞 + 关注 + 点亮小红心，你的支持就是我们持续创作的最大动力～