HotXLS vergleicht zwei Arbeitsmappen über TXLSXWorkbookCompare, das Arbeitsblätter nach Namen paart, die befüllten Zellen jedes Paares durchläuft und meldet, was abweicht, als strukturierte Liste von Unterschiedsdatensätzen und auf Wunsch als eine lesbare Zeile pro Unterschied. Es ist keine Excel-Installation beteiligt, und der Vergleich läuft vollständig auf dem geladenen Objektmodell in Delphi oder C++Builder
Der Bedarf zeigt sich meist, sobald erstmals jemand fragt, was sich geändert hat. Eine Finanz-Arbeitsmappe kommt aus der Prüfung zurück, ein nächtlicher Export wird nach einer Codeänderung neu erzeugt, oder zwei Abteilungen schicken Versionen derselben Vorlage. Beide nebeneinander zu öffnen funktioniert bei einem Blatt und scheitert bei zwanzig. Dateien byteweise zu vergleichen beantwortet gar nichts, weil zwei Speichervorgänge derselben Arbeitsmappe sich auf eine Weise unterscheiden, die niemanden interessiert
Was zählt als Unterschied?
Der Vergleich meldet acht Arten, und die Menge ist bewusst klein gehalten: ein hinzugefügtes oder entferntes Blatt, eine hinzugefügte oder entfernte befüllte Zelle, eine Zelle, deren Wert sich geändert hat, eine Zelle, deren Formel sich geändert hat, sowie ein hinzugefügter oder entfernter verbundener Bereich. Alles wird gegen die linke Arbeitsmappe als Basislinie ausgedrückt, sodass ein hinzugefügtes Element nur rechts existiert und ein entferntes nur links
Blätter werden nach Namen gepaart, nicht nach Position. Das Umordnen von Arbeitsblättern erzeugt daher überhaupt keine Unterschiede, was fast immer das gewünschte Verhalten ist: Ein Nutzer, der einen Reiter verschiebt, ist keine Datenänderung. Ein Blatt, das nur auf einer Seite vorhanden ist, meldet einen einzigen Eintrag auf Blattebene, statt jede darin befüllte Zelle einzeln aufzuführen, was den Bericht zweier strukturell unterschiedlicher Arbeitsmappen lesbar hält, statt Tausende Zeilen lang werden zu lassen
Wert oder Formel, und wie beides verglichen wird
Jede Zelle liefert eine Signatur, und die Regel ist einfach: Eine Zelle mit einer Formel wird anhand ihres Formeltexts mit führendem Gleichheitszeichen verglichen, eine Zelle ohne Formel anhand ihres in Text umgewandelten Werts. Diese Unterscheidung ist wichtiger, als sie zunächst wirkt. Zwei Zellen können dieselbe angezeigte Zahl tragen, während die eine ein Literal und die andere eine Formel ist, und sie als gleich zu behandeln würde genau die Änderung verbergen, die in einer geprüften Arbeitsmappe am ehesten auffallen sollte
Es bedeutet zudem, dass eine Formel, deren Text unverändert ist, keinen Unterschied meldet, selbst wenn ihr zwischengespeichertes Ergebnis abweicht, was für den Vergleich erstellten Inhalts korrekt ist und falsch, wenn man Neuberechnungs-Drift erkennen möchte. Für diese zweite Frage sollte man beide Arbeitsmappen vor dem Vergleich neu berechnen, sodass die verglichenen Werte diejenigen sind, die die Formeln heute tatsächlich liefern
Einen Vergleich durchführen
Compare nimmt die beiden geladenen Arbeitsmappen entgegen und gibt die Anzahl gefundener Unterschiede zurück. Die Unterschiedsliste steht anschließend indexiert zur Verfügung oder kann in beliebige TStrings ausgegeben werden:
uses
lxHandleX, lxCompare;
var
Left, Right: TXLSXWorkbook;
Cmp: TXLSXWorkbookCompare;
Lines: TStringList;
begin
Left := TXLSXWorkbook.Create;
Right := TXLSXWorkbook.Create;
Cmp := TXLSXWorkbookCompare.Create;
Lines := TStringList.Create;
try
if (Left.Open('baseline.xlsx') <> 1) or
(Right.Open('reviewed.xlsx') <> 1) then
Exit;
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;Eine von Report erzeugte Zeile liest sich wie value changed: Data!A2: 10 -> 99, was für einen Prüfer und für eine Commit-Nachricht ausreicht. Das ist die menschenlesbare Oberfläche. Die programmatische Oberfläche ist der Unterschiedsdatensatz selbst, und dieser sollte verwendet werden, wenn der Vergleich eine Entscheidung speist statt ein Dokument
Logik aus den strukturierten Unterschieden ableiten
Jeder Unterschied gibt seine Art preis, den Blattnamen, eins-basierte Zeile und Spalte bei Einträgen auf Zellebene, eine A1-Referenz bei Einträgen auf Verbund-Ebene sowie den linken und rechten Text. Einträge auf Blatt- und Verbund-Ebene melden Zeile und Spalte als null, woran man sie ohne Prüfung der Art unterscheiden kann:
var
I: Integer;
D: TlxCompareDiff;
FormulaEdits: Integer;
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 R%dC%d: %s => %s',
[D.Sheet, D.Row, D.Col, D.LeftText, D.RightText]));
end;
lckSheetAdded, lckSheetRemoved:
Writeln(Format('structure: %s', [D.Describe]));
lckMergeAdded, lckMergeRemoved:
Writeln(Format('layout: %s at %s', [D.Describe, D.Ref]));
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;Zwei Eigenschaften der Ausgabe sind es wert, vor dem Schreiben von Assertions bekannt zu sein. Die Reihenfolge der Einträge auf Zellebene folgt der internen Durchlaufreihenfolge des Zellspeichers, daher sollten Tests reihenfolgeunabhängig geschrieben werden. Und die Formelsignatur trägt ihr eigenes führendes Gleichheitszeichen, was bedeutet, dass eine durch Verkettung gebaute Beschreibungszeichenkette ein doppeltes == zeigen kann; man sollte die Feldwerte prüfen, statt die Beschreibungszeile zu parsen, wenn das Ergebnis Logik antreibt
Wo sich der Arbeitsmappen-Diff auszahlt
Drei Anwendungen rechtfertigen die Funktion für sich allein. Regressionstests eines Berichtsgenerators: eine bekannt gute Arbeitsmappe aufbewahren, neu generieren, vergleichen und den Build bei jedem unerwarteten Unterschied fehlschlagen lassen. Änderungsprüfung: einem Prüfer den lesbaren Bericht statt zweier Dateien geben. Und Migrationsverifikation: nach der Konvertierung einer Charge von Legacy-Arbeitsmappen jedes Ergebnis mit seiner Quelle vergleichen, um zu beweisen, dass nichts verloren ging
Dieser dritte Fall passt natürlich zu den in der Arbeitsmappen-Audit- und Konvertierungswerkbank beschriebenen Bestands- und Prüfdurchläufen, bei denen das Zählen dessen, was eine Arbeitsmappe enthält, vor der Konvertierung stattfindet und der Vergleich danach. Wenn sich die Unterschiede um eingefügte Zeilen häufen, erklären die Regeln zur Referenzumschreibung in Formel-Referenzanpassung beim Einfügen und Löschen, warum scheinbar unveränderte Formeln als geändert gemeldet werden
Die Grenzen, unverblümt genannt
Der Vergleich deckt Werte, Formeln, Verbünde und das Vorhandensein von Blättern ab. Er vergleicht keine Zahlenformate, Schriftarten, Füllungen, Regeln für bedingte Formatierung, Datenüberprüfungen, Diagramme, Bilder oder definierte Namen. Eine Zelle mit identischem Wert, deren Format sich aber von Standard zu Währung geändert hat, meldet keinen Unterschied, was für einen Datenvergleich korrekt und für eine Formatierungsprüfung unzureichend ist
Zellen mit Datumswerten verdienen eine besondere Warnung: Sie werden anhand ihrer Textumwandlung verglichen, sodass eine auf dem 1904-Datumssystem gespeicherte Arbeitsmappe und eine auf dem 1900-System sich auf eine Weise gleich oder ungleich verhalten können, die überrascht, falls die zugrunde liegenden fortlaufenden Zahlen abweichen. Die Regeln des Datumssystems werden in Datumsseriennummern und das 1904-System behandelt. Wenn Formatierungs- oder Objektebenen-Treue Teil der Fragestellung ist, sollte der Diff mit einem Audit-Durchlauf kombiniert werden, der diese Merkmale auf beiden Seiten zählt
Arbeitsmappen-Vergleich, Auditing und Konvertierung laufen alle auf derselben Engine für Delphi und C++Builder; die vollständige Funktionsliste findet sich auf der HotXLS-Delphi-Tabellenkalkulationskomponentenseite