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

在前兩篇 OpenPyXL 測試文章中已學會它主要的資料處理功能, 本篇要測試的是它較次要的儲存格的格式設定功能, 主要是應用在大量 xlsx 檔案的自動化處理. 本系列之前的文章參考 :

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

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

官網教學資料參考 :

# openpyxl.worksheet.worksheet module

# https://openpyxl.readthedocs.io/en/latest/tutorial.html

在此之前先摘要整理前一篇關於工作表操作之指令 :

• 取得工作表名稱 :
  sheet.title
• 取得工作表內容之列數與欄數 :
  sheet.max_row
  sheet.max_column
• 取得儲存格物件及屬性值 :
  cell=sheet.cell(row=2, column=3)        # 呼叫 cell() 方法
  cell=shee['C2']        # 使用 [] 運算子
  cell.row                   # 儲存格列索引
  cell.column             # 儲存格欄索引
  cell.value                # 儲存格內容
  cell.data_type         # 儲存格內容
• 取得儲存格內容 :
  sheet.cell(row=2, column=3).value
  sheet['C2'].value
• 將資料寫入儲存格 :
  sheet.cell(row=2, column=3).vaule=123
  sheet.cell(row=2, column=3).value='ok'
  sheet.cell(row=2, column=3).value='=SUM(B2:B5)'
  sheet.cell(row=2, column=3, value=123)
  sheet.cell(row=2, column=3, value='ok')
  sheet.cell(row=2, column=3, value='=SUM(B2:B5)')
  sheet['C2'].value=123
• 取得列儲存格物件 tuple :
  cells=sheet[5]     # 取得第 5 列所有儲存格 tuple
• 取得欄儲存格物件 tuple :
  cells=sheet['B'] 
• 取得指定範圍內的儲存格物件 tuple : 
  cells=sheet['B2':'E5']     # 取得 B2~E5 的全部儲存格物件
  cells=sheet['B2:E5']      # 取得 B2~E5 的全部儲存格物件
• 取得工作表全部儲存格的產生器 : 
  sheet.rows         # 逐列, 可傳給 list() 或 tuple() 產生全部儲存格
  sheet.columns   # 逐欄, 可傳給 list() 或 tuple() 產生全部儲存格
• 取得指定範圍內儲存格的產生器 :
  sheet.iter_rows(min_row, max_row, min_col, max_col)   # 逐列
  sheet.iter_cols(min_row, max_row, min_col, max_col)    # 逐欄
• 插入空白列 :
  sheet.insert_rows(3)               # 在第 3 列前面 (上方) 插入 1 個空列
  sheet.insert_rows(3, 2)           # 在第 3 列前面 (上方) 插入 2 個空列
• 插入空白欄 : 
  sheet.insert_cols(3)                # 在第 3 欄前面 (左方) 插入 1 個空白欄
  sheet.insert_cols(3, 2)            # 在第 3 欄前面 (左方) 插入 2 個空白欄
• 刪除指定列或連續列 :
  sheet.delete_rows(3)              # 刪除第 3 列
  sheet.delete_rows(3, 2)          # 從列索引 3 開始往下連續刪除 2 列
• 刪除指定欄或連續欄 :
  sheet.delete_cols(3)               # 刪除第 3 欄
  sheet.delete_cols(3, 2)           # 從欄索引 3 開始往右連續刪除 2 欄
• 添加一列資料於工作表尾端 :
  sheet.append(['元小稹', 88, 34, 100, '=SUM(B5:D5)'])   # 串列
  sheet.append({'A':'岑小參', 'B':90, 'C':45, 'D':100, 'E':'=SUM(B6:D6)'})  # 字典
• 移動一個範圍之儲存格 : 
  sheet.move_range('B2:E6', rows=3, cols=2)        # 向下 3 列向右 2 欄
  sheet.move_range('B2:E6', rows=-3, cols=-2)     # 向上 3 列向左 2 欄
• 合併一個範圍內之儲存格 :
  sheet.merge_cells('A1:E1')        # 將 'A1:E1' 合併為一格
• 將被合併的儲存格取消合併 :
  sheet.unmerge_cells('A1:E1')    # 將被合併的 'A1:E1' 範圍內儲存格取消合併
• 於儲存格中插入圖片 :
  from openpyxl.drawing.image import Image
  sheet.add_image(Image('say_hello.jpg'), 'B9')
• 凍結儲存格 :
  sheet.freeze_panes='A2'    # 凍結 A2 以左以上之儲存格 (即第一列)

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

1. scores2.xlsx

下載後放在與程式相同之工作目錄下即可. 

7. 儲存格的格式設定 :  

儲存格的格式包含下列項目 : 

• 字型樣式 (font)
• 寬度與高度 (width & height)
• 對齊方式 (alignment)
• 框線樣式 (border)
• 格式化條件 (formatting rules)

其中格式化條件用來指定一個條件, 當儲存格內容符合該條件時就改變格式, 例如當股價低於某值時改變儲存格背景色等等. 

(1). 字型樣式設定 :  

使用 Cell 物件的 font 屬性可以設定儲存格的字型, 其值為 openpyxl.styles.Font 物件, 因此使用前必須先匯入 Font 類別 : 

from openpyxl.styles import Font

呼叫 Font 類別建構子 Font() 並傳入如下字型樣式屬性參數即可建立 Font 物件 :

Font() 參數	說明
name	字型名稱字串, 例如 'Arial', 'Calibri' (預設), '微軟正黑體' 等
size	字型大小 (整數, 預設=11 pt)
color	字型色彩字串 (前景色), 預設 '000000' (黑色)
bold	是否粗體, True/False (預設 False)
italic	是否斜體, True/False (預設 False)
strike	是否有刪除線, True/False (預設 False)

字型樣式的預設值參考 :

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

例如 : 

>>> import openpyxl as xl     

>>> from openpyxl.styles import Font     

>>> wb=xl.Workbook()           # 建立一個空白工作簿

>>> wb.sheetnames                  # 檢視所有工作表名稱

['Sheet']

>>> sheet=wb['Sheet']             # 取得工作表物件

>>> sheet       

<Worksheet "Sheet">

>>> sheet['A1'].value='Hello World!'             # 寫入 A1 儲存格

>>> sheet['A2'].value='Hello Tony!'               # 寫入 A2 儲存格

>>> sheet['A3'].value='你是在說哈囉嗎?'     # 寫入 A3 儲存格

>>> fontA2=Font(name='Arial', size=14, bold=True, color='0000ff')      

>>> fontA3=Font(name='微軟正黑體', size=12, italic=True, strike=True)       

>>> sheet['A2'].font=fontA2              # 訂定 A2 儲存格字型樣式

>>> sheet['A3'].font=fontA3              # 訂定 A3 儲存格字型樣式

>>> wb.save('styles.xlsx')                   # 工作簿存檔

此例先在 A1, A2, A3 儲存格寫入資料, 然後利用 Font() 建立 A2, A3 儲存格之 Font 物件, 並將它們分別指派給 A2, A3 儲存格物件的 font 屬性 (A1 保持預設樣式), 最後呼叫 wb.save() 將工作簿存成 xlsx 檔. 用 Excel 開啟此檔檢視工作表內容 : 

可見 A1 儲存格為預設字型樣式 (字型 Calibri, 大小 11, 黑色); A2 儲存格字型為 Arial, 大小為 16, 粗體藍色; A3 儲存格字型為微軟正黑體, 大小 12, 斜體有刪除線. 

(2). 列高與欄寬設定 :  

Excel 儲存格的大小是整列或整欄一致的, 所以設定儲存格的大小就是調整列高與欄寬, 這必須透過工作表物件的 row_dimensions 與 column_dimensions 屬性來設定, 其值為 DimensionHolder 物件, 以上面範例中的工作表物件 Sheet 為例 : 

>>> type(sheet.row_dimensions)    

<class 'openpyxl.worksheet.dimensions.DimensionHolder'>

>>> type(sheet.column_dimensions)       

<class 'openpyxl.worksheet.dimensions.DimensionHolder'>

DimensionHolder 物件是列尺寸物件 RowDimensios 與欄尺寸物件 ColumnDimension 的容器, 可用 [] 運算子以列索引 (例如 1, 2, 3,...) 或欄索引 (例如 'A', 'B', 'C', ...) 取得尺寸物件, 例如 :

>>> type(sheet.row_dimensions[1])             # 以整數索引取得列尺寸物件

<class 'openpyxl.worksheet.dimensions.RowDimension'>

>>> type(sheet.column_dimensions['A'])    # 以字串索引取得欄尺寸物件

<class 'openpyxl.worksheet.dimensions.ColumnDimension'>

利用 RowDimension 物件的 height 屬性 (單位是 pt) 即可設定列高 (範圍 0~409 pt); 而 ColumnDimension 物件的 width 屬性 (單位是 pt) 即可設定欄寬 (範圍 0~255 pt), 例如 : 

>>> print(sheet.column_dimensions['A'].width)       

13.0

>>> print(sheet.row_dimensions[1].height)       

None

可見儲存格的欄寬預設是 13pt (72pt 為 1 英吋), 但列高無預設值 (視內容高度而定), 參考 : 

# https://openpyxl.readthedocs.io/en/stable/api/openpyxl.worksheet.dimensions.html 

用上面的工作表為例修改 A 欄的欄寬與第 2 列的列高如下 :

>>> sheet.row_dimensions[2].height=40             # 設定第 2 列列高=40pt

>>> sheet.row_dimensions[3].height=50             # 設定第 3 列列高=50pt

>>> sheet.column_dimensions['A'].width=60     # 設定第 1 欄欄寬=60pt

>>> wb.save('styles.xlsx')                                      # 工作簿存檔

用 Excel 開啟結果如下 :  

可見第 1 欄欄寬變寬了, 第 2, 3 列的列高也變大了. 

(3). 儲存格內容對齊設定 :  

儲存格的內容預設是水平靠左對齊, 垂直置中對齊, 但可以利用儲存格物件的 alignment 屬性來設定, 此屬性之值為一個 openpyxl.styles.Alignment 物件, 故須先匯入 Alignment 類別 : 

from openpyxl.styles import Alignment

然後呼叫其建構子 Alignment() 並傳入 vertical 或 horizontal 屬性來建立 Alignment 物件 :

Alignment(vertical=None, horizontal=None)     

其中 vertical 值為字串, 可傳入之值如下 :

• 'top'
• 'center'
• 'bottom'

horizontal 值也是字串, 可傳入之值如下 :

• 'left'
• 'center'
• 'right'

以上面範例中的工作表物件 Sheet 為例 : 

>>> sheet['A2'].alignment        

<openpyxl.styles.alignment.Alignment object>      # Alignment 物件

Parameters:

horizontal=None, vertical=None, textRotation=0, wrapText=None, shrinkToFit=None, indent=0.0, relativeIndent=0.0, justifyLastLine=None, readingOrder=0.0

>>> sheet['A2'].alignment=Alignment(vertical='top', horizontal='center')   

>>> sheet['A3'].alignment=Alignment(vertical='center', horizontal='right')   

>>> wb.save('styles.xlsx')         

此處將上面範例中的 A2 儲存格內容設為垂直靠上對齊, 水平置中對齊; 而 A3 儲存格設定為垂直置中對齊, 水平靠右對齊, 存檔後用 Excel 開啟檔案結果如下 : 

可見 A2 與 A3 儲存格的對齊方式改變了. 

(4). 儲存格邊框設定 :  

Excel 儲存格預設是 1px 的實線, 可以利用儲存格物件的 border 屬性來設定, 其值為一個  openpyxl.styles.Border 物件, 故使用前須匯入此類別來建立物件 :

from openpyxl.styles import Border 

呼叫建構子 Border() 並傳入 left, right, top, bottom 參數即可建立 Border 物件, 語法如下 :

Border(left, right, top, bottom)     

不過儲存格的邊界樣式實際上是透過傳入的四個參數來設定的, 其值為一個 Side 物件, 故還需要從 openpyxl.styles 匯入 Side 類別 :

from openpyxl.styles import Side

呼叫建構子 Side() 並傳入 color 與 style 參數即可建立 Side 物件, 語法如下 :

Side(color, style [, border_style])     

其中 color 為一個 openpyxl.styles.Color 物件, 但也可以直接顏色字串例如 "FF00FF" (與上面 Font 前景色的方式相同, 前面不需 #). 參數 style 為邊界樣式, 可用選項如下 :

• 'hair'
• 'thin'
• 'medium'
• 'double'
• 'thick'
• 'dotted'
• 'dashed'
• 'mediumDashed'
• 'dashdot'
• 'dashDotDot'
• 'slantDashDot'
• 'mediumDashDot'
• 'mediumDashDotDot'

參考 :

# https://openpyxl.readthedocs.io/en/stable/api/openpyxl.styles.borders.html?highlight=side#openpyxl.styles.borders.Side

以下以 scores2.xlsx 為例, 將超過 90 分的儲存格 B2 套上藍色框線, 將不及格的 D2 儲存格套上紅色框線, 所以要建立兩個 Border 物件, 例如 : 

>>> from openpyxl.styles import Border   

>>> from openpyxl.styles import Side   

>>> side1=Side(color='FF0000', style='thick')     # 紅色實線

>>> type(side1)    

<class 'openpyxl.styles.borders.Side'>   

>>> side2=Side(color='0000FF', style='mediumDotDot')    # 藍色虛線

>>> type(side2)   

<class 'openpyxl.styles.borders.Side'>  

>>> border1=Border(left=side1, right=side1, top=side1, bottom=side1)   

>>> border2=Border(left=side2, right=side2, top=side2, bottom=side2)    

>>> type(border1)     

<class 'openpyxl.styles.borders.Border'>  

>>> type(border2)   

<class 'openpyxl.styles.borders.Border'>   

此處先建立兩個 Side 物件, 分別給後面建立的兩個 Border 物件使用, 其中 border1 是紅框. 接下來只要將儲存格物件的 border 屬性指定為 Border 物件即可更改齊框線樣式了,  :

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

>>> sheet1=wb['工作表1']       

>>> sheet1['B3'].border=border2        # 藍色虛線 (超過 90 分)

>>> sheet1['D3'].border=border1        # 紅色實線 (不及格)

>>> wb.save('scores2.xlsx')      

開啟 scores2.xlsx 結果如下 :

可見已將不及格的分數 D3 套上紅色實框線, 超過 90 分的 B3 套上藍色虛框線了. 

如果要取消框線設定回復預設值, 只要呼叫 Side() 時傳入參數 border_style=None 建立一個 Side 物件, 並以此物件建立 Border 物件來設定儲存格物件的 border 屬性即可, 例如 :  

>>> side3=Side(border_style=None)    

>>> border3=Border(left=side3, right=side3, top=side3, bottom=side3)   

>>> sheet1['D2'].border=border3  

>>> sheet1['B2'].border=border3    

>>> wb.save('scores2.xlsx')       

以 Excel 開啟 scroes.xlsx 結果如下 : 

可見原本套用在儲存格 B3, D3 的框線樣式都被移除而恢復預設值了. 

(5). 儲存格背景色設定 :  

除了利用邊框樣式來凸顯特定儲存格外, 還可以利用填滿儲存格背景色, 這需要用到 Cell 物件的 fill 屬性, 其值為一個 PatternFill 物件, 故使用前需先從 openpyxl.styles 匯入 PatternFill 類別 :

from openpyxl.styles import PatternFill

呼叫 PatternFill 類別的建構子 PaternFill() 並傳入 bgColor 參數 (Color 物件或色碼字串例如 'FF0000') 即可建立一個 PatternFill 填色物件, 然後將此 PatternFill 物件指配給儲存格物件的 fill 屬性即可, 語法如下 :

PatternFill(start_color, fill_type[, end_color])    

參數 fill_type 為填色類型, 可用選項如下 :

• solid
• gray0625
• gray125
• lightGray
• mediumGray
• darkGray
• lightGrid
• darkGrid
• lightHorizontal
• darkHorizontal
• lightVertical
• darkVertical
• lightUp
• darkUp
• lightDown
• darkDown
• lightTrellis
• darkTrellis

單純填色使用 solid 即可. 參考 :

# openpyxl.styles.fills module

# Adding a background color to Cell OpenPyXL

以上面的 scores2.xlsx 成績單工作表為例, 若要將超過 90 分的儲存格 B3 套上黃色背景色, 以及將不及格的 D3 儲存格套上紅色背景色可以這麼做 :

>>> from openpyxl.styles import PatternFill    

>>> fill_B3=PatternFill(start_color='FFFF00', fill_type='solid')   # 黃色

>>> fill_D3=PatternFill(start_color='FF0000', fill_type='solid')    # 紅色

>>> sheet1['D3'].fill=fill_D3      

>>> sheet1['B3'].fill=fill_B3     

>>> wb.save('scores2.xlsx')       

用 Excel 開啟 scores2.xlsx 結果如下 : 

可見 B3 與 D3 儲存格都分別套上黃色與紅色底色了. 

(6). 格式化條件 :  

格式化條件是依據儲存格的內容來自動調整其格式, 例如股價收盤價與昨日相比若上漲標示為紅色底色; 下跌則標示為綠色底色, 完全依據今收價與昨收價儲存格之內容自動套用底色, 毋須人工指定. 工作表物件有一個 conditional_formatting 屬性, 其值為一個 ConditionalFormattingList 類別, 例如 :

>>> type(sheet1.conditional_formatting)      

<class 'openpyxl.formatting.formatting.ConditionalFormattingList'> 

ConditionalFormattingList 類別有一個靜態方法 add(), 可以不須建立物件直接呼叫並傳入要套用的儲存格區域與格式化條件物件, 這樣即可達成儲存格的格式化條件設定, 語法如下 : 

工作表物件.conditional_formatting.add(range, rule)    

第一參數 range 為要套用格式化條件的儲存格範圍, 第二參數 rule 為一個 Rule 物件, 用來設定執行儲存格格式化之條件與動作, 此物件可呼叫 openpyxl.formatting.rule 的 CellIsRule() 函式來建立, 故使用前須先匯入此函式 :

from openpyxl.formatting.rule import CellIsRule    

呼叫 CellIsRule() 並傳入參數即可建立一個 Rule 物件, 語法如下 :

CellIsRule(operator=None, 

                   formula=None, 

                   stopIfTrue=None, 

                   font=None, 

                   border=None, 

                   fill=None) 

其中 font (字型), border (框線), 以及 fill (填滿背景色) 是可套用的格式. 而所謂的條件則是由 operator 與 formula 這兩個參數來設定, operator 是運算子, 其值為字串, 可用的選項如下 :

operator 參數值	說明
equal	等於
notEqual	不等於
lessThan	小於
lessThanOrEqual	小於或等於
greaterThan	大於
greaterThanOrEqual	大於或等於
beginsWith	以~開頭
endsWith	以~結尾
between	介於~之間
notBetween	介於~之外
containsText	包含文字
notContains	不包含文字

參數 formula 是運算子後面的條件值, 其值為一個串列或元組, 或者是儲存格索引例如 '$B$3', 它可以只有一個元素, 例如用在運算子 'equal' 或 'lessThan' 時; 也有可能包含多個元素, 例如用在 'between' 運算子時. 

參考 :

# openpyxl.formatting.rule module

以上面的成績單為例, 如果要套用格式化條件在超過 90 分的儲存格套用黃底色; 而低於 60 分套用紅底色, 首先要匯入 openpyxl.formatting.rule.CellIsRule 函式來建立 Rule 物件 :

例如 : 

>>> from openpyxl.formatting.rule import CellIsRule      

>>> from openpyxl.styles import PatternFill    

>>> fill1=PatternFill(start_color='FFFF00',  end_color='FFFF00', fill_type='solid')   

>>> fill2=PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')    # 紅色

>>> rule1=CellIsRule(operator='lessThan', formula=[60], stopIfTrue=True, fill=fill1)     

>>> rule2=CellIsRule(operator='greaterThanOrEqual', formula=[90], stopIfTrue=True, fill=fill2)     

>>> sheet1.conditional_formatting.add('B3:D7', rule1)   

>>> sheet1.conditional_formatting.add('B3:D7', rule2)       

>>> wb.save('scores2.xlsx')      

此處使用的儲存格格式是填滿底色, 所以先建立兩個 FillPattern 物件, fill1 填黃色, fill2 填紅色, 然後分別傳入 CellIsRule() 函式建立兩個 Rule 物件 rule1 與 rule2, 最後呼叫工作表物件的 conditional_formatting.add() 方法設定成績區域 'B3:D7' 內的儲存格先後套用此兩規則 (誰先誰後皆可), 存檔後用 Excel 開啟檔案結果竟然無效, why?   

 

在官網找到另外一種做法, 改用 openpyxl.formatting.Rule 類別來產生 Rule 物件 :

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

>>> from openpyxl.formatting import Rule     

>>> from openpyxl.styles.differential import DifferentialStyle    

>>> from openpyxl.styles import Font    

>>> dxf1=DifferentialStyle(font=Font(bold=True), fill=fill1) 

>>> dxf2=DifferentialStyle(font=Font(bold=True), fill=fill2) 

>>> rule1=Rule(type='cellIs', dxf=dxf1, operator='lessThan', formula=[60]) 

>>> rule2=Rule(type='cellIs', dxf=dxf1, operator='greaterThanOrEqual', formula=[90]) 

>>> sheet1.conditional_formatting.add('B3:D7', rule1)   

>>> sheet1.conditional_formatting.add('B3:D7', rule2)       

>>> wb.save('scores2.xlsx')      

但也是無效, 有空再來研究看看. 

參考 : 

# openpyxl的基本使用

# Excel Automation with Openpyxl in Python

# Find last filled column in openpyxl

# Use multiple cells in openpyxl conditional formatting formula

# https://www.youtube.com/watch?v=qUj7BVZmuiw

# 【Python×Excel】Conditional formatting (Cell Highlighting, Top/Bottom Extraction) in openpyxl 【Part2】