小狐狸事務所: Python 學習筆記 : 使用 OpenPyXL 操作 EXCEL 試算表 (一)

2021年11月22日星期一

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

前幾天峰大師詢問是否有用 Excel 寫 GUI 輸入表單經驗, 觸發了我對如何以 Python 操作 Excel 試算表的興趣 (記得以前從證交所下載的財報好像也是 Excel 檔案), 所以就把手邊參考書中與此關的章節看了一遍, 隨手做了一些測試並整理筆記如下.

Excel 試算表不僅僅是一個二維資料儲存工具, 也是很常用的統計分析軟體. 在資料處理上經常面對的來源檔案是副檔名為 .xlsx 的 Excel 檔案, 開啟這種檔案需要安裝微軟 Office 2007 以後的版本, 否則無法取得其中的資訊.

不過 Python 有一個開放原始碼的第三方套件 OpenPyXL 可用來直接存取 Excel 試算表, 可讀寫 Office Open XML 格式的 Excel 檔案 (支援 xlsx, xlsm, xltx, 與 xltm 檔), 是目前不須安裝 Office 軟體下唯一能同時讀寫與編輯上述 Excel 檔案的套件, 參考 :

# https://openpyxl.readthedocs.io/en/stable/

當需要對大量 Excel 檔案進行資料處理, 且這些作業都具有規律性時 (例如讀取特定欄位資料進行運算後儲存於新的試算表中或自動繪製圖表等), 使用 OpenPyXL 進行 Excel 自動化處理將可節省巨大的人工操作時間.

本篇測試參考書目 :

Python 自動化的樂趣 (碁峰, 2017) 第 12 章
Python 自動化的樂趣 (第二版) (碁峰, 2020) 第 13 章
Python 入門邁向高手之路王者歸來 (深石, 2017) 第 19 章
Python for Excel｜自動化與資料分析的現代開發環境
超高效！Python × Excel資料分析自動化：輕鬆打造你的完美工作法！
Python 程式設計入門-金融商管實務案例第二版 (博碩 2018, 林萍珍) 第 7 章

第一本書譯自 NoStarch 出版的 "Automate the Boring Stuff with Python", 原文已出第二版.

以下測試會使用到下面兩個測試檔案 :

其中 users.xlsx 是我自建, example.xlsx 則是從 NoStarch.com 網站下載.

1. Excel 試算表結構 :

Excel 試算表是一個由橫列 (row) 與直欄 (column, 或稱行) 組成的二維矩陣結構, 學習 Excel 必須先了解下面幾個術語 :

Application (應用程式) : Excel 軟體本身
Workbook (活頁簿) : 一個活頁簿就是一個 xlsx 檔案
Worksheet (工作表) : 活頁簿中的一張張二維表格
Range (範圍) : 工作表裡面的儲存格區域
Row (列) : 工作表裡面的一個橫列
Column (行) : 工作表裡面的一個直欄
Cell (儲存格) : 工作表裡面的一格

Excel 資料存放於儲存格 (cell) 中. 如下圖所示, 列使用編號為 1 起始的連續整數來定位, 而欄則使用 A, B, C, .... , Z, AA, AB, AC, ... 等名稱來定位 (也可以用 1 起始的連續整數) :

同一個欄位的儲存格資料型態是一樣的, 不同欄位的型態則可以不同, 在統計學上, 各欄位相當於是不同的變數 (variable), 而各列則相當於是不同的觀測值.

一個 xlsx 試算表檔案可包含許多工作表 (worksheet), 使用 Excel 或 OpenPyXL 等建立一個空白試算表檔案時, 裡面預設會建立一個名稱為 "工作表1" 的工作表 (英文版為 Sheet1), 按左下角工作表頁籤右邊的 + 鈕可新增工作表, 點擊各工作表名稱可直接修改名稱, 但要注意, 工作表名稱是獨一無二的, 不可重複 :

另一個測試範例 example.xlsx 有三個工作表 (後兩個是空的) :

除了 Excel 2007 版後可開啟 .xlsx 檔外, 免費的 LibreOffice 與 OpenOffice 也可以.

在 OpenPyXL 套件中, 整個試算表 xlsx 檔案讀取後會被包裝成工作簿 Workbook 物件, 而裡面的工作表則被稱為 Worksheet 工作表物件.

2. 安裝 openpyxl :

使用 openpyxl 之前須用 pip/pip3 安裝套件 :

C:\Users\User>pip install openpyxl

Collecting openpyxl

Downloading openpyxl-3.0.9-py2.py3-none-any.whl (242 kB)

Collecting et-xmlfile

Downloading et_xmlfile-1.1.0-py3-none-any.whl (4.7 kB)

Installing collected packages: et-xmlfile, openpyxl

Successfully installed et-xmlfile-1.1.0 openpyxl-3.0.9

可見 openpyxl 套件並不大, 與相依檔案加起來也才 250KB 左右.

安裝完成後即可用 import 匯入, 可用 as 取一個簡名 :

import openpyxl as xl

例如 :

>>> import openpyxl as xl # 匯入 openpyxl 套件

>>> type(xl) # 型態為 module (因底下還有 module, 故應稱為套件)

>>> dir(xl) # 顯示模組內容

['DEFUSEDXML', 'LXML', 'NUMPY', 'Workbook', '__author__', '__author_email__', '__builtins__', '__cached__', '__doc__', '__file__', '__license__', '__loader__', '__maintainer_email__', '__name__', '__package__', '__path__', '__spec__', '__url__', '__version__', '_constants', 'cell', 'chart', 'chartsheet', 'comments', 'compat', 'constants', 'descriptors', 'drawing', 'formatting', 'formula', 'load_workbook', 'open', 'packaging', 'pivot', 'reader', 'styles', 'utils', 'workbook', 'worksheet', 'writer', 'xml']

>>> for mbr in members: # 走訪 openpyxl 模組成員

obj=eval('xl.' + mbr) # 用 eval() 求值取得 x1.成員之參考

if not mbr.startswith('_'): # 走訪所有不是 "_" 開頭的成員

print(mbr, type(obj))

DEFUSEDXML <class 'bool'>

LXML <class 'bool'>

NUMPY <class 'bool'>

Workbook <class 'type'>

cell <class 'module'>

chart <class 'module'>

chartsheet <class 'module'>

comments <class 'module'>

compat <class 'module'>

constants <class 'module'>

descriptors <class 'module'>

drawing <class 'module'>

formatting <class 'module'>

formula <class 'module'>

load_workbook <class 'function'>

open <class 'function'>

packaging <class 'module'>

pivot <class 'module'>

reader <class 'module'>

styles <class 'module'>

utils <class 'module'>

workbook <class 'module'>

worksheet <class 'module'>

writer <class 'module'>

xml <class 'module'>

其中高亮者為常用的成員, 用途摘要如下 :

常用模組與函式	說明
load_workbook(file)	載入 Excel 檔案 (xlsx 等), 傳回 Workbook 物件
drawing	圖片處理或繪圖模組 (drawing.image.Image)
styles	格式設定模組 (Font, Border, Side, Alignment, PatternFill 等)
formatting	格式化條件設定模組 (rule.CellsRule)

其中最常用的是 load_workbook() 函式, 用來載入 Excel 檔案. 若要在儲存格中插入圖檔, 則需要 drawing,image.Image 類別; 若需要設定儲存格字型, 邊框, 對齊, 以及填滿顏色等格式則需要用到 Styles 模組下的 Font, Border, Alignment 等類別. 另外 formatting 模組底下的 rule.CellsRule 類別可用來設定格式化的條件.

3. 載入現存的 Excel 檔案 :

呼叫 openpyxl 套件中的 load_workbook() 函式並傳入 xlsx 檔案名稱即可載入試算表, 它會傳回一個 Workbook (工作簿) 物件, 此物件提供了存取與操作試算表內容的方法 :

wb=openpyxl.load_workbook(檔案名稱 [, data_only=False])

必要參數為 Excel 檔案名稱, 備選參數 data_only 用來設定所有儲存格是否只取值, 因為有些儲存格是用來放函數的, 預設為 False 表示載入原始的儲存格內容 (若為函數就載入函數), 若設為 True 則表示儲存函數的所有儲存格都必須進行運算, 並將運算後的值存入儲存格內.

例如 :

>>> import openpyxl as xl

>>> wb=xl.load_workbook('users.xlsx') # 載入 Excel 試算表

>>> type(wb) # 傳回 Workbook 物件

>>> members=dir(wb) # 檢視 Workbook 物件之成員

>>> for mbr in members: # 走訪 Workbook 物件成員

obj=eval('wb.' + mbr) # 用 eval() 求值取得 wb.成員之參考

if not mbr.startswith('_'): # 走訪所有不是 "_" 開頭的成員

print(mbr, type(obj))

active <class 'openpyxl.worksheet.worksheet.Worksheet'>

add_named_range <class 'method'>

add_named_style <class 'method'>

calculation <class 'openpyxl.workbook.properties.CalcProperties'>

chartsheets <class 'list'>

close <class 'method'>

code_name <class 'NoneType'>

copy_worksheet <class 'method'>

create_chartsheet <class 'method'>

create_named_range <class 'method'>

create_sheet <class 'method'>

data_only <class 'bool'>

defined_names <class 'openpyxl.workbook.defined_name.DefinedNameList'>

encoding <class 'str'>

epoch <class 'datetime.datetime'>

excel_base_date <class 'datetime.datetime'>

~~get_index~~ <class 'method'>

get_named_range <class 'method'>

get_named_ranges <class 'method'>

~~get_sheet_by_name~~ <class 'method'>

~~get_sheet_names~~ <class 'method'>

index <class 'method'>

is_template <class 'bool'>

iso_dates <class 'bool'>

loaded_theme <class 'NoneType'>

mime_type <class 'str'>

move_sheet <class 'method'>

named_styles <class 'list'>

path <class 'str'>

properties <class 'openpyxl.packaging.core.DocumentProperties'>

read_only <class 'bool'>

rels <class 'openpyxl.packaging.relationship.RelationshipList'>

remove <class 'method'>

remove_named_range <class 'method'>

~~remove_sheet~~ <class 'method'>

save <class 'method'>

security <class 'NoneType'>

shared_strings <class 'openpyxl.utils.indexed_list.IndexedList'>

sheetnames <class 'list'>

style_names <class 'list'>

template <class 'bool'>

vba_archive <class 'NoneType'>

views <class 'list'>

worksheets <class 'list'>

write_only <class 'bool'>

注意, 這些成員中有些已經被廢棄無法使用 (deprecated), 例如 get_sheet_by_name(), get_active_sheet(), get_sheet_by_name() 等方法雖然還列在成員中, 但已經廢棄不用, 呼叫這些函式會出現錯誤訊息.

常用的 Workbook 物件屬性 (即類型為 str, list, bool 等不是 method 者) 如下表 :

常用的 Workbook 物件屬性	說明
active	傳回目前作用中的 Worksheet 物件
sheetnames	傳回所有工作表名稱字串串列
worksheets	傳回目前的工作表 Worlsheet 物件串列
data_only	儲存格是否只儲存值 (即將所有函式求值) : True/False (預設)
read_only	是否設為唯讀 : True/False (預設)
write_only	是否設為唯寫 (無法讀取) : True/False (預設)

常用的 Workbook 物件方法 (類型為 method) 如下表 :

常用的 Workbook 物件方法	說明
create_sheet(sheet_name)	建立名稱為 sheet_name 的工作表
remove(sheet)	刪除工作表物件 sheet
save(file)	將內容儲存到 .xlsx 檔案 file
index(sheet)	傳回指定 Worksheet 物件 sheet 之索引
copy_worksheet(sheet_obj)	複製工作表物件 sheet_obj 為新工作表 (名稱後面加 ' Copy')
close()	關閉活頁簿檔案 (只有 read-only 與 write-only 模式需要用)

注意, 任何對 Workbook 物件的操作 (工作表或儲存格) 其對象都只是位於記憶體內的物件, 操作完畢後必須呼叫 Workbook 物件的 save() 方法存回 .xlsx 檔案中才會生效, 否則一旦解譯器關閉, 所有的操作結果都會隨 Workbook 物件消失而無效, OpenPyXL 沒有即時同步修改 Excel 檔案內容的功能 (另一個套件 xlwings 有此功能, 但必須安裝 Excel 軟體, Windows 需 Excel 2007, MacOS 需 Excel 2012 以後版本). 其次, 呼叫 save() 前若有用 Excel 軟體開啟即將被儲存的 xlsx 檔案須先將其關閉, 否則會因無寫入權限而出現錯誤.

4. 操作工作表 (新建, 刪除, 儲存) :

用 Python 來操作 Excel 主要分成兩個部分 :

工作表的操作
儲存格的操作

存取 Excel 檔案內的工作表首先須載入 .xlsx 檔案以便在記憶體中建立 Workbook 物件, 再利用此物件的下列三個屬性取得工作表名稱或 Worksheet 物件 :

sheetnames : 傳回全部工作表名稱字串串列
worksheets : 傳回全部工作表物件串列
active : 傳回目前作用中的工作表物件

其中 sheetnames 與 worksheets 屬性都傳回一個串列, 所以可以用中括號 [] 以 0 起始的索引取得其元素, 兩者的差別是 sheetnames 的元素是工作表名稱字串; 而 worksheets 的元素是 Worksheet 物件. 注意, 舊版的 OpenPyXL 使用 get_sheet_names(), get_active_sheet(), 與 get_sheet_by_name() 等方法來取得工作表名稱或物件, 但現在都已經被移除或廢棄了 (deprecated).

建立, 複製, 與刪除工作表使用 Workbook 物件的如下方法 :

create_sheet() : 建立工作表
copy_worksheet() : 複製工作表
remove() : 刪除工作表

注意, 舊版 OpenPyXL 用的 remove_sheet() 方法已被廢棄, 須改用 remove() 方法.

(1). 取得工作表名稱與工作表物件 :

首先開啟下載的範例檔 users.xlsx, 查詢 worksheets 屬性可知裡面只有一個標題為 '工作表1' 的工作表, worksheets 屬性值是串列, 其元素為代表各工作表之 Worksheet 物件, 可用 [] 運算子取得各個工作表物件, 然後用 title 屬性取得工作表名稱. Workbook 物件的 sheetnames 則是傳回工作表名稱的串列, 利用 [] 運算子即可直接取得各工作表名稱, 例如 :

>>> import openpyxl as xl

>>> wb=xl.load_workbook('users.xlsx') # 載入試算表檔案

>>> sheets=wb.sheetnames # 傳回全部工作表名稱串列

>>> sheets

['工作表1']

>>> ws=wb.worksheets # 傳回全部工作表物件 (list)

>>> ws # 顯示串列內容

[<Worksheet "工作表1">]

>>> type(ws) # worksheets 屬性值為串列

>>> ws[0] # 用 [] 運算子取得工作表物件

>>> type(ws[0])

>>> ws[0].title # 利用 Worksheet 物件的 title 屬性取得工作表名稱

'工作表1'

>>> active_sheet=wb.active # 傳回目前作用中的工作表物件

>>> active_sheet

>>> active_sheet.title # 利用 Worksheet 物件的 title 屬性取得工作表名稱

'工作表1'

開啟上面的第二個範例試算表 example.xlsx. 查詢工作簿物件的 sheetnames 屬性可知裡面有 Sheet1, Sheet2, Sheet3 三個工作表 :

>>> wb=xl.load_workbook('example.xlsx') # 載入試算表檔案

>>> sheets=wb.sheetnames # 取得全部工作表名稱串列

>>> sheets

['Sheet1', 'Sheet2', 'Sheet3']

>>> ws=wb.worksheets # 傳回全部工作表物件 (list)

>>> ws

[<Worksheet "Sheet1">, <Worksheet "Sheet2">, <Worksheet "Sheet3">]

>>> ws[0].title # 利用 Worksheet 物件的 title 屬性取得工作表名稱

'Sheet1'

>>> ws[1].title # 利用 Worksheet 物件的 title 屬性取得工作表名稱

'Sheet2'

>>> ws[2].title # 利用 Worksheet 物件的 title 屬性取得工作表名稱

'Sheet3'

>>> active_sheet=wb.active # 取得作用中的工作表

>>> active_sheet # 傳回 Worksheet 物件

>>> active_sheet.title

'工作表1'

除了上面的方法外, 還可以直接透過 Workbook 物件與 [] 運算子以工作表名稱為索引來取得 Worksheet 物件, 例如 :

>>> sheet1=wb['Sheet1'] # 必須以工作表名稱當索引

>>> sheet1

>>> type(sheet1)

>>> sheet1.title

'Sheet1'

注意, 這種方法只能用工作表名稱當索引, 不可用 0 起始的數字索引 :

>>> wb[0] # 不可以用數字索引

Traceback (most recent call last):

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

File "C:\Python37\lib\site-packages\openpyxl\workbook\workbook.py", line 288, in __getitem__

raise KeyError("Worksheet {0} does not exist.".format(key))

KeyError: 'Worksheet 0 does not exist.'

(2). 建立新工作表 :

呼叫 Workbook 工作簿物件的 create_sheet() 方法並傳入工作表名稱即可在工作簿中建立新的工作表, 它會傳回新建立的工作表 Worksheet 物件, 如果沒有傳入名稱字串, 則預設的工作表名稱依序為 'Sheet', 'Sheet1', 'Sheet2', ... 等. 呼叫 remove_sheet() 則可以刪除指定的工作表, 但傳入參數不是工作表名稱, 而必須傳入工作表物件, 例如 :

>>> import openpyxl as xl

>>> wb=xl.load_workbook('users.xlsx') # 載入試算表檔案

>>> wb.create_sheet() # 建立預設名稱為 'Sheet' 的新工作表

>>> wb.worksheets # 取得全部工作表物件串列

[<Worksheet "工作表1">, <Worksheet "Sheet">]

>>> wb.create_sheet() # 建立預設名稱為 'Sheet1' 的新工作表

>>> wb.worksheets # 取得全部工作表物件串列

[<Worksheet "工作表1">, <Worksheet "Sheet">, <Worksheet "Sheet1">]

>>> wb.create_sheet("我的工作表") # 建立指定名稱為 '我的工作表' 的新工作表

>>> wb.worksheets

[<Worksheet "工作表1">, <Worksheet "Sheet">, <Worksheet "Sheet1">, <Worksheet "我的工作表">]

>>> wb.create_sheet() # 建立預設名稱 (自動遞增) 的新工作表

>>> wb.worksheets # 取得全部工作表物件串列

[<Worksheet "工作表1">, <Worksheet "Sheet">, <Worksheet "Sheet1">, <Worksheet "我的工作表">, <Worksheet "Sheet2">]

>>> wb.active # 建立新工作表不會改變作用中的工作表

可見呼叫 create_sheet() 時傳入工作表名稱字串會建立指定名稱的新工作表, 此例呼叫了四次 create_sheet(), 依序建立了名稱為 "Sheet", "Sheet1", "我的工作表", "Sheet2", ... 等新工作表. 新增工作表不會改變作用中的工作表設定, 仍然是預設的 "工作表1".

以上新建的工作表都是針對儲存於記憶體中的 Workbook 物件, 並不會同步更新 xlsx 檔案 (OpenPyXL 無即時更新更能), 必須呼叫 Workbook 物件的 save() 方法將結果回存到檔案才行, 例如 :

>>> wb.save('users.xlsx') # 將 Workbook 物件上的變更回存到檔案

此處指定原檔名故會覆蓋原檔案, 當然也可以指定儲存到不同的檔案, 回存後用 Excel 開啟 users.xlsx 檔, 可見確實已增加了四個空的工作表 :

注意, 執行 wb.save() 前若已用 Excel 開啟該 xlsx 檔, 務必先關閉後再呼叫 save(), 否則會因為檔案已被鎖住無寫入權限而出現錯誤訊息.

(3). 複製工作表 :

呼叫 Workbook 物件的 copy_worksheet() 方法可複製現有工作表, 但要注意, 其傳入參數為工作表物件而非工作表名稱, 例如承上面範例 :

>>> wb=xl.load_workbook('users.xlsx')

>>> wb.sheetnames

['工作表1', 'Sheet', 'Sheet1', '我的工作表', 'Sheet2']

>>> sheet1=wb['Sheet1'] # 以工作表名稱當索引取得工作表物件

>>> sheet1

>>> wb.copy_worksheet(sheet1) # 傳入 Wrksheet 物件複製工作表

>>> wb.save('users.xlsx') # 將工作簿物件存檔

用 Excel 開啟 users.xlsx 可知在最右邊多了一個名稱為 "Sheet1 Copy" 的工作表 :

可見來源工作表 "Sheet1" 的內容已被複製到新工作表 "Sheet1 Copy" 了.

(4). 刪除工作表 :

呼叫工作簿 Workbook 物件的 remove() 方法並傳入欲刪除之工作表物件即可刪除該指定之工作表, 如果沒有傳入參數, 則預設會刪除最後 (最右邊) 的工作表. 注意, 舊版的 remove_sheet() 方法已被廢棄, 不要再用. 例如 :

>>> wb.worksheets # 取得目前 users.xlsx 工作簿中的全部工作表

[<Worksheet "工作表1">, <Worksheet "Sheet">, <Worksheet "Sheet1">, <Worksheet "我的工作表">, <Worksheet "Sheet2">]

>>> wb.worksheets[1] # 索引 1 的工作表物件

>>> wb.remove(wb.worksheets[1]) # 刪除索引 1 的工作表物件 Sheet

>>> wb.worksheets

[<Worksheet "工作表1">, <Worksheet "Sheet1">, <Worksheet "我的工作表">, <Worksheet "Sheet2">]

>>> wb.worksheets[1] # 索引 1 的工作表物件

>>> wb.remove(wb.worksheets[1]) # 刪除索引 1 的工作表物件 Sheet1

>>> wb.worksheets

[<Worksheet "工作表1">, <Worksheet "我的工作表">,<Worksheet "Sheet2">]

>>> wb.save('users.xlsx') # 將 Workbook 物件上的變更回存到檔案

此例連續刪除 Workbook 物件中的兩個索引為 1 的工作表, 由於工作表被刪除後, 其索引會由後面 (右邊) 那個遞補, 因此連續兩次刪除索引 1 的工作表會將 'Sheet' 與 'Sheet1' 兩個工作表刪除, 最後呼叫 save() 方法存回原檔案. 開啟 users.xlsx 結果如下 :

5. 建立空白工作簿 :

以上的測試都是以 load_workbook() 函式載入現有的 .xlsx 試算表後操作工作表, 但其實 OpenPyXL 套件的 Workbook() 函式可直接建立空白的試算表檔案, 語法如下 :

import openpyxl as xl

wb=xl.Workbook()

Workbook() 函式會傳回一個 Workbook 物件, 裡面預設會自動建立一個名為 'Sheet' 的工作表, 接著就可以用上述方式操作工作表, 完畢後呼叫 save() 即可將物件存成 xlsx 檔案, 例如 :

>>> import openpyxl as xl

>>> wb=xl.Workbook()

>>> type(wb)

<class 'openpyxl.workbook.workbook.Workbook'> # 工作簿物件

>>> wb.worksheets # 傳回工作表物件串列

[<Worksheet "Sheet">]

>>> wb.create_sheet('新工作表') # 建立新工作表

>>> wb.worksheets

[<Worksheet "Sheet">, <Worksheet "新工作表">]

>>> wb.save('new_workbook.xlsx') # 存成 xlsx 檔案

此例呼叫 Workbook() 函式建立了一個工作簿, 利用 worksheets 屬性可知裡面已有一個預設的空白工作表 'Sheet', 然後呼叫 create_sheet() 方法建立新的工作表, 最後呼叫 save() 方法將工作簿物件存為一個 new_workbook.xlsx 試算表檔案, 開啟此 new_workbook.xlsx 結果如下 :