Python合并文件夹Excel实战：pandas+openpyxl高效处理多表

1. 为什么“合并文件夹里的Excel”值得单独拿出来讲手头有一堆结构相似的Excel文件散落在同一个文件夹里需要把它们拼成一张总表——这件事听起来简单但真正动手做过的人都知道它属于那种“第一次做花两小时第二次做还想花两小时”的重复劳动。尤其是当文件数量从三五个涨到三五十个或者每个月都要来一遍的时候手动复制粘贴不仅低效还特别容易出错漏掉一个文件、表头多粘了一行、列顺序对不上、日期格式变成一串数字这些坑几乎每个和数据打交道的人都踩过。用Python来做这件事核心价值不在于“炫技”而在于把一次性劳动变成可复用的工具。写一次脚本以后每次只需要改一下文件夹路径几秒钟就能拿到结果而且每次的合并逻辑完全一致不会因为手抖产生差异。这篇内容面向的是有一定Excel使用经验、但Python基础可能并不深的读者——你不需要懂什么高级语法只要能看懂基本的变量和循环就能跟着把整套流程跑通。文中会从思路设计讲到具体实现再到实际会遇到的坑和排查方法尽量做到看完就能直接抄作业。需要先说明一点合并Excel这件事没有唯一正确的写法不同的数据形态对应不同的策略。有的文件夹里所有表结构完全一致那是最省事的情况有的表列名相同但顺序不同还有的每个月多一列少一列。下面我会按照从简单到复杂的顺序把几种典型场景的处理方式都过一遍你可以根据自己的实际情况对号入座。2. 整体思路设计与方案选型2.1 先想清楚“合并”到底合的是什么很多人一上来就写代码结果写到一半发现数据对不上回头返工。我的习惯是先把合并规则想明白再动手。所谓“合并文件夹内的Excel”本质上要回答三个问题第一合并哪些文件——是文件夹下所有xlsx还是只合并名字里带特定关键词的第二合并哪些工作表——每个文件只取第一个sheet还是所有sheet都要第三怎么对齐列——是按列名匹配还是按列的位置硬拼。这三个问题的答案直接决定了代码的写法。举个最常见的场景每个月各部门交上来的报表文件名类似“某部门-某月份.xlsx”每个文件第一个sheet的结构完全一样第一行是表头。这种情况下最稳妥的做法是按列名对齐而不是按位置。原因很简单不同人做表的时候很可能把“金额”列放在“日期”列前面或者后面如果按位置拼数据就会错位而且这种错误非常隐蔽合并完不仔细看根本发现不了。按列名对齐虽然代码稍微多几行但能避免这类灾难性问题。还有一个容易被忽略的点要不要保留来源信息。合并之后你往往需要知道某一行数据来自哪个文件。我的做法是额外加一列“来源文件”把文件名写进去。这一列在后续核对数据、追溯问题时特别有用成本几乎为零强烈建议加上。2.2 工具选型pandas openpyxl 是性价比最高的组合处理Excel的Python库有好几个常见的有pandas、openpyxl、xlrd、xlwings。这里说一下我的选择逻辑。pandas负责数据的读取、拼接和写出它底层读取xlsx靠的是openpyxl这个引擎所以实际上两个库是一起用的。openpyxl单独用也能读写Excel但它的定位是“操作Excel文件本身”比如改单元格样式、插图表做数据合并这种批量表格运算pandas的DataFrame才是顺手的工具。为什么不选xlwings它需要本机安装Excel软件适合做自动化操作Excel界面的场景纯数据处理用不上而且速度偏慢。xlrd老版本只支持xls新版本对xlsx的支持也有限现在基本被openpyxl取代了。所以结论很明确pandas读数据、拼数据openpyxl做引擎需要精细控制格式时再单独调openpyxl。安装就一行命令pip install pandas openpyxl如果你的环境里pandas版本比较老建议顺手升级一下新版本对Excel的兼容性和读取速度都有改善。这里不需要装一堆乱七八糟的库两个足够。2.3 目录遍历方式的选择遍历文件夹里的文件最直接的是用os.listdir()但它会把所有文件包括临时文件、隐藏文件都列出来还得自己过滤扩展名。我更推荐用pathlib它是Python标准库里比较现代的文件路径处理模块写起来干净跨平台也没问题。用Path(folder).glob(*.xlsx)就能拿到所有xlsx文件还能用glob(**/*.xlsx)递归子文件夹需要的时候很方便。有一个细节要提醒用glob匹配时以~$开头的临时文件会被漏掉还是被匹配到取决于你的写法。Excel打开文件时会生成~$xxx.xlsx这样的临时锁文件如果被误读进来pandas会报错或者读出空表。所以过滤的时候要么排除以~$开头的文件要么在读取时加异常处理。这个坑后面还会细说。3. 核心细节解析与实操要点3.1 读取环节表头、sheet和数据类型读取每个文件时pd.read_excel()有几个参数值得关注。sheet_name默认是0也就是第一个sheet如果你的数据都在第一个sheet保持默认即可如果每个文件有多个sheet且都要合并就传sheet_nameNone它会返回一个字典键是sheet名值是DataFrame后续再遍历字典拼接。header参数默认是0表示第一行是表头绝大多数报表都符合这个约定但如果你的文件第一行是标题、第二行才是表头就要改成header1。数据类型这块是重灾区。pandas读取时会自动推断类型但推断结果不一定符合预期。最典型的是编号类字段像工号、订单号这种纯数字但实际是文本的列pandas会读成整数导致前面的0丢失比如“00123”变成123。解决办法是在读取时用dtypestr强制按文本读或者读完之后对特定列做astype(str)转换。另一个常见问题是日期Excel里的日期在pandas里可能读成datetime也可能读成一串数字Excel的序列号这取决于单元格的实际存储格式。如果合并后发现日期不对先检查源文件的单元格格式是不是“文本”是的话就得在读取后手动转换。还有一个实操中总结出来的经验读取时加上engineopenpyxl显式指定引擎。虽然pandas通常能自动判断但在某些环境下自动判断会出问题显式指定能减少不确定性。代码大概长这样import pandas as pd from pathlib import Path folder Path(你的文件夹路径) files [f for f in folder.glob(*.xlsx) if not f.name.startswith(~$)] frames [] for f in files: df pd.read_excel(f, engineopenpyxl, dtypestr) df[来源文件] f.name frames.append(df)注意这里dtypestr把所有列都按文本读适合以核对、汇总为主的场景。如果你后续要做数值计算就不能全转文本得针对性地处理。3.2 拼接环节concat的对齐逻辑拿到一堆DataFrame之后用pd.concat(frames, ignore_indexTrue)把它们摞起来。ignore_indexTrue的作用是重新生成行索引避免每个文件的行号重复。这里的关键在于concat默认按列名对齐列名相同的列会合并到一起某个文件多出来的列会在结果里保留缺失的位置填NaN。这个行为正好符合我们前面说的“按列名对齐”策略所以大多数情况下不需要额外处理。但有两种情况需要留意。第一种是列名有细微差异比如一个文件叫“金额”另一个叫“金额元”concat会认为是两列结果就是两列并存、各自一半是空值。这种问题在合并前最好先统一列名可以用df.rename(columns{...})做映射。第二种是列顺序不一致这个concat能自动处理不用担心但合并完之后建议用df df[期望的列顺序]重新排一下让结果表更规整。如果文件夹里的表结构差异很大比如有的表有十几列、有的只有五六列那就要想清楚是保留所有列结果会有大量空值还是只取公共列用pd.concat之前先取列交集。我的经验是如果差异在可接受范围内保留所有列更安全因为多出来的列往往包含有用信息如果差异大到结果表一半是空值那说明这批文件本来就不该混在一起合并应该先分类。3.3 写出环节格式与性能的权衡合并完写出用df.to_excel(合并结果.xlsx, indexFalse)。indexFalse是必须的否则会把行索引也写进去多出一列没用的数字。如果数据量比较大几万行以上写xlsx会明显变慢因为xlsx本质是个压缩包写入需要序列化。这时候可以考虑先写csv做中间结果需要的时候再转xlsx或者用xlsxwriter引擎它在写大文件时比openpyxl快一些但要注意xlsxwriter只能写不能读。还有一个细节写出时如果目标文件已经存在且正被Excel打开会报权限错误。这个错误很常见排查时第一反应应该是“是不是有个Excel窗口没关”。代码里可以加个try-except给出友好提示但根本上还是操作习惯问题——跑脚本之前先把结果文件关掉。4. 完整实操过程与关键环节实现4.1 从零跑通一个最小可用版本先把最基础的版本跑通再逐步加功能。下面这段代码是完整可运行的你只需要把folder改成自己的路径import pandas as pd from pathlib import Path folder Path(rC:\你的\数据文件夹) files sorted(f for f in folder.glob(*.xlsx) if not f.name.startswith(~$)) print(f共找到 {len(files)} 个文件) frames [] for f in files: try: df pd.read_excel(f, engineopenpyxl, dtypestr) df[来源文件] f.name frames.append(df) print(f已读取{f.name}{len(df)} 行) except Exception as e: print(f读取失败{f.name}原因{e}) if frames: result pd.concat(frames, ignore_indexTrue) result.to_excel(folder / 合并结果.xlsx, indexFalse) print(f合并完成共 {len(result)} 行{len(result.columns)} 列) else: print(没有可合并的文件)这段代码里几个设计点解释一下。sorted()是为了让文件按名字排序后再合并这样结果的行顺序是确定的方便复现和核对如果不排序glob返回的顺序依赖文件系统不同机器上可能不一样。try-except包住读取过程是为了单个文件出问题时不影响整体同时把失败原因打印出来方便定位。dtypestr前面说过了是为了防止编号类字段丢前导零。最后判断frames非空再合并避免文件夹为空时报错。跑通之后你会得到一个带“来源文件”列的总表。这时候先别急着用花两分钟做几项检查总行数是不是等于各文件行数之和用打印出来的行数加一下、列名是不是符合预期、随便挑几行对照源文件看看数据有没有错位。这几步检查花不了多少时间但能帮你及早发现对齐问题。4.2 处理多sheet和指定列的场景如果每个文件有多个sheet都要合并把读取部分改成这样for f in files: sheets pd.read_excel(f, engineopenpyxl, dtypestr, sheet_nameNone) for sheet_name, df in sheets.items(): df[来源文件] f.name df[来源Sheet] sheet_name frames.append(df)sheet_nameNone返回字典遍历字典就能拿到每个sheet。加一列“来源Sheet”记录sheet名追溯时更有用。注意这种情况下不同sheet的列结构可能差异很大合并前最好先打印一下每个sheet的列名确认是否适合放在一起。如果只需要合并特定的几列可以在读取后用df df[[列A, 列B, 列C]]筛选。这里有个小技巧先检查列是否存在再筛选否则某个文件缺列会直接报KeyError。稳妥的写法是need_cols [日期, 部门, 金额] df df[[c for c in need_cols if c in df.columns]]这样缺列时不会崩只是结果里少那一列配合前面的打印信息能快速发现哪个文件缺列。4.3 参数选择背后的计算与判断有人会问几万个文件或者几十万行数据这套流程扛得住吗。粗略估算一下单个xlsx文件读取耗时通常在几十毫秒到几百毫秒取决于文件大小和复杂度concat拼接是内存操作几十万行的拼接通常在秒级完成写出xlsx是瓶颈十万行左右可能要十几秒到几十秒。所以对于日常办公场景几十到几百个文件、总行数几万以内这套方案完全够用不需要上什么并行处理。真正需要优化的是内存。如果每个文件都很大把所有DataFrame攒在列表里再一次性concat内存峰值会比较高。这时候可以改成边读边写先创建一个空的ExcelWriter每读一个文件就追加写入。但追加写入xlsx本身效率不高更实际的做法是分批concat比如每50个文件合并一次写一个中间文件最后再合并中间文件。不过说实话除非你的数据量真的到了几十万行以上否则不用折腾这个先把简单方案用起来。5. 常见问题与排查技巧实录5.1 高频报错速查表下面这张表是我在实际操作中整理出来的常见问题按出现频率排序遇到报错可以先对照着看现象可能原因解决思路PermissionError结果文件正被Excel打开关闭Excel窗口后重跑读取出空表或报错误读了~$开头的临时文件过滤文件名前缀编号前导零丢失被推断为数值类型读取时加dtypestr列对不齐、数据错位按位置拼接而非按列名用concat按列名对齐合并前统一列名日期变成数字源单元格是文本格式读取后手动转换或统一源格式合并后行数对不上有文件读取失败被跳过看打印的失败信息逐个排查列名带空格导致匹配失败表头有不可见字符用df.columns df.columns.str.strip()清理这张表里最值得展开说的是“列对不齐”和“行数对不上”这两个。列对不齐往往是静默发生的程序不报错但数据已经错了所以合并后一定要抽查。行数对不上则通常是某个文件读取失败前面的try-except会把原因打出来顺着看就行。5.2 几个容易踩的坑和独家技巧第一个坑是表头行不统一。有的文件第一行是表头有的文件上面还有一行标题表头在第二行。这种混合情况如果统一用header0读后者就会把标题行当表头列名全乱。处理办法是先探测读每个文件时用headerNone读前几行判断哪一行像表头比如包含“日期”“金额”这类关键词再决定header参数。这个逻辑稍微复杂点但如果你的数据源确实混乱值得写。第二个坑是合并结果里的空值。按列名对齐时某个文件缺的列会填NaN这在结果里表现为空单元格。如果后续要做统计NaN会影响计算结果记得用fillna()或者按业务逻辑填充。但要注意不要无脑fillna(0)金额列填0和填空在业务上含义完全不同这个得根据实际情况判断。第三个技巧是给合并结果加一个校验列。比如在读取时记录每个文件的行数合并后按“来源文件”分组统计行数和读取时记录的数字对比不一致就说明有问题。这个校验逻辑几行代码就能加上对于需要保证数据完整性的场景非常值得。check result.groupby(来源文件).size() print(check)打印出来和读取时的行数对一遍心里就有底了。5.3 性能与稳定性的经验之谈跑批量任务时我习惯在循环里加进度打印哪怕只是简单的“已处理 x/n”。文件多了之后没有进度提示会让人不知道程序是卡住了还是在正常跑。另外先拿两三个文件做小样本测试确认合并逻辑正确、列对齐没问题再放开跑全量。这个习惯能省下大量返工时间——全量跑完发现列错了比小样本发现要痛苦得多。还有一点关于文件命名的建议结果文件不要和源文件放在同一个会被glob匹配到的位置否则下次跑的时候可能把上次的结果也读进去。要么把结果写到子文件夹要么在glob时排除结果文件名。这个细节很小但确实有人踩过。6. 把这套流程变成可复用的日常工具脚本跑通之后下一步是让它更好用。最直接的做法是把文件夹路径、需要的列、结果文件名这些做成变量放在开头每次用的时候只改这几行。再进一步可以用argparse做成命令行工具跑的时候传参数不用改代码。不过对于个人日常使用改几行变量已经足够了不必过度工程化。我个人在实际操作中的体会是这类脚本的价值会随着使用频率上升而放大。第一次写可能花半小时但之后每次合并都只要几秒钟而且结果的一致性有保证。更重要的是写脚本的过程逼着你去想清楚数据的结构和对齐规则这个思考本身对后续的数据处理工作也有帮助。踩过几次坑之后你会发现真正花时间的往往不是写代码而是搞清楚“这批表到底该怎么合”代码只是把想清楚的规则固化下来而已。