Eine Tabellenkalkulation mit einer Million Zeilen und einem Dutzend Spalten ist ein völlig normaler Export aus einem Datenbank-Reporting-Job. Öffnet man sie auf dem üblichen Weg, indem man die gesamte Arbeitsmappe in ein TXLSWorkbook lädt, muss der Prozess jede einzelne dieser zwölf Millionen Zellen als lebendes Objekt instanziieren, bevor die erste Zeile Ihrer Geschäftslogik ausgeführt wird. Die Datei auf der Festplatte mag vielleicht sechzig Megabyte an komprimiertem XML groß sein. Der Objektbaum, in den sie entpackt wird, ist um ein Vielfaches größer, und er muss auf einmal im Arbeitsspeicher vorhanden sein, da das Modell von Grund auf für wahlfreien Zugriff (Random Access) ausgelegt ist. Für einen Bericht, den Sie nur von oben nach unten lesen und dann verwerfen möchten, ist das extrem viel Speicher, der für eine Struktur aufgewendet wird, die Sie eigentlich nie gebraucht hätten
Es gibt einen zweiten Weg durch dieselbe Datei. Anstatt ein Modell aufzubauen, scannen Sie das Arbeitsblatt-XML nur vorwärts (Forward-only), Zelle für Zelle, und lassen jede Zelle vorbeifließen, nachdem Sie sie betrachtet haben. Nichts staut sich an. Der Speicherverbrauch bleibt nahezu konstant, egal ob das Blatt tausend oder zehn Millionen Zeilen hat, da der Reader niemals mehr im Speicher hält als den Teil, den er gerade parst, sowie ein paar kleine Lookup-Tabellen. Genau das macht der HotXLS-Direct-Reader, und der Rest dieses Artikels handelt davon, warum er so schlank bleibt und was Sie im Gegenzug dafür erhalten
Warum das In-Memory-Modell nicht skaliert
Eine XLSX-Datei ist ein ZIP-Paket aus XML-Teilen, die durch ECMA-376 beschrieben werden. Jedes Arbeitsblatt ist ein eigener Teil, xl/worksheets/sheetN.xml, und darin ist jede Zeile ein <row>-Element, das <c>-Zellelemente enthält. Der reguläre Ladepfad liest diesen Teil und konstruiert ein adressierbares Objekt für jede Zelle, sodass Sie später Cells[12345, 7] abfragen und in konstanter Zeit eine Antwort erhalten können. Wahlfreier Zugriff ist der eigentliche Sinn eines Arbeitsmappen-Modells und genau das, was das Bearbeiten, das Auswerten von Formeln und die Formatierung so komfortabel macht
Der Preis dafür ist, dass wahlfreier Zugriff erfordert, dass alles gleichzeitig vorhanden ist. Man kann nicht auf eine Struktur indexieren, die man nur teilweise aufgebaut hat. Der Spitzen-Speicherbedarf (Peak Memory) eines vollständigen Ladevorgangs ist also eine Funktion der Zellenzahl, und bei einem Blatt mit Millionen gefüllter Zellen landet diese Funktion in Regionen, in denen sich Ihr Dienst nicht befinden möchte – erst recht nicht, wenn mehrere solcher Jobs gleichzeitig auf einem gemeinsam genutzten Server laufen. Wenn das Zugriffsmuster, das Sie tatsächlich benötigen, sequenziell ist, zahlen Sie mit dem wahlfreien Zugriff für eine Fähigkeit, die Sie gar nicht nutzen werden
Ein Forward-only-SAX-Scan, der keinen Baum aufbaut
Der Direct-Reader öffnet das ZIP-Paket und durchläuft jeden Arbeitsblatt-Teil mit einem SAX-artigen Pull-Parser. SAX bedeutet hier, dass der Parser Parse-Ereignisse meldet, sobald er auf sie stößt – ein Start-Element, ein Textabschnitt, ein End-Element – und dann weitergeht. Er behält keinen Knotenbaum im Hintergrund bei. Der Reader verfolgt die aktuelle Zeile und Spalte aus den r-Attributen, sammelt den Typ, den Stilindex, den Wert und den Formeltext der Zelle ein, während die Ereignisse eintreffen, und wenn das schließende </c>-Tag auftaucht, gibt er eine Zelle aus und vergisst sie wieder. Die nächste Zelle verwendet dieselbe Handvoll lokaler Variablen wieder
Da zwischen den Zellen nichts zurückbehalten wird, wächst der Speicherbedarf nicht mit der Anzahl der Zellen. Genau das ist die Eigenschaft, an der man festhalten sollte. Ein Blatt mit zweihundert Zeilen und ein Blatt mit zwanzig Millionen Zeilen kosten den Reader denselben residenten Arbeitsspeicher; der Unterschied zwischen beiden besteht lediglich darin, wie lange der Scan dauert. Sie geben den wahlfreien Zugriff, das Hauptmerkmal des Modells, auf und erhalten im Gegenzug eine Speicherobergrenze, die auch durch die Zellenzahl nicht durchbrochen werden kann
Was resident bleibt und warum genau diese zwei Teile
Der Scan ist nicht völlig zustandslos, und die Ausnahmen sind aufschlussreich. Zwei kleine Tabellen müssen für die Dauer des Vorgangs im Arbeitsspeicher gehalten werden, da eine Zelle allein nicht genug Informationen trägt, um sie ohne diese zu interpretieren
Die erste ist die Tabelle der gemeinsamen Zeichenfolgen (Shared String Table). In SpreadsheetML speichert eine Textzelle ihren Text nicht selbst. Sie enthält t="s" und eine numerische Nutzlast, die ein Index in xl/sharedStrings.xml ist – einer einzigen, deduplizierten Liste aller eindeutigen Zeichenfolgen in der Arbeitsmappe. Dies ist ein guter Platzkompromiss für Dateien, bei denen sich dieselben Beschriftungen über Tausende von Zeilen wiederholen, bedeutet aber, dass der Reader diese String-Tabelle vorab laden und resident halten muss, da jede Zelle überall in jedem Blatt auf jeden Eintrag darin verweisen kann. Die Größe der Tabelle wird durch die Anzahl der eindeutigen Zeichenfolgen bestimmt, nicht durch die Zellenzahl, sodass sie selbst bei riesigen Blättern bescheiden bleibt
Die zweite ist die Zahlenformat-Zuordnung aus dem Format-Teil (Styles Part). Eine numerische Zelle und eine Datumszelle sind auf der Leitung Bit für Bit identisch: Beides sind einfache Zahlen, da ein Datum in SpreadsheetML lediglich eine fortlaufende Tageszählung ist. Das Einzige, was sie voneinander unterscheidet, ist der Stil der Zelle, der über cellXfs in xl/styles.xml auf eine Zahlenformat-ID verweist. Um ein Datum als Datum und nicht als rohe Seriennummer zu melden, lädt der Reader diese Stil-zu-Format-Tabelle und hält sie resident. Alles andere in der Datei, die tatsächlichen Zellendaten, die den Großteil der Bytes ausmachen, strömen vorbei, ohne gespeichert zu werden
Jede Zelle meldet eine Art und einen Wert
Jede ausgegebene Zelle kommt als TXLSDirectCell-Record an. Dieser enthält den Blattindex und -namen, die 1-basierte Zeile und Spalte, eine semantische Kind (Art), den Value (Wert) als Variant, den Formula-Text (ohne das führende Gleichheitszeichen) und den rohen StyleIndex. Die Art ist entweder xdkNumber, xdkString, xdkBoolean, xdkDate oder xdkError, sodass Sie danach verzweigen können, was die Zelle bedeutet, anstatt es erst aus den Attributen abzuleiten. Eine Formelzelle meldet die Art ihres zwischengespeicherten Ergebnisses, zusammen mit dem Formeltext, sodass eine berechnete Summe als Zahl durchkommt, die Ihnen gleichzeitig mitteilt, wie sie erzeugt wurde
type
TReportScan = class
procedure OnCell(Sender: TObject; const Cell: TXLSDirectCell;
var Abort: Boolean);
end;
procedure TReportScan.OnCell(Sender: TObject; const Cell: TXLSDirectCell;
var Abort: Boolean);
begin
case Cell.Kind of
xdkString: AccumulateLabel(Cell.Row, Cell.Col, VarToStr(Cell.Value));
xdkNumber: AddToTotals(Cell.Col, Double(Cell.Value));
xdkDate: NoteWhen(Cell.Row, VarToDateTime(Cell.Value));
xdkBoolean: FlagRow(Cell.Row, Boolean(Cell.Value));
xdkError: LogBadCell(Cell.Row, Cell.Col, VarToStr(Cell.Value));
end;
end;Ein Datum von einer Zahl unterscheiden
Die Datumsfrage verdient einen genaueren Blick, da hier die meisten naiven Scanner falsch liegen. Auf einer numerischen Zelle gibt es keinen Datumstyp. Eine Zelle, die den Serienwert 46000 enthält, könnte eine Menge, ein Preis oder der 17. Februar 2025 sein, und die Datei verrät Ihnen dies nur durch die Zahlenformat-ID, die über den Stil der Zelle erreicht wird. ECMA-376 reserviert einen Block integrierter Format-IDs, deren Bedeutung über jeden konformen Erzeuger hinweg festgelegt ist, und die datumsführenden IDs liegen in zwei Bereichen: 14 bis 22 für die Standard-Datums- und Zeitformate und 45 bis 47 für Formate der abgelaufenen Zeit wie [h]:mm:ss. Wenn DetectDates aktiviert ist (was standardmäßig der Fall ist), löst der Reader den Stil jeder numerischen Zelle in ihre Format-ID auf, und eine Zelle, deren ID in diese reservierten Bereiche fällt, wird als xdkDate gemeldet, wobei ihr Value bereits in ein Delphi-TDateTime konvertiert wurde. Benutzerdefinierte Formate werden ebenfalls überprüft, indem der Formatcode auf Datums- und Zeittoken untersucht wird, aber die reservierten Bereiche bilden das verlässliche Rückgrat. Wenn Sie DetectDates deaktivieren, wird die Format-Tabelle nicht einmal geladen, jede numerische Zelle kommt als xdkNumber durch, und der Scan ist noch einen Bruchteil schlanker
Blätter überspringen und frühzeitig abbrechen
Das sequenzielle Scannen hat einen stillen Vorteil, den der wahlfreie Zugriff nicht bieten kann: Man kann anhalten. Das Ereignis OnSheet wird ausgelöst, bevor jedes Arbeitsblatt geöffnet wird, und es gibt Ihnen zwei Schalter an die Hand. Setzen Sie SkipSheet, wird dieser gesamte Teil niemals geparst – so scannen Sie in einer Arbeitsmappe mit mehreren Blättern nur die Blätter, die Sie interessieren, ohne den Preis für das Lesen der restlichen zu zahlen. Setzen Sie Abort, endet der gesamte Scan sofort. Das Ereignis OnCell hat seinen eigenen Abort-Schalter, sodass Sie in dem Moment anhalten können, in dem Sie das Gesuchte gefunden haben – eine bestimmte Zeile, einen Wachwert (Sentinel Value), das Ende eines Kopfzeilenblocks –, ohne die verbleibenden Millionen Zellen lesen zu müssen. Bei einem Forward-only-Scan ist ein Abbruch wirklich kostenlos, denn die Arbeit, die Sie überspringen, ist Arbeit, die noch gar nicht stattgefunden hatte
procedure TReportScan.OnSheet(Sender: TObject; SheetIndex: Integer;
const SheetName: WideString; var SkipSheet: Boolean; var Abort: Boolean);
begin
// Scan only the "Data" sheet; leave the rest unread
SkipSheet := SheetName <> 'Data';
end;Zellen ohne Handler zählen
Eine neuere Verfeinerung ist erwähnenswert, da sie eine häufige Frage in einen einzigen, billigen Aufruf verwandelt. Der Reader zählt jede gefüllte Zelle, die er passiert, und zwar unabhängig davon, ob ein OnCell-Handler angehängt ist oder nicht. Früher wurde die Anzahl der gefüllten Zellen mit Null zurückgegeben, wenn kein Handler gesetzt war, da das Zählen ein Nebeneffekt der Ausgabe war. Jetzt ist die Zählung unabhängig von der Ausgabe. Das bedeutet, dass Sie nur eine Frage stellen können – wie viele gefüllte Zellen enthält diese Arbeitsmappe eigentlich? – und die Antwort zum Preis eines Scans ganz ohne Callbacks erhalten. Sowohl ReadFile als auch ReadStream geben diese Gesamtzahl als Int64 zurück, und dieselbe Zahl ist anschließend als Eigenschaft CellCount verfügbar. Ein Rückgabewert von -1 signalisiert, dass die Datei nicht geöffnet werden konnte oder kein OOXML-Paket ist
var
Reader: TXLSDirectReader;
Populated: Int64;
begin
Reader := TXLSDirectReader.Create;
try
// No OnCell handler: a pure populated-cell census, still near-constant memory
Populated := Reader.ReadFile('quarterly_export.xlsx');
if Populated < 0 then
raise Exception.Create('Not a readable XLSX package')
else
Writeln(Format('%d populated cells (CellCount = %d)',
[Populated, Reader.CellCount]));
finally
Reader.Free;
end;
end;Für den vollständigen Scan hängen Sie den Handler an und rufen ReadFile auf genau dieselbe Weise auf. Der Kontrast zu einem vollständigen Laden ist der eigentliche Punkt: Wo das Laden von quarterly_export.xlsx in eine Arbeitsmappe jede Zelle in ein residentes Objekt erweitern und alles festhalten würde, behält der Direct-Reader nur die gemeinsamen Zeichenfolgen und die Stil-Tabelle bei, während die zwölf Millionen Zellen nacheinander durch Ihr OnCell fließen. Die Arithmetik, die pro Zelle ausgeführt wurde, hinterlässt nichts, sodass der Spitzen-Speicherbedarf durch die Anzahl der eindeutigen Zeichenfolgen der Arbeitsmappe festgelegt wird, nicht durch deren Zeilenzahl
Der Direct-Reader ist das richtige Werkzeug, wenn die Aufgabe darin besteht, eine große Arbeitsmappe einmal zu lesen und sie zu extrahieren oder zusammenzufassen. Wenn Sie stattdessen den wahlfreien Zugriff des vollständigen Modells benötigen, dieses sich aber bei großen Dateien bewähren soll, deckt das Tuning in unseren Anmerkungen zur Performance großer Arbeitsmappen in Delphi diesen Weg ab. Und wenn die Richtung umgekehrt ist und Sie große Ausgaben produzieren statt sie zu konsumieren, wendet der Streaming-Write-Walkthrough für Server-Batch-Jobs dieselbe Disziplin des konstanten Speichers auf das Schreiben an. Alle drei sind Teil der HotXLS-Komponente für Delphi und C++Builder, zusammen mit den Lese-, Schreib-, Formel- und Formatierungs-APIs, die an anderer Stelle in diesem Blog behandelt werden