Row-Block-Speicherung und XLSX-Streaming in HotXLS

In SpreadsheetML braucht eine Spalte, die ein Währungsformat trägt, exakt ein Element: <col min="2" max="2" style="3"/>. ECMA-376 Part 1 hängt einen Zellstil nur an drei Stellen an: eine Spalte (§18.3.1.13), eine Zeile (§18.3.1.73) und eine Zelle (§18.3.1.4). Engt man das Format auf B2:D500000 ein, hat SpreadsheetML kein einzelnes Element für diese Form: Ein Spaltenstil liefe über Zeile 500000 hinaus und ein Zeilenstil würde jede Spalte decken, also gibt ein Schreiber es am Ende als reine Stil-Zellen aus, 1.499.997 davon

Ein In-Memory-Tabellenmodell stößt eine Stufe früher an dieselbe Wand. Wenn Formatierung bedeutet, eine Eigenschaft an jedem Zellobjekt zu setzen, alloziert ein Klick auf einen Spaltenkopf eine Million Objekte, um eine kleine Ganzzahl zu tragen, und ein Delphi-Prozess, der nur ein Zahlenformat wollte, geht auf einer Arbeitsmappe, die auf der Platte ein paar Kilobytes groß ist, den Adressraum aus. Die eigentliche Entwurfsfrage ist, wo Formatierung während der Bearbeitung lebt und wann sie zu Zelldaten wird

Wie HotXLS einen Bereich formatiert, ohne Zellen zu erzeugen

HotXLS hält Bereichsformatierung als Intervalle. Jeder Stil-Setter an TXLSXRange — SetNumberFormat, SetNumberFormatIndex, SetFontIndex, SetFillIndex, SetBorderIndex, SetAlignmentIndex — fügt der StyleOverlays-Liste des Arbeitsblatts einen Datensatz hinzu: vier Koordinaten, die Stil-Teile, die der Aufruf besitzt, und die Pool-Indizes für diese Teile. Zellen, die im Rechteck bereits existieren, übernehmen die neuen Teile sofort. Leere Koordinaten bleiben leer, und für sie wird nichts alloziert

var
  Workbook: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Edge: TXLSXBorder;
  BorderIdx, Row: Integer;
  Stats: TXLSXCellStorageStatistics;
begin
  Workbook := TXLSXWorkbook.Create;
  try
    Sheet := Workbook.Sheets.Add('Ledger');
    Sheet.WriteStringRowValues(1, 2, ['Net', 'Tax', 'Gross']);
    for Row := 2 to 1001 do
      Sheet.WriteFloatRowValues(Row, 2, [Row * 1.5, Row * 0.3, Row * 1.8]);

    // Spalten B..D über alle 1.048.576 Zeilen: ein Intervall je Aufruf
    Sheet.RCRange[1, 2, XlsxMaxRow, 4].SetNumberFormat('#,##0.00 "EUR"');

    // Den Rahmen einmal bauen, dann per Index anwenden
    Edge := TXLSXBorder.Create;
    try
      Edge.SetAll(xlsxBorderThin);
      BorderIdx := Workbook.Borders.LookupOrAdd(Edge); // 1-basiert
    finally
      Edge.Free;
    end;
    Sheet.RCRange[1, 2, XlsxMaxRow, 4].SetBorderIndex(BorderIdx);

    Stats := Sheet.Cells.GetStorageStatistics;
    // Erwarte 3.003: drei Kopfzellen plus 3.000 Werte
    WriteLn('Cells: ', Stats.CellCount);
    if Workbook.SaveAs('ledger.xlsx') <> 1 then
      raise Exception.Create('Save failed');
  finally
    Workbook.Free;
  end;
end;

Die beiden Formatierungsaufrufe deckten 3.145.728 Koordinaten ab und fügten dieser Zahl nichts hinzu. Die Intervalle beginnen mit Absicht bei Zeile 1: Ein Vollspalten-Intervall speichert als ein <col>-Element, und die Text-Köpfe in Zeile 1 sind von einem Zahlenformat ohnehin nicht betroffen. Bereiche, die RCRange, Range['A1:C5'], EntireColumn und EntireRow zurückgeben, gehören dem Arbeitsblatt, also gibt das Beispiel sie nie frei

TXLSXFormatParts ist das, was mehrere Intervalle davon abhält, sich zu bekämpfen. Seine Member sind xfpFont, xfpFill, xfpBorder, xfpNumberFormat, xfpAlignment, xfpProtection und xfpCellControl, und jeder TXLSXRange-Setter beansprucht genau einen davon. Wird eine Zelle aufgelöst, werden die Intervalle, die sie decken, in der Reihenfolge angewandt, in der sie hinzugefügt wurden, jedes schreibt nur seine eigenen Teile, und explizite Formatierung an der Zelle selbst gewinnt über alle. Ein Spalten-Intervall, das das Zahlenformat setzt, und ein Zeilen-Intervall, das eine Füllung setzt, erreichen Zelle B5 beide unversehrt

Wenn ein Datensatz mehrere Teile tragen soll, nimmt Sheet.StyleOverlays.Add(Row1, Col1, Row2, Col2, Parts, State) ein TXLSXCellStyleState mit eingetragenen Pool-Indizes entgegen. Add klemmt die Koordinaten auf das 1.048.576 × 16.384-Raster, tauscht verdrehte Ecken und gibt nil zurück, ohne etwas zu verzeichnen, wenn Parts leer ist. Struktur-Edits halten die Datensätze in Schritt: InsertRows innerhalb eines formatierten Blocks weitet das Intervall um die eingefügte Anzahl, und oberhalb einzufügen verschiebt den ganzen Datensatz nach unten

Warum erzeugt SetBorders Zellen, wenn SetBorderIndex es nicht tut?

TXLSXRange.SetBorders(Kind, Style, Color) muss jede Zelle einzeln ansehen, denn die Antwort hängt von der Position ab. Mit xlsxEdgeOutline bekommt die oberste Zeile eine obere Kante, die linke Spalte eine linke Kante, und das Innere nichts; mit xlsxEdgeInside ist es ungefähr umgekehrt. Der Aufruf verschmilzt die neuen Kanten außerdem mit dem Rahmen, den die Zelle bereits hat, also baut Outline gefolgt von Inside ein volles Gitter. Diese Zelle-für-Zelle-Komposition läuft über Cells[R, C], das eine Zelle erzeugt, wenn keine existiert. Auf einem 10 × 40-Report-Block ist das genau richtig. Auf EntireColumn(3) mit xlsxEdgeAll sind es 1.048.576 neue Zellobjekte

Die Aufteilung ist also einfach. Für alles, was höher ist als ein Bildschirm voll Daten, den Rahmen einmal bauen und per Index anwenden, wie es das erste Beispiel tut: TXLSXBorder.SetAll, dann Workbook.Borders.LookupOrAdd für einen 1-basierten Index, dann SetBorderIndex. SetBorders für Blöcke behalten, deren Außenrahmen und Innenlinien sich unterscheiden, etwa für den 10 × 40-Report-Block oben

Formate zurücklesen, ohne die Arbeit ungeschehen zu machen

Dieselbe Falle erwischt Code, der nur liest. Cells[R, C] ist ein Create-on-Miss-Accessor: Fragt man ihn nach dem Zahlenformat von B500000, bekommt man die richtige Antwort plus eine neue, gestylte, leere Zelle bei B500000, die nun gespeichert und geschrieben wird. Das über eine Spalte zu loopen, um Formatierung zu auditieren, baut die Million Objekte wieder auf, die das Intervall vermeiden sollte, ein harmlos aussehendes Lesen nach dem anderen

TXLSXWorksheet.GetCellVisibleFormat löst den effektiven Stil einer Koordinate auf, ohne eine Zelle hinzuzufügen. Es verschmilzt den Arbeitsblatt-Standard, Zeilen- und Spalten-Intervalle, Rechteck-Intervalle, Tabellenstile und jegliche explizite Formatierung an der Zelle. Die Überladung mit einem out ABuiltinNumberFormat-Parameter meldet ein eingebautes Excel-Format wie 14 (kurzes Datum) getrennt von NumberFormatIndex, das in den benutzerdefinierten NumberFormats-Pool der Arbeitsmappe zeigt. Cells.HasCell(R, C) beantwortet, ob überhaupt eine physische Zelle existiert

var
  Sheet: TXLSXWorksheet;
  Before, After: TXLSXCellStorageStatistics;
  State: TXLSXCellStyleState;
  Row, Builtin, Unformatted, Physical: Integer;
begin
  Sheet := Workbook.Sheets[1];  // Sheets ist 1-basiert
  Before := Sheet.Cells.GetStorageStatistics;
  Unformatted := 0;
  Physical := 0;
  for Row := 1 to 200000 do
  begin
    // Effektiver Stil, aufgelöst, ohne dem Blatt eine Zelle hinzuzufügen
    State := Sheet.GetCellVisibleFormat(Row, 2, Builtin);
    if (State.NumberFormatIndex = 0) and (Builtin = 0) then
      Inc(Unformatted);
    if Sheet.Cells.HasCell(Row, 2) then
      Inc(Physical);
    // Sheet.Cells[Row, 2].NumberFormatIndex beantwortet dieselbe Frage,
    // aber lässt für jede Zeile, die es besucht, ein neues Zellobjekt zurück
  end;
  After := Sheet.Cells.GetStorageStatistics;
  // Beide Zähler sollten vor und nach der Schleife identisch sein
  WriteLn(Format('cells %d -> %d, styled blanks %d -> %d',
    [Before.CellCount, After.CellCount,
     Before.StyledBlankCellCount, After.StyledBlankCellCount]));
end;

Eine solche Prüfung einmal gegen die echte Arbeitslast laufen lassen. StyledBlankCellCount zählt Zellen, die einen Stil und keinen Wert, keine Formel und keinen Rich-Text haben. Steigt er während eines Durchgangs, der nur lesend sein sollte, erzeugt irgendein Helfer Zellen rein für den Stil, typischerweise indem er Cells[R, C] aufruft, um zu entscheiden, ob eine Zelle Formatierung braucht

Was die 256-Zeilen-Blöcke für Ihren Code bedeuten

Physische Zellen leben in Blöcken von 256 Zeilen. Innerhalb eines Blocks hält jede belegte Zeile ihre Zellen in einer nach Spalte sortierten Liste; eine Zeile ohne Zellen ist nur ein leerer Slot, und eine 256-Zeilen-Strecke ohne Zellen hat gar keinen Block. Ein spärliches Blatt mit Daten in den Zeilen 3, 7 und 512 hält daher zwei Blöcke. Das Speichern geht dieselbe Struktur in Row-Major-Reihenfolge durch, der Reihenfolge, die SpreadsheetML für <row>-Elemente und ihre <c>-Kinder verlangt

Die TXLSXCell-Referenz, die man hält, bleibt dasselbe Objekt, während Zellen um sie herum hinzugefügt oder entfernt werden; die Blöcke reorganisieren ihre eigene Buchhaltung, nicht Ihre Handles. Bulk-Schreiber wie WriteFloatRowValues und WriteStringRowValues nehmen auf einem frischen Blatt den schnelleren Weg und hängen zeilenweise Werte in einen gepackten Speicher an. Der erste Zellzugriff über Cells[R, C] konvertiert diesen Speicher einmalig in Zellobjekte. GetStorageStatistics zeigt über PackedStorageActive und PackedCellCount, neben RowBlockCount und PopulatedRowCount, in welchem Zustand man ist

Stil-Pools werden beim Speichern gestutzt. Bevor styles.xml geschrieben wird, baut HotXLS die Font-, Fill-, Rahmen-, Zahlenformat-, Ausrichtungs- und Schutz-Pools so neu auf, dass sie nur noch Einträge enthalten, die in Gebrauch sind, und eine Arbeitsmappe, die ein Nutzer eine Stunde lang umgestylt hat, verschifft nicht jede verwaiste Füllung. Zellen, die an einen absoluten FormatIndex gepinnt sind, behalten ihren Slot unangetastet

Zeilen-Streaming-Saves und die Zahlen, die es zeigen

TXLSXWorkbook.StreamingWrite ist standardmäßig True. Eingeschaltet erzeugt SaveAs jedes Arbeitsblatt-<row> und schreibt es direkt in den Deflate-Stream seines ZIP-Eintrags, also ist das größte Stück Arbeitsblatt-XML im Speicher zu jedem Zeitpunkt eine einzelne Zeile. Ausgeschaltet wird die XML jedes Arbeitsblatts zuerst als ein kompletter String gebaut und danach komprimiert, und der Spitzenspeicher folgt dem größten Blatt. Die reinen Stil-Zellen, in die Rechteck-Intervalle übergehen — der Fall B2:D500000 aus der Eröffnung —, werden in dieser Zeilen-für-Zeilen-Schleife ebenfalls erzeugt, also wachsen sie in die Datei, sitzen aber nie als ein Block XML im Speicher

Shared Strings und Pivot-Cache-Records folgen demselben Muster. Jeder <si>- oder <r>-Eintrag wird einzeln erzeugt und geschrieben, also muss auf einem breiten Analyse-Workbook die Shared-String-Tabelle, häufig größer als jedes einzelne Arbeitsblatt, nie als ein Block XML existieren

Nichts davon muss man auf Glauben nehmen. Nach jedem Speichern legt die Arbeitsmappe vier schreibgeschützte Eigenschaften offen: LastSaveUsedStreaming, LastSavePeakRowXmlChars (die längste einzelne Zeile, in Zeichen), LastSaveBufferedWorksheetXmlChars (die gesamte Arbeitsblatt-XML im Speicher, null beim Streaming) und LastSavePeakAuxiliaryXmlChars (der längste Shared-String- oder Pivot-Eintrag). LastSaveUsedStreaming ist True für .xlsx-, .xltx- und .xltm-Ausgabe und für den binären .xlsb-Schreiber; OpenDocument-Ausgabe läuft durch ihren eigenen Schreiber und meldet False

procedure ReportSave(Workbook: TXLSXWorkbook; const FileName: string;
  Streaming: Boolean);
begin
  Workbook.StreamingWrite := Streaming;
  if Workbook.SaveAs(FileName) <> 1 then
    raise Exception.CreateFmt('Save failed: %s', [FileName]);
  WriteLn(Format('streaming=%s peakRow=%d buffered=%d peakAux=%d',
    [BoolToStr(Workbook.LastSaveUsedStreaming, True),
     Workbook.LastSavePeakRowXmlChars,
     Workbook.LastSaveBufferedWorksheetXmlChars,
     Workbook.LastSavePeakAuxiliaryXmlChars]));
end;

// Dieselbe Arbeitsmappe, beide Modi; beobachten Sie die Prozessspeicher in Ihrem Profiler
ReportSave(Workbook, 'ledger-stream.xlsx', True);
ReportSave(Workbook, 'ledger-buffered.xlsx', False);

Auf Ihren eigenen Daten zeigt die gepufferte Zeile einen buffered-Wert in der Größenordnung der ganzen Blatt-XML, während die Streaming-Zeile null und ein peakRow zeigt, nicht größer als Ihre breiteste Zeile. Lassen Sie StreamingWrite eingeschaltet. Das Flag entscheidet nur, ob Arbeitsblatt-XML vor der Komprimierung komplett zusammengesetzt wird, und LastSaveBufferedWorksheetXmlChars meldet die Größe dieser Zusammensetzung in Zeichen

Wohin der übrige Speicher geht

Sind Zellen und Stile kompakt, sind die nächsten großen Verbraucher auf einer großen Arbeitsmappe die Shared-String-Tabelle und die Satellitenteile, die die Datei trägt: Pivot-Caches, Zeichnungen und bewahrte XML aus Teilen, die das Objektmodell nicht modelliert. Die haben ihre eigenen Hebel, und keine einzige Einstellung deckt alle

Ist das Öffnen der Flaschenhals statt dem Speichern, ist selektives Laden der richtige Anfang: Der Durchgang durch Metadaten-only und selektives Arbeitsblatt-Laden deckt, eine Arbeitsmappe zu lesen, ohne für Blätter zu zahlen, die man nicht anfasst. Für Lese-Durchsatz auf sehr großen Dateien siehe die Hinweise zu parallelem XLSX-Parsing und dem Speicherallokator, und für reine Ausgabe-Jobs, die nie ein Objektmodell brauchen, passt Streaming-Write für Server-Batch-Jobs besser als jedes Maß an Tuning hier

HotXLS liest und schreibt XLS und XLSX aus nativem Delphi- und C++Builder-Code ohne Excel oder OLE-Automatisierung, also kommt jede Zahl oben aus Ihrem eigenen Prozess und lässt sich dort prüfen. Unterstützte Formate und RAD-Studio-Versionen sind auf der HotXLS-Delphi-Spreadsheet-Komponentenseite aufgeführt