Excel既是一種祝福，也是一種詛咒。

[[315780]]

當(dāng)涉及到足夠小的數(shù)據(jù)和足夠簡(jiǎn)單的操作時(shí)，Excel是王道。然而，一旦發(fā)現(xiàn)自己在努力走出這些區(qū)域，它就會(huì)變成一種痛苦。

當(dāng)然，可以使用ExcelVBA來解決這些問題，但是在2020年，幸運(yùn)的你不必這么做了!

如果有辦法把Excel和Python集成在一起，Excel……就會(huì)插上翅膀!

現(xiàn)在有了。一個(gè)名為xlwings的python庫(kù)允許用戶通過VBA調(diào)用python腳本并在兩者之間傳遞數(shù)據(jù)。

為什么要將Python與ExcelVBA集成?

事實(shí)上，用戶可以在VBA中做任何事情。所以，如果是這樣，為什么要使用Python?嗯，有很多原因。

1.在Excel中可以創(chuàng)建自定義函數(shù)，而不必學(xué)習(xí)VBA(如果讀者還不知道的話)

2.用戶對(duì)Excel很滿意

3.使用Python可以顯著加快數(shù)據(jù)操作

4.在Python中，幾乎所有東西都有庫(kù)(機(jī)器學(xué)習(xí)、數(shù)據(jù)科學(xué)等)

5.因?yàn)槟憧梢?!!

準(zhǔn)備使用xlwings

要做的第一件事，和想使用的任何新庫(kù)一樣，就是安裝它。這是非常容易做到的;有了這兩個(gè)命令，很快就能準(zhǔn)備就緒。所以，把命令輸入終端： 

pipinstall xlwings 

下載并安裝庫(kù)后，需要安裝Excel集成部分。確保已關(guān)閉所有Excel實(shí)例和任何終端類型： 

xlwings addin install 

假設(shè)沒有遇到錯(cuò)誤，應(yīng)該能夠繼續(xù)。然而，在Win10 的 Excel2016上，人們經(jīng)常會(huì)看到以下錯(cuò)誤： 

xlwings0.17.0 
[Errno 2] No such file or directory:'C:\\Users\\costa\\AppData\\Roaming\\Microsoft\\Excel\\XLSTART\\\xlwings.xlam' 
 

如果走運(yùn)地遇到了上述錯(cuò)誤，需要做的就是創(chuàng)建丟失的目錄。通過使用mkdir命令，可以很容易地做到這一點(diǎn)。就筆者而言，筆者做到了： 

mkdirC:\\Users\\costa\\AppData\\Roaming\\Microsoft\\Excel\\XLSTART 

假設(shè)excel與python庫(kù)的集成安裝成功，則可以立即注意到excel的主要區(qū)別： 

為xlwings啟用用戶定義函數(shù)

首先，需要加載Excel加載項(xiàng)。點(diǎn)擊Alt，L，H，然后導(dǎo)航到上面的目錄來加載插件。完成后，應(yīng)該能夠看到以下內(nèi)容： 

最后，需要啟用對(duì)VBA工程對(duì)象模型的信任訪問。可以通過導(dǎo)航到“文件>選項(xiàng)>信任中心>信任中心設(shè)置>宏設(shè)置”來執(zhí)行此操作：

xlwings入門

從Excel到Python(Python到Excel)有兩種主要方式。第一種是直接從VBA調(diào)用Python腳本，另一種是通過用戶定義的函數(shù)。快速看一下兩者。

為了避免任何混淆，每次都能正確設(shè)置，xlwings提供了創(chuàng)建Excel電子表格的功能，準(zhǔn)備就緒。下面來使用這個(gè)功能。使用終端，導(dǎo)航到喜歡的目錄并鍵入： 

xlwingsquickstart ProjectName 

把這稱之為MyFirstPythonXL。上面的命令將在預(yù)先導(dǎo)航的目錄中創(chuàng)建一個(gè)新文件夾，其中包含一個(gè)Excel工作表和一個(gè)python文件。 

打開.xlsm文件時(shí)，可以立即注意到一個(gè)名為xlwings.conf的新Excel工作表。如果要覆蓋xlwings的默認(rèn)設(shè)置，只需重命名此工作表并刪除起始下劃線。這樣，準(zhǔn)備工作就完成了，下面開始使用xlwings。

VBA到Python

在開始編碼之前，首先確保我們都在同一頁(yè)上。要打開ExcelVBA編輯器，請(qǐng)按Alt+F11。這將返回以下屏幕： 

帶xlwings的VBA編輯器

這里關(guān)鍵要注意的是，此代碼將執(zhí)行以下操作：

1.在與電子表格相同的位置查找Python腳本

2.查找與電子表格同名的Python腳本(但擴(kuò)展名為.py)

3.從Python腳本調(diào)用函數(shù)“main()”

言歸正傳，來看看幾個(gè)使用的實(shí)例。

例1：在Excel之外操作，并返回輸出

在本例中，將看到如何在Excel之外執(zhí)行操作，但隨后將結(jié)果返回到電子表格中。這可以有無限多的用例。

從CSV文件中獲取數(shù)據(jù)，對(duì)數(shù)據(jù)進(jìn)行修改，然后將輸出傳遞給Excel。這一操作很簡(jiǎn)單：

首先，VBA代碼：

它與默認(rèn)設(shè)置完全保持不變。

然后，Python代碼： 

importxlwings as xw 
import pandas as pddef main(): 
    wb = xw.Book.caller() 
    df =pd.read_csv(r'C:\temp\TestData.csv') 
    df['total_length'] =  df['sepal_length_(cm)'] +df['petal_length_(cm)'] 
    wb.sheets[0].range('A1').value = df 

結(jié)果如下： 

示例2：使用Excel輸入來驅(qū)動(dòng)操作

在本例中，從Excel讀取輸入，用Python對(duì)其進(jìn)行處理，然后將結(jié)果傳遞回Excel。

更具體地說，要讀一個(gè)問候語(yǔ)，一個(gè)名字和一個(gè)文件位置，在那里可以找到笑話。然后，Python腳本將從文件中隨機(jī)抽取一行，并返回一個(gè)笑話。

首先，VBA代碼：

它與默認(rèn)設(shè)置完全保持不變。

然后，Python代碼： 

importxlwings as xw 
import randomdef random_line(afile): 
    line = next(afile) 
    for num, aline in enumerate(afile,2): 
      if random.randrange(num): continue 
      line = aline 
    return line 
'Function from: stackoverflowdef main(): 
    wb = xw.Book.caller() 
    listloc =str(wb.sheets[0].range('B3').value) 
    fhandle = open(listloc, encoding ='utf-8')wb.sheets[0].range('A5').value = wb.sheets[0].range('B2').value + ' ' +wb.sheets[0].range('B1').value + ' here is a joke for you' 
    wb.sheets[0].range('A6').value =random_line(fhandle) 

結(jié)果為： 

帶xlwigs的用戶定義函數(shù)

用與以前幾乎相同的方式更改python文件中的代碼。為了將某些內(nèi)容轉(zhuǎn)換為Excel用戶定義函數(shù)，我們只需在函數(shù)所在的行之前包含“@xw.func”：

Python代碼： 

importxlwings as xw@xw.func 
def joke(x): 
    wb = xw.Book.caller() 
    fhandle = open(r'C:\Temp\list.csv') 
    for i, line in enumerate(fhandle): 
        if i == x: 
            return(line) 

結(jié)果是： 

如果你和小芯一樣，更喜歡使用Python而不是VBA，但又需要使用電子表格，這個(gè)工具是你的不二選擇，你可以把它當(dāng)作一個(gè)漂亮的小型數(shù)據(jù)庫(kù)。