1 importopenpyxl2 importos3
4 classExceltoExcel():5
6 def __init__(self, file, new_sheet):7 self.universityData ={}8 self.wb =openpyxl.load_workbook(file)9 #self.wb_new =openpyxl.Workbook() # 新建一个表格来存储生成的数据
10 self.sheet =self.wb.active11 self.new_sheet = new_sheet #新表单
12 self.maxrow =self.sheet.max_row13 self.maxcol =self.sheet.max_column14
15
16 deftodict(self):17 '''
18 统计每个大学全部的获奖数量,分别列出是几等奖和对应的数量19 {'同济大学':{'一等奖':{'team':1,'num'=3},'二等奖':{'team':2,'num'=6},'三等奖':{'team':1,'num'=3},'成功参与奖':{'team':1,'num'=3}},20 '清华大学':{'一等奖':{'team':1,'num'=3},'二等奖':{'team':1,'num'=3},'三等奖':{'team':1,'num'=3},'成功参与奖':{'team':1,'num'=3}},...}21 统计所有人数22 Fill in universityData with each rewardship‘s popularity23 '''
24 #tolal_team = self.maxrow
25 for row in range(2, self.maxrow + 1):26 #取每个单元格的数据
27 sheet_col = ['F', 'H', 'J']28 rewardcell = self.sheet['D' + str(row)].value #D列奖项
29 rewards = ['一等奖', '二等奖', '三等奖', '成功参与奖']30 for k insheet_col:31 university = self.sheet[k + str(row)].value #每列大学的名称
32 #team = self.sheet['C' + str(row)].value # C列队伍
33 #确定键值
34 self.universityData.setdefault(university, {})35 for reward inrewards:36 if reward ==rewardcell:37 self.universityData[university].setdefault(rewardcell, {'team': 0, 'num': 0})38 self.universityData[university][rewardcell]['num'] += 1 #统计各个奖项的所有人数
39 else:40 self.universityData[university].setdefault(reward, {'team': 0, 'num': 0})41
42 if k == 'F':43 self.universityData[university][rewardcell]['team'] += 1 #只统计队长所在大学
44 self.universityData = sorted(self.universityData.items(), key=lambda item: item[0]) #按照键值排序返回元祖
45 self.universityData = dict(self.universityData) #将元祖转换成字典
46 #print('universityData', self.universityData)
47 #print(type(self.universityData))
48 returnself.universityData49
50 #查找单个键
51 def find(self, target, dictData, notFound='没找到'):52 #倒序查找第一个出现的需要查找的键的值
53 queue = [dictData] #将字典存入列表
54 while len(queue) >0:55 data = queue.pop() #data是在queue中取出的最后一个元素,也就是原始字典;此时的queue为空列表[]
56 print('data', data)57 for key, value indata.items():58 if key ==target:59 returnvalue60 elif type(value) ==dict:61 queue.append(value)62 returnnotFound63
64 #有多个同名键在字典里时,可以用这个方法
65 def findAll(self, target, dictData, notFound=[]):66 #倒序查找所有出现的需要查找的键的值
67 queue =[dictData]68 result =[]69 while len(queue) >0:70 data =queue.pop()71 for key, value indata.items():72 if key ==target:73 result.append(value)74 elif type(value) ==dict:75 queue.append(value)76 if not result: result =notFound77 returnresult78
79 defwrite_list_to_excel(self, dictData, num_list, team_list):80 list_slice = [] #人数切片
81 team_slice = [] #队伍切片
82 sublist_sum = [] #每个人数切片的和
83 team_sum = [] #每个队伍切片的和
84 k =085 row_1 = ['学校名称','一等奖','二等奖','三等奖','成功参与奖','总人数','队伍数量']86 for i inrange(len(row_1)):87 self.new_sheet.cell(row=1,column=i+1,value=row_1[i])88 university_name =[]89 for key indictData.keys():90 university_name.append(key)91 for index, name inenumerate(university_name):92 self.new_sheet['A'+str(index+2)] =name93 while k
95 team_sub = team_list[k:k+4] #队伍子集
96 list_slice.append(sub_list) #切片后存入列表
97 team_slice.append(team_sub)98 sumlist = sum(sub_list) #计算每个子集的和
99 teamsum =sum(team_sub)100 sublist_sum.append(sumlist) #将每个子集的和加入列表
101 team_sum.append(teamsum)102 k += 4
103 if k >len(num_list):104 break
105 for row in range(2, len(university_name)+2):106 for col in range(2, 6):107 self.new_sheet.cell(column=col, row=row, value=list_slice[row-2][col-2])108 self.new_sheet.cell(column=6, row=row, value=sublist_sum[row-2])109 self.new_sheet.cell(column=7, row=row, value=team_sum[row - 2])110 returnuniversity_name, list_slice, sublist_sum, team_sum111
112
113 if __name__ == '__main__':114 file_path = './file' #excel文件路径
115 files = [] #存储excel文件名
116 list = os.listdir(file_path) #列出excel文件路径下所有的文件
117 list.sort(key=lambda x: x[4:5]) #按照题目顺序排序
118 #print('list', list)
119 for i inrange(len(list)):120 item =os.path.join(file_path, list[i])121 files.append(item)122 #print('files', files)
123 wb_new = openpyxl.Workbook() #新建一个表格来存储生成的数据
124 f = open('result.txt', 'w')125 for k,file inenumerate(files):126 new_sheet = wb_new.create_sheet('list', index=k) #插入新表单
127 excel =ExceltoExcel(file, new_sheet)128 dictData = excel.todict() #得到当前表格排序后的字典
129 #find_one = excel1.find('team', dictData)
130 find_num = excel.findAll('num', dictData) #查找当前表格每个大学每个奖项的获奖人数
131 find_team = excel.findAll('team', dictData) ## 查找当前表格每个大学每个奖项的获奖队伍数,只统计队长所在的学校
132 find_num.reverse() #正序排列
133 find_team.reverse() #正序排列
134 #print(find_num)
135 #print(find_team)
136 _, _, _, team_sum =excel.write_list_to_excel(dictData,find_num, find_team)137 wb_new.save('result.xlsx')138 #print('university name', university)
139 #print('list_slice', list_slice)
140 #print('sublist_sum', sublist_sum)
141 title = file.split('/')[2][4:5] #字符串分割提取题目,原标题为'./file/2019A.xlsx'
142 print('{}题共有{}支队伍获奖'.format(title, sum(team_sum)))143 f.write('{}题共有{}支队伍获奖\n'.format(title, sum(team_sum)))