Zwei Kopien derselben Quartalsmappe können in Zelle D2 beide 1,250.00 zeigen und sich trotzdem darüber uneinig sein, was diese Zelle überhaupt ist: In der einen ist sie ein getipptes Literal, in der anderen =SUM(A2:C2). Ein Prüfer, der zwischen den Dateien hin- und herschaltet, sieht identische Bildschirme. Ein Byte-Vergleich der beiden .xlsx-Pakete geht in die andere Richtung und meldet fast alles als unterschiedlich, denn ein zweimal geschriebenes Zip-Archiv stimmt selten byteweise überein. Keiner der beiden Checks beantwortet die Frage, die nach einer Prüfrunde wirklich gestellt wird: welche Zellen, Formeln und Blätter sich geändert haben
Diese Frage taucht an ein paar vorhersehbaren Stellen auf. Eine Finanzmappe kommt aus der Prüfung zurück, ein nächtlicher Export wird nach einer Codeänderung neu erzeugt, oder zwei Abteilungen schicken ihre eigenen Bearbeitungen derselben Vorlage zurück. Nebeneinanderlegen taugt für ein Blatt und versagt bei zwanzig, und genau der Formel-gegen-Literal-Tausch von oben ist es, den müde Augen entgehen
Zwei Arbeitsmappen mit TXLSXWorkbookCompare vergleichen
HotXLS vergleicht Arbeitsmappen über TXLSXWorkbookCompare in der Unit lxCompare. Compare(Left, Right) nimmt zwei geladene TXLSXWorkbook-Objekte entgegen und gibt die Zahl der gefundenen Unterschiede zurück. Die Unterschiedsliste steht danach indexiert über Diff(I) und Count bereit, und Report hängt an beliebige TStrings eine lesbare Zeile pro Unterschied an. Excel ist an keiner Stelle beteiligt; der Vergleich läuft auf dem Objektmodell, das HotXLS bereits geladen hat, und verhält sich darum in einem Konsolenjob, einem Dienst oder einer C++Builder-Anwendung gleich
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 gibt bei Erfolg 1 zurück; ein fehlgeschlagenes Öffnen muss hier stoppen,
// sonst wird eine leere Arbeitsmappe verglichen und das Ergebnis bedeutet nichts
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); // eine lesbare Zeile pro Unterschied
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;
Die expliziten Open-Prüfungen sind wichtiger, als sie aussehen. Compare gibt 0 zurück und leert seine Liste, wenn ein Argument nil ist, und zwei leere Arbeitsmappen vergleichen sich ebenfalls als gleich — eine Null bedeutet also nur dann "äquivalent", wenn beide Dateien wirklich geladen haben. Eine typische Zeile in workbook-diff.txt lautet value changed: Data!A2: 10 -> 99
Was zählt standardmäßig als Unterschied?
Ohne gesetzte Optionen meldet TXLSXWorkbookCompare acht Unterschiedsarten: ein hinzugefügtes oder entferntes Blatt, eine hinzugefügte oder entfernte Zelle, einen geänderten Wert, eine geänderte Formel sowie einen hinzugefügten oder entfernten verbundenen Bereich. Die Enum-Werte sind lckSheetAdded, lckSheetRemoved, lckCellAdded, lckCellRemoved, lckValueChanged, lckFormulaChanged, lckMergeAdded und lckMergeRemoved. Alles wird gegen die linke Arbeitsmappe als Basislinie gemessen, ein hinzugefügtes Element existiert also nur rechts und ein entferntes nur links
Blätter werden nach Namen gepaart, und der Vergleich ignoriert Groß- und Kleinschreibung, Data links paart sich also mit DATA rechts. Einen Reiter an eine neue Position zu ziehen erzeugt deshalb keine Einträge. Beim Umbenennen sieht es anders aus: Q3, das zu Q3 Final wird, taucht als eine sheet removed-Zeile und eine sheet added-Zeile auf, denn nichts in der Datei hält fest, dass beides dasselbe Blatt ist. Ein Blatt, das nur auf einer Seite existiert, liefert einen einzigen Eintrag auf Blattebene statt einer Zeile je Zelle darin, was den Bericht lesbar hält, wenn zwei Arbeitsmappen strukturell auseinanderliegen
Zellen werden nach Position gepaart. Der Vergleich läuft über die gespeicherten Zellen jedes gepaarten Blatts, und dazu gehören auch Zellen, die nur Formatierung tragen. Eine formatierte, aber leere Zelle, die links existiert und rechts nicht, wird als cell removed: Data!C4 was (blank) gemeldet. Verbundene Bereiche vergleichen über ihren A1-Text, aus A1:D1 verbreitert zu A1:E1 wird also ein merge removed und ein merge added
Warum melden eine Formel und ein Literal mit derselben Zahl eine Änderung?
Weil die Formel der erstellte Inhalt ist. Hält eine Seite eines Zellenpaars eine Formel, vergleicht TXLSXWorkbookCompare den Formeltext, und eine Formel auf der einen Seite gegen ein Literal auf der anderen ist ein lckFormulaChanged-Eintrag, selbst wenn beide 1,250 anzeigen. Die Berichtszeile für das Eingangsbeispiel lautet formula changed: Data!D2: 1250 -> =SUM(A2:C2), und der Datensatz trägt LeftKind = lcvNumber und RightKind = lcvFormula. Der Formeltext wird exakt so verglichen, wie er gespeichert ist, für die Anzeige kommt ein führendes Gleichheitszeichen dazu
Literale vergleichen als getippte Werte. Zahl gegen Zahl, Text gegen Text, und eine Zahl 10 gegen den Text "10" ist eine Änderung, obwohl beide Zellen dieselben Zeichen drucken. Im Bericht stehen die Typmarken, sobald die Arten auseinanderlaufen: value changed: Data!B7: [number] 10 -> [text] 10. Der Fall ist häufig bei Dateien, die einen CSV-Roundtrip oder Nutzer-Copy-Paste hinter sich haben, und er ist der, der später Nachschlageformeln bricht
Eine Formel mit unverändertem Text meldet standardmäßig nichts, selbst wenn ihr gecachtes Ergebnis abweicht. Für die Prüfung erstellter Inhalte ist das korrekt. Für Regressionstests eines Generators ist es zu nachsichtig, denn ein geändertes Eingabeblatt kann jede Summe verschieben, ohne eine einzige Formel anzufassen. lcoFormulaCachedResults in Options ergänzt einen lckFormulaCacheChanged-Eintrag, sobald die gecachten Ergebnisse abweichen oder eine Seite einen gecachten Wert hat und die andere keinen — gemeldet als (missing cache). Für Generator-Regressionstests schaltet man diese Option ein; eine Summe, die bei unveränderten Formeln driftet, ist genau der Fehler, den solche Tests fangen sollen
Gecachte Ergebnisse sind höchstens so frisch wie die letzte Berechnung — wenn die Frage lautet "was liefern diese Formeln heute", beide Arbeitsmappen zuerst neu berechnen. TXLSXWorkbook.Recalculate gibt lxOk bei Erfolg zurück; ohne Iteration meldet ein Referenzzirkel lxErrorRef. Gleitkomma-Rauschen ist die zweite Quelle falscher Alarme, und SetNumericTolerance(AbsoluteTolerance, RelativeTolerance) kümmert sich darum, für numerische Literale wie für gecachte Ergebnisse:
uses
SysUtils, lxCalc, lxHandleX, lxCompare;
function CompareGeneratedReport(Expected, Actual: TXLSXWorkbook;
Cmp: TXLSXWorkbookCompare): Integer;
begin
// Beide Caches vor dem Vergleich der Ergebnisse auf den neuesten Stand bringen
if (Expected.Recalculate <> lxOk) or (Actual.Recalculate <> lxOk) then
raise Exception.Create('recalculation failed');
Cmp.Options := [lcoFormulaCachedResults];
// 1e-9 absolut oder 1e-12 relativ; beide müssen endlich und nichtnegativ sein,
// sonst wird EArgumentException ausgelöst und keiner der Werte ändert sich
Cmp.SetNumericTolerance(1e-9, 1e-12);
Result := Cmp.Compare(Expected, Actual);
end;
Toleranzen defaulten auf null, also exakter Vergleich. Sie greifen nur bei Zahlen. Daten, Zeilen- und Spaltengeometrie sowie Stile vergleichen immer exakt, eine auf Währungsrundung abgestimmte Toleranz verdeckt also nie ein Datum, das um eine Sekunde gerückt ist
Logik aus den strukturierten Unterschieden ableiten
Jeder TlxCompareDiff-Datensatz stellt Kind, Sheet, eins-basierte Row und Col, eine A1-Ref, LeftText und RightText bereit, und bei Zelleneinträgen LeftKind und RightKind. Einträge auf Blatt- und Verbundebene führen Null in Row und Col. Sind die optionalen Prüfungen angeschaltet, tragen Einträge für Zeilengeometrie nur eine Zeile, solche für Spaltengeometrie nur eine Spalte, und Arbeitsmappen-Features wie definierte Namen gar nichts — also anhand von Kind verzweigen, nicht anhand von Null-Koordinaten:
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;
// Eine Prüfrichtlinie, die nur bei Formeländerungen blockiert
if FormulaEdits > 0 then
raise Exception.CreateFmt(
'%d formula change(s) need sign-off', [FormulaEdits]);
end;
Die Reihenfolge der Liste ist fest. Compare sortiert die Unterschiede nach Blattname, dann Zeile, Spalte, Art und Referenz, zwei Läufe über dasselbe Dateipaar erzeugen also Zeile für Zeile denselben Berichtstext. Blattnamen sortieren nach ordinalschem Stringvergleich, nicht nach Reiterposition, und Einträge mit Zeile null kommen in jeder Blattgruppe zuerst. Das macht den Berichtstext zu einer fairen Golden-File für einen Regressionstest: die erwartete workbook-diff.txt einchecken und den Build scheitern lassen, wenn's von der neuen abweicht. Für Richtlinienentscheidungen liest man die Datensatzfelder direkt und überlässt die Beschreibungszeile den Menschen
Formatierungs-, Layout- und Objektprüfungen dazuschalten
Die Default-Menge ist ein Datenvergleich, und eine Zelle, deren Zahlenformat von General zu Currency gewechselt hat, meldet darunter nichts. Die Eigenschaft Options weitet den Vergleich Kategorie für Kategorie aus. lcoCellStyles vergleicht den aufgelösten Stil jeder gepaarten Zelle und lcoBlankStyleRegions dasselbe für formatierte Bereiche ohne gespeicherte Zellen, wobei benachbarte Unterschiede zu Rechtecken verschmelzen, deren ferne Ecke in LastRow und LastCol liegt. lcoAxisGeometry vergleicht Zeilenhöhen und Spaltenbreiten mitsamt Hidden-, Gliederungsebenen- und Collapsed-Zustand. lcoRichTextRuns vergleicht formatierte Runs in Textzellen
Objektebene-Inhalt hat eigene Schalter: lcoDefinedNames, lcoDataValidations, lcoConditionalFormats, lcoTables, lcoDrawings (deckt Bilder in Zellen mit ab), lcoPivotTables, lcoPivotCaches, lcoSlicerState und lcoTimelineState. Diese Einträge packen den Schlüssel des Features in Ref und verraten über Change, ob's hinzugekommen, entfernt oder geändert wurde. Definierte Namen, Pivot-Caches und die Arbeitsmappen-Slicer- und -Timeline-Caches führen einen leeren Sheet
Die Ausgabe dieser Prüfungen mit den richtigen Erwartungen lesen. Stil-, Rich-Text- und Feature-Einträge melden SHA-256-Digests einer normalisierten Definition in LeftText und RightText, der Bericht sagt also, dass sich der Stil von Zelle C5 geändert hat, aber nicht, welche Eigenschaft's war. Geometrie-Einträge sind lesbar, etwa size=15; hidden=0; outline=0; collapsed=0, und eine Zeile ohne explizite Höhe vergleicht auf der Standardhöhe des Blatts, eine explizit auf 15 Punkt gesetzte Zeile passt also zu einer ohne, wenn's die Standardhöhe ebenfalls auf 15 bringt. Für Inhaltsprüfungen lcoCellStyles ausgeschaltet lassen. In einer großen Arbeitsmappe verwandelt sie jede neu formatierte Spalte in eine Wand aus Hashes, die die drei Zellbearbeitungen verdeckt, denen der Prüfer zustimmen soll
// Layout-Review: nur Geometrie, Verbünde und bedingte Formatierungen
Cmp.Options := [lcoAxisGeometry, lcoConditionalFormats, lcoDataValidations];
// Deckeln, was die Objektprüfungen erfassen dürfen: 32 MiB, 500.000 Einträge
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;
Die Objektprüfungen erfassen ihre Definitionen vor dem Vergleich, und SetFeatureLimits begrenzt diese Erfassung. Die Defaults sind 67.108.864 Bytes und 1.000.000 Einträge; wer eines davon überschreitet, löst EXLSXSemanticBudget aus. Löst Compare aus, bleibt die Unterschiedsliste des vorherigen erfolgreichen Aufrufs stehen, ein Aufrufer, der die Exception fängt, sollte Count also nicht als Ergebnis des gescheiterten Laufs lesen
Daten, eingefügte Zeilen und wo sich das Diffen auszahlt
Zellen, die die Arbeitsmappe als Datum führt, vergleichen als Daten — exakt — und drucken als date(...) mit dem zugrunde liegenden numerischen Datumswert. Ein Datum auf der einen Seite gegen eine schlichte Zahl auf der anderen zeigt die Typmarken, das übliche Zeichen dafür, dass eine Spalte auf dem Weg durch ein anderes Tool ihr Datumsformat verloren hat. Gemischte Datumssysteme sind hier die Falle. Eine Mappe auf dem 1904-System und eine auf dem 1900-System speichern denselben Kalendertag als Seriennummern im Abstand von 1.462, also Date1904 auf beiden Seiten prüfen, bevor man einer Spalte von Datumsänderungen vertraut. Den Hintergrund liefert Datumsseriennummern und das 1904-System
Positionspaarung hat eine Konsequenz, die man einplanen sollte. Fügt man nahe am oberen Rand eines Blatts eine Zeile ein, rückt jede Zelle darunter, der Vergleich meldet also eine Kette von geänderten, hinzugefügten und entfernten Zellen das Blatt hinunter, und Formeln, deren Referenzen der Insert umgeschrieben hat, melden sich als geändert. Wer die Pipeline kontrolliert, fährt einen Vergleich vor der strukturellen Änderung und einen danach, damit jeder Bericht eine Art von Änderung abdeckt. Die Umschreibungsregeln behandelt Formel-Referenzanpassung beim Einfügen und Löschen
Drei Jobs rechtfertigen den Einbau. Regressionstests eines Berichtsgenerators: eine bekannt gute Arbeitsmappe vorhalten, neu generieren, mit eingeschalteten gecachten Ergebnissen vergleichen und bei jedem Unterschied fehlschlagen. Änderungsprüfung: dem Prüfer den sortierten Bericht in die Hand geben statt zweier Dateien. Migrationsprüfung: nach der Konvertierung eines Stapels Legacy-Arbeitsmappen jedes Ergebnis gegen seine Quelle vergleichen und dann beidseitig Formatierungs- und Objekt-Features zählen, mit dem Inventurdurchlauf aus der Arbeitsmappen-Audit- und Konvertierungswerkbank
Vergleich, Neuberechnung und Konvertierung teilen sich eine Engine in HotXLS für Delphi und C++Builder, und die HotXLS-Delphi-Tabellenkalkulationskomponentenseite listet den Rest dessen, was diese Engine liest und schreibt