import os
from openpyxl import load_workbook
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
wb_second_index = load_workbook("statistics_all_index.xlsx")#print(wb_second_index.sheetnames)#ws_data = wb_second_index.get_sheet_by_name('原始数据')#ws_key = wb_second_index.get_sheet_by_name('关键字')
ws_data = wb_second_index['原始数据']
ws_key = wb_second_index['关键字']#print(ws_data["A3"].value)#print(ws_key["D2"].value)#print(ws_key.cell(row=2,column=4).value)
list_key1 =[]#级别
list_key2 =[]#城市
list_key3 =[]#省份
list_key4 =[]#战区for x inrange(2,8):
list_key1.append(ws_key.cell(row=x,column=4).value)#print(list_key1)for x inrange(2,247):
list_key2.append(ws_key.cell(row=x,column=3).value)#print(list_key2)for x inrange(2,31):
list_key3.append(ws_key.cell(row=x,column=2).value)#print(list_key3)for x inrange(2,7):
list_key4.append(ws_key.cell(row=x,column=1).value)#print(list_key4)for x inrange(2,559):
ws.cell(row=x,column=1).value=ws_data.cell(row=x,column=1).value
ws.cell(row=x,column=2).value=ws_data.cell(row=x,column=2).value
ws.cell(row=x,column=3).value=ws_data.cell(row=x,column=3).value
ws.cell(row=x,column=5).value=ws_data.cell(row=x,column=5).value
ws.cell(row=x,column=4).value=ws_data.cell(row=x,column=4).value
for y inrange(6,113,2):
ws.cell(row=1,column=y).value=str(ws_data.cell(row=1,column=((y-6)/2+6)).value)+"平均值"
ws.cell(row=1,column=y+1).value=str(ws_data.cell(row=1,column=((y-6)/2+6)).value)+"中位值"#for a in range(0,4):if ws_data.cell(row=x,column=5).value==list_key1[0]:#判断级别,1级
average_numbers = cal_average(ws_data,list_key1,y,0,5)
max_numbers = cal_max(ws_data,list_key1,y,0,5)if(average_numbers=="值有问题")or(max_numbers=="值有问题"):
ws.cell(row=x,column=y).value ="值有问题"
ws.cell(row=x,column=y+1).value ="值有问题"else:
ws.cell(row=x,column=y).value = average_numbers
ws.cell(row=x,column=y+1).value =((average_numbers)+(max_numbers))/2#ws.cell(row=x,column=y).value = cal_average(ws_data,list_key1,y,0,5)#ws.cell(row=x,column=y+1).value = cal_max(ws_data,list_key1,y,0,5)if ws_data.cell(row=x,column=5).value==list_key1[1]:#判断级别,2级for a inrange(0,5):if ws_data.cell(row=x,column=2).value==list_key4[a]:
average_numbers = cal_average(ws_data,list_key4,y,a,2)
max_numbers = cal_max(ws_data,list_key4,y,a,2)if(average_numbers=="值有问题")or(max_numbers=="值有问题"):
ws.cell(row=x,column=y).value ="值有问题"
ws.cell(row=x,column=y+1).value ="值有问题"else:
ws.cell(row=x,column=y).value = average_numbers
ws.cell(row=x,column=y+1).value =((average_numbers)+(max_numbers))/2#ws.cell(row=x,column=y).value = cal_average(ws_data,list_key4,y,a,2)#ws.cell(row=x,column=y+1).value = cal_max(ws_data,list_key4,y,a,2)if ws_data.cell(row=x,column=5).value==list_key1[2]:#判断级别,3级,for a inrange(0,5):if ws_data.cell(row=x,column=2).value==list_key4[a]:
average_numbers = cal_average(ws_data,list_key4,y,a,2)
max_numbers = cal_max(ws_data,list_key4,y,a,2)if(average_numbers=="值有问题")or(max_numbers=="值有问题"):
ws.cell(row=x,column=y).value ="值有问题"
ws.cell(row=x,column=y+1).value ="值有问题"else:
ws.cell(row=x,column=y).value = average_numbers
ws.cell(row=x,column=y+1).value =((average_numbers)+(max_numbers))/2#ws.cell(row=x,column=y).value = cal_average(ws_data,list_key4,y,a,2)#ws.cell(row=x,column=y+1).value = cal_max(ws_data,list_key4,y,a,2)#判断级别,4级if(ws_data.cell(row=x,column=5).value==list_key1[3])or(ws_data.cell(row=x,column=5).value==list_key1[4])or(ws_data.cell(row=x,column=5).value==list_key1[5]):for a inrange(0,29):if ws_data.cell(row=x,column=3).value==list_key3[a]:
average_numbers = cal_average(ws_data,list_key3,y,a,3)
max_numbers = cal_max(ws_data,list_key3,y,a,3)if(average_numbers=="值有问题")or(max_numbers=="值有问题"):
ws.cell(row=x,column=y).value ="值有问题"
ws.cell(row=x,column=y+1).value ="值有问题"else:
ws.cell(row=x,column=y).value = average_numbers
ws.cell(row=x,column=y+1).value =((average_numbers)+(max_numbers))/2#ws.cell(row=x,column=y).value = cal_average(ws_data,list_key3,y,a,3)#ws.cell(row=x,column=y+1).value = cal_max(ws_data,list_key3,y,a,3)#Aprint(count)#print(total)#print(count)#print(total)#if (total==0) or (count==0):#ws.cell(row=b,column=6).value = "有问题"#else:#ws.cell(row=b,column=6).value = total / count print("已处理第"+str(x)+"行数据")
wb.save('test.xlsx')print("完成")
defcal_average(ws_data,list_key,y,a,index):#求平均值
count =0
total =0for b inrange(2,559):if ws_data.cell(row=b,column=index).value== list_key[a]:if ws_data.cell(row=b,column=((y-6)/2+6)).value !=None:
count = count +1
value = ws_data.cell(row=b,column=((y-6)/2+6)).value
total = total + value
#Aprint(count)#print(total)if(count==0)or(total==0):
result="值有问题"else:
result=total/count
return result
print(count)print(total)