Python批量合并Excel实战：纵向追加、横向拼接与多Sheet处理

1. 为什么我最终放弃了手工复制粘贴

如果你手头经常要处理多个Excel文件，比如每周从不同渠道导出的销售数据、每月各部门提交的报表、或者从系统里分批下载的流水记录，那你一定经历过这种场景：打开十几个文件，挨个复制数据，再粘贴到一个总表里，最后还要检查有没有漏掉某个文件。这种活干一次两次还能忍，干上一个月，人基本就麻了。

我最早接触Python处理Excel，就是从“拼接合并”这个需求开始的。当时手头有三十多个结构完全一样的日报表，每个文件大概几千行，手工合并至少要花一个下午，而且中间一旦接个电话或者回个消息，很容易漏掉某个文件，最后对不上总数还得从头再来。后来用Python写了个脚本，第一次跑通的时候，三十多个文件合并只用了不到十秒，那种感觉就像发现了一个新世界。

这篇文章主要面向两类人：一类是已经会一点Python基础语法，但还没怎么用过Python操作Excel的；另一类是会写简单脚本，但遇到多文件合并时总是出各种小问题的。我会把“拼接合并”这件事拆开揉碎，从最基础的场景讲到稍微复杂一点的变体，包括横向拼接、纵向追加、多工作表合并、带条件筛选的合并，以及合并过程中最容易踩的那些坑。每个操作都会给出可以直接复制运行的代码，并且解释清楚为什么这么写。

提示：本文所有代码基于Python 3.8以上版本，主要使用pandas和openpyxl两个库。如果你还没装，可以先执行pip install pandas openpyxl。

2. 先搞清楚你要的是哪种“合并”

很多人一上来就问“怎么用Python合并Excel”，但“合并”这个词其实很模糊。在实际操作中，至少存在三种完全不同的合并需求，用错方法的话，要么结果不对，要么效率极低。所以在动手写代码之前，先花两分钟确认一下你属于哪种场景。

2.1 纵向追加：结构相同，行数累加

这是最常见的情况。比如你有一月份到十二月份的销售明细，每个月的表头完全一样，列的顺序也一致，你只是想把这些表上下摞在一起，变成一个全年总表。这种操作在数据库里叫“union all”，在pandas里用concat就能搞定。

判断标准很简单：所有文件的列名和列顺序完全一致，只是行数不同。如果你发现某个文件的列名多了或者少了一列，那就不是单纯的纵向追加，需要先做列对齐。

2.2 横向拼接：行对应，列扩展

另一种情况是，你有一个主表包含员工工号和姓名，另一个表包含工号和工资，你想把工资列拼到主表右边，通过工号这个共同列关联起来。这种叫“横向拼接”，在数据库里叫“join”，在pandas里用merge来实现。

横向拼接的关键在于找到两个表的“键列”，也就是用来对齐的那一列或多列。键列的值必须是唯一的，否则会产生笛卡尔积，行数会爆炸。这一点后面会详细说。

2.3 多工作表合并：一个文件里的多个Sheet

还有一种情况是，所有数据都在同一个Excel文件里，但分散在不同的Sheet中，比如“一月”“二月”“三月”三个Sheet，你想把它们合并成一个Sheet。这种操作和纵向追加的逻辑是一样的，只是数据源从多个文件变成了同一个文件的多个Sheet。

下面这张表可以帮你快速判断自己属于哪种场景：

确认清楚场景之后，后面的操作就有的放矢了。我见过不少人拿着横向拼接的需求去用concat，结果列名对不上，出来的表全是NaN，白白浪费半天时间。

3. 纵向追加的完整实操流程

纵向追加是三种场景里最简单、最常用的一种。我把它拆成几个步骤，每个步骤都解释清楚背后的逻辑，这样你遇到变体的时候也能自己调整。

3.1 读取单个文件并检查结构

在合并之前，必须先确认所有文件的结构是一致的。我习惯先读一个文件看看它的列名、数据类型和行数。代码很简单：

import pandas as pd # 读取单个文件，查看基本信息 df_sample = pd.read_excel('data/一月销售.xlsx') print(df_sample.shape) # 查看行数和列数 print(df_sample.columns.tolist()) # 查看列名列表 print(df_sample.dtypes) # 查看每列的数据类型 print(df_sample.head(3)) # 查看前3行数据

这几行代码看起来不起眼，但能帮你避开很多坑。比如有时候某个文件的列名前面多了空格，或者“金额”列在某些文件里是文本类型、在另一些文件里是数值类型，直接合并就会出问题。提前检查一遍，心里有数。

注意：read_excel默认读取第一个Sheet。如果你的数据不在第一个Sheet，需要加sheet_name参数指定。

3.2 批量读取文件夹下所有文件

确认结构没问题之后，就可以批量读取了。我通常用os.listdir或者pathlib来遍历文件夹，然后用一个列表把所有DataFrame存起来。这里有一个细节：读取的时候最好只读需要的列，或者至少确保每个文件读进来的列是一致的。

import os import pandas as pd folder_path = 'data/月度报表' all_files = [f for f in os.listdir(folder_path) if f.endswith('.xlsx')] df_list = [] for file in all_files: file_path = os.path.join(folder_path, file) df = pd.read_excel(file_path) df_list.append(df) print(f'共读取了 {len(df_list)} 个文件')

这段代码的逻辑很直白：遍历文件夹，找到所有.xlsx结尾的文件，逐个读取，存进列表。但这里有几个容易出问题的地方，我一个个说。

第一个问题是文件名排序。os.listdir返回的顺序是不确定的，有时候是按文件名排序，有时候不是。如果你对合并后的行顺序有要求，比如希望一月在前、十二月在后，那就需要手动排序：

all_files.sort() # 按文件名排序

第二个问题是临时文件。Excel打开文件时会生成以~$开头的临时文件，这些文件也会被endswith('.xlsx')匹配到，但读取时会报错。所以最好加一个过滤：

all_files = [f for f in os.listdir(folder_path) if f.endswith('.xlsx') and not f.startswith('~$')]

第三个问题是子文件夹。如果文件夹里还有子文件夹，os.listdir会把子文件夹的名字也列出来，读取时就会报错。这种情况可以用glob来递归匹配：

from glob import glob all_files = glob(os.path.join(folder_path, '**', '*.xlsx'), recursive=True)

3.3 用concat完成合并并保留来源信息

读取完所有文件之后，合并本身只需要一行代码：

df_all = pd.concat(df_list, ignore_index=True)

ignore_index=True的作用是重新生成行索引，不然合并后的索引会是每个文件原来的索引重复出现，比如0到999出现十二次，看起来很不舒服，后续做筛选也容易出问题。

但这里有一个很实用的技巧：在合并的时候保留每条数据来自哪个文件。这个信息在排查问题时非常有用，比如你发现总表里某条数据不对，想知道它来自哪个原始文件，如果没有来源列，就只能一个个文件去翻。

for file in all_files: file_path = os.path.join(folder_path, file) df = pd.read_excel(file_path) df['来源文件'] = file # 新增一列记录来源 df_list.append(df) df_all = pd.concat(df_list, ignore_index=True)

多这一列几乎不占什么空间，但排查问题时能省下大量时间。我强烈建议你在合并时都加上这一列。

3.4 合并后的数据校验

合并完成不代表万事大吉，必须做一次校验。最基本的校验是行数核对：所有文件的行数之和应该等于合并后的行数。

total_rows = sum(df.shape[0] for df in df_list) print(f'各文件行数之和：{total_rows}') print(f'合并后行数：{df_all.shape[0]}') assert total_rows == df_all.shape[0], '行数不一致，请检查'

如果行数对不上，最常见的原因是某个文件有隐藏的空行，或者某个文件的表头不在第一行。这时候就需要逐个文件检查，找出那个“异类”。

另一个校验是检查关键列是否有空值：

print(df_all.isnull().sum())

如果某个关键列出现了大量空值，说明某些文件的列名可能不一致，导致pandas在合并时自动对齐了列名，不匹配的列就变成了空值。

4. 横向拼接的关键细节与避坑指南

横向拼接比纵向追加稍微复杂一点，因为涉及到“键列”的概念。用得好，几秒钟就能把两个表关联起来；用不好，行数翻倍、数据错乱，排查起来非常头疼。

4.1 理解merge的四种连接方式

pd.merge的how参数决定了连接方式，常用的有四种：

默认是inner，也就是只保留两个表都能匹配上的行。这个默认值很容易让人踩坑：如果你用默认的inner去合并，结果发现行数比左表少了很多，那就是因为右表里没有对应的键值，这些行被丢掉了。

4.2 键列重复导致的笛卡尔积问题

这是横向拼接里最容易出大问题的地方。假设左表是员工基本信息，每个工号只有一行；右表是员工项目参与记录，一个工号可能对应多行。如果你直接用工号做键列去merge，结果就是每个员工的基本信息会被复制多份，行数等于该员工参与的项目数。

# 左表：员工基本信息 df_emp = pd.DataFrame({ '工号': ['A001', 'A002', 'A003'], '姓名': ['张三', '李四', '王五'] }) # 右表：项目参与记录 df_proj = pd.DataFrame({ '工号': ['A001', 'A001', 'A002'], '项目': ['项目X', '项目Y', '项目Z'] }) # 直接merge result = pd.merge(df_emp, df_proj, on='工号', how='left') print(result)

结果会是A001出现两行，因为他在右表里有两条记录。这本身不一定是错误，取决于你的业务需求。但如果你没意识到这一点，以为合并后还是三行，那后续的统计就会全部出错。

避免这个问题的方法有两个：一是合并前先确认右表的键列是否唯一，用df_proj['工号'].duplicated().any()来检查；二是如果确实需要合并多行记录，合并后要意识到行数会变化，后续统计要用正确的方式。

4.3 列名冲突的处理

如果两个表有同名的列，但不是键列，merge之后pandas会自动加后缀_x和_y来区分。这个后缀可以自定义：

result = pd.merge(df_left, df_right, on='工号', how='left', suffixes=('_左', '_右'))

我建议在合并前就把列名改清楚，比如把“金额”改成“基本工资”和“绩效工资”，这样合并后一目了然，不用去猜_x和_y分别代表什么。

4.4 合并后的数据完整性检查

横向拼接完成后，重点检查两件事：一是行数是否符合预期，二是键列有没有出现空值。

print(f'合并前行数：{df_left.shape[0]}') print(f'合并后行数：{result.shape[0]}') print(f'键列空值数：{result["工号"].isnull().sum()}')

如果合并后行数比左表多，说明右表的键列有重复；如果键列出现了空值，说明有行没有匹配上，需要根据业务判断是保留还是剔除。

5. 多工作表合并与批量文件处理进阶

前面讲的是单个文件或单个Sheet的情况，实际工作中还经常遇到一个文件里多个Sheet，或者文件夹里嵌套文件夹的情况。这一部分把几个进阶场景串起来讲。

5.1 一个文件多个Sheet的合并

pd.read_excel有一个很实用的参数sheet_name=None，它会一次性读取所有Sheet，返回一个字典，键是Sheet名，值是对应的DataFrame。

# 一次性读取所有Sheet sheets_dict = pd.read_excel('data/季度数据.xlsx', sheet_name=None) # 查看所有Sheet名 print(sheets_dict.keys()) # 合并所有Sheet df_all = pd.concat(sheets_dict.values(), ignore_index=True)

如果想保留Sheet来源信息，可以在合并前给每个DataFrame加一列：

for sheet_name, df in sheets_dict.items(): df['来源Sheet'] = sheet_name df_all = pd.concat(sheets_dict.values(), ignore_index=True)

这里有一个细节：sheets_dict.values()返回的是字典的值视图，在遍历时如果修改了DataFrame，原字典里的DataFrame也会被修改。所以上面的代码是可行的，但如果你不想修改原数据，可以先复制一份。

5.2 只合并指定的Sheet

有时候一个文件里有十几个Sheet，但你只需要其中几个。可以在读取时指定Sheet名列表：

sheets_dict = pd.read_excel('data/年度数据.xlsx', sheet_name=['一月', '二月', '三月']) df_all = pd.concat(sheets_dict.values(), ignore_index=True)

或者读取全部之后再筛选：

sheets_dict = pd.read_excel('data/年度数据.xlsx', sheet_name=None) target_sheets = ['一月', '二月', '三月'] df_all = pd.concat([sheets_dict[s] for s in target_sheets], ignore_index=True)

5.3 批量处理时的性能优化

当文件数量很多、每个文件又很大的时候，读取速度会成为瓶颈。我实测下来，有几个技巧可以明显提升速度。

第一个是只读取需要的列。read_excel的usecols参数可以指定列名或列索引：

df = pd.read_excel(file_path, usecols=['工号', '姓名', '金额'])

第二个是指定数据类型。pandas会自动推断每列的类型，这个过程比较耗时。如果提前知道列的类型，可以用dtype参数指定：

df = pd.read_excel(file_path, dtype={'工号': str, '金额': float})

第三个是考虑用openpyxl的只读模式。pandas底层读取xlsx文件用的就是openpyxl，但默认不是只读模式。如果文件特别大，可以先用openpyxl的只读模式加载，再转成DataFrame。不过这个操作稍微复杂一些，一般情况下用前两个技巧就够了。

提示：如果文件是.xls格式而不是.xlsx，需要安装xlrd库，并且read_excel会自动调用它。但xlrd对新版xlsx支持不好，建议尽量把文件转成xlsx格式。

6. 常见报错与排查技巧实录

这一部分是我在实际操作中踩过的坑，以及帮别人排查问题时总结出来的经验。每一个问题都给出了具体的报错信息和解决方法，你可以当成速查表来用。

6.1 文件读取相关报错

报错：FileNotFoundError: [Errno 2] No such file or directory

这个报错最常见的原因是路径写错了。Windows系统里路径分隔符是反斜杠\，但在Python字符串里反斜杠是转义字符，所以要么用双反斜杠\\，要么用正斜杠/，要么在字符串前面加r变成原始字符串。

# 三种写法都可以 df = pd.read_excel('data\\一月.xlsx') df = pd.read_excel('data/一月.xlsx') df = pd.read_excel(r'data\一月.xlsx')

另一个原因是文件名里有空格或特殊字符，而代码里没写对。建议用os.path.join来拼接路径，避免手动拼接出错。

报错：ValueError: File is not a recognized excel file

这个报错通常是因为文件扩展名是.xlsx，但实际内容不是Excel格式，比如是一个CSV文件改了扩展名，或者文件损坏了。可以用file命令（Linux/Mac）或查看文件头来判断真实格式。

6.2 合并相关报错

报错：ValueError: No objects to concatenate

这个报错的意思是concat的列表是空的，也就是一个文件都没读到。原因可能是文件夹路径写错了，或者过滤条件太严格，把所有文件都过滤掉了。建议在读取前先打印一下文件列表：

print(f'找到 {len(all_files)} 个文件') print(all_files[:5]) # 打印前5个文件名

报错：KeyError: '工号'

这个报错出现在merge时，说明指定的键列名在某个表里不存在。最常见的原因是列名前后有空格，或者列名是“工号 ”（后面多了一个空格）。可以用df.columns.tolist()打印列名，仔细核对。

6.3 数据内容相关的问题

问题：合并后某些列全是NaN

这种情况通常是因为不同文件的列名不一致。比如一个文件里叫“金额”，另一个文件里叫“金额（元）”，pandas在concat时会按列名对齐，不匹配的列就填NaN。解决方法是在读取后统一列名：

df.rename(columns={'金额（元）': '金额'}, inplace=True)

问题：数字变成了科学计数法

工号、订单号这类长数字，如果被pandas识别为数值类型，显示时会变成科学计数法，比如1.23457E+11。解决方法是在读取时指定为字符串类型：

df = pd.read_excel(file_path, dtype={'工号': str})

或者读取后转换：

df['工号'] = df['工号'].astype(str)

问题：日期格式不一致

不同文件里的日期格式可能不一样，有的是“2024-01-01”，有的是“2024/1/1”，有的是Excel的日期序列号。合并后需要统一格式：

df['日期'] = pd.to_datetime(df['日期'], errors='coerce')

errors='coerce'的作用是遇到无法解析的值就设为NaT，而不是直接报错。这样你可以先合并，再统一处理异常值。

6.4 常见问题速查表

7. 几个让我省下大量时间的实操心得

最后分享几个我在实际项目中总结出来的小技巧，都是那种“知道了能省不少事”的经验。

第一个是先小后大。不要一上来就拿几百个文件跑，先用三五个文件测试脚本，确认逻辑没问题、结果正确，再扩大到全部文件。我见过有人直接跑全量数据，结果因为一个文件的列名不对，整个结果都错了，还得从头再来。

第二个是保留中间结果。合并完成后，先把结果存成一个新文件，再做后续处理。这样万一后续步骤出错，不用重新读取和合并所有文件。

df_all.to_excel('output/合并结果.xlsx', index=False)

第三个是用Parquet格式做中间存储。如果你需要反复读取合并后的数据，存成Parquet比Excel快很多，而且文件更小。pandas直接支持：

df_all.to_parquet('output/合并结果.parquet') df = pd.read_parquet('output/合并结果.parquet')

第四个是给脚本加日志。不用很复杂，在关键步骤打印一下进度就行。比如每读取十个文件打印一次，这样你能知道脚本跑到哪里了，有没有卡住。

for i, file in enumerate(all_files): if i % 10 == 0: print(f'正在处理第 {i+1}/{len(all_files)} 个文件') # 读取和处理逻辑

第五个是异常处理要具体。不要用一个try...except把所有异常都吞掉，那样出了问题你根本不知道是哪个文件、什么原因。我习惯在读取单个文件时捕获异常，并打印文件名：

for file in all_files: try: df = pd.read_excel(os.path.join(folder_path, file)) df_list.append(df) except Exception as e: print(f'读取文件 {file} 失败：{e}')

这样即使某个文件有问题，脚本也不会中断，而且你能清楚地知道是哪个文件出了问题，单独去处理它就行。

这些经验看起来简单，但都是我在实际工作中一次次踩坑之后才总结出来的。尤其是异常处理那一条，早期我写脚本从来不处理异常，结果一个文件有问题整个脚本就崩了，还得从头跑一遍，浪费了大量时间。后来加上异常处理和日志之后，脚本的稳定性提升了很多，即使有问题也能快速定位。