python xlwings won' t save the excel file in each iteration

Question

This is a bit complex to explain, please ask if there are any doubts.

I have two excel files named, initial and updated. updated always has more sheets and maybe more rows in each sheet or changed values. I am trying to compare each sheet that exists in both initial and updated files and write and highlight the changes into a new excel file.

This is the code that i have.

from pathlib import Path
import pandas as pd
import numpy as np
import xlwings as xw


initial_version = Path.cwd() / "ConfigurationReport_TEST.xlsx"
updated_version = Path.cwd() / "ConfigurationReport_DEV2.xlsx"

excel1 = pd.ExcelFile(initial_version)
excel2 = pd.ExcelFile(updated_version)

lesser_sheetnames_dict = {}
greater_sheetnames_dict  = {}

for idx, value in enumerate(excel1.sheet_names if len(excel1.sheet_names) < len(excel2.sheet_names) else excel2.sheet_names):
    lesser_sheetnames_dict[idx] = value
for idx, value in enumerate(excel1.sheet_names if len(excel1.sheet_names) > len(excel2.sheet_names) else excel2.sheet_names):
    greater_sheetnames_dict[idx] = value

print(lesser_sheetnames_dict)
print(len(lesser_sheetnames_dict))
print(len(greater_sheetnames_dict))

for sheetnum,sheetname in lesser_sheetnames_dict.items():
    if sheetname not in greater_sheetnames_dict.values():
        continue
    else:
        df1 = pd.read_excel(initial_version,sheet_name=sheetname)
        df2 = pd.read_excel(updated_version,sheet_name=sheetname)
        df1 = df1.fillna('')
        df2 = df2.fillna('')
        
        df2 = df2.reset_index()
        df3 = pd.merge(df1,df2,how='outer',indicator='Exist')
        df3 = df3.query("Exist != 'both'")
        df_highlight_right = df3.query("Exist == 'right_only'")

        df_highlight_left = df3.query("Exist == 'left_only'")

        highlight_rows_right = df_highlight_right['index'].tolist()
        highlight_rows_right = [int(row) for row in highlight_rows_right]

        first_row_in_excel = 2

        highlight_rows_right = [x + first_row_in_excel for x in highlight_rows_right]

        with xw.App(visible=False) as app:

            updated_wb = app.books.open(updated_version)
            print(updated_wb.sheets([x+1 for x in greater_sheetnames_dict.keys() if greater_sheetnames_dict[x] == sheetname][0]))
            updated_ws = updated_wb.sheets([x+1 for x in greater_sheetnames_dict.keys() if greater_sheetnames_dict[x] == sheetname][0])
            rng = updated_ws.used_range

            print(f"Used Range: {rng.address}")

            # Hightlight the rows in Excel
            for row in rng.rows:
                if row.row in highlight_rows_right:
                    row.color = (255, 71, 76)  # light red
                
            updated_wb.save(Path.cwd() / "Difference_Highlighted.xlsx")

The problem that im facing is in the with block. Ideally this code should run for each sheet that exists in both the files and highlight the changes and save it into a new file.

But in this case, it runs for each sheet that exists in both the files, but only highlights and saves the last sheet.

Being my first interaction with xlwings library, i have very little idea on how that block works. Any assistance will be much appreciated.

Answer 1

I feel stupid to post this question now. The error was because of the the scope of with block. Since it was inside the if block, it kept opening a workbook every single time, wrote to it, highlighted the changes of the current sheet that's being iterated on, then saved it. Obviously, during the last iteration(sheet) it opened the file again, wrote to it, highlighted the changes and overwritten the previously saved file.

To avoid this, I moved the with block's opening statement to before if block, and now it works perfectly as intended.

with xw.App(visible=False) as app:
    for sheetnum,sheetname in lesser_sheetnames_dict.items():
        if sheetname not in greater_sheetnames_dict.values():
            continue
        else:
          // code                    
    updated_wb.save(Path.cwd() / "Difference_Highlighted.xlsx")