2022年10月17日 星期一

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 的學習, 參考如下筆記 : 


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

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


Source : 博客來


此書範例程式下載網址 :



Source : 博客來


此書範例程式下載網址 :



Source : 博客來


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


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


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 儲存格之值  

結果如下 : 




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

參考 :


沒有留言:

張貼留言