在公司经常自己处理excel,而且任务重复,繁多,趁着学python,自己搞一个脚本以后处理
# coding=utf-8 import xlwt import numpy import xlrd from xlutils.copy import copy import json # 转化格式 #获取文件,用户输入 print json.dumps("请输入excel文件 格式 f.xlsx ",encoding="UTF-8",ensure_ascii=False) OnLinefile=raw_input() #获取时间,用户输入 print json.dumps("请输入上线时间 格式 2017-11-09",encoding="UTF-8",ensure_ascii=False) OnLineDate=raw_input() #获取实验组尾号 print json.dumps("请输入实验组尾号 格式 2",encoding="UTF-8",ensure_ascii=False) TestNum=raw_input() TestNum=int(TestNum) # 构造列表存储数据 listn = [] # 存储sheet name #打开excel读取内容 OnLinefile=unicode(OnLinefile,'gbk')#防止中文名 fname = '/Users/likun/Desktop/'+OnLinefile GetExcel = xlrd.open_workbook(fname) # 打开文件 #获取每个sheet的文件名称以及sheet数量 sheetNumber=len(GetExcel.sheets()) for i in range(sheetNumber): listn.append(GetExcel.sheet_by_index(i).name) # 创建一个Excel文件,存储内容 book = xlwt.Workbook(encoding='utf-8', style_compression=0) # 创建一个sheet sheet=book.add_sheet("result", cell_overwrite_ok=True) for m in range(sheetNumber): # 构造列表存储数据 listb = ['before'] # 存储上线前差值 lista = ['after'] # 存储上线后差值 listr = ['result'] # 存储最终结果 #读取一个sheet的内容 SheetContent = GetExcel.sheet_by_index(m) # 对sheet建索引 nrows = SheetContent.nrows # 获取行数 ncols = SheetContent.ncols # 获取列数 sheetNewName = listn[m] sheetNewName = json.dumps(sheetNewName, encoding="UFT-8", ensure_ascii=False) print sheetNewName print json.dumps(SheetContent.row_values(1),encoding="UTF-8",ensure_ascii=False) #判断是哪一天上线的 for i in range(nrows): if SheetContent.cell(i,0).value==OnLineDate: BreakDay=i #获取上线后的平均值 for j in range(1,ncols): dis = 2 total=0 for i in range(2,BreakDay): a=SheetContent.cell(i,j).value if a=="": a=0 dis=dis+1 total = total + a b=total/(BreakDay-dis) lista.append(b) #获取上线前的平均值 for j in range(1,ncols): dis = 2 total=0 beginday=BreakDay+1 lastday=BreakDay+BreakDay-1 if lastday>nrows: lastday=nrows for i in range(beginday,lastday): a=SheetContent.cell(i,j).value if a=="": a=0 dis=dis+1 total = total + a b=total/(lastday-beginday-dis+2) listb.append(b) #打印差值 for i in range(1,len(lista)): x=lista[i]/listb[i]-1 x=x*100 listr.append(x) #-*- 单开一个地方算平均差值 -*- #求上线后人均 if len(SheetContent.row_values(1))==21: listavp=[] for i in range(1,ncols-1,2): listava = [] # 存储上线后人均 for j in range(2,BreakDay): a= SheetContent.cell(j, i).value b= SheetContent.cell(j,i+1).value if a==0: a=1 y=b/a listava.append(y) listavb = [] # 存储上线前人均 beginday = BreakDay + 1 lastday = BreakDay + BreakDay - 1 if lastday >nrows: lastday=nrows for j in range(beginday, lastday): a = SheetContent.cell(j, i).value b = SheetContent.cell(j, i + 1).value if a == 0: a = 1 y = b / a listavb.append(y) #计算平均的平均 q=sum(listava)/len(listava) p=sum(listavb)/len(listavb) x=(q/p-1)*100 listavp.append(x) #将结果打印出来 print listb print lista print listr if len(SheetContent.row_values(1))==11: y1=listr[TestNum+1]-listr[9] y1=str(y1)+"%" print "--------------------------------"+y1+"%"+"--------------------------------" llatnum=y1 elif len(SheetContent.row_values(1))==21: y2=listr[TestNum*2+1]-listr[17] y2=str(y2) y3=listr[TestNum*2+2]-listr[18] y3=str(y3) y4=listavp[TestNum]-listavp[8] y4=str(y4) llatnum="renshu:"+y2+"%"+" cishu:"+y3+"%"+" renjun:"+ y4+"%" print "--------------------------------renshu:"+y2+"%"+" cishu:"+y3+"%"+" "+"renjun:"+ y4+"%"+"--------------------------------" else: llatnum="" # 向表sheet中添加数据 sheetNewName = listn[m] sheetNewName = json.dumps(sheetNewName, encoding="UFT-8", ensure_ascii=False) sheet.write(m*6,0,sheetNewName) for i in range(ncols): sheet.write(m*6 + 1, i, SheetContent.cell(1, i).value) for i in range(len(listb)): sheet.write(m*6 + 2, i, listb[i]) sheet.write(m*6 + 3, i, lista[i]) sheet.write(m*6 + 4, i, listr[i]) sheet.write(m*6 + 5, 0, llatnum) # 保存 book.save('result_visitor.xls')