<p>正如其他人所建议的,csv阅读速度更快。因此,如果您在windows上使用Excel,可以调用vbscript将Excel转换为csv,然后读取csv。我试了下面的脚本,大概花了30秒。</p>
<pre><code># create a list with sheet numbers you want to process
sheets = map(str,range(1,6))
# convert each sheet to csv and then read it using read_csv
df={}
from subprocess import call
excel='C:\\Users\\rsignell\\OTT_Data_All_stations.xlsx'
for sheet in sheets:
csv = 'C:\\Users\\rsignell\\test' + sheet + '.csv'
call(['cscript.exe', 'C:\\Users\\rsignell\\ExcelToCsv.vbs', excel, csv, sheet])
df[sheet]=pd.read_csv(csv)
</code></pre>
<p>下面是创建ExcelToCsv.vbs脚本的一小段python:</p>
<pre><code>#write vbscript to file
vbscript="""if WScript.Arguments.Count < 3 Then
WScript.Echo "Please specify the source and the destination files. Usage: ExcelToCsv <xls/xlsx source file> <csv destination file> <worksheet number (starts at 1)>"
Wscript.Quit
End If
csv_format = 6
Set objFSO = CreateObject("Scripting.FileSystemObject")
src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(1))
worksheet_number = CInt(WScript.Arguments.Item(2))
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)
oBook.Worksheets(worksheet_number).Activate
oBook.SaveAs dest_file, csv_format
oBook.Close False
oExcel.Quit
""";
f = open('ExcelToCsv.vbs','w')
f.write(vbscript.encode('utf-8'))
f.close()
</code></pre>
<p>这个答案得益于<a href="https://stackoverflow.com/questions/1858195/convert-xls-to-csv-on-command-line">Convert XLS to CSV on command line</a>和<a href="https://stackoverflow.com/questions/16182822/csv-xlsx-files-import-to-pandas-data-frame-speed-issue">csv & xlsx files import to pandas data frame: speed issue</a></p>