2022年6月26日 星期日

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

本篇繼續 OpenPyXL 套件的測試, 主題是統計圖表的繪製, 本系列之前的文章參考:


參考書籍如下 :
本篇測試中用到的資料來自之前 Matplotlib 測試所使用的數據, 參考 :
首先歸納整理前一篇 (三) 關於儲存格格式化的指令範例 :
  • 字型設定 :
    from openpyxl.styles import Font  
    sheet['A2'].font=Font(name='Arial', size=14, bold=True, color='0000ff')
    sheet['A3'].font=Font(name='微軟正黑體', size=12, italic=True, strike=True)
  • 列高與欄寬設定 : 
    sheet.row_dimensions[2].height=40             # 設定第 2 列列高
    sheet.column_dimensions['A'].width=60      # 設定 A 欄欄寬
  • 對齊設定 :
    from openpyxl.styles import Alignment 
    sheet['A2'].alignment=Alignment(vertical='top', horizontal='center')
    sheet['A3'].alignment=Alignment(vertical='center', horizontal='right') 
    vertical 參數 : 'top', 'center', 'bottom'
    horizontal 參數 : 'left', 'center', 'right'
  • 框線設定 :
    from openpyxl.styles import Border
    from openpyxl.styles import Side
    side=Side(color='FF0000', style='thick')
    border=Border(left=side, right=side, top=side, bottom=side)
    sheet['B3'].border=border 
    style 參數 : 'hair', 'thin', 'medium', 'double', 'thick', 'dotted', 'dashed', 'mediumDashed', 'dashdot', 'dashDotDot', 'slantDashDot', 'mediumDashDot', 'mediumDashDotDot'
  • 背景色設定 : 
    from openpyxl.styles import PatternFill
    sheet['D3'].fill=PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')  # 
    fill_type 參數 : 'solid', 'gray0625', 'gray125', 'lightGray', 'mediumGray', 'darkGray', 'lightGrid', 'darkGrid', 'lightHorizontal', 'darkHorizontal', 'lightVertical', darkVertical', 'lightUp', 'darkUp', 'lightDown', 'darkDown', 'lightTrellis', 'darkTrellis'
  • 格式化條件 (背景色) :
    from openpyxl.formatting.rule import CellIsRule
    from openpyxl.styles import PatternFill
    fill=PatternFill(start_color='FFFF00',  end_color='FFFF00', fill_type='solid') 
    rule=CellIsRule(operator='lessThan', formula=[60], stopIfTrue=True, fill=fill)  
    sheet.conditional_formatting.add('B3:D7', rule)   

8. 繪製統計圖表 :  

OpenPyXL 套件的 openpyxl.chart 子模組中提供了可用來繪製統計圖表的類別 : 
  • BarChart (長條圖)
  • LineChart (折線圖)
  • BarChart (圓餅圖)
  • AreaChart (區域圖)
  • ScaterChart (散佈圖)
  • BubbleChart (泡泡圖)
  • RadarChart (雷達圖) 
  • StockChart (股票圖)
  • BarChart3D (3D 直條圖)
  • PieChart3D (3D 圓餅圖)
  • AreaChart3D (3D 區域圖)
使用 OpenPyXL 繪製圖表除了要匯入這些類別外, 還需要匯入 openpyxl.chart.Reference 類別, 此類別用來將工作表的儲存格資料與軸的標籤資料引入圖表物件中, 呼叫此類別之建構子 Reference() 即可建立 Reference 物件 :  

Reference(worksheet, min_col, maxcol, min_row, max_row)

參數 worksheet 為工作表物件, min_col 為儲存格範圍最左欄索引; max_col 為最右欄索引; min_row 為最上列索引; max_row 為最下列索引, 這些索引均為整數. 由於工作表第一列通常是欄位名稱, max_row 可以傳入工作表物件的 max_row 屬性值, 這樣就不需要去查最後一列索引了. 

繪製圖表程序如下 :
  1. 建立圖表物件 (設定圖表標題與座標軸標題)
  2. 建立資料的 Reference 物件 (指定工作表中的儲存格範圍)
  3. 建立軸或標籤的 Reference 物件 (指定工作表中的儲存格範圍)
  4. 呼叫圖表物件的 add_data() 方法加入資料的 Reference 物件
  5. 呼叫圖表物件的 set_categories() 方法加入軸或標籤的 Reference 物件
  6. 呼叫工作表物件的 add_chart() 方法將圖表物件插入指定儲存格中
其中步驟 1, 2, 3 先後順序不重要. 

官網教學文件參考 :



(1). 繪製長條圖 :  

長條圖用來展示分類或分組資料的大小差異, 結構上有垂直與水平兩種類型, BarChart 類別預設是垂直長條圖. 官網範例參考 :


繪製長條圖須匯入 BarChart 與 Refernece 兩個類別 : 

>>> import openpyxl as xl   
>>> from openpyxl.chart import BarChart, Reference   
>>> Reference    
<class 'openpyxl.chart.reference.Reference'>   
>>> BarChart    
<class 'openpyxl.chart.bar_chart.BarChart'>   

呼叫 BarChart() 建立一個 BarChart 圖表物件 : 

>>> chart=BarChart()      
>>> type(chart)   
<class 'openpyxl.chart.bar_chart.BarChart'>    

利用 eval() 與 dir() 來檢視 BarChart 物件的公開成員 : 

>>> members=dir(chart)     
>>> for mbr in members:   
    obj=eval('chart.' + mbr)       
    if not mbr.startswith('_'):     
        print(mbr, type(obj))   
        
add_data <class 'method'>
anchor <class 'str'>
append <class 'method'>
axId <class 'list'>
barDir <class 'str'>
dLbls <class 'NoneType'>
dataLabels <class 'NoneType'>
display_blanks <class 'str'>
extLst <class 'openpyxl.descriptors.base.Typed'>
from_tree <class 'method'>
gapWidth <class 'float'>
graphical_properties <class 'NoneType'>
grouping <class 'str'>
height <class 'float'>
idx_base <class 'int'>
layout <class 'NoneType'>
legend <class 'openpyxl.chart.legend.Legend'>
mime_type <class 'str'>
namespace <class 'NoneType'>
overlap <class 'NoneType'>
path <class 'str'>
pivotFormats <class 'tuple'>
pivotSource <class 'NoneType'>
plot_area <class 'openpyxl.chart.plotarea.PlotArea'>
roundedCorners <class 'NoneType'>
ser <class 'list'>
serLines <class 'NoneType'>
series <class 'list'>
set_categories <class 'method'>
style <class 'NoneType'>
tagname <class 'str'>
title <class 'NoneType'>
to_tree <class 'method'>
type <class 'str'>
varyColors <class 'NoneType'>
visible_cells_only <class 'bool'>
width <class 'int'>
x_axis <class 'openpyxl.chart.axis.TextAxis'>
y_axis <class 'openpyxl.chart.axis.NumericAxis'>

常用的屬性如下表 : 


 BarChart 物件常用屬性 說明
 title 圖表之標題
 x_axis X 軸物件 (TextAxis), 可用其 title 屬性存取 X 軸標題文字
 y_axis Y 軸物件 (NumberAxis), 可用其 title 屬性存取 Y 軸標題文字
 type 圖表類型 : 'col'=垂直長條圖 (預設), 'bar'=水平長條圖
 style 樣式 (整數, 預設 None)
 width 寬度 (整數, 預設 15)
 height 高度 (整數, 預設 7.5)


常用的方法如下表 :


 BarChart 物件常用方法 說明
 append(series) 將一組資料 (Series 物件) 加入圖表中 
 add_data(y) 將 Y 軸資料 (Reference 物件) 加入圖表物件中
 add_categories(x) 將 X 軸類別 (Reference 物件) 加入圖表物件中


注意, add_data() 還有一個備選參數 titles_from_data, 若設為 True 會將第一列視為圖例 (legend) 的來源, 這時在建立 Y 軸資料的 Reference 物件時就要納入第一列. 

首先來繪製單組 (單變數) 的長條圖, 下面以台灣 2020 年總統大選得票數統計資料為例來繪製長條圖, 先建立一個空白將資料寫入工作表 : 

>>> wb=xl.Workbook()     
>>> ws=wb.active   
>>> ws     
<Worksheet "Sheet">   
>>> votes=[['候選人', '得票數'],        
       ['宋楚瑜', 608590],    
       ['韓國瑜', 5522119],       
       ['蔡英文', 8170231]]    
>>> for row in votes:           # 將串列逐列寫入工作表
    ws.append(row)    
    
>>> for row in ws.rows:      # 檢視工作表內容       
    for cell in row:           
        print(cell. value, end='\t')             
    print()     
   
候選人 得票數
宋楚瑜 608590
韓國瑜 5522119
蔡英文 8170231

接下來就可以根據這兩個範圍 X, Y 軸的 Reference 物件 : 

>>> x=Reference(ws, min_col=1, max_col=1, min_row=2, max_row=ws.max_row) 
>>> y=Reference(ws, min_col=2, max_col=2, min_row=1, max_row=ws.max_row) 
>>> type(x)   
<class 'openpyxl.chart.reference.Reference'>
>>> type(y)    
<class 'openpyxl.chart.reference.Reference'>

此處因 X 軸分類都在第一欄, 故 min_col 與 max_col 均為 1, 分類內容從第二列至最後一列. Y 軸資料都在第二欄, 故 min_col 與 max_col 均為 2. 要注意的是, 雖然資料內容是從第二列至最後列, 但因為需要用第一列的欄名當圖例 (legend), 所以資料的 min_row 要從第一列開始, 這樣才會自動將欄名設為圖例. 

接著便可將 X 軸與 Y 軸的 Reference 物件分別呼叫圖表物件的 set_categories() 與 add_data() 加入圖表中, 並且設定圖表標題與兩座標軸之標題 : 

>>> chart.add_data(y, titles_from_data=True)      # 將 Y 軸資料加入圖表
>>> chart.set_categories(x)                                       # 將 X 軸類別加入圖表
>>> chart.title='2020 總統大選得票數'     # 圖表標題
>>> chart.x_axis.title='候選人'                  # X 軸標題
>>> chart.y_axis.title='得票數'                  # Y 軸標題

注意, 此處 add_data() 的參數 titles_from_data 設為 True 表示要以第一列的欄名當圖例 (所以在上面在建立 Y 軸資料的 Reference 物件時 min_row 要設為 1). 最後將圖表物件插入到儲存格 : 
  
>>> ws.add_chart(chart, 'C1')   
>>> wb.save('2020_presidential.xlsx')    

用 Excel 開啟檔案結果如下 : 




茲將以上繪圖程式碼整理如下 : 

範例 1 : 2020 總統大選得票數垂直長條圖 [原始碼]

import openpyxl as xl
from openpyxl.chart import BarChart, Reference
wb=xl.Workbook()
ws=wb.active
votes=[['候選人', '得票數'],        
       ['宋楚瑜', 608590],    
       ['韓國瑜', 5522119],       
       ['蔡英文', 8170231]]
for row in votes:    
    ws.append(row)
chart=BarChart()
chart.title='2020 總統大選得票數'
chart.x_axis.title='候選人'
chart.y_axis.title='得票數'
x=Reference(ws, min_col=1, max_col=1, min_row=2, max_row=ws.max_row)
y=Reference(ws, min_col=2, max_col=2, min_row=1, max_row=ws.max_row)  
chart.add_data(y, titles_from_data=True)
chart.set_categories(x)
ws.add_chart(chart, 'C1')
wb.save('2020_presidential_1.xlsx')


如果要繪製垂直長條圖的話, 只要將圖表物件的 type 屬性設為 'bar' 即可 :

chart.type='bar'    

例如 : 

範例 2 : 2020 總統大選得票數水平長條圖 [原始碼]

import openpyxl as xl
from openpyxl.chart import BarChart, Reference
wb=xl.Workbook()
ws=wb.active
votes=[['候選人', '得票數'],        
       ['宋楚瑜', 608590],    
       ['韓國瑜', 5522119],       
       ['蔡英文', 8170231]]
for row in votes:    
    ws.append(row)
chart=BarChart()
chart.title='2020 總統大選得票數'
chart.x_axis.title='候選人'
chart.y_axis.title='得票數'
chart.type='bar'
x=Reference(ws, min_col=1, max_col=1, min_row=2, max_row=ws.max_row)
y=Reference(ws, min_col=2, max_col=2, min_row=1, max_row=ws.max_row)  
chart.add_data(y, titles_from_data=True)
chart.set_categories(x)
ws.add_chart(chart, 'C1')
wb.save('2020_presidential_2.xlsx')

結果如下 : 




繪製多組資料的長條圖作法相同, 差別只是 Y 軸資料是一欄以上而已, 

範例 3 : 兩組資料之長條圖-ETF 殖利率比較  [原始碼]

import openpyxl as xl
from openpyxl.chart import BarChart, Reference
wb=xl.Workbook()
ws=wb.active
dividend=[['年份', '0056殖利率', '0050殖利率'],        
          ['2015', 4.33, 3.01],
          ['2016', 5.67, 1.28],
          ['2017', 3.78, 6.1],
          ['2018', 5.62, 7.1],
          ['2019', 6.7, 7.22]]  
for row in dividend:    
    ws.append(row)
chart=BarChart()
chart.title='0056 vs 0050 殖利率'
chart.x_axis.title='ETF'
chart.y_axis.title='殖利率'
x=Reference(ws, min_col=1, max_col=1, min_row=2, max_row=ws.max_row)
y=Reference(ws, min_col=2, max_col=3, min_row=1, max_row=ws.max_row)  
chart.add_data(y, titles_from_data=True)
chart.set_categories(x)
ws.add_chart(chart, 'D1')
wb.save('ETF_dividend_yield.xlsx')

注意此處 Y 軸資料位於 B, C 兩欄, 故 max_col 設為 3, 對於多組資料 max_col 可以設為 ws.max_col.

結果如下 :




可見由於在 add_data() 中有傳入 titles_from_data=True, 且建立 Y 軸資料的 Reference 物件時 min_row 設為 1, 將第一列的欄位名稱納入資料中, 繪製圖表時就會自動以欄名作為圖例, 這使所繪製的多組資料很有可讀性. 

只要將上面範例中匯入的 BarChart 類別改成 BarChart3D 就能繪製 3D 長條圖了, 例如 :

範例 4 : 兩組資料之 3D 長條圖-ETF 殖利率比較  [原始碼]

import openpyxl as xl
from openpyxl.chart import BarChart3D, Reference
wb=xl.Workbook()
ws=wb.active
dividend=[['年份', '0056殖利率', '0050殖利率'],        
          ['2015', 4.33, 3.01],
          ['2016', 5.67, 1.28],
          ['2017', 3.78, 6.1],
          ['2018', 5.62, 7.1],
          ['2019', 6.7, 7.22]]  
for row in dividend:    
    ws.append(row)
chart=BarChart3D()   
chart.title='0056 vs 0050 殖利率'
chart.x_axis.title='ETF'
chart.y_axis.title='殖利率'
x=Reference(ws, min_col=1, max_col=1, min_row=2, max_row=ws.max_row)
y=Reference(ws, min_col=2, max_col=3, min_row=1, max_row=ws.max_row)  
chart.add_data(y, titles_from_data=True)
chart.set_categories(x)
ws.add_chart(chart, 'D1')
wb.save('ETF_dividend_yield_3D.xlsx')

結果如下 : 




(2). 繪製折線圖 :  

折線圖是將各個資料點以直線連接的統計圖, 官網範例參考 :


以上面的 0056 vs 0050 殖利率為例, 只要匯入 LineChart 類別替換即可繪製折線圖 : 

範例 5 : 兩組資料之折線圖-ETF 殖利率比較  [原始碼]

import openpyxl as xl
from openpyxl.chart import LineChart, Reference
wb=xl.Workbook()
ws=wb.active
dividend=[['年份', '0056殖利率', '0050殖利率'],        
          ['2015', 4.33, 3.01],
          ['2016', 5.67, 1.28],
          ['2017', 3.78, 6.1],
          ['2018', 5.62, 7.1],
          ['2019', 6.7, 7.22]]  
for row in dividend:    
    ws.append(row)
chart=LineChart()
chart.title='0056 vs 0050 殖利率'
chart.x_axis.title='ETF'
chart.y_axis.title='殖利率'
x=Reference(ws, min_col=1, max_col=1, min_row=2, max_row=ws.max_row)
y=Reference(ws, min_col=2, max_col=3, min_row=1, max_row=ws.max_row)  
chart.add_data(y, titles_from_data=True)
chart.set_categories(x)
ws.add_chart(chart, 'D1')
wb.save('ETF_dividend_yield_linechart.xlsx')

結果如下 : 




(3). 繪製圓餅圖 :  

圓餅圖用來顯示一個數值資料相對於全部資料的大小相對關係, 只能用於單一變數 (一組序列資料) 繪圖. 官網範例參考 :


繪製圓餅圖須匯入 PieChart 與 Reference 類別, 其用法與上面直條圖/折線圖類似, 但 PieChart 物件沒有 x_axis 與 y_axis 屬性, 例如 : 

範例 6 : 資產配置圓餅圖  [原始碼]

import openpyxl as xl
from openpyxl.chart import PieChart, Reference
wb=xl.Workbook()
ws=wb.active
dividend=[['資產項目', '配置比率'],        
          ['股票', 60],
          ['基金', 20],
          ['現金', 10],
          ['黃金', 5],
          ['不動產', 5]]  
for row in dividend:    
    ws.append(row)
chart=PieChart()    
chart.title='資產配置比例'
x=Reference(ws, min_col=1, max_col=1, min_row=2, max_row=ws.max_row)
y=Reference(ws, min_col=2, max_col=2, min_row=1, max_row=ws.max_row) 
chart.add_data(y, titles_from_data=True)
chart.set_categories(x)
ws.add_chart(chart, 'D1')
wb.save('asset_allocation.xlsx')

結果如下 :




只要將上面範例中的 PieChart 改成 PieChart3D 就可以繪製 3D 圓餅圖了, 例如 :

範例 7 : 資產配置 3D 圓餅圖  [原始碼]

import openpyxl as xl
from openpyxl.chart import PieChart3D, Reference
wb=xl.Workbook()
ws=wb.active
dividend=[['資產項目', '配置比率'],        
          ['股票', 60],
          ['基金', 20],
          ['現金', 10],
          ['黃金', 5],
          ['不動產', 5]]  
for row in dividend:    
    ws.append(row)
chart=PieChart3D()    
chart.title='資產配置比例'
x=Reference(ws, min_col=1, max_col=1, min_row=2, max_row=ws.max_row)
y=Reference(ws, min_col=2, max_col=2, min_row=1, max_row=ws.max_row) 
chart.add_data(y, titles_from_data=True)
chart.set_categories(x)
ws.add_chart(chart, 'C1')
wb.save('asset_allocation_3D.xlsx')

結果如下 : 




如果要凸顯圓餅途中的某個分類之扇形, 讓它從圓餅中分離 (explosion) 開來, 這需要從 openpyxl.series 子模組中匯入 DataPoint 類別來建立一個 DataPoint 物件 : 

>>> from openpyxl.series import DataPoint
>>> dir(DataPoint)    
['__add__', '__attrs__', '__class__', '__copy__', '__delattr__', '__dict__', '__dir__', '__doc__', '__elements__', '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__iter__', '__le__', '__lt__', '__module__', '__namespaced__', '__ne__', '__nested__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', '__weakref__', 'bubble3D', 'explosion', 'extLst', 'from_tree', 'graphicalProperties', 'idx', 'idx_base', 'invertIfNegative', 'marker', 'namespace', 'pictureOptions', 'spPr', 'tagname', 'to_tree']

此類別的 idx 屬性用來指定要分離的類別索引 (注意, 這是 0 起始索引); 而 explosion 屬性則用來指定分離的程度 (0~100), 例如 :

>>> data_point=DataPoint(idx=1, explosion=20)　# idx=1 為第 2 個類別要分離　　
>>> type(data_point)　　　　
<class 'openpyxl.chart.marker.DataPoint'>　　　

此處 idx=1 表示是要分離第 2 個類別, 注意 idx 是 0 起始, 以上面資產配置比例為例, idx=1 是基金, 而 idx=0 是股票. 

然後將圖表中的 Series[0] 物件之 data_points 屬性指定為此 DataPoint 串列即可 : 

>>> type(chart.series[0])   
<class 'openpyxl.chart.series.Series'>      
>>> dir(chart.series[0])   
['__add__', '__attrs__', '__class__', '__copy__', '__delattr__', '__dict__', '__dir__', '__doc__', '__elements__', '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__iter__', '__le__', '__lt__', '__module__', '__namespaced__', '__ne__', '__nested__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', '__weakref__', 'bubble3D', 'bubbleSize', 'cat', 'dLbls', 'dPt', 'data_points', 'errBars', 'explosion', 'extLst', 'from_tree', 'graphicalProperties', 'identifiers', 'idx', 'idx_base', 'invertIfNegative', 'labels', 'marker', 'namespace', 'order', 'pictureOptions', 'shape', 'smooth', 'spPr', 'tagname', 'title', 'to_tree', 'trendline', 'tx', 'val', 'xVal', 'yVal', 'zVal']
>>> chart.series[0].data_points=[data_point]   

完整的程式碼如下 : 

範例 8 : 資產配置圓餅圖 : 分離凸顯某類別  [原始碼]

import openpyxl as xl
from openpyxl.chart import PieChart, Reference
from openpyxl.chart.series import DataPoint   
wb=xl.Workbook()
ws=wb.active
dividend=[['資產項目', '配置比率'],        
          ['股票', 60],
          ['基金', 20],
          ['現金', 10],
          ['黃金', 5],
          ['不動產', 5]]  
for row in dividend:    
    ws.append(row)
chart=PieChart()
chart.title='資產配置比例'
x=Reference(ws, min_col=1, max_col=1, min_row=2, max_row=ws.max_row)
y=Reference(ws, min_col=2, max_col=2, min_row=1, max_row=ws.max_row) 
chart.add_data(y, titles_from_data=True)
chart.set_categories(x)
data_point=DataPoint(idx=1, explosion=20)     
chart.series[0].data_points=[data_point]    
ws.add_chart(chart, 'C1')
wb.save('asset_allocation_with_explosion.xlsx')

結果如下 : 




將上面範例中的 PieChart 改成 PieChart3D 就可以繪製有凸顯分離效果的 3D 圓餅圖了, 例如 :

範例 9 : 資產配置 3D 圓餅圖 : 分離凸顯某類別  [原始碼]

import openpyxl as xl
from openpyxl.chart import PieChart3D, Reference
from openpyxl.chart.series import DataPoint   
wb=xl.Workbook()
ws=wb.active
dividend=[['資產項目', '配置比率'],        
          ['股票', 60],
          ['基金', 20],
          ['現金', 10],
          ['黃金', 5],
          ['不動產', 5]]  
for row in dividend:    
    ws.append(row)
chart=PieChart3D()   
chart.title='資產配置比例'
x=Reference(ws, min_col=1, max_col=1, min_row=2, max_row=ws.max_row)
y=Reference(ws, min_col=2, max_col=2, min_row=1, max_row=ws.max_row) 
chart.add_data(y, titles_from_data=True)
chart.set_categories(x)
data_point=DataPoint(idx=1, explosion=20)
chart.series[0].data_points=[data_point]      
ws.add_chart(chart, 'C1')
wb.save('asset_allocation_with_explosion_3D.xlsx')

結果如下 : 





於

沒有留言:

張貼留言