I'm writing excel file using Python openpyxl module
I would like to ask you a question about how you can shorten the code while working with the for statement and list.
The code below is the code that you enter into the sheet of B file using the sheet contents of A file.
The maximum and average values are entered into file B as cell values for a specific range of file A.
The bottom part is the part where you get the same coordinates as the cell coordinates where the maximum value obtained by the specific range of cell values in file A is located and enter them into file B.
It was implemented as targeted, but as you can see, the resultant value input code using the for statement and ws.cell is being repeated too much because there are 16 lines of the target value.
I'd like to ask you how to shorten this part.
Thank you.
from openpyxl import load_workbook
wb = load_workbook(filename='Afile.xlsx')
ws = wb['Ark1']
wb1 = load_workbook(filename='Bfile.xlsx')
ws1 = wb1['Ark1']
cell_range100 = ws['A2':'A212']
cell_range = ws['B2':'B212']
cell_range2 = ws['C2':'C212']
cell_range3 = ws['D2':'D212']
cell_range4 = ws['E2':'E212']
cell_range5 = ws['F2':'F212']
cell_range6 = ws['G2':'G212']
cell_range7 = ws['H2':'H212']
cell_range8 = ws['I2':'I212']
cell_range9 = ws['J2':'J212']
cell_range10 = ws['K2':'K212']
cell_range11 = ws['L2':'L212']
cell_range12 = ws['M2':'M212']
cell_range13 = ws['N2':'N212']
cell_range14 = ws['O2':'O212']
cell_range15 = ws['P2':'P212']
cell_range16 = ws['Q2':'Q212']
list_var100 = []
list_var = []
list_var2 = []
list_var3 = []
list_var4 = []
list_var5 = []
list_var6 = []
list_var7 = []
list_var8 = []
list_var9 = []
list_var10 = []
list_var11 = []
list_var12 = []
list_var13 = []
list_var14 = []
list_var15 = []
list_var16 = []
for row in cell_range100:
for cell in row:
list_var100.append((cell.value))
for row in cell_range:
for cell in row:
list_var.append(cell.value)
for row in cell_range2:
for cell in row:
list_var2.append(cell.value)
for row in cell_range3:
for cell in row:
list_var3.append(cell.value)
for row in cell_range4:
for cell in row:
list_var4.append(cell.value)
for row in cell_range5:
for cell in row:
list_var5.append(cell.value)
for row in cell_range6:
for cell in row:
list_var6.append(cell.value)
for row in cell_range7:
for cell in row:
list_var7.append(cell.value)
for row in cell_range8:
for cell in row:
list_var8.append(cell.value)
for row in cell_range9:
for cell in row:
list_var9.append(cell.value)
for row in cell_range10:
for cell in row:
list_var10.append(cell.value)
for row in cell_range11:
for cell in row:
list_var11.append(cell.value)
for row in cell_range12:
for cell in row:
list_var12.append(cell.value)
for row in cell_range13:
for cell in row:
list_var13.append(cell.value)
for row in cell_range14:
for cell in row:
list_var14.append(cell.value)
for row in cell_range15:
for cell in row:
list_var15.append(cell.value)
for row in cell_range16:
for cell in row:
list_var16.append(cell.value)
ws1.cell(row=4, column=3).value = "{}".format("%0.2f%%" % (sum(list_var) / len(list_var)))
ws1.cell(row=5, column=3).value = "{}".format("%0.2f%%" % (sum(list_var2) / len(list_var2)))
ws1.cell(row=6, column=3).value = "{}".format("%0.2f%%" % (sum(list_var3) / len(list_var3)))
ws1.cell(row=7, column=3).value = "{}".format("%0.2f%%" % (sum(list_var4) / len(list_var4)))
ws1.cell(row=8, column=3).value = "{}".format("%0.2f%%" % (sum(list_var5) / len(list_var5)))
ws1.cell(row=9, column=3).value = "{}".format("%0.2f%%" % (sum(list_var6) / len(list_var6)))
ws1.cell(row=10, column=3).value = "{}".format("%0.2f%%" % (sum(list_var7) / len(list_var7)))
ws1.cell(row=11, column=3).value = "{}".format("%0.2f%%" % (sum(list_var8) / len(list_var8)))
ws1.cell(row=12, column=3).value = "{}".format("%0.2f%%" % (sum(list_var9) / len(list_var9)))
ws1.cell(row=13, column=3).value = "{}".format("%0.2f%%" % (sum(list_var10) / len(list_var10)))
ws1.cell(row=14, column=3).value = "{}".format("%0.2f%%" % (sum(list_var11) / len(list_var11)))
ws1.cell(row=15, column=3).value = "{}".format("%0.2f%%" % (sum(list_var12) / len(list_var12)))
ws1.cell(row=16, column=3).value = "{}".format("%0.2f%%" % (sum(list_var13) / len(list_var13)))
ws1.cell(row=17, column=3).value = "{}".format("%0.2f%%" % (sum(list_var14) / len(list_var14)))
ws1.cell(row=18, column=3).value = "{}".format("%0.2f%%" % (sum(list_var15) / len(list_var15)))
ws1.cell(row=19, column=3).value = "{}".format("%0.2f%%" % (sum(list_var16) / len(list_var16)))
ws1.cell(row=4, column=4).value = "{}".format("%0.2f%%" % (max(list_var)))
ws1.cell(row=5, column=4).value = "{}".format("%0.2f%%" % (max(list_var2)))
ws1.cell(row=6, column=4).value = "{}".format("%0.2f%%" % (max(list_var3)))
ws1.cell(row=7, column=4).value = "{}".format("%0.2f%%" % (max(list_var4)))
ws1.cell(row=8, column=4).value = "{}".format("%0.2f%%" % (max(list_var5)))
ws1.cell(row=9, column=4).value = "{}".format("%0.2f%%" % (max(list_var6)))
ws1.cell(row=10, column=4).value = "{}".format("%0.2f%%" % (max(list_var7)))
ws1.cell(row=11, column=4).value = "{}".format("%0.2f%%" % (max(list_var8)))
ws1.cell(row=12, column=4).value = "{}".format("%0.2f%%" % (max(list_var9)))
ws1.cell(row=13, column=4).value = "{}".format("%0.2f%%" % (max(list_var10)))
ws1.cell(row=14, column=4).value = "{}".format("%0.2f%%" % (max(list_var11)))
ws1.cell(row=15, column=4).value = "{}".format("%0.2f%%" % (max(list_var12)))
ws1.cell(row=16, column=4).value = "{}".format("%0.2f%%" % (max(list_var13)))
ws1.cell(row=17, column=4).value = "{}".format("%0.2f%%" % (max(list_var14)))
ws1.cell(row=18, column=4).value = "{}".format("%0.2f%%" % (max(list_var15)))
ws1.cell(row=19, column=4).value = "{}".format("%0.2f%%" % (max(list_var16)))
for row in cell_range:
for cell in row:
if cell.value == max(list_var):
for row in cell_range100:
for cell500 in row:
if cell500.coordinate[1:4] == cell.coordinate[1:4]:
ws1.cell(row=4, column=5).value = "{}".format(cell500.value)
for row in cell_range2:
for cell1 in row:
if cell1.value == max(list_var2):
for row in cell_range100:
for cell501 in row:
if cell501.coordinate[1:4] == cell1.coordinate[1:4]:
ws1.cell(row=5, column=5).value = "{}".format(cell501.value)
for row in cell_range3:
for cell in row:
if cell.value == max(list_var3):
for row in cell_range100:
for cell502 in row:
if cell502.coordinate[1:4] == cell.coordinate[1:4]:
ws1.cell(row=6, column=5).value = "{}".format(cell502.value)
for row in cell_range4:
for cell in row:
if cell.value == max(list_var4):
for row in cell_range100:
for cell503 in row:
if cell503.coordinate[1:4] == cell.coordinate[1:4]:
ws1.cell(row=7, column=5).value = "{}".format(cell503.value)
for row in cell_range5:
for cell in row:
if cell.value == max(list_var5):
for row in cell_range100:
for cell504 in row:
if cell504.coordinate[1:4] == cell.coordinate[1:4]:
ws1.cell(row=8, column=5).value = "{}".format(cell504.value)
for row in cell_range6:
for cell in row:
if cell.value == max(list_var6):
for row in cell_range100:
for cell505 in row:
if cell505.coordinate[1:4] == cell.coordinate[1:4]:
ws1.cell(row=9, column=5).value = "{}".format(cell505.value)
for row in cell_range7:
for cell in row:
if cell.value == max(list_var7):
for row in cell_range100:
for cell506 in row:
if cell506.coordinate[1:4] == cell.coordinate[1:4]:
ws1.cell(row=10, column=5).value = "{}".format(cell506.value)
for row in cell_range8:
for cell in row:
if cell.value == max(list_var8):
for row in cell_range100:
for cell507 in row:
if cell507.coordinate[1:4] == cell.coordinate[1:4]:
ws1.cell(row=11, column=5).value = "{}".format(cell507.value)
for row in cell_range9:
for cell in row:
if cell.value == max(list_var9):
for row in cell_range100:
for cell508 in row:
if cell508.coordinate[1:4] == cell.coordinate[1:4]:
ws1.cell(row=12, column=5).value = "{}".format(cell508.value)
for row in cell_range10:
for cell in row:
if cell.value == max(list_var10):
for row in cell_range100:
for cell509 in row:
if cell509.coordinate[1:4] == cell.coordinate[1:4]:
ws1.cell(row=13, column=5).value = "{}".format(cell509.value)
for row in cell_range11:
for cell in row:
if cell.value == max(list_var11):
for row in cell_range100:
for cell510 in row:
if cell510.coordinate[1:4] == cell.coordinate[1:4]:
ws1.cell(row=14, column=5).value = "{}".format(cell510.value)
for row in cell_range12:
for cell in row:
if cell.value == max(list_var12):
for row in cell_range100:
for cell511 in row:
if cell511.coordinate[1:4] == cell.coordinate[1:4]:
ws1.cell(row=15, column=5).value = "{}".format(cell511.value)
for row in cell_range13:
for cell in row:
if cell.value == max(list_var13):
for row in cell_range100:
for cell512 in row:
if cell512.coordinate[1:4] == cell.coordinate[1:4]:
ws1.cell(row=16, column=5).value = "{}".format(cell512.value)
for row in cell_range14:
for cell in row:
if cell.value == max(list_var14):
for row in cell_range100:
for cell513 in row:
if cell513.coordinate[1:4] == cell.coordinate[1:4]:
ws1.cell(row=17, column=5).value = "{}".format(cell513.value)
for row in cell_range15:
for cell in row:
if cell.value == max(list_var15):
for row in cell_range100:
for cell514 in row:
if cell514.coordinate[1:4] == cell.coordinate[1:4]:
ws1.cell(row=18, column=5).value = "{}".format(cell514.value)
for row in cell_range16:
for cell in row:
if cell.value == max(list_var16):
for row in cell_range100:
for cell515 in row:
