|
使用Python加載最新的Excel讀取類庫xlwings可以說是Excel數(shù)據(jù)處理的利器，但使用起來還是有一些注意事項(xiàng),，否則高大上的Python會(huì)跑的比老舊的VBA還要慢,。 這里我們對比一下，用幾種不同的方法,，從一個(gè)Excel表格中讀取一萬行數(shù)據(jù),，然后計(jì)算結(jié)果，看看他們的耗時(shí),。
1. 處理要求： 一個(gè)Excel表格中包含了3萬條記錄,，其中B，C兩個(gè)列記錄了某些計(jì)算值,，讀取前一萬行記錄,，將這兩個(gè)列的差值進(jìn)行計(jì)算，然后匯總得出差的和,。
文件是這個(gè)樣子:Book300s.xlsx ,。
2. 處理方式有以下3種，我們對比一下耗時(shí)的大小,。
3. 首先測試第一種，XLS_READ_SHEET.py 使用Python的xlwings類庫,，讀取Excel文件,，然后引用Excel的Sheet和Range的方式來讀取并計(jì)算  1 #coding=utf-8
2 import xlwings as xw
3 import pandas as pd
4 import time
5
6 start_row = 2 # 處理Excel文件開始行
7 end_row = 10002 # 處理Excel結(jié)束行
8
9 #記錄打開表單開始時(shí)間
10 start_open_time = time.time()
11
12 #指定不顯示地打開Excel，讀取Excel文件
13 app = xw.App(visible=False, add_book=False)
14 wb = app.books.open('D:/PYTHON/TEST_CODE/Book300s.xlsx') # 打開Excel文件
15 sheet = wb.sheets[0] # 選擇第0個(gè)表單
16
17 #記錄打開Excel表單結(jié)束時(shí)間
18 end_open_time = time.time()
19
20 #記錄開始循環(huán)計(jì)算時(shí)間
21 start_run = time.time()
22
23 row_content = []
24 #讀取Excel表單前10000行的數(shù)據(jù),，Python的in range是左閉右開的,到10002結(jié)束,但區(qū)間只包含2到10001這一萬條
25 for row in range(start_row, end_row):
26 row_str = str(row)
27 #循環(huán)中引用Excel的sheet和range的對象,，讀取B列和C列的每一行的值，對比計(jì)算
28 start_value = sheet.range('B' + row_str).value
29 end_value = sheet.range('C' + row_str).value
30 if start_value <= end_value:
31 values = end_value - start_value
32 #同時(shí)測試List數(shù)組添加記錄
33 row_content.append(values)
34
35 #計(jì)算和
36 total_values = sum(row_content)
37
38 #記錄結(jié)束循環(huán)計(jì)算時(shí)間
39 end_run = time.time()
40 sheet.range('E2').value = str(total_values)
41 sheet.range('E3').value = '使用Sheet計(jì)算時(shí)間(秒):' + str(end_run - start_run)
42
43 #保存并關(guān)閉Excel文件
44 wb.save()
45 wb.close()
46
47 print ('結(jié)果總和:', total_values)
48 print ('打開并讀取Excel表單時(shí)間(秒):', end_open_time - start_open_time)
49 print ('計(jì)算時(shí)間(秒):', end_run - start_run)
50 print ('處理數(shù)據(jù)條數(shù):' , len(row_content))
用Python直接訪問Sheet和Range取值的計(jì)算結(jié)果如下： 讀取Excel文件用時(shí) 4.47秒 處理Excel 10000 行數(shù)據(jù)花費(fèi)了117秒的時(shí)間,。
4. 然后我們用Excel自帶的VBA語言來處理一下相同的計(jì)算,。也是直接引用Sheet，Range等Excel對象,，但VBA的數(shù)組功能實(shí)在是不好用,，就不測試添加數(shù)組了。
1 Option Explicit
2
3 Sub VBA_CAL_Click()
4 Dim i_count As Long
5 Dim offset_value, total_offset_value As Double
6 Dim st, et As Date
7 st = Time()
8
9 i_count = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
10 i_count = 10001
11 For i_count = 2 To i_count
12 If Range("C" & i_count).Value > Range("B" & i_count).Value Then
13 offset_value = Range("C" & i_count).Value - Range("B" & i_count).Value
14 total_offset_value = total_offset_value + offset_value
15 End If
16 Next i_count
17
18 et = Time()
19 Range("E2").Value = total_offset_value
20 Range("E3").Value = et - st
21
22 MsgBox "Result: " & total_offset_value & Chr(10) & "Running time: " & et - st
23 End Sub
VBA處理計(jì)算結(jié)果如下： 保存了3萬條數(shù)據(jù)的Excel文件是通過手工打開的,，在電腦上大概花費(fèi)了8.2秒的時(shí)間 處理Excel 前10000行數(shù)據(jù)花費(fèi)了1.16秒的時(shí)間,。
5. 使用Python的xlwings類庫，讀取Excel文件，然后采用Python的自帶數(shù)據(jù)類型List進(jìn)行數(shù)據(jù)存儲和計(jì)算,，計(jì)算完成后再將結(jié)果寫到Excel表格中
1 #coding=utf-8
2 import xlwings as xw
3 import pandas as pd
4 import time
5
6 #記錄打開表單開始時(shí)間
7 start_open_time = time.time()
8
9 #指定不顯示地打開Excel,，讀取Excel文件
10 app = xw.App(visible=False, add_book=False)
11 wb = app.books.open('D:/PYTHON/TEST_CODE/Book300s.xlsx') # 打開Excel文件
12 sheet = wb.sheets[0] # 選擇第0個(gè)表單
13
14 #記錄打開Excel表單結(jié)束時(shí)間
15 end_open_time = time.time()
16
17 #記錄開始循環(huán)計(jì)算時(shí)間
18 start_run = time.time()
19
20 row_content = []
21 #讀取Excel表單前10000行的數(shù)據(jù)，并計(jì)算B列和C列的差值之和
22 list_value = sheet.range('A2:D10001').value
23 for i in range(len(list_value)):
24 #使用Python的類庫直接訪問Excel的表單是很緩慢的,，不要在Python的循環(huán)中引用sheet等Excel表單的單元格,，
25 #而是要用List一次性讀取Excel里的數(shù)據(jù)，在List內(nèi)存中計(jì)算好了,，然后返回結(jié)果
26 start_value = list_value[i][1]
27 end_value = list_value[i][2]
28 if start_value <= end_value:
29 values = end_value- start_value
30 #同時(shí)測試List數(shù)組添加記錄
31 row_content.append(values)
32
33 #計(jì)算和
34 total_values = sum(row_content)
35 #記錄結(jié)束循環(huán)計(jì)算時(shí)間
36 end_run = time.time()
37 sheet.range('E2').value = str(total_values)
38 sheet.range('E3').value = '使用List 計(jì)算時(shí)間(秒):' + str(end_run - start_run)
39
40 #保存并關(guān)閉Excel文件
41 wb.save()
42 wb.close()
43
44 print ('結(jié)果總和:', total_values)
45 print ('打開并讀取Excel表單時(shí)間(秒):', end_open_time - start_open_time)
46 print ('計(jì)算時(shí)間(秒):', end_run - start_run)
47 print ('處理數(shù)據(jù)條數(shù):' , len(row_content))
用Python的LIST在內(nèi)存中計(jì)算結(jié)果如下： 讀取Excel文件用時(shí) 4.02秒 處理Excel 10000 行數(shù)據(jù)花費(fèi)了 0.10 秒的時(shí)間,。
6 結(jié)論：
Python操作Excel的類庫有以往有 xlrd、xlwt,、openpyxl,、pyxll等，這些類庫有的只支持讀取,，有的只支持寫入,，并且有的不支持Excel的xlsx格式等。 所以我們采用了最新的開源免費(fèi)的xlwings類庫,，xlwings能夠很方便的讀寫Excel文件中的數(shù)據(jù),，并支持Excel的單元格格式修改，也可以與pandas等類庫集成使用,。
VBA是微軟Excel的原生二次開發(fā)語言,，是辦公和數(shù)據(jù)統(tǒng)計(jì)的利器，在金融,，統(tǒng)計(jì)，管理,，計(jì)算中應(yīng)用非常廣泛,，但是VBA計(jì)算能力較差，支持的數(shù)據(jù)結(jié)構(gòu)少,，編輯器粗糙,。 雖然VBA有很多不足，但是VBA的宿主Office Excel卻是天才程序員基于C++開發(fā)的作品,，穩(wěn)定,，高效，易用 ,。 有微軟加持,，VBA雖然數(shù)據(jù)結(jié)構(gòu)少，運(yùn)行速度慢,，但訪問自己Excel的Sheet,，Range，Cell等對象卻速度飛快，這就是一體化產(chǎn)品的優(yōu)勢,。 VBA讀取Excel的Range,，Cell等操作是通過底層的API直接讀取數(shù)據(jù)的，而不是通過微軟統(tǒng)一的外部開發(fā)接口,。所以Python的各種開源和商用的Excel處理類庫如果和VBA來比較讀寫Excel格子里面的數(shù)據(jù),，都是處于劣勢的（至少是不占優(yōu)勢的），例子2的VBA 花費(fèi)了1.16秒就能處理完一萬條數(shù)據(jù),。
Python基于開源,，語法優(yōu)美而健壯，支持面向?qū)ο箝_發(fā),，最重要的是,，Python有豐富而功能強(qiáng)大的類庫，支持多種工作場景的開發(fā),。 我們應(yīng)該認(rèn)識到,，Excel對于Python而言，只是數(shù)據(jù)源文件的一種,，當(dāng)處理大量數(shù)據(jù)時(shí),，Python處理Excel就要把Excel當(dāng)數(shù)據(jù)源來處理，一次性地讀取數(shù)據(jù)到Python的數(shù)據(jù)結(jié)構(gòu)中,，而不是大量調(diào)用Excel里的對象,，不要說頻繁地寫入Excel，就是頻繁地讀取Excel里面的某些單元格也是效率較低的,。例子1的Python頻繁讀取Sheet,，Range數(shù)據(jù)，結(jié)果花費(fèi)了117秒才處理完一萬條數(shù)據(jù),。
Python的計(jì)算效率和數(shù)據(jù)結(jié)構(gòu)的操作方便性可比VBA強(qiáng)上太多,，和VBA聯(lián)合起來使用，各取所長是個(gè)好主意,。 當(dāng)Excel數(shù)據(jù)一次性讀入Python的內(nèi)存List數(shù)據(jù)結(jié)構(gòu)中,，然后基于自身的List數(shù)據(jù)結(jié)構(gòu)在內(nèi)存中計(jì)算，例子3的Python只用了 0.1秒就完成了一萬條數(shù)據(jù)的計(jì)算并將結(jié)果寫回Excel,。 總結(jié)：
|
|
|
