使用Python快速打開一個百萬行級別的超大Excel文件的方法

Posted on by

知乎上有同學求助說，當他試圖打開一個20M左右的excel文件時，無論是使用pandas的read_excel，還是直接使用xlrd或者openpyxl模塊，速度都慢到無法忍受的程度，耗時大約1分鐘左右。

真的會這樣嗎？第一感覺是，這位同學在使用openpyxl模塊時沒有設置隻讀模式。為便於測試，先用下面的代碼生成一個一百萬行數據的excel文件。

>>> 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模塊打開文件的函數，分別考察關閉和開啟隻讀模式的時間消耗。

>>> 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秒鐘

運行測試，果然，不開啟隻讀的話，真的需要1分多鐘，而使用隻讀模式的話，則僅需0.4秒鐘。

不過，也別高興得太早，openpyxl模塊並沒有提供像pandas.read_excel()那樣把全部數據讀入一個數據結構的功能，隻能定位到行、列或格子以後再讀取數據。要想使用openpyxl模塊把全部數據讀入到數組或DataFrame中，需要遍歷所有的行和列，這仍然是一個非常耗時的操作。

那麼，pandas.read_excel()是否也支持隻讀模式呢？遺憾的是，read_excel()並沒有類似read_only這樣的參數。盡管read_excel()可以接受文件路徑、文件對象、類文件對象，甚至是二進制數據，但即使將文件內容傳入，read_excel()解析這100萬行數據仍然需要大約80秒鐘。下面的代碼驗證瞭這一點。

>>> 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模塊的隻讀模式，可以快速打開並取得指定格子的數據，但不要嘗試將全部數據讀入到自己定義的數據結構中，這將花費漫長的時間。對此，pandas也無能為力。

到此這篇關於使用Python快速打開一個百萬行級別的超大Excel文件的方法的文章就介紹到這瞭,更多相關python打開excel文件內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：