使用Python Xlsxwriter创建Excel电子表格（第3部分：格式，迷你图与图表）
使用Python Xlsxwriter创建Excel电子表格（第3部分：格式，迷你图与图表）
标签：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
数字格式
在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所示。
