Python 玩转 Excel 案例【第 24 期】：Excel数据追加，保留原始数据

📌 案例说明

在实际工作中，我们经常需要定期向同一个Excel模板追加新数据，而不是每次都从头覆盖。比如：

• • 每月销售数据汇总
• • 每日日志记录追加
• • 多批次数据合并

本案例演示如何自动识别模板中已有的数据行，将新数据追加到末尾，同时保持列映射的灵活性（数据文件的列顺序可以与模板不同）。

模板文件（模板.xlsx）：

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

需求：将数据文件中的44行数据追加到模板末尾，通过表头匹配自动对应列，不管数据文件的列顺序如何。

核心操作：

1. 1. 自动查找模板最后一个有数据的行
2. 2. 根据表头名称建立列映射（而非固定列位置）
3. 3. 从空行开始追加写入新数据

关键库：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 = 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. 追加写入新数据
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} 行")

# 7. 保存
wb.save(OUTPUT)
print(f"完成！→ {OUTPUT}")
print(f"追加数据范围: 第{write_start_row}行 到 第{write_start_row + rows - 1}行")

运行结果（追加数据后.xlsx）：

💡 结果：模板原有的1行数据被保留，新追加的44行数据从第3行开始写入，共计45行数据。

第一步：导入所需库

import pandas as pd
from openpyxl import load_workbook

库说明：

• • pandas
  读取数据文件，处理列映射
• • openpyxl
  操作模板，支持读写Excel文件

第二步：读取数据

df = pd.read_excel(DATA_FILE)
rows = len(df)

功能说明：

• • pd.read_excel()
    读取 Excel 文件，返回 DataFrame 对象
• • len(df)
    获取 DataFrame 的行数（数据条数）

输出结果：

读取数据: 44行

数据文件结构

数据文件的列顺序与模板不同：

这正是本案例需要列映射的原因——数据文件的列顺序是任意的，必须通过表头名称进行匹配。

第三步：加载模板

wb = load_workbook(TEMPLATE)
ws = wb.active

代码解析：

• • load_workbook(TEMPLATE)
  打开指定路径的 Excel 文件，返回 Workbook 对象（代表整个工作簿）
• • wb.active
  获取当前激活的工作表，返回 Worksheet 对象（代表具体的 Sheet）

Workbook 对象常用操作：

• • wb.sheetnames
  获取所有工作表名称列表
• • wb["Sheet1"]
  通过名称获取指定工作表
• • wb.save(OUTPUT)
  保存工作簿到文件

Worksheet 对象常用操作：

• • ws.title：获取或修改工作表名称
• • ws.max_row：获取有数据的最大行号
• • ws.max_column：获取有数据的最大列号
• • ws[row]：获取某行的所有单元格
• • ws.cell(row, column)：获取指定单元格

模板结构：

• • 第1行：表头行，用于列映射
• • 第2行：已有数据
• • 第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

为什么要从下往上找？

Excel 工作表可能存在空单元格夹杂在数据中间的情况。从最后一行倒序查找，第一个遇到的非空行就是真正的最后一条数据所在行。

倒序查找逻辑：

列号:   1    2    3    4    5
       A    B    C    D    E
行号: ┌────┬────┬────┬────┬────┐
1    │日期│产品│销售│单价│数量│ ← 表头行（跳过）
     ├────┼────┼────┼────┼────┤
2    │1/1 │手机│张三│5000│ 5  │ ← 有数据（last_row = 2）
     ├────┼────┼────┼────┼────┤
3    │    │    │    │    │    │ ← 空行
     ├────┼────┼────┼────┼────┤
4    │    │    │    │    │    │ ← 空行
     └────┴────┴────┴────┴────┘

遍历过程：

any() 函数的作用

any(cell.value is not None for cell in ws[row])

拆解分析：

• • ws[row]：获取第 row 行的所有单元格，返回一个元组，如 (A2单元格, B2单元格, C2单元格, D2单元格, E2单元格)
• • for cell in ws[row]：遍历这行中的每一个单元格对象
• • cell.value is not None：判断单元格的值是否不为空（None）
• • 外层 any()：对遍历产生的多个布尔值进行"或"运算

any() 的执行逻辑：

# 假设第2行的值
values = ["2024/1/1", "手机", "张三", 5000, 5]
# any() 等价于：
result = (values[0] is not None) or (values[1] is not None) or ... or (values[4] is not None)
# 结果：True

短路特性：

# any() 从左到右检查，遇到第一个 True 就立即返回 True，不再继续检查
any([False, False, True, 停止检查, 不再执行])

一句话总结：any(cell.value is not None for cell in ws[row]) 判断该行是否至少有一个非空单元格。只要有一个单元格有值，就认为这行有数据。

range(ws.max_row, START_ROW - 1, -1) 参数详解

range(ws.max_row, START_ROW - 1, -1)

参数说明：

• • start = ws.max_row：从最大行号开始（如1048576）
• • stop = START_ROW - 1：在 START_ROW - 1 处停止（不包含）
• • step = -1：每次减1，倒序遍历

range() 的左闭右开特性：

range(start, stop, step) 生成的序列包含 start，不包含 stop。

当 START_ROW = 2 时：

• • stop = 2 - 1 = 1
• • 生成的序列：max_row, max_row-1, max_row-2, ..., 3, 2
• • 包含第 max_row 行，包含第 2 行，不包含第 1 行

为什么要设置 stop = 1？

因为要排除第1行（表头行），即使表头有值也不能作为数据行处理。设置 stop = 1 后，循环到第2行就结束，第1行被排除在外。

一句话总结：range(max_row, START_ROW-1, -1) 从最后一行倒序遍历到第2行（含），不包含第1行。

for...else 结构解析

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

核心规则（两种情况）：

• • if条件满足，break 跳出整个 for 循环，同时跳过else 子句
• • 循环正常结束（没有被 break 中断）时，执行 else 子句

Python 特有语法：for 和 while 都可以跟 else，这是 C、Java、JavaScript 中没有的特性。

场景分析：

示例：模板只有表头，无数据行

• • 循环遍历每一行，全部为空 → if 条件始终为 False → 永远不会执行 break
• • 循环正常结束后，进入 else 子句，设置 last_row = 1
• • 后续计算 write_start_row = last_row + 1 = 2，数据从第2行开始写入 ✅

第五步：确定数据写入起始行

write_start_row = last_row + 1
print(f"新数据将从第{write_start_row}行开始写入")

计算公式：

写入起始行 = 最后数据行 + 1

输出示例：

模板现有数据: 最后一行是第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

为什么要建立列映射？

模板和数据文件的列顺序可能不同：

如果按固定位置写入（第1列写第1列），数据就会错位。通过表头名称匹配，可以确保：

• • 模板的"产品名称"列 ← 数据文件的"产品名称"列
• • 模板的"销售员"列 ← 数据文件的"销售员"列

enumerate(headers, start=1) 的作用

headers = ["日期", "产品名称", "销售员", "单价", "数量"]

enumerate(headers, start=1) 生成：

start=1 让索引从1开始，直接对应 Excel 的列号（A=1, B=2...）。

列映射结果

col_map = {
    "日期": 1,      # 模板A列
    "产品名称": 2,   # 模板B列
    "销售员": 3,     # 模板C列
    "单价": 4,       # 模板D列
    "数量": 5        # 模板E列
}

输出示例：

列映射: {'日期': 1, '产品名称': 2, '销售员': 3, '单价': 4, '数量': 5}

if header in df.columns 的作用

只映射同时在模板和数据文件中都存在的列，避免：

• • 模板中的列在数据文件中不存在 → 跳过，不会报错
• • 数据文件中的列在模板中不存在 → 不会出现在映射中，不会被写入

第七步：追加写入新数据

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)

df.iterrows() 逐行遍历

for i, row in df.iterrows():

iterrows() 的作用：遍历 DataFrame 的每一行，返回 (索引, 行数据)。row 是一个 Series 对象，可以像字典一样通过列名取值。

示例：

取值方式：

• • row["列名"]（推荐）
• • row.列名（也支持，但不推荐，容易与 Series 方法冲突）

目标行号计算

target_row = write_start_row + i

当 write_start_row = 3 时：

col_map.items() 遍历解析

for col_name, col_idx in col_map.items():
    value = row[col_name]

col_map 的内容：

col_map = {
    "日期": 1,
    "产品名称": 2,
    "销售员": 3,
    "单价": 4,
    "数量": 5
}

col_map.items() 的作用：

返回字典的键值对视图，每个元素是一个元组 (键, 值)。

list(col_map.items())
# [("日期", 1), ("产品名称", 2), ("销售员", 3), ("单价", 4), ("数量", 5)]

循环过程：

遍历 col_map 的每一对键值，依次执行：

1. 1. col_name = "日期"，col_idx = 1
   → value = row["日期"]
   → 写入 ws.cell(row=target_row, column=1)
2. 2. col_name = "产品名称"，col_idx = 2
   → value = row["产品名称"]
   → 写入 ws.cell(row=target_row, column=2)
3. 3. col_name = "销售员"，col_idx = 3
   → value = row["销售员"]
   → 写入 ws.cell(row=target_row, column=3)
4. 4. col_name = "单价"，col_idx = 4
   → value = row["单价"]
   → 写入 ws.cell(row=target_row, column=4)
5. 5. col_name = "数量"，col_idx = 5
   → value = row["数量"]
   → 写入 ws.cell(row=target_row, column=5)

核心逻辑：

• • col_name 作为桥梁：先从 row 中通过列名取出数据（row[col_name]），再写入到模板对应的列位置（col_idx）
• • 这样做的好处是：无论数据文件的列顺序如何，都能正确匹配到模板的列

示意图：

row (数据文件一行)
    ├── row["产品名称"] = "充电器"  ──→ 写入第2列 (B列)
    ├── row["日期"] = "2024-01-01" ──→ 写入第1列 (A列)
    ├── row["单价"] = 6560         ──→ 写入第4列 (D列)
    └── ...

col_map 自动处理顺序转换

pd.isna() 判断空值

if not pd.isna(value):
    cell.value = value

• • pd.isna(value) 返回 True 的情况：None、NaN、NaT（时间空值）
• • 只有非空值才写入单元格
• • 这样可以避免用空值覆盖已有的内容

ws.cell() 写入单元格

ws.cell(row=target_row, column=col_idx, value=value)

参数说明：

• • row：行号（从1开始），如 3 表示第3行
• • column：列号（从1开始），如 2 表示第2列（B列）
• • value：要写入的值

一句话总结：ws.cell(row, column, value) 将 value 写入第 row 行、第 column 列的单元格。

第八步：保存文件

wb.save(OUTPUT)
print(f"完成！→ {OUTPUT}")
print(f"追加数据范围: 第{write_start_row}行 到 第{write_start_row + rows - 1}行")

输出结果：

读取数据: 44行
模板现有数据: 最后一行是第2行
新数据将从第3行开始写入
列映射: {'日期': 1, '产品名称': 2, '销售员': 3, '单价': 4, '数量': 5}
追加写入新数据...
数据写入完成，共 44 行
完成！→ 追加数据后.xlsx
追加数据范围: 第3行 到 第46行

📚 核心知识点总结

1. 自动查找最后数据行

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

• • 从下往上找：跳过中间可能存在的空行，找到真正的最后一条数据
• • any()函数：判断行中是否有非空单元格
• • for...else结构：完全空白时设置保底值

2. 基于表头的列映射

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

• • 读取模板第1行作为表头
• • enumerate(start=1)让索引直接对应Excel列号
• • 只映射同时存在的列，避免错误

3. 灵活的数据追加

target_row = write_start_row + i
value = row[col_name]  # 通过列名获取值

• • 动态计算目标行号，支持任意数量的追加
• • 通过列名获取值，而非固定位置，列顺序无关

4. 空值处理

if not pd.isna(value):
    cell.value = value

• • 使用 pd.isna() 判断空值，兼容 None、NaN、NaT
• • 避免用空值覆盖有效内容

🔄 核心流程图

读取数据文件 (pandas)
    ↓
加载模板 (openpyxl)
    ↓
从下往上查找最后数据行 ──→ 完全空白？──→ 起始行 = 2
    ↓                              ↓
找到最后行 = row              起始行 = 最后行 + 1
    ↓
读取模板表头，建立列映射
    ↓
遍历数据行 (df.iterrows())
    ↓
通过列映射找到对应的模板列
    ↓
非空值写入目标单元格
    ↓
保存文件 ✓

🗳️ 点单时间到！ 🗳️

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

📦 资源获取提示

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

❤️ 支持我们

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