Python implements a method to customize the order and arrangement of writing data to Excel-Python Tutorial-php.cn

Python implements a method to customize the order and arrangement of writing data to Excel

不言

Release： 2018-04-23 15:13:42

Original

2912 people have browsed it

这篇文章主要介绍了Python实现自定义顺序、排列写入数据到Excel的方法,涉及Python针对Excel文件的数据处理及读写相关操作技巧,需要的朋友可以参考下

本文实例讲述了Python实现自定义顺序、排列写入数据到Excel的方法。分享给大家供大家参考，具体如下：

例1. 数据框顺序写入Excel：

data=a
import xlsxwriter
workbook = xlsxwriter.Workbook(&#39;F:/chart1.xlsx&#39;)
worksheet = workbook.add_worksheet(&#39;请求接口&#39;)
title = [u&#39;订单号&#39;,u&#39;债权编号&#39;,u&#39;请求参数&#39;,u&#39;创建时间&#39;,u&#39;结果&#39;]
print data.iloc[:,0]
format=workbook.add_format()
format.set_border(1)
format_title=workbook.add_format()
format_title.set_border(1)
format_title.set_bg_color(&#39;#cccccc&#39;)
format_title.set_align(&#39;center&#39;)
format_title.set_bold()
format_ave=workbook.add_format()
format_ave.set_border(1)
format_ave.set_num_format(&#39;0.00&#39;)
worksheet.write_row(&#39;A1&#39;,title,format_title)
worksheet.write_column(&#39;A2:&#39;, data.iloc[:,0],format)
worksheet.write_column(&#39;B2&#39;, data.iloc[:,1],format)
worksheet.write_column(&#39;C2&#39;, data.iloc[:,2],format)
worksheet.write_column(&#39;D2&#39;, data.iloc[:,3],format)
worksheet.write_column(&#39;E2&#39;, data.iloc[:,4],format)
workbook.close()

Copy after login

例2. (自动报表）：

#coding: utf-8
import xlsxwriter
workbook = xlsxwriter.Workbook(&#39;F:/chart.xlsx&#39;)
worksheet = workbook.add_worksheet()
chart = workbook.add_chart({&#39;type&#39;: &#39;column&#39;})
title = [u&#39;业务名称&#39;,u&#39;星期一&#39;,u&#39;星期二&#39;,u&#39;星期三&#39;,u&#39;星期四&#39;,u&#39;星期五&#39;,u&#39;星期六&#39;,u&#39;星期日&#39;,u&#39;平均流量&#39;]
buname= [u&#39;业务官网&#39;,u&#39;新闻中心&#39;,u&#39;购物频道&#39;,u&#39;体育频道&#39;,u&#39;亲子频道&#39;]
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],
]
print data
format=workbook.add_format()
format.set_border(1)
format_title=workbook.add_format()
format_title.set_border(1)
format_title.set_bg_color(&#39;#cccccc&#39;)
format_title.set_align(&#39;center&#39;)
format_title.set_bold()
format_ave=workbook.add_format()
format_ave.set_border(1)
format_ave.set_num_format(&#39;0.00&#39;)
worksheet.write_row(&#39;A1&#39;,title,format_title)
worksheet.write_column(&#39;A2&#39;, buname,format)
worksheet.write_row(&#39;B2&#39;, data[0],format)
worksheet.write_row(&#39;B3&#39;, data[1],format)
worksheet.write_row(&#39;B4&#39;, data[2],format)
worksheet.write_row(&#39;B5&#39;, data[3],format)
worksheet.write_row(&#39;B6&#39;, data[4],format)
def chart_series(cur_row):
  worksheet.write_formula(&#39;I&#39;+cur_row, \
   &#39;=AVERAGE(B&#39;+cur_row+&#39;:H&#39;+cur_row+&#39;)&#39;,format_ave)
  chart.add_series({
    &#39;categories&#39;: &#39;=Sheet1!$B$1:$H$1&#39;,
    &#39;values&#39;:   &#39;=Sheet1!$B$&#39;+cur_row+&#39;:$H$&#39;+cur_row,
    &#39;line&#39;:    {&#39;color&#39;: &#39;black&#39;},
    &#39;name&#39;:  &#39;=Sheet1!$A$&#39;+cur_row,
  })
for row in range(2, 7):
  chart_series(str(row))
chart.set_table()
chart.set_style(30)
chart.set_size({&#39;width&#39;: 577, &#39;height&#39;: 287})
chart.set_title ({&#39;name&#39;: u&#39;业务流量周报图表&#39;})
chart.set_y_axis({&#39;name&#39;: &#39;Mb/s&#39;})
worksheet.insert_chart(&#39;A8&#39;, chart)
workbook.close()

Copy after login