Blocchi di righe e salvataggi XLSX in streaming in HotXLS

In SpreadsheetML una colonna che porta un formato valuta richiede esattamente un elemento: <col min="2" max="2" style="3"/>. ECMA-376 Part 1 attacca uno stile cella solo in tre posti: una colonna (§18.3.1.13), una riga (§18.3.1.73) e una cella (§18.3.1.4). Restringete il formato a B2:D500000 e SpreadsheetML non ha un singolo elemento per la forma: uno stile di colonna correrebbe oltre la riga 500000 e uno stile di riga coprirebbe ogni colonna, quindi un writer finisce per emetterlo come celle solo stile, 1.499.997 di esse

Un modello di foglio di calcolo in memoria sbatte contro lo stesso muro un passo prima. Se formattare significa impostare una proprietà su ogni oggetto cella, un clic su un'intestazione di colonna alloca un milione di oggetti per portare un piccolo intero, e un processo Delphi che voleva solo un formato numerico rimane senza spazio di indirizzamento su un workbook che su disco è di qualche kilobyte. La vera domanda di design è dove vive la formattazione mentre modificate, e quando diventa dati per cella

Come HotXLS formatta un intervallo senza creare celle

HotXLS conserva la formattazione di intervallo come intervalli. Ogni setter di stile su TXLSXRange — SetNumberFormat, SetNumberFormatIndex, SetFontIndex, SetFillIndex, SetBorderIndex, SetAlignmentIndex — aggiunge un record alla lista StyleOverlays del foglio: quattro coordinate, le parti di stile che la chiamata possiede, e gli indici di pool per quelle parti. Le celle che esistono già dentro il rettangolo prendono le nuove parti subito. Le coordinate vuote restano vuote, e non viene allocato nulla per loro

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]);

    // Colonne B..D su tutte le 1.048.576 righe: un intervallo per chiamata
    Sheet.RCRange[1, 2, XlsxMaxRow, 4].SetNumberFormat('#,##0.00 "EUR"');

    // Costruite il bordo una volta, poi applicatelo per indice
    Edge := TXLSXBorder.Create;
    try
      Edge.SetAll(xlsxBorderThin);
      BorderIdx := Workbook.Borders.LookupOrAdd(Edge); // 1-based
    finally
      Edge.Free;
    end;
    Sheet.RCRange[1, 2, XlsxMaxRow, 4].SetBorderIndex(BorderIdx);

    Stats := Sheet.Cells.GetStorageStatistics;
    // Aspettatevi 3.003: tre celle di intestazione più 3.000 valori
    WriteLn('Cells: ', Stats.CellCount);
    if Workbook.SaveAs('ledger.xlsx') <> 1 then
      raise Exception.Create('Save failed');
  finally
    Workbook.Free;
  end;
end;

Le due chiamate di formattazione hanno coperto 3.145.728 coordinate e non hanno aggiunto nulla a quel conteggio. Gli intervalli partono dalla riga 1 di proposito: un intervallo di colonna piena si salva come un elemento <col>, e le intestazioni di testo nella riga 1 non sono toccate da un formato numerico comunque. Gli intervalli restituiti da RCRange, Range['A1:C5'], EntireColumn e EntireRow sono posseduti dal foglio, quindi l'esempio non li libera mai

TXLSXFormatParts è ciò che impedisce a più intervalli di darsi battaglia. I suoi membri sono xfpFont, xfpFill, xfpBorder, xfpNumberFormat, xfpAlignment, xfpProtection e xfpCellControl, e ogni setter di TXLSXRange rivendica esattamente uno di essi. Quando una cella viene risolta, gli intervalli che la coprono vengono applicati nell'ordine in cui sono stati aggiunti, ciascuno scrivendo solo le proprie parti, e la formattazione esplicita sulla cella stessa batte tutti. Un intervallo di colonna che imposta il formato numerico e un intervallo di riga che imposta un riempimento raggiungono entrambi la cella B5 intatti

Quando un record deve portare più parti, Sheet.StyleOverlays.Add(Row1, Col1, Row2, Col2, Parts, State) prende un TXLSXCellStyleState con gli indici di pool riempiti. Add taglia le coordinate alla griglia 1.048.576 × 16.384, scambia gli angoli invertiti, e restituisce nil senza registrare nulla quando Parts è vuoto. Le modifiche strutturali tengono i record al passo: InsertRows dentro un blocco formattato allarga l'intervallo del conteggio inserito, e inserire sopra di esso sposta l'intero record in giù

Perché SetBorders crea celle quando SetBorderIndex no?

TXLSXRange.SetBorders(Kind, Style, Color) deve guardare ogni cella per conto suo, perché la risposta dipende dalla posizione. Con xlsxEdgeOutline la riga in alto prende un bordo superiore, la colonna a sinistra un bordo sinistro, e l'interno niente; con xlsxEdgeInside è più o meno il contrario. La chiamata fonde anche i nuovi bordi in qualunque bordo la cella abbia già, quindi Outline seguito da Inside costruisce una griglia completa. Quella composizione per cella passa per Cells[R, C], che crea una cella quando non ne esiste. Su un blocco report di 10 × 40 è esattamente ciò che serve. Su EntireColumn(3) con xlsxEdgeAll sono 1.048.576 nuovi oggetti cella

Quindi la ripartizione è semplice. Per qualsiasi cosa più alta di uno schermo di dati, costruite il bordo una volta e applicatelo per indice, come fa il primo esempio: TXLSXBorder.SetAll, poi Workbook.Borders.LookupOrAdd per un indice 1-based, poi SetBorderIndex. Conservate SetBorders per i blocchi il cui contorno e le cui linee interne differiscono, come il blocco report di 10 × 40 sopra

Rileggere i formati senza disfare il lavoro

La stessa trappola becca il codice che solo legge. Cells[R, C] è un accessore create-on-miss: chiedetegli il formato numerico di B500000 e avete la risposta giusta più una nuova cella vuota e stilizzata a B500000 che ora verrà memorizzata e salvata. Fate girare quello su una colonna per fare audit della formattazione e avete ricostruito il milione di oggetti che l'intervallo doveva evitare, una lettura innocente alla volta

TXLSXWorksheet.GetCellVisibleFormat risolve lo stile effettivo di una coordinata senza aggiungere una cella. Fonde il predefinito del foglio, gli intervalli di riga e colonna, gli intervalli a rettangolo, gli stili di tabella e qualsiasi formattazione esplicita sulla cella. L'overload con un parametro out ABuiltinNumberFormat riporta un formato Excel integrato come 14 (data breve) separatamente da NumberFormatIndex, che punta nel pool custom NumberFormats del workbook. Cells.HasCell(R, C) risponde se esiste affatto una cella fisica

var
  Sheet: TXLSXWorksheet;
  Before, After: TXLSXCellStorageStatistics;
  State: TXLSXCellStyleState;
  Row, Builtin, Unformatted, Physical: Integer;
begin
  Sheet := Workbook.Sheets[1];  // Sheets è 1-based
  Before := Sheet.Cells.GetStorageStatistics;
  Unformatted := 0;
  Physical := 0;
  for Row := 1 to 200000 do
  begin
    // Stile effettivo, risolto senza aggiungere una cella al foglio
    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 risponde alla stessa domanda,
    // ma lascia dietro un nuovo oggetto cella per ogni riga che visita
  end;
  After := Sheet.Cells.GetStorageStatistics;
  // Entrambi i conteggi dovrebbero essere identici prima e dopo il loop
  WriteLn(Format('cells %d -> %d, styled blanks %d -> %d',
    [Before.CellCount, After.CellCount,
     Before.StyledBlankCellCount, After.StyledBlankCellCount]));
end;

Eseguite un controllo del genere una volta contro il vostro vero carico. StyledBlankCellCount conta le celle che hanno uno stile e nessun valore, formula o rich text. Se sale durante un passaggio che doveva essere read-only, qualche helper sta creando celle puramente per tenere stile, tipicamente chiamando Cells[R, C] per decidere se una cella ha bisogno di formattazione

Cosa significano i blocchi da 256 righe per il vostro codice

Le celle fisiche vivono in blocchi da 256 righe. Dentro un blocco, ogni riga popolata tiene le sue celle in una lista ordinata per colonna; una riga senza celle è solo uno slot vuoto, e un tratto di 256 righe senza celle non ha affatto un blocco. Un foglio sparso con dati nelle righe 3, 7 e 512 tiene quindi due blocchi. Il salvataggio percorre la stessa struttura in ordine row-major, che è l'ordine in cui SpreadsheetML richiede gli elementi <row> e i loro figli <c>

Il riferimento TXLSXCell che tenete resta lo stesso oggetto mentre altre celle vengono aggiunte o rimosse attorno; i blocchi riorganizzano la loro contabilità interna, non i vostri handle. I writer bulk come WriteFloatRowValues e WriteStringRowValues prendono una strada più veloce su un foglio fresco e accodano valori row-major in uno store compresso. Il primo accesso per cella attraverso Cells[R, C] converte quello store in oggetti cella una volta sola. GetStorageStatistics mostra in quale stato siete tramite PackedStorageActive e PackedCellCount, accanto a RowBlockCount e PopulatedRowCount

I pool di stile vengono risolti al momento del salvataggio. Prima che styles.xml venga scritto, HotXLS ricostruisce i pool di font, riempimento, bordo, formato numerico, allineamento e protezione perché contengano solo le voci ancora in uso, e un workbook che un utente ha ristilato per un'ora non spedisce ogni riempimento abbandonato. Le celle ancorate a un FormatIndex assoluto conservano il loro slot intatto

Salvataggi row-streamed e i numeri che lo mostrano

TXLSXWorkbook.StreamingWrite vale True per predefinito. Con essa attiva, SaveAs genera ogni <row> del foglio e la scrive dritta nello stream deflate della sua voce ZIP, quindi il più grande pezzo di XML del foglio in memoria in ogni momento è una singola riga. Con essa spenta, l'XML di ogni foglio viene prima costruito come un'unica stringa completa e compresso dopo, e la memoria di picco segue il foglio più grande. Le celle solo stile in cui si trasformano gli intervalli a rettangolo, il caso B2:D500000 dall'apertura, vengono generate dentro quel loop per riga, così aggiungono al file ma non stanno mai in memoria come un blocco di XML

Shared string e record di pivot cache seguono lo stesso schema. Ogni voce <si> o <r> viene generata e scritta per conto suo, quindi su un workbook analitico largo la tabella shared string, spesso più grande di qualsiasi singolo foglio, non deve mai esistere come un blocco di XML

Nulla di questo va preso per fede. Dopo ogni salvataggio il workbook espone quattro proprietà read-only: LastSaveUsedStreaming, LastSavePeakRowXmlChars (la riga singola più lunga, in caratteri), LastSaveBufferedWorksheetXmlChars (l'XML totale del foglio tenuto in memoria, zero quando in streaming) e LastSavePeakAuxiliaryXmlChars (la voce shared-string o pivot più lunga). LastSaveUsedStreaming è True per output .xlsx, .xltx e .xltm e per il writer binario .xlsb; l'output OpenDocument passa per il suo writer e riporta 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;

// Stesso workbook, entrambe le modalità; osservate la memoria del processo nel vostro profiler
ReportSave(Workbook, 'ledger-stream.xlsx', True);
ReportSave(Workbook, 'ledger-buffered.xlsx', False);

Sui vostri dati, la riga buffered mostra una cifra buffered dell'ordine dell'XML dell'intero foglio, mentre la riga streaming mostra zero e un peakRow non più grande della vostra riga più larga. Lasciate StreamingWrite attivo. Il flag decide solo se l'XML del foglio viene assemblato per intero prima della compressione, e LastSaveBufferedWorksheetXmlChars riporta la dimensione di quell'assemblaggio in caratteri

Dove va il resto della memoria

Una volta che celle e stili sono compatti, i prossimi maggiori consumatori su un workbook grande sono la tabella shared string e qualsiasi parte satellite il file porta: pivot cache, disegni, e XML conservato da parti che il modello a oggetti non modella. Quelle hanno le loro leve, e nessuna singola impostazione le copre tutte

Se è l'apertura il collo di bottiglia più che il salvataggio, il caricamento selettivo è il posto dove iniziare: la guida di caricamento dei fogli metadata-only e selettivo copre leggere un workbook senza pagare i fogli che non toccherete. Per il throughput del percorso di lettura su file molto grandi, vedete le note su parsing XLSX parallelo e allocatore di memoria, e per job di solo output che non hanno bisogno di un modello a oggetti, scritture in streaming per job batch su server sta meglio di qualsivoglia quantità di tuning qui

HotXLS legge e scrive XLS e XLSX da codice Delphi e C++Builder nativo senza Excel o automazione OLE, quindi ogni numero sopra viene dal vostro processo e può essere controllato lì. I formati supportati e le versioni RAD Studio sono elencati nella pagina del componente foglio di calcolo Delphi HotXLS