体验python automation office

  • A+
所属分类:体育平台

实验步骤

一、制作一个测试使用的excel工作簿,销售月报.xlsx


体验python automation office

销售月报.xlsx


体验python automation office

销售月报.xlsx

二、使用python实现各季度销售统计与生成报表


体验python automation office

pycharm2018


体验python automation office

pycharm2018


三、执行lab.py脚本生成新建立的excel工作簿

体验python automation office

销售月报图表.xlsx


体验python automation office

销售月报图表.xlsx,最终生成的各季度水果销量汇总图表


代码如下:(初识python代码,能力不足比较繁琐)

import xlrd,xlsxwriter

#读取数据

data=xlrd.open_workbook("销售月报.xlsx")

sheetinfo=[]

for sheet in data.sheets():

dict={"name":sheet.name,"sales":0}

sum=0

for i in range(sheet.nrows):

if i>1:

sum+=float(sheet.cell(i,1).value)

dict["sales"]=sum

sheetinfo.append(dict)

sheetinfo2=[]

for sheet in data.sheets():

dict={"name":sheet.name,"sales":0}

sum=0

for i in range(sheet.nrows):

if i>1:

sum+=float(sheet.cell(i,2).value)

dict["sales"]=sum

sheetinfo2.append(dict)

sheetinfo3=[]

for sheet in data.sheets():

dict={"name":sheet.name,"sales":0}

sum=0

for i in range(sheet.nrows):

if i>1:

sum+=float(sheet.cell(i,3).value)

dict["sales"]=sum

sheetinfo3.append(dict)


#创建新工作簿制作报表

wb=xlsxwriter.Workbook("销售月报图表.xlsx") #xlsxwriter模块支持xlsx格式

sheet=wb.add_worksheet()

#写入每个季度的销售

nameinfo=[]

salesinfo=[]

nameinfo2=[]

salesinfo2=[]

nameinfo3=[]

salesinfo3=[]

for item in sheetinfo:

nameinfo.append(item["name"])

salesinfo.append(item["sales"])

sheet.write_row("A1",nameinfo)

sheet.write_row("A2",salesinfo)

for item in sheetinfo2:

nameinfo2.append(item["name"])

salesinfo2.append(item["sales"])

sheet.write_row("A1",nameinfo2)

sheet.write_row("A3",salesinfo2)

for item in sheetinfo3:

nameinfo3.append(item["name"])

salesinfo3.append(item["sales"])

sheet.write_row("A1",nameinfo3)

sheet.write_row("A4",salesinfo3)

# #写入图表

sheet=wb.add_worksheet()

chart=wb.add_chart({"type":"column"})

chart.set_title({'name':'2021季度销售统计'})

#写入数据

chart.add_series({

"name":"销量",

"categories":"=Sheet1!$A$1:$D$1",

"values":["Sheet1",1,0,1,3],

"data_labels":{"value":True}

})

chart.add_series({

"name":"销量",

"categories":"=Sheet1!$A$1:$D$1",

"values":["Sheet1",2,0,2,3],

"data_labels":{"value":True}

})

chart.add_series({

"name":"销量",

"categories":"=Sheet1!$A$1:$D$1",

"values":["Sheet1",3,0,3,3],

"data_labels":{"value":True}

})

sheet.insert_chart("A1",chart)

wb.close()

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: