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。下面这张表可以帮你快速判断自己属于哪种场景场景类型数据源合并方式核心函数典型用途纵向追加多个文件上下摞起来pd.concat月度报表合并成年报横向拼接多个文件或Sheet左右拼起来pd.merge补充字段信息多Sheet合并同一个文件上下摞起来pd.concatsheet_nameNone分月数据汇总确认清楚场景之后后面的操作就有的放矢了。我见过不少人拿着横向拼接的需求去用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), recursiveTrue)3.3 用concat完成合并并保留来源信息读取完所有文件之后合并本身只需要一行代码df_all pd.concat(df_list, ignore_indexTrue)ignore_indexTrue的作用是重新生成行索引不然合并后的索引会是每个文件原来的索引重复出现比如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_indexTrue)多这一列几乎不占什么空间但排查问题时能省下大量时间。我强烈建议你在合并时都加上这一列。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内连接只保留键列匹配上的行两个表都要有对应数据left左连接保留左表所有行右表匹配不上的填NaN以主表为准补充信息right右连接保留右表所有行以补充表为准outer外连接保留所有行匹配不上的填NaN查漏补缺默认是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工号, howleft) print(result)结果会是A001出现两行因为他在右表里有两条记录。这本身不一定是错误取决于你的业务需求。但如果你没意识到这一点以为合并后还是三行那后续的统计就会全部出错。避免这个问题的方法有两个一是合并前先确认右表的键列是否唯一用df_proj[工号].duplicated().any()来检查二是如果确实需要合并多行记录合并后要意识到行数会变化后续统计要用正确的方式。4.3 列名冲突的处理如果两个表有同名的列但不是键列merge之后pandas会自动加后缀_x和_y来区分。这个后缀可以自定义result pd.merge(df_left, df_right, on工号, howleft, 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_nameNone它会一次性读取所有Sheet返回一个字典键是Sheet名值是对应的DataFrame。# 一次性读取所有Sheet sheets_dict pd.read_excel(data/季度数据.xlsx, sheet_nameNone) # 查看所有Sheet名 print(sheets_dict.keys()) # 合并所有Sheet df_all pd.concat(sheets_dict.values(), ignore_indexTrue)如果想保留Sheet来源信息可以在合并前给每个DataFrame加一列for sheet_name, df in sheets_dict.items(): df[来源Sheet] sheet_name df_all pd.concat(sheets_dict.values(), ignore_indexTrue)这里有一个细节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_indexTrue)或者读取全部之后再筛选sheets_dict pd.read_excel(data/年度数据.xlsx, sheet_nameNone) target_sheets [一月, 二月, 三月] df_all pd.concat([sheets_dict[s] for s in target_sheets], ignore_indexTrue)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(rdata\一月.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{金额元: 金额}, inplaceTrue)问题数字变成了科学计数法工号、订单号这类长数字如果被pandas识别为数值类型显示时会变成科学计数法比如1.23457E11。解决方法是在读取时指定为字符串类型df pd.read_excel(file_path, dtype{工号: str})或者读取后转换df[工号] df[工号].astype(str)问题日期格式不一致不同文件里的日期格式可能不一样有的是“2024-01-01”有的是“2024/1/1”有的是Excel的日期序列号。合并后需要统一格式df[日期] pd.to_datetime(df[日期], errorscoerce)errorscoerce的作用是遇到无法解析的值就设为NaT而不是直接报错。这样你可以先合并再统一处理异常值。6.4 常见问题速查表报错/问题可能原因解决方法FileNotFoundError路径错误或文件不存在检查路径用os.path.join拼接No objects to concatenate文件列表为空打印文件列表检查过滤条件KeyError列名不存在或拼写错误打印columns核对合并后列全是NaN列名不一致统一列名后再合并数字变科学计数法被识别为数值类型读取时指定dtypestr行数比预期多键列有重复检查duplicated确认业务逻辑日期格式混乱各文件格式不同用to_datetime统一转换7. 几个让我省下大量时间的实操心得最后分享几个我在实际项目中总结出来的小技巧都是那种“知道了能省不少事”的经验。第一个是先小后大。不要一上来就拿几百个文件跑先用三五个文件测试脚本确认逻辑没问题、结果正确再扩大到全部文件。我见过有人直接跑全量数据结果因为一个文件的列名不对整个结果都错了还得从头再来。第二个是保留中间结果。合并完成后先把结果存成一个新文件再做后续处理。这样万一后续步骤出错不用重新读取和合并所有文件。df_all.to_excel(output/合并结果.xlsx, indexFalse)第三个是用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正在处理第 {i1}/{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})这样即使某个文件有问题脚本也不会中断而且你能清楚地知道是哪个文件出了问题单独去处理它就行。这些经验看起来简单但都是我在实际工作中一次次踩坑之后才总结出来的。尤其是异常处理那一条早期我写脚本从来不处理异常结果一个文件有问题整个脚本就崩了还得从头跑一遍浪费了大量时间。后来加上异常处理和日志之后脚本的稳定性提升了很多即使有问题也能快速定位。