I have two xlsx workbooks with the same template and multiple sheets. I am calculating the percentage difference between these workbooks for each cell and writing it to a third workbook. The problem is that performance isnt satisfactory (takes several hours to do a thousand rows each with 20 columns).
I am using the following flow:
df1 = pd.read_excel(file1, sheet_name=None)
df2 = pd.read_excel(file2, sheet_name=None)
xfile = openpyxl.load_workbook(file3)
wb1 = load_workbook(file1, data_only=True)
wb2 = load_workbook(file2, data_only=True)
for x in range(len(wb1.sheetnames)):
ws1 = wb1[wb1.sheetnames[x]]
ws2 = wb2[wb2.sheetnames[x]]
sheet_out = xfile.get_sheet_by_name(wb1.sheetnames[x])
for y in range(start, ws1.max_row+1):
row1 = [cell.value for cell in ws1[y]]
row2 = [cell.value for cell in ws2[y]]
for i in range(len(row1)):
perc_diff = percentage_difference_cells(row1[i],row2[i])
sheet_out[cell_out] = perc_diff
workbook.save(file3)
Is there any way to improve performance?