如何在Excel中呼叫Python指令碼進行數據處理

2024-07-08辦公

今天我們介紹如何使用xlwings將Python和Excel兩大數據工具進行整合，更便捷地處理日常工作。

說起Excel，那絕對是數據處理領域王者般的存在，盡管已經誕生三十多年了，現在全球仍有7.5億忠實使用者，而作為網紅語言的Python，也僅僅只有700萬的開發人員。

Excel是全世界最流行的程式語言。對，你沒看錯，自從微軟引入了LAMBDA定義函式後，Excel已經可以實作程式語言的演算法，因此它是具備圖靈完備性的，和JavaScript、Java、Python一樣。

雖然Excel對小規模數據場景來說是剛需利器，但它面對大數據時就會有些力不從心。

我們知道一張Excel表最多能顯示1048576行和16384列，處理一張幾十萬行的表可能就會有些卡頓，當然你可以使用VBA進行數據處理，也可以使用Python來操作Excel。

這就是本文要講到的主題，Python的第三方庫-xlwings，它作為Python和Excel的互動工具，讓你可以輕松地透過VBA來呼叫Python指令碼，實作復雜的數據分析。

比如說自動匯入數據：

或者隨機匹配文本：

一、為什麽將Python與Excel VBA整合？

VBA作為Excel內建的宏語言，幾乎可以做任何事情，包括自動化、數據處理、分析建模等等，那為什麽要用Python來整合Excel VBA呢？主要有以下三點理由：

如果你對VBA不算精通，你可以直接使用Python編寫分析函式用於Excel運算，而無需使用VBA；

Python相比VBA執行速度更快，且程式碼編寫更簡潔靈活；

Python中有眾多優秀的第三方庫，隨用隨取，可以節省大量程式碼時間；

對於Python愛好者來說，pandas、numpy等數據科學庫用起來可能已經非常熟悉，如果能將它們用於Excel數據分析中，那將是如虎添翼。

二、為什麽使用xlwings？

Python中有很多庫可以操作Excel，像xlsxwriter、openpyxl、pandas、xlwings等。

但相比其他庫，xlwings效能綜合來看幾乎是最優秀的，而且xlwings可以實作透過Excel宏呼叫Python程式碼。

安裝xlwings非常簡單，在命令列透過pip實作快速安裝：

pip install python

安裝好xlwings後，接下來需要安裝xlwings的 Excel整合外掛程式，安裝之前需要關閉所有 Excel 套用，不然會報錯。

同樣在命令列輸入以下命令：

xlwings addin install

出現下面提示代表整合外掛程式安裝成功。

xlwings和外掛程式都安裝好後，這時候開啟Excel，會發現工具列出現一個xlwings的選單框，代表xlwings外掛程式安裝成功，它起到一個橋梁的作用，為VBA呼叫Python指令碼牽線搭橋。

另外，如果你的選單欄還沒有顯示「開發工具」，那需要把「開發工具」添加到功能區，因為我們要用到宏。

步驟很簡單：

1、在"檔"索引標籤上，轉到"自訂>選項"。

2、在「自訂功能區」和「主索引標籤」下，選中「開發工具」核取方塊。

選單欄顯示開發工具，就可以開始使用宏。

如果你還不知道什麽是宏，可以暫且把它理解成實作自動化及批次處理的工具。

到這一步，前期的準備工作就完成了，接下來就是實戰！

三、玩轉xlwings

要想在excel中呼叫python指令碼，需要寫VBA程式來實作，但對於不懂VBA的小夥伴來說就是個麻煩事。

但xlwings解決了這個問題，不需要你寫VBA程式碼就能直接在excel中呼叫python指令碼，並將結果輸出到excel表中。

xlwings會幫助你建立 .xlsm 和 .py 兩個檔，在 .py 檔裏寫python程式碼，在 .xlsm 檔裏點選執行，就完成了excel與python的互動。

怎麽建立這兩個檔呢？非常簡單，直接在命令列輸入以下程式碼即可：

xlwings quickstart ProjectName

這裏的 ProjectName 可以自訂，是建立後檔的名字。

如果你想把檔建立到指定資料夾裏，需要提前將命令列導航到指定目錄。

建立好後，在指定資料夾裏會出現兩個檔，就是之前說的 .xlsm 和 .py 檔。

我們開啟 .xlsm 檔，這是一個excel宏檔，xlwings已經提前幫你寫好了呼叫Python的VBA程式碼。

按快捷鍵 Alt + F11 ，就能調出VBA編輯器。

Sub SampleCall() mymodule = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare) - 1)) RunPython "import " & mymodule & ";" & mymodule & ".main()"End Sub

裏面這串程式碼主要執行兩個步驟：

1、在 .xlsm 檔相同位置尋找相同名稱的 .py 檔

2、呼叫 .py 指令碼裏的 main() 函式

我們先來看一個簡單的例子，自動在excel表裏輸入 ['a','b','c','d','e']

第一步：我們把 .py 檔裏的程式碼改成以下形式。

import xlwings as xw import pandas as pd defmain(): wb = xw.Book.caller() values = ['a','b','c','d','e'] wb.sheets[0].range('A1').value = values @xw.func defhello(name): returnf"Hello {name}!" if __name__ == "__main__": xw.Book("PythonExcelTest.xlsm").set_mock_caller() main()

然後在 .xlsm 檔 sheet1 中建立一個按鈕，並設定預設的宏，變成一個觸發按鈕。

設定好觸發按鈕後，我們直接點選它，就會發現第一行出現了 ['a','b','c','d','e'] 。

同樣的，我們可以把鳶尾花數據集自動匯入到excel中，只需要在.py檔裏改動程式碼即可，程式碼如下：

import xlwings as xw import pandas as pd defmain(): wb = xw.Book.caller() df = pd.read_csv(r"E:\\test\\PythonExcelTest\\iris.csv") df['total_length'] = df['sepal_length'] + df['petal_length'] wb.sheets[0].range('A1').value = df @xw.func defhello(name): returnf"Hello {name}!" if __name__ == "__main__": xw.Book("PythonExcelTest.xlsm").set_mock_caller() main()