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
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
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