Poniamo che un servizio Delphi notturno generi un XLSX per cliente, qualche centinaio di file, alcuni larghi 400.000 righe. Profilatelo e la sorpresa raramente è il ciclo che riempie le celle. È la chiamata SaveAs. Con il writer predefinito ogni foglio viene serializzato in una sola stringa XML in memoria prima che quella stringa sia compressa nello zip OOXML, e per un foglio largo la stringa transitoria può superare di molto il modello di celle da cui è stata costruita. Così un lavoro che costruisce comodamente i propri dati e si assesta a 800 MB schizza oltre il limite di 2 GB del contenitore durante il salvataggio, e il killer per esaurimento memoria apre la segnalazione alle 03:00, quando nessuno sta guardando. HotXLS, la libreria nativa di losLab per fogli di calcolo in Delphi e C++Builder, ha una proprietà mirata proprio a quel picco: StreamingWrite. Attorno a essa stanno altre due leve che decidono se un worker batch resta dentro il proprio budget di memoria e di tempo, cioè le callback di scrittura a livello di riga e il modo in cui il pool di stili si comporta dentro un ciclo stretto
Cosa mette in buffer il percorso di salvataggio predefinito, e cosa cambia StreamingWrite
Il writer XLSX predefinito predilige la semplicità. Rende completamente l'XML del foglio, poi consegna la stringa finita al compressore zip. È il compromesso giusto per la stragrande maggioranza delle cartelle di lavoro, dove l'XML dell'intero foglio sta in qualche megabyte. Smette di esserlo quando la forma serializzata di un foglio arriva a centinaia di megabyte. L'XML dei fogli di calcolo è prolisso: ogni cella numerica costa decine di caratteri di markup, e la stringa che li contiene tutti deve essere contigua. Su un grafico della memoria la firma è difficile da mancare. Un lungo altopiano piatto mentre si riempiono le righe, poi un picco triangolare netto durante SaveAs, poi il crollo una volta scaricato lo zip
Impostare Book.StreamingWrite := True commuta SaveAs su un writer di fogli che emette l'XML direttamente nel flusso zip mentre lo genera. La stringa intermedia non viene mai allocata, e il picco triangolare si appiattisce nel rumore di fondo
Siate precisi su cosa vi compra davvero, perché sopravvalutarlo porta a piani di capacità sbagliati. Il flag cambia solo il percorso di salvataggio. Costruire la cartella di lavoro alloca comunque l'intero modello di celle in memoria, quindi l'altopiano della fase di riempimento è alto esattamente come prima. Ciò che sparisce è il picco di serializzazione che prima si impilava sopra quell'altopiano al momento del salvataggio, e per un lavoro che riempie 400.000 righe quel picco è di norma tutta la differenza fra rientrare in un budget di memoria e sfondarlo. La proprietà vale False per impostazione predefinita per preservare il comportamento storico, quindi aderirvi è una riga esplicita che scrivete di proposito
Un export in blocco con il flag attivo
Book := TXLSXWorkbook.Create;
try
BoldIdx := Book.Fonts.Add('Calibri', 11, True, False); // indice del pool, in base 0
Sheet := Book.Sheets.Add('Bulk');
for R := 1 to 100000 do
begin
Sheet.Cells[R, 1].Value := R;
Sheet.Cells[R, 2].Value := 'Row ' + IntToStr(R);
Sheet.Cells[R, 3].Value := R * 1.5;
if (R mod 1000) = 0 then
Sheet.Cells[R, 2].FontIndex := BoldIdx + 1; // in base 1 sulla cella
end;
Book.StreamingWrite := True; // scrive l'XML del foglio direttamente nello zip
Book.SaveAs('bulk.xlsx');
finally
Book.Free;
end;Cells[R, C] crea le celle su richiesta, il che mantiene pulito il corpo del ciclo. Vale la pena mandare a memoria due limiti della griglia: 1.048.576 righe e 16.384 colonne, esposti come XlsxMaxRow e XlsxMaxCol. Un flusso di dati che supera il limite di righe va suddiviso su più fogli nel vostro codice. Nulla a valle si accorge del superamento né lo corregge per voi, e il file finisce semplicemente troncato al limite
Riempire le righe senza il sovraccarico dei Variant per cella
Ogni assegnazione Cells[R, C].Value paga una ricerca di cella e una conversione Variant. A diecimila righe nessuno se ne accorge. A un milione di righe da venti colonne ciascuna, quel sovraccarico per chiamata diventa il costo dominante della fase di riempimento, e il profiler lo indicherà senza esitazioni. Le interfacce in blocco vi permettono di consegnare al writer una riga intera per volta. WriteRows pilota una callback che fornisce una riga per invocazione:
procedure TBulkExporter.FillRow(Sender: TObject; SheetIndex, Row, FirstCol,
LastCol: Integer; var Values: Variant; var Skip: Boolean;
var Cancel: Boolean);
begin
if not FReader.Next then
begin
Cancel := True; // sorgente dati esaurita: fermatevi in modo pulito
Exit;
end;
Values := VarArrayCreate([FirstCol, LastCol], varVariant);
Values[FirstCol] := FReader.RecordId;
Values[FirstCol + 1] := FReader.CustomerName;
Values[FirstCol + 2] := FReader.Amount;
end;
// riempie le righe 2..100001, colonne A..C, prelevando dal lettore
Sheet.WriteRows(2, 1, 100001, 3, FillRow);Il flag Cancel è ciò che trasforma un intervallo fisso di righe in "fino a N righe", che è la forma naturale quando il numero di righe viene da una query che non avete finito di eseguire. Skip è il tocco più leggero: lascia vuota una singola riga senza fermare l'esecuzione. Oltre a riempire le celle, la callback si rivela una buona sede per le preoccupazioni operative che altrimenti vengono innestate in modo goffo su un ciclo di riempimento. Un contatore di avanzamento che scatta ogni mille righe, un token di annullamento interrogato dallo schedulatore dei lavori, un limitatore di frequenza sulle letture dal database sorgente: tutto sta in un solo punto invece di essere infilato nel codice che scrive le celle. Sul lato lettura, ForEachRow e ForEachCell rispecchiano lo stesso schema, cosa che conta quando un lavoro batch consuma e produce file grandi insieme
I pool di stili premiano chi li porta fuori dal ciclo
Il modello di stile XLSX è un insieme di pool condivisi. Fonts.Add, Fills.AddSolid e Borders.Add restituiscono tutti un indice di pool in base 0, e una cella richiama un font memorizzando quell'indice più uno in FontIndex, dove lo zero è riservato al valore predefinito della cartella di lavoro. Il +1 è proprio lì nell'esempio in blocco qui sopra. Dimenticatelo e la cella prende in silenzio lo stile sbagliato, perché uno scarto di uno in un indice di pool di stili è comunque un indice valido e non viene sollevato nulla
La disciplina che ne consegue è creare ogni oggetto di stile prima del ciclo sulle righe e richiamarne l'indice dentro il ciclo. Fonts.Add deduplica le definizioni identiche, quindi chiamarlo una volta per riga spreca solo CPU. Alignments.Add è la trappola, perché restituisce una voce nuova a ogni chiamata. Dentro un ciclo da 100.000 righe questo seppellisce styles.xml sotto centomila record di allineamento duplicati, il che gonfia il file su disco e rallenta ogni apertura successiva in Excel mentre i duplicati vengono rianalizzati. Costruite ogni stile una volta fuori dal ciclo, poi richiamatene l'indice tutte le volte che vi serve
Flussi, cartelle temporanee e il ciclo batch attorno a tutto
Niente di tutto questo richiede un file system. Entrambe le facciate portano overload su TStream lungo tutta la loro superficie di IO, fra cui Open, SaveAs, SaveAsCSV, SaveAsHTML e SaveAsODS, quindi un worker batch può rendere direttamente in un TMemoryStream destinato a un archivio blob o a una risposta HTTP senza toccare mai il disco. C'è uno spigolo vivo da ricordare. SaveAs(Stream) scrive dalla posizione corrente del flusso e non riavvolge dopo, quindi impostate voi Position := 0 prima di consegnare il flusso a chi lo distribuisce, altrimenti il consumatore legge zero byte. La facciata XLS aggiunge due manopole proprie. SetTempDir punta i file temporanei del writer BIFF a un volume che abbia lo spazio e il margine di IO per assorbirli, cosa che conta sui server dove il percorso temporaneo predefinito sta su un disco di sistema angusto. UseSharedFormulas ripiega i corpi di formula ripetuti in gruppi condivisi, una riduzione di dimensione reale per la classica forma di report in cui una formula è copiata lungo un'intera colonna
Il ciclo batch in sé resta noioso di proposito:
for FileName in SourceFiles do
begin
Book := TXLSXWorkbook.Create; // istanza nuova: nessuna contaminazione di stato
try
Book.StreamingWrite := True;
if Book.Open(FileName) <> 1 then
Continue; // un input difettoso non deve uccidere il batch
Book.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
finally
Book.Free;
end;
end;Un'istanza di cartella di lavoro nuova per file costa microsecondi e rimuove un'intera categoria di difetti da contaminazione fra file: stili, nomi definiti e proprietà del documento del file 17 non hanno alcuna via per filtrare nel file 18. Il salta-e-continua su un Open fallito ripaga altrettanto, perché un caricamento troncato in un batch da 600 file dovrebbe costarvi una sola riga di log anziché il resto dell'esecuzione. Vale la pena segnalare anche ciò che il tratto CSV deliberatamente non fa. SaveAsCSV scrive le formule come testo letterale e non le valuta mai, quindi un batch di conversione i cui consumatori si aspettano numeri calcolati deve prima eseguire Calculate sulle celle rilevanti, oppure partire da cartelle di lavoro che portano già risultati in cache da un calcolo precedente
Modello di concorrenza: una cartella di lavoro per thread
Gli oggetti di nessuna delle due facciate sono sicuri fra thread, e il progetto non ha mai preteso il contrario. Poiché fra le istanze non esiste stato globale condiviso, la regola di scalabilità è semplicemente una cartella di lavoro per thread di lavoro, senza condividere una cartella fra thread. Un pool di N worker, ciascuno proprietario del proprio TXLSXWorkbook, scala quasi linearmente finché la memoria non diventa il tetto, e a quel tetto potete dare un numero: il modello di celle concorrente più grande moltiplicato per il numero di worker, più l'eventuale sovraccarico di salvataggio che StreamingWrite ha appiattito. Quando la coda si allunga, applicate la contropressione sulla coda dei lavori invece che dentro il writer. Un thread affamato che ha scritto a metà una cartella di lavoro non ha prodotto nulla di utile, mentre un lavoro che ha atteso qualche secondo un worker libero arriva in fondo intatto
Per il quadro più ampio dell'ottimizzazione, comprese le formule condivise, il salto della grafica sul lato lettura e le leve specifiche di XLS, vedete la guida alle prestazioni sulle cartelle di lavoro grandi. I lavori batch le cui righe arrivano direttamente da una query sono trattati a parte negli schemi di esportazione da database per i report Delphi
HotXLS si compila dentro il vostro servizio Delphi o C++Builder come Object Pascal nativo senza dipendenze esterne; edizioni e licenze stanno sulla pagina di prodotto di HotXLS Delphi Component