Un foglio di calcolo con un milione di righe e una dozzina di colonne è un'esportazione perfettamente normale da un lavoro di reportistica del database. Se lo si apre nel modo consueto, caricando l'intera cartella di lavoro in un TXLSWorkbook, il processo deve materializzare ognuna di quei dodici milioni di celle come un oggetto live prima che venga eseguita la prima riga di logica aziendale. Il file su disco potrebbe essere di sessanta megabyte di XML compresso. L'albero di oggetti in cui si espande è diverse volte più grande, e tutto deve risiedere in memoria contemporaneamente perché il modello è ad accesso casuale (random-access) per progettazione. Per un rapporto che si intende leggere dall'inizio alla fine e poi scartare, si tratta di una grande quantità di memoria spesa per una struttura di cui non si ha mai avuto bisogno
C'è un secondo percorso attraverso lo stesso file. Invece di costruire un modello, si scansiona l'XML del foglio di lavoro solo in avanti, una cella alla volta, lasciando scorrere via ciascuna cella dopo averla esaminata. Non si accumula nulla. La memoria rimane pressoché costante indipendentemente dal fatto che il foglio abbia mille o dieci milioni di righe, perché il lettore non trattiene mai più della parte che sta analizzando in quel momento più un paio di piccole tabelle di ricerca. Questo è ciò che fa il lettore diretto HotXLS, e il resto di questo articolo spiega perché rimane così leggero e cosa offre in cambio
Perché il modello in memoria non scala
Un file XLSX è un pacchetto ZIP di parti XML descritte da ECMA-376. Ogni foglio di lavoro è una parte a sé stante, xl/worksheets/sheetN.xml, e al suo interno ogni riga è un elemento <row> che contiene elementi cella <c>. Il normale percorso di caricamento legge quella parte e costruisce un oggetto indirizzabile per ogni cella in modo che in seguito si possa chiedere Cells[12345, 7] e ottenere una risposta in tempo costante. L'accesso casuale è il punto cruciale di un modello di cartella di lavoro, ed è esattamente ciò che rende convenienti la modifica, la valutazione delle formule e la formattazione
Il costo è che l'accesso casuale richiede che tutto sia presente contemporaneamente. Non è possibile indicizzare una struttura costruita solo parzialmente. Pertanto, la memoria di picco di un caricamento completo è in funzione del numero di celle e, su un foglio con milioni di celle popolate, questa funzione finisce in un punto in cui il servizio non vorrebbe trovarsi, specialmente se più lavori del genere vengono eseguiti contemporaneamente su una macchina condivisa. Quando il modello di accesso di cui si ha effettivamente bisogno è sequenziale, pagare per l'accesso casuale significa pagare per una funzionalità che non si utilizzerà
Una scansione SAX solo in avanti che non costruisce alcun albero
Il lettore diretto apre il pacchetto ZIP e percorre ogni parte del foglio di lavoro con un parser pull in stile SAX. SAX qui significa che il parser riporta gli eventi di analisi man mano che li incontra: un elemento di inizio, un blocco di testo, un elemento di fine, e poi va avanti. Non si lascia dietro alcun albero di nodi. Il lettore traccia la riga e la colonna correnti dagli attributi r, raccoglie il tipo della cella, l'indice di stile, il valore e il testo della formula man mano che arrivano gli eventi, e quando viene visto il tag di chiusura </c> emette una singola cella e la dimentica. La cella successiva riutilizza la stessa manciata di variabili locali
Poiché nulla viene trattenuto tra una cella e l'altra, l'impronta di memoria non cresce con il numero di celle. Questa è la proprietà a cui vale la pena aggrapparsi. Un foglio da duecento righe e uno da venti milioni di righe costano al lettore la stessa memoria residente, e la differenza tra i due è data solo dalla durata della scansione. Si rinuncia all'accesso casuale, la funzionalità principale del modello, e in cambio si ottiene un tetto massimo di memoria che il conteggio delle celle non può superare
Cosa rimane residente e perché proprio quelle due parti
La scansione non è del tutto priva di stato (stateless), e le eccezioni sono istruttive. Due piccole tabelle devono essere mantenute in memoria per tutta la durata, perché una cella da sola non porta con sé informazioni sufficienti per essere interpretata senza di esse
La prima è la tabella delle stringhe condivise. In SpreadsheetML, una cella di testo non memorizza il proprio testo. Riporta t="s" e un payload numerico che funge da indice in xl/sharedStrings.xml, un singolo elenco deduplicato di ogni stringa distinta nella cartella di lavoro. Questo è un buon compromesso di spazio per i file in cui le stesse etichette si ripetono su migliaia di righe, ma significa che il lettore deve caricare quella tabella di stringhe all'inizio e mantenerla residente, perché qualsiasi cella ovunque in qualsiasi foglio può fare riferimento a un suo elemento. La tabella è dimensionata in base al numero di stringhe distinte, non in base al conteggio delle celle, perciò rimane modesta anche su fogli enormi
La seconda è la mappatura del formato dei numeri dalla parte degli stili. Una cella numerica e una cella data sono identiche byte per byte sul filo: entrambe sono un semplice numero, perché una data in SpreadsheetML è solo un conteggio seriale dei giorni. L'unica cosa che le distingue è lo stile della cella, che punta attraverso cellXfs in xl/styles.xml a un ID di formato numerico. Per segnalare una data come tale piuttosto che come semplice numero seriale grezzo, il lettore carica quella tabella da stile a formato e la mantiene residente. Tutto il resto nel file, gli effettivi dati delle celle che costituiscono la maggior parte dei byte, scorre via senza essere memorizzato
Ogni cella segnala un tipo e un valore
Ogni cella emessa arriva come un record TXLSDirectCell. Riporta l'indice e il nome del foglio, la riga e la colonna su base 1, un Kind semantico, il Value come Variant, il testo Formula senza il segno di uguale iniziale e il grezzo StyleIndex. Il tipo (kind) è uno tra xdkNumber, xdkString, xdkBoolean, xdkDate o xdkError, in modo che si possa diramare in base al significato della cella piuttosto che doverlo ricavare nuovamente dagli attributi. Una cella con formula riporta il tipo del suo risultato memorizzato nella cache, affiancato al testo della formula, perciò un totale calcolato arriva come un numero che ti dice anche come è stato prodotto
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;Telling a date from a number
La questione della data merita un'occhiata più da vicino perché è lì che la maggior parte degli scanner ingenui sbaglia. Non esiste alcun tipo di data su una cella numerica. Una cella contenente il valore seriale 46000 potrebbe essere una quantità, un prezzo o il 17 febbraio 2025, e il file indica quale sia solo tramite l'ID del formato del numero raggiunto tramite lo stile della cella. ECMA-376 riserva un blocco di ID di formato integrati il cui significato è fisso per tutti i produttori conformi, e gli ID contenenti date si trovano in due intervalli: da 14 a 22 per i formati standard di data e ora, e da 45 a 47 per i formati del tempo trascorso come [h]:mm:ss. Quando DetectDates è attivo, cosa che avviene di default, il lettore risolve lo stile di ciascuna cella numerica nel suo ID di formato, e una cella il cui ID rientra in quegli intervalli riservati viene segnalata come xdkDate con il suo Value già convertito in un TDateTime Delphi. Anche i formati personalizzati vengono controllati ispezionando il codice di formato in cerca di token di data e ora, ma gli intervalli riservati rappresentano la spina dorsale affidabile. Se si disattiva DetectDates, la tabella degli stili non viene nemmeno caricata, ogni cella numerica arriva come xdkNumber e la scansione risulta un po' più snella
Saltare i fogli e interrompere in anticipo
La scansione sequenziale ha un vantaggio silenzioso che l'accesso casuale non può eguagliare: ci si può fermare. L'evento OnSheet scatta prima dell'apertura di ogni foglio di lavoro e fornisce due interruttori. Impostando SkipSheet quell'intera parte non viene mai analizzata, ed è così che si scansionano solo i fogli che interessano in una cartella di lavoro multi-foglio senza pagare per leggere il resto. Impostando Abort, l'intera scansione termina immediatamente. L'evento OnCell porta con sé il proprio Abort, in modo da potersi fermare nel momento in cui si è trovato ciò che si cercava (una particolare riga, un valore sentinella, la fine di un blocco di intestazione) senza leggere i restanti milioni di celle. In una scansione solo in avanti (forward-only), l'interruzione è genuinamente gratuita, perché il lavoro che si salta è lavoro che non è ancora avvenuto
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;Counting cells without a handler
Un recente perfezionamento merita di essere menzionato perché trasforma una domanda comune in una singola chiamata economica. Il lettore conta ogni cella popolata che oltrepassa, e lo fa sia che sia collegato o meno un gestore OnCell. In precedenza, se non era impostato alcun gestore, il conteggio delle celle popolate tornava a zero, poiché il conteggio era un effetto collaterale dell'emissione. Ora il conteggio è indipendente dall'emissione. Ciò significa che si può porre una sola domanda, "quante celle popolate contiene effettivamente questa cartella di lavoro?", e ottenere la risposta al prezzo di una scansione senza alcun callback. Sia ReadFile sia ReadStream restituiscono quel totale come un Int64, e lo stesso numero è disponibile in seguito come proprietà CellCount. Un valore di ritorno pari a -1 segnala che il file non poteva essere aperto o non è un pacchetto OOXML
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;Per la scansione completa, si collega il gestore e si chiama ReadFile esattamente nello stesso modo. Il contrasto con un caricamento completo è l'intero punto: mentre il caricamento di quarterly_export.xlsx in una cartella di lavoro espanderebbe ogni cella in un oggetto residente e tratterrebbe il tutto, il lettore diretto mantiene solo le stringhe condivise e la tabella degli stili mentre i dodici milioni di celle scorrono attraverso il tuo OnCell una alla volta. L'aritmetica eseguita per ogni cella non lascia nulla alle spalle, perciò la memoria di picco è stabilita dal conteggio delle stringhe distinte della cartella di lavoro, non dal suo conteggio delle righe
Il lettore diretto è lo strumento giusto quando il compito è leggere una cartella di lavoro di grandi dimensioni una sola volta per estrarla o riassumerla. Quando invece serve l'accesso casuale del modello completo ma si desidera che si comporti bene su file di grandi dimensioni, l'ottimizzazione delineata nelle nostre note sulle prestazioni di cartelle di lavoro di grandi dimensioni in Delphi copre quel percorso. E quando la direzione si inverte, ovvero produrre un grande output piuttosto che consumarlo, la guida alla scrittura in streaming per i lavori batch su server applica la stessa disciplina a memoria costante alla scrittura. Tutti e tre vengono forniti come parte del HotXLS Component per Delphi e C++Builder, insieme alle API di lettura, scrittura, formule e formattazione trattate altrove su questo blog