Python 學習筆記 : 使用 xlwings 套件操作 Excel 試算表 (一)

我在 6/7 時因為上了高啟昌老師的課而首次接觸到 Python 的 Excel 處理套件 xlwings, 它最大的好處是可以在開啟 xlsx 試算表檔案的情況下操作工作表, 可以同步看到工作表上的資料跳動 (Python 與 Excel 雙向操作); 而另一個較老牌的 Openpyxl 套件則不行, 若 xlsx 檔案被 Excel 開啟中會被鎖住, OpenPyXL 將無法操作工作表.   

不過, OpenPyXL 的好處是, 即使沒有安裝 Excel 軟體仍可使用, 而 xlwings 則是必須依賴 Excel 軟體才能使用, 在樹莓派上雖然可以順利安裝 xlwings 套件, 但無法操作 xlsx 檔案 (因為 Excel 只有 Windows 與 macOS 版). 由於我主要的運算機器是樹莓派 (嵌入式設備, 可以 24 小時低功率運轉), 所以之前我著重於 OpenPyXL 的學習, 參考如下筆記 : 

# Python 學習筆記 : 使用 OpenPyXL 操作 EXCEL 試算表 (一)

# Python 學習筆記 : 使用 OpenPyXL 操作 EXCEL 試算表 (二)

# Python 學習筆記 : 使用 OpenPyXL 操作 EXCEL 試算表 (三)

# Python 學習筆記 : 使用 OpenPyXL 操作 EXCEL 試算表 (四)

# Python 學習筆記 : 使用 OpenPyXL 操作 EXCEL 試算表 (五)

在許多可操作 Excel 試算表檔案的套件中, xlwings 是唯一真正能編輯任何格式的 Excel 檔案的套件 (不會改變或丟棄既有內容與格式), 讓使用者可輕鬆地讀寫 Excel 試算表中的資料 (例如可以不必存檔就能動態地讀取 Excel 儲存格內容, 用 OpenpyXL 須先存檔關檔才能存取), 是 Excel VBA 的 Python 版, 最主要的用途是可利用 Excel 作為使用者介面來打造一個互動式應用. 

參考書籍 : 

1. 超簡單! Python 與 Excel 整合應用 150 技 (碁峰 2022) 
2. Python for Excel｜自動化與資料分析的現代開發環境  (歐萊禮 2021) 第 9 章
3. Python x Excel VBA x Javascript 網路爬蟲實戰演練 (碁峰 2021) 第 3 章

最近從母校圖書館借到這三本好書, 所以要開始來學習 xlwings 套件了.

Source : 博客來

此書範例程式下載網址 :

# http://books.gotop.com.tw/v_ACL065200 

Source : 博客來

此書範例程式下載網址 :

# https://github.com/fzumstein/python-for-excel

Source : 博客來

xlwings 有完整的 API 說明文件, 參考 :

# https://docs.xlwings.org/en/stable/api.html

以下是我讀這三本書的測試筆記 : 

1. 安裝 xlwings 套件 : 

在 Window 下直接用 pip install xlwings 套件 : 

C:\Users\User>pip install xlwings    

Collecting xlwings

  Downloading xlwings-0.27.8-py3-none-any.whl (1.0 MB)

Requirement already satisfied: pywin32>=224 in c:\python37\lib\site-packages (from xlwings) (227)

Installing collected packages: xlwings

Successfully installed xlwings-0.27.8

可見 xlwings 套件很小, 才 1MB 而已. 匯入時通常會取 xw 別名 :

>>> import xlwings as xw   

>>> xw.__version__      

'0.27.8'

Anaconda 已將 xlwings 預載, 故 Anaconda 使用者毋須安裝 xlwings. 

注意, 在 Windows 上使用 xlwings 至少須安裝 Excel 2007 以上版本; macOS 系統則須安裝 Excel 2016 版以上, 或者訂購 Microsoft 365 的桌面 Excel. 

2. 檢視 xlwings 套件之成員 : 

先來檢視 xlwings 套件的成員內容, 以下使用一個自訂模組 members, 其 list_members() 函式會列出模組或套件中的公開成員 (即屬性與方法), 參考 :

# Python 學習筆記 : 檢視物件成員與取得變數名稱字串的方法

>>> import xlwings as xw     

>>> import members   

>>> members.list_members(xw)      

App <class 'type'>

Book <class 'type'>

Chart <class 'type'>

Engine <class 'type'>

LicenseError <class 'type'>

Name <class 'type'>

PRO <class 'bool'>

Picture <class 'type'>

Range <class 'type'>

RangeColumns <class 'type'>

RangeRows <class 'type'>

Shape <class 'type'>

ShapeAlreadyExists <class 'type'>

Sheet <class 'type'>

USER_CONFIG_FILE <class 'str'>

XlwingsError <class 'type'>

apps <class 'xlwings.main.ActiveEngineApps'>

arg <class 'function'>

books <class 'xlwings.main.ActiveAppBooks'>

constants <class 'module'>

conversion <class 'module'>

engines <class 'xlwings.main.Engines'>

expansion <class 'module'>

func <class 'function'>

gencache <class 'module'>

get_udf_module <class 'function'>

import_udfs <class 'function'>

load <class 'function'>

main <class 'module'>

os <class 'module'>

ret <class 'function'>

serve <class 'function'>

server <class 'module'>

sheets <class 'xlwings.main.ActiveBookSheets'>

sub <class 'function'>

sys <class 'module'>

udfs <class 'module'>

utils <class 'module'>

view <class 'function'>

wraps <class 'function'>

其中有四個類別用來建立 Excel 中的物件結構 :

• App : 代表 Excel 應用程式實例
• Book : 代表工作簿 (包含多個工作表)
• Sheet : 代表工作表 (二維網格的資料表)
• Range : 代表儲存格 (單一儲存格或矩形範圍內之儲存格)

這些 xlwings 的物件在結構上是階層式的, 與 Excel 物件的對應如下圖所示 :

最上層物件 App 代表 Excel 應用程式實例 (即 Excel 視窗), Book 為工作簿物件, 裡面包含一個以上的工作表 (Sheet 物件), 每一個工作表又由 Range 物件 (即儲存格) 構成, 不管是單一儲存格還是方形範圍內之多儲存格, 其類型都是 Range 物件. 

3. 用 xlwings 建立 Excel 工作簿 : 

 

呼叫 App 類別的的建構子 App() 即可建立一個 App 物件 : 

 

>>> import xlwings as xw    

>>> app=xw.App(visible=True, add_book=False)  

>>> type(app)   

<class 'xlwings.main.App'>      # 建立了一個 App 物件

此處傳入參數 visible=True 表示要顯示開啟之 Excel 視窗, 而 add_book=False 則是不要新增預設之工作簿, 通常是為了自行新增工作簿或開啟已存在的 xlsx 檔案, 不希望 App 裡面有預設之工作簿以免干擾. 以上述指令建立 App 後會自動開啟一個 Excel 視窗 :

這是一個空白的 Excel 應用程式實例, 裡面並無工作簿, 可以用 App 物件的 books 屬性檢視 :

>>> app.books    

Books([])

>>> type(app.books)    

<class 'xlwings.main.Books'>    

(1). 呼叫 app.books.add() 建立 Book 物件 : 

books 屬性類型為一個 Books 物件, 這是一個存放 Book 物件的容器, 此處內容為空 (因為在建立 App 時傳入 add_book=False 之故). 可以呼叫 Books 物件的 add() 方法建立工作簿 (此方法無傳入參數), 例如 : 

>>> wb=app.books.add()       # 建立工作簿 Book 物件

>>> type(wb)       

<class 'xlwings.main.Book'>      

可見 add() 方法會建立一個 Book 工作簿物件, 這時上面自動開啟的 Excel 視窗 (App 物件實例) 也會同步出現工作簿, 裡面有一個預設工作表 '工作表 1' :

這就是 xlwings 與 Excel 同步互動的效果, 對儲存格的操作也會同步反映在 Excel 視窗上. 

(2). 利用 wb.sheets[] 取得 Sheet 物件 : 

接著可以用工作簿 Book 物件的 sheets 屬性以索引 (0 起始) 取得工作表 Sheet 物件 :

>>> sheet=wb.sheets[0]     

>>> type(sheet)     

<class 'xlwings.main.Sheet'>     # 取得工作簿裡面的一個 Sheet 物件

由於目前只有一個工作表, 所以索引 0 即代表 '工作表1' 這張工作表. 也可以用工作表名稱 (例如此處是 '工作表1') 當作索引, 效果是一樣的 :

>>> sheet=wb.sheets['工作表1']   

>>> type(sheet)   

<class 'xlwings.main.Sheet'>      # 取得工作簿裡面的一個 Sheet 物件

(3). 呼叫 sheet.range() 取得 Range 物件 : 

接著便可以用 Sheet 物件的 range() 方法存取此工作表內的儲存格, 可傳入位置索引字串來定位儲存格, 與 Excel 用法一樣, 欄索引由左向右依序是 A, B, C, D, .... Z, AA, AB, AC, .... 而列索引則是由上而下依序為 1, 2, 3, 4, 5, .... 等, 位置索引之範例如下 : 

range() 的儲存格定位字串	說明
'A1'	儲存格 A1
'A1:B2'	從 A1 至 B2 的儲存格 (範圍)
'A:A'	A 欄的所有儲存格
'A:B'	A 欄與 B 凡的所有儲存格
'1:1'	第一列的所有儲存格
'1:5'	第一列到第五列的所有儲存格

例如 : 

>>> rangeA1=sheet.range('A1')   

>>> rangeA1   

<Range [活頁簿2]工作表1!$A$1>   

>>> type(rangeA1)   

<class 'xlwings.main.Range'>   

>>> rangeA1B2=sheet.range('A1:B2')   

>>> rangeA1B2   

<Range [活頁簿2]工作表1!$A$1:$B$2>     

>>> type(rangeA1B2)   

<class 'xlwings.main.Range'>   

可見不管是單一儲存格或是矩形範圍內的多個儲存格, 其資料類型都是 Range 物件. 呼叫 Range 物件的 value() 法即可存取儲存格內容 (傳入參數為 setter, 不傳參數為 getter), 例如在 A1 儲存格填入一個 2*2 串列資料, 它會被存入 A1:B2 範圍內的儲存格裡, 在 A4 儲存格存入 'Hello World' 則只存在 A4 裡 :

>>> sheet.range('A1').value=[[1, 2], [3, 4]]    

>>> sheet.range('A4').value='Hello World'    

結果如下 :

工作表內容部分放大如下 : 

讀取儲存格內容 : 

>>> sheet.range('A1').value    

1.0

>>> sheet.range('A1:B2').value    

[[1.0, 2.0], [3.0, 4.0]]

>>> sheet.range('A4').value   

'Hello World'

Range 物件可以用中括號 [] 進行索引 (indexing, 0 起始), 例如 : 

>>> sheet.range('A1:B2')[0, 0]    

<Range [活頁簿2]工作表1!$A$1>

>>> sheet.range('A1:B2')[1, 1]   

<Range [活頁簿2]工作表1!$B$2>

>>> sheet.range('A4')[0, 0]      

<Range [活頁簿2]工作表1!$A$4>

呼叫 Sheet 物件的 range() 方法會傳回一個 Range 物件, 如果它是一個包含多儲存格的矩形範圍, 就可以用 0 起始的 [列, 行] 索引來存取儲存格, 如果傳入 range() 的是像 'A1' 這樣的單一儲存格, 就只能用 [0, 0] 存取, 否則就會出現 IndexError :

>>> sheet.range('A4')[0, 1]    

Traceback (most recent call last):

  File "<pyshell>", line 1, in <module>

  File "C:\Python37\lib\site-packages\xlwings\main.py", line 2332, in __getitem__

    "Column index %s out of range (%s columns)." % (col, n)

IndexError: Column index 1 out of range (1 columns).

除了可以用索引來存取 Range 物件的儲存格資料外, 也可以用切片 (slicing), 切片的結果也是傳回一個 Range 物件. 注意, 切片的索引是 0 起始的 (0 為首列首欄), 例如 : 

>>> sheet.range('A1:B2')[:, 1]   

<Range [活頁簿2]工作表1!$B$1:$B$2>

>>> sheet.range('A1:B2')[1, :]   

<Range [活頁簿2]工作表1!$A$2:$B$2>

上面第一個例子用 [:, 1] 切片, 表示取出 Range 物件所包含的範圍中 B 欄 (A 欄為 0, B 欄為 1, C 欄為 2, ...) 所有列的儲存格, 所以結果會傳回 B1 與 B2 儲存格組成之 Range 物件. 第二例用 [1, :] 切片, 表示要取出 Range 物件所包含的範圍中列 1 所有欄的儲存格, 故傳回 A2 與 B2 儲存格組成之 Range 物件. 

(4). 使用 sheet[] 取得 Range 物件 : 

除了呼叫 Sheet 物件的 range() 方法取得指定範圍儲存格之 Range 物件外, 也可以直接在 Sheet 物件上使用中括號 [] 來取得, [] 內可用上面 range() 方法的儲存格定位字元, 也可以 0 起始的 [列, 行] 索引, 例如 :

>>> sheet['A1']   

<Range [活頁簿2]工作表1!$A$1>     

>>> sheet['A1:B2']    

<Range [活頁簿2]工作表1!$A$1:$B$2>

>>> sheet['A4']    

<Range [活頁簿2]工作表1!$A$4>   

>>> sheet['A1'].value    

1.0   

>>> sheet['A1:B2'].value       

[[1.0, 2.0], [3.0, 4.0]]    

>>> sheet['A4'].value      

'Hello World'

可見與上面呼叫 range() 方法的結果相同. sheet[] 比 range() 方法還多了可用 0 起始索引取得 Range 物件的方法, 例如 : 

>>> sheet[0, 0]       

<Range [活頁簿2]工作表1!$A$1>

>>> sheet[:2, :2]       

<Range [活頁簿2]工作表1!$A$1:$B$2>

>>> sheet[3, 0]           # A4 在列 3 欄 0                 

<Range [活頁簿2]工作表1!$A$4>

>>> sheet[0, 0].value    

1.0

>>> sheet[:2, :2].value    

[[1.0, 2.0], [3.0, 4.0]]

>>> sheet[3, 0].value   

'Hello World'

可見也與上面呼叫 range() 方法的結果相同. 既然用 [] 運算符也可取得相同的 Range 物件, 當然也可以使用索引, 例如 : 

>>> sheet['A1:B2'][0, 0]    

<Range [活頁簿2]工作表1!$A$1>

>>> sheet['A1:B2'][1, 1]      

<Range [活頁簿2]工作表1!$B$2>

>>> sheet[:2, :2][0, 0]      

<Range [活頁簿2]工作表1!$A$1>

>>> sheet[:2, :2][1, 1]     

<Range [活頁簿2]工作表1!$B$2>

也可以使用切片 : 

>>> sheet['A1:B2'][:, 1]   

<Range [活頁簿2]工作表1!$B$1:$B$2>

>>> sheet['A1:B2'][1, :]   

<Range [活頁簿2]工作表1!$A$2:$B$2>

>>> sheet[:2, :2][:, 1]   

<Range [活頁簿2]工作表1!$B$1:$B$2>

>>> sheet[:2, :2][1, :]    

<Range [活頁簿2]工作表1!$A$2:$B$2>

兩組中括號很容易讓人忘記其意義, 第一個中括號是從 Sheet 物件中取得指定範圍的 Range 物件; 而第二個中括號則是在 Range 物件中進行索引或切片. 

(5). 呼叫 sheet.cells() 取得 Range 物件 :

取得 Range 物件還可以使用 Sheet 物件的 cells(列, 行) 方法並傳入 1 起始的列與行索引 (所以 A 欄是行 1, 首列是列 1), 但這只能取得單一儲存格, 例如 :   

>>> sheet.cells(1, 1)    

<Range [活頁簿2]工作表1!$A$1>    

>>> sheet.cells(4, 1)  

<Range [活頁簿2]工作表1!$A$4>   

>>> sheet.cells(1, 1).value    

1.0

>>> sheet.cells(4, 1).value         

'Hello World' 

(6). 修改儲存格內容 :

以上面幾種存取 Range 物件的方式修改儲存格內容 : 

>>> sheet.range('A1').value=5                     # 修改 A1 儲存格之值

>>> sheet['B1'].value=6                                # 修改 B1 儲存格之值

>>> sheet.range('A2:B2').value=[7, 8]         # 修改 A2, B2 儲存格之值

>>> sheet.cells(4, 1).value='Hello Tony!'     # 修改 A4 儲存格之值  

結果如下 : 

可見原來的四個儲存格內容都被改變了. 

參考 :

# 处理Excel 的模块 xlwings

# Python操作Excel的Xlwings教程（一）

# Python操作Excel的Xlwings教程（二）

# Python操作Excel的Xlwings教程（三）

# Python操作Excel的Xlwings教程（四）

# Python操作Excel的Xlwings教程（五）

# Python操作Excel的Xlwings教程（六）

# Python操作Excel的Xlwings教程（七）

# Python操作Excel的Xlwings教程（八）