使用Python快速打开一个百万行级别的超大Excel文件的方法

问题背景与性能瓶颈

在处理包含百万行数据的大型Excel文件（例如20MB）时，开发者常遇到读取速度极慢的问题。通过实际测试发现：

• 使用 pandas 的 read_excel 打开文件耗时约1分钟。
• 直接使用 xlrd 模块打开文件耗时约1分钟。
• 直接使用 openpyxl 模块（默认模式）打开文件耗时约1分钟。

这些工具在默认配置下，往往需要将全部数据加载到内存结构中，导致性能瓶颈。特别是 pandas.read_excel() 不支持只读模式参数，必须解析全部数据。

测试环境构建

为了验证不同模块的性能，首先构建一个包含100万行数据的Excel文件作为测试基准。该文件包含“id”、“语文”、“数学”、“英语”、“物理”五个字段。

>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> sh = wb.active
>>> sh.append(['id', '语文', '数学', '英语', '物理'])
>>> for i in range(1000000): # 写入100万行数据
	sh.append([i+1, 90, 100, 95, 99])

	
>>> wb.save(r'd:\bigxlsx.xlsx')
>>> import os
>>> os.path.getsize(r'd:\bigxlsx.xlsx') # 文件大小：20M字节
20230528

核心解决方案：openpyxl只读模式

openpyxl 模块提供了一个关键参数 read_only。开启该模式后，库不会将全部数据加载到内存中，而是采用流式读取方式，从而大幅提升打开速度。

代码实现与对比

通过定义一个读取函数，对比开启和关闭只读模式的时间差异：

>>> from openpyxl import load_workbook
>>> import time
>>> def read_xlsx(read_only):
	t0 = time.time()
	wb = load_workbook(r'd:\bigxlsx.xlsx', read_only=read_only)
	t1 = time.time()
	print(wb.sheetnames)
	print(sh.cell(row=1, column=1).value)
	print(sh.cell(row=100, column=3).value)
	print('耗时%0.3f秒钟'%(t1-t0))

	
>>> read_xlsx(True)
['Sheet']
id
100
耗时0.404秒钟
>>> read_xlsx(False)
['Sheet']
id
100
耗时67.817秒钟

结果显示，开启只读模式（read_only=True）仅需0.4秒，而关闭该模式（read_only=False）则需要67.8秒。这表明对于仅需查看或遍历部分数据的大文件，只读模式是最佳选择。

pandas读取大文件的局限性

尽管 pandas 是数据处理的主流工具，但其 read_excel() 函数在解析百万行数据时依然耗时较长（约81秒）。即使通过二进制方式读取文件内容，也无法绕过其全量解析的机制。

>>> import pandas as pd
>>> def read_excel_by_pandas():	
	with open(r'd:\bigxlsx.xlsx', 'rb') as fp:
		content = fp.read()
		t0 = time.time()
		df = pd.read_excel(content, engine='openpyxl')
		t1 = time.time()
	print(df.head())
	print(df.tail())
	print('耗时%0.3f秒钟'%(t1-t0))

	
>>> read_excel_by_pandas()
  id 语文  数学 英语 物理
0  1 90 100 95 99
1  2 90 100 95 99
2  3 90 100 95 99
3  4 90 100 95 99
4  5 90 100 95 99
       id 语文  数学 英语 物理
999995  999996 90 100 95 99
999996  999997 90 100 95 99
999997  999998 90 100 95 99
999998  999999 90 100 95 99
999999 1000000 90 100 95 99
耗时81.369秒钟

总结与建议

当面对百万行级别的Excel文件时，若目标是快速打开文件或仅访问部分数据，推荐使用 openpyxl 并开启 read_only=True 参数。该方法能显著减少内存占用和读取时间。若需进行复杂的数据分析或全量处理，pandas 仍是可靠选择，但需接受较长的解析时间。以上就是使用Python快速打开一个百万行级别的超大Excel文件的方法的详细内容，更多关于Python处理Excel数据的技术资料请关注本站其它相关文章！