HotXLS ordena elementos de PivotTable por valor en Delphi mediante dos propiedades de TXLSPivotField: SortType elige ascendente o descendente, y SortDataField nombra el campo de datos zero-based cuyos totales gobiernan el orden. Cuando HotXLS abre un XLSX escrito por Excel, recupera ese campo de datos del elemento autoSortScope, donde Excel guarda el campo Values como field="4294967294", la grafía sin signo de -2
Ordenar por valor es la petición que todo informe de ventas acaba recibiendo: «muéstrame las regiones de mayor a menor». En la UI de Excel son dos clics. En el formato de fichero es un atributo, un área de pivot anidada, un índice de campo reservado y un ordinal de campo de datos seleccionado, y un lector que pierda cualquiera de esas piezas abre el fichero igualmente sin quejarse. Simplemente ordena por lo equivocado
Dónde guarda Excel una medida de ordenación por valor
Excel guarda la medida de ordenación solo dentro de autoSortScope, no en el propio campo. El atributo sortType de <pivotField> (un valor ST_FieldSortType de ECMA-376 Part 1: manual, ascending o descending) dice en qué dirección ordenar. Qué medida usar vive en un hijo <autoSortScope>, que envuelve un <pivotArea> cuya referencia de eje de datos selecciona un campo de datos
<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>El elemento reference apunta normalmente a un campo de pivot por índice. El índice de campo -2 está reservado en la parte PivotTable de ECMA-376 para el pseudo-campo Values, el que Excel muestra como «Values» cuando dos o más medidas se sientan en un eje. El atributo está tipado como entero de 32 bits sin signo, así que -2 viaja por el cable como 4294967294. El <x v="0"/> debajo selecciona el campo de datos 0, la primera entrada de <dataFields>. También hay un atributo rankBy en <pivotField>, pero en los ficheros que comparamos Excel lo dejó fuera y se apoyó solo en el scope
Por qué la medida de ordenación volvía como -1
El lector de HotXLS perdía al principio la medida de ordenación porque 4294967294 no cabe en un entero de 32 bits con signo. El helper de atributos es un envoltorio fino sobre StrToIntDef, y un valor fuera de rango devuelve silenciosamente el valor por defecto, así que la prueba de eje de datos nunca casaba, SortDataField se quedaba en -1, y el campo conservaba solo sortType="descending" sin medida adjunta
El fallo era silencioso de la peor manera. Un campo descendente sin medida aún ordena, simplemente por etiqueta de elemento al revés, así que un informe que Excel ordenó por total volvía de HotXLS con el orden de elementos invertido. Nada lanzaba una excepción y los números de cada celda eran correctos. Solo el orden de las filas estaba mal, y solo en ficheros que Excel había escrito
El arreglo en lxPivotXml.pas compara el token crudo del atributo como string antes de cualquier conversión a entero. Si rankBy ya fijó una medida, gana él; si no, un reference cuyo token de field es exactamente '4294967294' resuelve SortDataField desde su valor x seleccionado. El lado de evaluación no necesitó cambios: el comparador ya finalizaba totales por miembro en los ejes de filas y columnas, manejaba el orden descendente y rompía empates. Los datos simplemente nunca llegaban
La lección general sirve para cualquier lector OOXML escrito en Delphi. ECMA-376 tipa varios atributos de índice como xsd:unsignedInt y luego usa el techo del rango como sentinela, así que un lector con signo con un valor por defecto convierte un valor reservado en «atributo ausente». Comparad el token, o parsead a Int64 o Cardinal, dondequiera que un índice de campo pueda significar Values
Ordenar elementos de pivot por una medida desde código Delphi
Para ordenar un campo de pivot de HotXLS por valor, poned SortType a xlpfstAscending o xlpfstDescending y poned SortDataField al índice zero-based de la medida en la lista DataFields de la tabla. Con SortType = xlpfstManual, que es el valor por defecto, SortDataField se ignora y los elementos conservan su orden de caché o programático
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';
// ... rellenad las filas 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 de datos 0
Region.SortType := xlpfstDescending; // la región más grande primero
Region.SortDataField := 0; // ordenar por Suma de Units
Region.AutoSortScope := '0'; // también emite el autoSortScope propio de 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;Dos detalles merecen nota. El writer de HotXLS emite SortDataField como rankBy, y cuando AutoSortScope no está vacío escribe además un bloque autoSortScope con el reference de eje de datos 4294967294, metiendo el string de la propiedad en el hueco x v. Al cargar, AutoSortScope recibe ese mismo token x v, así que para ordenaciones por valor la propiedad contiene el ordinal del campo de datos como texto. Ponerlo a '0' produce por tanto un fichero que lleva la medida en ambos sitios, lo más parecido a lo que el propio Excel escribe. MaterializeNativePivotTable prepara entonces la rejilla ordenada y sus metadatos de layout juntos y devuelve 1 en caso de éxito o -1 con un diagnóstico
Las filas de elementos no son lo único que se ordena. Los campos de columna pasan por el mismo comparador, usando totales por columna en lugar de totales por fila, así que un campo de columna con SortType = xlpfstDescending y un SortDataField válido ordena sus columnas por valor exactamente igual
Inspeccionar la ordenación que un libro de Excel ya lleva
Para ficheros escritos por Excel, HotXLS rellena SortType, SortDataField y AutoSortScope mientras carga la definición del pivot, así que podéis auditar o cambiar la ordenación de un informe sin reconstruir la tabla. El fragmento de abajo lista cada campo que ordena por una medida y nombra la medida por su 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;
// voltear el orden de Region a ascendente y reconstruir la rejilla existente
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;El segundo argumento de MaterializeNativePivotTable dice a HotXLS que adopte el rango de salida existente del pivot en lugar de tratarlo como colisión. El mismo patrón aparece en la guía de Show Values As rank y totales acumulados en HotXLS, donde el orden importa porque los totales acumulados se suman en orden de visualización
Cómo rompe empates HotXLS cuando dos elementos tienen el mismo valor
HotXLS rompe los empates por etiqueta de elemento, de forma determinista, lo que es una diferencia conocida respecto a Excel. Cuando dos miembros tienen totales idénticos, el comparador recurre a comparar sus captions, y el flag descendente invierte también esa comparación. Los elementos cuya métrica no es un número válido ordenan después de los válidos en orden ascendente, y el flag descendente invierte la comparación entera, ese agrupamiento incluido
Excel no promete ningún orden de empate. En una fixture de prueba donde cuatro miembros, A, B, C y D, tenían todos el mismo total, una ordenación ascendente en Excel produjo B, C, A, D. Nada en los metadatos guardados predice esa permutación; es un subproducto de una ordenación inestable, y puede diferir entre ficheros. HotXLS devuelve A, B, C, D para la misma entrada. Nuestra verificación para ese caso comprueba el conjunto de miembros y los valores por miembro en lugar de la secuencia, y la fixture descendente con empate 15/15 casó celda a celda con Excel. Si vuestro código posterior depende de la posición exacta de las filas empatadas, ordenad por una segunda medida o haced los valores distintos
Miembros colapsados y el campo más interno
Los miembros colapsados se materializan en HotXLS como una fila agregada por elemento colapsado. Cuando Excel oculta los detalles de un elemento, escribe sd="0" en ese <item>; HotXLS lo expone como TXLSPivotItem.IsDetailHidden. El elemento colapsado se renderiza con su etiqueta plana, sustituye todo su subárbol, y el colapso aplica a ese elemento bajo cualquier padre, mientras los bloques que lo envuelven conservan sus propios subtotales
var
Category: TXLSPivotField;
begin
// El pivot tiene Category (exterior) y Product (interior) en filas
Category := Pivot.FindFieldByName('Category');
if (Category <> nil) and (Category.ItemCount > 0) then
Category.Items[0].IsDetailHidden := True; // colapsar la primera categoría
if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
raise Exception.Create('Collapse was rejected');
end;Colapsar interactúa con la ordenación de forma predecible: una fila colapsada sigue siendo un miembro de su campo, así que toma su posición de su total como cualquier otro elemento. El campo más interno es distinto. No tiene nivel interior, así que no tiene nada que colapsar y, desde un arreglo anterior, HotXLS tampoco renderiza nunca una fila de subtotal para él, incluso cuando su flag defaultSubtotal está activo. Excel se comporta igual en ejes de un solo campo y multinivel, y antes de ese cambio HotXLS podía emitir una fila Total fantasma bajo el campo más interno. Para los atributos a nivel de campo que entran aquí, el recorrido de HotXLS por la validez de esquema de CT_PivotField cubre lo que el esquema exige sobre <pivotField> y sus items
Trampas al comprobar contra automatización de Excel
Cuando crucéis una ordenación de HotXLS contra Excel mediante automatización COM, llamad a AutoSort con el caption localizado de la medida, escrito exactamente como lo muestra Excel. En un Excel en chino el caption por defecto de un campo Units sumado es '求和项:Units'; pasar 'Sum of Units' en esa máquina no lanza ningún error y no cambia nada. El mismo script que funciona en un Excel en inglés se convierte silenciosamente en un no-op en otro sitio, así que leed el caption del campo de datos en lugar de hardcodearlo
PivotField.SortTypeno se podía leer de vuelta por COM en la build de Excel que probamos, así que verificad la ordenación desde el XML guardado o las celdas renderizadas- En esa build, poner un campo padre de grupo manual en el mismo eje fuera del campo ordenado suprimía la ordenación de miembros por valor; el renderizado ordenado bajo padres de grupo manual no está verificado
- Las escrituras a
PivotField.OutlineyPivotField.LayoutFormse rechazaban también en esa build, así que poned un layout de esquema para una fixture mediantePivotTable.RowAxisLayout
Ninguna de esas rarezas cambia el formato de fichero, pero deciden si una prueba de regresión prueba algo. Una prueba que llama a AutoSort con el caption equivocado pasa contra un pivot que nunca se ordenó
FAQ
¿Qué significa el campo 4294967294 en un fichero XML de PivotTable?
El campo 4294967294 es la codificación sin signo de 32 bits del índice de campo -2, que ECMA-376 reserva para el pseudo-campo Values. Dentro de autoSortScope marca una referencia de eje de datos, y su hijo x selecciona por qué campo de datos se ordenan los elementos
¿Ordena HotXLS elementos de pivot por valor sin Excel instalado?
Sí. HotXLS evalúa la ordenación él mismo cuando materializa la rejilla del pivot, usando los totales por miembro del campo de datos elegido, y escribe sortType, rankBy y opcionalmente autoSortScope para que Excel conserve el mismo orden tras un refresh. Solo el orden de empate entre valores iguales puede diferir de Excel
¿Por qué mis elementos de pivot salen ordenados alfabéticamente en lugar de por valor?
Normalmente porque el campo tiene dirección de ordenación pero ninguna medida. Comprobad que SortType no es xlpfstManual y que SortDataField está entre 0 y DataFieldCount - 1; con -1, HotXLS ordena los elementos por etiqueta en la dirección pedida
La ordenación por valor, los miembros colapsados y el resto del pipeline nativo de pivot vienen en HotXLS, el componente de hojas de cálculo Delphi para XLS y XLSX, que lee, edita y escribe libros de Excel sin Excel instalado. Si estáis construyendo el pivot desde cero primero, el tutorial de HotXLS sobre construir y refrescar tablas dinámicas cubre cachés, campos y refresh antes de llegar a ordenarlos