HotXLS crea e aggiorna tabelle pivot XLSX native da Delphi e C++Builder senza installazione di Excel e senza automazione COM sulla macchina. Chiami AddPivotTable con un intervallo di origine, inserisci i campi sugli assi di riga, colonna, pagina e dati, aggiungi elementi calcolati o una visualizzazione in percentuale sul totale, e il componente scrive le parti pivotCacheDefinition e pivotTableDefinition che Excel apre come pivot attiva e aggiornabile
Lo scenario che rende questa operazione utile è un server di reportistica. Generi centinaia di cartelle di lavoro a notte, ciascuna contenente una pivot che riassume un conto, e il mese successivo i numeri variano e ogni file deve riflettere le nuove righe di origine. Gestire Excel da un servizio Windows è fragile e non concesso in licenza per l'uso su server, e scrivere a mano l'XML della pivot è un progetto di archeologia delle specifiche che non finisce mai. HotXLS si colloca tra queste due strade senza uscita: un modello a oggetti tipizzato sulle parti pivot OOXML, in modo che lo stesso codice Pascal che riempie le celle dichiari anche la pivot e ricostruisca la sua cache nello stesso processo
Come si crea una tabella pivot in Delphi senza Excel?
Ne crei una con una singola chiamata e poi posizioni i campi sugli assi. AddPivotTable accetta l'intervallo di origine in notazione A1, la cella di destinazione a cui si ancora la tabella e un nome; analizza l'intervallo, esegue la scansione di ogni colonna per dedurne il tipo di dati, crea una cache pivot (o ne riutilizza una già associata allo stesso intervallo) e restituisce un oggetto TXLSPivotTable i cui campi iniziano tutti fuori asse. Da qui, i metodi pratici AddRowField, AddColumnField, AddPageField e AddDataFieldByName collegano il layout, e ciascun campo dati assume una delle undici aggregazioni dell'enumerazione TXLSPivotAggregation (xlpaSum, xlpaCount, xlpaAverage, xlpaMax, xlpaMin, xlpaProduct, xlpaCountNums, xlpaStdDev, xlpaStdDevP, xlpaVar, xlpaVarP)
uses
lxHandleX, lxPivot;
var
Book : TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Pivot: TXLSPivotTable;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('sales.xlsx');
Sheet := Book.Sheets[1]; // foglio di report (a base 1 nel motore XLSX)
// Origine A1:E500 sul foglio 'Data'; ancora la pivot alla riga 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.AddPageField('Year');
Pivot.AddDataFieldByName('Revenue', xlpaSum);
Pivot.AddDataFieldByName('Units', xlpaAverage);
Book.SaveAs('sales-pivot.xlsx');
end;
finally
Book.Free;
end;
end;Perché la cache e la tabella sono due parti separate
Una tabella pivot è in realtà composta da due artefatti che fanno riferimento l'uno all'altro, e comprendere la suddivisione è ciò che rende chiaro tutto il resto. Il pivotCacheDefinition è l'istantanea dei dati: un worksheetSource che punta all'intervallo di origine e un cacheField per colonna contenente i valori distinti di quella colonna (i suoi sharedItems) oltre ai limiti derivati. Il pivotTableDefinition è la vista: quale campo cache si trova su quale asse, i campi dati e le loro aggregazioni, i controlli di layout. La vista si collega alla cache tramite cacheId attraverso la relazione pivotCaches della cartella di lavoro, esattamente come stabilito da ECMA-376 Parte 1 §18.10 e [MS-XLSX]
Questa indirezione non è burocrazia, garantisce due vantaggi. Una sola cache può supportare diverse tabelle, quindi l'aggiornamento della cache aggiorna ogni vista estratta da essa. Inoltre, la cache memorizza i valori di ciascuna colonna deduplicati con una tabella di indici per record anziché con la griglia grezza, motivo per cui creare una pivot significa scansionare l'origine, non copiare celle. HotXLS modella questo aspetto allo stesso modo per entrambi i motori, quindi il codice sopra è quasi identico al percorso classico documentato insieme al layout binario dei record SX dietro le classiche pivot .xls. Se la sorgente si trova su un altro foglio o vi si fa riferimento tramite un nome, il prefisso dell'intervallo e i nomi definiti e i riferimenti tra fogli seguono le consuete regole A1, con nomi di foglio racchiusi tra virgolette consentiti per i nomi contenenti spazi
Gli elementi calcolati non sono campi calcolati
Questi tre termini indicano tre cose diverse, e confonderli è il classico errore delle tabelle pivot. Un elemento calcolato vive all'interno di un singolo campo e combina gli elementi di quel campo per nome; ad esempio, all'interno di un campo Region puoi definire una riga sintetica CoreMarkets pari a North più South; HotXLS lo espone come TXLSPivotField.AddCalculatedItem e scrive un <calculatedItem> sotto <calculatedItems> di quel campo. Un campo calcolato è diverso: si tratta di un nuovo valore derivato da altre colonne, come Margin da Revenue e Cost, ed è trasportato come formula su un campo cache tramite TXLSPivotCacheField.Formula. Un membro calcolato, aggiunto con TXLSPivotTable.AddCalculatedMember, è un membro personalizzato a livello di tabella che può fungere da misura (membro di tipo data) o membro di dimensione, principalmente di interesse per pivot strutturate OLAP
var
Region: TXLSPivotField;
Member: TXLSPivotCalculatedMember;
begin
// Un ELEMENTO calcolato combina elementi di un singolo campo per nome.
Region := Pivot.AddRowField('Region');
Region.AddCalculatedItem('CoreMarkets', '=North+South');
// Un MEMBRO calcolato è dichiarato a livello di tabella. Il tipo di membro
// 'data' lo contrassegna come misura; un tipo vuoto è un membro di dimensione.
Member := Pivot.AddCalculatedMember('AvgTicket', '=Revenue/Units');
Member.MemberType := 'data';
end;Un limite onesto si applica a tutti e tre. HotXLS emette il testo della formula nell'XML di definizione; non lo valuta. Excel calcola l'elemento, il campo o il membro calcolato quando apre il file, allo stesso modo in cui calcola ogni aggregazione nella griglia. HotXLS scrive le istruzioni, not i risultati, quindi le formule fornite devono essere formule pivot valide nel dialetto di Excel, facendo riferimento ai nomi dei campi e degli elementi come farebbe Excel
Come mostrare i valori come percentuale del totale?
Imposti la modalità di visualizzazione del valore sul campo dati anziché trasformare i numeri autonomamente. TXLSPivotDataField.ShowDataAs accetta l'enumerazione TXLSPivotShowDataAs, che rispecchia i valori OOXML ST_ShowDataAs: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal e xlpsdaIndex. Un trucco comune consiste nel posizionare la stessa colonna di origine sull'asse dei dati due volte, una come somma grezza e una come quota del totale complessivo, in modo che il report mostri sia il numero sia il suo peso
var
Rev, Share: TXLSPivotDataField;
begin
Rev := Pivot.AddDataFieldByName('Revenue', xlpaSum);
Share := Pivot.AddDataFieldByName('Revenue', xlpaSum);
Share.DisplayName := 'Share of total';
Share.ShowDataAs := xlpsdaPercentOfTotal;
// Le modalità relative agli elementi richiedono una base con cui eseguire il confronto. Un totale parziale
// lungo 'Quarter' (indice campo cache 3) leggerebbe:
// Share.ShowDataAs := xlpsdaRunTotal;
// Share.BaseField := 3; // indice del campo cache su cui viene eseguita la trasformazione
// Share.BaseItem := $7FFD; // $7FFD = "(All)"
end;xlpsdaPercentOfTotal non richiede alcuna base perché è relativo al totale complessivo, al contrario delle modalità relative agli elementi. xlpsdaDifference, xlpsdaPercentDiff e xlpsdaRunTotal richiedono BaseField (l'indice del campo cache su cui viene eseguito il confronto) e, per le forme ancorate agli elementi, un indice BaseItem, dove $7FFD rappresenta la sentinella (All). HotXLS li scrive come attributi <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> e, come per le formule calcolate, lascia l'aritmetica a Excel
Raggruppare date e numeri, e filtri dei campi di pagina
Il raggruppamento viene configurato sul campo cache, non sul campo pivot, perché modifica il modo in cui il dominio di origine viene suddiviso. Imposta HasGroup := True sul campo cache restituito da Cache.FindFieldByName, quindi scegli le manopole dell'intervallo numerico (GroupStartNum, GroupEndNum, GroupInterval) o i flag della gerarchia delle date (GroupByDate con GroupMonths, GroupQuarters, GroupYears e un intervallo GroupStartDate / GroupEndDate). HotXLS emette l'elemento <fieldGroup><rangePr groupBy="months"/> corrispondente in modo che un campo raggruppato per mese o per una fascia numerica con ampiezza 1000 si apra come lo avrebbe raggruppato Excel
I campi di pagina sono gli elenchi a discesa dei filtri sopra la tabella. AddPageField posiziona un campo sull'asse di pagina e PageItemIndex preseleziona un singolo elemento della cache, impostato per impostazione predefinita su xlPageItemAll ($7FFD, ovvero (All)). Per consentire a un lettore di selezionare più elementi contemporaneamente, imposta MultipleItemSelectionAllowed := True, che HotXLS scrive come <pivotField multipleItemSelectionAllowed="1"/>. Per criteri che vanno oltre una selezione manuale, ogni campo contiene una raccolta Filters tipizzata che copre le famiglie OOXML ST_FilterType — filtri per conteggio, percentuale, somma, didascalia, valore e data — con ciascuna voce che associa un tipo di filtro ai relativi valori di confronto
Come si aggiorna la cache pivot quando l'origine cambia?
RefreshPivotCache su TXLSXWorkbook scansiona nuovamente l'intervallo di origine e ricostruisce la cache sul posto, operazione necessaria per una pipeline batch dopo aver modificato le righe sottostanti. Passa l'ID cache e il metodo rileggerà ogni cella nell'intervallo di origine (intestazione sulla prima riga, dati sulla seconda), ricostruirà il dominio degli elementi condivisi di ciascun campo deduplicando i valori e ricalcolando i limiti per tipo, e riscriverà gli indici degli elementi per record. Restituisce 1 in caso di successo e -1 quando l'ID cache è sconosciuto o il foglio sorgente non è presente
var
Rc: Integer;
begin
// ...i dati di origine sono cambiati da quando è stata creata la pivot...
Sheet := Book.Sheets[1];
Sheet.Cells[2, 5].Value := 128000; // un dato sui ricavi (Revenue) corretto
// Ricostruisce gli elementi condivisi della cache e gli indici dei record dall'origine.
Rc := Book.RefreshPivotCache(Pivot.CacheId); // 1 = aggiornato, -1 = cache/origine mancante
if Rc = 1 then
Book.SaveAs('sales-pivot-refreshed.xlsx');
end;Il confine semantico qui merita di essere dichiarato chiaramente. Prima dell'introduzione di questo metodo, HotXLS si affidava al flag refreshOnLoad="1" lasciando tutto il lavoro di aggiornamento a Excel all'apertura successiva, approccio valido quando un essere umano apre il file ma inutile in una pipeline headless che deve consegnare dati corretti. RefreshPivotCache rende attuale la cache salvata in-process, e poiché le tabelle si collegano a una cache tramite ID, ogni pivot tratta da quella cache vede l'aggiornamento. Ciò che non fa è definire il layout o aggregare la griglia visibile — Excel ricalcola comunque la tabella renderizzata dalla cache aggiornata all'apertura del file
Cosa scrive HotXLS e cosa calcola Excel
Mantieni chiara la divisione del lavoro e nulla ti sorprenderà. HotXLS è uno scrittore di definizioni: produce il pivotCacheDefinition, i record cache e il pivotTableDefinition, completi di assi, aggregazioni, elementi e membri calcolati, modalità di visualizzazione dei valori, raggruppamento e filtri. Excel è il calcolatore: all'apertura materializza i gruppi (bucket), valuta le formule calcolate, applica le trasformazioni showDataAs e aggrega il corpo. I valori visualizzati dall'utente sono quelli di Excel, generati a partire dalle istruzioni scritte da HotXLS, motivo per cui ogni formula e indice di base deve essere corretto al momento della scrittura anziché verificato al momento del rendering
Due limiti meritano di essere conosciuti prima di progettare intorno a questa funzionalità. AddPivotTable analizza un intervallo A1 intervallo A1 rettangolare con un prefisso di foglio opzionale; le origini di intervalli denominati e cartelle di lavoro esterne esulano da ciò che il costruttore risolve, sebbene le cache lette da un file creato in Excel mantengano la loro origine di intervallo denominato nel ciclo completo. Inoltre, le tabelle pivot create con Excel che HotXLS legge dal disco vengono riprodotte byte per byte al momento del salvataggio, quindi le modifiche tipizzate descritte qui si applicano in modo pulito alle tabelle pivot create nel codice mentre le pivot esistenti rimangono integre. Per la parte di input di un report — le celle convalidate e le tabelle filtrate riassunte dalla pivot — vedi convalida dei dati, AutoFilter e tabelle strutturate
Il modello pivot mostrato qui fa parte di HotXLS Delphi Excel Component standard per Delphi e C++Builder, che legge e scrive sia le parti pivot tipizzate XLSX sia i classici record BIFF8 dallo stesso modello a oggetti