#!/usr/bin/python
#coding =utf-8
#导入模块
Import Xlsxwriter
#新建一个表文件
Workbook = Xlsxwriter. Workbook (' sample.xlsx ')
#新建一个表
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-child channel ']
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.set_border (1)
Format_title=workbook.add_format ()
Format_title.set_border (1)
Format_title.set_bg_color) (' #cccccc ')
Format_title.set_align (' Centor ')
Format_title.set_blod ()
Format_ave=workbook.add_format ()
Format_ave.set_border (1)
Format_ave.set_num_format (' 0.00 ')
#插入数据
Worksheet.write_row (' A1 ', title)
Worksheet.write_column (' A2 ', buname)
Worksheet.write_row (' B2 ', data[0])
Worksheet.write_row (' B3 ', data[1])
Worksheet.write_row (' B4 ', data[2])
Worksheet.write_row (' B5 ', data[3])
Worksheet.write_row (' B6 ', data[4])
#定义图表数据系列函数
def chart_series (Cur_now):
Worksheet.write_formula (' I ' +cur_now, \
' =average (B ' +cur_now+ ': H ' +cur_now+ ') ', ' Format_ave ') #计算平均值
Chart.add_series ({
' Categories ': ' =sheet1! $B $ $H ', #B1至H1元素作为x轴
' Values ': ' =sheet1! $B $ ' +cur_now+ ': H ' +cur_now, #从B2-h2 start, each row as data
' line ': {' color ': ' #cccccc '}, #线条颜色
' Name ': ' =sheet1! $A $ ' +cur_now, #引用A列作为数值类型
})
For row in range (2,7):
Chart_series (str (ROW))
Chart.set_size ({' width ': 577, ' height ': 277})
Chart.set_title ({' Name ': U ' business Traffic Weekly Report '})
Chart.set_y_axis ({' name ': ' MB/s '})
Worksheet.insert_chart (' A8 ', chart)
Workbook.close ()
Excel table Effects:
Making an Excel table of Python scripts