夜雨聆风Python 玩转 Excel 案例【第 25 期】：批量合并多个Excel文件，自动添加来源标识
📌 案例说明
在实际工作中，我们经常需要将多个结构相同的Excel文件合并成一个总表，同时还要知道每条数据来自哪个文件。比如：
• 各部门提交的月度报表汇总 • 各分公司的销售数据合并 • 电子签章导出的多份文档详情列表
本案例演示如何批量读取文件夹下所有Excel文件，将数据纵向堆叠合并，并自动添加一列“表名”来标识数据来源。
文件结构：
D:/合并表格/├── 项目A.xlsx├── 项目B.xlsx├── 项目C.xlsx└── 合并结果.xlsx (程序生成)每个文件的格式（文档详情列表 sheet）：
⚠️ 注意：文件的第一行可能是空行或标题，第二行才是真正的表头。
需求：
1. 读取文件夹下所有 .xlsx文件2. 每个文件读取指定的 sheet（文档详情列表） 3. 跳过第一行，以第二行作为表头 4. 清理数据中的空格和空字符串 5. 删除全空的行 6. 添加“表名”列，记录数据来源的文件名 7. 将所有数据合并成一个总表
合并后的效果（合并结果.xlsx）：
核心操作：
1. 用 Path.glob()遍历文件夹下所有Excel文件2. 用 skiprows跳过非表头行3. 用 applymap+strip()清理字符串前后空格4. 用 dropna(how='all')删除全空行5. 用 insert(0, ...)在第一列添加来源标识6. 用 pd.concat()纵向合并所有数据
关键库：pandas、pathlib
📜 完整代码
"""批量合并Excel文件工具功能：将文件夹下所有Excel文件的指定sheet合并成一个总表适用：多部门/多项目/多批次数据的汇总场景"""import pandas as pdfrom pathlib import Path# ========== 配置区域 ==========folder_path = Path("D:/合并表格")sheet_name = "文档详情列表"# =============================all_dfs = []print(f"正在扫描文件夹: {folder_path}")print(f"目标Sheet: {sheet_name}")print("-" * 50)# 遍历文件夹下所有xlsx文件for file in Path(folder_path).glob("*.xlsx"): # 跳过程序生成的合并结果文件（避免循环读取） if file.name == "合并结果.xlsx": continue try: # 1. 读取Excel：跳过第一行，以第二行作为表头 df = pd.read_excel(file, skiprows=1, sheet_name=sheet_name) # 2. 清理字符串：去掉前后空格 df = df.applymap(lambda x: x.strip() if isinstance(x, str) else x) # 3. 将空字符串替换为NaN df = df.replace(r'^\s*$', pd.NA, regex=True) # 4. 删除所有列都为空的行 df = df.dropna(how='all') # 5. 添加来源列：在第一列插入文件名（不含扩展名） df.insert(0, "表名", file.stem) all_dfs.append(df) print(f"✓ 已读取: {file.name} - {len(df)} 行数据") except Exception as e: print(f"✗ 读取失败: {file.name} - 错误: {e}")print("-" * 50)# 6. 合并所有数据if all_dfs: merged_df = pd.concat(all_dfs, ignore_index=True) print(f"合并完成！") print(f"总行数: {len(merged_df)}") print(f"总列数: {len(merged_df.columns)}") print(f"\n表头列名:") for i, col in enumerate(merged_df.columns, 1): print(f" {i}. {col}") # 7. 保存合并结果 output_file = folder_path / "合并结果.xlsx" merged_df.to_excel(output_file, index=False) print(f"\n✓ 合并后的文件已保存到: {output_file}") # 8. 数据预览 print(f"\n数据预览（前5行）:") print(merged_df.head())else: print("没有成功读取任何文件，请检查：") print(" 1. 文件夹路径是否正确") print(" 2. 是否有xlsx文件") print(" 3. sheet名称是否拼写正确")运行输出示例：
正在扫描文件夹: D:/合并表格目标Sheet: 文档详情列表--------------------------------------------------✓ 已读取: 项目A.xlsx - 156 行数据✓ 已读取: 项目B.xlsx - 203 行数据✓ 已读取: 项目C.xlsx - 98 行数据--------------------------------------------------合并完成！总行数: 457总列数: 5✓ 合并后的文件已保存到: D:/合并表格/合并结果.xlsx数据预览（前5行）: 表名 文档名称 文档编号 签署时间 签署人0 项目A 合同_001.pdf CON-001 2024/1/1 张三1 项目A 合同_002.pdf CON-002 2024/1/2 李四2 项目B 合同_003.pdf CON-003 2024/1/3 王五3 项目B 合同_004.pdf CON-004 2024/1/4 赵六4 项目C 合同_005.pdf CON-005 2024/1/5 孙七第一步：导入所需库
import pandas as pdfrom pathlib import Path库说明：
• pandas：读取Excel、处理数据、合并文件• pathlib：路径操作，比os.path更简洁、更现代化
第二步：配置路径和参数
folder_path = Path("D:/合并表格")sheet_name = "文档详情列表"代码解析：
• Path("路径")：创建一个路径对象，支持链式操作• sheet_name：要读取的工作表名称（所有文件必须有一致的sheet名）
Path对象的优势：
# 传统写法（os.path）import osfiles = os.listdir("D:/folder")for f in files: if f.endswith(".xlsx"): full_path = os.path.join("D:/folder", f)# Path写法（更优雅）for file in Path("D:/folder").glob("*.xlsx"): print(file.name) # 文件名 print(file.stem) # 文件名（不含扩展名） print(file.parent) # 父文件夹路径第三步：遍历文件夹中的所有Excel文件
all_dfs = []for file in Path(folder_path).glob("*.xlsx"): if file.name == "合并结果.xlsx": continuePath.glob("*.xlsx") 详解：
• glob()是路径匹配方法，支持通配符• "*.xlsx"表示匹配所有以.xlsx结尾的文件• 返回一个生成器，逐个产生匹配的文件路径
为什么要跳过 合并结果.xlsx？
如果不跳过，程序会把上一次合并生成的 合并结果.xlsx 也读进来，导致：
1. 把历史汇总结果再次合并进去 → 数据重复 2. 可能造成无限循环（新合并的文件又被读入）
最佳实践：给输出文件取一个固定的名字，并在遍历时排除它。
第四步：读取Excel文件
df = pd.read_excel(file, skiprows=1, sheet_name=sheet_name)参数说明：
file | ||
skiprows=1 | ||
sheet_name |
为什么要 skiprows=1？
原文件的格式：
第1行：可能是标题或空行（不需要）第2行：真正的表头（文档名称、文档编号、签署时间、签署人）第3行起：数据如果不跳过第1行，表头就会错位：
# 错误示例：没有skiprowsdf.columns = ["乱七八糟的内容", ...] # 第1行成了表头# 正确示例：skiprows=1df.columns = ["文档名称", "文档编号", "签署时间", "签署人"] # 第2行成了表头第五步：清理数据中的空格
df = df.applymap(lambda x: x.strip() if isinstance(x, str) else x)applymap() 是什么？
• DataFrame 的一个方法，作用是对每一个单元格执行同一个函数 • 它会遍历整个表格（所有行、所有列），把每个单元格的值传给函数，然后用函数的返回值替换原值 • 相当于写了两层循环（遍历行 + 遍历列），但用一行代码搞定
lambda x: x.strip() if isinstance(x, str) else x这是一个匿名函数，x 代表传入的单元格值。执行逻辑分两步判断：
• 先判断类型： isinstance(x, str)检查x是不是字符串• 如果是字符串：执行 x.strip()，去掉字符串开头和结尾的空白字符（空格、制表符、换行符等），中间的空格保留• 如果不是字符串（数字、日期、布尔值、None 等）：直接返回 x本身，不做任何处理
为什么要判断类型？
如果对数字调用 strip() 会直接报错。比如数字 100 没有 .strip() 方法，不判断类型的话程序会崩溃。
举例说明
原始 DataFrame：
经过 applymap 处理后：
注意金额列是数字，没有被 strip 影响。
一个容易被忽略的细节
strip() 只能去掉字符串前后的空格，单元格中间的空格会保留。比如 "张 三" 处理后还是 "张 三"，中间多个空格不会被删掉。如果业务上需要统一中间空格，那是另一个话题，要用 replace 或正则处理。
第六步：将空字符串替换为NaN
df = df.replace(r'^\s*$', pd.NA, regex=True)replace() 参数说明：
r'^\s*$' | |
pd.NA | |
regex=True |
正则表达式 r'^\s*$' 详解：
^ | |
\s* | |
$ | |
r'' |
匹配示例：
• ""（空字符串） → 匹配 ✅• " "（多个空格） → 匹配 ✅• "abc"→ 不匹配 ❌
为什么要替换空字符串？
经过 applymap 的 strip() 处理后，原来只有空格的单元格变成了空字符串 ""。但 "" 不是 pandas 的空值标记，dropna 删不掉它。
举个例子：
" " | "" | pd.NA | |
"" | "" | pd.NA | |
None | None | pd.NA | |
" 张三 " | "张三" | "张三" | |
123 | 123 | 123 |
# 如果不做 replace，dropna 无效df = df.applymap(lambda x: x.strip() if isinstance(x, str) else x)df.dropna(how='all') # 删不掉空字符串行！# 加上 replace 才能生效df = df.replace(r'^\s*$', pd.NA, regex=True)df.dropna(how='all') # 生效了一句话：strip() 把空格变 ""，但 dropna 只认 pd.NA，所以需要 replace 转一下。
第七步：删除全空的行
df = df.dropna(how='all')dropna(how='all') 详解：
how='all' | |
how='any' |
示例：
# 原始数据 文档名称 文档编号 签署时间 签署人0 合同1 CON1 1/1 张三1 合同2 NaN 1/2 李四 # 部分空，保留2 NaN NaN NaN NaN # 全空，删除3 NaN NaN 空字符串 NaN # 空字符串已被转NaN，也全空，删除# dropna(how='all') 之后 文档名称 文档编号 签署时间 签署人0 合同1 CON1 1/1 张三1 合同2 NaN 1/2 李四注意：经过上一步的空字符串替换后，原本的 "" 已经变成了 NaN，所以能被正确识别并删除。
第八步：添加来源标识列
df.insert(0, "表名", file.stem)insert() 参数说明：
0 | |
"表名" | |
file.stem |
file.stem 的作用：
# 假设 file = Path("D:/folder/项目A.xlsx")file.name # "项目A.xlsx" （含扩展名）file.stem # "项目A" （不含扩展名）最终效果：
每一行数据前面都会多一列"表名"，标识这条数据来自哪个文件。
表名 文档名称 文档编号 ...0 项目A 合同1.pdf CON001 ...1 项目A 合同2.pdf CON002 ...2 项目B 合同3.pdf CON003 ...实用价值：当合并后想追溯某条数据的来源时，这一列就是“身份证”。
第九步：收集所有数据
all_dfs.append(df)print(f"✓ 已读取: {file.name} - {len(df)} 行数据")all_dfs 列表的结构：
all_dfs = [ df_项目A, # 156行 × 5列 df_项目B, # 203行 × 5列 df_项目C # 98行 × 5列]第十步：纵向合并所有数据
merged_df = pd.concat(all_dfs, ignore_index=True)pd.concat() 参数说明：
all_dfs | |
ignore_index=True |
ignore_index 的效果对比：
# ignore_index=False（默认）df1索引: 0,1,2df2索引: 0,1,2合并后: 0,1,2,0,1,2 # 索引重复！# ignore_index=Truedf1索引: 0,1,2df2索引: 0,1,2合并后: 0,1,2,3,4,5 # 索引连续且唯一推荐：合并时始终设置 ignore_index=True，避免索引混乱。
第十一步：保存合并结果
output_file = folder_path / "合并结果.xlsx"merged_df.to_excel(output_file, index=False)/ 操作符的作用：
pathlib 支持用 / 拼接路径，非常直观：
# 传统写法output = os.path.join(folder_path, "合并结果.xlsx")# Path写法output = folder_path / "合并结果.xlsx"to_excel(index=False) 的作用：
• index=False：不把行索引写入Excel• 如果写成 index=True（默认），会在Excel最左边多出一列无名序号
第十二步：数据预览
print(merged_df.head())head() 方法：默认显示前5行数据，快速检查合并结果是否正确。
📚 核心知识点总结
1. 遍历文件夹
for file in Path(folder).glob("*.xlsx"): print(file.stem) # 文件名（无扩展名）• glob()支持通配符匹配• file.stem获取不含扩展名的文件名
2. 跳过指定行读取
df = pd.read_excel(file, skiprows=1, sheet_name="Sheet1")• skiprows=1跳过第1行，以第2行作表头• 适合文件前几行是标题/空行的情况
3. 字符串清理组合拳
df = df.applymap(lambda x: x.strip() if isinstance(x, str) else x)df = df.replace(r'^\s*$', pd.NA, regex=True)df = df.dropna(how='all')• applymap+strip()：去掉字符串前后空格• replace+ 正则：空字符串 → NaN• dropna(how='all')：删除全空行
4. 添加来源列
df.insert(0, "来源", file.stem)• insert(0, ...)：在第一列插入• 便于追溯每条数据的来源文件
5. 纵向合并
result = pd.concat(all_dfs, ignore_index=True)• ignore_index=True：重置索引，避免重复
🔄 核心流程图
开始 ↓遍历文件夹中所有.xlsx文件 ↓跳过"合并结果.xlsx" ↓读取Excel（skiprows=1跳过第1行） ↓清理字符串空格（applymap + strip） ↓空字符串 → NaN（replace + 正则） ↓删除全空行（dropna how=all） ↓添加"表名"列（insert） ↓存入all_dfs列表 ↓是否还有文件？ ──是──→ 继续遍历 ↓ 否pd.concat纵向合并 ↓保存为"合并结果.xlsx" ↓打印预览信息 ↓结束🛠️ 常见问题与解决方案
Q1：报错 No sheet named '文档详情列表'
A：检查每个Excel文件是否都有这个sheet，名称是否完全一致（注意空格和大小写）。
Q2：表头列数不一致导致合并出错
A：pd.concat() 会自动对齐列名，列数不同也能合并，缺失的列会填充NaN。
Q3：合并后总行数比预期多
A：检查是否没有跳过 合并结果.xlsx，导致把之前合并的文件又读了一遍。
Q4：空行没有被删除
A：确保先执行了 replace(r'^\s*$', pd.NA, regex=True)，因为 dropna 只删除NaN，不删除空字符串。
🗳️ 点单时间到！ 🗳️
下期写什么？你来定，我来写。
📦 资源获取提示
关注「码农自习室」，一起动手掌握高效数据操作的核心技巧！
🛠️ 更多工具
点击下方图片，进入小程序，随时查阅往期所有案例代码。
❤️ 支持我们
如果本文对你有帮助，欢迎点赞 + 关注 + 点亮小红心，你的支持就是我们持续创作的最大动力～