我在 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 作為使用者介面來打造一個互動式應用.
參考書籍 :
- 超簡單! Python 與 Excel 整合應用 150 技 (碁峰 2022)
- Python for Excel|自動化與資料分析的現代開發環境 (歐萊禮 2021) 第 9 章
- Python x Excel VBA x Javascript 網路爬蟲實戰演練 (碁峰 2021) 第 3 章
最近從母校圖書館借到這三本好書, 所以要開始來學習 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 儲存格之值
結果如下 :
可見原來的四個儲存格內容都被改變了.
參考 :
沒有留言 :
張貼留言