Pandas Read Excel: 如何在Python中导入Excel文件 – Kanaries

Pandas Read Excel: 如何在Python中导入Excel文件

NameSoren AtelierUpdated on 2026/2/9

Excel文件仍然是商业环境中存储表格数据的最常见格式,然而将它们导入Python进行分析时常常会产生摩擦。您需要处理多个工作表、不一致的数据类型、缺失值和内存约束,同时保持数据完整性。pandas的read_excel()函数解决了这些挑战,但前提是您知道如何正确配置其30多个参数。

本指南向您展示如何高效地将Excel文件导入pandas DataFrame,从基本的单工作表导入到高级场景,如同时读取多个工作表、处理大文件和解决常见错误。

📚

pandas read_excel()的基本用法

将Excel文件读入pandas DataFrame的最简单方法只需要文件路径:

import pandas as pd

# 读取Excel文件的第一个工作表

df = pd.read_excel('data.xlsx')

print(df.head())

这默认读取第一个工作表,将第一行视为列标题。该函数自动检测文件格式(.xlsx或.xls)并使用适当的引擎。

对于不同目录中的文件,使用绝对路径或相对路径:

# 绝对路径

df = pd.read_excel('/Users/username/Documents/sales_data.xlsx')

# 相对路径

df = pd.read_excel('../data/sales_data.xlsx')

理解关键参数

read_excel()函数提供了对如何导入数据的广泛控制。以下是最重要参数的参考表:

参数类型描述示例sheet_namestr, int, list, None指定要读取的工作表sheet_name='Sales' 或 sheet_name=0headerint, list, None用作列名的行header=0 (默认) 或 header=Noneusecolsstr, list要解析的列usecols='A:D' 或 usecols=[0,1,2]dtypedict, Type列的数据类型dtype={'ID': str, 'Age': int}skiprowsint, list开头要跳过的行skiprows=2 或 skiprows=[0,2,5]nrowsint要读取的行数nrows=1000na_valuesscalar, str, list, dict识别为NaN的值na_values=['NA', 'N/A', '']parse_datesbool, list, dict要解析为日期的列parse_dates=['Date']enginestr要使用的Excel引擎engine='openpyxl' 或 engine='xlrd'

处理多个工作表

读取特定工作表

按名称或索引(从0开始)指定工作表:

# 按工作表名称读取

df = pd.read_excel('financial_report.xlsx', sheet_name='Q1 Revenue')

# 按索引读取 (0 = 第一个工作表)

df = pd.read_excel('financial_report.xlsx', sheet_name=0)

一次读取多个工作表

传递工作表名称或索引列表,将多个工作表导入DataFrame字典:

# 读取多个特定工作表

sheets_dict = pd.read_excel('annual_report.xlsx',

sheet_name=['Q1', 'Q2', 'Q3', 'Q4'])

# 访问单个DataFrame

q1_df = sheets_dict['Q1']

q2_df = sheets_dict['Q2']

print(f"Q1 shape: {q1_df.shape}")

print(f"Q2 shape: {q2_df.shape}")

读取所有工作表

使用sheet_name=None导入工作簿中的每个工作表:

# 将所有工作表读入字典

all_sheets = pd.read_excel('company_data.xlsx', sheet_name=None)

# 遍历所有工作表

for sheet_name, df in all_sheets.items():

print(f"\nSheet: {sheet_name}")

print(f"Shape: {df.shape}")

print(f"Columns: {df.columns.tolist()}")

当您需要处理具有相似结构的多个工作表时,这特别有用:

# 将所有工作表合并为单个DataFrame

all_sheets = pd.read_excel('monthly_sales.xlsx', sheet_name=None)

combined_df = pd.concat(all_sheets.values(), ignore_index=True)

print(f"Combined shape: {combined_df.shape}")

使用标题和列控制数据导入

指定标题行

控制哪一行成为列名:

# 跳过前2行,使用第3行作为标题

df = pd.read_excel('data.xlsx', header=2)

# 无标题行 - pandas分配数字列名

df = pd.read_excel('data.xlsx', header=None)

# 多级列标题

df = pd.read_excel('data.xlsx', header=[0, 1])

跳过行

从文件顶部删除不需要的行:

# 跳过前3行

df = pd.read_excel('data.xlsx', skiprows=3)

# 跳过特定行 (从0开始索引)

df = pd.read_excel('data.xlsx', skiprows=[0, 2, 5])

# 使用函数跳过行

df = pd.read_excel('data.xlsx', skiprows=lambda x: x % 2 == 0) # 跳过偶数行

选择特定列

仅导入所需的列以减少内存使用:

# 按字母范围选择列

df = pd.read_excel('data.xlsx', usecols='A:D')

# 按字母选择特定列

df = pd.read_excel('data.xlsx', usecols='A,C,E,G')

# 按索引选择列

df = pd.read_excel('data.xlsx', usecols=[0, 2, 4, 6])

# 按名称选择列

df = pd.read_excel('data.xlsx', usecols=['Name', 'Age', 'Salary'])

# 使用函数选择列

df = pd.read_excel('data.xlsx', usecols=lambda x: 'total' in x.lower())

数据类型指定和转换

设置列数据类型

明确定义数据类型以防止自动推断错误:

# 为多列指定dtypes

df = pd.read_excel('customer_data.xlsx',

dtype={

'CustomerID': str,

'ZipCode': str,

'Age': int,

'Revenue': float

})

# 将数字ID保留为字符串以保留前导零

df = pd.read_excel('products.xlsx', dtype={'ProductID': str})

处理日期

自动解析日期列:

# 解析单个日期列

df = pd.read_excel('orders.xlsx', parse_dates=['OrderDate'])

# 解析多个日期列

df = pd.read_excel('employee_data.xlsx',

parse_dates=['HireDate', 'TerminationDate'])

# 将列组合为单个datetime

df = pd.read_excel('logs.xlsx',

parse_dates={'DateTime': ['Date', 'Time']})

# 检查结果

print(df.dtypes)

管理缺失值

控制pandas如何识别和处理缺失数据:

# 识别自定义缺失值指示符

df = pd.read_excel('survey_data.xlsx',

na_values=['NA', 'N/A', 'null', 'None', '-'])

# 每列不同的NA值

df = pd.read_excel('mixed_data.xlsx',

na_values={

'Age': ['Unknown', 'N/A'],

'Salary': [0, -1, 'Not Disclosed']

})

# 保留默认NA值并添加更多

df = pd.read_excel('data.xlsx',

na_values=['Missing'],

keep_default_na=True)

处理大型Excel文件

Excel文件在完全加载到RAM时会消耗大量内存。对大型数据集使用以下策略:

限制行数

只读取所需的行:

# 读取前10,000行

df = pd.read_excel('large_file.xlsx', nrows=10000)

# 使用skiprows和nrows读取特定行范围

df = pd.read_excel('large_file.xlsx', skiprows=1000, nrows=5000)

使用转换器提高内存效率

在导入期间应用转换以减少内存占用:

# 读取时转换列

def categorize_status(value):

return 'Active' if value == 1 else 'Inactive'

df = pd.read_excel('users.xlsx',

converters={

'Status': categorize_status,

'Category': lambda x: x.lower().strip()

})

迭代处理工作表

对于具有多个大型工作表的工作簿,一次处理一个工作表:

import pandas as pd

# 首先获取所有工作表名称

xls = pd.ExcelFile('massive_workbook.xlsx')

sheet_names = xls.sheet_names

# 分别处理每个工作表

for sheet in sheet_names:

df = pd.read_excel(xls, sheet_name=sheet, nrows=10000)

# 在此处理df

result = df.describe()

print(f"\n{sheet} Summary:\n{result}")

替代方案: 使用openpyxl进行流式处理

对于极大的文件,考虑使用openpyxl逐行读取:

from openpyxl import load_workbook

# 以只读模式加载工作簿

wb = load_workbook('huge_file.xlsx', read_only=True)

ws = wb['Sheet1']

# 迭代处理行

data = []

for row in ws.iter_rows(min_row=2, max_row=1000, values_only=True):

data.append(row)

# 转换为DataFrame

df = pd.DataFrame(data, columns=['Col1', 'Col2', 'Col3'])

wb.close()

理解Excel引擎

Pandas根据格式和已安装的包使用不同的库来读取Excel文件:

引擎文件格式安装用例openpyxl.xlsxpip install openpyxl现代Excel文件(Excel 2010+),.xlsx的默认值xlrd.xls, .xlsxpip install xlrd旧版.xls文件(Excel 2003),.xlsx已弃用pyxlsb.xlsbpip install pyxlsb二进制Excel文件odf.odspip install odfpyOpenDocument电子表格

明确指定引擎:

# 强制使用openpyxl引擎

df = pd.read_excel('data.xlsx', engine='openpyxl')

# 读取旧版.xls文件

df = pd.read_excel('old_data.xls', engine='xlrd')

# 读取二进制Excel文件

df = pd.read_excel('data.xlsb', engine='pyxlsb')

Pandas read_excel vs read_csv: 主要区别

虽然两个函数都创建DataFrame,但它们有重要的区别:

特性read_excel()read_csv()文件格式二进制Excel文件(.xlsx, .xls)纯文本CSV文件速度较慢(复杂格式解析)较快(简单文本解析)依赖需要openpyxl/xlrd无额外依赖多个工作表是(sheet_name参数)否(仅单个文件)格式化数据保留一些Excel格式无格式(纯文本)内存使用更高(二进制解析开销)更低(文本流)公式仅读取计算值N/A日期解析Excel日期自动转换需要parse_dates大文件内存密集型支持chunksize参数最适合商业数据、多工作表报告大型数据集、数据导出

建议: 用read_csv()进行大规模数据处理,用read_excel()处理现有Excel工作簿或多个工作表。

使用PyGWalker即时可视化Excel数据

使用pandas导入Excel数据后,您通常需要对其进行可视化探索。PyGWalker (opens in a new tab)将您的DataFrame直接在Jupyter笔记本中转换为类似Tableau的交互式可视化界面,无需编写绘图代码。

import pandas as pd

import pygwalker as pyg

# 读取Excel数据

df = pd.read_excel('sales_data.xlsx')

# 启动交互式可视化

pyg.walk(df)

这会打开一个拖放界面,您可以:

通过将列拖到视觉通道来创建图表

在图表类型之间即时切换(条形图、折线图、散点图、热图)

交互式应用过滤器和聚合

将可视化导出为图像或代码

PyGWalker在导入Excel文件后进行探索性数据分析时特别有用,让您无需编写matplotlib或seaborn代码即可理解数据分布和关系。

常见错误和故障排除

ModuleNotFoundError: No module named 'openpyxl'

这是读取.xlsx文件时最常见的错误:

ImportError: Missing optional dependency 'openpyxl'. Use pip or conda to install openpyxl.

解决方案: 安装所需的引擎:

# 安装openpyxl

pip install openpyxl

# 或安装所有Excel依赖

pip install pandas[excel]

XLRDError: Excel xlsx file; not supported

如果您看到此错误,xlrd正在尝试读取.xlsx文件(自2.0版本以来仅支持.xls):

解决方案: 安装openpyxl或指定引擎:

df = pd.read_excel('data.xlsx', engine='openpyxl')

FileNotFoundError

检查您的文件路径和工作目录:

import os

# 打印当前目录

print(os.getcwd())

# 列出目录中的文件

print(os.listdir('.'))

# 使用绝对路径

df = pd.read_excel('/absolute/path/to/file.xlsx')

大文件的内存错误

如果pandas因内存错误而崩溃:

# 通过选择列减少内存

df = pd.read_excel('large_file.xlsx', usecols=['A', 'B', 'C'])

# 限制行数

df = pd.read_excel('large_file.xlsx', nrows=50000)

# 指定dtypes以减少内存

df = pd.read_excel('large_file.xlsx',

dtype={'ID': 'int32', 'Category': 'category'})

ValueError: Excel file format cannot be determined

当文件扩展名与实际格式不匹配时会发生这种情况:

解决方案: 明确指定引擎或使用正确的扩展名重命名文件:

# 尝试不同的引擎

df = pd.read_excel('data.xlsx', engine='openpyxl')

日期解析问题

Excel将日期存储为序列号。如果日期未正确解析:

# 强制日期解析

df = pd.read_excel('data.xlsx', parse_dates=['DateColumn'])

# 手动转换

df['DateColumn'] = pd.to_datetime(df['DateColumn'])

高级技术

读取受密码保护的文件

虽然pandas不直接支持受密码保护的文件,但可以使用msoffcrypto-tool:

import msoffcrypto

import io

import pandas as pd

# 解密文件

decrypted = io.BytesIO()

with open('protected.xlsx', 'rb') as f:

file = msoffcrypto.OfficeFile(f)

file.load_key(password='yourpassword')

file.decrypt(decrypted)

# 读取解密的文件

df = pd.read_excel(decrypted)

将read_excel与上下文管理器结合

为了更好的资源管理:

from contextlib import closing

import pandas as pd

with closing(pd.ExcelFile('data.xlsx')) as xls:

df1 = pd.read_excel(xls, sheet_name='Sheet1')

df2 = pd.read_excel(xls, sheet_name='Sheet2')

# 此块后文件自动关闭

使用URL

直接从URL读取Excel文件:

url = 'https://example.com/data.xlsx'

df = pd.read_excel(url)

使用RunCell进行Excel数据调试

在Jupyter笔记本中处理复杂的Excel导入时,RunCell (opens in a new tab)提供AI驱动的协助来调试导入错误、优化参数和修复数据类型问题。AI代理可以:

诊断为什么read_excel()无法正确解析日期

建议内存效率的最佳dtype规范

调试引擎兼容性问题

帮助排除编码和格式问题

这在导入具有不一致格式或复杂多工作表结构的混乱现实世界Excel文件时特别有价值。

FAQ

如何在pandas中读取无标题的Excel文件?

设置header=None告诉pandas第一行是数据而不是列名。Pandas将分配数字列名(0、1、2...):

df = pd.read_excel('data.xlsx', header=None)

然后您可以手动重命名列:

df.columns = ['Name', 'Age', 'Salary']

pandas可以一次读取多个Excel文件吗?

是的,使用循环或列表推导式读取多个文件并连接它们:

import pandas as pd

from glob import glob

# 读取目录中的所有Excel文件

files = glob('data/*.xlsx')

dfs = [pd.read_excel(f) for f in files]

combined_df = pd.concat(dfs, ignore_index=True)

如何只读取Excel文件中的特定行?

组合skiprows和nrows参数:

# 读取第100-199行(跳过前100行,读取接下来的100行)

df = pd.read_excel('data.xlsx', skiprows=100, nrows=100)

为什么read_excel比read_csv慢得多?

Excel文件是需要复杂解析的二进制格式,而CSV文件是纯文本。对于大型数据集,首先将Excel转换为CSV:

# 一次性转换

df = pd.read_excel('large_file.xlsx')

df.to_csv('large_file.csv', index=False)

# 将来的读取使用CSV

df = pd.read_csv('large_file.csv') # 快得多

如何处理Excel文件中的合并单元格?

Pandas读取合并单元格的左上角值,并用NaN填充其余部分。您可以向前填充这些值:

df = pd.read_excel('data.xlsx')

df['MergedColumn'] = df['MergedColumn'].fillna(method='ffill')

结论

pandas read_excel()函数提供了对将Excel文件导入Python的全面控制,从简单的单工作表导入到涉及多个工作表、自定义数据类型和大文件处理的复杂场景。掌握关键参数 – sheet_name、usecols、dtype、parse_dates和skiprows – 以高效准确地导入数据。

对于大多数工作流程,基本的pd.read_excel('file.xlsx')就足够了,但在处理真实世界的商业数据时,理解高级选项(如引擎选择、转换器函数和内存优化技术)变得至关重要。记得安装适当的引擎(.xlsx文件使用openpyxl),并考虑将大型Excel文件转换为CSV以获得更好的性能。

pandas用于数据导入、PyGWalker用于即时可视化、RunCell用于调试的组合为Python中的Excel数据分析创建了强大的工作流程。

📚