python使用xlsxwriter模块生成和处理Excel文档，,xlsxwriter

python使用xlsxwriter模块生成和处理Excel文档，,xlsxwriter

安装模块

wget https://files.pythonhosted.org/packages/04/c9/d5a8b02561a32bfcbec767a7d094c1ce54874eba9bc6bbaa58dd9ad523e7/XlsxWriter-1.0.4.tar.gz

tar zxf XlsxWriter-1.0.4.tar.gz

cd XlsxWriter-1.0.4

python setup.py install

官网参考示例http://xlsxwriter.readthedocs.io/chart_examples.html

#!/usr/bin/envpython#-*-coding:utf-8-*-importxlsxwriterworkbook=xlsxwriter.Workbook('chart.xlsx')worksheet=workbook.add_worksheet()chart=workbook.add_chart({'type':'column'})chart1=workbook.add_chart({'type':'line'})chart2=workbook.add_chart({'type':'column'})line_chart2=workbook.add_chart({'type':'line'})chart3=workbook.add_chart({'type':'line'})chart4=workbook.add_chart({'type':'pie'})title=[u'业务名称',u'星期一',u'星期二',u'星期三',u'星期四',u'星期五',u'星期六',u'星期日',u'平均流量']buname=[u'业务官网',u'新闻中心',u'购物频道',u'体育频道',u'亲子频道']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)title_format=workbook.add_format()title_format.set_border(1)title_format.set_bg_color('#cccccc')title_format.set_align('center')title_format.set_bold()ave_format=workbook.add_format()ave_format.set_border(1)ave_format.set_num_format('0.00')worksheet.write_row('A1',title,title_format)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)defchart_series(cur_row):worksheet.write_formula('I'+cur_row,'=AVERAGE(B'+cur_row+':H'+cur_row+')',ave_format)chart.add_series({'categories':'=Sheet1!$B$1:$H$1','values':'=Sheet1!$B$'+cur_row+':$H$'+cur_row,'line':{'color':'black'},'name':'=Sheet1!$A$'+cur_row,})forrowinrange(2,7):chart_series(str(row))chart1.add_series({'categories':'=Sheet1!$A$2:$A$6','values':'=Sheet1!$I$2:$I$6','marker':{'type':'diamond','size':7,'color':'green'},'data_labels':{'value':'True'},'line':{'color':'blue'},'name':u'平均流量分布'})chart2.add_series({'categories':'=Sheet1!A2:A6','values':'=Sheet1!B2:B6','line':{'color':'blue'},'name':u'周一流量'})line_chart2.add_series({'categories':'=Sheet1!A2:A6','values':'=Sheet1!I2:I6','marker':{'type':'diamond','size':7},'data_labels':{'value':'True'},'line':{'color':'blue'},'name':u'平均流量分布'})chart2.combine(line_chart2)chart3.add_series({'categories':'=Sheet1!B1:H1','values':'=Sheet1!B2:H2','name':u'业务官网流量'})chart3.add_series({'categories':'=Sheet1!B1:H1','values':'=Sheet1!B3:H3','name':u'新闻中心流量'})chart3.add_series({'categories':'=Sheet1!B1:H1','values':'=Sheet1!B4:H4','name':u'购物频道流量'})chart3.add_series({'categories':'=Sheet1!B1:H1','values':'=Sheet1!B5:H5','name':u'体育频道流量'})chart3.add_series({'categories':'=Sheet1!B1:H1','values':'=Sheet1!B6:H6','name':u'亲子频道流量'})chart4.add_series({'categories':'=Sheet1!A2:A6','values':'=Sheet1!I2:I6','data_labels':{'value':'True'},'name':u'平均流量分布'})chart.set_size({'width':577,'height':287})chart.set_title({'name':u'业务流量报表'})chart.set_y_axis({'name':'Mb/s'})chart1.set_size({'width':577,'height':287})chart1.set_title({'name':u'业务流量报表'})chart1.set_y_axis({'name':'Mb/s'})chart2.set_size({'width':577,'height':287})chart2.set_title({'name':u'业务流量报表'})chart2.set_y_axis({'name':'Mb/s'})chart3.set_size({'width':577,'height':287})chart3.set_title({'name':u'业务流量报表'})chart3.set_y_axis({'name':'Mb/s'})chart4.set_size({'width':577,'height':287})chart4.set_title({'name':u'业务流量报表'})chart4.set_y_axis({'name':'Mb/s'})worksheet.insert_chart('A8',chart)worksheet.insert_chart('K8',chart1)worksheet.insert_chart('A24',chart2)worksheet.insert_chart('K24',chart3)worksheet.insert_chart('A40',chart4)workbook.close()

python chart.py

运行结果 生产chart.xlsx文件

python使用xlsxwriter模块生成和处理Excel文档