HotXLS imposta le modalità «Show Values As» delle tabelle pivot Excel da Delphi attraverso due proprietà di TXLSPivotDataField: ShowDataAs per le modalità classiche come totale progressivo, differenza e percentuale di riga, e ExtendedShowDataAs per rank, percentuale del parent e percentuale di totale progressivo. TXLSXWorkbook.MaterializeNativePivotTable poi scrive la griglia calcolata come farebbe Excel. Le due proprietà si escludono a vicenda, e quelle estese dipendono da un URI di estensione che Excel confronta distinguendo maiuscole e minuscole
Quel dettaglio finale è il motivo per cui esiste questo post. Una tabella pivot con una colonna di rank sembra una piccola cosa da generare, e alla prima apertura di solito lo è. Il guaio inizia quando un utente clicca Refresh in Excel e la colonna di rank torna a essere una semplice somma senza alcun avviso. Centrare Show Values As significa centrare due vocabolari XML, più una manciata di regole numeriche che Excel non documenta mai nella UI: come si classificano i pareggi, cosa significa una percentuale cumulativa di una media, e quale cella vuota diventa blank invece di un errore
Perché due proprietà per un solo menu di Excel?
Excel mostra un solo menu «Show Values As», ma il file lo conserva in due posti diversi, così HotXLS espone due proprietà. Le modalità originali di Excel 2007 vivono nell'attributo showDataAs dell'elemento dataField dentro pivotTableDefinition, usando l'enumerazione ECMA-376 ST_ShowDataAs. Le modalità aggiunte da Excel 2010, rank e le percentuali parent e cumulative, non entravano in quell'enumerazione, quindi sono finite in un elemento x14:dataField con un attributo pivotShowAs, avvolto in un blocco di estensione extLst sullo stesso data field
TXLSPivotShowDataAs rispecchia il primo gruppo: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal e xlpsdaIndex. TXLSPivotExtendedShowDataAs copre il secondo: xlpesdaPercentOfParent, xlpesdaPercentOfParentRow, xlpesdaPercentOfParentColumn, xlpesdaPercentOfRunningTotal, xlpesdaRankAscending e xlpesdaRankDescending, con xlpesdaNone come valore neutro. Un data field usa un gruppo o l'altro, mai entrambi. Se lasciate ShowDataAs a un valore non normale e impostate anche una modalità estesa, MaterializeNativePivotTable restituisce -1 e lascia l'output esistente esattamente com'era, celle e confini della pivot inclusi
BaseField e BaseItem portano il bersaglio del confronto. BaseField è un indice a base zero nell'elenco dei campi della tabella pivot, non nei nomi delle intestazioni delle colonne sorgente, e guida confronti, totali progressivi, percentuali di un parent scelto e rank. BaseItem è una posizione di item a base zero dentro quel campo, o una delle costanti xlPivotBaseItemPrevious e xlPivotBaseItemNext per «a confronto col vicino». Le percentuali di riga parent e di colonna parent ignorano del tutto BaseField; prendono il campo più interno sull'asse corrispondente
Costruire una pivot con rank in Delphi
Il percorso funzionante più corto è costruire la pivot, impostare la modalità di visualizzazione sul data field e materializzarla prima di salvare. L'esempio sotto classifica gli importi regionali per categoria in ordine discendente, così l'importo maggiore di ogni riga è rank 1
uses
SysUtils, lxHandleX, lxPivot;
var
Book: TXLSXWorkbook;
Data, Report: TXLSXWorksheet;
Pivot: TXLSPivotTable;
Measure: TXLSPivotDataField;
begin
Book := TXLSXWorkbook.Create;
try
Data := Book.Sheets.Add('Data');
Data.Cells[1, 1].Value := 'Category';
Data.Cells[1, 2].Value := 'Region';
Data.Cells[1, 3].Value := 'Amount';
// ... riempite le righe 2..7 ...
Report := Book.Sheets.Add('Report');
Pivot := Report.AddPivotTable('Data!A1:C7', 4, 3, 'RegionRank');
Pivot.AddRowField('Category'); // campo pivot 0
Pivot.AddColumnField('Region'); // campo pivot 1
Measure := Pivot.AddDataFieldByName('Amount', xlpaSum);
Measure.DisplayName := 'Rank by Region';
Measure.ShowDataAs := xlpsdaNormal; // la modalità base resta spenta
Measure.ExtendedShowDataAs := xlpesdaRankDescending;
Measure.BaseField := 1; // rank attraverso Region
if Book.MaterializeNativePivotTable(Pivot) <> 1 then
raise Exception.Create('Pivot display mode was rejected');
Book.SaveAs('region-rank.xlsx');
finally
Book.Free;
end;
end;Due abitudini ripagano qui. Prima, impostate ShowDataAs := xlpsdaNormal esplicitamente quando scegliete una modalità estesa, anche su un campo fresco, perché lo stesso codice spesso finisce riutilizzato contro una pivot importata il cui data field porta già una modalità base. Seconda, controllate il valore di ritorno. Una materializzazione rifiutata è atomica, il che è bene, ma significa anche che un controllo mancato spedisce un file con dentro la griglia precedente
BaseField sopravvive al salvataggio anche se non chiamate mai MaterializeNativePivotTable. HotXLS scrive l'attributo baseField per percentuale del parent, percentuale di totale progressivo e entrambe le modalità di rank non appena la modalità estesa è impostata, così un workbook salvato subito dopo la configurazione dice comunque a Excel su quale campo fare il rank quando fa il refresh
Cosa va storto con l'URI di estensione?
Il blocco di estensione che porta pivotShowAs è identificato dall'URI {E15A36E0-9728-4e99-A89B-3F7291B0FE68}, e Excel confronta quella stringa distinguendo maiuscole e minuscole durante il refresh. Notate la e minuscola in 4e99. I GUID sono normalmente case-insensitive e molti scrittori XML li mettono in maiuscolo, che è esattamente la modalità di guasto: un file con 4E99 si apre in Excel con ogni cella in cache corretta, perché Excel si limita a dipingere i valori memorizzati. Dopo RefreshTable, però, Excel non riconosce più l'estensione, butta la modalità estesa e ricalcola la colonna come somma ordinaria. Nessun prompt di riparazione, nessun errore, solo una colonna di rank diventata silenziosamente totali
HotXLS scrive l'URI letterale come Excel se lo aspetta, quindi questo vi morde solo se qualcosa a valle riscrive il package, per esempio un passo di post-processing che normalizza il case dei GUID o ricostruisce gli elementi extLst. Il test che lo becca costa poco: aprite il file generato in Excel, fate il refresh della pivot e confermate che la colonna estesa mostra ancora rank o percentuali invece che aggregati grezzi. Un test che legge solo le celle in cache passerà su un file rotto ogni volta
Come classifica i pareggi Excel e ordina i totali progressivi?
Il rank pivot di Excel è un dense rank, e HotXLS lo riproduce. Con importi discendenti di 30, 30 e 20, il risultato è 1, 1 e 2, non lo stile competizione 1, 1 e 3 che ottiene RANK.EQ su un foglio di lavoro. Un pareggio conta come un solo valore precedente distinto. Se un report ha bisogno del ranking a competizione, calcolatelo nei dati sorgente o in una formula del foglio accanto alla pivot, perché nessuna modalità di visualizzazione della pivot lo produce
I totali progressivi, le percentuali di totale progressivo e i confronti previous o next seguono l'ordine in cui i membri compaiono davvero, non l'ordine dell'elenco di item del campo. Quella distinzione conta appena un chiamante cambia l'ordinamento dopo che la cache è stata costruita
// Righe ordinate in modo discendente: il totale progressivo accumula in ordine di visualizzazione
Pivot.Fields[0].SortType := xlpfstDescending;
Measure.ExtendedShowDataAs := xlpesdaNone;
Measure.ShowDataAs := xlpsdaPercentDiff; // % di differenza da...
Measure.BaseField := 0; // ...il campo Category
Measure.BaseItem := xlPivotBaseItemPrevious; // ...il membro visibile precedente
if Book.MaterializeNativePivotTable(Pivot) <> 1 then
raise Exception.Create('Pivot display mode was rejected');Dopo che SortType è passato a xlpfstDescending, «previous» significa il membro mostrato sopra, e il totale progressivo parte dalla cima dell'elenco ordinato. Un'implementazione che percorre invece l'elenco di item memorizzato produce numeri che sembrano plausibili e dissentono da Excel nel momento in cui qualcuno fa il refresh. Anche i membri al bordo richiedono cura: al primo membro non c'è un vicino precedente, e solo la modalità percent-of confronta il membro al bordo con se stesso; le modalità di differenza lasciano quella cella al comportamento al bordo di Excel stesso invece di inventare uno zero
Perché una percentuale cumulativa può superare il 100 percento?
Una percentuale di totale progressivo sulle medie può legittimamente superare il 100 percento, perché Excel accumula gli aggregati dei membri mostrati e divide per un aggregato che non è la loro somma. Per le somme le due cose coincidono, così l'ultimo membro atterra esattamente sul 100 percento. Per le medie, sommare tre medie di categoria e dividere per la media complessiva non è una frazione di niente, e la riga finale può atterrare ben sopra il 100 percento. HotXLS qui eguaglia Excel apposta invece di serrare il valore, perché un numero serrato dissentirebbe dal file dopo il refresh
Il divisore dipende anche da dove sta il campo base. Quando il campo base è il campo più interno sul proprio asse, il divisore è l'aggregato originale per quella posizione di asse. Quando il campo base è un campo esterno in un layout annidato, il divisore è la somma attraverso i membri corrispondenti dell'asse restante. Entrambe le posizioni sono state verificate indipendentemente contro l'output di Excel, perché una regola che funziona per un livello di annidamento e fallisce in silenzio sull'altro è il classico bug della pivot
Le percentuali semplici si comportano diversamente da quella cumulativa. Percentuale di riga, colonna e totale generale dividono per l'aggregato originale all'ambito rilevante, comprese le aggregazioni non additive come le medie, così una percentuale del totale sulle medie è la media della cella divisa per la media complessiva, non una quota di un pool sommato
Blank, #NULL! e #DIV/0! nelle modalità di confronto
I confronti percent-of-base distinguono tre situazioni vuote, e ciascuna produce una cella diversa. Quando l'intersezione del numeratore non ha righe sorgente, la cella mostra #NULL!. Quando l'item base non ha dati a quell'intersezione, la cella è blank. Quando l'item base esiste e il suo aggregato è zero, la cella mostra #DIV/0!. Il codice che legge la griglia materializzata dovrebbe testare con VarIsEmpty e VarIsError invece di dare per scontato che ogni cella di valore contenga un numero
Le percentuali semplici sono più permissive: un numeratore assente lì conta come zero. Le percentuali di riga parent e di colonna parent aggiungono un altro caso, poiché una pivot senza campi sull'asse corrispondente non ha un parent per cui dividere, quindi quei risultati sono blank. I totali generali seguono anche loro la semantica di ciascuna modalità, il che comprende totali blank lungo l'asse su cui cammina un confronto o un totale progressivo; una riga di totale piena di blank sotto un totale progressivo è corretta, non un fallimento di materializzazione
Cambiare modalità su una pivot importata
Cambiare la modalità di visualizzazione di una pivot arrivata da un workbook esistente funziona allo stesso modo, con un flag in più. Passate True come AAdoptExisting così MaterializeNativePivotTable può prendere possesso delle celle che Excel ha già scritto per quella pivot
Pivot := Book.Sheets.SheetByName('Sales').PivotTables[0];
with Pivot.DataFields[0] do
begin
ShowDataAs := xlpsdaNormal; // azzera la modalità base
ExtendedShowDataAs := xlpesdaPercentOfParentRow; // niente BaseField necessario
end;
if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
raise Exception.Create('Could not re-materialize the pivot');Quando la modalità cambia, HotXLS rimuove dalla definizione in staging gli attributi obsoleti showDataAs, pivotShowAs e baseItem, così il file salvato non porta mai una modalità base stantia accanto a una estesa nuova. Lo stesso passo di staging tiene i flag compact e outline su campi dati e inattivi allineati al layout del report, il che conta perché Excel ricava le didascalie dei dati rinfrescati da quei flag e non solo dai campi di riga e colonna. I valori di enum non validi vengono rifiutati anche loro: castare un byte di 255 in uno dei due enum può fare sign-extension a -1 in Delphi, quindi HotXLS controlla sia il limite inferiore sia quello superiore e restituisce -1 senza toccare la griglia
Se aggiungete anche calculated field o calculated item allo stesso report, leggete prima costruire e rinfrescare tabelle pivot con campi calcolati in HotXLS, e per le regole XML a livello di campo che impediscono a Excel di riparare il file, vedere validità dello schema dei campi pivot XLSX in Delphi
FAQ
Un data field HotXLS può usare ShowDataAs e ExtendedShowDataAs insieme?
No. TXLSPivotDataField.ShowDataAs e ExtendedShowDataAs si escludono a vicenda; con entrambe impostate, MaterializeNativePivotTable restituisce -1 e lascia l'output precedente invariato
Perché il mio rank pivot è diventato una somma dopo il refresh in Excel?
L'URI di estensione è stato probabilmente riscritto con un case diverso. Excel confronta {E15A36E0-9728-4e99-A89B-3F7291B0FE68} distinguendo maiuscole e minuscole e ricade sull'aggregazione semplice dopo RefreshTable quando non corrisponde
Il rank pivot di HotXLS salta i numeri dopo un pareggio?
No. HotXLS segue il dense rank pivot di Excel, quindi 30, 30 e 20 si classificano come 1, 1 e 2
Il supporto Show Values As, la materializzazione nativa delle pivot e il resto del set di funzionalità XLSX e XLS vengono spediti nel componente di fogli di calcolo HotXLS Delphi per Delphi e C++Builder