用ExcelPython在Excel中調用Python

xctdlxg 2015-12-22

展開全文

介紹

ExclePython 是一款輕量級的COM庫,，它支持你從Excel VBA中調用Python代碼和運行Python其他功能（確實任何語言都支持COM）,。這篇文章我將展示你如何去使用它。

背景

雖然excel的市場地位正日益面臨免費的（OpenOffice）和云軟件（Google Sheets）的挑戰(zhàn)和逐漸取代的風險,，但是Excel依然是現(xiàn)在辦公環(huán)境中,，使用范圍最廣、用戶界面友好的數(shù)據處理軟件,。能夠實現(xiàn)從Excel中調用python,，得益于Excel擁有龐大收集能力的庫和強大的語言能力。ExcelPython 是一款處理性的COM庫,，它能讓你加載python模塊,，調用程序和運行函數(shù)。另外一些Excel/Python接口工具,，例如PyXLL和Python-Excel,，它們同樣都是很優(yōu)秀的工具，只是與ExcelPython在功能上和適用性上有些微差別,。ExcelPython的優(yōu)勢有：

1. 免費（不用購買和免費協(xié)議）

2. 小而輕

3. 高度嵌入：似乎采用已經打包好的python函數(shù),，所以可以在不安裝python情況下表格程序可以擴展使用；

4. 對最終用戶高度透明：python由Excel VBA調用所以沒有不需要其他另外的中間程序插入,，即使是表格程序已經升級了,；

5. 通用性：任何存在的Python模塊和函數(shù)都可以調用，以及任何Python對象都可以被處理,，另外,，ExcelPython不必寫入額外的Python代碼來做可調用的函數(shù)，可以直接調用Python的的各種庫,；

使用代碼

如果你想了解最基本的關于如何使用這個庫的功能的話,，直接登錄ExcelPython在SourceForge的網友，那里你會找到一個維基條目,，可以一步步指引你如何操作,。

在本章介紹中，我們將展示下利用ExcelPython去合并收集到的列表,，排列他們的數(shù)據以及刪除任意副本,。這是一個非常簡單的python任務，只需要幾行代碼,，而如果用VBA代碼來實現(xiàn)同樣功能則需要更多的代碼,。

以下是我們在Excel中準備調用的python代碼：

def merge_sort_unique(lists):
s = set
for L in lists:
s.update(L)
return sorted(s)

任意在本地建立一個目錄，并保存這個代碼并命名為Methods.py,。我們一步步看看如何從Excel的工作表中如何調用這個腳本,。打開Excel，填寫一些數(shù)據以便提供python腳本處理,，并保持這個表格于剛才的python腳本在同一目錄下,。你的工作表應該如下：

用ExcelPython在Excel中調用Python

接下來,，打開VBA并提交給ExcelPython。打開VBA按Alt + F11然后進入工具界面的References···如果你已經正確安裝了ExcelPython你可以從對話框中選擇,。

用ExcelPython在Excel中調用Python

如圖選擇你就可以引用ExcelPython庫了,，輸入?PyVar(PyEval("1+2")) 在彈窗里（如果沒有彈窗請按 Ctrl + G），你會直接看到結果,。

用ExcelPython在Excel中調用Python

現(xiàn)在,，我們準備調用python腳本，創(chuàng)建一個新的模塊（Insert | Module）并敲以下VBA代碼：

Function msu(lists As Range)
Set methods = PyModule("Methods", AddPath:=ThisWorkbook.Path)
Set result = PyCall(methods, "merge_sort_unique", PyTuple(lists.Value2)) msu = WorksheetFunction.Transpose(PyVar(result))
Exit Function
End Function

讓我們一行行來解析下：

Set methods = PyModule("Methods", AddPath:=ThisWorkbook.Path)

這里首先是導入python模塊,，也就是從我們預先建好的Methods.py所在的目錄中導入,，并存儲成為VBA的模塊變量。也就是說現(xiàn)在VBA函數(shù)擁有了一個本地的叫methods的變量,，它直接調用的其實是python模塊,，注意AddPath參數(shù)，這里要填入我們一開始定義python腳本的路徑-這里我們輸入對應路徑以便找到我們的Methods.py,。

接下來我調用 merge_sort_unique：

Set result = PyCall(methods, "merge_sort_unique", PyTuple(lists.Value2))

ExcelPython 的PyCall模塊選擇一個待處理列,，這個列會被PyTuple模塊處理，你也可以通過 PyDict模塊自由選擇任意指定的列,。這里被處理的結果會作為VBA的一個結果變量,。

這個結果變量現(xiàn)在是一個python的list對象，它還不能直接導回到工作表（會導致一個typeerror）,。所以,，最后一步，我們用PyVar模塊來對python的list對象做編碼轉換,。

msu = WorksheetFunction.Transpose(PyVar(result))

PyVar模塊一般會將python對象轉換和VBA相同的編碼,，這里面，可能使用是將python lsit-of-lsits 轉換成2D數(shù)組,。更多的細節(jié)請在Excelpython的維基上查詢,。

現(xiàn)在我們看看是否成功運行！在工作表創(chuàng)建一個數(shù)組塊（選擇多個表格,，輸入公式并按 Ctrl-Shift-Enter）輸入公式=msu(A1:C16),。你會看到以下結果：

用ExcelPython在Excel中調用Python

如果沒有成功，別擔心,，ExcelPython非常容易debug，按下面修改VBA代碼

Function msu(lists As Range)
On Error GoTo do_error
Set methods = PyModule("Methods", AddPath:=PyPath)
Set result = PyCall(methods, "merge_sort_unique", PyTuple(lists.Value2)) msu = WorksheetFunction.Transpose(PyVar(result))
Exit Function
do_error: msu = Err.Description
End Function

這樣任何錯誤都可以被抓取并打印在工作表中,，你可以分析哪里出錯,。最后說明一點，這個功能會返回一個0,在列表的開頭,，即使這個0并不存在任何的列當中,，實際上這是python的一個空值作為一個開始寫進到工作表的,。為什么函數(shù)會返回一個空值在被處理的數(shù)據中呢？

原因在于導入的出來的幾個列都是不同長度的,，所以空的表格也會作為一個數(shù)據塊,，會被當做一個VBA空的值。這個值也會被封裝成一個空值傳給python,，所以導入處理的數(shù)據塊其實是包含了一個空值的,，一個快速解決方法是，修改merge_sort_unique參數(shù),，它可以從返回的值當中去掉空值：