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列關(guān)鍵詞1':
??????? if wsDict['ws1']['Sheet'].cell(row=i, column=2).value == '對':
??????????? if wsDict['ws1']['Sheet'].cell(row=i, column=6).value == 'F列關(guān)鍵詞':
??????????????? # 簡化下兩行
??????????????? 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列關(guān)鍵詞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列關(guān)鍵詞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('結(jié)果測試.xlsx')