Ein Reporting-Job läuft ein Jahr lang einwandfrei. Er erstellt eine Arbeitsmappe, füllt ein Blatt mit dem, was die Abfrage zurückgibt, und speichert es. Dann bittet ein Kunde mit einer fünfjährigen Historie um einen vollständigen Export, die Zeilenanzahl übersteigt eine Million, und der Prozess stirbt mit einem Out-of-Memory-Fehler, lange bevor die Datei auf die Festplatte geschrieben wird. Mit dem Code war nichts falsch. Er hielt lediglich die gesamte Arbeitsmappe im Arbeitsspeicher, um sie am Ende serialisieren zu können, und der benötigte Speicher wuchs im Gleichschritt mit der Anzahl der Zeilen, die geschrieben werden sollten
Die Lösung ist keine größere Maschine. Es ist ein anderes Schreibmodell. Der Streaming-Direct-Writer in HotXLS gibt das OOXML-Paket inkrementell aus, sobald Zeilen eintreffen, sodass der verwendete Speicherplatz nicht davon abhängt, wie viele Zeilen Sie schreiben. Er ist das schreibseitige Gegenstück zum Streaming-Reader: Wo der Reader ein riesiges Blatt durchläuft, ohne einen Zellenbaum aufzubauen, erzeugt der Writer eines, ohne ebenfalls einen Zellenbaum aufzubauen
Warum der normale Speicherpfad mit den Daten mitwächst
Der reguläre TXLSXWorkbook-Pfad baut zunächst ein vollständiges Objektmodell auf. Jede Zelle mit ihrem Wert, Typ und Stilverweis existiert als Objekt im Arbeitsspeicher, bis Sie „Speichern“ aufrufen; an diesem Punkt wird der gesamte Baum in das Paket serialisiert. Dieses Modell ist das richtige, wenn Sie ein Blatt lesen, bearbeiten, neu berechnen und wieder zurückschreiben möchten, denn wahlfreier Zugriff (Random Access) auf jede beliebige Zelle ist genau das, was für die Bearbeitung erforderlich ist. Es ist jedoch das falsche Modell, wenn Sie Zeilen nur in eine Richtung schütten und nie wieder zurückblicken, da Sie dafür bezahlen, jede Zeile resident im Speicher zu halten, ohne davon zu profitieren. Eine Million Zeilen von Objekten sind nun mal eine Million Zeilen von Objekten, egal, ob Sie sie jemals wieder aufrufen oder nicht
Der Streaming-Writer entfernt den Baum. Sobald eine Zelle geschrieben wird, wird sie zu Bytes im Arbeitsblattteil, und diese Bytes werden an die ZIP-Ausgabe übergeben. Der Arbeitsblatt-Stream ist der einzige Puffer, der wächst, und er wächst auf der Ausgabeseite, nicht als lebende Delphi-Objekte auf dem Heap. Was resident bleibt, ist ein fester Betrag an Verwaltungsaufwand: die Blattnamen, einige Flags, die aktuelle Zeilennummer, ein Zellenzähler. Dieses Set ändert sich zwischen Zeile eins und Zeile zehn Millionen nicht
Die Shared-String-Tabelle ist die Falle, und Inline-Strings sind der Ausweg
Die meisten Streaming-XLSX-Writer leisten gute Arbeit, bis sie auf Text stoßen. Das OOXML-Format speichert Zeichenfolgen normalerweise in einer Tabelle der gemeinsamen Zeichenfolgen (Shared-String-Tabelle): Jede eindeutige Zeichenfolge wird einmal in einen separaten Teil geschrieben, und jede Zelle, die diese Zeichenfolge enthält, trägt anstelle des Textes einen Index in die Tabelle. Es ist eine gute Platzoptimierung für Dateien, die voller sich wiederholender Beschriftungen sind, und es ist der Standard, den der normale Speicherpfad verwendet. Das Problem für einen Streaming-Writer ist jedoch brutal. Zur Deduplizierung muss die Tabelle für die gesamte Aufgabe resident bleiben, da jede noch kommende Zeile eine Zeichenfolge aus einer bereits geschriebenen Zeile wiederholen könnte, und nur eine vollständige In-Memory-Zuordnung bereits gesehener Zeichenfolgen kann den richtigen Index zuweisen. Die einzige Struktur, die ein Streaming-Writer also nicht streamen kann, ist genau die Struktur, die die Datei eigentlich klein halten soll. Textlastige Daten machen das Streaming zunichte, wegen dem Sie eigentlich gekommen sind
Der Direct-Writer umgeht die Tabelle vollständig. Zeichenfolgen werden inline geschrieben, als t="inlineStr"-Zellen, deren Text mit einem <is><t>-Element direkt in der Zelle sitzt. Es gibt keine Tabelle, die sich ansammelt, und keine Map von bereits gesehenen Zeichenfolgen, die vorgehalten werden muss, sodass Textspalten nicht mehr Speicherplatz verbrauchen als numerische. Der Kompromiss ist explizit und es wert, deutlich ausgesprochen zu werden: Inline-Strings wiederholen denselben Text überall dort, wo er vorkommt, sodass eine Datei mit vielen identischen Beschriftungen auf der Festplatte größer ist als das Äquivalent mit Shared-Strings. Sie opfern Dateigröße, um konstanten Arbeitsspeicher zu kaufen. Bei einem One-Pass-Export ist das genau der richtige Kompromiss, und die ZIP-Komprimierung fängt auf dem Weg nach draußen ohnehin einen Großteil der Wiederholungen ab
Die Stil-Tabelle kommt am Ende, mit einem einzigen Datumsformat
Stile weisen dasselbe Spannungsverhältnis wie Zeichenfolgen auf. Eine Arbeitsmappe verweist über einen Format-Teil (Styles Part) auf ihre Formatierung, und ein Streaming-Writer kann eine wachsende Palette von Stilen nicht mit Zellen synchron halten, die er bereits geflusht hat. Der Direct-Writer reagiert darauf, indem er die Stil-Tabelle klein und fest (fixed) hält und sie erst beim Schließen (Close) anstatt vorab ausgibt. Ein Standard-Zellenformat deckt gewöhnliche Zellen ab. Ein einziges Datums-Zahlenformat deckt Daten ab, registriert mit einem Formatcode von yyyy-mm-dd an einer bekannten Position in der Liste der Zellenformate
Dieses Datumsformat ist der Grund dafür, dass WriteDateTime als eigener Aufruf existiert. Excel hat keinen nativen Datumstyp; ein Datum ist lediglich eine Zahl, die ein Datumsformat trägt. WriteDateTime schreibt den Wert als einfache Seriennummer und versieht die Zelle mit eben jenem einen Datumsstil, sodass die Tabellenkalkulation ihn als Datum und nicht als fünfstellige Ganzzahl rendert. Die Seriennummer, die es schreibt, ist wichtig für die verlustfreie Umwandlung (Round-Tripping). Es speichert den TDateTime-Wert direkt im 1900-Datumssystem, was genau derselben Konvention entspricht, die auch der reguläre TXLSXWorkbook-Speicherpfad verwendet. Da sich beide Pfade auf die Seriennummer einigen, lässt sich eine vom Streaming-Writer erzeugte Datei wieder durch den HotXLS-Reader einlesen und in Excel öffnen, wobei die Daten genau Ihren Erwartungen entsprechen – ohne Off-by-one-Fehler oder böse Epochen-Überraschungen zwischen Writer und Reader
Reihenfolge ist zwingend erforderlich, da die Bytes bereits weg sind
Streaming erkauft sein Speicherprofil mit einer Regel, die Sie zwingend einhalten müssen. Die Ausgabe wird im laufenden Betrieb erzeugt und kann nicht nachträglich überarbeitet werden. Alles muss also genau in der Reihenfolge geschrieben werden, in der es in der Datei erscheint. Innerhalb einer Zeile verlaufen die Zellen in aufsteigender Spaltenreihenfolge. Innerhalb eines Arbeitsblatts verlaufen die Zeilen in aufsteigender Reihenfolge. Es gibt keinen Puffer, der den Writer Ihre Zellen im Nachhinein sortieren lässt, denn die Zeile, die Sie gerade erst geschlossen haben, besteht bereits aus Bytes im ZIP-Stream und ist nicht mehr erreichbar. Übergeben Sie ihm Spalte 5 und danach Spalte 2 in derselben Zeile, ist die Ausgabe fehlerhaft (malformed), da der Writer einfach das ausgibt, was Sie ihm geben, und zwar genau in der Sequenz, in der Sie es ihm geben
Die Zeilen-API bietet eine kleine Annehmlichkeit für den häufigsten Fall. AddRow akzeptiert einen 1-basierten Zeilenindex, aber die Übergabe von 0 bedeutet: Nimm die nächste Zeile nach der vorherigen. Eine sequenzielle Befüllung muss also keinen hochzählenden Zähler nachverfolgen und übergeben. Jedes AddRow schließt die davor liegende Zeile und jedes AddSheet schließt das davor liegende Blatt, sodass Sie eine Zeile oder ein Blatt nie explizit beenden müssen. Sie beginnen einfach das nächste, und der Writer finalisiert die noch offene Struktur für Sie
Das Escaping wird dort behandelt, wo Text in das XML eingeht
Jeder Text, den Sie schreiben, wird Teil eines XML-Dokuments. Daher müssen die fünf vordefinierten XML-Entitäten maskiert (escaped) werden, da das Paket sonst in dem Moment ungültig ist, in dem ein Wert ein kaufmännisches Und-Zeichen oder eine spitze Klammer enthält. Der Writer maskiert &, <, >, " und ' für Sie sowohl im Text von Inline-Strings als auch in Formeltexten – den beiden Stellen, an denen von Ihnen bereitgestellte Zeichen innerhalb des Markups landen. Sie übergeben einen rohen WideString, und der Writer macht ihn sicher. Ein Produktname wie Smith & Co <Ltd> oder eine Formel, die auf einen in Anführungszeichen gesetzten Blattnamen verweist, wird als wohlgeformtes XML ausgegeben, ohne dass Sie sich um jegliches Escaping kümmern müssen
Lebenszyklus und warum Destroy dennoch schließt
Das Finalisieren des Pakets ist das, was den Workbook-Teil, den Styles-Teil, die Content-Types und Relationship-Teile und schließlich das zentrale ZIP-Verzeichnis schreibt. Diese Arbeit geschieht in Close. Ein Paket, das niemals geschlossen wird, ist eine unvollständige ZIP-Datei, die kein Tabellenkalkulationsprogramm öffnen wird. Das Schließen ist also keine optionale Bereinigung, sondern genau der Schritt, der die Datei gültig macht. Um sich vor einem vergessenen Close in einem Fehlerpfad zu schützen, führt Destroy ein Best-Effort-Schließen durch, falls das Paket noch offen ist. Auf diese Weise hinterlässt die Freigabe des Writers keine Speicherlecks des zugrunde liegenden ZIP-Objekts, selbst wenn eine Ausnahme den expliziten Aufruf übersprungen hat. Das zuverlässige Muster ist weiterhin das gewöhnliche Delphi-Muster: Schreiben innerhalb eines try-Blocks, Aufruf von Close und Freigabe im finally-Block
Ein großes Blatt komplett von Anfang bis Ende streamen
Die Form der Aufgabe lautet: Beginnen, ein Blatt hinzufügen, Zeilen hineinschütten, schließen. Das folgende Beispiel schreibt eine Kopfzeile und anschließend einen langen Durchlauf typisierter Datenzeilen, wobei Zeichenfolgen, Zahlen, eine Formel ohne zwischengespeichertes Ergebnis und ein Datum gemischt werden. Der verwendete Speicher ist bei zehn Zeilen und zehn Millionen Zeilen exakt gleich, da jede Zelle den ZIP-Stream in genau dem Moment verlässt, in dem sie geschrieben wird
uses
lxDirectWrite;
procedure StreamReport(const Path: string; RowCount: Integer);
var
W: TXLSDirectWriter;
I: Integer;
begin
W := TXLSDirectWriter.Create;
try
W.BeginFile(Path);
W.AddSheet('Sales');
// Header row, written in ascending column order
W.AddRow(1);
W.WriteString(1, 'Item');
W.WriteString(2, 'Qty');
W.WriteString(3, 'Price');
W.WriteString(4, 'Total');
W.WriteString(5, 'Date');
// Data rows; pass 0 to AddRow to take the next row automatically
for I := 1 to RowCount do
begin
W.AddRow(0);
W.WriteString(1, 'Item ' + IntToStr(I));
W.WriteNumber(2, I);
W.WriteNumber(3, 1.5 + (I mod 10));
W.WriteFormula(4, Format('B%d*C%d', [I + 1, I + 1]));
W.WriteDateTime(5, EncodeDate(2026, 1, 1) + I);
end;
W.Close; // finalises the package
finally
W.Free;
end;
end;Ein zweites Blatt ist einfach ein weiteres AddSheet, bevor Sie fortfahren, und der Writer schließt das erste Blatt in dem Moment, in dem er das zweite öffnet. Boolesche Flags verwenden WriteBoolean, was eine typisierte boolesche Zelle anstelle des Textes "True" schreibt. Wenn Sie bestätigen möchten, dass die Datei fehlerfrei ist und sich verlustfrei zurückwandeln (Round-Trip) lässt, meldet die Eigenschaft CellCount, wie viele Zellen geschrieben wurden, und das anschließende Lesen des Ergebnisses mit dem Streaming-Reader sollte denselben Gesamtwert ergeben
// A second sheet of typed flags after the data sheet above
W.AddSheet('Flags');
W.AddRow(1);
W.WriteString(1, 'Name');
W.WriteString(2, 'Active');
W.AddRow(0);
W.WriteString(1, 'alpha');
W.WriteBoolean(2, True);
WriteLn(Format('wrote %d cells', [W.CellCount]));Das Schreiben in einen Stream anstelle einer Datei erfolgt mit demselben Code, nur mit BeginStream statt BeginFile. Dadurch kann ein Server die Arbeitsmappe als HTTP-Antwort oder in einen Speicherstream (Memory Stream) senden, ohne dass eine temporäre Datei auf der Festplatte benötigt wird. Der Writer besitzt den von Ihnen übergebenen Stream nicht, sodass Sie die Kontrolle über dessen Lebensdauer behalten
Wenn es sich bei der Arbeit um einen Server-Endpunkt handelt, der Arbeitsmappen bei Bedarf erstellt, zeigen die Muster in Streaming-Schreibvorgänge für Server- und Batch-Jobs, wie Sie dies in einen Request-Handler und einen geplanten Export einbinden können. Wenn es um die weitreichenderen Kosten sehr großer Arbeitsmappen geht – sowohl beim Lesen als auch beim Schreiben –, deckt der Artikel Performance großer Arbeitsmappen in Delphi ab, wohin Zeit und Speicher tatsächlich fließen. Der Streaming-Direct-Writer wird als Teil der HotXLS-Komponente für Delphi und C++Builder ausgeliefert, zusammen mit den vollständigen Lese-, Bearbeitungs- und Speicher-APIs, die an anderer Stelle in diesem Blog behandelt werden