---

Pandas 读取 Excel 最全攻略 (2024最新版)：从入门到精通

前言：为何 Pandas 是 Excel 的“天作之合”？

在数据分析的江湖里，Excel 凭借其直观的界面和强大的功能，至今仍是企业中最普及的数据处理工具。然而，当数据量激增，或需要进行复杂的清洗、转换和分析时，Excel 的手动操作便显得力不从心。此时，Python 的 Pandas 库横空出世，以其强大的数据处理能力和编程的灵活性，成为了数据科学家和分析师的瑞士军刀。

Pandas 与 Excel 的结合，是自动化与便捷性的完美联姻。通过 Pandas，我们可以用几行代码完成过去需要数小时手动操作的 Excel 任务。pandas.read_excel() 函数正是连接这两个世界的关键桥梁。它功能之强大、参数之丰富，远超许多人的想象。

本篇攻略（2024最新版）将带你深入探索 pd.read_excel() 的每一个角落，从最基础的文件读取，到精细化的数据筛选、类型控制，再到处理超大文件的性能优化，以及应对各种“奇葩”格式的实战技巧。无论你是初学者还是有经验的开发者，相信这篇详尽的指南都能让你对 Pandas 读取 Excel 的能力有一个全新的、颠覆性的认识。

第一章：基础入门与环境准备

在开始之前，请确保你已经安装了 Pandas 以及它读取 Excel 文件所依赖的“引擎”。

1.1 安装核心库

首先，安装 Pandas：
bash
pip install pandas

1.2 安装 Excel 读写引擎

Pandas 本身不直接解析 Excel 文件，而是通过第三方库作为“引擎”来完成。你需要根据要处理的 Excel 文件类型（.xls 或 .xlsx）安装相应的引擎。

• openpyxl: 用于读取和写入 .xlsx 文件（Excel 2007+ 格式）。这是目前推荐和默认的引擎。
  bash
pip install openpyxl
• xlrd: 曾是读取 .xls 和 .xlsx 的主流库。重要更新：自 xlrd 2.0.0 版本后，它不再支持读取 .xlsx 文件，仅支持旧版的 .xls 文件。如果你需要处理 .xlsx，请务必使用 openpyxl。
  bash
pip install xlrd
• pyxlsb: 用于读取二进制格式的 .xlsb 文件，这种格式在处理含有大量宏或数据的超大文件时较为常见。
  bash
pip install pyxlsb

2024年最佳实践：同时安装 openpyxl 和 xlrd，这样你的 Pandas 就能通吃 .xlsx 和 .xls 两种格式的文件。

1.3 最简单的读取操作

假设我们有一个名为 sales_data.xlsx 的文件，其内容如下：

OrderDate	Region	Rep	Item	Units	Unit Cost	Total
2023-01-06	East	Jones	Pencil	95	1.99	189.05
2023-01-23	Central	Kivell	Binder	50	19.99	999.50
2023-02-09	Central	Jardine	Pencil	36	4.99	179.64
2023-02-26	Central	Gill	Pen	27	19.99	539.73
2023-03-15	West	Sorvino	Pencil	56	2.99	167.44

用 Pandas 读取它，只需一行代码：

“`python
import pandas as pd

读取 Excel 文件

df = pd.read_excel(‘sales_data.xlsx’)

打印 DataFrame 的前5行

print(df.head())
“`

这行代码背后，Pandas 默默地完成了许多工作：
1. 自动推断文件类型并选择合适的引擎（openpyxl for .xlsx）。
2. 默认读取第一个工作表（Sheet）。
3. 默认将文件的第一行作为列名（Header）。
4. 自动为每一行创建从0开始的整数索引。
5. 尝试推断每一列的数据类型（如日期、文本、数字）。

第二章：read_excel() 核心参数详解

pd.read_excel() 的强大之处在于其丰富的参数，它们能让你像做“外科手术”一样精确地控制数据读取过程。

python
pandas.read_excel(
io,
sheet_name=0,
header=0,
names=None,
index_col=None,
usecols=None,
dtype=None,
engine=None,
converters=None,
skiprows=None,
skipfooter=0,
nrows=None,
na_values=None,
keep_default_na=True,
parse_dates=False,
... # 还有其他参数，但以上为最核心部分
)

下面我们来逐一剖析这些关键参数。

2.1 文件与工作表选择 (io & sheet_name)

• io: 指定文件来源。

  • 本地路径: 最常见，如 'data/sales.xlsx'。
  • URL:可以直接读取网络上的 Excel 文件。
    python
url = 'https://github.com/pandas-dev/pandas/raw/main/doc/data/air_quality_no2.xlsx'
df_from_url = pd.read_excel(url, engine='openpyxl')
  • 文件对象: 可以读取已在 Python 中打开的文件对象。

• sheet_name: 指定要读取哪个或哪些工作表（Sheet）。这是除 io 外最重要的参数。

  • 整数: sheet_name=0 读取第一个，sheet_name=1 读取第二个，以此类推（索引从0开始）。
  • 字符串: sheet_name='Sales_Q1' 直接通过工作表名称读取。
  • 列表: sheet_name=['Sales_Q1', 'Sales_Q2'] 或 sheet_name=[0, 2] 可以同时读取多个指定的工作表。返回一个字典，键是工作表名/索引，值是对应的 DataFrame。
    python
all_sheets_dict = pd.read_excel('sales_data_multi_sheet.xlsx', sheet_name=['Q1_Sales', 'Q2_Sales'])
df_q1 = all_sheets_dict['Q1_Sales']
df_q2 = all_sheets_dict['Q2_Sales']
  • None: sheet_name=None 读取所有工作表。同样返回一个字典。这是批量处理多 Sheet 文件的利器。
    python
all_sheets = pd.read_excel('sales_data_multi_sheet.xlsx', sheet_name=None)
# 合并所有 sheet 到一个 DataFrame
combined_df = pd.concat(all_sheets.values(), ignore_index=True)

2.2 定义表头与索引 (header & index_col)

• header: 指定哪一行作为列名。

  • header=0 (默认): 第一行是列名。
  • header=None: 文件没有列名行。Pandas 会自动生成从0开始的整数列名。
  • header=2: 第三行是列名（索引从0开始）。
  • header=[0, 1]: 指定多行作为多级索引（MultiIndex）的列名。
    python
# 假设第1行和第2行共同构成列名
df_multi_header = pd.read_excel('multi_header.xlsx', header=[0, 1])

• names: 如果原始文件没有表头（header=None），或者你想在读取时直接重命名列，可以使用此参数提供一个列名列表。
  python
# 假设文件无表头，我们手动提供列名
column_names = ['Date', 'Zone', 'Agent', 'Product', 'Quantity', 'Price', 'Revenue']
df_no_header = pd.read_excel('sales_no_header.xlsx', header=None, names=column_names)

• index_col: 指定哪一列作为 DataFrame 的行索引。

  • index_col=0: 第一列作为索引。
  • index_col='OrderDate': 名为 ‘OrderDate’ 的列作为索引。
  • index_col=[0, 1]: 将第一列和第二列共同作为多级行索引。
  • index_col=None (默认): 不使用任何列做索引，自动创建从0开始的整数索引。

2.3 数据筛选与范围控制 (usecols, skiprows, nrows, skipfooter)

这些参数对于处理大型、不规整的 Excel 文件至关重要，能极大提升读取效率和数据整洁度。

• usecols: 指定要读取的列。这是性能优化的关键，因为 Pandas 只会解析你需要的列。

  • 列表: usecols=[0, 2, 4] (按索引) 或 usecols=['OrderDate', 'Rep', 'Units'] (按名称)。
  • Excel范围字符串: usecols='A,C:E'，非常直观，表示读取A列，以及C到E列。
  • 可调用函数: usecols=lambda x: x.startswith('Rep')，可以传入一个函数，对每一列名进行判断，只读取返回 True 的列。

• skiprows: 跳过文件顶部的指定行数。

  • 整数: skiprows=5 跳过前5行。
  • 列表: skiprows=[0, 2, 4] 跳过第1、3、5行。
  • 可调用函数: skiprows=lambda x: x % 2 != 0，可以跳过所有奇数行。

• nrows: 只读取指定的行数。非常适合在正式处理前，先快速预览大文件的结构。
  python
# 只读取大文件的前100行数据进行探索
df_preview = pd.read_excel('very_large_file.xlsx', nrows=100)

• skipfooter: 跳过文件底部的指定行数。适用于处理底部有总计、备注等无关信息的报表。

组合使用范例（处理不规整报表）:
假设一个 Excel 文件，前3行是标题和空行，最后2行是签名和备注，我们只需要 “Date”, “Sales”, “Region” 三列数据。

python
df_clean = pd.read_excel(
'messy_report.xlsx',
sheet_name='MonthlySummary',
skiprows=3, # 跳过顶部的标题和空行
skipfooter=2, # 忽略底部的签名和备注
usecols='B,D,F', # 假设这三列是我们需要的数据
header=0, # 第4行（跳过后）是表头
names=['Date', 'Sales', 'Region'] # 读取后立即重命名
)

2.4 数据类型与解析 (dtype, converters, parse_dates)

• dtype: 强制指定列的数据类型。这对于防止 Pandas 自动类型推断错误至关重要。

  • 场景1：保持ID、邮编等的前导零
    python
# 如果 'ZipCode' 列有 '07734' 这样的值，Pandas 可能会将其读成整数 7734
# 使用 dtype=str 可以保证其为文本格式
df_zip = pd.read_excel('customers.xlsx', dtype={'CustomerID': str, 'ZipCode': str})
  • 场景2：性能优化
    对于非常大的文件，预先指定 dtype 可以避免 Pandas 逐行推断类型，从而显著加快读取速度并减少内存占用。

• converters: 更强大的自定义类型转换工具。它是一个字典，键是列名/索引，值是一个函数，该函数会应用到该列的每一个单元格上。
  “`python
  # 假设 ‘Price’ 列是 ‘$ 123.45’ 格式，我们需要将其转为浮点数
  def clean_price(price_str):
  if isinstance(price_str, str):
  return float(price_str.replace(‘$’, ”).replace(‘,’, ”).strip())
  return price_str

  df_converted = pd.read_excel(‘products.xlsx’, converters={‘Price’: clean_price})
  “`

• parse_dates: 智能地将一列或多列解析为日期时间对象。

  • 列表: parse_dates=['OrderDate', 'ShipDate'] 将这两列解析为 datetime。
  • 列表的列表: parse_dates=[['Year', 'Month', 'Day']] 会将 ‘Year’, ‘Month’, ‘Day’ 三列合并，并解析成一个名为 ‘Year_Month_Day’ 的新日期时间列。
  • 字典: parse_dates={'FullDate': ['Year', 'Month', 'Day']}，效果同上，但可以自定义新列的名称。

2.5 处理缺失值 (na_values & keep_default_na)

• na_values: 指定哪些值在读取时应该被视为空值（NaN）。

  • 单个值: na_values='N/A'
  • 列表: na_values=['N/A', 'Not Available', -1]
  • 字典: na_values={'Region': ['Unknown'], 'Units': [-1]}，可以为不同的列指定不同的空值标记。

• keep_default_na: 默认为 True，表示除了你用 na_values 指定的值外，Pandas 默认的空值集合（如 #N/A, NULL, NaN, '' 等）依然有效。如果你想完全自定义空值，可以设为 False。

第三章：性能优化与大文件处理

当 Excel 文件大小达到几百MB甚至GB级别时，直接用 pd.read_excel() 可能会导致内存溢出（MemoryError）或长时间等待。以下是决胜大文件的关键策略。

3.1 核心三板斧：usecols, dtype, engine

• usecols: 前文已述，只读取必要的列是减少内存占用的第一道防线。
• dtype: 预先指定类型，特别是将数值列降级（如float64 -> float32）或使用更高效的类型（如category），能大幅减少内存占用。
• engine:
  • 对于 .xlsx 文件，openpyxl 是标准选择。
  • 对于 .xlsb (二进制) 文件，它们通常比 .xlsx 更小、读取更快，此时务必指定 engine='pyxlsb'。

3.2 终极武器：分块读取 (chunksize)

如果文件实在太大，无法一次性装入内存，chunksize 参数就是你的救星。它不会一次性返回一个完整的 DataFrame，而是返回一个迭代器（iterator）。你可以通过循环来逐块处理数据。

“`python

假设有一个 2GB 的 Excel 文件

file_path = ‘massive_sales_log.xlsx’

创建一个迭代器，每次读取 100,000 行

chunk_iterator = pd.read_excel(
file_path,
chunksize=100000,
dtype={‘CustomerID’: str, ‘ProductID’: str}, # 依然推荐指定 dtype
usecols=[‘CustomerID’, ‘ProductID’, ‘SaleAmount’] # 依然推荐指定 usecols
)

初始化一个空列表来存放处理结果

results = []

循环处理每个数据块

print(“开始分块处理大文件…”)
for i, chunk in enumerate(chunk_iterator):
print(f”处理第 {i+1} 块，大小: {chunk.shape}”)

# 在这里对每个 chunk 进行你的数据处理
# 例如，计算每个客户的总销售额
chunk_summary = chunk.groupby('CustomerID')['SaleAmount'].sum()
results.append(chunk_summary)

所有块处理完毕后，合并结果

final_summary = pd.concat(results).groupby(level=0).sum()

print(“\n处理完成！最终客户销售总额摘要：”)
print(final_summary.head())
“`

使用 chunksize 的模式是：分而治之。将大任务拆解成对小数据块的循环处理，最终将结果合并。这使得处理超过内存大小的文件成为可能。

第四章：实战场景与疑难杂症

4.1 场景一：合并多个结构相同的 Excel 文件

假设一个文件夹内有 Jan.xlsx, Feb.xlsx, Mar.xlsx…，需要将它们全部读取并合并。

“`python
import pandas as pd
import glob

获取所有 xlsx 文件的路径

path = ‘monthly_reports/’
all_files = glob.glob(path + “*.xlsx”)

循环读取并存入列表

li = []
for filename in all_files:
df = pd.read_excel(filename, index_col=None, header=0)
# 可以增加一列来标记数据来源
df[‘source_file’] = filename.split(‘/’)[-1]
li.append(df)

合并所有 DataFrame

full_report = pd.concat(li, axis=0, ignore_index=True)
print(full_report.info())
“`

4.2 场景二：处理合并单元格

Pandas 读取含有合并单元格的 Excel 时，默认只有合并区域左上角的那个单元格有值，其他单元格为 NaN。这通常需要后处理。

策略：使用 ffill() (向前填充) 方法。

假设 A 列有合并单元格：

| Region | Sales |
|--------|-------|
| North | 100 |
| NaN | 150 |
| NaN | 120 |
| South | 200 |
| NaN | 180 |

“`python
df = pd.read_excel(‘merged_cells.xlsx’)

使用 ffill() 填充 Region 列的 NaN 值

df[‘Region’] = df[‘Region’].ffill()
print(df)
输出：
| Region | Sales |
|——–|——-|
| North | 100 |
| North | 150 |
| North | 120 |
| South | 200 |
| South | 180 |
“`

4.3 常见错误与解决方案

• ImportError: Missing optional dependency 'openpyxl'.

  • 原因: 没有安装处理 .xlsx 文件的引擎。
  • 解决: pip install openpyxl。

• xlrd.biffh.XLRDError: Excel file format is not supported.

  • 原因: 你正在使用新版 xlrd (>=2.0) 尝试读取 .xlsx 文件。
  • 解决: 明确指定引擎 engine='openpyxl'。即 pd.read_excel('file.xlsx', engine='openpyxl')。

• 数值列（如邮编、ID）的前导零丢失

  • 原因: Pandas 自动将纯数字的列识别为数值类型。
  • 解决: 在读取时使用 dtype 参数强制指定为字符串，如 dtype={'ZipCode': str}。

• MemoryError

  • 原因: 文件太大，内存不足。
  • 解决: 优先使用 usecols 和 dtype 优化。如果问题依旧，必须使用 chunksize 进行分块读取。

总结：你的数据处理新起点

pandas.read_excel() 不仅仅是一个函数，它是连接结构化电子表格世界与强大编程分析能力的门户。通过精通其丰富的参数，你将能够：

1. 精准提取: 无论数据藏在哪个工作表、哪个区域，都能精确制导，拿到你想要的一切。
2. 优雅清洗: 在数据加载的瞬间，就完成跳过无关信息、重命名、定义数据类型等清洗工作，让后续分析事半功倍。
3. 极致性能: 面对海量数据不再束手无策，通过分块处理等策略，从容应对GB级别的挑战。
4. 高度自动化: 将繁琐的手动操作流程，固化为可复用、可扩展的 Python 脚本，一键完成数据导入与预处理。

2024年，数据处理的效率和深度决定了分析的价值。希望这篇详尽的攻略，能成为你工具箱中的一件利器，助你在数据分析的道路上披荆斩棘，游刃有余。现在，就打开你的编辑器，用 pd.read_excel() 开启一段全新的高效数据之旅吧！