https://shorturl.at/BUiOu import openpyxl from openpyxl import Workbook from openpyxl.utils import get_column_letter def compare_excels_with_external_formulas(file1_name, file2_name, output_file): wb1 = openpyxl.load_workbook(file1_name, data_only=False) wb2 = openpyxl.load_workbook(file2_name, data_only=False) output_wb = Workbook() output_wb.remove(output_wb.active) for sheet_name in wb1.sheetnames: if sheet_name not in wb2.sheetnames: print(f"Skipping sheet '{sheet_name}' as it's not in both files.") continue ws1 = wb1[sheet_name] ws2 = wb2[sheet_name] result_ws = output_wb.create_sheet(title=sheet_name) max_row = max(ws1.max_row, ws2.max_row) max_col = max(ws1.max_column, ws2.max_column) for row in range(1, max_row + 1): for col in range(1, max_col + 1): cell1 = ws1.cell(row=row, column=col) cell2 = ws2.cell(row=row, column=col) val1 = cell1.value val2 = cell2.value col_letter = get_column_letter(col) cell_ref = f"'{sheet_name}'!{col_letter}{row}" output_cell = result_ws.cell(row=row, column=col) if isinstance(val1, (int, float)) and isinstance(val2, (int, float)): # Formula with just file names, assuming all files are in same folder formula = f"=[{file1_name}]{cell_ref} - [{file2_name}]{cell_ref}" output_cell.value = formula else: # Copy the non-numeric value from file1 output_cell.value = val1 output_wb.save(output_file) print(f"Saved difference workbook with formulas as: {output_file}") Example usage compare_excels_with_external_formulas("file1.xlsx", "file2.xlsx", "difference_output.xlsx") Continue reading...
