标簽：Python與Excel,Xlsxwriter
在上次的基礎上，我們将使用Python xlsxwriter庫通過添加格式、迷你圖和圖表來美化我們枯燥的Excel電子表格。如果你需要從頭開始，請參閱：
>>>使用PythonXlsxwriter建立Excel電子表格
>>>使用PythonXlsxwriter建立Excel電子表格（第2部分：公式，連結與命名區域）
Excel之是以成為最受歡迎的商業軟體，其中一個原因可能是其豐富的示範功能。它有許多内置功能，可以将普通檔案轉換為講故事的工具。如果你還沒有“美化”你的Excel工作表，現在就應該考慮去做。甚至更好的是，可以編寫一個Python程式來自動使Excel更漂亮。
格式化Excel工作表
要建立格式化的單元格，涉及兩個步驟：建立一個Format對象，然後将格式寫入單元格。
建立Format對象
要将格式添加到Excel工作表，第一步是建立一個format對象，這是使用workbook.add_format方法完成的。
format = workbook.add_format() 複制
現在，這是一個“空”格式，我們需要用想要的特定格式填充它。有兩種方法可以做到。
1.使用對象方法/接口添加格式
例如，我們想要加粗一個文本單元格，使文本顔色為綠色，同時将字型更改為”微軟雅黑”。
title_format = workbook.add_format()
title_format.set_bold()
title_format.set_font_color('green')
title_format.set_font_name('微軟雅黑') 複制
2.使用格式構造函數添加格式
下面是如何使用格式構造函數實作相同的結果，我們隻需傳遞一個包含所有格式屬性的字典：
title_format = workbook.add_format({
'bold': True,
'font_color':'green',
'font_name':'微軟雅黑'
}) 複制
看起來，第二種方法更簡潔。
将格式寫入單元格
我們剛剛建立了文本格式，但還沒有在工作表上使用它。要在工作表上顯示，我們需要将其寫入單元格。
worksheet.write()方法接受另一個參數cell_format。
寫入“财富累積”作為計算表的标題。繼續前面的示例，我們将“計算”工作表命名為ws_2。
ws_2.write(0,1,"累積财富",title_format)
在電子表格中添加邊框線
在使用xlsxwriter時，可能不想在大多數情況下為邊框線而煩惱。這是可以做到的，但卻是一個乏味的過程，除非所有邊的邊框線都是相同的樣式。原因如下：
要向單元格添加任何格式，需要将格式連同資料一起寫入單元格。這意味着“更改”為不同的格式需要在将新格式傳遞到worksheet.write()方法時重新寫入資料。
明白了嗎？如果要在Excel中手動添加格式，則隻需選擇資料單元格/區域，然後按格式按鈕。相比之下，對于xlsxwriter，我們必須同時向單元格寫入資料和格式。
編寫如下表格式的代碼需要大量工作。對于某些單元格，我們需要為單元格的每一側使用不同的邊框格式。是以，其要點是，不用麻煩在xlsxwriter中繪制邊框線，因為它的80%的工作隻得到2%的結果。
格式化屬性和設定方法
下面是用于設定格式的格式屬性及其相應對象方法的清單。xlsxwriter官方文檔如下：
https://xlsxwriter.readthedocs.io/format.html#format-methods-and-format-properties
圖1
數字格式
在Excel中設定數字格式很簡單：
1.在Excel中，右鍵單擊任意單元格，選擇“設定單元格格式”。
2.在“數字”頁籤下，單擊清單中的“自定義”。
3.在右側視窗中找到不同的數字格式，粘貼到Python代碼中。
讓我們建立一些數字格式的對象，使我們的電子表格看起來更專業。
f_bold =wb.add_format({'bold':True}) #加粗
f_money = wb.add_format()
f_money.set_num_format('#,##0') #貨币格式 ->
f_thousand =wb.add_format({'num_format':'#,##0'}) #千位分隔符 -> 1,000
f_pct = wb.add_format({'num_format':'0%',
'font_color':'green',
'bg_color':'black'}) #綠色字型，黑色背景的百分比格式
f_top_border =wb.add_format({'top':3})
f_left_board =wb.add_format({'left':2}) 複制
合并單元格
合并單元格很簡單，隻需使用worksheet.merge_range()。我們可以使用（開始行、開始列、結束行、結束列）表示法或A1表示法，合并一些單元格，同時格式化它們。
f_merge = wb.add_format({'border':6,
'bold':True,
'font_size':20,
'font_color':'green',
'font_name':'微軟雅黑',
'align':'center'})
ws_2.merge_range(0,1,0,17, "财富積累",f_merge)
#ws_2.merge_range("B1:R1","财富積累",f_merge) 複制
自動篩選
如果經常使用自動篩選功能，還可以在工作表中添加自動篩選功能。同樣，（行、列）和A1樣式的符号是可互換的。
ws_2.autofilter('B10:F10')
#ws_2.autofilter(9,1,9,5) 複制
建立圖表
與建立格式的方式類似，對于圖表，也需要建立一個“空”圖表對象，然後向其添加元素。
xlsxwriter目前支援以下圖表類型：
- 面積圖
- 條形圖或柱狀圖（即直方圖）
- 折線圖
- 餅圖或圓環圖
- 散點圖
- 股價圖
- 雷達圖
為了示範，我們将建立一個折線圖和一個柱狀圖。
折線圖
可以自定義圖表元素，例如顔色、資料标簽、圖例等。這是通過将屬性字典傳遞chart.add_series()來完成的。
下列屬性是我們在示範中要使用的，有關完整的功能清單，請參閱xlsxwriter文檔。
- “categories”：這是x軸标簽
- “values”：圖形的實際值
- “name”：資料系列的名稱，也用于圖例
- “data_labels”：我們可以使用此參數标記資料點
- “y2_axis”：次坐标軸y軸
line_chart =wb.add_chart({'type':'line'})
## 收入
line_chart.add_series({'categories':"=計算!B20",
'values':"=計算！C20",
'line':{'color':'blue'},
'name':'收入',
'y2_axis':True})
## 累積财富
line_chart.add_series({'categories':"=計算!
'values':"=計算!F20",
'line':{'color':'green'},
'name':'财富',
'data_labels':{'value':True,'position':'above','num_format':'$#,##0'},
'y2_axis':True}) 複制
接着，必須選擇放置圖表的位置。以下代碼将圖表放置在單元格H3中，或者更準确地說，它将使圖表的左上角位于單元格H3中。
ws_2.insert_chart('H3', line_chart) 複制
柱狀圖
建立一個柱狀/條形圖。“柱狀”圖将顯示垂直直方圖，“條形”圖将顯示水準直方圖。這個約定與Excel描述這些圖表類型的方式完全比對。
我們還可以使用{'subtype'：'stacked'}來指定堆積柱狀圖。
column_chart =wb.add_chart({'type':'column'})
# 收入
column_chart.add_series({'categories':"=計算!B20",
'values':"=計算!C20",
'fill':{'color':'blue'},
'name':'收入'})
# 支出
column_chart.add_series({'categories':"=計算!B20",
'values':"=計算!D20",
'fill':{'color':'yellow'},
'name':'支出'})
# 可投資收入
column_chart.add_series({'categories':"=計算!B20",
'values':"=計算!E20",
'fill':{'color':'green'},
'name':'可投資收入'})
ws_2.insert_chart('H21',column_chart) 複制
使用Python組合兩個Excel圖表
一個很酷的特性是，我們可以使用Python xlsxwriter建立雙軸圖表。基本上，我們将兩個圖表組合在一起，形成一個新的圖表。當然，這兩個圖表需要有一些共同點，例如在示例中的x軸。否則，将完全不同的圖表組合在一起可能沒有多大意義。
通過組合柱形圖和折線圖，基本上建立了一個帕累托圖。下面的代碼塊很簡單：将柱形與折線圖結合起來，然後在帕累托圖上調整各個元素，例如标題、軸名稱、圖例位置等。
column_chart.combine(line_chart)
column_chart.set_title({'name':'财富積累'})
column_chart.set_x_axis({'name':'年數'})
column_chart.set_legend({'position':'bottom'})
column_chart.set_y_axis({'name':'收入标準'})
column_chart.set_y2_axis({'name':'财富标準'})
#設定圖表寬度和高度
column_chart.set_size({'width':700,'height':400})
ws_2.insert_chart('H33',column_chart) 複制
迷你圖
下面介紹如何将迷你圖（Sparkine）添加到電子表格中。現在你可能已經注意到了這個結構，無論想在工作表上添加什麼元素，通常都是通過worksheet.add_something()方法完成的。
### 添加迷你圖
ws_2.add_sparkline('E3',
{'range':'C11:C20',
'type':'column',
'style':3})
ws_2.add_sparkline('E4',
{'range':'D11:D20',
'type':'column',
'style':2}) 複制
小結
綜上，下面是生成的功能齊全的Excel電子表格的Python代碼。
import xlsxwriter
from xlsxwriter.utility importxl_rowcol_to_cell
import pathlib
wb =xlsxwriter.Workbook(r'D:\excel-formula.xlsx')
ws_1 = wb.add_worksheet('輸入')
ws_2 = wb.add_worksheet('計算')
proj_start_row = 9
proj_start_col = 1
row = 1
col = 1
variables = {
'年數': 10,
'收入': 50000,
'支出': 30000,
'收入增長率': 0.05,
'投資回報率': 0.06,
'通脹率': 0.03,
}
for i in variables.keys():
ws_1.write(row,col,i)
ws_1.write(row,col+1,variables[i])
row += 1
row = 1
f_bold =wb.add_format({'bold':True})
f_money = wb.add_format()
f_money.set_num_format('$#,##0')
f_thousand =wb.add_format({'num_format':'#,##0' })
f_pct =wb.add_format({'num_format':'0%',
'font_color': 'green',
'bg_color': 'black'})
f_merge = wb.add_format({'border':6,
'bold': True,
'font_size':20,
'font_color': 'green',
'font_name': '微軟雅黑',
'align': 'center'})
f_top_border =wb.add_format({'top':3})
f_left_board =wb.add_format({'left':2})
ws_2.merge_range("B1:R1",'财富積累', f_merge)
for i in variables.keys():
ws_2.write(row, col, i)
if i in ['收入', '支出']:
ws_2.write(row, col+1, f'=輸入!{xl_rowcol_to_cell(row,col+1)}', f_money)
elif i in ['收入增長率','投資回報率','通脹率']:
ws_2.write(row, col+1, f'=輸入!{xl_rowcol_to_cell(row,col+1)}', f_pct)
else:
ws_2.write(row, col+1, f'=輸入!{xl_rowcol_to_cell(row,col+1)}')
row += 1
calc_cols = ['年份','收入','支出','可投資收入','累積财富']
ws_2.write_row(proj_start_row,1,calc_cols)
ws_2.write_column(proj_start_row+1,proj_start_col,range(variables['年數']))
def annual_increase(ws, start_row,start_col, n, initial_value, name_range):
ws.write(start_row, start_col, initial_value,f_thousand)
for i in range(n-1):
ws.write(start_row+1+i, start_col,f'={xl_rowcol_to_cell(start_row+i,start_col)}*(1+{name_range})',f_thousand)
ws.write(start_row+n, start_col, f'=SUM({xl_rowcol_to_cell(start_row,start_col)}:{xl_rowcol_to_cell(start_row+n-1, start_col)})',f_thousand)
wb.define_name('income_increase',"='輸入'!
wb.define_name('investment_return',"='輸入'!
wb.define_name('inflation',"='輸入'!
annual_increase(ws_2, proj_start_row+1,proj_start_col+1, variables['年數'],variables['收入'], 'income_increase')
annual_increase(ws_2,proj_start_row+1, proj_start_col+2, variables['年數'],variables['支出'], 'inflation')
for i in range(variables['年數']):
ws_2.write(proj_start_row+1+i,
proj_start_col+3,
f'={xl_rowcol_to_cell(proj_start_row+1+i,proj_start_col+1)}-{xl_rowcol_to_cell(proj_start_row+1+i, proj_start_col+2)}',f_thousand)
ws_2.write(proj_start_row+1,proj_start_col+4, f'={xl_rowcol_to_cell(proj_start_row+1,proj_start_col+3)}',f_thousand)
for i in range(variables['年數']-1):
ws_2.write(proj_start_row+2+i, proj_start_col+4,
f"={xl_rowcol_to_cell(proj_start_row+1+i,proj_start_col+4)}*(1+investment_return)+{xl_rowcol_to_cell(proj_start_row+2+i,proj_start_col+3)}",f_thousand)
line_chart =wb.add_chart({'type':'line'})
line_chart.add_series({'categories':"=計算!B20",
'values':"=計算！C20",
'line':{'color':'blue'},
'name':'收入',
'y2_axis':True})
line_chart.add_series({'categories':"=計算!
'values':"=計算!F20",
'line':{'color':'green'},
'name':'财富',
'data_labels':{'value':True,'position':'above','num_format':'$#,##0'},
'y2_axis':True})
column_chart =wb.add_chart({'type':'column'})
column_chart.add_series({'categories':"=計算!B20",
'values':"=計算!C20",
'line':{'color':'blue'},
'name':'收入'})
column_chart.add_series({'categories':"=計算!B20",
'values':"=計算!D20",
'line':{'color':'yellow'},
'name':'支出'})
column_chart.add_series({'categories':"=計算!B20",
'values':"=計算!E20",
'line':{'color':'green'},
'name':'可投資收入'})
column_chart.combine(line_chart)
column_chart.set_title({'name':'财富積累'})
column_chart.set_x_axis({'name':'年數'})
column_chart.set_legend({'position':'bottom'})
column_chart.set_y_axis({'name':'收入标準'})
column_chart.set_y2_axis({'name':'财富标準'})
column_chart.set_size({'width':700,'height':400})
ws_2.insert_chart('H3',column_chart)
### 添加迷你圖
ws_2.add_sparkline('E3',
{'range':'C11:C20',
'type':'column',
'style':3})
ws_2.add_sparkline('E4',
{'range':'D11:D20',
'type':'column',
'style':2})
ws_2.autofilter('B10:F10')
wb.close() 複制
代碼運作結果如下圖2所示。
圖2