2021年11月22日 星期一

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

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

Excel 試算表不僅僅是一個二維資料儲存工具, 也是很常用的統計分析軟體. 在資料處理上經常面對的來源檔案是副檔名為 .xlsx 的 Excel 檔案, 開啟這種檔案需要安裝微軟 Office 2007 以後的版本, 否則無法取得其中的資訊. 不過 Python 有一個開放原始碼的第三方套件 OpenPyXL 可用來直接存取 Excel 試算表, 是目前不須安裝 Office 軟體下唯一能讀寫 .xlsx 檔的套件, 參考 :


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

本篇測試參考書目 : 
  1. Python 自動化的樂趣 (碁峰, 2017) 第 12 章
  2. Python 自動化的樂趣 (第二版) (碁峰, 2020) 第 13 章
  3. Python 入門邁向高手之路王者歸來 (深石, 2017) 第 19 章
第一本書譯自 NoStarch  出版的 "Automate the Boring Stuff with Python", 原文已出第二版. 以下測試會使用到下面兩個測試檔案 : 
其中 users.xlsx 是我自建, example.xlsx 則是從 NoStarch.com 網站下載. 


1. Excel 試算表結構 :  

Excel 試算表是一個由橫列 (row) 與直欄 (column, 或稱行) 組成的二維矩陣結構, 資料存放於儲存格 (cell) 中. 如下圖所示, 列使用編號為 1 起始的連續整數來定位, 而欄則使用 A, B, C, .... , Z, AA, AB, AC, ... 等名稱來定位 (也可以用 1 起始的連續整數) :




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

一個 xlsx 試算表檔案可包含許多工作表 (worksheet), 預設只有一個名稱為 "工作表1" 的工作表, 按左下角的 + 鈕可新增工作表, 點擊各工作表名稱可直接修改 : 




另一個測試範例 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, 故應稱為套件)
<class '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() 函式, 用來載入 Excel 檔案. 


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 物件
<class 'openpyxl.workbook.workbook.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), 常用的 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 之索引




不過其中有些函式例如 get_sheet_by_name(), get_active_sheet(), get_sheet_by_name() 等雖然還列在成員中, 但已經廢棄不用, 呼叫這些函式會出現


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

存取 Excel 檔案內的工作表首先須載入 .xlsx 檔案以便在記憶體中建立 Workbook 物件, 再利用此物件的下列三個屬性取得工作表物件或名稱 : 
  • sheetnames : 傳回全部工作表名稱字串串列
  • worksheets : 傳回全部工作表物件串列
  • active : 傳回目前作用中的工作表物件
舊版的 OpenPyXL 使用 get_sheet_names(), get_active_sheet(), 與 get_sheet_by_name()  等方法來取得工作表名稱或物件, 但現在都已經被移除或廢棄了. 

屬性 worksheets 會傳回工作表物件串列, 故只要用 [] 運算子以 0 起始的索引就可以取得各個 Worksheet 物件. 

建立與刪除工作表使用 Workbook 物件的如下方法 : 
  • create_sheet() : 建立工作表
  • remove() : 刪除工作表
注意, remove_sheet() 方法在新版 OpenPyXL 中已被廢棄, 改用 remove() 方法. 

任何對 Workbook 物件的操作都只是在記憶體內, 操作完畢後必須呼叫 Workbook 物件的 save() 方法存回 .xlsx 檔案中才會生效, 否則一旦解譯器關閉所有的操作都會隨 Workbook 物件消失而無效. 


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

首先開啟下載的範例檔 users.xlsx, 查詢 worksheets 屬性可知裡面只有一個標題為 '工作表1' 的工作表, worksheets 屬性值是串列, 其元素為各工作表之 Workseet 物件, 可用 [] 運算子取得各個工作表物件, 可用工作表物件之 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 屬性值為串列          
<class 'list'> 
>>> ws[0]                                             # 用 [] 運算子取得工作表物件
<Worksheet "工作表1">  
>>> type(ws[0])   
<class 'openpyxl.worksheet.worksheet.Worksheet'>     
>>> ws[0].title                                     # 利用 Worksheet 物件的 title 屬性取得工作表名稱
'工作表1'
>>> active_sheet=wb.active               # 傳回目前作用中的工作表物件
>>> active_sheet    
<Worksheet "工作表1">
>>> 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 物件
<Worksheet "工作表1">   
>>> active_sheet.title    
'工作表1'

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


(2). 建立新工作表 :  

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

>>> import openpyxl as xl    
>>> wb=xl.load_workbook('users.xlsx')     # 載入試算表檔案
>>> wb.create_sheet()                                   # 建立預設名稱為 'Sheet' 的新工作表
<Worksheet "Sheet">     
>>> wb.worksheets                                        # 取得全部工作表物件串列
[<Worksheet "工作表1">, <Worksheet "Sheet">]
>>> wb.create_sheet()                                   # 建立預設名稱為 'Sheet1' 的新工作表  
<Worksheet "Sheet1">    
>>> wb.worksheets                                        # 取得全部工作表物件串列
[<Worksheet "工作表1">, <Worksheet "Sheet">, <Worksheet "Sheet1">]
>>> wb.create_sheet("我的工作表")          # 建立指定名稱為 '我的工作表' 的新工作表
<Worksheet "我的工作表">    
>>> wb.worksheets   
[<Worksheet "工作表1">, <Worksheet "Sheet">, <Worksheet "Sheet1">, <Worksheet "我的工作表">]
>>> wb.create_sheet()                                    # 建立預設名稱為 'Sheet2' 的新工作表
<Worksheet "Sheet2">   
>>> wb.worksheets                                        # 取得全部工作表物件串列
[<Worksheet "工作表1">, <Worksheet "Sheet">, <Worksheet "Sheet1">, <Worksheet "我的工作表">, <Worksheet "Sheet2">]
>>> wb.active                                # 建立新工作表不會改變作用中的工作表
<Worksheet "工作表1">     

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

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

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

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





(3). 刪除工作表 :  

呼叫工作簿 Workbook 物件的 remove_sheet() 並傳入現有之工作表物件可以刪除該指定之工作表, 否則預設會刪除最後 (右) 的工作表, 例如 (承接上面在 users.xlsx 中現有的資料表) : 

>>> wb.worksheets              # 取得目前 users.xlsx 工作簿中的全部工作表 
[<Worksheet "工作表1">, <Worksheet "Sheet">, <Worksheet "Sheet1">, <Worksheet "我的工作表">, <Worksheet "Sheet2">]
>>> wb.worksheets[1]          # 索引 1 的工作表物件
<Worksheet "Sheet">  
>>> wb.remove(wb.worksheets[1])      # 刪除索引 1 的工作表物件 Sheet 
>>> wb.worksheets    
[<Worksheet "工作表1">, <Worksheet "Sheet1">, <Worksheet "我的工作表">, <Worksheet "Sheet2">]
>>> wb.worksheets[1]          # 索引 1 的工作表物件
<Worksheet "Sheet1">  
>>> 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' 的工作表, 然後即可用上述方式操作工作表, 例如 : 

>>> import openpyxl as xl   
>>> wb=xl.Workbook()    
>>> type(wb)    
<class 'openpyxl.workbook.workbook.Workbook'>     # 工作簿物件 
>>> wb.worksheets                                       # 傳回工作表物件串列
[<Worksheet "Sheet">]   
>>> wb.create_sheet('新工作表')                # 建立新工作表
<Worksheet "新工作表">    
>>> wb.worksheets         
[<Worksheet "Sheet">, <Worksheet "新工作表">]    
>>> wb.save('new_workbook.xlsx')            # 存成 xlsx 檔案

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





參考 : 


沒有留言 :