Technischer Artikel

Zwei Excel-Arbeitsmappen in Delphi mit HotXLS vergleichen

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

Übersichtsdiagramm des HotXLS-Arbeitsmappenvergleichs in Delphi: baseline.xlsx und reviewed.xlsx speisen TXLSXWorkbookCompare, das Blätter nach Name paart, gespeicherte Zellen durchläuft und strukturierte Differenz-Records plus einen lesbaren Report ausgibt
TXLSXWorkbookCompare verwandelt zwei geladene HotXLS-Arbeitsmappen in eine strukturierte Diff-Liste für die Logik und einen lesbaren Bericht für Prüfer

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

Diagramm der acht HotXLS-Arbeitsmappen-Differenzarten, gruppiert in Blatt-, Zellen- und Verbundebene, jeweils gegen die linke Arbeitsmappe als Basis gemessen, mit Zeile und Spalte null als Kennzeichnung für Blatt- und Verbundebenen-Einträge
HotXLS meldet acht Unterschiedsarten über drei Ebenen, alle ausgedrückt gegen die linke Arbeitsmappe, daher meldet das Umordnen von Blättern überhaupt nichts

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