Quasi ogni parte dello storico formato binario di Excel è rappresentata da un singolo record con un tipo definito a due byte e una lunghezza a due byte. Una cella corrisponde a un record LABELSST o NUMBER. Un'area unita è definita da un record MERGEDCELLS. È possibile leggere gran parte di un foglio di lavoro scorrendo i record uno alla volta ed eseguendo operazioni in base al tipo. Le PivotTable interrompono questa struttura. Una tabella pivot non è un record, bensì un piccolo programma composto da decine di record che interagiscono tra loro, distribuiti in due punti diversi dello stesso stream di documenti composti OLE, e le relazioni tra essi sono posizionali, compresse a livello di bit (bit-packed) e rigide. Questa è la struttura che la maggior parte dei lettori BIFF8 ignora o conserva come byte non elaborati, poiché scriverne una da zero richiede di riprodurre ogni riferimento incrociato gestito da Excel.
Il motivo per cui una tabella pivot è complessa risiede nel fatto che si compone di due elementi integrati. Vi è la cache pivot, un'istantanea indipendente dei dati di origine con il proprio sottostream, e vi è la vista della tabella, il layout che definisce quali campi occupino un determinato asse. La cache e la vista si riferiscono reciprocamente tramite indici. Un solo indice errato causa errori di aggiornamento all'apertura del file o una griglia vuota senza segnalazioni.
La cache pivot risiede in un proprio sottostream
La cache risiede nello stream globale della cartella di lavoro come sottostream BIFF completo, delimitato da un record BOF il cui tipo di documento è 0x0006 (il valore che contraddistingue una cache pivot, rispetto a 0x0005 per la cartella di lavoro o 0x0010 per un foglio di lavoro) e chiuso dal rispettivo record EOF. All'interno di questa struttura il layout è fisso. Un record SXDB rappresenta l'intestazione della cache. Contiene il conteggio dei record, il numero di campi della cache e l'identificatore dello stream che la vista della tabella utilizzerà per collegarsi. Ogni colonna di origine fornisce quindi un record di definizione del campo SXFDB seguito da un SXFDBType che lo classifica, e successivamente dai valori univoci assunti da quella colonna, emessi come record di elementi tipizzati per ciascun valore distinto.
I record degli elementi sono il punto in cui la cache si rivela fondamentale. Un valore di testo diventa un record SXSTRING, un valore numerico un record SXNUM, un valore logico un record SXBOOLEAN e un errore di formula un record SXERR. La cache non memorizza la griglia di origine, bensì i valori distinti per ogni campo e una tabella di indici che indica, per il record n, quale elemento distinto sia associato a ciascun campo. Per questo motivo, la creazione a livello di codice di una tabella pivot non consiste nel copiare le celle. È necessario analizzare l'intervallo di origine, determinare il tipo di ogni campo dai valori contenuti, rimuovere i duplicati organizzandoli in un elenco di elementi tipizzati e salvare ogni riga come tupla di indici di elementi. HotXLS esegue questo processo: una colonna interamente numerica genera elementi SXNUM, una colonna con testo misto genera elementi SXSTRING, e le date sono gestite come valori seriali tramite lo stesso percorso numerico.
SXDBB e la compressione a livello di bit (bit-packing)
La tabella degli indici per record è l'elemento tecnicamente più particolare dell'intera struttura e risiede nel record SXDBB. Una codifica semplice memorizzerebbe l'indice di ogni elemento del campo come parola a 16 bit. Excel si comporta diversamente: racchiude l'indice di ciascun campo esattamente nel numero di bit necessari per indirizzare gli elementi di quel campo. La larghezza è pari a ceil(log2(itemCount + 1)) bit. L'elemento + 1 è fondamentale: il valore aggiuntivo è una sentinella che indica "vuoto, nessun valore per questo campo in questo record", quindi un campo con tre elementi distinti deve rappresentare quattro stati e richiede due bit, non il singolo bit che tre elementi da soli farebbero presupporre. Un campo privo di elementi non occupa bit e viene saltato durante la compressione.
I bit per un record vengono concatenati in tutti i campi, quindi il record successivo inizia su un nuovo confine di byte. I record sono allineati ai byte, non compressi a livello di bit da un'estremità all'altra; questo rende possibile l'accesso casuale alla tabella a scapito di pochi bit di riempimento per riga. La compressione all'interno di un byte assegna priorità al bit meno significativo. Una volta accettate queste due regole, l'encoder funziona come una pompa di bit diretta, e il decodificatore ne rappresenta il riflesso.
// Width of one field's index in the SXDBB stream.
// citmTotal distinct items need ceil(log2(citmTotal + 1)) bits,
// the +1 reserving a "blank" sentinel value.
function BitsForFieldItems(itemCount: Integer): Integer;
var
capacity: Integer;
begin
Result := 0;
if itemCount <= 0 then
Exit; // empty field contributes zero bits
Result := 1;
capacity := 2;
while capacity < itemCount + 1 do
begin
Inc(Result);
capacity := capacity * 2;
end;
end;Il motivo per cui questo dettaglio non può essere trascurato è il limite massimo di 8224 byte per un singolo record BIFF. Ogni record nel formato, compresi quelli pivot, deve contenere il proprio payload in un massimo di 8224 byte, e una cache pivot ricca di informazioni con migliaia di righe di origine supererà tale soglia molto prima di aver emesso ogni riga. Per questo motivo, la tabella degli indici viene suddivisa. HotXLS limita il corpo di un singolo record SXDBB a 8220 byte (il limite di 8224 del record meno l'intestazione a quattro byte di tipo e lunghezza), lo divide per la larghezza in byte di un record compresso per determinare quante righe intere possano rientrare, ed emette il numero di record SXDBB aggiuntivi richiesto dal conteggio delle righe. Ogni continuazione inizia in modo pulito su un confine di record, evitando che una riga venga divisa tra due record. Un lettore che conosce la larghezza in bit per record può scorrere ogni SXDBB in sequenza come se si trattasse di un'unica griglia di bit contigua.
Il layout della vista: SXLI per il corpo, SXPI per la pagina
Gli elementi di riga dell'asse, ovvero le righe del corpo della tabella pivot che elencano ogni combinazione di valori dei campi di riga e colonna tracciati dalla tabella. Questi dati risiedono nei record SXLI (tipo record 0x00B5, descritto in [MS-XLS] §2.4.275). Un singolo record SXLI contiene molte righe, anche in questo caso finché il limite di 8224 byte non impone un nuovo record, e adotta una tecnica di compressione: ogni riga memorizza solo le differenze rispetto alla riga soprastante, espresse come conteggio dei prefissi comuni, in modo che un asse annidato non ripeta i valori dei campi esterni in ogni riga. La riga dei totali complessivi e la prima riga di ogni record reimpostano sempre tale conteggio a zero, evitando che il lettore debba fare riferimento a record precedenti per ricostruire una riga.
L'asse di pagina, ovvero i menu a discesa dei filtri posizionati sopra la tabella pivot, risiede in un record separato. Il record SXPI (tipo di record 0x00B6, [MS-XLS] §2.4.276) contiene una voce da dieci byte per ogni campo di pagina: l'indice del campo pivot isxvd, l'elemento di cache selezionato iCache, una parola di posizione ipos, e un id dell'oggetto legacy objId. Il valore iCache è l'elemento da analizzare con attenzione. Un campo di pagina che mostra "(All)", senza applicare filtri, memorizza la sentinella 0x7FFD invece di un indice di elemento reale. Una tabella pivot creata a livello di codice si apre con ogni campo di pagina impostato su "(All)" finché il chiamante non seleziona preventivamente un elemento; in quel momento l'indice della cache di tale elemento sostituisce la sentinella ed Excel si apre con il filtro applicato. Accanto a questi si trovano i record di supporto che descrivono i singoli campi e la loro formattazione, SXVD e SXVDEx per le definizioni di vista dei campi, SXIVD per gli elenchi degli indici dei campi che ordinano ciascun asse, e SXFormat per la formattazione numerica, ciascuno dei quali fa riferimento alla stessa cache a cui rimandano le righe del corpo.
Due writer in uno: blob grezzi e modello tipizzato
Vi è una ragione strutturale per cui HotXLS mantiene due percorsi separati per la scrittura di una tabella pivot, legata a requisiti di fedeltà del dato. Quando una cartella di lavoro viene letta da disco, i suoi record pivot sono stati scritti da Excel o da un altro applicativo, e possono presentare varianti di record, logiche di ordinamento o record di estensione non riprodotti completamente da gestori esterni. L'unica operazione sicura da compiere con tali byte consiste nel restituirli inalterati. Pertanto, una tabella pivot caricata da un file viene contrassegnata da FromRawBlobs = True, e al salvataggio il writer riproduce esattamente i blob dei record conservati. Nulla viene rigenerato, nulla viene reinterpretato, e il salvataggio mantiene stabili i byte originali.
Una tabella pivot creata a livello di programma rappresenta il caso opposto. Non vi sono byte originali da conservare, ma solo il modello di oggetti tipizzato: un TXLSPivotCache con i suoi campi ed elenchi di elementi, e un TXLSPivotTable con le assegnazioni degli assi. Tale tabella è contrassegnata da FromRawBlobs = False, e il writer la serializza emettendo un nuovo sottostream di cache BOF = 0x0006, strutturando la tabella degli indici SXDBB a partire dagli indici degli elementi presenti nel modello tipizzato, e organizzando i record SXLI e SXPI in base alla configurazione degli assi. L'indicatore è ciò che consente a entrambe le modalità di coesistere in una singola cartella di lavoro. Senza di esso, un singolo writer dovrebbe scartare l'accuratezza delle tabelle caricate o rifiutare di generarne di nuove. Eventuali record di estensione specifici del produttore presenti in una tabella letta vengono conservati come record supplementari, accessibili tramite l'elenco SupplementalRecords, in modo che l'analisi tramite modello tipizzato non perda le parti non descritte dal modello.
Creare una tabella pivot nel codice
Tutti i componenti sopra descritti sono gestiti da una singola chiamata. La funzione AddPivotTable accetta l'intervallo di origine in notazione A1, la cella di destinazione in cui posizionare l'angolo superiore sinistro della tabella, e un nome. Analizza l'intervallo, lo esamina per identificare i tipi di campo e crea la cache (riutilizzando una cache esistente se un'altra tabella è già associata allo stesso intervallo), restituendo un oggetto tipizzato TXLSPivotTable con un campo per ogni colonna di origine, inizialmente disattivato. Sarà poi possibile posizionare i campi sugli assi e selezionare un'aggregazione. La firma è esattamente questa, e la cache, la compressione SXDBB e i record della vista vengono generati automaticamente al salvataggio.
uses
lxHandle, lxPivot;
var
Book : TXLSWorkbook;
Sheet: IXLSWorkSheet;
Pivot: TXLSPivotTable;
begin
Book := TXLSWorkbook.Create;
try
Book.Open('Sales.xls');
Sheet := Book.Sheets[1];
// Source A1:E500 on 'Data'; anchor the pivot at row 3, col 1.
Pivot := Sheet.AddPivotTable('Data!$A$1:$E$500', 3, 1, 'SalesByRegion');
if Pivot <> nil then
begin
Pivot.AddRowField('Region');
Pivot.AddColumnField('Quarter');
Pivot.AddDataFieldByName('Revenue', xlpaSum);
end;
Book.SaveAs('Sales-Pivot.xls');
finally
Book.Free;
end;
end;La prima riga dell'intervallo di origine viene letta come intestazione che definisce i nomi dei campi della cache, quindi AddRowField('Region') associa una colonna in base al testo dell'intestazione anziché alla posizione. Poiché la tabella restituita è un modello tipizzato con FromRawBlobs = False, il writer segue il percorso da zero: crea una cache indipendente che non richiede la presenza dell'intervallo di origine in fase di aggiornamento, la caratteristica desiderata quando la tabella deve essere inviata a un destinatario che potrebbe spostare o eliminare i dati sottostanti.
La lettura e la riconciliazione dei record pivot e di cache di file generati esternamente, incluso il percorso di conservazione dei blob originali, sono trattate nella guida all'analisi e conversione delle cartelle di lavoro. Quando l'intervallo di origine raggiunge decine di migliaia di righe e lo stream SXDBB si estende su molti record di continuazione, le tecniche descritte nelle note sulle prestazioni per cartelle di lavoro di grandi dimensioni evitano che la compilazione della cache influisca sul runtime. Entrambe si affiancano al writer pivot incluso nel componente per fogli di calcolo HotXLS per Delphi e C++Builder, insieme alle API per celle, formule, grafici e formattazione trattate in altre sezioni di questo blog.