excelperfect

標(biāo)簽：Python與Excel,xlwings

本文將向你展示如何使用Python xlwings庫(kù)自動(dòng)化Excel。毋庸置疑，Excel是一款非常棒的軟件，具有簡(jiǎn)單直觀的用戶界面，而Python是一種強(qiáng)大的編程語(yǔ)言，在數(shù)據(jù)分析方面非常高效。xlwings就像膠水一樣，將兩者連接到一起，讓我們能夠同時(shí)擁有兩者最好的一面。

你可以使用xlwings+Python執(zhí)行下列任務(wù)：

1.使用Python自動(dòng)化Excel，例如生成報(bào)告。

2.使用Python編寫宏，并通過單擊按鈕從Excel運(yùn)行。

3.使用Python編寫用戶定義的函數(shù)，并像調(diào)用任何Excel內(nèi)置函數(shù)一樣從Excel中調(diào)用這些函數(shù)。

聽起來(lái)很刺激？讓我們開始吧！

第一部分：安裝xlwings

安裝xlwings有兩個(gè)部分：Python庫(kù)和Excel加載項(xiàng)。

先安裝Python庫(kù)：

pip install xlwings

然后從xlwings的官方Github存儲(chǔ)庫(kù)下載這個(gè)Excel加載項(xiàng)，即頁(yè)面上的xlwings.xlam文件。（或者，你可以到知識(shí)星球中的完美Excel社群下載）

將xlwings.xlam文件放入Excel加載項(xiàng)文件夾，該文件夾所在位置為：

C:\用戶\xxxx\AppData\Roaming\Microsoft\AddIns

xxxx是計(jì)算機(jī)上自己的用戶名。

然后，打開Excel，選擇“文件->選項(xiàng)->加載項(xiàng)”。單擊“管理：Excel加載項(xiàng)”旁邊的“轉(zhuǎn)到”按鈕，如下圖1所示。

圖1

在“加載宏”對(duì)話框中，選取Xlwings前的復(fù)選框，如下圖2所示，單擊“確定”按鈕。

圖2

現(xiàn)在，Excel功能區(qū)中將出現(xiàn)一個(gè)名為“xlwings”的選項(xiàng)卡，如下圖3所示。

圖3

至此，設(shè)置已完成，我們可以使用用Python自動(dòng)化Excel了！

第二部分：自動(dòng)化Excel

運(yùn)行以下Python腳本，它將打開一個(gè)新的Excel實(shí)例。

import xlwings as xw

wb = xw.Book()

將數(shù)據(jù)寫入Excel

這里，wb引用新的（且打開的）Excel文件，同時(shí)它也是一個(gè)Python對(duì)象，這意味著我們可以在Python中操作它（Excel文件）。嘗試下面的代碼，它將允許你將值從Python輸入到Excel。

sheet = wb.sheets['Sheet1']

sheet.range('A1').value ='Hi,Excel,我來(lái)自Python'

圖4

我們也可以使用.range((x，y))表示法來(lái)引用Excel中的單個(gè)單元格，其中x表示行，y表示列。因此，.range((3，2))表示單元格B3。

sheet.range((3,2)).value = 'x軸'

sheet.range((3,3)).value = 'y軸'

for i in range(5):

   sheet.range((i+4,2)).value = i

圖5

也可以使用Python在Excel中編寫公式?；旧希覀兪窃谙騿卧裰袑懭胱址?。這里，我們要在另一列中計(jì)算x軸的指數(shù)值。在下面的代碼中，我們使用了“f-string”，這是從Python 3.6開始的一種改進(jìn)的字符串格式語(yǔ)法。

for i in range(5):

   sheet.range((i+4,3)).value = f'=exp(B{i+4})'

圖6

從Excel中讀取數(shù)據(jù)

從Excel讀取數(shù)據(jù)同樣簡(jiǎn)單，下面的代碼將Excel數(shù)據(jù)作為列表讀取到Python中。

data = sheet.range('B3:C8').value

圖7

如果要將Excel數(shù)據(jù)作為pandas數(shù)據(jù)框架讀入Python，代碼如下。

import pandas as pd

df = xw.Range('B3').expand().options(pd.DataFrame).value

df.reset_index(inplace=True)

.expand()自動(dòng)檢測(cè)數(shù)據(jù)的維度，.options()指定我們需要pandas數(shù)據(jù)框架。我們?cè)谀┪仓刂昧怂饕虼?/span>x軸將被視為列，而不是數(shù)據(jù)框架索引。

圖8

數(shù)據(jù)已經(jīng)讀入到Python，我們可以生成一個(gè)圖形，然后將其放入Excel文件中。為了繪制圖形，我們將使用matplotlib庫(kù)。

import matplotlib.pyplot as plt

fig = plt.figure()

plt.plot(df['x軸'],df['y軸'])

plt.xlabel('x-axis')

plt.ylabel('y-axis')

sheet.pictures.add(fig,name='MyPlot',update=True)

圖9

最后，正如我們對(duì)每個(gè)Excel電子表格所做的那樣，我們必須保存我們的工作并關(guān)閉文件。

wb.save('auto_excel_with_python.xlsx')

wb.close()

第三部分：在Python中編寫宏并在Excel中運(yùn)行

澄清一下，這里的“宏”不是指VBA編寫的宏，而是Python程序，可以從Excel執(zhí)行。然而，它需要一點(diǎn)VBA來(lái)允許Excel調(diào)用Python函數(shù)。

Python腳本

讓我們首先編寫一個(gè)簡(jiǎn)單的Python函數(shù)，該函數(shù)生成10個(gè)隨機(jī)數(shù)，然后將它們放在Excel工作表單元格A1中。注意，xw.Book.caller()引用當(dāng)前工作簿。

將以下腳本另存為“rand_10.py”。

import numpy as np

import xlwings as xw

def generate():

   wb = xw.Book.caller()

   wb.sheets[0].range('A1').value = np.random.rand(10)

Excel VBA & 鏈接到Python腳本

好了，現(xiàn)在有了Python腳本。接著，在Excel中按Alt+F11組合鍵，打開VBA編輯器。

在VBA編輯器中，單擊菜單“工具->引用”，找到并選取“xlwings”前的復(fù)選框，如下圖10所示，然后單擊“確定”按鈕。

圖10

接下來(lái)，單擊“插入——模塊”，插入一個(gè)標(biāo)準(zhǔn)模塊。在右側(cè)的代碼窗口，輸入以下VBA代碼。

Sub Rand_10()

   RunPython ('import rand_10; rand_10.generate()')

End Sub

這里需要注意的是:rand_10是Python腳本文件名。我們剛剛將腳本保存為”rand_10.py”。VBA代碼RunPython (”import rand_10；rand_10.generate()”)基本上是說(shuō)：導(dǎo)入名為”rand_10”的Python腳本，然后使用Python運(yùn)行函數(shù)generate()。

接下來(lái)，保存VBA代碼，現(xiàn)在我們要在Excel工作表中創(chuàng)建一個(gè)按鈕。返回Excel界面，在“開發(fā)工具”選項(xiàng)卡，單擊“插入->按鈕”，并指定剛創(chuàng)建的宏Rand_10。

單擊該按鈕，將在單元格A1至J1中填充10個(gè)隨機(jī)數(shù)，如下圖11所示。

圖11

注意：rand_10.py文件應(yīng)放置在下面的文件夾中：

C:\Users\xxxx\AppData\Local\Programs\Python\Python39\Lib\site-packages

xxxx是計(jì)算機(jī)上自己的用戶名。

或者：是否可以直接在代碼中指定該文件放置的位置，而不必非得將文件放在上述文件夾。

第四部分：在Python中編寫用戶定義的函數(shù)并在Excel中調(diào)用該函數(shù)

高級(jí)Excel用戶都知道，我們可以在VBA中創(chuàng)建用戶定義的函數(shù)。這項(xiàng)功能很棒，因?yàn)椴⒎撬袃?nèi)置的Excel函數(shù)都適合我們的需要。然而，VBA功能有限，使用xlwings，我們可以在Python中創(chuàng)建自己的用戶定義函數(shù)。我們所需要的只是一個(gè)Python腳本，并在Excel中進(jìn)行一些設(shè)置來(lái)實(shí)現(xiàn)。

Python腳本

編寫一個(gè)簡(jiǎn)單的Python函數(shù)，計(jì)算數(shù)字的平方。

import xlwings as xw

@xw.func

def square(x):

   return x ** 2

這里需要注意兩件事：

• @xw.func是一個(gè)裝飾器。必須將其添加到def之前，以讓xlwings知道這是一個(gè)用戶定義的函數(shù)。

• 該函數(shù)必須返回某些內(nèi)容，以便將返回的值傳遞到Excel中。

Excel設(shè)置

默認(rèn)設(shè)置預(yù)計(jì)Python代碼和Excel文件為：

• 在同一目錄中

• 名稱相同，但Python文件以.py結(jié)尾，Excel文件以.xlsm（或.xlsb）結(jié)尾

為了演示，我將把文件命名為“square.py”和“square.xlsm”。在Excel中打開square.xlsm，轉(zhuǎn)到xlwings選項(xiàng)卡，然后單擊Import Functions（導(dǎo)入函數(shù)）。

如果一切正常，意味著成功導(dǎo)入了Python函數(shù)，我們就可以直接在工作表中使用square()函數(shù)了，如下圖12所示，在單元格中輸入=square(A1)。

圖12

注意到，當(dāng)鍵入函數(shù)時(shí)，square實(shí)際上會(huì)顯示在函數(shù)列表中——我們可以像使用Excel內(nèi)置函數(shù)一樣使用Python函數(shù)，并且可以將單元格引用傳遞到函數(shù)中。我們似乎在使用Excel函數(shù)，但其實(shí)在后臺(tái)，Python正在進(jìn)行所有計(jì)算，然后通過Excel向用戶顯示結(jié)果。這意味著，由于Python的強(qiáng)大功能，我們可以創(chuàng)建非常復(fù)雜的函數(shù)。

要說(shuō)明的是，在某些情況下，可能會(huì)彈出“Automatio error 440”錯(cuò)誤消息提示，則需要進(jìn)行一些設(shè)置。

首先，到“信任中心”的“宏設(shè)置”選項(xiàng)卡中，選取“信任對(duì)VBA工程對(duì)象模型的訪問(V)”前的復(fù)選框，如下圖13所示。

圖13

然后，到VBA編輯器中，設(shè)置對(duì)“xlwings”的引用，如上文中圖10所示。

調(diào)試

剛開始可能會(huì)遇到的兩個(gè)常見錯(cuò)誤是：

1.“Automatio error 440”（自動(dòng)化錯(cuò)誤404）。上文中已討論了如何修復(fù)此錯(cuò)誤，確保Excel宏設(shè)置正確。

2.鍵入用戶定義的函數(shù)時(shí)，單元格中會(huì)顯示“Object Require”（對(duì)象要求）。確保在VBA編輯器菜單“工具->引用”中選取了“xlwings”，并將更改保存到相應(yīng)的Excel文件中。有時(shí)，當(dāng)打開多個(gè)Excel工作表時(shí)，我們可能會(huì)無(wú)意中將此更改應(yīng)用于另一個(gè)文件。

注：本文學(xué)習(xí)整理自pythoninoffice.com。

歡迎到知識(shí)星球：完美Excel社群，進(jìn)行技術(shù)交流和提問，獲取更多電子資料，并通過社群加入專門的微信討論群，更方便交流。