定制业务质量报表详解

在日常运维工作中，会涉及大量不同来源的数据，比如每天的服务器性能数据、平台监控数据、自定义业务上报数据等，需要根据不同的时段，周期性地输出数据报表，以方便管理员更加清晰、及时地了解业务的运营情况。在业务监控过程中，也需要更加直观地展示报表，以便快速定位问题。

一. 数据报表之Excel操作模块

利用Python操作Excel的模块为XlsxWriter，可以操作多个工作表的文字、数字、公式、图表等。

示例地址：https://xlsxwriter.readthedocs.io/working_with_charts.html。

XlsxWriter模块具有以下功能：

  • 100%兼容的Excel XLSX文件，支持Excel 2003、Excel 2007等版本；
  • 支持所有Excel单元格数据格式；
  • 单元格合并、批注、自动筛选、丰富多格式字符串等；
  • 支持工作表PNG、JPEG图像、丰富多格式字符串等；
  • 内存优化模式支持写入大文件；

安装XlsxWriter：

  1. [root@localhost ~]# pip3 install XlsxWriter

先看一个简单案例

  1. #!/usr/bin/python3
  2. import xlsxwriter
  4. workbook = xlsxwriter.Workbook('demo1.xlsx') #创建一个Excel文件
  5. worksheet = workbook.add_worksheet()   #创建一个工作表对象
  7. worksheet.set_column('A:A', 20) #设定第一列（A）宽带为20像素
  8. bold = workbook.add_format({'bold': True})  #定义一个加粗的格式对象
  10. worksheet.write('A1', 'Hello')  #A1单元格写入'hello'
  11. worksheet.write('A2', 'World', bold) #A2单元格写入'World'并引用加粗格式对象bold
  12. worksheet.write('B2', u'中文测试', bold)
  14. #用行列表示法写入数字'32'和'32.5'
  15. #行列表示法的单元格下标以0作为起始值，‘3,0’等价于'A3'
  16. worksheet.write(2,0,32)
  17. worksheet.write(3,0,35.5)
  18. worksheet.write(4,0, '=SUM(A3:A4)') #求A3:A4的和，结果写入'4,0'，即A4
  20. #在B5单元格插入图片
  21. worksheet.insert_image('B5', 'img/python-logo.jpg')
  22. workbook.close()  #关闭Excel文件

程序生成的demo1.xlsx文档截图如下：

3. 定制业务质量报表详解 - 图1

再看一下官方的案例

  1. #!/usr/bin/python3
  2. import xlsxwriter
  4. workbook = xlsxwriter.Workbook('chart_line.xlsx')
  5. worksheet = workbook.add_worksheet()
  7. # 添加要打印的工作表数据
  8. data = [10, 40, 50, 20, 10, 50]
  9. worksheet.write_column('A1', data)
  11. # 创建新的图表对象
  12. chart = workbook.add_chart({'type': 'line'})
  14. # 向图表中添加序列
  15. chart.add_series({'values': '=Sheet1!$A$1:$A$6'})
  17. # 将图表插入工作表
  18. worksheet.insert_chart('C1', chart)
  20. workbook.close()

程序结果：

3. 定制业务质量报表详解 - 图2

二. 模块常用方法说明

2.1 Workbook 类

Workbook 类定义：Workbook(filename[,options])，该类实现创建一个XlsxWriter的Workbook对象。Workbook类代表整个电子表格文件，并且存储在磁盘上。参数filename(String类型)为创建的Excel文件存储路径；参数options(Dict类型)为可选的Workbook参数，一般作为初始化工作表内容格式，例如值为{‘strings_to_numbers’: True}表示使用worksheet.write()方法时激活字符串转换数字。

  • add_worksheet([sheetname]) 方法，作用是添加一个新的工作表，参数sheetname(String类型)为可选的工作表名称，默认为Sheet1。例如，下面的代码对应的效果图如下：
    3. 定制业务质量报表详解 - 图3 

    1. worksheet1 = workbook.add_worksheet()    #Sheet1
    2. worksheet2 = workbook.add_worksheet('Foglio2')    #Foglio2
    3. worksheet3 = workbook.add_worksheet('Data')    #Data
    4. worksheet4 = workbook.add_worksheet()    #Sheet4

  • add_format([properties]) 方法，作用是在工作表中创建一个新的格式对象来格式化单元格。参数 properties(dict类型)为指定一个格式属性的字典，例如设置一个加粗的格式对象，workbook.add_format({‘bold’: True})。通过Format methods(格式化方法)也可以实现格式的设置，等价的设置加粗格式代码如下：
    更多的格式化方法：https://xlsxwriter.readthedocs.io/format.html 

    1. bold = workbook.add_format()
    2. bold.set_bold()
    1. cell_format = workbook.add_format({'bold': True, 'font_color': 'red'})
    2. #也可以使用
    3. cell_format = workbook.add_format()
    4. cell_format.set_bold()
    5. cell_format.set_font_color('red')

  • add_chart(options) 方法，作用是在工作表中创建一个图表对象，内部是通过insert_chart()方法来实现，参数options(dict类型)为图表指定一个字典属性，例如设置一个线条类型的图表对象，代码为： 

    1. chart = workbook.add_chart({'type': 'line'})

  • close() 方法，作用是关闭工作表文件，如workbook.close()。

2.2 Worksheet类

Worksheet类代表了一个Excel工作表，是XlsxWriter模块操作Excel内容最核心的一个类，例如将数据写入单元格或工作表格式布局等。Worksheet对象不能直接实例化，而是通过Workbook对象调用add_worksheet()方法来创建。Worksheet类提供了非常丰富的操作Excel内容的方法，其中几个常用的方法如下：

  • write(row, col, args)
    方法，作用是写普通数据到工作表的单元格，参数row为行坐标，col为列坐标，坐标索引起始值为0；    args无名字参数为数据内容，可以为数字、公式、字符串或格式对象。为了简化不同数据类型的写入过程，write方法以及作为其他更加具体数据类型方法的别名，包括：
    下面通过具体的示例来观察别名write方法与数据类型方法的对应关系
    ```

    write_string()写入字符串类型数据

    worksheet.wirte_string(0, 0, ‘Your text here’)

write_number()写入数字类型数据

worksheet.write_number(‘A2’, 2.3451)

write_blank() 写入空类型数据

worksheet.write_blank(‘A2’, None)

write_formula()写入公式类型数据

worksheet.write_formula(2, 0, ‘=SUM(B1:B5)’)

write_datetime()写入日期类型数据

worksheet.write_datetime(7,0, datetime.datetime.strptime(‘2020-05-02’, ‘%Y-%m-%d’), workbook.add_format({‘num_format’: ‘yyyy-mm-dd’}))

write_boolean()写入逻辑类型数据

worksheet.write_boolean(0, 0, True)

write_url() 写入超链接类型数据

worksheet.write_url(‘A1’, ‘ftp://www.python.org’)

  1. ```
  2. worksheet.write(0,0,'Hello')   # write_string()
  3. worksheet.write(1,0, 'World')  # write_string()
  4. worksheet.write(2,0,2)         # write_number()
  5. worksheet.write(3,0,3.00001)   # write_number()
  6. worksheet.write(4,0, '=SIN(PI()/4)')  #write_formula()
  7. worksheet.write(5,0, '')       # write_blank()
  8. worksheet.write(6,0, None)     # write_blank()

  • set_row(row,height,cell_format,options)
    方法，作用是设置行单元格的属性。参数row(int类型)指定行位置，起始下标为0；参数heigh(float类型)设置行高，单位像素；参数cell_format(format类型)指定格式对象；参数options(dict类型)设置行hidden(隐藏)、level(组合分级)、collapsed(折叠)。操作示例如下： 

    1. worksheet.write('A1', 'Hello') #在A1单元格写入'Hello'字符串
    2. cell_format = workbook.add_format({'bold': True})  #定义一个加粗的格式对象
    3. worksheet.set_row(0 , 40, cell_format) #设置第1行单元格高度为40像素，且引用加粗格式对象
    4. worksheet.set_row(1, None, None, {'hidden': True}) #隐藏第2行单元格

  • set_column(first_col,last_col,width,cell_format,options)
    方法，作用为设置一列或多列单元格属性。参数first_col(int类型)指定开始列位置，起始下标为0;参数last_col(int类型)指定结束列位置，起始下标为0，可以设置成与first_col一样；参数width(float类型)设置列宽；参数cell_format(Format类型)指定格式对象；参数options(dict类型)设置行hidden（隐藏）、level(组合分级)、collapsed(折叠)。操作示例如下：
    ``` worksheet.write(‘A1’, ‘Hello’) #在A1单元格写入’Hello’字符串 worksheet.write(‘B1’, ‘World’) #在B1单元格写入’World’字符串 cell_format = workbook.add_format({‘bold’: True}) #定义一个加粗的格式对象

设置0到1即（A到B)列单元格宽带为10像素，且引用加粗格式对象

worksheet.set_column(0,1,10,cell_format)

设置C到D列单元格宽带为20像素

worksheet.set_column(‘C:D’, 20)

隐藏E到G列单元格

worksheet.set_column(‘E:G’, None, None, {‘hidden’: 1})

  2. -  insert_image(row,col,image[,options])<br />方法，作用是插入图片到指定单元格，支持PNG、JPEG、BMP等图片格式。参数row为行坐标，col为列坐标，坐标索引起始值为0；参数image（string类型）为图片路径；参数options（dict类型）为可选参数，作用是指定图片的位置、比例、链接URL等信息。操作示例如下：

在B5单元格插入python-logo.png图片，图片超级链接为http://python.org

worksheet.insert_image(‘B5’, ‘img/python-logo.png’, {‘url’:’http://python.org'})

  2. <a name="c6ef736a"></a>
  3. ### 2.3 Chart类
  5. Chart类实现在XlsxWriter模块中图表组件的基类，支持的图表类型包括面积、条形图、柱形图、折线图、饼图、散点图、股票和雷达等，一个图表对象是通过Workbook（工作薄）的add_chart方法创建，通过{type,‘图表类型’}字典参数指定图表的类型，语句如下：

chart = workbook.add_chart({type, ‘column’}) #创建一个column(柱形)图表

  2. 更多图表类型说明：
  4. - area: 创建一个面积样式的图表
  5. - bar：创建一个条形样式的图表
  6. - column：创建一个柱形样式的图表
  7. - line：创建一个线条样式的图表
  8. - pie：创建一个饼图样式的图表
  9. - scatter：创建一个散点样式的图表
  10. - stock：创建一个股票样式的图表
  11. - radar：创建一个雷达样式的图表
  13. 然后再通过Worksheet(工作表)的insert_chart()方法插入到指定位置，语句如下：

worksheet.insert_chart(‘A7’, chart) #在A7单元格插入图表

  2. 下面介绍chart类的几个常用方法：
  4. -  chart.add_series(options)方法，作用为添加一个数据系列到图表，参数options(dict类型)设置图表系列选项的字典，操作示例如下： <br />add_series方法最常用的三个选项为categories，values，line，其中categories作用是设置图表类别标签范围；values为设置图表数据范围；line为设置图表线条属性，包括颜色、宽度等。

chart.add_series({ ‘categories’: ‘=Sheet1!$A$1:$A$5’, ‘values’: ‘=Sheet1!$B$1:$B$5’, ‘line’: {‘color’: ‘red’}, })

  2. -  其他常用方法及示例 
  3.    -  set_x_axis(options) 方法，设置图表X轴选项，示例代码如下

chart.set_x_axis({ ‘name’: ‘Earnings per Quarter’, #设置X轴标题名称 ‘name_font’: {‘size’: 14, ‘bold’: True}, #设置X轴标题字体属性 ‘num_font’: {‘italic’: True}, #设置X轴数字字体属性 })

  2.    -  set_size(options) 方法，设置图表大小，如：

chart.set_size({‘width’: 720,’height’: 576}),

  2.    -  set_title(options)方法，设置图表标题，如：

chart.set_title({‘name’: ‘Year End Results’}),

  2.    -  set_style(style_id)方法，设置图表样式，style_id为不同数字则代表不同样式，如：

chart.set_style(37)

  2.    -  set_table(options)方法，设置X轴为数据表格形式，如

set_table()

  2. <a name="554df200"></a>
  3. ### 2.4 示例

!/usr/bin/python3

import xlsxwriter

创建一个Excel文件demo.xlsx

workbook = xlsxwriter.Workbook(‘demo.xlsx’)

创建一个Excel sheet

worksheet = workbook.add_worksheet()

设置列A、B、C的宽度，为20像素

worksheet.set_column(‘A:C’, 20)

设置一个加粗属性

bold = workbook.add_format() bold.set_bold()

设置一个颜色属性

color = workbook.add_format() color.set_bg_color(‘red’) color.set_color(‘green’)

在A1和A2单元格上分别写入“你好”、’python’

worksheet.write(‘A1’,’你好’) worksheet.write(‘A2’,’Python’,bold)

在第2行第0列写入10，由于行号是从0开始的，即写入的单元格为A3

worksheet.write(2,0,10,color) worksheet.write(3,0,20) worksheet.write(4,0,’=SUM(A3:A4)’)

业务表 服务器流量 图表

title = [‘服务器流量’,’周一’,’周二’,’周三’,’周四’,’周五’,’周六’,’周日’] allinfo = [] allinfo.append([‘server1’,101,90,97,86,79,100,80]) allinfo.append([‘server2’,80,89,79,92,85,91,69])

添加一个柱状图

chart = workbook.add_chart({‘type’:’column’})

从C1到J1写入标题

worksheet.write_row(‘C1’,title,bold)

从C2到J2写入内容，从C3到J3写入内容

for num in range(len(allinfo)): row = num + 2 worksheet.write_row(‘C{}’.format(row), allinfo[num]) chart.add_series({ ‘name’:’=Sheet1!$C${}’.format(row), ‘categories’:’=Sheet1!$D$1:$J$1’, ‘values’:’=Sheet1!$D${0}:$J${0}’.format(row), ‘line’:{‘color’:’red’}, })

在生成的图表下面写入生成图表的数据

chart.set_table()

设置图表配色风格

chart.set_style(30)

设置图表尺寸

chart.set_size({‘width’:700,’height’:300})

设置图表标题

chart.set_title({‘name’:’服务器流量报表’})

设置Y轴单位等信息

chart.set_y_axis({‘name’:’GB’})

插入图表

worksheet.insert_chart(‘C6’,chart)

保存关闭Excel表格

workbook.close() ```

程序运行结果如下：

3. 定制业务质量报表详解 - 图4