Python automatically processes data generation reports

Using the module Xlsxwriter

Import Xlsxwriter

Workbook = Xlsxwriter. Workbook (' chart.xlsx ') #创建一个Excel文件

Worksheet = Workbook.add_worksheet () #创建一个工作表对象

Chart = Workbook.add_chart ({' type ': ' column '}) #创建一个图表对象

#定义数据表头列表

title = [u ' Business name ', U ' Monday ', U ' Tuesday ', U ' Wednesday ', U ' Thursday ', U ' Friday ', U ' Saturday ', U ' Sunday ', U ' average flow ']

buname= [u ' Business official website ', U ' News Center ', u ' shopping channel ', U ' sports channel ', U ' parent channel '] #定义频道名称

#定义5频道一周7天流量数据列表

data = [

[150,152,158,149,155,145,148],

[89,88,95,93,98,100,99],

[201,200,198,175,170,198,195],

[75,77,78,78,74,70,79],

[88,85,87,90,93,88,84],

]

Format=workbook.add_format () #定义format格式对象

Format.set_border (1) format #定义format对象单元格边框加粗 (1 pixels)

Format_title=workbook.add_format () #定义format_title格式对象

Format_title.set_border (1) format #定义format_title对象单元格边框加粗 (1 pixels)

Format_title.set_bg_color (' #cccccc ') #定义format_title对象单元格背景颜色为cccccc的格式

Format_title.set_align (' center ') #定义format_title对象单元格居中对齐的格式

Format_title.set_bold () #定义format_title对象单元格内容加粗的格式

Format_ave=workbook.add_format () #定义format_ave格式对象

Format_ave.set_border (1) format #定义format_ave对象单元格边框加粗 (1 pixels)

Format_ave.set_num_format (' 0.00 ') #定义format_ave对象单元格数字类别显示格式

#下面分别以行或列写入方式将标题, business name, traffic data written to the original cell, referencing different format objects

Worksheet.write_row (' A1 ', title,format_title)

Worksheet.write_column (' A2 ', Buname,format)

Worksheet.write_row (' B2 ', Data[0],format)

Worksheet.write_row (' B3 ', Data[1],format)

Worksheet.write_row (' B4 ', Data[2],format)

Worksheet.write_row (' B5 ', Data[3],format)

Worksheet.write_row (' B6 ', Data[4],format)

#定义图表数据系列函数

def chart_series (Cur_row):

Worksheet.write_formula (' I ' +cur_row, ' =average (B ' +cur_row+ ': H ' +cur_row+ ') ', Format_ave) #计算 (AVERAGE function) channel average weekly flow

Chart.add_series ({

' Categories ': ' =sheet1! $B $: $H ', #将 ' Monday to Sunday ' as a chart data label (x-axis)

' Values ': ' =sheet1! $B $ ' +cur_row+ ': $H $ ' +cur_row, #频道一周所有数据作为数据区域

' line ': {' color ': ' Red '}, #线条颜色定义为black (black)

' Name ': ' =sheet1! $A $ ' +cur_row, #引用业务名称为图例项

})

For row in range (2, 7): Chart data series function call #数据域以第2.

Chart_series (str (ROW))

#chart. Set_table () #设置X轴表格格式, this example does not enable

#chart. Set_style (#设置图表样式), this example does not enable

Chart.set_size ({' width ': 577, ' height ': 287}) #设置图表大小

Chart.set_title ({' Name ': U ' traffic data Weekly Report '}) #设置图表 (above) Big title

Chart.set_y_axis ({' name ': ' MB/s} ') #设置y轴 (left) small title

Worksheet.insert_chart (' A11 ', chart) #在A8单元格插入图表

Workbook.close ()

After you generate a report, you can use a mail handler to send to the specified contact.

The more useful mail processing module under Python is Smtplib, please refer to my previous articles on how to use Smtplib to send mail http://itech.blog.51cto.com/192113/1782213

This article is from a "sense of direction" blog, be sure to keep this source http://itech.blog.51cto.com/192113/1788666

Python automatically processes data generation reports