Python怎么读取Excel多个Sheet并存入字典
详细介绍在Python中如何读取Excel文件的多个Sheet工作表并存入字典。涵盖使用Pandas的一行核心语法、指定部分Sheet读取、使用openpyxl构建原生字典以及常见避坑要点。
在 Python 中读取 Excel 的多个 Sheet 并存入字典,最直接且高效的方法是使用 pandas 库的 pd.read_excel() 函数,并将参数 sheet_name 设置为 None。这一行设置会告诉 Pandas 遍历工作簿中的全部工作表,并自动返回一个标准字典:工作表名称作为字典的键(Key),对应工作表解析出的 DataFrame 作为字典的值(Value)。如果项目中无需引入 Pandas 依赖,也可以使用轻量库 openpyxl 遍历工作表并按行组织为纯 Python 原生字典。
一、核心方案:使用 Pandas 一行读取全部 Sheet 到字典
在常规数据分析场景中,Pandas 是处理 Excel 表格的首选工具。通常我们调用 pd.read_excel('file.xlsx') 时,它默认只读取第一个工作表。若要读取所有工作表并存入字典,关键就在于传给 sheet_name 参数的值。
import pandas as pd
# 将 sheet_name 设为 None,即可读取所有工作表并返回字典
excel_path = 'data.xlsx'
sheets_dict = pd.read_excel(excel_path, sheet_name=None)
# 验证返回类型及包含的工作表名称
print(f"返回对象类型: {type(sheets_dict)}")
print(f"包含的Sheet名称: {list(sheets_dict.keys())}")
# 通过字典键直接访问指定 Sheet 的 DataFrame
for sheet_name, df in sheets_dict.items():
print(f"--- 工作表: {sheet_name} (行数: {len(df)}, 列数: {len(df.columns)}) ---")
print(df.head(2))
sheet_name 为具体字符串或数字下标时,返回的是单个 DataFrame;当 sheet_name=None 时,返回的是 {str: DataFrame} 字典,囊括了工作簿中的全部可见及隐藏工作表。
二、数据映射逻辑与指定 Sheet 提取
Excel 工作簿本质上是由多个独立的数据网格组成的文件容器,而 Python 字典是基于键值对(Key-Value)的哈希结构。读取过程就是将工作簿的表名提取为键,将表格数据转换为对象作为值的映射过程。
1. 仅读取部分特定 Sheet 并存入字典
如果 Excel 文件包含很多工作表,但业务上只需其中某几个,可以给 sheet_name 传递一个包含工作表名称或索引号的列表。Pandas 同样会返回字典,但只包含列表中声明的表:
# 方式 A:指定表名列表
target_sheets = ['销售明细', '客户信息']
selected_dict = pd.read_excel('data.xlsx', sheet_name=target_sheets)
# 方式 B:指定序号列表(0 代表第一个 Sheet,1 代表第二个 Sheet)
index_dict = pd.read_excel('data.xlsx', sheet_name=[0, 1])
2. 将 DataFrame 字典进一步转为纯 Python 字典
上述方法生成的字典值类型为 pandas.DataFrame。如果后续程序要求纯 Python 原生数据结构(如 JSON 序列化、Web API 传输),可以通过字典推导式配合 to_dict() 完成转换:
# 将各表的 DataFrame 转换为记录列表字典: {Sheet名: [ {列1: 值, 列2: 值}, ... ]}
native_dict = {
name: df.to_dict(orient='records')
for name, df in sheets_dict.items()
}
# 输出转换后的样例结构
print(native_dict['销售明细'][:2])
三、轻量方案:使用 openpyxl 逐表构建自定义字典
在自动化运维、轻量级脚本或受限部署环境中,安装完整的 Pandas 及其二进制依赖可能过重。此时可以使用专门操作 Excel 的 openpyxl 库,直接提取数据并组装成字典。
from openpyxl import load_workbook
# 加载 Excel 文件(read_only=True 可提升读取速度并减少内存占用)
wb = load_workbook('data.xlsx', data_only=True, read_only=True)
excel_dict = {}
# 遍历所有工作表名
for sheet_name in wb.sheetnames:
ws = wb[sheet_name]
# 获取所有行数据
rows = list(ws.iter_rows(values_only=True))
if not rows:
excel_dict[sheet_name] = []
continue
headers = rows[0] # 第一行为表头
sheet_data = []
for row in rows[1:]:
# 表头与当前行数据打包为单条记录字典
row_dict = dict(zip(headers, row))
sheet_data.append(row_dict)
excel_dict[sheet_name] = sheet_data
wb.close()
print(f"成功解析 {len(excel_dict)} 个工作表并存入字典")
四、常见问题排查与避坑指南
在实际工程使用中,读取多工作表常会遇到环境缺失、内存溢出或数据异常等问题,掌握以下排查技巧能够避免代码报错:
1. 缺少读取引擎依赖 (ImportError)
Pandas 本身不直接解析 .xlsx 文件的底层二进制 XML,而是依赖第三方解析器。如果只安装了 pandas,运行 pd.read_excel 可能会报错:
ImportError: Missing optional dependency 'openpyxl'. Use pip or conda to install openpyxl.
解决办法:在终端或虚拟环境中补齐依赖安装:
pip install pandas openpyxl
如果读取的是旧版 .xls 格式(Excel 97-2003),则需要额外安装 xlrd(推荐 pip install xlrd==1.2.0)。
2. 多表全量加载导致的内存溢出
sheet_name=None 会一次性把整个工作簿的所有工作表载入内存。如果一个 Excel 文件有十几个工作表,每个工作表有数十万行数据,极易触发系统的 OOM(内存溢出)。
应对策略:不要使用 sheet_name=None 一次性读取,而是借助 openpyxl 的只读模式获取所有表名后,写一个生成器逐表读取并处理,处理完毕后再加载下一张表:
from openpyxl import load_workbook
import pandas as pd
wb = load_workbook('big_data.xlsx', read_only=True)
for sheet in wb.sheetnames:
# 逐表按需加载,处理完毕后释放
df = pd.read_excel('big_data.xlsx', sheet_name=sheet)
# 进行业务计算或落库...
wb.close()
3. 空单元格 (NaN) 与字段类型问题
Pandas 在读取空单元格时会自动填入浮点型 NaN(Not a Number)。在转为纯字典或转为 JSON 时,NaN 可能会导致序列化失败。可以在转字典前进行清洗:
# 将所有空值替换为 None 或空字符串
cleaned_dict = {
name: df.fillna('').to_dict(orient='records')
for name, df in sheets_dict.items()
}
五、两种方案对比与总结
根据不同项目的运行环境与处理目标,选用合适的技术方案:
| 对比维度 | Pandas 方案 (sheet_name=None) | openpyxl 原生遍历方案 |
|---|---|---|
| 代码简洁度 | 极高(单行即可完成读取和字典打包) | 中等(需遍历工作表和行列并组装) |
| 依赖体积 | 较大(需安装 pandas、numpy 等) | 轻量(仅需安装 openpyxl) |
| 返回值类型 | {str: DataFrame} |
自定义原生 {str: list[dict]} |
| 后续数据分析 | 原生支持分组、过滤、聚合与绘图 | 需手写 Python 原生列表循环处理 |
| 内存控制 | 默认全量载入,大表消耗较高 | 支持 read_only=True 流式遍历 |
总结而言,在 Python 中处理多 Sheet 读取并转存字典,“pd.read_excel('data.xlsx', sheet_name=None)” 是最高效、最通用的标准解法。结合 sheet_name 参数的列表指定、字典推导式的灵活转换以及环境引擎的配置,即可从容应对绝大多数日常 Excel 批量多表处理任务。
本站声明
本文内容仅供参考,不代表本站观点。转载或引用请注明出处。如发现内容问题,请通过联系我们页面反馈。













