用 Python 寫 Excel 巨集
Tags: python, excel
Published: 2021-10-18 01:46:20

說到 Excel 巨集，最直覺聯想到的應該會是 VBA，但其實微軟不只提供一種巨集語言，例如在網頁版的 Excel 並不支援 VBA，微軟給用戶的巨集語言是 Office Scripts，Office Scripts 是 TypeScript 的語法加上 Excel 物件模型的綜合體；又或者是增益集，微軟大大推的也是 JavaScript / TypeScript 的開發方案，而本文要介紹的是以 Python 為基礎的 Excel 開發模式。

## 什麼是「Python 寫 Excel 巨集」？

簡單的說，就是在 Python 程式內呼叫 Windows API 操控 Excel，具體的說是使用 [xlwings](https://www.xlwings.org/) 提供的封裝過的的 Excel COM API 以及透過 pywin32 提供的 Windows API 橋接能力來實現在 Python 程式操控 Excel 的目的，這樣的組合用起來和 VBA 寫巨集有八成像，所以才說「用 Python 寫 Excel 巨集」。

這種模式其實和在 C# 調用 Excel COM API 是一樣的，差別在於 Python 在數據處理領域擁有 C# 無法比擬的**成熟**生態系。

和一般用 Python 套件讀寫 Excel 檔案不同的是，Python + xlwings 的做法是去操控 Excel，所以不會有讀寫後格式跑掉的問題，就算有也是 Excel 自己造成的，而用戶對 Excel 自己造成的跑版問題有著異常高的寬容度。（反之，LibreOffice 開 Office 文件有點小跑版，用戶就會勃然大怒。）

## 為什麼是 Python，VBA 不香嗎？或 JS 不香嗎？

改用 Python 最顯著的好處當然是可以享用 Python 生態系的一切工具，特別是在數據處理這塊，這些好處列舉如下：

- 可以用 pandas 等著名的數據套件處理 Excel 資料。
- 可以在 Jupyter Notebook 環境或任何你喜愛的編輯器寫 Python 腳本，再也不用碰那陳年的 VBA 編輯器。
- 你的巨集腳本可以被版控、被集中整理，而不會隨著 Excel 檔案散落四方，因而享有所有版控的特性，多人協作、功能分支等等。
- 你還可以在 Python 腳本內透過 xlwings 調用 Excel 公式。

這幾點在其他的語言能否找到與之相當的工具，就看各個語言的套件生態豐不豐富了，至少在 npm 裡面我沒找到相對應的工具鍊。

而 VBA 本身，雖然也曾經輝煌過，十幾年前 AutoCAD 和 CorelDRAW 也一度支援 VBA 巨集，後來 AutoCAD 回歸 Lisp 的懷抱、CorelDRAW 也認清要平面設計師寫巨集是不切實際的奢望…，現在的 VBA 不論對微軟或對我們來說，都更像是個歷史遺產而不是資產，因此對沒有歷史包袱的巨集開發者來說，改用 Python 完全是個可以考慮的選項。

當然，改用 Python 必然要建立 Python 開發環境，這點額外的負擔可以參見〈[建置 Python 3.9 開發環境](<https://editor.leonh.space/2020/set-a-development-environment-of-python-3-9/>)〉，讓你快速在二十分鐘內建出心愛的 Python 環境。
## 用 Python 操控 Excel

前面提過，用 Python 操控 Excel 是靠 [xlwings](https://www.xlwings.org/) 與 pywin32 兩個套件實現的，這與我們利用 [Playwright](@/2021/2021-07-07-Playwright/index.md) 或 Selenium 做瀏覽器自動化的模式相當類似，所以用 Python 寫 Excel 巨集這件是我們也可以稱為用 Python 做 Excel 自動化。

先放一部 xlwings 的影片感受一下操作體驗：

{% youtube A3jrUXNokYk %}

---

這裡我們直接用程式說明 xlwings 的用法。

### 初始化與讀檔、存檔

```py
import xlwings as xw

with xw.App(add_book=False, visible=True) as app:
    wb: xw.Book = app.books.open('example.xlsx')
    sheet: xw.Sheet = wb.sheets[0]

    # handle the sheet

    wb.save()
```

在引入 `xlwings` 並命名為 `xw` 後，我們建立了一個 `App` 物件，因為有存取 Excel 及開關檔案的原因，`App` 是被設計成支援 context manager 的，因此我們用 `with` 敘述讓 Python 自動幫我們處理掉區塊執行後自動關閉 Excel 的瑣事。

接著用一個 `book` 物件叫 Excel 開啟 example.xlsx 檔案，再把檔案內的第一個工作表指到 `sheet`，最後我們用 `save()` 叫 Excel 幫我存檔。

中間的 `# handle the sheet` 部分，典型的操作是找到你要的儲存格，再做後續的讀取、運算、寫入等工作。

### 選擇儲存格

儲存格的選定，不論是單格或一個範圍，都是用 xlwings 的 `range()` 函式。`range()` 可以接受多種的儲存格標示法：

```py
# Excel 標示法－單格
sheet.range('A1')

# Excel 標示法－多格
sheet.range('A1:C3')

# 以 1 為首的 tuple (row, column) 標示法－單格
sheet.range((1,2)) # B1

# 以 1 為首的 tuple (row, column) 標示法－多格
sheet.range((1,2), (3,4)) # B1:D3
```

建議不要用 Excel 標示法，因為欄數一超過 26 欄還要去處理 `AA`、`AB`、`AC` 這類兩碼欄號的邏輯問題。

### 儲存格的取值與賦值

取值與賦值也相當直覺，下面改用 Python REPL 模式說明較能直觀感受到指令的回應：

```py
>>> rng1 = sheet.range((1,2)) # B1
>>> rng1.value
'每次 Jira 的卡頓都令人抓狂'
>>> rng1.value = '用戰術上的勤奮掩蓋戰略上的怠惰'
>>> rng1.value
'用戰術上的勤奮掩蓋戰略上的怠惰'

>>> rng2 = sheet.range((1,2), (1,4)) # B1:D1
>>> rng2.value
['a', 'b', 'c']
>>> rng2.value = ['d', 'e', 'f']
>>> rng2.value
['d', 'e', 'f']

>>> rng3 = sheet.range((10,1), (11,3)) # A10:C11
>>> rng3.value
[['a', 'b', 'c'], [10.0, 20.0, 30.0]]
```

對於 `A10:C11` 這樣 3 * 2 的範圍，對應的值也會以 3 * 2 的巢狀二維陣列形式表示。

只要靠 `range()` 搭配程式本身的迴圈或迭代結構，就可以完成大部分的任務囉，是不是相當簡單直覺呢！

### 小訣竅

分享一點小訣竅：

- 一個 xlwings 的 `App` 可以開啟多份 Excel 檔案，可以互相剪剪貼貼。
- 也可以開多個 xlwings 的 `app`，此時他們會有各自的程序 ID。

## 結語

VB 大哥已死，小弟弟 Small Basic 又注定只能是玩具不能是工具，肩負著教育責任的 Small Basic 首頁卻有著奇葩的烏龜人：

![Small Basic 烏龜人](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dhfnvuptf78eeekkt3p6.png)

在這樣的勢態下，微軟僅容你 VBA 苟活，至於那美好的未來，看來是 Office Scripts 會與我們一同走過，但在 Office Scripts 尚未支援桌面版 Excel 的當下，對於沒有歷史包袱的我們，改用 Python 可能是更好的選擇，而且身分還能從「寫巨集的😞」搖身一變成🕺「大數據工程師🤓」，一整個潮到出汁💦。

本文僅介紹 xlwings 最常用的幾個功能，但 xlwings 的能力遠不止於此，它還有個特異功能，用 xlwings 的 CLI 指令讀檔，它會以 HTTP 服務的方式供應那份 Excel 檔的 API，各種花式用法請參閱 [xlwings 文件](https://docs.xlwings.org/zh_TW/latest/)。