2022年6月21日 星期二

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

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


在此之前先摘要整理前一篇關於工作表操作之指令 :
  • 取得工作表名稱 :
    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 以左以上之儲存格 (即第一列)
以下測試會使用到下面兩個測試檔案 : 
下載後放在與程式相同之工作目錄下即可. 


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)


字型樣式的預設值參考 :


例如 : 

>>> 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 英吋), 但列高無預設值 (視內容高度而定), 參考 : 


用上面的工作表為例修改 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'
參考 :


以下以 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 即可. 參考 :


以上面的 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' 運算子時. 

參考 :


以上面的成績單為例, 如果要套用格式化條件在超過 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 物件 :


>>> 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')      

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

參考 : 

價格親民的 Linux 個人電腦 MP510-50

昨天一頭熱對香蕉派 Banana Pi 系列產品做了一番市調, 發現 BPI-M5 與 BPI-M2 Zero 是很不錯的樹莓派代替品, 但今天找到下面這個由中鼎 (客服 02-23623620, 0935-909-821) 開發的 Ubuntu Mate Linux 微型電腦, 大小只有一個巴掌大, 規格跟 BPI-M5 幾乎一樣 (Flash 是四倍 64 GB) :  





仔細看其硬體規格, 發現根本就是 Banana Pi BPI-M5 的客製版, 差別只是內建 Flash 由 16GB 提升為 64GB 以及裝好了 Ubuntu Mate 作業系統而已, 買來即可使用不須做任何設定, PCHome 價格只要 2450 元, 而買一片 Banana Pi BPI-M5 裸版就要 2600 元, 如果只是要拿 Linux 電腦當教學工具 (例如學生因疫情必須居家學習上網連線) 還不如買這台, 不需要買裸板來自行安裝 :  


但若是要做實驗那就得買裸板了. 

2022-06-21 補充 :

昨天 PCHome 商品頁原本顯示 "已售完補貨中", 我今天詢問中鼎, 客服說他查有貨, 說已經催 PCHome 重新上架, 希望我也跟他們反映, 早上寫了 email 給 PCHome (他們的電話根本沒辦法按 9 專人接聽), 結果晚上就上架了, 我馬上下單買一台 : 


折 100 元實付 2350 元. 

報名高雄 Python 社群 Flask 免費線上課程 (3)

今天在臉書 Python 社群看到 6/28 晚上 19:30~20:30 的 Flask (3) 的免費線上課程 : 


我已在 KKTIX 報名參加課程, 到時連線 Google Meet 上課即可 :





我好像之前有報名過 Flask (1), 但卻忘記去上課了. 老師有把之前的課程資料放在 GitHub :


明天起連續三天是 Django 內訓, 月底再上完 Flask, 這樣六月底前就把 Python Web 的學習給一舉解決掉, 不亦快哉! (應該是想得美, 前兩周的 Python + Excel 到現在都還沒結束掉).

香蕉派 Banana Pi M1 A20 開發板

昨天對香蕉派 BPI 開發板做了一點研究, 覺得 BPI-M5 是目前樹莓派市場價格飆漲下很不錯的另類選擇. 這讓我想起 Tim 兄以前好像送給我一塊他在鴻海時開發的香蕉派 (2018 年 9 月, 另外還送我一塊 bpi:bit, 也是他開發的, 超級厲害), 趕緊搜尋零件盒把它找出來, 根據背板上的 A20 晶片, 研判應該是第一代的 Banana Pi M1 A20 開發板, 因為一直都沒時間測試, 所以連防靜電塑膠袋都還沒撕開呢 :




這塊板子很大的特點是提供了 SATA 硬碟插槽 (正下方 HDMI 左邊那個黑色那個), 以及左下角的硬碟電源輸出, 而且板載 MIC 麥克風與紅外線 IR 感測器. 其主要規格如下 : 
  • 處理器 : 全志 A20 雙核Cortex-A7 1 GHz 
  • GPU 圖形處理器 : Mali-400 MP2 
  • 記憶體 : 1GB DDR3 DRAM
  • 外部儲存 : Micro SD (最高可插 64 GB 卡)
  • USB 埠 : USB 2.0 * 2
  • 視訊輸出 : HDMI 
  • 視訊輸入 : CSI
  • 音訊輸出 : 3.5mm 插孔與 HDMI
  • 網路介面 : 1 GbE LAN 
  • 感測器 : 麥克風 & 紅外線 (IR)
  • GPIO : 26 Pins
  • 電源 : USB Type C (2A 以上)
這塊板子光是雙核 CPU 與 1GB DRAM 這兩項, 就比當時主流的 Pi B+ (單核 512 MB DRAM) 還要強悍很多, 參考 : 


教學文件下載 :


主要的 OS 映像檔可從下列網址下載 :

Ubuntu 16.04 映像檔下載 :


Debian 10 映像檔下載 :


Ambian 22.05 映像檔下載 :


2022年6月20日 星期一

市圖預約書目

目前市圖預約中書目 : 
  1. TensorFlow與Keras : Python深度學習應用實務
  2. AI黃金時期正好學 : TensorFlow 2高手有備而來
  3. 一行指令學Python : 用機器學習掌握人工智慧
  4. Python3.x網頁資料擷取與分析特訓教材
  5. Python網路文字探勘入門到上手
  6. Python技術者們練功!
  7. 用Excel學Python資料分析
記下來方便續借與再次預約. 

Banana Pi BPI-M5 開發板

最近樹莓派變成期貨商品似的, 價格持續上漲, 臉書社群一貼出二手 Pi 4B 馬上就被收走, 實在令人傻眼. 早知道去年幫湘芸老師架站時就應該順便自己也買一片, 當時跟飆機器人買 8GB 一片才 2800 元而已, 參考 :


如今 Pi 4B 已飆到近 8 千塊, 讓人買不下去. 

我把目光轉向 Pi 4B 的替代品 : Banana Pi, 目前最新的是 BPI-M5 型號, 露天價格最便宜的大約在 2600 元左右 (Aliexpress 約 73 美元) : 



參考 : 


其中 Aliexpress 73 美元免運折合台幣大約才2200 元不到最划算 (但打五折優惠已過). 

香蕉派 BPI-M5 在硬體上比起 Pi 4 其實並不差, 主要規格如下 :
  • 處理器 : Amlogic S905X3 四核Cortex-A55 2 GHz 
  • GPU 圖形處理器 : Mali-G31 MP2 (4 執行續 650 MHz)
  • 記憶體 : 4GB DDR4 DRAM, 16GB eMMC Flash
  • 外部儲存 : Micro SD (最高可插 256 GB 卡)
  • USB 埠 : USB 3.0 * 4
  • 視訊輸出 : HDMI 2.0
  • 音訊輸出 : 3.5mm 插孔
  • 網路介面 : 1 GbE LAN 
  • 感測器 : 紅外線 (IR)
  • 電源 : USB Type C (3A 以上)
其中內建 16 GB eMMC Flash 我覺得是一大亮點, 這樣作業系統就可以直接安裝在此內建的 Flash 上, 不需要準備一張 Micro SD 卡, 這 16 GB 容量不管是要安裝 Ubuntu, Rasbian, 或 Android 都綽綽有餘. 當然還是可以插一張 MicroSD 卡, 但那就是用來做為存放資料的外部儲存之用. 但缺點是網路僅提供 LAN, 沒有像樹莓派那樣內建 WiFi 與藍芽, 上網需要插一個 WiFi dongle, 或購買 WiFi + BT 擴充板 : 

 
參考 : 


作業系統安裝教學參考 : 


2022-06-20 補充 :

BPI 也有出類似 Pi Zero W 的產品 BPI-M2 Zero, 採用四核處理器比 Pi Zero W 的單核效能強, 而且板帶 IPEX 天線座可外接天線, 目前 Aliexpress 最便宜的是 30.42 美元 :


參考 : 


跑 Benchmark 顯示 BPI-M2 Zero 的效能是 Pi Zero 的四倍, 是 Pi 3A+ 的一半 : 





看起來 BPI-M2 Zero 效能真的比 Pi Zero 要強很多啊! 參考 :


2022年6月19日 星期日

2022 年第 25 周記事

本周主要的心力花在 Python 套件 openpyxl 的測試學習上, 希望一鼓作氣把去年底開的頭拉到底做完, 因為我發現 Excel 很適合做為資料庫的代用品, 可以快速將構想的 Prototype 實作出來, 若需要做成完整的網頁應用程式再移植到資料庫. 另外一個 xlwings 可能先 Hold 一下, 因為還要回頭把正規式進階篇測完 (其實還有一個原因 : 樹莓派上無法使用 xlwings).

因為菁菁說這禮拜想跟我回鄉下, 所以就提早在週六早上就回去了. 但匆匆忙忙竟然忘了把已到貨的露營帳篷帶回去試用, 周末這兩天好熱, 到了晚上夕曬的房間還是熱烘烘, 而二樓頂天台上卻非常涼快, 我打算以後周末回鄉下晚上就睡二樓頂天台的帳篷.

今天下午忙完後去爬獅形頂, 用小米手錶運動版測量繞一圈回來剛好 3 公里 :






走 3 公里路大約花 40~50 分鐘, 每天留些汗真的很不錯. 下周要開始動起來買材料搭建儲能太陽能板的支架了, 估計尺寸跟上回並聯型一樣, 保留一個擴充位置, 看使用情況也許未來還要再加一塊, 600 多瓦應該足夠應付平常夜間照明與緊急用電需求. 

料理實驗 : 實作阿慶師的脆皮燒肉

端午節前我在臉書看到阿慶師的脆皮燒肉作法, 覺得不難做 :





剛好端午節拜祖先的豬肉可以拿來做這道, 但冰箱菜太多, 這幾周終於把親友給的魚消化掉了, 而本周剛好菁菁與水某有一起回鄉下, 就趁這機會實作了這道菜. 







實作結果算是 85 分, 因為皮似乎脆過頭而有點硬, 但有些又真的脆, 可能我家老烤箱溫度比較高, 下次做要設 180 度. 另外我這次醃製時間只有四個小時 (阿慶師說至少要 6 個小時, 最好是放冰箱隔夜), 比較沒那麼入味, 但還是被吃到只剩一塊. 

Chrome 頁籤備份

因為 Chrome 開啟的頁籤實在太多了, 這樣會吃掉很多記憶體, 暫時將目前沒時間細看, 但具有參考價值者先記錄下來後關掉 : 


2022年6月18日 星期六

市圖還書 4 本

本周市圖有四本書被預約要還 :
因目前正在專心學習 Python+Excel+Pandas+爬蟲, 故 No.1~3 的機器學習暫緩也好, 尤其 PyTorch 我看是排在 TF2 之後為宜. No.4 是在鄉下的分館偶然在書架上看到的, 稍微翻閱一下發現房市水還真的很深啊! 買房前值得一讀. 

2022年6月17日 星期五

河堤健走

連續三天下班後都恢復去河堤健走, 因為在家上班都沒出去呼吸外面的空氣, 雖然還是跟在辦公室一樣早上下午休息時間各做一次 TABATA, 但運動量比健走還是差很多. 前兩天都忘了帶小米手錶運動版去, 今天特地帶上測量里程與運動量, 走到大順路折返再走到新莊仔路折回共約 3.4KM, 4700 步, 費時約 43 分鐘 : 





燒掉 200 大卡左右, 雖然不夠看, 下周還是持續走. 我現在不帶耳機聽英文了, 因為也沒在專心聽, 乾脆默念四字佛號, 四步一句, 4700 步就是念了近 1200 次佛號, 運動即修行, 看看能否念到一心不亂. 

2022年6月15日 星期三

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

因為疫情我已在家上班一個月了, 除了公務外, 公司的線上課程每天都要看, 最近上了台大張佑成老師的 Python + Excel 整合術課程 (超棒), 學到了另一個 Excel 套件 xlwings 的基本用法, 但 xlwings 只能用在有安裝 Excel 軟體的 Windows 與 MacOS 上, 於是我又回頭繼續 OpenPyXL 的學習, 因為它是目前唯一可以在沒有 Excel 的環境下讀寫編輯 xlsx 檔案的套件, 所以若要在樹莓派 Linux 上操作 xlsx 檔, 首選便是 OpenPyXL 啦!

本系列前一篇文章參考 : 


官網教學資料參考 :


本篇主要是測試 Worksheet 工作表物件與操作工作表的內容, 在此之前先摘要整理前一篇關於工作簿與工作表操作之指令 : 
  • 匯入 openpyxl 套件 :
    import openpyxl as xl
  • 載入現有 xlsx 檔為工作簿物件 : 
    wb=xl.load_excel('users.xlsx')
    wb=xl.load_excel(r'C:\test\users.xlsx')
  • 建立空白工作簿物件 :
    wb.Workbook()
  • 將工作簿物件儲存為 xlsx 檔 (若有用 Excel 軟體開啟須先關檔) : 
    wb.save('users.xlsx')
    wb.save(r'C:\test\users.xlsx')
  • 取得工作表名稱串列 :
    wb.sheetnames
  • 取得工作表物件串列 :
    wb.worksheets
  • 取得作用中的工作表物件 :
    wb.active
  • 從工作簿物件取得工作表物件 :
    wb.worksheets[0]     # 使用 worksheets 串列索引
    wb['工作表1']          # 使用工作表名稱索引
  • 建立新工作表 :
    wb.create_sheet()    # 自動產生名稱
    wb.create_sheet('工作表2')     # 自訂名稱
  • 複製工作表 :
    wb.copy_worksheet(sheet1)   # 須傳入工作表物件
  • 刪除工作表 :
    wb.remove(sheet1)    # 須傳入工作表物件
以下測試會使用到下面兩個測試檔案 : 
下載後放在與程式相同之工作目錄下即可. 


6. 工作表物件 Worksheet 與儲存格物件 Cell 之基本操作 :  

由上述可知, 利用 Workbook 物件的 worksheets 屬性可取得工作簿中全部工作表物件串列, 透過 [index] 運算子即可取得特定的工作表 Worksheet 物件, 利用 Worksheet 物件的屬性與方法可以對工作表的列, 欄, 以及儲存格進行操作. 

首先使用 eval() 求值函式取得 Worksheet 物件成員的參考, 然後透過迴圈來檢視各成員的類型是屬性還是方法, 例如 : 

>>> import openpyxl as xl    
>>> wb=xl.load_workbook('users.xlsx')     # 載入試算表檔案
>>> sheet=wb['我的工作表1']       
 >>> members=dir(ws[0])     
>>> for mbr in members:               # 走訪 Workbook 物件成員
    obj=eval('ws[0].' + mbr)             # 用 eval() 求值取得 wb.成員之參考
    if not mbr.startswith('_'):            # 走訪所有不是 "_" 開頭的成員
        print(mbr, type(obj))
        
BREAK_COLUMN <class 'int'>
BREAK_NONE <class 'int'>
BREAK_ROW <class 'int'>
HeaderFooter <class 'openpyxl.worksheet.header_footer.HeaderFooter'>
ORIENTATION_LANDSCAPE <class 'str'>
ORIENTATION_PORTRAIT <class 'str'>
PAPERSIZE_A3 <class 'str'>
PAPERSIZE_A4 <class 'str'>
PAPERSIZE_A4_SMALL <class 'str'>
PAPERSIZE_A5 <class 'str'>
PAPERSIZE_EXECUTIVE <class 'str'>
PAPERSIZE_LEDGER <class 'str'>
PAPERSIZE_LEGAL <class 'str'>
PAPERSIZE_LETTER <class 'str'>
PAPERSIZE_LETTER_SMALL <class 'str'>
PAPERSIZE_STATEMENT <class 'str'>
PAPERSIZE_TABLOID <class 'str'>
SHEETSTATE_HIDDEN <class 'str'>
SHEETSTATE_VERYHIDDEN <class 'str'>
SHEETSTATE_VISIBLE <class 'str'>
active_cell <class 'str'>
add_chart <class 'method'>
add_data_validation <class 'method'>
add_image <class 'method'>
add_pivot <class 'method'>
add_table <class 'method'>
append <class 'method'>
auto_filter <class 'openpyxl.worksheet.filters.AutoFilter'>
calculate_dimension <class 'method'>
cell <class 'method'>
col_breaks <class 'openpyxl.worksheet.pagebreak.ColBreak'>
column_dimensions <class 'openpyxl.worksheet.dimensions.DimensionHolder'>
columns <class 'generator'>
conditional_formatting <class 'openpyxl.formatting.formatting.ConditionalFormattingList'>
data_validations <class 'openpyxl.worksheet.datavalidation.DataValidationList'>
delete_cols <class 'method'>
delete_rows <class 'method'>
dimensions <class 'str'>
encoding <class 'str'>
evenFooter <class 'openpyxl.worksheet.header_footer.HeaderFooterItem'>
evenHeader <class 'openpyxl.worksheet.header_footer.HeaderFooterItem'>
firstFooter <class 'openpyxl.worksheet.header_footer.HeaderFooterItem'>
firstHeader <class 'openpyxl.worksheet.header_footer.HeaderFooterItem'>
formula_attributes <class 'dict'>
freeze_panes <class 'NoneType'>
insert_cols <class 'method'>
insert_rows <class 'method'>
iter_cols <class 'method'>
iter_rows <class 'method'>
legacy_drawing <class 'NoneType'>
max_column <class 'int'>
max_row <class 'int'>
merge_cells <class 'method'>
__main__:1: DeprecationWarning: Call to deprecated function merged_cell_ranges (Use ws.merged_cells.ranges).
merged_cell_ranges <class 'list'>
merged_cells <class 'openpyxl.worksheet.cell_range.MultiCellRange'>
mime_type <class 'str'>
min_column <class 'int'>
min_row <class 'int'>
move_range <class 'method'>
oddFooter <class 'openpyxl.worksheet.header_footer.HeaderFooterItem'>
oddHeader <class 'openpyxl.worksheet.header_footer.HeaderFooterItem'>
orientation <class 'NoneType'>
page_breaks <class 'tuple'>
page_margins <class 'openpyxl.worksheet.page.PageMargins'>
page_setup <class 'openpyxl.worksheet.page.PrintPageSetup'>
paper_size <class 'NoneType'>
parent <class 'openpyxl.workbook.workbook.Workbook'>
path <class 'str'>
print_area <class 'NoneType'>
print_options <class 'openpyxl.worksheet.page.PrintOptions'>
print_title_cols <class 'NoneType'>
print_title_rows <class 'NoneType'>
print_titles <class 'NoneType'>
protection <class 'openpyxl.worksheet.protection.SheetProtection'>
row_breaks <class 'openpyxl.worksheet.pagebreak.RowBreak'>
row_dimensions <class 'openpyxl.worksheet.dimensions.DimensionHolder'>
rows <class 'generator'>
scenarios <class 'openpyxl.worksheet.scenario.ScenarioList'>
selected_cell <class 'str'>
set_printer_settings <class 'method'>
sheet_format <class 'openpyxl.worksheet.dimensions.SheetFormatProperties'>
sheet_properties <class 'openpyxl.worksheet.properties.WorksheetProperties'>
sheet_state <class 'str'>
sheet_view <class 'openpyxl.worksheet.views.SheetView'>
show_gridlines <class 'NoneType'>
show_summary_below <class 'bool'>
show_summary_right <class 'bool'>
tables <class 'openpyxl.worksheet.table.TableList'>
title <class 'str'>
unmerge_cells <class 'method'>
values <class 'generator'>
views <class 'openpyxl.worksheet.views.SheetViewList'>

Worksheet 物件的常用屬性如下表 : 


 Worksheet 物件常用屬性 說明
 title 工作表名稱
 max_row 工作表內的資料列數 (唯讀)
 max_column 工作表內的資料欄數 (唯讀)
 rows 包裹列的儲存格之產生器 (generator)
 columns 包裹欄的儲存格之產生器 (generator)
 freeze_panes 凍結指定座標儲存格上方列與左側欄 (預設 'A1' 或 None=不凍結)
 row_dimensions 列的尺寸設定物件 (其 height 屬性用來設定列儲存格高度)
 column_dimensions 欄的尺寸設定物件 (其 width 屬性用來設定欄儲存格寬度)
 conditional_formatting 格式化條件設定, 值為一 ConditionalFormattingList 物件


其中 row_dimensions 與 column_dimensions 屬性分別用來設定列高與欄寬; freeze_panes 用來凍結儲存格所在上方全部列與左側全部欄 (不包含儲存格所在之列或欄), 主要用在凍結第一列的欄名, 這樣當資料列很多往下捲動時第一列的欄名仍紋風不動, 方便閱讀資料. max_row 與 max_column 標示了工作表內容的範圍 (唯讀); rows 與 columns 則是列與欄儲存格之產生器 (generator), 只要將其傳給 list() 就能產生可迭代物件, 關於產生器參考 :


Worksheet 物件的常用方法如下表 :


 Worksheet 物件常用方法 說明
 append(iterable) 將可迭代物件 (tuple/list/dict) 內容添加到工作表最後一列資料後面
 iter_rows(r1, r2, c1, c2) 逐列產生 ['c1r1':'c2r2'] 範圍內的儲存格物件, 傳回 generator
 iter_cols(r1, r2, c1, c2) 逐欄產生 ['c1r1':'c2r2'] 範圍內的儲存格物件, 傳回 generator
 insert_rows(id [,num=1]) 在指定列 id 前面插入 num 列空白列
 insert_cols(id [,num=1]) 在指定欄 id 左方插入 num 欄空白欄
 delete_rows(id [,num=1]) 刪除指定列 id 起共 num 列資料
 delete_cols(id [,num=1]) 刪除指定欄 id 起共 num 欄資料 
 move_range(range, rows, cols) 將指定範圍 range (例如 'B2:D5') 內的儲存格移動 rows 列 cols 欄 (正=向下/向左, 負=向上/向右) 
 merge_cells(range) 將指定範圍 range (例如 'B2:D5') 內的儲存格合併
 unmerge_cells(range) 將指定範圍 range (例如 'B2:D5') 內的儲存格取消合併
 add_image(img, anchor) 在儲存格座標 anchor (例如 'A1') 插入圖片 img (Image 物件)
 add_chart(chart, anchor) 在儲存格座標 anchor (例如 'A1') 插入圖表 chart (Chart 物件)


其中 move_range() 方法可將指定範圍 range 內的儲存格移動 rows 列與 cols 欄 (預設均為 0, 表示不移動), rows 為正往下移動為負往上移動; cols 為正往左移動為負往右移動, 目標區域的原資料會被覆蓋. merge_cells() 則可將指定範圍 range 內的儲存格合併為一格, 而 unmerge_cells() 方法則是將已合併為一格之指定範圍 range 內儲存格取消合併. 注意, 存取合併儲存格時是以合併前之左上角儲存格為索引座標

以下分別測試這些主要的屬性與方法 :


(1). 取得工作表內容之列數與欄數:  

從上面 GitHub 下載 scores.xlsx 檔案放置於工作目錄下, 用 Excel 軟體開啟可知裡面有一個名為 '工作表1' 的工作表, 內容是學生的國英數成績, 占用 4 列 5 欄 :




用 openpyxl 載入後取得其中的工作表物件,  : 

>>> wb=xl.load_workbook('scores.xlsx')   
>>> wb.worksheets   
[<Worksheet "工作表1">]
>>> sheet1=wb.worksheets[0]     # 也可以用 wb['工作表1'] 取得
>>> sheet1     
<Worksheet "工作表1">      
>>> sheet1.title                   # 工作表名稱
'工作表1'
>>> sheet1.max_row          # 工作表內容占幾列 
4
>>> sheet1.max_column    # 工作表內容占幾欄
5

這個 max_row 與 max_culumn 在以迴圈取出內容時會用到. 這兩個值也可以將任一列或欄的儲存格物件串列傳給 len() 得到, 例如 : 

>>> len(sheet1['A'])      # 取得 A 欄之儲存格物件串列後傳給 len()
4
>>> len(sheet1[1])         # 取得第一列之儲存格物件串列後傳給 len()
5


(2). 呼叫工作表物件的 cell() 方法取得儲存格物件 :  

呼叫工作表物件的 cell() 方法並傳入 row 與 column 參數指定列與欄索引即可取得代表儲存格的 Cell 物件, 注意這兩個參數都是 1 起始的, Excel 的列索引本來就是用 1, 2, 3, 4, ... 定位的, 但欄索引此處也要改用 1, 2, 3, 4, ... 來定位 column 參數, 亦即 A 欄要用 column=1, B 欄要用 column=2, ... 依此類推, 例如 : 

>>> wb=xl.load_workbook('scores.xlsx')   
>>> sheet1=wb['工作表1']                          # 取得做表物件
>>> cell=sheet1.cell(row=2, column=3)     # 取得第 2 列第 3 欄 (C2) 儲存格
>>> cell   
<Cell '工作表1'.C2>
>>> type(cell)              
<class 'openpyxl.cell.cell.Cell'>     # Cell 物件

cell() 方法會傳回一個 Cell 物件, 此處第二列第三欄為 C2 儲存格 : 




可用 eval(0, dir(), 與 type() 來檢視 Cell 物件的成員 : 

>>> members=dir(cell)    
>>> for mbr in members:     
    obj=eval('cell.' + mbr)    
    if not mbr.startswith('_'):    
        print(mbr, type(obj))      
        
alignment <class 'openpyxl.styles.proxy.StyleProxy'>
base_date <class 'datetime.datetime'>
border <class 'openpyxl.styles.proxy.StyleProxy'>
check_error <class 'method'>
check_string <class 'method'>
col_idx <class 'int'>
column <class 'int'>
column_letter <class 'str'>
comment <class 'NoneType'>
coordinate <class 'str'>
data_type <class 'str'>
encoding <class 'str'>
fill <class 'openpyxl.styles.proxy.StyleProxy'>
font <class 'openpyxl.styles.proxy.StyleProxy'>
has_style <class 'bool'>
hyperlink <class 'NoneType'>
internal_value <class 'int'>
is_date <class 'bool'>
number_format <class 'str'>
offset <class 'method'>
parent <class 'openpyxl.worksheet.worksheet.Worksheet'>
pivotButton <class 'bool'>
protection <class 'openpyxl.styles.proxy.StyleProxy'>
quotePrefix <class 'bool'>
row <class 'int'>
style <class 'str'>
style_id <class 'int'>
value <class 'int'>

可見 Cell 儲存格物件的成員都是屬性, 沒有方法, 常用之屬性如下表 : 


 Cell 物件常用屬性 說明
 column 儲存格所在之欄索引 (1 起始之整數)
 column_letter 儲存格所在之欄索引字串 ('A', 'B', 'C', ...)
 row 儲存格所在之列索引 (1 起始之整數)
 data_type 儲存格之資料型態
 number_format 儲存格之數值格式
 value 儲存格之內容
 font 儲存格內容的字型 (值為 Font 物件)
 alignment 儲存格對齊 vertical=top/center/bottom, horizontal=left/center/right
 border 儲存格框線, 值=Border 物件 (屬性 left/right/top/bottom=Side 物件)
 fill 儲存格背景色, 值為 PatternFill 物件


屬性 column 與 column_letter 分別記錄 Cell 物件所在的欄索引, column 是整數索引, column_letter 是字串索引; font 屬性用來設定儲存格的字型樣式 (name, size, color, bold, italic, strike 等), 其值為一個 openpyxl.styles.Font 物件. alignment 屬性值為一個 Alignment 物件; border 屬性值為一個 Border 物件; fill 屬性值為一個 PatternFill 物件, 這四個屬性為儲存格常用的格式設定屬性. 

儲存格物件屬性其中最常用的是 value 屬性, 可用來存取儲存格內容. 以上面的 C2 為例 :

>>> cell=sheet1.cell(row=2, column=3)     # 取得第 2 列第 3 欄 (C2) 儲存格
>>> cell.row                      # 儲存格所在之列索引
2
>>> cell.column                # 儲存格所在之欄索引
3
>>> cell.column_letter     # 儲存格所在之欄索引字串
'C'
>>> cell.value                    # 儲存格之內容
60
>>> cell.data_type            # 儲存格之資料型態 
'n'
>>> cell.number_format  # 儲存格之數值格式
'General'

透過 value 屬性可以存取儲存格內容, 亦即可以讀取也可以寫入, 這是使用 Python 操作 Excel 試算表的核心運算, 例如 : 

>>> cell=sheet1.cell(row=2, column=3)     # 取得 C3 儲存格物件
>>> cell.value             # 讀取儲存格內容
60
>>> cell.value=70       # 更改儲存格內容
>>> cell.value    
70

除了使用 Cell 物件的 value 屬性賦值來寫入儲存格內容外, 還可以在呼叫 cell() 方法時傳入 value 參數來寫入, 語法如下 :

工作表物件.cell(row, column, value)

例如 : 

>>> cell=sheet1.cell(row=2, column=3, value=80) 
>>> cell.value    
80

注意, 若要將公式寫入儲存格, 必須以字串型態寫入, 例如 cell.value='=SUM(A1:B1)'.


(3). 使用 [] 運算子取得儲存格物件:  

除了呼叫 Worksheet 物件的 cell() 方法取得 Cell 物件外, 也可以用 [] 運算子, 以儲存格索引座標字串例如 'A1', 'C3', 'E4' 等當索引來取得, 其格式為 : 

工作表物件['儲存格座標']

例如 :   

>>> cell=sheet1['C2']    
>>> cell    
<Cell '工作表1'.C2>     
>>> type(cell)   
<class 'openpyxl.cell.cell.Cell'>     
>>> cell.value       
60

也可以用 [] 運算子以 1 起始的列索引來選取列資料的 Cell 物件, 語法如下 : 

工作表物件[列索引] 

以上面的 scores.xlsx 為例, 第 2 列為李小白成績 : 

>>> sheet1[2]           # 取得第二列的 Cell 物件串列
(<Cell '工作表1'.A2>, <Cell '工作表1'.B2>, <Cell '工作表1'.C2>, <Cell '工作表1'.D2>, <Cell '工作表1'.E2>)
>>> for cell in sheet1[2]:          # 迭代第 2 列中的 Cell 物件
    print(cell.value, end='\t')      # 印出 Cell 物件之值
    
李小白 94 60 49 =SUM(B2:D2)

同樣地, 可以用 [] 運算子以欄索引字串來選取欄資料的 Cell 物件, 語法如下 : 

工作表物件['欄索引'] 

以上面的 scores.xlsx 為例, 第 2 欄為國文成績 : 

>>> sheet1['B']         # 取得第二欄的 Cell 物件串列
(<Cell '工作表1'.B1>, <Cell '工作表1'.B2>, <Cell '工作表1'.B3>, <Cell '工作表1'.B4>)
>>> for cell in sheet1['B']:     
    print(cell.value, end='\n')        # 以跳行結尾
    
國文
94
85
77

除此之外還可以在 [] 中使用座標索引切片來取得一個矩形範圍內的 Cell 物件, 語法為 :

工作表物件['左上角座標' : '右下角座標']  或 
工作表物件['左上角座標 : 右下角座標']  

例如 sheet1['A2' : 'E4'] 就可取得 A2 至 E4 間的矩形區域內的所有儲存格物件, 結果會以巢狀 tuple 傳回, 內部 tuple 由每列之 Cell 物件構成, 然後各列再組成外部 tuple, 例如 : 

>>> cell_range=sheet1['B2':'E4']    
>>> cell_range         
((<Cell '工作表1'.B2>, <Cell '工作表1'.C2>, <Cell '工作表1'.D2>, <Cell '工作表1'.E2>), (<Cell '工作表1'.B3>, <Cell '工作表1'.C3>, <Cell '工作表1'.D3>, <Cell '工作表1'.E3>), (<Cell '工作表1'.B4>, <Cell '工作表1'.C4>, <Cell '工作表1'.D4>, <Cell '工作表1'.E4>))
>>> type(cell_range)      
<class 'tuple'>  
>>> sheet1['B2:E4']      # [] 內的範圍用 'B2:E4' 或 'B2':'E4' 都可以    
((<Cell '工作表1'.B2>, <Cell '工作表1'.C2>, <Cell '工作表1'.D2>, <Cell '工作表1'.E2>), (<Cell '工作表1'.B3>, <Cell '工作表1'.C3>, <Cell '工作表1'.D3>, <Cell '工作表1'.E3>), (<Cell '工作表1'.B4>, <Cell '工作表1'.C4>, <Cell '工作表1'.D4>, <Cell '工作表1'.E4>)) 

可以用雙層迴圈迭代此巢狀 tuple 來取得此範圍內的儲存格內容 : 

>>> for row in cell_range:             # 迭代範圍內之每列
    for cell in row:                             # 迭代範圍內每列之儲存格物件
        print(cell.value, end='\t')        # 每列 Cell 內容以 Tab 隔開
    print()    
    
94 60 49 =SUM(B2:D2)
85 75 76 =SUM(B3:D3)
77 85 65 =SUM(B4:D4)

可見這些都是 B2 與 A4 範圍內的儲存格內容. 注意, [] 內的儲存格範圍雖然兩種用法都可以, 但方法中的 range 座標只能用 '左上角座標 : 右下角座標', 因為那是字串. 


(4). 利用工作表物件的 rows 與 columns 屬性取得儲存格物件 :  

在上面 Worksheet 物件的常用屬性中有 rows 與 columns 這兩個屬性, 它們的值是一個儲存格物件的產生器 (generator), 只要將產生器傳入 list() 就可以用來產生列儲存格與欄儲存格組成之可迭代物件 (串列), 例如 : 

>>> sheet1.rows             # 值為產生器
<generator object Worksheet._cells_by_row at 0x00000162BB27C750>
>>> sheet1.columns       # 值為產生器
<generator object Worksheet._cells_by_col at 0x00000162BB27CDE0>

將這兩個產生器傳入 list() 產生串列 : 

>>> list(sheet1.rows)            # 產生各列儲存格打包成 tuple 之串列
[(<Cell '工作表1'.A1>, <Cell '工作表1'.B1>, <Cell '工作表1'.C1>, <Cell '工作表1'.D1>, <Cell '工作表1'.E1>), (<Cell '工作表1'.A2>, <Cell '工作表1'.B2>, <Cell '工作表1'.C2>, <Cell '工作表1'.D2>, <Cell '工作表1'.E2>), (<Cell '工作表1'.A3>, <Cell '工作表1'.B3>, <Cell '工作表1'.C3>, <Cell '工作表1'.D3>, <Cell '工作表1'.E3>), (<Cell '工作表1'.A4>, <Cell '工作表1'.B4>, <Cell '工作表1'.C4>, <Cell '工作表1'.D4>, <Cell '工作表1'.E4>)]
>>> list(sheet1.columns)      # 產生各欄儲存格打包成 tuple 之串列
[(<Cell '工作表1'.A1>, <Cell '工作表1'.A2>, <Cell '工作表1'.A3>, <Cell '工作表1'.A4>), (<Cell '工作表1'.B1>, <Cell '工作表1'.B2>, <Cell '工作表1'.B3>, <Cell '工作表1'.B4>), (<Cell '工作表1'.C1>, <Cell '工作表1'.C2>, <Cell '工作表1'.C3>, <Cell '工作表1'.C4>), (<Cell '工作表1'.D1>, <Cell '工作表1'.D2>, <Cell '工作表1'.D3>, <Cell '工作表1'.D4>), (<Cell '工作表1'.E1>, <Cell '工作表1'.E2>, <Cell '工作表1'.E3>, <Cell '工作表1'.E4>)]

可見 list(sheet1.rows) 會將每一列儲存格物件先打包成 tuple, 然後再組成串列; 反之, list(sheet1.columns) 則是將每欄儲存格物件先打包成 tuple, 然後再組成串列. 只要用迴圈迭代這些串列即可依序取得 Cell 儲存格物件與其內容, 例如迭代第 2 列李小白資料 :

>>> for cell in list(sheet1.rows)[1]:     # 串列索引 1 為 scores.xlsx 的第 2 列
    print(cell.value)        
    
李小白
94
60
49
=SUM(B2:D2)

注意, 這裡最後一欄的總分欄因為用 load_workbook() 載入 xlsx 檔時沒有傳入參數 data_only=True, 所以公式儲存格預設不會先執行計算, 故載入原始公式. 

迭代第三欄的英文成績例如 : 

>>> for cell in list(sheet1.columns)[2]:    # 串列索引 2 為 scores.xlsx 的第 3 列  
    print(cell.value)      
    
英文
60
75
85

如果要取得工作表中的全部資料, 就必須用雙層迴圈, 第一種做法是先迭代列, 再迭代欄中的儲存格, 這種方式與 Excel 中的排列方式一致, 例如 : 

>>> for row in sheet1.rows:          # 迭代列
    for cell in row:                            # 迭代列中的各欄儲存格
        print(cell. value, end='\t')
    print()   
    
None 國文 英文 數學 總分
李小白 94 60 49 =SUM(B2:D2)
杜小甫 85 75 76 =SUM(B3:D3)
王小維 77 85 65 =SUM(B4:D4)

當然也可以先迭代欄, 然後迭代每欄中的各列儲存格, 例如 : 

>>> for column in sheet1.columns:     
    for cell in column:    
        print(cell. value, end='\t')   
    print()        
    
None 李小白 杜小甫 王小維
國文 94 85 77
英文 60 75 85
數學 49 76 65
總分 =SUM(B2:D2) =SUM(B3:D3) =SUM(B4:D4)

可見這種迭代方式與 Excel 的排列欄與列顛倒. 


(5). 呼叫工作表物件的 iter_rows() 與 iter_cols() 取得儲存格產生器 :  

iter_rows() 與 iter_cols() 這兩個方法與上面的 rows 與 columns 屬性一樣都是傳回一個範圍的儲存格產生器 (generator), 語法如下 :

iter_rows(min_row, max_row, min_col, max_col)     
iter_cols(min_row, max_row, min_col, max_col) 

傳入的四個參數即範圍之座標 (皆為整數索引, 故 A 欄要用 1, B 欄要用 2, ....), 兩者的差別是產生的順序, iter_rows() 是逐列 (先列後欄); 而 iter_cols() 是逐欄 (先欄後列).

例如 : 

>>> sheet1.iter_rows(1, 6, 1, 5)    
<generator object Worksheet._cells_by_row at 0x00000162BB2FC390>
>>> sheet1.iter_cols(1, 6, 1, 5)   
<generator object Worksheet._cells_by_col at 0x00000162BB2FC6D8>

只要將產生器傳入 list() 就可以將此範圍內的儲存格依序產生出來 : 

>>> list(sheet1.iter_rows(1, 6, 1, 5))        # 先列後欄
[(<Cell '工作表1'.A1>, <Cell '工作表1'.B1>, <Cell '工作表1'.C1>, <Cell '工作表1'.D1>, <Cell '工作表1'.E1>), (<Cell '工作表1'.A2>, <Cell '工作表1'.B2>, <Cell '工作表1'.C2>, <Cell '工作表1'.D2>, <Cell '工作表1'.E2>), (<Cell '工作表1'.A3>, <Cell '工作表1'.B3>, <Cell '工作表1'.C3>, <Cell '工作表1'.D3>, <Cell '工作表1'.E3>), (<Cell '工作表1'.A4>, <Cell '工作表1'.B4>, <Cell '工作表1'.C4>, <Cell '工作表1'.D4>, <Cell '工作表1'.E4>), (<Cell '工作表1'.A5>, <Cell '工作表1'.B5>, <Cell '工作表1'.C5>, <Cell '工作表1'.D5>, <Cell '工作表1'.E5>), (<Cell '工作表1'.A6>, <Cell '工作表1'.B6>, <Cell '工作表1'.C6>, <Cell '工作表1'.D6>, <Cell '工作表1'.E6>)]
>>> list(sheet1.iter_cols(1, 6, 1, 5))         # 先欄後列
[(<Cell '工作表1'.A1>, <Cell '工作表1'.A2>, <Cell '工作表1'.A3>, <Cell '工作表1'.A4>, <Cell '工作表1'.A5>), (<Cell '工作表1'.B1>, <Cell '工作表1'.B2>, <Cell '工作表1'.B3>, <Cell '工作表1'.B4>, <Cell '工作表1'.B5>), (<Cell '工作表1'.C1>, <Cell '工作表1'.C2>, <Cell '工作表1'.C3>, <Cell '工作表1'.C4>, <Cell '工作表1'.C5>), (<Cell '工作表1'.D1>, <Cell '工作表1'.D2>, <Cell '工作表1'.D3>, <Cell '工作表1'.D4>, <Cell '工作表1'.D5>), (<Cell '工作表1'.E1>, <Cell '工作表1'.E2>, <Cell '工作表1'.E3>, <Cell '工作表1'.E4>, <Cell '工作表1'.E5>), (<Cell '工作表1'.F1>, <Cell '工作表1'.F2>, <Cell '工作表1'.F3>, <Cell '工作表1'.F4>, <Cell '工作表1'.F5>)]
 
可見 iter_rows() 產出的結果與 rows 屬性一樣; 而 iter_cols() 產出的結果與 columns 屬性一樣. 


(6). 呼叫工作表物件的 insert_rows() 方法插入空白列 :  

呼叫 Worksheet 物件的 insert_rows() 方法可在指定的列索引前面插入一個或多個空列, 語法為 :

工作表物件.insert_rows(idx [, num=1]) 

其中 idx 為欲插入的列索引, 備選參數為要插入的列數 (預設是 1 列), 例如 : 

>>> sheet1.insert_rows(3)           # 在第 3 列前面插入一個空列
>>> for row in sheet1.rows:         
    for cell in row:     
        print(cell. value, end='\t')        
    print()     
    
None 國文 英文 數學 總分
李小白 94 60 49 =SUM(B2:D2)
None None None None None      
杜小甫 85 75 76 =SUM(B3:D3)
王小維 77 85 65 =SUM(B4:D4)

可見在原第三列 (杜小甫) 前面添加了一個空列, 內容都是 None. 如果傳入第二參數 num 則可以一 次添加好幾個空列, 例如 : 

>>> sheet1.insert_rows(3, 2)      # 在第三列前面插入兩個空列
>>> for row in sheet1.rows:     
    for cell in row: 
        print(cell. value, end='\t')     
    print()   
    
None 國文 英文 數學 總分
李小白 94 60 49 =SUM(B2:D2)
None None None None None
None None None None None   
None None None None None
杜小甫 85 75 76 =SUM(B3:D3)
王小維 77 85 65 =SUM(B4:D4)

這些空列都不會即時反應在開啟的 Excel 程式裡, 必須呼叫 wb.save('scores.xlsxsx') 存檔才行, 但若已經開啟 scores.xlsx 必須先關檔 :

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

再次開啟 scores.xlsx 檔即可看到這三列空白列 : 




(7). 呼叫工作表物件的 delete_rows() 方法刪除列 :  

呼叫 Worksheet 物件的 delete_rows() 方法可從指定的列索引開始刪除一個或多個空列, 語法為 :

工作表物件.delete_rows(idx [, num=1]) 

其中 idx 為欲刪除的列索引, 備選參數為要刪除的列數 (預設是 1 列), 首先來看一下目前工作表物件中的內容 : 

>>> for row in sheet1.rows:    
    for cell in row:    
        print(cell. value, end='\t')    
    print()   
    
None 國文 英文 數學 總分
李小白 94 60 49 =SUM(B2:D2)
None None None None None
None None None None None
None None None None None
杜小甫 85 75 76 =SUM(B3:D3)
王小維 77 85 65 =SUM(B4:D4)

現在要呼叫 delete_rows() 來刪除列 3, 4, 5 這三個空白列, 首先只傳入列索引 3 表示只刪除第三列這一列, 例如 : 

>>> sheet1.delete_rows(3)       # 只刪除第三列
>>> for row in sheet1.rows:    
    for cell in row:    
        print(cell. value, end='\t')    
    print() 
    
None 國文 英文 數學 總分
李小白 94 60 49 =SUM(B2:D2)
None None None None None
None None None None None
杜小甫 85 75 76 =SUM(B3:D3)
王小維 77 85 65 =SUM(B4:D4)

可見第一個空白列 (第三列) 已被刪除, 現在剩下列索引為 3, 4 的兩個空白列 (刪除後下方各列索引往上補位). 若傳入第二參數 num 則可一次刪除從列索引 idx 起往下的連續多列資料, 例如 : 

>>> sheet1.delete_rows(3, 2)       # 從列索引 3 開始往下刪除連續 2 列
>>> for row in sheet1.rows:    
    for cell in row:    
        print(cell. value, end='\t')    
    print() 

None 國文 英文 數學 總分
李小白 94 60 49 =SUM(B2:D2)
杜小甫 85 75 76 =SUM(B3:D3)
王小維 77 85 65 =SUM(B4:D4)

可見所有空白列都已經被刪除了. 


(8). 呼叫工作表物件的 insert_cols() 方法插入空白欄 :  

呼叫 Worksheet 物件的 insert_cols() 方法可在指定的欄索引前面 (即左邊) 插入一個或多個空白欄, 語法為 :

工作表物件.insert_cols(idx [, num=1]) 

其中 idx 為欲插入的欄索引, 備選參數為要插入的欄數 (預設是 1 欄), 例如 : 

>>> sheet1.insert_cols(3)           # 在第 3 欄前面 (左邊) 插入一個空白欄
>>> for row in sheet1.rows:         
    for cell in row:     
        print(cell. value, end='\t')        
    print()     
    
None 國文 None 英文 數學 總分
李小白 94 None 60 49 =SUM(B2:D2)
杜小甫 85 None 75 76 =SUM(B3:D3)
王小維 77 None 85 65 =SUM(B4:D4)

可見在原第三欄 (英文) 前面或左方添加了一個空白欄, 內容都是 None. 如果傳入第二參數 num 則可以一 次添加好幾個空欄, 現在第三欄是 None 欄, 下面範例是在其前面插入兩個空欄 : 

>>> sheet1.insert_cols(3, 2)      # 在第三欄前面 (左方) 插入兩個空欄
>>> for row in sheet1.rows:     
    for cell in row: 
        print(cell. value, end='\t')     
    print()   
    
None 國文 None None None 英文 數學 總分
李小白 94 None None None 60 49 =SUM(B2:D2)
杜小甫 85 None None None 75 76 =SUM(B3:D3)
王小維 77 None None None 85 65 =SUM(B4:D4)

若已經開啟 scores.xlsx 先關檔, 然後呼叫 wb.save('scores.xlsxsx') 存檔再用 Excel 開啟便能看到上面兩個 insert_cols() 插入的三個空欄了 : :

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

再次開啟 scores.xlsx 檔即可看到這三列空白欄 : 




(9). 呼叫工作表物件的 delete_cols() 方法刪除欄 :  

呼叫 Worksheet 物件的 delete_cols() 方法可從指定的欄索引開始刪除一個或多個空欄, 語法為 :

工作表物件.delete_cols(idx [, num=1])    

其中 idx 為欲刪除的欄索引 (1 起始的整數, 1 對應 A 欄, 2 對應 B 欄, ... 其餘類推), 備選參數 num 為要刪除的欄數 (預設是 1 欄). 在上面的範例中已於工作表物件內插入三個空欄 : 

>>> for row in sheet1.rows:    
    for cell in row:    
        print(cell. value, end='\t')    
    print()   
    
None 國文 None None None 英文 數學 總分
李小白 94 None None None 60 49 =SUM(B2:D2)
杜小甫 85 None None None 75 76 =SUM(B3:D3)
王小維 77 None None None 85 65 =SUM(B4:D4)

現在要呼叫 delete_cols() 方法來刪除列 3, 4, 5 這三個空白欄, 若只傳入欄索引 3 表示只刪除第三欄這一欄 (即 C 欄), 例如 : 

>>> sheet1.delete_cols(3)       # 只刪除第三欄 (C 欄)
>>> for row in sheet1.rows:    
    for cell in row:    
        print(cell. value, end='\t')    
    print() 
    
None 國文 None None 英文 數學 總分
李小白 94 None None 60 49 =SUM(B2:D2)
杜小甫 85 None None 75 76 =SUM(B3:D3)
王小維 77 None None 85 65 =SUM(B4:D4)

可見第一個空白欄 (C 欄) 已被刪除, 現在剩下欄索引為 3, 4 的兩個空白欄 (原 C 欄被刪除後右邊個欄索引會往左補位). 若傳入第二參數 num 則可一次刪除從 idx 起往右的連續多欄資料, 例如 : 

>>> sheet1.delete_cols(3, 2)       # 從欄索引 3 開始向右刪除連續 2 欄
>>> for row in sheet1.rows:    
    for cell in row:    
        print(cell. value, end='\t')    
    print() 

None 國文 英文 數學 總分
李小白 94 60 49 =SUM(B2:D2)
杜小甫 85 75 76 =SUM(B3:D3)
王小維 77 85 65 =SUM(B4:D4)

可見所有空白欄都已經被刪除了. 


(10). 呼叫工作表物件的 append() 方法添加資料於列尾 :  

呼叫 Worksheet 物件的 append() 方法並傳入一個可迭代物件 (例如串列, 元組, 或字典) 可將其內容添加到工作表之列尾, 例如 : 

>>> sheet1.append(['元小稹', 88, 34, 100, '=SUM(B5:D5)'])    # 也可以用 tuple
>>> for row in sheet1.rows:    
    for cell in row:    
        print(cell. value, end='\t')    
    print() 
    
None 國文 英文 數學 總分
李小白 94 60 49 =SUM(B2:D2)
杜小甫 85 75 76 =SUM(B3:D3)
王小維 77 85 65 =SUM(B4:D4)
元小稹 88 34 100 =SUM(B5:D5)

可見已在列尾添加新資料列. 注意, 公式寫入儲存格必須使用字串型態

append() 也可以傳入字典, 這時必須以欄索引字串例如 'A', 'B', 'C', ... 等當作字典的鍵 (key), 例如 :

>>> new_row={'A':'岑小參', 'B':90, 'C':45, 'D':100, 'E':'=SUM(B6:D6)'}   # 新資料 
>>> sheet1.append(new_row)       # 傳入字典
>>> for row in sheet1.rows:    
    for cell in row:    
        print(cell. value, end='\t')    
    print() 
    
None 國文 英文 數學 總分
李小白 94 60 49 =SUM(B2:D2)
杜小甫 85 75 76 =SUM(B3:D3)
王小維 77 85 65 =SUM(B4:D4)
元小稹 88 34 100 =SUM(B5:D5)
岑小參 90 45 100 =SUM(B6:D6)

可見傳入的字典以添加於列尾. 


(11). 呼叫 move_cells() 方法移動儲存格 :  

此方法可將指定區域內的儲存格平移, 語法如下 : 

工作表物件.move_range(range, rows=0, cols=0) 

第一參數 range 是表示範圍的座標字串, 格式為 '左上角座標:右下角座標', 例如 'B2:E6'. 參數 rows 表示要往下 (正值) 或往上 (負值) 移動; 而 cols 表示要向右 (正值) 或往左 (負值) 移動, 因為兩者預設值均為 0, 故若不傳入此兩參數就不移動, 以上面的成績為例, 成績範圍位於 'B2:E6' : 




若要向右向下平移兩格可以這麼做 :

>>> sheet1.move_range('B2:E6', rows=2, cols=2)     # 'B2:E6' 向下向右移動兩格
>>> for row in sheet1.rows:    
    for cell in row:    
        print(cell. value, end='\t')    
    print() 
    
None 國文 英文 數學 總分 None None
李小白 None None None None None None
杜小甫 None None None None None None
王小維 None None 94 70 49 =SUM(B2:D2)
元小稹 None None 85 75 76 =SUM(B3:D3)
岑小參 None None 77 85 65 =SUM(B4:D4)
None None None 88 34 100 =SUM(B5:D5)
None None None 90 45 100 =SUM(B6:D6)

可見平移後工作表從 6 列 5 欄變成 8 列 7 欄, 遺留的儲存格內容變成 None (空白), 原來 'B2:E6' 的成績儲存格被移到 'D4:G8', 若要移回去可以這麼做 : 

>>> sheet1.move_range('D4:G8', rows=-2, cols=-2)       # 'D4:G8' 向上向左移動兩格
>>> for row in sheet1.rows:    
    for cell in row:    
        print(cell. value, end='\t')    
    print()
    
None 國文 英文 數學 總分 None None
李小白 94 70 49 =SUM(B2:D2) None None
杜小甫 85 75 76 =SUM(B3:D3) None None
王小維 77 85 65 =SUM(B4:D4) None None
元小稹 88 34 100 =SUM(B5:D5) None None
岑小參 90 45 100 =SUM(B6:D6) None None
None None None None None None None
None None None None None None None
>>> sheet1.max_row  
8
>>> sheet1.max_column     

可見雖然成績儲存格移回去了, 但遺留下來空白儲存格仍然存在, 工作表物件被撐大了, 必須呼叫 delete_rows() 與 delete_cols() 方法刪除這些空白欄與列, 例如 :

>>> sheet1.delete_rows(7, 2)     # 刪除兩個空白列
>>> sheet1.delete_cols(6, 2)       # 刪除兩個空白欄
>>> for row in sheet1.rows:    
    for cell in row:    
        print(cell. value, end='\t')    
    print()
    
None 國文 英文 數學 總分
李小白 94 70 49 =SUM(B2:D2)
杜小甫 85 75 76 =SUM(B3:D3)
王小維 77 85 65 =SUM(B4:D4)
元小稹 88 34 100 =SUM(B5:D5)
岑小參 90 45 100 =SUM(B6:D6)

這樣工作表物件就恢復原樣了. 


(12). 呼叫 merge_cells() 與 unmerge_cells() 方法合併與取消合併儲存格 :  

有時跨幾個列或欄位的儲存格需要合併為一個儲存格以便能顯示完整的內容, 這時可用工作表物件的 merge_cells() 方法來合併, 取消合併則呼叫 unmerge_cells(), 語法如下 :

工作表物件.merge_cells(range) 
工作表物件.unmerge_cells(range) 

參數 range 為欲合併儲存格的座標範圍字串, 例如 'A1:E1' 是合併第一列從 A1 到 E1 的五個儲存格, 而 'B2:C6' 為合併 B2 至 C6 的 10 個儲存格等. 

以上面的 sheet1 工作表中的成績單為例, 如果在第一列欄名上面插入一個空白列, 然後在 A1 儲存格寫入標題後存檔, 結果如下 :

>>> sheet1.insert_rows(1)                                  # 在第一列前面插入一個空白列    
>>> sheet1['A1'].value='唐人班段考成績單'   # 寫入 A1 儲存格  
>>> sheet1.merge_cells('A1:E1')                       # 合併 A1:E1 為一格
>>> for row in sheet1.rows:    
    for cell in row:    
        print(cell. value, end='\t')    
    print()
    
唐人班段考成績單 None None None None
None 國文 英文 數學 總分
李小白 94 70 49 =SUM(B2:D2)
杜小甫 85 75 76 =SUM(B3:D3)
王小維 77 85 65 =SUM(B4:D4)
元小稹 88 34 100 =SUM(B5:D5)
岑小參 90 45 100 =SUM(B6:D6)
>>> wb.save('scores.xlsx')     # 存檔

這時用 Excel 開啟 scores.xlsx 檔 :




可見第一列標題列所占的五個儲存格已經合併為一格. 關閉檔案後呼叫 unmerge_cells() 取消合併, 例如 : 

>>> sheet1.unmerge_cells('A1:E1')    
>>> wb.save('scores.xlsx')      

再次用 Excel 開啟 scores.xlsx :




可見第一列那五個儲存格已經被取消合併了. 


(13). 呼叫工作表物件的 add_image() 於儲存格中插入圖片 :  

儲存格的內容除了文數字外也可以是圖片, 呼叫工作表物件的 add_image() 方法可在儲存格中插入圖片, 但需要先利用 openpyxl.drawing.image 中的 Image 類別將 jpg/png 等圖檔打包成 Image 物件傳入 add_image() 方法中, 所以需額外匯入 Image 類別 :

from openpyxl.drawing.image import Image 

此處使用的 say_hello.jpg 圖片如下 :




按滑鼠右鍵選 "另存圖片" 將此圖檔下載後放在與程式相同之工作目錄中, 這樣就可以建立 Image 物件實例, 然後呼叫工作表物件的 add_image() 方法將其插入上面 sheet1 的 B9 欄位, 例如 : 

>>> from openpyxl.drawing.image import Image   
>>> img=Image('say_hello.jpg')   
>>> type(img)     
<class 'openpyxl.drawing.image.Image'>    
>>> sheet1.add_image(img, 'B9') 
>>> wb.save('scores.xlsx')    

存檔後用 Excel 開啟 scores.xlsx :




可見圖片已插入 B9 儲存格中. 


(14). 利用工作表物件的 freeze_panes 凍結儲存格 :  

工作表物件的屬性 freeze_panes 可用來凍結指定儲存格上方的所有列以及左方的所有欄, 但不包括該儲存格本身, 這樣當捲動垂直卷軸時, 被凍結的儲存格會定在原位不動, 語法如下 :

工作表物件.freeze_panes='儲存格座標'     

freeze_pane 預設值為 None, 表示無儲存格被凍結.  它通常被設為 'A2' 用來凍結第一列, 因為第一列一般是做為欄名, 當資料列很長往下捲動時, 若第一列的欄名不隨之捲動有助於資料閱讀. 

此處使用台積電 2020 年的每日盤後資訊為例說明 (可在開頭下載第三個範例檔 tw2330.xlsx) :




>>> wb=xl.load_workbook('tw2330.xlsx')    
>>> sheet=wb.active    
>>> sheet.max_row   
246
>>> sheet.max_column    
5

可見此工作表內有 246 列資料, 當往下捲動時, 第一列的欄名就不見了, 這樣就不知道儲存格上的數據到底是甚麼價格了 :




如果將 freeze_panes 設為第二列第一欄的座標 'A2', 則其上方的第一列會被凍結 : 

>>> sheet.freeze_panes='A2'      # 凍結 'A2' 上方與左方所有儲存格
>>> wb.save('tw2330.xlsx')         # 存檔

這時再開啟 tw2330.xlsx 會發現第一欄底下多了一條線, 標示該欄已被凍結 : 




即使捲動到最底端, 被凍結的第一列仍然不動 : 




以上便是 OpenPyXL 的主要功能, 如果只是要用 xlsx 當作小型資料庫, 這樣應該就綽綽有餘了, 剩下的是比較次要的格式調整與圖表繪製 (大量自動化處理 xlsx 檔可能會用到), 留待下一篇再處理. 離前一篇已超過半年, 若不是參加這次線上課程的觸發, 關於 OpenPyXL 的測試學習不知要拖到何時才能完成呢. 

參考 :