#!/usr/bin/env python3 """ diff_xlsx_range.py Compare the cell contents of all sheets in two Excel .xlsx files. Usage: python diff_xlsx_range.py old.xlsx new.xlsx python diff_xlsx_range.py old.xlsx new.xlsx --cols B:H --rows 8:200 Notes: - Formulas are compared as formulas, not as calculated values. - Only cell contents are compared. Cell styles, widths, colors, comments, merged-cell settings, charts, and images are not compared. - By default, columns A:D and all rows are compared. """ from __future__ import annotations import argparse import re import sys from pathlib import Path from typing import Any, Dict, Iterable, Optional, Tuple from openpyxl import load_workbook from openpyxl.utils import column_index_from_string, get_column_letter CellKey = Tuple[int, int] CellMap = Dict[CellKey, Any] Range1D = Tuple[Optional[int], Optional[int]] def format_value(value: Any) -> str: """Return a readable representation of an Excel cell value.""" if value is None: return "" return repr(value) def cell_address(row: int, col: int) -> str: """Convert 1-based row/column indexes to an Excel address such as A1.""" return f"{get_column_letter(col)}{row}" def parse_col_token(token: str) -> int: """Parse a column token such as A, AD, 1, or 28.""" token = token.strip() if not token: raise ValueError("empty column token") if token.isdigit(): idx = int(token) else: if not re.fullmatch(r"[A-Za-z]+", token): raise ValueError(f"invalid column token: {token!r}") idx = column_index_from_string(token.upper()) if idx < 1: raise ValueError(f"column index must be >= 1: {token!r}") return idx def parse_row_token(token: str) -> int: """Parse a row token such as 8 or 100.""" token = token.strip() if not token or not token.isdigit(): raise ValueError(f"invalid row token: {token!r}") idx = int(token) if idx < 1: raise ValueError(f"row index must be >= 1: {token!r}") return idx def parse_col_range(spec: str) -> Range1D: """ Parse a column range specification. Supported forms: A:D, A-D, A, 1:4, all """ spec = spec.strip() if spec.lower() == "all": return None, None sep = ":" if ":" in spec else "-" if "-" in spec else None if sep is None: idx = parse_col_token(spec) return idx, idx left, right = spec.split(sep, 1) left = left.strip() right = right.strip() min_col = parse_col_token(left) if left else None max_col = parse_col_token(right) if right else None if min_col is not None and max_col is not None and min_col > max_col: raise ValueError(f"invalid column range: start > end: {spec!r}") return min_col, max_col def parse_row_range(spec: str) -> Range1D: """ Parse a row range specification. Supported forms: 8:100, 8-100, 8:, 8-, :100, 8, all """ spec = spec.strip() if spec.lower() == "all": return None, None sep = ":" if ":" in spec else "-" if "-" in spec else None if sep is None: idx = parse_row_token(spec) return idx, idx left, right = spec.split(sep, 1) left = left.strip() right = right.strip() min_row = parse_row_token(left) if left else None max_row = parse_row_token(right) if right else None if min_row is not None and max_row is not None and min_row > max_row: raise ValueError(f"invalid row range: start > end: {spec!r}") return min_row, max_row def range_label(kind: str, rng: Range1D) -> str: """Return a human-readable label for a row or column range.""" start, end = rng if start is None and end is None: return "all" if kind == "col": def fmt(x: Optional[int]) -> str: return "" if x is None else get_column_letter(x) else: def fmt(x: Optional[int]) -> str: return "" if x is None else str(x) if start == end and start is not None: return fmt(start) return f"{fmt(start)}:{fmt(end)}" def in_range(value: int, rng: Range1D) -> bool: """Return True when a 1-based index is inside an optional inclusive range.""" start, end = rng if start is not None and value < start: return False if end is not None and value > end: return False return True def read_non_empty_cells(ws, col_range: Range1D, row_range: Range1D) -> CellMap: """ Return a dictionary of non-empty cells in the selected range. openpyxl's ws.max_row/ws.max_column can include cells that are only styled. Iterating rows and keeping only cells whose value is not None avoids reporting style-only blank cells as content differences. """ min_col, max_col = col_range min_row, max_row = row_range cells: CellMap = {} for row in ws.iter_rows( min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col, ): for cell in row: if not in_range(cell.row, row_range): continue if not in_range(cell.column, col_range): continue if cell.value is not None: cells[(cell.row, cell.column)] = cell.value return cells def sorted_cell_keys(keys: Iterable[CellKey]) -> list[CellKey]: """Sort cell keys in normal worksheet order: row first, then column.""" return sorted(keys, key=lambda rc: (rc[0], rc[1])) def compare_sheet( sheet_name: str, ws1, ws2, label1: str, label2: str, col_range: Range1D, row_range: Range1D, compact: bool = False, ) -> int: """Compare one worksheet and print differences. Return difference count.""" cells1 = read_non_empty_cells(ws1, col_range=col_range, row_range=row_range) cells2 = read_non_empty_cells(ws2, col_range=col_range, row_range=row_range) diff_count = 0 all_keys = set(cells1) | set(cells2) for row, col in sorted_cell_keys(all_keys): v1 = cells1.get((row, col)) v2 = cells2.get((row, col)) if v1 != v2: addr = cell_address(row, col) if compact: print(f"[{sheet_name}] {addr}: {format_value(v1)}: {format_value(v2)}") else: print(f"[{sheet_name}] {addr}") print(f" {label1}: {format_value(v1)}") print(f" {label2}: {format_value(v2)}") diff_count += 1 return diff_count def compare_workbooks( path1: Path, path2: Path, col_range: Range1D, row_range: Range1D, compact: bool = False, ) -> int: """Compare all worksheets in two workbooks. Return total difference count.""" wb1 = load_workbook(path1, data_only=False, read_only=False) wb2 = load_workbook(path2, data_only=False, read_only=False) sheet_names1 = set(wb1.sheetnames) sheet_names2 = set(wb2.sheetnames) total_diffs = 0 only1 = sorted(sheet_names1 - sheet_names2) only2 = sorted(sheet_names2 - sheet_names1) for name in only1: print(f"[sheet only in {path1.name}] {name}") total_diffs += 1 for name in only2: print(f"[sheet only in {path2.name}] {name}") total_diffs += 1 # Compare common sheets in the order used by the first workbook. common = [name for name in wb1.sheetnames if name in sheet_names2] for name in common: total_diffs += compare_sheet( name, wb1[name], wb2[name], path1.name, path2.name, col_range=col_range, row_range=row_range, compact=compact, ) return total_diffs def make_parser() -> argparse.ArgumentParser: parser = argparse.ArgumentParser( description="Compare cell contents of all sheets in two Excel .xlsx files." ) parser.add_argument("file1", type=Path, help="first Excel .xlsx file") parser.add_argument("file2", type=Path, help="second Excel .xlsx file") parser.add_argument( "--cols", default="A:D", help="columns to compare: A:D, A-D, A, 1:4, or all. Default: A:D", ) parser.add_argument( "--rows", default="all", help="rows to compare: 8:100, 8:, :100, 8, or all. Default: all", ) parser.add_argument( "--compact", action="store_true", help="print each difference on one line", ) return parser def main(argv: Optional[list[str]] = None) -> int: parser = make_parser() args = parser.parse_args(argv) path1: Path = args.file1 path2: Path = args.file2 if not path1.exists(): print(f"Error: file not found: {path1}", file=sys.stderr) return 2 if not path2.exists(): print(f"Error: file not found: {path2}", file=sys.stderr) return 2 try: col_range = parse_col_range(args.cols) row_range = parse_row_range(args.rows) except ValueError as exc: print(f"Error: {exc}", file=sys.stderr) return 2 print(f"range: columns {range_label('col', col_range)}, rows {range_label('row', row_range)}") total_diffs = compare_workbooks( path1, path2, col_range=col_range, row_range=row_range, compact=args.compact, ) if total_diffs == 0: print("No differences found.") return 0 print(f"\nTotal differences: {total_diffs}") return 1 if __name__ == "__main__": raise SystemExit(main())