本系列將結(jié)合實(shí)際應(yīng)用,，教會(huì)你如何利用xlwings,，把Excel與Python的各自優(yōu)勢(shì)充分發(fā)揮

說到必需學(xué)習(xí)的數(shù)據(jù)工具，Excel 無疑是唯一的答案 ,，各種基本操作,、函數(shù)公式、透視表,，這些都是非常好用的功能,，加上 vba 可以實(shí)現(xiàn)自動(dòng)化需求。

自動(dòng)化控制 Excel,，我認(rèn)為 vba 是目前最好的平臺(tái),。但是 vba 的數(shù)據(jù)處理能力實(shí)在有限(別把表格處理與數(shù)據(jù)處理混淆)。

而 Python 之所以在數(shù)據(jù)領(lǐng)域受寵,，完全是因?yàn)樗幸恍┓浅：糜玫膸?kù)(numpy,、pandas等)，如果沒有這些庫(kù),，實(shí)際上 Python 與 vba 沒有多大區(qū)別(只是在數(shù)據(jù)處理方面),。但是 Python 做數(shù)據(jù)任務(wù)有個(gè)不太好的地方，沒有一個(gè)舒服的操作界面(雖然有許多第三方庫(kù)做界面,，但是實(shí)在太麻煩)

Excel 就是一個(gè)很好的操作界面，為何不結(jié)合他們,？

本系列文章我將完成一些小工具的制作,，通過 Excel 完成各種輸出格式的自動(dòng)化，而把數(shù)據(jù)處理交給 pandas 完成,。

這里有我的 pandas 專欄,，我相信市面上還沒有這么多干貨的pandas教程：

這是一個(gè)能讓你通過簡(jiǎn)單操作，即可對(duì)數(shù)據(jù)進(jìn)行各種操作的小工具,，如下動(dòng)圖：

此工具界面完全使用 Excel 制作(大部分情況下不需要編寫任何 vba 代碼)，后臺(tái)處理使用 Python(大部分情況使用 pandas),。

xlwings 是 Python 的一個(gè)第三方庫(kù),，主要用于讓你的 Python 代碼可以在 Excel 上被調(diào)用。

我們要借助 xlwings 的一個(gè)開發(fā)工具,，因此執(zhí)行如下命令行：

這個(gè)工具只是方便你開發(fā)使用,，實(shí)際使用時(shí)并不需要安裝此工具

此時(shí)你打開 Excel ，應(yīng)該會(huì)看到 xlwings 的加載項(xiàng)

暫且不解釋他的原理,，稍后在實(shí)踐中再講解其中的機(jī)制,。

接下來,，我們需要生成一個(gè)項(xiàng)目，說白了,，就是生成一些必要的文件：

首先導(dǎo)航到你的項(xiàng)目文件夾中,，執(zhí)行如下命令：

此時(shí)你會(huì)發(fā)現(xiàn)項(xiàng)目文件夾中生成了一個(gè)名為 'myproject' 的文件夾,，其中有文件：

接下來開始我們的小工具制作

從一個(gè)簡(jiǎn)單任務(wù)開始,，當(dāng)我們?cè)谝粋€(gè) Excel 單元格上輸入文件路徑與工作表名字，下方顯示文件中的數(shù)據(jù),。

這通過動(dòng)態(tài)數(shù)組公式完成這個(gè)任務(wù),。首先打開 myproject.py 文件，自定義一個(gè)函數(shù)：

Python 的代碼已經(jīng)有了,，但是 Excel 是不可能直接識(shí)別你定義的函數(shù)。

幸運(yùn)的是,，Excel 可以識(shí)別 vba 定義的函數(shù),。因此，我們需要 xlwings 幫我們自動(dòng)生成 vba 代碼,。

打開 Excel 文件 myproject.xlsm(注意要啟動(dòng)宏)：

此時(shí)我們輸入函數(shù)公式時(shí),，就能從提示中看到函數(shù)：

按照參數(shù),，選擇對(duì)應(yīng)的單元格引用即可：

回車后發(fā)現(xiàn)返回一段錯(cuò)誤信息：

輸入完整的文件路徑即可：

如果文章只是簡(jiǎn)單列出操作步驟，那么這是一個(gè)不合格的教程,。你在不懂原理的情況下,，很多問題都無法自己解決。

接下來我將講解其運(yùn)行機(jī)制的直覺理解,。

目前為止,，我們沒有編寫一句 vba 代碼，只是簡(jiǎn)單定義出一個(gè)加載數(shù)據(jù)的 Python 自定義函數(shù),，就可以在 Excel 上使用公式實(shí)現(xiàn)效果,。

從步驟上來說：

那么為什么需要點(diǎn)擊 '導(dǎo)入函數(shù)' 按鈕？

如果我修改了 Python 代碼,，需要重新點(diǎn)擊這個(gè)按鈕嗎,？

首先，我們之所以能在 Excel 上輸入公式時(shí),，出現(xiàn)我們的自定義函數(shù),，是因?yàn)樵谶@個(gè) Excel 文件中，存在 vba 代碼,，定義了同名的方法：

vbe 是 編寫 vba 代碼的界面,。通過快捷鍵 alt + F11 即可打開

因此，Excel 公式執(zhí)行時(shí),，會(huì)轉(zhuǎn)而執(zhí)行 Python 文件中的同名函數(shù),。

理解這點(diǎn)非常重要,，從中可以得知：

例如,，修改 myproject.py 中的代碼：

修改后,，保存一下此 Python 文件，在 Excel 上無須點(diǎn)擊'導(dǎo)入函數(shù)'按鈕,，只要公式有刷新(比如修改公式引用到的單元格的值),，就能看到最新結(jié)果：

你可能會(huì)覺得每次修改都點(diǎn)一下導(dǎo)入按鈕，萬(wàn)無一失,。

如果你是制作工具給別人使用的情況下,，就需要知道代碼變動(dòng)后，哪些文件(Excel或Python文件)需要更新

首先，我們希望返回結(jié)果不要把 DataFrame 的行索引輸出：

這可以通過裝飾器的參數(shù)設(shè)置：

你會(huì)發(fā)現(xiàn)即使數(shù)據(jù)文件就在項(xiàng)目文件夾中,，使用相對(duì)路徑是讀取不到文件,。

但是，每次輸入全路徑太麻煩了,，如果能輸入相對(duì)路徑就很好了,。

這問題由于 xlwings 在執(zhí)行 vba 的方法時(shí)，會(huì)啟動(dòng)一個(gè) Python 進(jìn)程(只有首次運(yùn)行的時(shí)候),，然后加載 myproject.py 中的代碼,。但他沒有設(shè)置啟動(dòng)目錄。

因此默認(rèn)情況下啟動(dòng)目錄是 Python.exe 所在目錄,。

我們只需要在 myproject.py 中修改啟動(dòng)目錄即可：

現(xiàn)在可以支持相對(duì)路徑：

文件名字如果可以下拉選擇就好了,！

我們來看看如何實(shí)現(xiàn)

首先，我們需要一個(gè)能找出 myproject.py 文件所在目錄的所有 Excel 文件,，我們使用 Python 實(shí)現(xiàn)這功能(這不是 vba 擅長(zhǎng)的)：

在 Excel 文件 myproject.xlsm 中，創(chuàng)建一個(gè)新的工作表(示例中名字為 Sheet2),，執(zhí)行這個(gè)公式：

到界面工作表,，為 B1 單元格設(shè)置數(shù)據(jù)有效性：

現(xiàn)在可以下拉選擇文件：

目前這個(gè)功能無法隨時(shí)監(jiān)控文件夾的文件變化，后面我們?cè)傧朕k法解決

今天內(nèi)容已經(jīng)比較多了,，以后我們將繼續(xù)完善和添加其他功能到此工具上,，過程中將講解更多相關(guān)機(jī)制：