Python Openpyxl slow performance: Read two workbooks and write to third workbook

Viewed 40

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?

0 Answers
Related