excelperfect 标签: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./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') #货币格式 ->$1,000 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('$B$10:$F$10') #ws_2.autofilter(9,1,9,5) 创建图表 与创建格式的方式类似,对于图表,也需要创建一个“空”图表对象,然后向其添加元素。 xlsxwriter当前支持以下图表类型:
为了演示,我们将创建一个折线图和一个柱状图。 折线图 可以自定义图表元素,例如颜色、数据标签、图例等。这是通过将属性字典传递chart.add_series()来完成的。 下列属性是我们在演示中要使用的,有关完整的功能列表,请参阅xlsxwriter文档。
line_chart =wb.add_chart({'type':'line'}) ## 收入 line_chart.add_series({'categories':'=计算!$B$11:$B$20', 'values':'=计算!$C$11:$C$20', 'line':{'color':'blue'}, 'name':'收入', 'y2_axis':True}) ## 累积财富 line_chart.add_series({'categories':'=计算!$B11$B20', 'values':'=计算!$F$11:$F$20', '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':'=计算!$B$11:$B$20', 'values':'=计算!$C$11:$C$20', 'fill':{'color':'blue'}, 'name':'收入'}) # 支出 column_chart.add_series({'categories':'=计算!$B$11:$B$20', 'values':'=计算!$D$11:$D$20', 'fill':{'color':'yellow'}, 'name':'支出'}) # 可投资收入 column_chart.add_series({'categories':'=计算!$B$11:$B$20', 'values':'=计算!$E$11:$E$20', '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':'$C$11:$C$20', 'type':'column', 'style':3}) ws_2.add_sparkline('E4', {'range':'$D$11:$D$20', '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','='输入'!$C$5') wb.define_name('investment_return','='输入'!$C$6') wb.define_name('inflation','='输入'!$C$7') 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':'=计算!$B$11:$B$20', 'values':'=计算!$C$11:$C$20', 'line':{'color':'blue'}, 'name':'收入', 'y2_axis':True}) line_chart.add_series({'categories':'=计算!$B11$B20', 'values':'=计算!$F$11:$F$20', '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':'=计算!$B$11:$B$20', 'values':'=计算!$C$11:$C$20', 'line':{'color':'blue'}, 'name':'收入'}) column_chart.add_series({'categories':'=计算!$B$11:$B$20', 'values':'=计算!$D$11:$D$20', 'line':{'color':'yellow'}, 'name':'支出'}) column_chart.add_series({'categories':'=计算!$B$11:$B$20', 'values':'=计算!$E$11:$E$20', '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':'$C$11:$C$20', 'type':'column', 'style':3}) ws_2.add_sparkline('E4', {'range':'$D$11:$D$20', 'type':'column', 'style':2}) ws_2.autofilter('$B$10:$F$10') wb.close() 代码运行结果如下图2所示。 图2 注:本文学习整理自pythoninoffice.com。 欢迎到知识星球:完美Excel社群,进行技术交流和提问,获取更多电子资料,并通过社群加入专门的微信讨论群,更方便交流。 |
|
来自: hercules028 > 《Python and AI》