Technischer Artikel

Ein Arbeitsblatt in einer großen XLSX von Delphi aus patchen

Eine von Hand gepflegte Finanz-Arbeitsmappe sammelt Pivot-Caches, Slicer, bedingte Formate und Jahre von Zellformaten, doch der nächtliche Job muss nur die Zahlen auf einem Datenblatt austauschen. Ein vollständiges Laden und Speichern parst, baut wieder auf und deflated jeden Part erneut. Nur dieses Worksheet-Part neu zu schreiben und jeden anderen Zip-Eintrag unangetastet durchzureichen überspringt fast die ganze Arbeit

Ermöglicht wird das vom Paketformat selbst. Eine .xlsx-Datei ist ein Zip-Archiv aus XML-Parts nach den Open Packaging Conventions (ECMA-376 Part 2), und jeder Zip-Eintrag führt seine eigene Kompressionsmethode, CRC-32 und komprimierte Größe im Local Header und nochmals im Central Directory auf. Ein Eintrag kann daher von einem Archiv ins andere wandern, ohne jemals dekomprimiert zu werden, sofern der Kopierer diese drei Werte zusammen mit den rohen Bytes schreibt. Der Pivot-Cache muss nicht verstanden werden, um erhalten zu bleiben. Er muss nur kopiert werden

Warum komprimierte Bytes kopieren, statt die Arbeitsmappe neu zu speichern?

Das Kopieren auf komprimierter Ebene macht den größten Teil des Patches zu einer schlichten Stream-Kopie. In HotXLS öffnet TXLSDirectWriter.BeginPatch das Quellpaket nur lesend und übergibt jeden Eintrag, den der Patch unberührt lässt, an TZipArchive.CopyCompressedFrom, das die komprimierten Bytes, CRC und Größen des Quelleintrags direkt ins Zielarchiv schreibt. Ein konventioneller Schreibpfad zahlt ein Inflate auf dem Hinweg und ein Deflate auf dem Rückweg, und Deflate ist die teure Hälfte. Bei einer Arbeitsmappe, deren Hauptmasse ein großer Pivot-Cache plus ein paar Dutzend eingebettete Bilder sind, fließt die Zeit in das Blatt, das man tatsächlich schreibt

Die Rohkopie deckt unkomprimiert gespeicherte Einträge (Zip-Methode 0) und deflated Einträge (Methode 8) ab. Ein Eintrag mit einer anderen Kompressionsmethode wird trotzdem übertragen: Der Writer liest ihn über einen dekomprimierenden Stream und schreibt ihn wieder aus, der Part landet also so oder so im Ziel. Verzeichnis-Markereinträge tragen keinen Inhalt und werden übersprungen, weil der Writer seine eigenen erzeugt. Eine Handvoll Einträge wird nie kopiert, weil sie neu erzeugt oder bearbeitet werden: das Ziel-Worksheet und sein _rels-Part, xl/calcChain.xml, xl/_rels/workbook.xml.rels und [Content_Types].xml, plus xl/workbook.xml, wenn ein Blatt eingefügt wird

Diagramm eines HotXLS-in-place-XLSX-Patches aus Delphi: Das Ziel-Worksheet wird über AddSheet, AddRow und die Write-Aufrufe neu geschrieben, während jeder andere Paketpart über CopyCompressedFrom mit seinen komprimierten Bytes unverändert hinübergeht, und der veraltete calcChain-Part fällt weg, weil Excel ihn bei der Neuberechnung neu aufbaut
HotXLS kopiert unberührte Parts auf komprimierter Ebene und schreibt nur das Zielblatt neu, während die veraltete Berechnungskette entfernt statt kopiert wird

Ein einzelnes Arbeitsblatt mit BeginPatch an Ort und Stelle ersetzen

Die Zwei-Argument-Überladung BeginPatch(FileName, SheetName) ersetzt ein vorhandenes Arbeitsblatt und schreibt das Ergebnis an denselben Pfad zurück. Sie löst den Namen über das Sheet-Register in xl/workbook.xml und die Relationship-Map auf, die jedes Blatt mit seinem Part verbindet, vergleicht Namen ohne Groß-/Kleinschreibung und wirft eine Exception mit "worksheet not found", wenn nichts passt. Von dort an wird das Blatt mit denselben AddSheet-, AddRow- und Write*-Aufrufen geschrieben, die der Streaming-Writer für neue Dateien nutzt

var
  W: TXLSDirectWriter;
begin
  W := TXLSDirectWriter.Create;
  try
    W.BeginPatch('monthly-dashboard.xlsx', 'Data');  // an Ort und Stelle ersetzen
    W.AddSheet('Data');               // Name ist im Patch-Modus nur informativ
    W.AddRow(1);
    W.WriteString(1, 'Region');
    W.WriteString(2, 'Revenue');
    W.AddRow(2);
    W.WriteString(1, 'North');
    W.WriteNumber(2, 184320.55);
    W.AddRow(3);
    W.WriteString(1, 'South');
    W.WriteNumber(2, 97410.20);
    W.AddRow(4);
    W.WriteString(1, 'Total');
    W.WriteFormula(2, 'SUM(B2:B3)');  // ohne führendes '='
    W.Close;
  finally
    W.Free;
  end;
end;

Zwei Details in dieser Auflistung sind leicht misszuverstehen. Der Name, der AddSheet übergeben wird, benennt nichts um: Der Reiter behält den Namen, die sheetId und die Position, die er bereits in xl/workbook.xml hat, also würde AddSheet('Totals') trotzdem in Data schreiben. Und ein zweites AddSheet wirft, weil ein Patch genau ein Worksheet schreibt. Drei Reiter zu aktualisieren bedeutet drei Patches, von denen jeder das Paket noch einmal kopiert

Die Formel kommt ohne Gleichheitszeichen hinein. WriteFormula setzt ihren Text direkt in das <f>-Element der Zelle, und ECMA-376 Part 1 §18.3.1.40 speichert Formeln dort ohne das "=", das Excel in der Bearbeitungsleiste zeigt; '=SUM(B2:B3)' zu schreiben bringt ein literales Gleichheitszeichen ins XML. WriteFormula emittiert auch kein gecachetes Ergebnis. Liest etwas Downstream gecachte Werte, statt neu zu berechnen, schreibt WriteFormulaNumber die Formel zusammen mit der Zahl, zu der sie nachweislich auswertet

Die In-place-Überladung schreibt nie in das Original, während sie arbeitet. Das gepatchte Paket wird in einer Nachbardatei aufgebaut, die nach der Quelle benannt ist und .hxlpatch.tmp angehängt bekommt. Bei Close wird das Handle der Quelle freigegeben und die bereitgestellte Datei ersetzt das Original durch ein einzelnes Rename. Scheitert das Rename, wirft Close eine Exception, deren Meldung beide Pfade nennt, behält die bereitgestellte Kopie und lässt das Original, wo es war. Ein Prozess, der vor Close getötet wird, hinterlässt das Original unangetastet und eine verwaiste .hxlpatch.tmp daneben, die das nächste BeginPatch auf dieser Datei vor dem Start löscht

Ein neues Arbeitsblatt an eine Kopie der Vorlage anhängen

Die Vier-Argument-Überladung BeginPatch(SourceFileName, TargetFileName, SheetName, InsertSheet) lässt die Quelldatei unangetastet und schreibt ein neues Paket. Mit InsertSheet = False ersetzt sie das genannte Blatt in der Kopie; mit True hängt sie ein neues Worksheet unter diesem Namen an und wirft "worksheet already exists", wenn die Vorlage bereits einen Reiter mit diesem Namen hat

  // template.xlsx bleibt unangetastet; output.xlsx bekommt einen Reiter extra
  W.BeginPatch('template.xlsx', 'output.xlsx', 'Extra', True);
  W.AddSheet('Extra');
  W.AddRow(1);
  W.WriteString(1, 'Appended by the nightly job');
  W.Close;

Das Einfügen ist der Ort, an dem die Paketbuchhaltung bearbeitet wird. Der Writer scannt die vorhandenen xl/worksheets/sheetN.xml-Einträge nach der höchsten Part-Nummer, die <sheet>-Elemente nach der höchsten sheetId und die Workbook-Relationships nach der höchsten rId und nimmt jeweils den nächsten Wert. Das neue <sheet>-Element rückt unmittelbar vor </sheets>, der neue Reiter erscheint also zuletzt. Das Relationship-Attribut übernimmt das Präfix, mit dem die Quellarbeitsmappe Blätter bindet, üblicherweise r:id, aber nicht zwingend

Die Relationship-Typen folgen ebenfalls dem Quellpaket. Verwenden die Workbook-Relationships bereits die ISO/IEC-29500-Strict-Namespaces unter purl.oclc.org, wird die neue Worksheet-Relationship mit der Strict-Type-URI geschrieben; eine transitionale Arbeitsmappe bekommt die transitionale. Ein gepatchtes Strict-Workbook endet also nicht mit einer einzelnen Transitional-Relationship unter Stricten

Diagramm der beiden HotXLS-BeginPatch-Überladungen in Delphi: Die In-place-Form stellt das gepatchte Workbook in einer temporären Datei bereit und schließt mit dem atomaren Ersetzen des Originals durch ein Rename ab, sodass ein Absturz das Original unangetastet lässt; die Insert-Form schreibt nach der Operation am Sheet-Register und der Relationship-Map eine neue Datei
In-place-Patching stellt das Ergebnis in einer temporären Datei bereit, bevor der atomare Tausch erfolgt, während das Einfügen das Sheet-Register und die Relationship-Map einer separaten Zieldatei umschreibt

Styles, Strings und die Teile, die ein Patch unberührt lässt

Innerhalb eines Patches muss alles, worauf das neue Blatt zeigt, im Quellpaket bereits existieren. Strings werden deshalb inline ins Blatt geschrieben statt in xl/sharedStrings.xml, denn die Shared-String-Tabelle geht Byte für Byte hinüber und kann nicht wachsen. UseSharedStrings auf True zu setzen lässt AddSheet im Patch-Modus werfen

Dieselbe Regel ändert die Bedeutung von StyleIndex. In einem Patch ist er eine Position in der cellXfs-Liste der Quellarbeitsmappe (ECMA-376 Part 1 §18.8.10), und der Writer prüft nur, dass er zwischen 0 und 65534 liegt. Ob Index 14 in genau dieser Datei existiert, kann er nicht wissen, also geht eine falsche Zahl direkt in die Ausgabe. Der verlässlichste Weg, den richtigen zu finden: die aktuelle Version des Blatts entpacken und das s-Attribut einer Zelle lesen, die bereits das gewünschte Format trägt; diese Zahl ist die cellXfs-Position. Excel kann die Liste jederzeit erweitern, sobald jemand Zellen in der Vorlage formatiert, also schlagen Sie den Index nach jeder Vorlage-Änderung erneut nach, statt einer Konstante zu vertrauen, die vor einem Jahr geschrieben wurde

const
  // cellXfs-Position eines Datumsformats in DIESER Vorlage, entnommen aus dem
  // s="..."-Attribut einer bestehenden Datumszelle; nach Vorlage-Änderungen neu prüfen
  TemplateDateXf = 5;
begin
  ...
  W.AddRow(5);
  W.WriteString(1, 'Refreshed');
  W.WriteDateTime(2, Now, TemplateDateXf);  // expliziter Index: akzeptiert

  // W.WriteDateTime(2, Now);  wirft im Patch-Modus: ihr implizites Datumsformat
  //                           liegt in einem styles-Part, das der Patch nie schreibt
end;

Alles, was neue Paketparts hinzufügen würde, wird abgewiesen. AddTable, AddChart, die AddImage-Familie, AddComment, AddDefinedName und AddCellStyle werfen, sobald sie aufgerufen werden, und Close prüft vor dem Commit erneut, was auch bedingte Formate erwischt, die ihre eigene Füllung, Schriftfarbe, Fett- oder Kursivsetzung mitbringen, denn die brauchen neue Einträge in styles.xml. Ein abgewiesener Aufruf ist besser als ein Paket, in dem das Blatt auf einen Table-Part verweist, den niemand registriert hat

Der Replace-Modus beginnt ebenfalls bei einem leeren Blatt. Der Relationship-Part des alten Blattes wird nicht kopiert, also hängen eine Zeichnung, Kommentare oder externe Hyperlinks der vorherigen Version nicht mehr am neuen, und Spaltenbreiten, fixierte Fensterbereiche, verbundene Bereiche und Datenvalidierung aus dem alten Blatt-XML sind ebenfalls weg. Was im Blatt-XML liegt, lässt sich mit SetColumnWidth, FreezePanes, MergeCells, AddDataValidation und AddHyperlink neu schreiben. Ein auf dem Datenblatt verankerter Chart oder Bild kommt durch einen Patch nicht zurück, also halten Sie Charts auf einem eigenen Blatt und richten ihre Serien auf das Datenblatt

Die Berechnungskette fällt in beiden Moden weg. xl/calcChain.xml listet Formelzellen in der Reihenfolge, in der Excel sie zuletzt ausgewertet hat; nach einem Replace beschreiben ihre Einträge Zellen, die es in dieser Form nicht mehr gibt, und nach einem Insert haben sich die Blattindizes verschoben, auf die sie sich bezieht. Der Part bleibt außerhalb der Kopie, seine Relationship und sein Override in [Content_Types].xml werden entfernt, damit das Paket keinen fehlenden Part referenziert. Excel baut die Kette beim nächsten Neuberechnen wieder auf

Wenn die Zeilenschleife auf halbem Weg wirft

Der Destruktor von TXLSDirectWriter finalisiert das Paket, wenn Close nie aufgerufen wurde, also hinterlässt ein vergessenes Close kein halb geschriebenes Zip. Weil er finalisiert, was auch immer der Writer hält, committet eine Exception, die unter der In-place-Überladung durch Free durchläuft, trotzdem die bisher geschriebenen Zeilen und tauscht sie über das Original. Eine Schleife, die Zeilen holt und schreibt, auf halbem Weg wirft und den Writer in einem finally-Block freigibt, endet genau so

Für alles, was unbeaufsichtigt läuft: In ein explizites Ziel schreiben, das Ergebnis öffnen, prüfen und erst dann die Live-Datei selbst ersetzen. Eine Exception aus der Zeilenschleife lässt Free weiterhin die bereitgestellte Datei finalisieren, überspringt aber den Tausch, also behält die Live-Arbeitsmappe die Zahlen von gestern statt die Hälfte von heute

uses
  Windows, SysUtils, Variants, lxDirectWrite, lxHandleX;

procedure RefreshDashboard;
const
  Live = 'monthly-dashboard.xlsx';
  Staged = 'monthly-dashboard.next.xlsx';
var
  W: TXLSDirectWriter;
  Check: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  W := TXLSDirectWriter.Create;
  try
    W.BeginPatch(Live, Staged, 'Data', False);
    W.AddSheet('Data');
    WriteRefreshedRows(W);  // die eigene Zeilenschleife, kein Teil von HotXLS
    W.Close;
  finally
    W.Free;
  end;

  Check := TXLSXWorkbook.Create;
  try
    if Check.Open(Staged) <> 1 then
      raise Exception.Create('Staged workbook does not open');
    Sheet := Check.Sheets.SheetByName('Data');
    if (Sheet = nil) or (VarToStr(Sheet.Cells[2, 1].Value) <> 'North') then
      raise Exception.Create('Data sheet did not come back as written');
  finally
    Check.Free;
  end;

  // Windows-API, nicht HotXLS: die Live-Datei erst nach den Prüfungen ersetzen
  if not MoveFileEx(PChar(Staged), PChar(Live), MOVEFILE_REPLACE_EXISTING) then
    RaiseLastOSError;
end;

TXLSXWorkbook.Open gibt bei Erfolg 1 zurück, und Sheets wie Cells sind 1-basiert: Check.Sheets[1] ist der erste Reiter, Cells[2, 1] ist A2. Über einen Stichwert hinaus erwischen zwei strukturelle Prüfungen die meisten Fehler. Check.Sheets.Count sollte nach einem Replace der Anzahl der Vorlage entsprechen und nach einem Insert um genau eins darüber liegen, und eine Archivliste aus einem beliebigen Zip-Tool sollte weder xl/calcChain.xml noch einen doppelt auftretenden Eintrittsnamen zeigen. Doppelte Namen sind wichtig, weil manche Reader den letzten Eintrag mit einem Namen nehmen, wodurch zwei Blätter so aussehen, als hätten ihre Inhalte getauscht

Patchen, streamen oder die Arbeitsmappe laden?

Patchen passt, wenn die Arbeitsmappe groß ist, die Änderung auf ein Blatt beschränkt bleibt und der Rest der Datei bitgenau erhalten bleiben muss. Es ist das falsche Werkzeug, wenn die Änderung mehrere Blätter betrifft, wenn die neuen Daten Formate brauchen, die die Vorlage noch nicht definiert, wenn Charts oder Tabellen erzeugt werden müssen, oder wenn die Datei klein genug ist, dass ein normales Laden und Speichern nichts Messbares kostet. Für die Massenerzeugung von Grund auf ist der Streaming-Direct-Writer die bessere Wahl, und weil er dieselben AddRow- und Write*-Aufrufe teilt, ist der Wechsel zwischen beiden meist nur ein Tausch von BeginFile gegen BeginPatch

Braucht der Job wirklich das vollständige Objektmodell, wird die Arbeit auf Blattebene innerhalb einer geladenen Arbeitsmappe in Duplizieren von Arbeitsblättern in XLSX-Paketen behandelt. Und falls Patchen nur deshalb auf dem Tisch liegt, weil die Verarbeitung ganzer Arbeitsmappen langsam geworden ist, schaut Leistung großer Arbeitsmappen darauf, wohin Lade- und Speicherzeit und Speicher tatsächlich gehen, bevor man sich auf einen Ansatz festlegt

Der Patch-Writer, der Streaming-Writer und das vollständige TXLSXWorkbook-Objektmodell kommen alle in einer Bibliothek für Delphi und C++Builder; Ausgaben und die vollständige Funktionsliste finden sich auf der HotXLS-Delphi-Tabellenkalkulationskomponentenseite