朱老师找我帮忙 处理几张表格并将表格数据合并,看起来确实很繁琐。同时python表格处理也确实好久没有使用了。正好拿过来处理下,熟悉下知识。
思路很简单,将几张表全部读入内存 合并成一个列表 ,第二步就是循环列表合并数据 第三步写入保存列表。
半个小时,搞定!检查了下 数据完美。
粘下代码,日后留观。
import xlrd import xlwt from xlutils.copy import copy # 思路如下 首先将四张表分别读取 xlrd 版本1.2以上不支持xlsx 这是很不好的 # 将四个结果组合成一个字典 # 3、 将四个字典逐个合并 # 4 保存表格 # 1、 分别将四张表读取到四个不同的变量中 xls_1 = xlrd.open_workbook('1.xls') xls_2 = xlrd.open_workbook('2.xls') xls_3 = xlrd.open_workbook('3.xls') xls_4 = xlrd.open_workbook('4.xls') sheet1 = xls_1.sheet_by_index(0) sheet2 = xls_2.sheet_by_index(0) sheet3 = xls_3.sheet_by_index(0) sheet4 = xls_3.sheet_by_index(1) sheet5 = xls_4.sheet_by_index(0) row1 = sheet1.nrows row2 = sheet2.nrows row3 = sheet3.nrows row4 = sheet4.nrows row5 = sheet5.nrows data_all = [] # 写一个函数 完成数据的添加工作 def adddata(sheet1, row1): for n in range(1, row1): school = sheet1.cell(n, 0).value name = sheet1.cell(n, 1).value xueduan = sheet1.cell(n, 2).value xueke = sheet1.cell(n, 3).value cengci = str(sheet1.cell(n, 4).value) gongzuoshi = sheet1.cell(n, 5).value xuekao = sheet1.cell(n, 6).value isgood = sheet1.cell(n, 7).value sign = sheet1.cell(n, 8).value list = [school, name, xueduan, xueke, cengci, gongzuoshi, xuekao, isgood, sign] # //加入列表 data_all.append(list) adddata(sheet1, row1) adddata(sheet2, row2) adddata(sheet3, row3) adddata(sheet4, row4) adddata(sheet5, row5) newlist = [] # 进行每行数据比对增加信息 for i in data_all: # 检查是否存在同个人信息 tiao = False if len(newlist) > 0: for newdata in newlist: if i[0] == newdata[0] and i[1] == newdata[1] and i[2] == newdata[2] and i[3] == newdata[3]: if i[5] != "": newdata[5] = i[5] elif i[6] != "": newdata[6] = i[6] elif i[7] != "": newdata[7] = i[7] elif i[8] != "": newdata[8] = i[8] elif i[4] != "": newdata[4] = i[4] tiao = True continue if tiao == False: newlist.append(i) else: newlist.append(i) print(len(newlist)) new_xls = copy(xls_1) new_sheet = new_xls.get_sheet(0) n = 1 # n 为行 for v in newlist: new_sheet.write(n, 0, v[0]) new_sheet.write(n, 1, v[1]) new_sheet.write(n, 2, v[2]) new_sheet.write(n, 3, v[3]) new_sheet.write(n, 4, v[4]) new_sheet.write(n, 5, v[5]) new_sheet.write(n, 6, v[6]) new_sheet.write(n, 7, v[7]) new_sheet.write(n, 8, v[8]) n = n + 1 new_xls.save('10.xls') print('well done!')
文章评论