Python -- custom automated business flow report weekly

Please indicate the source of Reprint: http://blog.csdn.net/l1028386804/article/details/78987851

For the enterprise production environment, monitoring the traffic data of the server is very important, so how to generate the corresponding traffic report through the traffic data? In this example, the traffic report of 5 channels of the website is customized, and the traffic data is written into the Excel document through the XlsxWriter module. At the same time, the average weekly traffic of each channel is calculated automatically, and then the data chart is generated. At the same time, the generated report can be sent to the mailbox of the designated person by combining with the mail sending module. Here we focus on using Python to generate the traffic report. How to send the mail please integrate yourself. At the same time, this example is the simulated traffic data. You can replace the data with the real traffic data of your server.
The specific code is as follows:

# -*- coding:UTF-8 -*-
'''
Created on 2018 January 6, 2006

@author: liuyazhuang
'''
import xlsxwriter


#Create an Excel file
workbook = xlsxwriter.Workbook('chart.xlsx')
#Create a sheet object
worksheet = workbook.add_worksheet()
#Create a chart object
chart = workbook.add_chart({'type':'column'})
#Define data header list
title = [u'Business name', u'Monday', u'Tuesday', u'Wednesday', u'Thursday', u'Friday', u'Saturday', u'Sunday', u'Average flow']
#Define channel name
buname = [u'Official business website', u'Information Center', u'Shopping channel', u'Sports channel', u'Parent child channel']
#Define the traffic data list of channel 5 for 7 days a week, simulated data
data = [
        [150,152,158,149,155,145,148],
        [89.88,95,93,98,100,99],
        [201,200,198,175,179,198,195],
        [75,77,78,78,74,70,79],
        [88,85,87,90,93,88,84]
    ]

#Define format object
format = workbook.add_format()
#Define the format of the bold (1 pixel) cell border of the format object
format.set_border(1)
#Define the format "title object
format_title = workbook.add_format()
#Define the format of bold (1 pixel) cell border of the object
format_title.set_border(1)
#Definition format_title Object cell background color is#cccccc format
format_title.set_bg_color('#cccccc')
#Define the format of the center alignment of the cell of the format ﹣ Title Object
format_title.set_align('center')
#Define the bold format of the cell content of the format ﹣ Title Object
format_title.set_bold()
#Define the format? Ave format object
format_ave = workbook.add_format()
#Define the format of bold (1 pixel) cell border of the format? Ave object
format_ave.set_border(1)
#Define the format of cell data category display
format_ave.set_num_format('0.00')
#Next, write the header, business name, and traffic data to the initial cell in the way of row or column writing, and reference 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)

#Data series functions defined in the table

#Calculate channel weekly average traffic
def chart_series(cur_row):
    worksheet.write_formula('I' + cur_row, '=AVERAGE(B' + cur_row + ':H' +cur_row+')')
    chart.add_series({
            #Use Monday to Sunday as chart data labels (X-axis)
            'categories': '=Sheet1!$B$1:$H$1',
            #All data of the channel in one week as data area
            'values':'=Sheet1!$B$' + cur_row + ':$H$' + cur_row,
            #Define line color as black
            'line' : {'color': 'black'},
            #Reference business name as legend item
            'name' : '=Sheet1!$A$' + cur_row
        })

#The data area calls the chart data series function in lines 2-6
for row in range(2, 7):
    chart_series(str(row))
#Set chart size
chart.set_size({'width': 577, 'height': 287})
#Set the headline of the chart
chart.set_title({'name':u'Business flow weekly report chart'})
#Set y-axis left subtitle
chart.set_y_axis({'name':'Mb/s'})

#Insert chart in cell A8
worksheet.insert_chart('A8', chart)
#Close Excel document
workbook.close()
The report results generated by running the program are as follows:


Tags: Excel Python

Posted on Tue, 05 May 2020 14:47:19 -0400 by g_p_java