场景：有如下2个excel，重叠字段为：randomID
image.png
image.png
现需要将2个excel合并为一个。
实现步骤：
1.先读取2个excel中的内容，分别存入字典、列表中
2.合并读取的数据，遍历列表中的数据并转换为字典，循环判断重叠字段randomID的值是否相同
3.定义新列表，将randomID相同的数据，为其追加no及对应值，否则设置no为空
代码：
# -*- coding: utf-8 -*-
import openpyxl
mybook = openpyxl.load_workbook("D:/A.xlsx")
mySheet = mybook["data"]
my_res = list(mySheet.rows)
# 按行获取单元格（第一行除外--标题，不是数据）
myRows = list(mySheet.values)[1:]
mydics = {}
for myRow in myRows:
mydics[myRow[0]] = myRow[1]
workbook = openpyxl.load_workbook("D:/B.xlsx")
sheet = workbook["Sheet1"]
# rows 按照行获取表单中所有的格子，每一行的数据放到一个元祖中
res = list(sheet.rows)
# 获取excel表格中的第一行的数据，作为字典的key==》生成一个list列表
title = [i.value for i in res[0]]
# 作为每个字典的容器
cases = []
# 遍历第一行意外的所有行
for item in res[1:]:
# 获取每行的数据
dataline = [i.value for i in item]
# 把遍历的每行数据与第一行title数据打包成字典
dicline = dict(zip(title, dataline))
cases.append(dicline)
#合并excel
listnew=[]
for i in range(len(cases)):
cdict = dict(eval(str(cases[i])))
dictnew = {}
for k,v in mydics.items():
ouid = cdict.get("ouid")
if ouid == k:
dictnew['id'] = cdict.get("id")
dictnew['randomID'] = cdict.get("randomID")
dictnew['score'] = cdict.get("score")
dictnew['no'] = v
break
else:
pass
else:
dictnew['id'] = cdict.get("id")
dictnew['randomID'] = cdict.get("randomID")
dictnew['score'] = cdict.get("score")
dictnew['no'] = "null"
i=i+1
listnew.append(dictnew)
print(listnew)
输出结果为：
[{'id': 1, 'randomID': 'AAE_AdoYAADnV8cAAdk6HzNj', 'score': 599.0, 'no': 778315120022724617}, {'id': 2, 'randomID': 'AAE0AdoYAADnV8cAAdmYBvlN', 'score': 99.0, 'no': 787756648316944391}, {'id': 3, 'randomID': 'AAE0AdoYAADnV8cAAdmYBvlN', 'score': 699.0, 'no': 787756648316944391}, {'id': 4, 'randomID': 'AAE0AdoYAADnV8cAAdmYBvlN', 'score': 21.11, 'no': 787756648316944391}, {'id': 5, 'randomID': 'AAE1AdoYAADnV8cAAdlQSmHq', 'score': 1959.0, 'no': 'null'}, {'id': 6, 'randomID': 'AAE1AdoYAADnV8cAAdn7-ylG', 'score': 119.0, 'no': 777604554716028930}, {'id': 7, 'randomID': 'AAE2AdoYAADnV8cAAdnzV0jU', 'score': 839.0, 'no': 780087715652710401}, {'id': 8, 'randomID': 'AAE5AdoYAADnV8cAAdmAAEMm', 'score': 1399.0, 'no': 700457991720939528}, {'id': 9, 'randomID': 'AAE5AdoYAADnV8UAAdufjehV', 'score': 5599.0, 'no': 780604224598454277}, {'id': 10, 'randomID': 'AAE5AdoYAADnV8UAAdufjehV', 'score': 5129.0, 'no': 780604224598454277}, {'id': 11, 'randomID': 'AAE6AdoYAADnV8cAAdl1i_lx', 'score': 1999.0, 'no': 777508343620055048}]
