用 Python 寫 Excel 巨集

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

什麼是「Python 寫 Excel 巨集」？

簡單的說，就是在 Python 程式內呼叫 Windows API 操控 Excel，具體的說是使用 xlwings 提供的封裝過的的 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 開發環境〉，讓你快速在二十分鐘內建出心愛的 Python 環境。

用 Python 操控 Excel

前面提過，用 Python 操控 Excel 是靠 xlwings 與 pywin32 兩個套件實現的，這與我們利用 Playwright 或 Selenium 做瀏覽器自動化的模式相當類似，所以用 Python 寫 Excel 巨集這件是我們也可以稱為用 Python 做 Excel 自動化。

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

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

初始化與讀檔、存檔 

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() 可以接受多種的儲存格標示法：

# 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 模式說明較能直觀感受到指令的回應：

>>> 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 首頁卻有著奇葩的烏龜人：

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

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

16

Catégories:

Python

Tags:

PythonExcel