Python 自动化 Excel 批量读取多个 Excel 文件并合并详细教程
在实际工作中，我们经常会遇到需要把多个Excel文件合并成一个的场景，比如各部门提交的报表、各月份的销售数据、各分支机构的台账等。手动合并不仅费时费力，还容易出错，用Python可以轻松实现批量读取合并，效率提升几十倍。本文将详细介绍各种合并场景的实现方法，包含完整代码示例和最佳实践。
一、环境准备
1.1 安装依赖库
bash
pip install openpyxl pandas os
1.2 适用场景
批量合并Excel是非常高频的需求，常见场景包括：
二、基础合并操作
2.1 合并相同结构的Excel文件
最常见的场景：多个Excel文件结构相同（表头一样），需要合并到一个文件中。
示例场景：
> 有一个”销售报表”文件夹，里面是每个销售提交的个人业绩表，格式完全相同，都有”日期、产品、销售额、利润、销售人”五列，需要合并成一个总表。
python
import os
import pandas as pd
# 文件夹路径
folder_path = "销售报表/"
# 获取文件夹下所有Excel文件
excel_files = [f for f in os.listdir(folder_path) if f.endswith((".xlsx", ".xls")) and not f.startswith("~$")]
print(f"找到 {len(excel_files)} 个Excel文件：")
for f in excel_files:
print(f" - {f}")
# 存储所有数据
all_data = []
# 循环读取每个文件
for file in excel_files:
file_path = os.path.join(folder_path, file)
try:
# 读取Excel文件，默认读取第一个工作表
df = pd.read_excel(file_path)
# 可选：添加一列记录来源文件
df["来源文件"] = file
all_data.append(df)
print(f"✅ 读取成功：{file}，共{len(df)}行数据")
except Exception as e:
print(f"❌ 读取失败：{file}，错误：{str(e)}")
# 合并所有数据
if all_data:
merged_df = pd.concat(all_data, ignore_index=True)
# 保存合并后的文件
merged_df.to_excel("合并后的销售报表.xlsx", index=False)
print(f"\n🎉 合并完成！总行数：{len(merged_df)}，已保存为'合并后的销售报表.xlsx'")
else:
print("\n⚠️ 没有可合并的数据")
2.2 合并指定工作表
如果每个Excel文件有多个工作表，只需要合并指定名称的工作表：
python
import os
import pandas as pd
folder_path = "多工作表文件/"
sheet_name = "销售数据" # 要合并的工作表名称
excel_files = [f for f in os.listdir(folder_path) if f.endswith((".xlsx", ".xls")) and not f.startswith("~$")]
all_data = []
for file in excel_files:
file_path = os.path.join(folder_path, file)
try:
df = pd.read_excel(file_path, sheet_name=sheet_name)
df["来源文件"] = file
all_data.append(df)
print(f"✅ 读取{file}的{sheet_name}工作表成功，共{len(df)}行")
except Exception as e:
print(f"❌ 读取{file}失败：{str(e)}")
if all_data:
merged_df = pd.concat(all_data, ignore_index=True)
merged_df.to_excel("合并指定工作表.xlsx", index=False)
print(f"\n✅ 合并完成，共{len(merged_df)}行数据")
2.3 合并所有工作表
有些文件每个工作表都是相同结构，需要把所有工作表都合并：
python
import os
import pandas as pd
from openpyxl import load_workbook
folder_path = "文件/"
all_data = []
for file in os.listdir(folder_path):
if file.endswith((".xlsx", ".xls")) and not file.startswith("~$"):
file_path = os.path.join(folder_path, file)
try:
# 获取所有工作表名称
wb = load_workbook(file_path, read_only=True)
sheet_names = wb.sheetnames
wb.close()
for sheet_name in sheet_names:
df = pd.read_excel(file_path, sheet_name=sheet_name)
df["来源文件"] = file
df["来源工作表"] = sheet_name
all_data.append(df)
print(f"✅ 读取{file} - {sheet_name}，共{len(df)}行")
except Exception as e:
print(f"❌ 处理{file}失败：{str(e)}")
if all_data:
merged_df = pd.concat(all_data, ignore_index=True)
merged_df.to_excel("合并所有工作表.xlsx", index=False)
print(f"\n✅ 全部合并完成，共{len(merged_df)}行数据")
三、高级合并技巧
3.1 跳过表头行
有些Excel文件前几行是说明、标题等，需要跳过指定行数再读取：
python
import os
import pandas as pd
folder_path = "带表头说明的报表/"
skip_rows = 2 # 跳过前2行，从第3行开始读
header_row = 0 # 第3行作为表头（相对于跳过行后的索引）
all_data = []
for file in os.listdir(folder_path):
if file.endswith((".xlsx", ".xls")) and not file.startswith("~$"):
file_path = os.path.join(folder_path, file)
try:
# skiprows：跳过前N行，header：指定第几行作为表头
df = pd.read_excel(file_path, skiprows=skip_rows, header=header_row)
df["来源文件"] = file
all_data.append(df)
print(f"✅ 读取{file}成功，共{len(df)}行")
except Exception as e:
print(f"❌ 读取{file}失败：{str(e)}")
if all_data:
merged_df = pd.concat(all_data, ignore_index=True)
merged_df.to_excel("跳表头合并.xlsx", index=False)
print(f"\n✅ 合并完成，共{len(merged_df)}行")
3.2 只合并特定列
不需要所有列，只合并需要的几列：
python
import os
import pandas as pd
folder_path = "销售报表/"
# 只需要合并的列
usecols = ["日期", "产品名称", "销售额", "销售人"]
all_data = []
for file in os.listdir(folder_path):
if file.endswith((".xlsx", ".xls")) and not file.startswith("~$"):
file_path = os.path.join(folder_path, file)
try:
df = pd.read_excel(file_path, usecols=usecols)
df["来源文件"] = file
all_data.append(df)
print(f"✅ 读取{file}成功，共{len(df)}行")
except Exception as e:
print(f"❌ 读取{file}失败：{str(e)}")
if all_data:
merged_df = pd.concat(all_data, ignore_index=True)
merged_df.to_excel("指定列合并.xlsx", index=False)
print(f"\n✅ 合并完成，共{len(merged_df)}行")
3.3 按条件筛选后合并
读取时只合并符合条件的数据，不需要的行直接过滤：
python
import os
import pandas as pd
folder_path = "销售报表/"
all_data = []
for file in os.listdir(folder_path):
if file.endswith((".xlsx", ".xls")) and not file.startswith("~$"):
file_path = os.path.join(folder_path, file)
try:
df = pd.read_excel(file_path)
# 只合并销售额大于10000的记录
df_filtered = df[df["销售额"] > 10000]
if not df_filtered.empty:
df_filtered["来源文件"] = file
all_data.append(df_filtered)
print(f"✅ 读取{file}成功，符合条件{len(df_filtered)}行")
else:
print(f"ℹ️ {file}没有符合条件的数据")
except Exception as e:
print(f"❌ 读取{file}失败：{str(e)}")
if all_data:
merged_df = pd.concat(all_data, ignore_index=True)
merged_df.to_excel("筛选后合并.xlsx", index=False)
print(f"\n✅ 合并完成，共{len(merged_df)}行符合条件的数据")
3.4 处理不同列名的文件
有些文件列名可能不一致，比如有的叫”销售金额”，有的叫”销售额”，需要先统一列名再合并：
python
import os
import pandas as pd
folder_path = "列名不一致的报表/"
# 列名映射：统一成标准列名
column_mapping = {
"销售金额": "销售额",
"出售额": "销售额",
"营收": "销售额",
"销售员": "销售人",
"业务员": "销售人",
"产品": "产品名称",
"货品名称": "产品名称"
}
# 标准列名
standard_columns = ["日期", "产品名称", "销售额", "销售人"]
all_data = []
for file in os.listdir(folder_path):
if file.endswith((".xlsx", ".xls")) and not file.startswith("~$"):
file_path = os.path.join(folder_path, file)
try:
df = pd.read_excel(file_path)
# 重命名列
df = df.rename(columns=column_mapping)
# 只保留标准列，缺失的列填充空值
for col in standard_columns:
if col not in df.columns:
df[col] = None
df = df[standard_columns + ["来源文件"]]
df["来源文件"] = file
all_data.append(df)
print(f"✅ 读取{file}成功，共{len(df)}行")
except Exception as e:
print(f"❌ 读取{file}失败：{str(e)}")
if all_data:
merged_df = pd.concat(all_data, ignore_index=True)
merged_df.to_excel("统一列名合并.xlsx", index=False)
print(f"\n✅ 合并完成，共{len(merged_df)}行")
四、合并后的数据处理
4.1 去重
合并后去除重复行：
python
merged_df = pd.concat(all_data, ignore_index=True)
# 按所有列去重，保留第一个
merged_df = merged_df.drop_duplicates(keep="first")
# 按指定列去重，比如按"日期"和"订单号"去重
merged_df = merged_df.drop_duplicates(subset=["日期", "订单号"], keep="last")
print(f"✅ 去重完成，剩余{len(merged_df)}行")
4.2 排序
合并后按指定列排序：
python
# 按日期升序排序
merged_df["日期"] = pd.to_datetime(merged_df["日期"]) # 先转换成日期格式
merged_df = merged_df.sort_values(by="日期", ascending=True)
# 多列排序：先按日期升序，再按销售额降序
merged_df = merged_df.sort_values(by=["日期", "销售额"], ascending=[True, False])
4.3 数据清洗
合并后进行简单的数据清洗：
python
# 删除空行
merged_df = merged_df.dropna(how="all") # 删除全为空的行
merged_df = merged_df.dropna(subset=["日期", "销售额"]) # 删除日期或销售额为空的行
# 填充空值
merged_df["销售人"] = merged_df["销售人"].fillna("未知")
merged_df["备注"] = merged_df["备注"].fillna("")
# 修正数据类型
merged_df["销售额"] = pd.to_numeric(merged_df["销售额"], errors="coerce")
merged_df["日期"] = pd.to_datetime(merged_df["日期"], errors="coerce")
4.4 添加汇总行
合并后添加合计行：
python
# 计算合计
total_sales = merged_df["销售额"].sum()
total_profit = merged_df["利润"].sum()
# 添加汇总行
summary_row = pd.DataFrame({
"日期": ["合计"],
"产品名称": [""],
"销售额": [total_sales],
"利润": [total_profit],
"销售人": [""],
"来源文件": [""]
})
merged_df = pd.concat([merged_df, summary_row], ignore_index=True)
五、复杂合并场景
5.1 合并不同层级的文件
文件夹下还有子文件夹，需要递归读取所有Excel文件：
python
import os
import pandas as pd
root_folder = "多层级报表/"
all_data = []
# 递归遍历所有子文件夹
for root, dirs, files in os.walk(root_folder):
for file in files:
if file.endswith((".xlsx", ".xls")) and not file.startswith("~$"):
file_path = os.path.join(root, file)
try:
df = pd.read_excel(file_path)
df["来源路径"] = os.path.relpath(file_path, root_folder) # 保存相对路径
all_data.append(df)
print(f"✅ 读取{file_path}成功，共{len(df)}行")
except Exception as e:
print(f"❌ 读取{file_path}失败：{str(e)}")
if all_data:
merged_df = pd.concat(all_data, ignore_index=True)
merged_df.to_excel("递归合并.xlsx", index=False)
print(f"\n✅ 全部合并完成，共{len(merged_df)}行")
5.2 按文件名分组合并到不同工作表
把不同类型的文件合并到同一个Excel的不同工作表：
python
import os
import pandas as pd
folder_path = "各类报表/"
# 按关键词分组，每个关键词对应一个工作表
groups = {
"销售": "销售数据",
"财务": "财务报表",
"人力": "人力数据",
"库存": "库存报表"
}
# 初始化每个组的数据列表
group_data = {name: [] for name in groups.values()}
for file in os.listdir(folder_path):
if file.endswith((".xlsx", ".xls")) and not file.startswith("~$"):
file_path = os.path.join(folder_path, file)
try:
df = pd.read_excel(file_path)
df["来源文件"] = file
# 判断属于哪个组
assigned = False
for keyword, group_name in groups.items():
if keyword in file:
group_data[group_name].append(df)
assigned = True
print(f"✅ {file} 分配到 {group_name}")
break
if not assigned:
print(f"ℹ️ {file} 未匹配到分组，跳过")
except Exception as e:
print(f"❌ 读取{file}失败：{str(e)}")
# 保存到不同工作表
with pd.ExcelWriter("分组合并.xlsx") as writer:
for sheet_name, data_list in group_data.items():
if data_list:
df = pd.concat(data_list, ignore_index=True)
df.to_excel(writer, sheet_name=sheet_name, index=False)
print(f"✅ {sheet_name}工作表保存，共{len(df)}行")
print("\n🎉 分组合并完成！")
5.3 百万级大数据合并
数据量很大（超过100万行）时，分批读取合并，避免内存不足：
python
import os
import pandas as pd
folder_path = "大数据文件/"
output_file = "大数据合并结果.xlsx"
chunksize = 100000 # 每次读取10万行
# 第一次先创建文件，写入表头
first_file = True
for file in os.listdir(folder_path):
if file.endswith((".xlsx", ".xls")) and not file.startswith("~$"):
file_path = os.path.join(folder_path, file)
# 分批读取
for chunk in pd.read_excel(file_path, chunksize=chunksize):
if first_file:
# 第一次写入，包含表头
chunk.to_excel(output_file, index=False)
first_file = False
else:
# 追加写入，不写表头
with pd.ExcelWriter(output_file, mode="a", engine="openpyxl", if_sheet_exists="overlay") as writer:
# 获取现有行数
book = writer.book
sheet = book.active
startrow = sheet.max_row
chunk.to_excel(writer, index=False, header=False, startrow=startrow)
print(f"✅ 处理完成：{file}")
print("✅ 大数据合并完成！")
5.4 合并后保留原格式
用pandas合并会丢失格式，如果需要保留原文件的样式，用openpyxl实现：
python
import os
from openpyxl import load_workbook, Workbook
folder_path = "需要保留格式的文件/"
output_wb = Workbook()
output_ws = output_wb.active
first_file = True
row_offset = 1 # 行偏移量，从第一行开始
for file in os.listdir(folder_path):
if file.endswith(".xlsx") and not file.startswith("~$"):
file_path = os.path.join(folder_path, file)
wb = load_workbook(file_path)
ws = wb.active
# 第一个文件复制表头，其他文件跳过表头
start_row = 1 if first_file else 2
for row in range(start_row, ws.max_row + 1):
for col in range(1, ws.max_column + 1):
source_cell = ws.cell(row=row, column=col)
target_cell = output_ws.cell(row=row_offset, column=col, value=source_cell.value)
# 复制样式
if source_cell.has_style:
target_cell.font = source_cell.font.copy()
target_cell.border = source_cell.border.copy()
target_cell.fill = source_cell.fill.copy()
target_cell.number_format = source_cell.number_format
target_cell.protection = source_cell.protection.copy()
target_cell.alignment = source_cell.alignment.copy()
row_offset += 1
first_file = False
print(f"✅ 复制完成：{file}")
output_wb.save("带格式合并.xlsx")
print("✅ 带格式合并完成！")
六、常见问题与解决方案
6.1 合并后列错位
问题：合并后列数据错乱，对不上表头
原因：文件列名、列顺序不一致
解决方案：
合并前统一列名，参考3.4节方法
合并时指定列顺序，只保留需要的列
不要忽略表头，确保每个文件的表头正确识别
6.2 内存不足报错
问题：文件太多太大，合并时内存不足程序崩溃
解决方案：
分批读取合并，参考5.3节方法，每次处理一部分
只读取需要的列，减少内存占用
64位系统、Python环境可以支持更大内存
非常大的数据建议保存为csv格式，比Excel更省内存
6.3 读取速度慢
问题：几十上百个文件读取很慢
解决方案：
优先用pandas读取，比openpyxl快很多
跳过不需要的行、列，减少数据量
关闭杀毒软件、文件同步工具，提升磁盘IO速度
大文件可以转换成csv格式再处理，速度提升明显
6.4 数字变成科学计数法
问题：合并后长数字、手机号、身份证号变成科学计数法
解决方案：
读取时指定列类型为字符串：
python
df = pd.read_excel(file_path, dtype={"手机号": str, "身份证号": str})
保存时设置单元格格式：
python
with pd.ExcelWriter("合并结果.xlsx", engine="openpyxl") as writer:
df.to_excel(writer, index=False)
worksheet = writer.sheets["Sheet1"]
# 设置B列为文本格式
for cell in worksheet["B"]:
cell.number_format = "@"
6.5 合并后有空行
问题：合并结果里有很多空行
解决方案：
合并后删除空行：`merged_df = merged_df.dropna(how=”all”)`
读取时过滤空行：`df = df[df.notna().any(axis=1)]`
检查原文件是否有空行，读取时跳过
七、最佳实践
1. 先备份再操作：合并前备份所有源文件，避免合并错误导致数据丢失
2. 小批量测试：先拿几个文件测试合并逻辑，确认结果正确再批量处理
3. 统一模板优先：尽量让源文件格式统一，减少后续处理成本
4. 保留来源信息：合并时添加”来源文件”、”来源工作表”等列，方便后续溯源
5. 结果验证：合并后检查总行数是否等于各文件行数之和，抽样核对数据是否正确
6. 命名规范：合并后的文件名包含日期、合并范围等信息，比如”2026Q1销售报表合并_20260331.xlsx”
7. 大文件优化：超过10万行的数据优先用分批处理，避免内存不足
8. 异常处理：代码中添加异常捕获，避免单个文件错误导致整个合并过程中断
批量合并Excel是Python自动化办公中最实用的技能之一，熟练掌握后可以把几小时的手动工作缩短到几分钟，还能避免人为错误。根据实际场景选择合适的合并方法，可以大幅提升工作效率！
