★ FEATURED ARTICLE Excel 文件散落在文件夹里每个月末做汇总的时候一个个打开、复制、粘贴这种场景我见得太多了。尤其是那种按部门、按门店、按日期拆分的报表一个文件夹里躺着几十个结构相同但数据不同的 xlsx手工合并不仅慢还特别容易漏行、错位、把表头也粘进去。这篇内容就围绕用 Python 快速合并文件夹内 Excel 数据这件事展开把我在实际项目里反复用到的思路、代码和踩过的坑一次讲清楚。不管你是完全没写过代码的办公族还是刚学 Python 想找个练手项目的新手又或者是经常要处理批量报表的分析人员这套方法都能直接拿去用。核心工具就是pandas加上pathlib或os几十行代码就能把一整个文件夹的表格合成一张总表而且能处理表头不一致、列顺序不同、多工作表、空文件这些真实场景里必然遇到的情况。下面我不只给代码更要把每一步为什么这么写、哪里容易翻车讲透让你改一改就能套到自己的数据上。1. 先想清楚合并 Excel 到底在合并什么很多人一上来就找代码复制粘贴结果跑出来数据对不上回头排查半天。问题往往不在代码而在于动手前没想清楚合并这个动作的语义。文件夹里的 Excel 可能长得像但内部结构千差万别先花五分钟把下面几件事确认清楚能省掉后面两小时的调试。1.1 三种常见的合并诉求处理方式完全不同我把实际遇到的合并需求归成三类你可以对号入座纵向堆叠追加行每个文件结构一样只是数据不同比如各门店的销售明细。这种最简单把每个文件读成 DataFrame 后上下拼接即可。横向拼接追加列每个文件记录的是同一批对象的不同指标比如一个文件是销售额、一个是库存量需要按某个主键左右合并。这种要用merge或join主键选错就会产生笛卡尔积式的爆炸。多表汇总跨工作表一个文件里有多个 sheet需要把每个 sheet 的数据都抽出来。这种要遍历 sheet 名而不是只读第一个。绝大多数合并文件夹内 Excel的需求属于第一种纵向堆叠本文也以它为主线但后面会专门讲另外两种怎么处理。先明确你的场景属于哪类再往下看。1.2 表头是否一致决定了代码的复杂度这是最容易被忽略的一点。你以为所有文件第一行都是表头但真实数据里经常出现有的文件第一行是标题比如XX公司2024年销售报表第二行才是字段名有的文件多了几列备注或者列的顺序被打乱有的文件字段名带空格、带换行、大小写不统一比如销售额和销售 额。如果表头不一致还硬拼结果就是列对不齐、出现大量 NaN。所以我的习惯是先写一段探查代码把所有文件的列名打印出来对比确认一致后再正式合并。这一步花不了几分钟但能避免后面反复返工。1.3 数据量和文件数量影响读取策略文件夹里是 5 个文件还是 500 个文件单个文件是几百行还是几十万行处理方式不一样。文件少、数据小怎么读都行文件多、数据大就要考虑内存和速度比如分批读取、只读需要的列、用更快的引擎。后面讲性能优化时会具体说。提示动手前先数一下文件数量、看一眼单个文件大小心里有个底。几百个文件一次性读进内存普通办公电脑可能会卡。2. 环境准备与核心工具选型工具选型这块我不绕弯子直接说结论和理由。处理 Excel 的 Python 库有好几个但组合起来最顺手的还是pandas加一个 Excel 读写引擎。2.1 为什么是 pandas而不是 openpyxl 直接操作openpyxl能直接读写 xlsx功能很全但它是单元格级的操作你要自己循环行、循环列、拼列表代码冗长。pandas是表格级的抽象一个read_excel就把整张表变成 DataFrame拼接、去重、筛选都是一行代码的事。对于合并这种批量操作pandas 的效率和人机工程明显更好。当然 pandas 底层读 xlsx 也要靠引擎默认会用openpyxl。所以安装 pandas 的同时把openpyxl装上这是读写 xlsx 的标配。如果是老的 xls 格式还需要xlrd。pip install pandas openpyxl xlrd三个包各司其职pandas做数据处理openpyxl读写 xlsxxlrd读老式 xls。装完就可以开始了。2.2 用 pathlib 还是 os我选 pathlib遍历文件夹找 Excel传统写法是os.listdir或glob能用但字符串拼接路径很啰嗦。pathlib是 Python 3.4 之后的标准库用面向对象的方式处理路径Path(文件夹).glob(*.xlsx)一行就能拿到所有 xlsx 文件还自带后缀判断、路径拼接代码干净很多。我现在的习惯是能用 pathlib 就不用 os.path。from pathlib import Path folder Path(./报表文件夹) files list(folder.glob(*.xlsx)) print(f找到 {len(files)} 个文件)这段代码先跑一下确认文件数量对不对是排查为什么没读到文件的第一步。2.3 一个容易忽略的细节临时文件与隐藏文件Excel 打开文件时会生成以~$开头的临时文件这些文件不是真正的数据文件读它会报错。另外 macOS 下可能有.DS_Store这类隐藏文件。所以 glob 之后最好过滤一下files [f for f in folder.glob(*.xlsx) if not f.name.startswith(~$)]这个过滤看着不起眼但少了它程序跑到一半突然抛异常排查起来很浪费时间。我第一次遇到时愣了半天后来养成习惯凡是遍历文件必加这层过滤。3. 从零写出可用的合并脚本环境齐了思路清了开始写代码。我会从最简版本讲起再逐步加上真实场景需要的健壮性处理你可以按需取用。3.1 最小可用版本十行代码跑通合并先看最核心的逻辑理解了这个后面都是锦上添花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) frames.append(df) result pd.concat(frames, ignore_indexTrue) result.to_excel(合并结果.xlsx, indexFalse)逐行解释一下为什么这么写frames []先建一个空列表把每个文件读出来的 DataFrame 存进去最后一次性concat。这比在循环里反复append到同一个 DataFrame 要快得多因为 pandas 的 DataFrame 每次拼接都会复制数据循环里拼几百次性能会很差。pd.concat(frames, ignore_indexTrue)里的ignore_indexTrue很关键它让合并后的行索引重新从 0 开始编号。不加的话每个文件的行索引会原样保留结果就是 0、1、2、0、1、2 这样重复后续做筛选、去重会出问题。to_excel(..., indexFalse)的indexFalse是防止把行索引也写进 Excel否则会多出一列莫名其妙的数字。这十行代码能解决大部分结构规整的合并需求。但真实数据往往不规整下面逐个补强。3.2 加上表头行号参数应对第一行不是表头前面说过有些文件第一行是标题真正的字段名在第二行甚至第三行。read_excel的header参数就是干这个的它指定用第几行从 0 开始计数作为列名df pd.read_excel(f, header1) # 用第二行做表头如果各文件的表头行号不一致那就得先探查。我的做法是先读一两个文件看看结构确认统一后再批量处理。如果实在无法统一可以先用headerNone读进来再手动定位表头行但这属于比较极端的情况一般不会遇到。3.3 只读需要的列减少内存占用和读取时间如果原表有几十列而你只需要其中几列用usecols参数指定列名或列索引能显著加快读取速度、降低内存df pd.read_excel(f, usecols[订单号, 门店, 金额, 日期])文件多的时候这个优化效果很明显。注意usecols传列名时如果某个文件缺这一列会报错所以要么确保列名一致要么传列索引如usecols[0, 2, 5]更稳妥但列索引依赖列顺序各文件顺序不一致时又会错位。权衡下来列名一致时用列名不一致时先统一列名再读。3.4 处理多工作表遍历 sheet 而不是只读第一个read_excel默认只读第一个 sheet。如果一个文件里有多个 sheet 都要合并用sheet_nameNone可以一次性读出所有 sheet返回一个字典键是 sheet 名值是 DataFramefor f in files: sheets pd.read_excel(f, sheet_nameNone) for name, df in sheets.items(): df[来源工作表] name # 可选标记数据来源 frames.append(df)加一列来源工作表是个好习惯合并后如果发现某批数据有问题能快速定位是哪个 sheet 来的。这个技巧在处理结构复杂的工作簿时特别有用。3.5 记录来源文件名方便追溯同理纵向合并多个文件时加一列记录数据来自哪个文件排查问题时价值极高for f in files: df pd.read_excel(f) df[来源文件] f.name frames.append(df)别小看这一列合并几十个文件后如果发现某几行数据异常没有来源标记你根本不知道去哪找原始文件。这是我在实际项目里被坑过之后养成的习惯强烈建议加上。4. 真实数据里的坑与对应处理代码能跑通只是开始真实数据会以各种方式给你惊喜。这一节把我踩过的典型坑列出来每个都给出排查思路和解决方案。4.1 列名不一致导致的错位与 NaN最常见的翻车现场文件 A 的列是[订单号, 金额]文件 B 的列是[订单号, 金额(元)]直接 concat 后会出现两列各自有一半是 NaN。排查方法是先把所有文件的列名收集起来对比all_columns set() for f in files: cols pd.read_excel(f, nrows0).columns # nrows0 只读表头很快 all_columns.update(cols) print(all_columns)nrows0这个技巧很实用它只读表头不读数据速度极快适合用来快速探查所有文件的列结构。发现列名不一致后有两种处理方式一是在读取时用rename统一列名二是合并后再做列名映射。我倾向于读取时就统一逻辑更清晰rename_map {金额(元): 金额, 销售 额: 销售额} df pd.read_excel(f).rename(columnsrename_map)4.2 数据类型混乱数字变成文本、日期变成序列号Excel 里同一列可能混着数字和文本pandas 读进来后整列会变成 object 类型后续求和、排序都会出问题。日期更麻烦Excel 内部用序列号存日期读进来可能是数字也可能是 datetime取决于单元格格式。处理思路是合并后统一做类型转换result[金额] pd.to_numeric(result[金额], errorscoerce) result[日期] pd.to_datetime(result[日期], errorscoerce)errorscoerce的作用是把无法转换的值变成 NaN 而不是直接报错这样你能通过检查 NaN 找出哪些数据有问题。转换完记得统计一下 NaN 的数量如果突然多出一大批说明原始数据格式有问题得回头查。4.3 空文件、空表、只有表头的文件文件夹里混进一个空文件或只有表头的文件read_excel可能返回空 DataFrameconcat 时一般不会报错但会引入无意义的空行。稳妥的做法是读取后判断一下df pd.read_excel(f) if df.empty: print(f跳过空文件{f.name}) continue这个判断成本很低但能避免结果里混入脏数据。我在一次批量处理里就遇到过几个只有表头的模板文件加了判断后结果干净很多。4.4 合并后出现重复数据如果文件夹里的文件有重叠比如有人重复放了一份合并后会出现完全重复的行。用drop_duplicates()去重result result.drop_duplicates()但要注意去重前想清楚重复的定义。如果两行所有列都一样那确实是重复但如果只是主键重复而其他列不同可能是正常的多次记录不能盲目去重。我一般会先result.duplicated().sum()看看有多少重复再决定怎么处理。4.5 大文件合并的内存与速度问题文件多、数据大时一次性把所有 DataFrame 放列表里再 concat内存可能扛不住。两个优化方向分批处理每读 N 个文件就 concat 一次并写入临时结果最后再合并临时结果。只读必要列前面说的usecols能砍掉大量无关数据。另外写结果时如果数据量很大to_excel会比较慢可以考虑先存成 csv 或 parquet 做中间结果需要时再转 Excel。不过对大多数办公场景几万到几十万行的数据直接 to_excel 完全够用。5. 让脚本更耐用封装与参数化一次性脚本用完就扔没问题但如果这个合并需求每周都要做就该把它封装成可复用的函数或命令行工具改改参数就能跑。5.1 封装成函数把文件夹路径和输出路径变成参数def merge_excel_folder(folder_path, output_path, header0, usecolsNone): folder 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, headerheader, usecolsusecols) if df.empty: continue df[来源文件] f.name frames.append(df) if not frames: raise ValueError(没有读到任何有效数据) result pd.concat(frames, ignore_indexTrue) result.to_excel(output_path, indexFalse) return result这样封装后换个文件夹、换个表头行号改参数就行不用动逻辑。函数最后返回 result方便调用方继续做后续处理。5.2 加日志和进度提示长任务心里有底文件多的时候跑起来没反应会让人怀疑是不是卡死了。加个简单的进度打印for i, f in enumerate(files, 1): print(f[{i}/{len(files)}] 正在处理{f.name}) ...如果文件特别多可以用tqdm库显示进度条一行代码的事体验提升明显。这类小细节不影响功能但决定了脚本好不好用。5.3 异常处理单个文件出错不该拖垮整个任务某个文件损坏或格式异常时整个脚本崩掉很让人抓狂。用 try-except 把单个文件的处理包起来出错就跳过并记录for f in files: try: df pd.read_excel(f) frames.append(df) except Exception as e: print(f处理失败{f.name}原因{e})这样即使有个别坏文件其他数据照样能合并出来最后看一眼失败列表再单独处理。这个思路在处理来源不明的批量文件时特别重要。6. 横向合并与多表汇总的补充方案前面主线讲的是纵向堆叠这里补上另外两种场景的处理方式让这套方法覆盖更全。6.1 按主键横向合并merge 的用法与陷阱如果每个文件是同一批对象的不同指标需要按主键左右合并。假设文件 A 有[订单号, 金额]文件 B 有[订单号, 库存]用mergemerged pd.merge(df_a, df_b, on订单号, howouter)how参数决定保留哪些行inner只保留两边都有的outer保留全部left/right保留一侧。选错会导致数据丢失或引入大量 NaN。另外要警惕主键重复如果 A 里一个订单号出现多次merge 后行数会翻倍合并前先检查主键唯一性print(df_a[订单号].duplicated().sum())6.2 多文件多 sheet 的笛卡尔式汇总最复杂的场景是多个文件每个文件多个 sheet都要汇总。思路是双层循环外层遍历文件内层遍历 sheet把每个 DataFrame 都加上来源标记后塞进列表最后统一 concat。代码在前面 3.4 已经给过这里强调一点不同 sheet 的列结构可能不同concat 后列会取并集缺失的补 NaN这是正常行为但要心里有数别看到 NaN 就以为出错了。6.3 合并前的数据体检清单不管哪种合并动手前建议过一遍这个清单能挡掉大部分问题检查项检查方法处理方式文件数量对不对len(files)确认 glob 规则和过滤条件列名是否一致nrows0读表头对比rename 统一表头行号是否一致抽查几个文件header 参数指定是否有空文件df.empty判断跳过主键是否唯一duplicated().sum()去重或确认业务含义数据类型是否合理df.dtypesto_numeric / to_datetime这张表我基本每次批量处理都会过一遍养成习惯后很少再出低级错误。7. 几个提升效率的实战心得最后分享几个从实际项目里攒下来的经验都是文档里不太会写、但用起来很实在的东西。第一个心得是先小批量试跑。拿到一个新文件夹别急着全量跑先挑三五个文件跑一遍看看合并结果对不对列有没有错位行数对不对。确认没问题再全量跑能避免大任务跑到一半发现逻辑错了重来。第二个心得是保留中间结果。合并脚本跑完除了输出最终 Excel我习惯把合并后的 DataFrame 也存一份 csv 或 pickle。万一后续分析发现数据有问题不用重新读一遍所有文件直接从中间结果接着处理省时间。第三个心得是输出文件别放在源文件夹里。如果输出路径和源文件夹是同一个下次再跑 glob 时会把上次的输出文件也读进去导致数据重复。要么输出到别的目录要么在 glob 时排除输出文件名。这个坑我踩过一次合并结果莫名其妙多了一倍数据查了半天才发现是把自己上次的输出又读了一遍。第四个心得是列名统一用映射表管理。如果不同来源的文件列名五花八门与其在代码里到处写 if-else不如维护一个rename_map字典集中管理所有别名到标准名的映射代码清爽维护也方便。第五个心得是日期和数字的格式在最后统一处理。合并过程中不要急着转换类型等所有数据拼到一起后再统一to_numeric、to_datetime这样只需要处理一次逻辑也更集中。中途转换容易因为个别文件格式不同而反复出错。这套方法我从最早的十几行脚本逐步加健壮性、加日志、加异常处理最后沉淀成一个能反复用的工具函数。核心其实很简单读文件、拼列表、concat、写结果剩下的都是围绕真实数据的边角处理。你把第 3 节的最小版本跑通再对照第 4 节的坑逐个补上基本就能应付绝大多数文件夹合并 Excel 的需求了。真正花时间的从来不是写代码而是搞清楚数据长什么样、合并后要什么结果这两件事想明白了代码就是水到渠成的事。
阅读完成 · 觉得有帮助？
