HotXLS ordena elementos de PivotTable por valor en Delphi a través de dos propiedades de TXLSPivotField: SortType elige ascendente o descendente, y SortDataField nombra el campo de datos base 0 cuyos totales manejan el orden. Cuando HotXLS abre un XLSX autoría de Excel, recupera ese campo de datos del elemento autoSortScope, donde Excel guarda el campo de Values como field="4294967294", la grafía sin signo de -2
Ordenar por valor es el pedido que todo reporte de ventas recibe tarde o temprano: "muéstreme las regiones de la más grande a la más pequeña". En la interfaz de Excel son dos clics. En el formato de archivo es un atributo, un área de pivote anidada, un índice de campo reservado y un ordinal de campo de datos seleccionado, y un lector al que se le escapa cualquiera de esas piezas abre el archivo sin quejarse. Solo ordena por la cosa equivocada
¿Dónde guarda Excel una medida de orden por valor?
Excel guarda la medida de orden solo dentro de autoSortScope, no en el campo mismo. El atributo sortType de <pivotField> (un valor ST_FieldSortType de ECMA-376 Part 1: manual, ascending o descending) dice en qué dirección ordenar. La medida por la que ordenar 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 normalmente apunta a un campo de pivote por índice. El índice de campo -2 está reservado en la parte PivotTable de ECMA-376 para el pseudo-campo de Values, el que Excel muestra como "Values" cuando dos o más medidas están 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 archivos que comparamos Excel lo omitió y confió solo en el scope
¿Por qué la medida de orden volvía como -1?
El lector de HotXLS originalmente perdía la medida de orden porque 4294967294 no cabe en un entero de 32 bits con signo. El helper de atributos es un envoltorio delgado sobre StrToIntDef, y un valor fuera de rango devuelve silenciosamente el valor por defecto, así que la prueba de eje de datos nunca coincidía, SortDataField se quedaba en -1, y el campo conservaba solo sortType="descending" sin ninguna medida adjunta
La falla era silenciosa de la peor manera. Un campo descendente sin medida todavía ordena, solo que por etiqueta de elemento en reversa, así que un reporte que Excel ordenó por total volvía de HotXLS con el orden de elementos invertido. Nada lanzaba una excepción y los números en cada celda eran correctos. Solo el orden de filas estaba mal, y solo en archivos autoría de Excel
La corrección en lxPivotXml.pas compara el token crudo del atributo como cadena antes de cualquier conversión a entero. Si rankBy ya fijó una medida, gana esa; de lo contrario una referencia cuyo token field es exactamente '4294967294' resuelve SortDataField a partir de su valor x seleccionado. El lado de evaluación no necesitó cambios: el comparador ya finalizaba los totales por miembro en los ejes de filas y columnas, manejaba el orden descendente y rompía empates. Los datos simplemente nunca llegaban hasta él
La lección general aplica a cualquier lector OOXML escrito en Delphi. ECMA-376 tipa varios atributos de índice como xsd:unsignedInt y luego usa el tope del rango como sentinela, así que un lector con signo con un fallback por defecto convierte un valor reservado en "atributo faltante". Compare el token, o parseé a Int64 o Cardinal, dondequiera que un índice de campo pueda significar Values
Ordenar elementos de pivote por una medida desde código Delphi
Para ordenar un campo de pivote de HotXLS por valor, fije SortType a xlpfstAscending o xlpfstDescending y fije SortDataField al índice base 0 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';
// ... llene 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 Sum of 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 una nota. El escritor de HotXLS emite SortDataField como rankBy, y cuando AutoSortScope no está vacío también escribe un bloque autoSortScope con la referencia de eje de datos 4294967294, poniendo la cadena de la propiedad en el hueco x v. Al cargar, AutoSortScope recibe ese mismo token x v, así que para órdenes por valor la propiedad contiene el ordinal del campo de datos como texto. Fijarlo a '0' produce por tanto un archivo que lleva la medida en ambos lugares, que es la coincidencia más cercana a lo que el propio Excel escribe. MaterializeNativePivotTable entonces apila la grilla ordenada y sus metadatos de layout juntos y devuelve 1 en é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 de la misma manera
Inspeccionar el orden que un libro de Excel ya trae
Para archivos autoría de Excel, HotXLS llena SortType, SortDataField y AutoSortScope mientras carga la definición del pivote, así que puede auditar o cambiar el orden de un reporte 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;
// voltee el orden de Region a ascendente y reconstruya la grilla 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 le dice a HotXLS que adopte el rango de salida existente del pivote en lugar de tratarlo como una colisión. El mismo patrón aparece en la guía de Show Values As rango 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 empates por etiqueta de elemento, de forma determinista, lo que es una diferencia conocida frente a Excel. Cuando dos miembros tienen totales idénticos, el comparador recae en comparar sus captions, y el flag descendente también invierte esa comparación. Los elementos cuya métrica no es un número válido se ordenan después de los válidos en orden ascendente, y el flag descendente invierte toda la comparación, incluida esa agrupación
Excel no promete ningún orden de empate en absoluto. En un fixture de prueba donde cuatro miembros, A, B, C y D, tenían todos el mismo total, un orden ascendente en Excel produjo B, C, A, D. Nada en los metadatos guardados predice esa permutación; es un subproducto de un orden inestable, y puede diferir entre archivos. HotXLS devuelve A, B, C, D para la misma entrada. Nuestra verificación para ese caso revisa el conjunto de miembros y los valores por miembro en lugar de la secuencia, y el fixture descendente con empate 15/15 coincidió con Excel celda por celda. Si su código aguas abajo depende de la posición exacta de filas empatadas, ordene por una segunda medida o haga distintos los valores
Miembros colapsados y el campo más interno
Los miembros colapsados se materializan en HotXLS como una fila agregada por elemento colapsado. Cuando Excel esconde 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 simple, reemplaza todo su subárbol, y el colapso aplica a ese elemento bajo cada padre, mientras los bloques que lo contienen 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; // colapse la primera categoría
if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
raise Exception.Create('Collapse was rejected');
end;El colapso interactúa con el orden 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 interno, así que no tiene nada que colapsar y, desde una corrección anterior, HotXLS tampoco renderiza nunca una fila de subtotal para él, aunque 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 de nivel de campo que intervienen aquí, el recorrido de HotXLS por la validez de esquema de CT_PivotField cubre lo que el esquema exige en <pivotField> y sus elementos
Trampas al verificar contra automatización de Excel
Cuando contraste un orden de HotXLS contra Excel a través de automatización COM, llame a AutoSort con el caption localizado de la medida, escrito exactamente como Excel lo muestra. 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 error y no cambia nada. El mismo script que funciona en un Excel en inglés se vuelve silenciosamente un no-op en otros lados, así que lea el caption desde el campo de datos en lugar de codificarlo a fuego
PivotField.SortTypeno se pudo leer de vuelta por COM en la compilación de Excel que probamos, así que verifique el orden desde el XML guardado o las celdas renderizadas- En esa compilación, poner un campo padre de grupo manual en el mismo eje fuera del campo ordenado suprimía el orden de miembros por valor; el renderizado ordenado bajo padres de grupo manual no está verificado
- Las escrituras a
PivotField.OutlineyPivotField.LayoutFormtambién se rechazaban en esa compilación, así que fije un layout de esquema para un fixture mediantePivotTable.RowAxisLayout
Ninguna de esas rarezas cambia el formato de archivo, pero deciden si una prueba de regresión prueba algo. Una prueba que llama a AutoSort con el caption equivocado pasa contra un pivote que nunca se ordenó
FAQ
¿Qué significa el campo 4294967294 en un archivo 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 de 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
¿HotXLS ordena elementos de pivote por valor sin Excel instalado?
Sí. HotXLS evalúa el orden por su cuenta cuando materializa la grilla del pivote, 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 empates entre valores iguales puede diferir de Excel
¿Por qué mis elementos de pivote quedan ordenados alfabéticamente en lugar de por valor?
Normalmente porque el campo tiene una dirección de orden pero ninguna medida. Verifique que SortType no sea xlpfstManual y que SortDataField esté entre 0 y DataFieldCount - 1; con -1, HotXLS ordena los elementos por etiqueta en la dirección solicitada
El orden por valor, los miembros colapsados y el resto del pipeline de pivotes nativo 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 primero está construyendo el pivote desde cero, el tutorial de HotXLS sobre construir y refrescar tablas dinámicas cubre cachés, campos y refresh antes de llegar a ordenarlos