Pandas读取Excel所有工作表方法详解

本文深入解析了Pandas读取和写入Excel多工作表的核心技巧与常见陷阱：读取时必须使用`sheet_name=None`（而非"all"或留空）才能一次性获取所有可见工作表组成的字典，需警惕隐藏表被自动跳过、内存暴涨及表名被openpyxl自动修正（如空格变下划线、重名加#后缀）等问题；写入多表则务必通过`with pd.ExcelWriter()`确保安全关闭，避免文件损坏，并强调`engine="openpyxl"`已成为xlsx读写的事实标准；全文以实战痛点为线索，直击新手最易踩坑的“当dict用list索引”“忘记close writer”“引擎兼容错误”等关键细节，帮你稳、准、快地驾驭Excel多表处理。

用 pd.read_excel 读所有 sheet，sheet_name=None 是唯一正解

直接传 sheet_name=None，Pandas 就会返回一个 dict，key 是 sheet 名，value 是对应 DataFrame。别试 sheet_name="all" 或留空——那俩都不认，会报 ValueError: Worksheet named 'all' not found 或默认只读第一个表。

• sheet_name=None 返回 dict，不是 list，别用索引取 df[0]，得用 df["Sheet1"]
• 如果 Excel 里有隐藏表（hidden sheet），它**不会被读取**，Pandas 默认跳过
• 文件大、sheet 多时，sheet_name=None 会一次性全加载进内存，卡顿明显；真要处理上百个 sheet，得考虑分批读或换 openpyxl 手动迭代

pd.ExcelWriter 写多表必须用 with 语句或手动 close()

写多个 sheet 到同一个 Excel 文件，靠 pd.ExcelWriter 实例的 to_excel(..., sheet_name=...) 方法。但不关掉 writer，文件会损坏——打开提示“发现部分内容有问题”，或者只看到最后一个 sheet。

• 推荐写法：

  with pd.ExcelWriter("out.xlsx") as writer:
      df1.to_excel(writer, sheet_name="data")
      df2.to_excel(writer, sheet_name="summary")

• 不用 with 的话，必须显式调 writer.close()，del writer 不管用
• 不能重复用同一个 sheet_name，否则后写入的会覆盖前一个；也不支持追加写（append mode），想加新 sheet 只能先读原文件再重建 writer

读写时指定引擎影响兼容性和功能

read_excel 和 ExcelWriter 默认用 openpyxl（.xlsx）或 xlrd（旧版 .xls），但 xlrd 从 2.0 版起**彻底不支持 .xlsx**，只认 .xls。现在主流是统一用 openpyxl，尤其写文件时必须显式指定。

• 读 xlsx：默认可用，但显式写 engine="openpyxl" 更稳，避免环境里混装了旧 xlrd
• 写文件：必须加 engine="openpyxl"，否则 pd.ExcelWriter 在新 Pandas 上会报 KeyError: 'xlsx'
• 如果要读带公式结果的单元格（非公式文本），openpyxl 没问题；但想读公式本身，得用 openpyxl.load_workbook(..., data_only=False)，Pandas 层不暴露这个开关

表名含空格、特殊字符或重复时的处理

Excel 表名里有空格、括号、中文甚至重名（比如两个 “Sheet1”），Pandas 读出来会自动改名：加 #1、#2 后缀，或把空格转成下划线。这不是 bug，是 openpyxl 底层行为，Pandas 照单全收。

• 读取后检查 df.keys()，别硬编码 df["Sheet 1"] —— 实际可能是 df["Sheet_1"] 或 df["Sheet 1#1"]
• 写入时如果 sheet_name="销售数据(2024)"，openpyxl 允许，但 Excel 打开可能警告“名称无效”，建议用 re.sub(r"[^\w\s-]", "_", name) 预清洗
• 表名超 31 字符会被截断，且末尾的空格会被 trim，这些在读写前后都不可逆

今天带大家了解了的相关知识，希望对你有所帮助；关于文章的技术知识我们会一点点深入介绍，欢迎大家关注golang学习网公众号，一起学习编程~