学python的第一次实战
from openpyxl import *
wb = load_workbook('文件名.xlsx')
wsDict = {
'ws1': {'Sheet': wb["2022"], 'MaxRow': wb["2022"].max_row},
'ws2': {'Sheet': wb["LSPT"], 'MaxRow': wb["LSPT"].max_row},
'ws3': {'Sheet': wb["LSTQ"], 'MaxRow': wb["LSTQ"].max_row},
'ws4': {'Sheet': wb["LSTY"], 'MaxRow': wb["LSTY"].max_row},
'ws5': {'Sheet': wb["WLPT"], 'MaxRow': wb["WLPT"].max_row},
'ws6': {'Sheet': wb["WLTQ"], 'MaxRow': wb["WLTQ"].max_row},
'ws7': {'Sheet': wb["WLTY"], 'MaxRow': wb["WLTY"].max_row}
}
# i是总表的行
i = 2
# j是各个附表的行
j = 6
while i <= wsDict['ws1']['MaxRow']:
if wsDict['ws1']['Sheet'].cell(row=i, column=1).value == 'A列关键词1':
if wsDict['ws1']['Sheet'].cell(row=i, column=2).value == '对':
if wsDict['ws1']['Sheet'].cell(row=i, column=6).value == 'F列关键词':
# 简化下两行
temp1 = wsDict['ws1']['Sheet'].cell(row=i, column=11).value
while j <= wsDict['ws6']['MaxRow']:
if temp1 == wsDict['ws6']['Sheet'].cell(row=j, column=2).value:
# 简化下一行
temp2 = wsDict['ws6']['Sheet'].cell(row=j, column=3).value
wsDict['ws1']['Sheet'].cell(row=i, column=17, value=temp2)
j += 1
j = 6
else:
# 简化下两行
temp1 = wsDict['ws1']['Sheet'].cell(row=i, column=6).value
while j <= wsDict['ws6']['MaxRow']:
if temp1 == wsDict['ws6']['Sheet'].cell(row=j, column=2).value:
# 简化下一行
temp2 = wsDict['ws6']['Sheet'].cell(row=j, column=3).value
wsDict['ws1']['Sheet'].cell(row=i, column=17, value=temp2)
j += 1
j = 6
else:
# 简化下三行
temp1 = wsDict['ws1']['Sheet'].cell(row=i, column=6).value
while j <= wsDict['ws3']['MaxRow']:
if temp1 == wsDict['ws3']['Sheet'].cell(row=j, column=2).value:
# 简化下一行
temp2 = wsDict['ws3']['Sheet'].cell(row=j, column=3).value
wsDict['ws1']['Sheet'].cell(row=i, column=17, value=temp2)
j += 1
j = 6
elif wsDict['ws1']['Sheet'].cell(row=i, column=1).value == 'A列关键词2':
if wsDict['ws1']['Sheet'].cell(row=i, column=2).value == '对':
# 简化下九行
temp1 = wsDict['ws1']['Sheet'].cell(row=i, column=6).value
# 简化下六行
temp3 = wsDict['ws1']['Sheet'].cell(row=i, column=11).value
while j <= wsDict['ws7']['MaxRow']:
# 简化下四行
temp2 = wsDict['ws7']['Sheet'].cell(row=j, column=2).value
# 简化下一行
temp4 = wsDict['ws7']['Sheet'].cell(row=j, column=4).value
if temp1 == temp2 and temp3 == temp4:
# 简化下三行
temp5 = wsDict['ws7']['Sheet'].cell(row=j, column=5).value
# 简化下两行
temp6 = wsDict['ws7']['Sheet'].cell(row=j, column=13).value
wsDict['ws1']['Sheet'].cell(row=i, column=17, value=temp5)
wsDict['ws1']['Sheet'].cell(row=i, column=18, value=temp6)
j += 1
j = 6
else:
# 简化下九行
temp1 = wsDict['ws1']['Sheet'].cell(row=i, column=6).value
# 简化下六行
temp3 = wsDict['ws1']['Sheet'].cell(row=i, column=11).value
while j <= wsDict['ws4']['MaxRow']:
# 简化下四行
temp2 = wsDict['ws4']['Sheet'].cell(row=j, column=2).value
# 简化下一行
temp4 = wsDict['ws4']['Sheet'].cell(row=j, column=4).value
if temp1 == temp2 and temp3 == temp4:
# 简化下三行
temp5 = wsDict['ws4']['Sheet'].cell(row=j, column=5).value
# 简化下两行
temp6 = wsDict['ws4']['Sheet'].cell(row=j, column=13).value
wsDict['ws1']['Sheet'].cell(row=i, column=17, value=temp5)
wsDict['ws1']['Sheet'].cell(row=i, column=18, value=temp6)
j += 1
j = 6
elif wsDict['ws1']['Sheet'].cell(row=i, column=1).value == 'A列关键词3':
if wsDict['ws1']['Sheet'].cell(row=i, column=2).value == '对':
# 简化下九行
temp1 = wsDict['ws1']['Sheet'].cell(row=i, column=6).value
# 简化下六行
temp3 = wsDict['ws1']['Sheet'].cell(row=i, column=11).value
while j <= wsDict['ws5']['MaxRow']:
# 简化下四行
temp2 = wsDict['ws5']['Sheet'].cell(row=j, column=2).value
# 简化下一行
temp4 = wsDict['ws5']['Sheet'].cell(row=j, column=4).value
if temp1 == temp2 and temp3 == temp4:
# 简化下三行
temp5 = wsDict['ws5']['Sheet'].cell(row=j, column=5).value
# 简化下两行
temp6 = wsDict['ws5']['Sheet'].cell(row=j, column=12).value
wsDict['ws1']['Sheet'].cell(row=i, column=17, value=temp5)
wsDict['ws1']['Sheet'].cell(row=i, column=18, value=temp6)
j += 1
j = 6
else:
# 简化下九行
temp1 = wsDict['ws1']['Sheet'].cell(row=i, column=6).value
# 简化下六行
temp3 = wsDict['ws1']['Sheet'].cell(row=i, column=11).value
while j <= wsDict['ws2']['MaxRow']:
# 简化下四行
temp2 = wsDict['ws2']['Sheet'].cell(row=j, column=2).value
# 简化下一行
temp4 = wsDict['ws2']['Sheet'].cell(row=j, column=4).value
if temp1 == temp2 and temp3 == temp4:
# 简化下三行
temp5 = wsDict['ws2']['Sheet'].cell(row=j, column=5).value
# 简化下两行
temp6 = wsDict['ws2']['Sheet'].cell(row=j, column=12).value
wsDict['ws1']['Sheet'].cell(row=i, column=17, value=temp5)
wsDict['ws1']['Sheet'].cell(row=i, column=18, value=temp6)
j += 1
j = 6
i += 1
wb.save('结果测试.xlsx')
