Technical Article

Diff Two Excel Workbooks in Delphi with HotXLS

Two copies of the same quarterly workbook can both show 1,250.00 in cell D2 and still disagree about what that cell is: in one it is a typed literal, in the other it is =SUM(A2:C2). A reviewer flipping between the files sees identical screens. A byte comparison of the two .xlsx packages goes the other way and reports almost everything as different, because a zip archive written twice rarely matches byte for byte. Neither check answers the question people actually ask after a review round, which is which cells, formulas and sheets changed

That question comes up in a few predictable places. A finance workbook returns from review, a nightly export is regenerated after a code change, or two departments send back their own edits of one template. Side-by-side inspection is fine for one sheet and hopeless for twenty, and it is exactly the formula-versus-literal swap above that a tired pair of eyes misses

Overview diagram of HotXLS workbook comparison in Delphi: baseline.xlsx and reviewed.xlsx feed TXLSXWorkbookCompare, which pairs sheets by name, walks stored cells, and emits structured difference records plus a readable report
TXLSXWorkbookCompare turns two loaded HotXLS workbooks into one structured diff list for logic and one readable report for reviewers

Comparing two workbooks with TXLSXWorkbookCompare

HotXLS compares workbooks through TXLSXWorkbookCompare in the lxCompare unit. Compare(Left, Right) takes two loaded TXLSXWorkbook objects and returns the number of differences found. The difference list is then available by index through Diff(I) and Count, and Report appends one readable line per difference to any TStrings. Excel is not involved at any point; the comparison runs on the object model HotXLS already loaded, so it works the same in a console job, a service or a C++Builder application

uses
  SysUtils, Classes, lxHandleX, lxCompare;

var
  Left, Right: TXLSXWorkbook;
  Cmp: TXLSXWorkbookCompare;
  Lines: TStringList;
begin
  Left := TXLSXWorkbook.Create;
  Right := TXLSXWorkbook.Create;
  Cmp := TXLSXWorkbookCompare.Create;
  Lines := TStringList.Create;
  try
    // Open returns 1 on success; a failed open must stop here,
    // otherwise an empty workbook is compared and the result means nothing
    if Left.Open('baseline.xlsx') <> 1 then
      raise Exception.Create('cannot open baseline.xlsx');
    if Right.Open('reviewed.xlsx') <> 1 then
      raise Exception.Create('cannot open reviewed.xlsx');

    if Cmp.Compare(Left, Right) = 0 then
      Writeln('workbooks are equivalent')
    else
    begin
      Cmp.Report(Lines);              // one readable line per difference
      Lines.SaveToFile('workbook-diff.txt');
      Writeln(Format('%d difference(s) written', [Cmp.Count]));
    end;
  finally
    Lines.Free;
    Cmp.Free;
    Right.Free;
    Left.Free;
  end;
end;

The explicit open checks matter more than they look. Compare returns 0 and clears its list when either argument is nil, and two empty workbooks also compare as equal, so a zero result only means "equivalent" when both files really loaded. A typical line in workbook-diff.txt reads value changed: Data!A2: 10 -> 99

What counts as a difference by default?

With no options set, TXLSXWorkbookCompare reports eight kinds of difference: a sheet added or removed, a cell added or removed, a value changed, a formula changed, and a merged range added or removed. The enum values are lckSheetAdded, lckSheetRemoved, lckCellAdded, lckCellRemoved, lckValueChanged, lckFormulaChanged, lckMergeAdded and lckMergeRemoved. Everything is measured against the left workbook as the baseline, so an added item exists only on the right and a removed item only on the left

Diagram of the eight default HotXLS workbook difference kinds grouped into sheet, cell and merge level, each measured against the left workbook as the baseline, with zero row and column marking sheet-level and merge-level entries
By default HotXLS reports eight difference kinds across three levels, all expressed against the left workbook, so reordering sheets reports nothing at all

Sheets are paired by name, and the match ignores case, so Data on the left pairs with DATA on the right. Dragging a tab to a new position therefore produces no entries. Renaming a sheet is a different story: Q3 renamed to Q3 Final shows up as one sheet removed line and one sheet added line, because nothing in the file records that the two are the same sheet. A sheet that exists on one side only produces a single sheet-level entry rather than one line per cell inside it, which keeps the report readable when two workbooks differ in structure

Cells are paired by position. The comparison walks the stored cells of each paired sheet, and that includes cells that hold only formatting. A formatted but empty cell that exists on the left and not on the right is reported as cell removed: Data!C4 was (blank). Merged ranges compare by their A1 text, so A1:D1 widened to A1:E1 yields one merge removed and one merge added

Why does a formula and a literal with the same number report a change?

Because the formula is the authored content. When either side of a cell pair holds a formula, TXLSXWorkbookCompare compares formula text, and a formula on one side against a literal on the other is a lckFormulaChanged entry even if both display 1,250. The report line for the opening example is formula changed: Data!D2: 1250 -> =SUM(A2:C2), and the record carries LeftKind = lcvNumber and RightKind = lcvFormula. Formula text is compared exactly as stored, with a leading equals sign added for display

Literal values compare as typed values. A number compares with a number, text with text, and a number 10 against the text "10" is a change even though both cells print the same characters. The report shows the type labels whenever the kinds differ: value changed: Data!B7: [number] 10 -> [text] 10. That case is common in files that pass through CSV round trips or user copy-paste, and it is the one that breaks lookups later

A formula whose text is unchanged reports nothing by default, even when its cached result differs. For reviewing authored content that is correct. For regression-testing a generator it is too lenient, since a changed input sheet can move every total without touching a single formula. Setting lcoFormulaCachedResults in Options adds a lckFormulaCacheChanged entry whenever the cached results differ, or when one side has a cached value and the other has none, which is reported as (missing cache). For generator regression tests, turn that option on; a total that drifts with unchanged formulas is exactly the failure those tests exist to catch

Cached results are only as fresh as the last calculation, so recalculate both workbooks first when the question is "what do these formulas produce today". TXLSXWorkbook.Recalculate returns lxOk on success; with iteration off, a reference cycle returns lxErrorRef. Floating-point noise is the other source of false alarms, and SetNumericTolerance(AbsoluteTolerance, RelativeTolerance) handles it for numeric literals and cached results alike:

uses
  SysUtils, lxCalc, lxHandleX, lxCompare;

function CompareGeneratedReport(Expected, Actual: TXLSXWorkbook;
  Cmp: TXLSXWorkbookCompare): Integer;
begin
  // Bring both caches up to date before comparing results
  if (Expected.Recalculate <> lxOk) or (Actual.Recalculate <> lxOk) then
    raise Exception.Create('recalculation failed');

  Cmp.Options := [lcoFormulaCachedResults];
  // 1e-9 absolute or 1e-12 relative; both must be finite and nonnegative,
  // otherwise EArgumentException is raised and neither value changes
  Cmp.SetNumericTolerance(1e-9, 1e-12);

  Result := Cmp.Compare(Expected, Actual);
end;

Tolerances default to zero, which means exact comparison. They apply only to numbers. Dates, row and column geometry and styles always compare exactly, so a tolerance tuned for currency rounding never hides a date that moved by a second

Driving logic from the structured differences

Each TlxCompareDiff record exposes Kind, Sheet, one-based Row and Col, an A1 Ref, LeftText and RightText, and for cell entries LeftKind and RightKind. Sheet-level and merge-level entries carry zero in Row and Col. Once optional checks are enabled, row geometry entries carry only a row, column geometry entries only a column, and workbook features such as defined names carry neither, so branch on Kind rather than on zero coordinates:

var
  I, FormulaEdits: Integer;
  D: TlxCompareDiff;
begin
  FormulaEdits := 0;
  for I := 0 to Cmp.Count - 1 do
  begin
    D := Cmp.Diff(I);
    case D.Kind of
      lckFormulaChanged:
        begin
          Inc(FormulaEdits);
          Writeln(Format('%s!%s: %s => %s',
            [D.Sheet, D.Ref, D.LeftText, D.RightText]));
        end;
      lckValueChanged:
        if D.LeftKind <> D.RightKind then
          Writeln('type change: ' + D.Describe);
      lckSheetAdded, lckSheetRemoved:
        Writeln('structure: ' + D.Describe);
      lckMergeAdded, lckMergeRemoved:
        Writeln('layout: ' + D.Describe);
    end;
  end;

  // A review policy that blocks only on formula edits
  if FormulaEdits > 0 then
    raise Exception.CreateFmt(
      '%d formula change(s) need sign-off', [FormulaEdits]);
end;

The order of the list is fixed. Compare sorts the differences by sheet name, then row, column, kind and reference, so two runs over the same pair of files produce the same report text line for line. Sheet names sort by ordinal string comparison, not by tab position, and entries with row zero come first inside each sheet's group. That makes the report text a fair golden file for a regression test: commit the expected workbook-diff.txt and fail the build when the new one differs. For policy decisions, read the record fields directly and leave the descriptive line for humans

Opting into formatting, layout and object checks

The default set is a data comparison, and a cell whose number format moved from General to Currency reports nothing under it. The Options property widens the comparison one category at a time. lcoCellStyles compares the resolved style of each paired cell and lcoBlankStyleRegions does the same for formatted areas that have no stored cells, merging adjacent differences into rectangles whose far corner sits in LastRow and LastCol. lcoAxisGeometry compares row heights and column widths together with hidden, outline level and collapsed state. lcoRichTextRuns compares formatted runs inside text cells

Object-level content has its own switches: lcoDefinedNames, lcoDataValidations, lcoConditionalFormats, lcoTables, lcoDrawings (which covers in-cell images too), lcoPivotTables, lcoPivotCaches, lcoSlicerState and lcoTimelineState. Those entries put the feature's key in Ref and say through Change whether it was added, removed or changed. Defined names, pivot caches and the workbook-level slicer and timeline caches carry an empty Sheet

Read the output of these checks with the right expectations. Style, rich text and feature entries report SHA-256 digests of a normalized definition in LeftText and RightText, so the report says that cell C5's style changed but not which property did. Geometry entries are readable, for example size=15; hidden=0; outline=0; collapsed=0, and a row with no explicit height compares at the sheet's standard height, so an explicit 15-point row matches an unset one when the standard height is also 15. Keep lcoCellStyles off for content reviews. On a large workbook it turns every reformatted column into a wall of hashes that hides the three cell edits the reviewer was asked to approve

// Layout review: geometry, merges and conditional formats only
Cmp.Options := [lcoAxisGeometry, lcoConditionalFormats, lcoDataValidations];
// Cap what the object checks may capture: 32 MiB, 500,000 items
Cmp.SetFeatureLimits(32 * 1024 * 1024, 500000);
try
  Cmp.Compare(Left, Right);
except
  on E: EXLSXSemanticBudget do
    Writeln('workbooks too large for this check: ' + E.Message);
end;

The object checks capture their definitions before comparing, and SetFeatureLimits bounds that capture. The defaults are 67,108,864 bytes and 1,000,000 items; exceeding either raises EXLSXSemanticBudget. When Compare raises, the difference list from the previous successful call stays in place, so a caller that catches the exception should not read Count as the result of the failed run

Dates, inserted rows and where diffing pays off

Cells the workbook holds as dates compare as dates, exactly, and print as date(...) with the underlying numeric date value. A date on one side against a plain number on the other shows the kind labels, the usual sign that a column lost its date format on the way through some other tool. Mixed date systems are the trap here. A workbook on the 1904 date system and one on the 1900 system store the same calendar day as serials 1,462 apart, so check Date1904 on both sides before trusting a column of date changes. The background is in date serial numbers and the 1904 system

Positional pairing has one consequence worth planning for. Insert a row near the top of a sheet and every cell below it moves, so the comparison reports a run of changed, added and removed cells down the sheet, and formulas whose references were rewritten by the insert report as changed. When you control the pipeline, run one comparison before the structural edit and another after it, so each report covers one kind of change. The rewriting rules are covered in formula reference adjustment on insert and delete

Three jobs justify wiring this in. Regression testing a report generator: keep a known-good workbook, regenerate, compare with cached results on, and fail on any difference. Change review: hand the reviewer the sorted report instead of two files. Migration checks: after converting a batch of legacy workbooks, compare each result against its source, then count formatting and object features on each side with the inventory pass from the workbook audit and conversion workbench

Comparison, recalculation and conversion share one engine in HotXLS for Delphi and C++Builder, and the HotXLS Delphi spreadsheet component page lists the rest of what that engine reads and writes