Python 玩转 Excel 案例【第 20 期】

📌 案例说明

原始数据表（employees.xlsx）：

这是企业人事财务中非常常见的场景：需要为每位员工批量生成个性化的工资条。

原始数据是员工工资明细表，我们需要：

1. 1. 读取 Excel 中的员工工资数据
2. 2. 加载设计好的工资条模板
3. 3. 将每位员工的数据填充到模板的占位符中
4. 4. 为每位员工生成独立的工资条文件

本期案例将跟大家一起学习如何用 Python 批量生成工资条，实现“一人一表，自动填充”。

核心操作：模板占位符替换 + 批量循环生成 + 文件自动保存

• • 关键库：pandas、openpyxl、pathlib
• • 核心逻辑：用 pandas 读取员工数据，用 openpyxl 操作模板并替换占位符

📜 完整代码

"""
工资条批量生成 - 基于模板自动填充每位员工数据
使用 pandas + openpyxl 实现“一人一表”自动生成
"""
import pandas as pd
from openpyxl import load_workbook
from pathlib import Path

def batch_generate():
    # 读取员工数据
    df = pd.read_excel("employees.xlsx")

    # 创建输出文件夹
    output_dir = Path("工资条输出")
    output_dir.mkdir(exist_ok=True)

    # 逐人生成
    for _, row in df.iterrows():
        # 加载模板
        wb = load_workbook("工资条模板.xlsx")
        ws = wb.active

        # 替换占位符
        for row_cells in ws.iter_rows():
            for cell in row_cells:
                if cell.value == "{{姓名}}":
                    cell.value = row["姓名"]
                elif cell.value == "{{工号}}":
                    cell.value = str(row["工号"])
                elif cell.value == "{{部门}}":
                    cell.value = row["部门"]
                elif cell.value == "{{基本工资}}":
                    cell.value = f"{row['基本工资']:,}"
                elif cell.value == "{{绩效奖金}}":
                    cell.value = f"{row['绩效奖金']:,}"
                elif cell.value == "{{扣款}}":
                    cell.value = f"{row['扣款']:,}"

        # 保存
        output_path = output_dir / f"{row['姓名']}_工资条.xlsx"
        wb.save(output_path)

    print(f"生成完成，共 {len(df)} 份")

if __name__ == "__main__":
    batch_generate()

模板文件（工资条模板.xlsx）：

运行结果（生成的文件）：

张三_工资条.xlsx：

李四_工资条.xlsx：

💡 结果分析：每位员工都生成了独立的工资条文件，模板中的 {{占位符}} 被替换为对应的真实数据，数字自动添加了千位分隔符（如 8,000），格式清晰规范。

第一步：导入所需库

import pandas as pd
from openpyxl import load_workbook
from pathlib import Path

代码解释：

• • import pandas as pd
  导入 pandas 库，用于读取 employees.xlsx 中的员工数据。pandas 能自动将 Excel 表格解析为 DataFrame 结构，方便按列名（如 row["姓名"]）获取数据。
• • from openpyxl import load_workbook
  导入 openpyxl 的 load_workbook 函数，用于加载工资条模板文件，并在填充数据后保存新文件。openpyxl 擅长保留 Excel 原有的样式（字体、边框、背景色等），这是 pandas 单独做不到的。
• • from pathlib import Path
  导入 Path 类，用于处理文件夹和文件路径，比传统的 os.path 更简洁直观。

💡 小贴士：pandas 负责“读数据”，openpyxl 负责“写样式”，两者配合是 Excel 自动化处理的黄金组合。

第二步：读取员工数据

df = pd.read_excel("employees.xlsx")

代码解释：

• • pd.read_excel("employees.xlsx")
  读取员工工资表，返回一个 DataFrame 对象。假设文件内容如下：

• • df 的结构：每一行代表一位员工，每一列对应一个字段。后续会通过循环逐行取出数据。。

💡 小贴士：pd.read_excel() 默认读取第一个工作表，如果数据在别的 sheet，可以用 sheet_name="工作表名" 指定。

第三步：创建输出文件夹

output_dir = Path("工资条输出")
output_dir.mkdir(exist_ok=True)

代码解释：

• • Path("工资条输出")
  创建一个 Path 对象，表示“工资条输出”这个文件夹路径。
• • output_dir.mkdir(exist_ok=True)
  创建文件夹。exist_ok=True 的作用是：
• • 如果文件夹不存在 → 创建它
• • 如果文件夹已存在 → 不报错，静默跳过
• • 补充：parents=True 参数
  如果需要创建多级嵌套文件夹，比如 Path("output/2024/工资条输出")，可以加上 parents=True：

output_dir = Path("output/2024/工资条输出")
output_dir.mkdir(exist_ok=True, parents=True)

parents=True 的作用是：如果父级目录（output 和 output/2024）不存在，会一并创建，否则会报 FileNotFoundError。

为什么需要这步？
如果不提前创建文件夹，后续保存文件时如果文件夹不存在，程序会报错 FileNotFoundError。有了这行代码，无论第几次运行都不会出错。如果路径涉及多层目录，记得加上 parents=True。

第四步：遍历每位员工

for _, row in df.iterrows():

这是批量生成的核心——逐行遍历员工数据。

4.1 df.iterrows() 是什么？

iterrows() 是 pandas 遍历行的方法，只遍历数据行，不遍历表头。

for index, row in df.iterrows():
    # index = 0 → 第一条数据（张三）
    # index = 1 → 第二条数据（李四）
    # row["姓名"] → 当前行的姓名

• • index：数据行的编号（从0开始），0不是表头，是第一条员工数据
• • row：当前行数据，用 row["列名"] 取值

4.2 为什么用下划线 _？

for _, row in df.iterrows():  # 只取 row，忽略 index

下划线 _ 是 Python 的惯例写法，表示“这个值我需要接收，但后面不会用到”。因为我们在循环里只需要 row 来获取员工数据，不需要行号 index，所以用 _ 占位。

💡 小贴士：如果不写 _ 而写 for index, row in ...，虽然也能运行，但 index 变量从未被使用，代码检查工具会报警告。

第五步：加载工资条模板

wb = load_workbook("工资条模板.xlsx")
ws = wb.active

代码解释：

• • load_workbook("工资条模板.xlsx")
  加载预先设计好的工资条模板文件，返回一个 Workbook 对象。
• • ws = wb.active
  获取当前活动的工作表。模板中已经设计好了样式（字体、边框、对齐方式等），并预留了 {{姓名}}、{{工号}} 等占位符。

为什么要每次循环都重新加载模板？
因为如果不重新加载，第一次填充后模板中的占位符就被替换成了具体数据，第二次循环时就没有占位符可替换了。每次重新加载相当于拿到一张“全新的空白模板”。

第六步：替换占位符

for row_cells in ws.iter_rows():
    for cell in row_cells:
        if cell.value == "{{姓名}}":
            cell.value = row["姓名"]
        elif cell.value == "{{工号}}":
            cell.value = str(row["工号"])
        # ... 其他占位符类似

6.1 ws.iter_rows() 的作用

iter_rows() 是 openpyxl 提供的方法，用于遍历工作表中的所有行。包括第1行。它会按从上到下的顺序，每次返回一行中的所有单元格。

举个例子： 假设模板长这样

遍历过程如下：

• • 第1次循环：row_cells = (A1, B1)
• • 第2次循环：row_cells = (A2, B2)

然后内层循环 for cell in row_cells 逐个处理每个单元格。

6.2 内层循环：遍历每一行的每个单元格

for row_cells in ws.iter_rows():      # 外层：遍历每一行
    for cell in row_cells:            # 内层：遍历该行的每个单元格
        if cell.value == "{{姓名}}":
            cell.value = row["姓名"]

内层循环的作用：

row_cells 是一行中的所有单元格，比如 (A1, B1, C1, ...)。内层循环 for cell in row_cells 逐个取出这些单元格，检查它的值是不是占位符。

举个例子：

假设模板第3行是：A3 = "姓名："，B3 = "{{姓名}}"

• • 内层循环第1次：cell = A3，cell.value = "姓名："，不是占位符，跳过
• • 内层循环第2次：cell = B3，cell.value = "{{姓名}}"，匹配成功，替换为 row["姓名"]

为什么要两层循环？

因为需要检查模板里的每一个单元格，看看它是不是占位符。外层负责“走到哪一行”，内层负责“检查这一行的每一列”，两层配合才能覆盖整个表格。

6.3 占位符替换逻辑

if cell.value == "{{姓名}}":
    cell.value = row["姓名"]

• • cell.value：当前单元格的内容
• • row["姓名"]：当前员工的姓名

注意： 这里的 row 来自最外层的 for _, row in df.iterrows():，它代表当前正在处理的这位员工。外层循环每获取一位员工（一行数据），内层替换逻辑就会用这位员工的数据把模板里的占位符全部替换一遍。

6.4 数字格式化

cell.value = f"{row['基本工资']:,}"

f"{row['基本工资']:,}" 中的 :, 是 Python 的格式化语法，会自动给数字添加千位分隔符：

• • 8000 → 8,000
• • 12000 → 12,000

第七步：保存文件

output_path = output_dir / f"{row['姓名']}_工资条.xlsx"
wb.save(output_path)

代码解释：

• • output_dir / f"{row['姓名']}_工资条.xlsx"
  使用 / 运算符拼接文件夹路径和文件名。Path 对象会自动适配不同操作系统的路径分隔符（Windows 用 \，Mac/Linux 用 /）。
• • 张三 → 工资条输出/张三_工资条.xlsx
• • 李四 → 工资条输出/李四_工资条.xlsx
• • wb.save(output_path)
  将填充好数据的工作簿保存到指定路径。

第八步：输出完成提示

print(f"生成完成，共 {len(df)} 份")

代码解释：

• • len(df) 获取员工总数
• • 程序运行结束后，在控制台显示成功信息

第九步：df.iterrows() 与 ws.iter_rows()

这两个方法名字很像，但来自不同的库，用法也不同：

代码对比：

# pandas：遍历员工，直接取数据
for _, row in df.iterrows():          # 用 _ 忽略索引
    name = row["姓名"]                 # 直接通过列名取值
    salary = row["基本工资"]

# openpyxl：遍历表格格子，逐个检查
for row_cells in ws.iter_rows():      # row_cells 是一行所有单元格
    for cell in row_cells:            # 逐个检查每个格子
        if cell.value == "{{姓名}}":   # 找到占位符
            cell.value = name          # 替换成员工姓名

核心区别：

• • df.iterrows() 的 row 是一个员工，你可以直接问他“姓名是什么”、“工号是多少”
• • ws.iter_rows() 的 row_cells 是一排格子，你需要一个个打开格子看里面写了什么

📚 本期核心知识点

📍 知识点 1：Path.mkdir(exist_ok=True) 安全创建文件夹

from pathlib import Path
output_dir = Path("工资条输出")
output_dir.mkdir(exist_ok=True)

最佳实践：在保存文件之前，永远先 mkdir(exist_ok=True) 确保父文件夹存在。

📍 知识点 2：df.iterrows() 遍历 DataFrame

for _, row in df.iterrows():
    print(row["姓名"], row["工号"])

• • 返回值：(index, row) 元组
• • row 的类型：pandas Series，支持用列名（字符串）索引
• • 性能注意：iterrows() 在数据量很大（几万行以上）时较慢，但对于工资条生成这种小规模数据完全够用

📍 知识点 3：ws.iter_rows() 遍历工作表

for row_cells in ws.iter_rows():
    for cell in row_cells:
        print(cell.value)

• • 返回值：行生成器，每次迭代返回一个包含该行所有单元格的元组
• • 与 df.iterrows() 的区别：
• • df.iterrows() 返回 2 个值（索引 + 数据行）→ 需要用 _ 忽略索引
• • ws.iter_rows() 返回 1 个值（行的单元格元组）→ 直接接收即可

📍 知识点 4：字符串格式化添加千位分隔符

f"{number:,}"

📍 知识点 5：if __name__ == "__main__": 的作用

if __name__ == "__main__":
    batch_generate()

• • 当直接运行这个 .py 文件时，__name__ 的值为 "__main__"，函数会被执行
• • 当被其他文件 import 时，__name__ 不是 "__main__"，函数不会自动执行
• • 这是 Python 的标准入口写法，让代码既能作为脚本运行，也能作为模块导入

🔄 本案例核心流程

① 读取员工数据 → ② 创建输出文件夹 → ③ 遍历每位员工 → ④ 加载模板 → ⑤ 替换占位符 → ⑥ 保存工资条 → ⑦ 输出完成提示

读取 employees.xlsx (pandas)
        ↓
创建“工资条输出”文件夹 (Path.mkdir)
        ↓
遍历每位员工 (df.iterrows)
        ↓
┌─────────────────────────────┐
│ 对于每位员工：               │
│   加载模板 (load_workbook)   │
│   遍历所有单元格             │
│   找到 {{占位符}} 并替换     │
│   保存为“姓名_工资条.xlsx”   │
└─────────────────────────────┘
        ↓
输出“生成完成，共 N 份”

🗳️ 点单时间到！ 🗳️

下期写什么？
你来定，我来写。

评论区见！👇

📦 资源获取提示

关注「码农自习室」，后台回复关键词 PythonExcel案例，即可获取本文完整代码及配套练习数据集，一起动手掌握高效数据操作的核心技巧！

❤️ 支持我们

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