HotXLS ordina gli elementi delle PivotTable per valore in Delphi attraverso due proprietà su TXLSPivotField: SortType sceglie crescente o decrescente, e SortDataField nomina il campo dati in base zero i cui totali guidano l'ordine. Quando HotXLS apre un XLSX scritto da Excel, recupera quel campo dati dall'elemento autoSortScope, dove Excel memorizza il campo Values come field="4294967294", la notazione senza segno di -2
Ordinare per valore è la richiesta che prima o poi arriva a ogni report vendite: "mostrami le regioni dalla più grande alla più piccola". Nella UI di Excel sono due clic. Nel formato file è un attributo, un'area pivot annidata, un indice di campo riservato e un ordinale di campo dati selezionato, e un reader che perde anche uno solo di quei pezzi apre comunque il file senza lamentarsi. Ordina semplicemente per la cosa sbagliata
Dove memorizza Excel una misura di ordinamento per valore?
Excel memorizza la misura di ordinamento solo dentro autoSortScope, non sul campo stesso. L'attributo sortType su <pivotField> (un valore ST_FieldSortType di ECMA-376 Part 1: manual, ascending o descending) dice in che direzione ordinare. Quale misura usare per l'ordinamento vive in un figlio <autoSortScope>, che avvolge un <pivotArea> il cui reference sull'asse dati seleziona un campo dati
<pivotField axis="axisRow" showAll="0" sortType="descending">
<items count="5"> ... </items>
<autoSortScope>
<pivotArea dataOnly="0" outlineLevel="all" fieldPosition="0">
<references count="1">
<reference field="4294967294" count="1" selected="0">
<x v="0"/>
</reference>
</references>
</pivotArea>
</autoSortScope>
</pivotField>L'elemento reference di norma punta a un campo pivot per indice. L'indice di campo -2 è riservato nella parte PivotTable di ECMA-376 allo pseudo-campo Values, quello che Excel mostra come "Values" quando due o più misure stanno su un asse. L'attributo è tipizzato come intero a 32 bit senza segno, quindi -2 viaggia sul filo come 4294967294. Il <x v="0"/> sotto di esso seleziona il campo dati 0, la prima voce in <dataFields>. C'è anche un attributo rankBy su <pivotField>, ma nei file che abbiamo confrontato Excel lo ha lasciato fuori e ha fatto affidamento solo sullo scope
Perché la misura di ordinamento è tornata -1?
Il reader HotXLS all'origine perse la misura di ordinamento perché 4294967294 non sta in un intero a 32 bit con segno. L'helper degli attributi è un sottile wrapper su StrToIntDef, e un valore fuori intervallo restituisce silenziosamente il predefinito, così il test sull'asse dati non combaciava mai, SortDataField restava a -1, e il campo conservava solo sortType="descending" senza alcuna misura attaccata
Il fallimento era silenzioso nel peggiore dei modi. Un campo decrescente senza misura ordina comunque, solo per etichetta di elemento al contrario, così un report che Excel aveva ordinato per totale tornava da HotXLS con l'ordine degli elementi invertito. Nulla sollevava un'eccezione e i numeri in ogni cella erano corretti. Solo l'ordine delle righe era sbagliato, e solo per i file scritti da Excel
La correzione in lxPivotXml.pas confronta il token grezzo dell'attributo come stringa prima di qualunque conversione a intero. Se rankBy ha già impostato una misura, vince lei; altrimenti un reference il cui token field è esattamente '4294967294' risolve SortDataField dal suo valore x selezionato. Il lato valutazione non ha avuto bisogno di cambiamenti: il comparatore finalizzava già i totali per membro sugli assi riga e colonna, gestiva l'ordine decrescente e rompeva i pari. I dati semplicemente non arrivavano mai fino a lui
La lezione generale vale per qualunque reader OOXML scritto in Delphi. ECMA-376 tipizza parecchi attributi indice come xsd:unsignedInt e poi usa la cima dell'intervallo come sentinella, così un reader con segno dotato di fallback predefinito trasforma un valore riservato in "attributo mancante". Confrontate il token, o analizzate in Int64 o Cardinal, ovunque un indice di campo possa significare Values
Ordinare gli elementi pivot per una misura dal codice Delphi
Per ordinare per valore un campo pivot HotXLS, impostate SortType a xlpfstAscending o xlpfstDescending e SortDataField all'indice in base zero della misura nell'elenco DataFields della tabella. Con SortType = xlpfstManual, che è il predefinito, SortDataField viene ignorato e gli elementi conservano il proprio ordine di cache o programmatico
uses
SysUtils, lxHandleX, lxPivot;
var
Book: TXLSXWorkbook;
Data, Report: TXLSXWorksheet;
Pivot: TXLSPivotTable;
Region: TXLSPivotField;
begin
Book := TXLSXWorkbook.Create;
try
Data := Book.Sheets.Add('Data');
Data.Cells[1, 1].Value := 'Region';
Data.Cells[1, 2].Value := 'Product';
Data.Cells[1, 3].Value := 'Units';
// ... riempite le righe 2..41 ...
Report := Book.Sheets.Add('Report');
Pivot := Report.AddPivotTable('Data!A1:C41', 3, 1, 'UnitsByRegion');
Region := Pivot.AddRowField('Region');
Pivot.AddDataFieldByName('Units', xlpaSum); // campo dati 0
Region.SortType := xlpfstDescending; // prima la regione più grande
Region.SortDataField := 0; // ordina per Sum of Units
Region.AutoSortScope := '0'; // emette anche l'autoSortScope di Excel
if Book.MaterializeNativePivotTable(Pivot) <> 1 then
raise Exception.Create('Pivot layout was rejected');
Book.SaveAs('units-by-region.xlsx');
finally
Book.Free;
end;
end;Due dettagli meritano una nota. Il writer HotXLS emette SortDataField come rankBy, e quando AutoSortScope non è vuoto scrive anche un blocco autoSortScope con il reference sull'asse dati 4294967294, mettendo la stringa della proprietà nello slot x v. Al caricamento, AutoSortScope riceve quello stesso token x v, quindi per gli ordinamenti per valore la proprietà contiene l'ordinale del campo dati come testo. Impostarlo a '0' produce quindi un file che porta la misura in entrambi i posti, che è la corrispondenza più vicina a ciò che scrive Excel stesso. MaterializeNativePivotTable poi mette in scena la griglia ordinata e i suoi metadati di layout insieme e restituisce 1 in caso di successo o -1 con un diagnostico
Le righe di elemento non sono l'unica cosa che viene ordinata. I campi colonna passano per lo stesso comparatore, usando i totali per colonna invece dei totali per riga, così un campo colonna con SortType = xlpfstDescending e un SortDataField valido ordina le proprie colonne per valore esattamente allo stesso modo
Ispezionare l'ordinamento che una cartella Excel porta già con sé
Per i file scritti da Excel, HotXLS riempie SortType, SortDataField e AutoSortScope durante il caricamento della definizione pivot, così potete auditare o cambiare l'ordinamento di un report senza ricostruire la tabella. Lo snippet sotto elenca ogni campo che ordina per una misura e nomina la misura con il suo DisplayName
var
Book: TXLSXWorkbook;
Pivot: TXLSPivotTable;
Field: TXLSPivotField;
I: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('sales-report.xlsx');
Pivot := Book.Sheets.SheetByName('Report').PivotTables[0];
for I := 0 to Pivot.FieldCount - 1 do
begin
Field := Pivot.Fields[I];
if (Field.SortType <> xlpfstManual) and (Field.SortDataField >= 0) then
Writeln(Field.Name, ' sorted by ',
Pivot.DataFields[Field.SortDataField].DisplayName);
end;
// girare l'ordinamento della Region a crescente e ricostruire la griglia esistente
Field := Pivot.FindFieldByName('Region');
if Field <> nil then
Field.SortType := xlpfstAscending;
if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
raise Exception.Create('Could not re-materialize the pivot');
Book.SaveAs('sales-report-asc.xlsx');
finally
Book.Free;
end;
end;Il secondo argomento di MaterializeNativePivotTable dice a HotXLS di adottare l'intervallo di output esistente della pivot invece di trattarlo come una collisione. Lo stesso schema appare nella guida a Show Values As rank e running totals in HotXLS, dove l'ordine conta perché i running totals si accumulano in ordine di visualizzazione
Come rompe HotXLS i pari quando due elementi hanno lo stesso valore?
HotXLS rompe i pari per etichetta di elemento, in modo deterministico, che è una differenza nota rispetto a Excel. Quando due membri hanno totali identici, il comparatore ripiega sul confronto delle loro didascalie, e il flag decrescente inverte anche quel confronto. Gli elementi la cui metrica non è un numero valido vengono dopo quelli validi in ordine crescente, e il flag decrescente inverte l'intero confronto, quel raggruppamento compreso
Excel non promette alcun ordine di pari per niente. In un fixture di test in cui quattro membri, A, B, C e D, avevano tutti lo stesso totale, un ordinamento crescente in Excel produceva B, C, A, D. Nulla nei metadati salvati predice quella permutazione; è un sottoprodotto di un ordinamento instabile, e può differire tra file. HotXLS restituisce A, B, C, D per lo stesso input. La nostra verifica per quel caso controlla l'insieme dei membri e i valori per membro invece della sequenza, e il fixture decrescente con un pari 15/15 combaciava con Excel cella per cella. Se il vostro codice a valle dipende dalla posizione esatta delle righe a pari, ordinate su una seconda misura o rendete distinti i valori
Membri compressi e il campo più interno
I membri compressi si materializzano in HotXLS come una riga aggregata per elemento compresso. Quando Excel nasconde i dettagli di un elemento, scrive sd="0" su quel <item>; HotXLS lo espone come TXLSPivotItem.IsDetailHidden. L'elemento compresso si renderizza con la propria etichetta semplice, sostituisce l'intero proprio sottoalbero, e la compressione si applica a quell'elemento sotto ogni padre, mentre i blocchi che lo contengono conservano i propri subtotali
var
Category: TXLSPivotField;
begin
// La pivot ha Category (esterno) e Product (interno) sulle righe
Category := Pivot.FindFieldByName('Category');
if (Category <> nil) and (Category.ItemCount > 0) then
Category.Items[0].IsDetailHidden := True; // comprime la prima categoria
if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
raise Exception.Create('Collapse was rejected');
end;La compressione interagisce con l'ordinamento in modo prevedibile: una riga compressa resta comunque un membro del suo campo, quindi prende la propria posizione dal suo totale come qualunque altro elemento. Il campo più interno è diverso. Non ha un livello interno, quindi non ha nulla da comprimere e, da una correzione precedente, HotXLS non renderizza mai nemmeno una riga di subtotale per lui, anche quando il suo flag defaultSubtotal è impostato. Excel si comporta allo stesso modo sugli assi a campo singolo e multilivello, e prima di quel cambiamento HotXLS poteva emettere una riga Total fantasma sotto il campo più interno. Per gli attributi a livello di campo coinvolti qui, la guida passo passo di HotXLS alla validità dello schema CT_PivotField copre ciò che lo schema richiede su <pivotField> e i suoi elementi
Trappole quando verificate contro l'automazione di Excel
Quando controverificate un ordinamento HotXLS contro Excel tramite automazione COM, chiamate AutoSort con la didascalia localizzata della misura, scritta esattamente come Excel la mostra. Su un Excel in cinese la didascalia predefinita per un campo Units sommato è '求和项:Units'; passare 'Sum of Units' su quella macchina non solleva alcun errore e non cambia nulla. Lo stesso script che funziona su un Excel inglese diventa silenziosamente un no-op altrove, quindi leggete la didascalia dal campo dati invece di fissarla nel codice
PivotField.SortTypenon poteva essere letto indietro tramite COM nella build di Excel che abbiamo testato, quindi verificate l'ordinamento dall'XML salvato o dalle celle renderizzate- In quella build, mettere un campo padre di gruppo manuale sullo stesso asse fuori dal campo ordinato sopprimeva l'ordinamento dei membri per valore; il rendering ordinato sotto padri di gruppo manuale non è verificato
- Anche le scritture su
PivotField.OutlineePivotField.LayoutFormvenivano rifiutate in quella build, quindi impostate un layout outline per un fixture tramitePivotTable.RowAxisLayout
Nessuna di quelle bizzarrie cambia il formato file, ma decidono se un test di regressione dimostri qualcosa. Un test che chiama AutoSort con la didascalia sbagliata passa contro una pivot che non è mai stata ordinata
FAQ
Cosa significa il campo 4294967294 in un file XML PivotTable?
Il campo 4294967294 è la codifica a 32 bit senza segno dell'indice di campo -2, che ECMA-376 riserva allo pseudo-campo Values. Dentro autoSortScope segna un reference sull'asse dati, e il suo figlio x seleziona in base a quale campo dati gli elementi vengono ordinati
HotXLS ordina gli elementi pivot per valore senza Excel installato?
Sì. HotXLS valuta l'ordinamento da sé quando materializza la griglia pivot, usando i totali per membro del campo dati scelto, e scrive sortType, rankBy e facoltativamente autoSortScope così Excel conserva lo stesso ordine dopo un refresh. Solo l'ordine dei pari tra valori uguali può differire da Excel
Perché i miei elementi pivot sono ordinati alfabeticamente invece che per valore?
Di solito perché il campo ha una direzione di ordinamento ma nessuna misura. Controllate che SortType non sia xlpfstManual e che SortDataField sia tra 0 e DataFieldCount - 1; con -1, HotXLS ordina gli elementi per etichetta nella direzione richiesta
Ordinamento per valore, membri compressi e il resto della pipeline pivot nativa sono inclusi in HotXLS, il componente spreadsheet Delphi per XLS e XLSX, che legge, modifica e scrive cartelle di lavoro Excel senza Excel installato. Se prima state costruendo la pivot da zero, il tutorial HotXLS su costruire e aggiornare le pivot table copre cache, campi e refresh prima di arrivare a ordinarle