处理几个表格

朱老师找我帮忙 处理几张表格并将表格数据合并，看起来确实很繁琐。同时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!')