Python批量合并Excel表格技巧

本文深入解析了Python中用pandas高效合并多个Excel工作表的核心技巧与常见陷阱：从纠正默认只读首张表的误解，到正确使用`sheet_name=None`批量读取并保留sheet名称；详解如何通过`assign()`和`pd.concat()`无缝注入来源标识、避免索引混乱；揭秘`to_excel`多表写入必须依赖`ExcelWriter`上下文管理器，防止“Workbook already closed”错误；并直面大文件内存瓶颈，提供CSV中转、`calamine`引擎、列名预检等实战方案，最终引导读者理性评估——当数据规模上升时，CSV、Parquet或DuckDB往往比硬扛Excel更可靠、更高效。

用pandas.read_excel读取多个sheet时，为什么只拿到第一个？

默认情况下，pd.read_excel() 只读取第一个工作表（sheet_name=0），这不是bug，是设计行为。想读全部，必须显式传参：sheet_name=None——它会返回一个 dict，key 是 sheet 名，value 是对应 DataFrame。

常见错误是写成 sheet_name='all' 或 sheet_name=True，这些都不合法，会直接报错 ValueError: sheet_name 'all' is not available。

实操建议：

• 用 pd.read_excel(file_path, sheet_name=None) 一次性加载全部 sheet
• 如果只关心特定几个，传列表更高效：例如 sheet_name=['Sales', 'Marketing']
• 注意内存：大 Excel 文件含几十个 sheet 时，sheet_name=None 可能触发 OOM，建议先用 pd.ExcelFile(file_path).sheet_names 查看并筛选

concat多个DataFrame时，如何保留原sheet名作为新列？

直接 pd.concat(dfs) 会丢掉来源信息，后续无法区分哪行来自哪个 sheet。解决办法是在拼接前，给每个 DataFrame 加一列标识，比如 source_sheet。

不要用循环反复 df['source_sheet'] = name 后再 concat，容易因索引重复或列顺序不一致导致对齐错乱。

实操建议：

• 用字典推导式 + pd.concat 一步到位：

  all_sheets = pd.read_excel('data.xlsx', sheet_name=None)
  dfs_with_name = [df.assign(source_sheet=name) for name, df in all_sheets.items()]
  combined = pd.concat(dfs_with_name, ignore_index=True)

• 如果原始 sheet 中有同名列（如都叫 ID），concat 不会报错但可能掩盖数据问题，建议提前检查：set.union(*[set(df.columns) for df in all_sheets.values()])
• ignore_index=True 很关键，否则拼接后索引会重复（每个 sheet 都从 0 开始），影响后续去重或切片

to_excel写入多sheet时，为什么报错“Workbook already closed”？

典型场景：想把合并后的数据和原始各 sheet 分别写进同一个 Excel 的不同 tab，但写了类似 df.to_excel('out.xlsx', sheet_name='combined') 再写一次 df1.to_excel('out.xlsx', sheet_name='raw_1')，第二步就崩了。

原因：每次调用 to_excel 都会新建并关闭一个 ExcelWriter 对象，第二次写入时文件已被关闭，且路径冲突。

实操建议：

• 必须用 pd.ExcelWriter 上下文管理器统一写入：

  with pd.ExcelWriter('output.xlsx') as writer:
      combined.to_excel(writer, sheet_name='Combined', index=False)
      for name, df in all_sheets.items():
          df.to_excel(writer, sheet_name=name[:31], index=False)  # Excel sheet名最长31字符

• sheet_name 超长会直接报错 InvalidWorksheetName，务必截断或映射（如用哈希缩写）
• 如果要追加写入已有 Excel（非覆盖），不能用 ExcelWriter 默认模式；需用 openpyxl 手动加载，pandas 本身不支持追加

合并大文件时内存暴涨甚至卡死，怎么缓解？

当单个 sheet 就有几十万行，或总 sheet 数超 20 个时，read_excel(..., sheet_name=None) 会把所有内容载入内存，极易触发 MemoryError。

根本矛盾在于：Excel 是行列混合格式，pandas 为保持结构必须全量解析；而 CSV 没这负担。所以不是参数调优能彻底解决的。

实操建议：

• 优先转中间格式：用 openpyxl 或 xlsx2csv 工具先把 Excel 拆成多个 CSV，再用 pd.read_csv 流式读取 + chunksize 处理
• 若必须原格式处理，改用 engine='odf'（仅限 .ods）或 engine='calamine'（新版 pandas 2.1+ 支持，比 openpyxl 快且省内存）
• 避免无意义操作：比如 df.fillna('') 在合并前执行，会把大量 NaN 转成字符串，显著增加内存占用

真正难的不是语法，是判断该不该用 Excel 做这事——如果最终目标只是查数、统计、喂模型，CSV + DuckDB 或 Parquet 几乎总是更稳的选择。

到这里，我们也就讲完了《Python批量合并Excel表格技巧》的内容了。个人认为，基础知识的学习和巩固，是为了更好的将其运用到项目中，欢迎关注golang学习网公众号，带你了解更多关于的知识点！