El atributo axis="rowAxis" en un elemento pivotField se lee como XML perfectamente sensato, y un lector que lo mapee a "campo de fila" cargará el pivot con cada campo en el eje correcto. Pase la misma parte por un validador construido desde el esquema ECMA-376 Part 1 §18.10 y falla en el primer atributo: ST_Axis tiene exactamente cuatro tokens, axisRow, axisCol, axisPage y axisValues, y rowAxis no es uno de ellos. Un escritor de pivots tiene que satisfacer el esquema en lugar de su propio lector, porque la gente que abre el archivo usa Excel y sus propios parsers
Las definiciones de campos de pivot están llenas de reglas como esa. Tres tienden a escabullirse más allá de una prueba de round-trip: los tokens de enumeración, el requisito de que un elemento items nunca está vacío, y las entradas de subtotal finales que Excel lista después de los ítems de datos. Dos más quedan del lado de la lectura, en atributos que Excel omite porque llevan su valor por defecto del esquema. El resto de este artículo recorre cada regla, muestra cómo tiene que verse el XML, y muestra las llamadas Delphi que lo producen
Por qué un round trip no puede atrapar un token de axis equivocado
Un round trip prueba que el escritor y el lector concuerdan entre sí, y nada más. Si el lector acepta tanto axisRow como rowAxis, un escritor que emite el equivocado pasa toda verificación de guardar-y-cargar que usted pueda escribir, porque el lector repara el error en el camino de vuelta. La falla solo aparece cuando un consumidor más estricto abre la parte: un validador de esquema, un parser generado desde el XSD, o Excel decidiendo que la parte necesita reparación
HotXLS separa los dos lados a propósito. El escritor XLSX emite solo los cuatro tokens ST_Axis, mientras que el lector de pivots también mapea las grafías que parecen inglés rowAxis, colAxis, pageAxis y dataAxis al eje correspondiente, así que los archivos de productores que las usan todavía cargan con su layout intacto. La salida se rige por el esquema; la entrada solo tiene que ser reconocible. Lo inverso, un lector estricto emparejado con un escritor descuidado, al menos fallaría ruidosamente en sus propias pruebas; un lector tolerante emparejado con un escritor descuidado no falla en ningún lugar que usted pueda ver
<!-- inválido: rowAxis no es un valor ST_Axis, y CT_Items no puede estar vacío -->
<pivotField axis="rowAxis" defaultSubtotal="1"><items count="0"></items></pivotField>
<!-- válido: token ST_Axis, ítems reales, subtotal default final -->
<pivotField axis="axisRow" defaultSubtotal="1">
<items count="4"><item x="0"/><item x="1"/><item x="2" h="1"/><item t="default"/></items>
</pivotField>
Qué espera CT_PivotField más allá del axis
CT_PivotField tiene tres requisitos más que son fáciles de soltar. Un campo agregado en el área de valores debe decirlo en su propia definición con dataField="1", aunque la lista <dataFields> ya lo referencie. HotXLS pone esa bandera en cada campo de pivot al que apunte una entrada en DataFields, así que un campo agregado con AddDataFieldByName queda marcado en ambos lugares
La segunda regla es estructural. CT_Items exige al menos un hijo item, lo que hace <items count="0"></items> inválido aunque sea XML bien formado y se parsee en todas partes. Cuando un campo no tiene ítems, HotXLS deja fuera el elemento items entero. La tercera regla va sobre el estado de cada item: un miembro oculto lleva h="1" y un miembro con detalles contraídos lleva sd="0". Ambos vienen de TXLSPivotItem.IsHidden y TXLSPivotItem.IsDetailHidden y se escriben en cada guardado, así que un filtro configurado en código sobrevive hasta Excel
Ítems de subtotal finales y la trampa del conteo
Cuando un campo tiene ítems, Excel sigue los ítems de datos con un item extra por función de subtotal, tipeados con ST_ItemType. El subtotal automático es <item t="default"/>; las funciones explícitas usan sum, countA, avg, max, min, product, count, stdDev, stdDevP, var y varP. Estas entradas no llevan índice x hacia el caché, y se incluyen en el atributo count de items. Un escritor que las deja fuera produce una lista de ítems que ya no coincide con las banderas de subtotal del mismo elemento
HotXLS deriva la cola desde TXLSPivotField.Subtotals al momento de guardar: un item t="…" por función en el conjunto, en orden de enumeración, agregado al conteo, más el atributo al estilo sumSubtotal="1" correspondiente en el campo. Los campos creados por AddPivotTable arrancan con un conjunto vacío, lo que escribe defaultSubtotal="0" y ningún ítem final en absoluto. Para reportes generados, deje el conjunto vacío: las filas de subtotal cambian la altura del pivot y desplazan cualquier cosa colocada debajo. Agregue funciones a Subtotals solo en los campos donde un lector necesite las filas extra
El nombrado merece una segunda mirada antes de que usted elija una función. xlpsCount mapea a countA, que cuenta todas las entradas no vacías, y xlpsCountNums mapea a count, que cuenta solo números. Cualquiera que lea "count" en el XML y estire la mano hacia xlpsCount recibe la otra función
El programa de abajo construye un pivot desde una hoja de datos existente con un campo de fila, un campo de columna y un campo de valor. El campo Region recibe el subtotal automático más un promedio, lo que produce dos ítems finales después de sus ítems de datos y dataField="1" en Revenue
uses
SysUtils, lxHandleX, lxPivot;
var
Book : TXLSXWorkbook;
Sheet : TXLSXWorksheet;
Pivot : TXLSPivotTable;
Region: TXLSPivotField;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('orders.xlsx');
Sheet := Book.Sheets[1]; // Sheets es base 1
Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 3, 6, 'RegionTotals');
if Pivot = nil then
raise Exception.Create('Bad source range or anchor');
Region := Pivot.AddRowField('Region'); // nil si no existe el campo
if Region <> nil then
Region.Subtotals := [xlpsDefault, xlpsAverage]; // -> t="default", t="avg"
Pivot.AddColumnField('Quarter');
Pivot.AddDataFieldByName('Revenue', xlpaSum); // Revenue recibe dataField="1"
Book.SaveAs('orders-pivot.xlsx');
finally
Book.Free;
end;
end;
Leer lo que Excel omite
El lector tiene el problema de imagen especular. Un campo de pivot hecho por Excel lista sus entradas de subtotal en items, y cargarlas como miembros ordinarios le daría al campo ítems fantasma que no apuntan a ningún valor de caché. HotXLS se salta cualquier item cuyo atributo t esté presente y no sea data, lo que cubre entradas de subtotal, total general y vacías. Las funciones de subtotal llegan a través de las banderas del propio campo y aterrizan en Subtotals, así que el código que recorre Items ve solo miembros reales y cada CacheItemIndex refiere a un valor de caché
Los atributos ausentes son la otra mitad. En el esquema, defaultSubtotal en CT_PivotField y containsString en CT_SharedItems tienen ambos por defecto true, y Excel los omite cuando llevan ese valor. HotXLS lee un defaultSubtotal ausente como true y agrega xlpsDefault a Subtotals cuando el campo no lleva banderas de subtotal explícitas, que es como el propio Excel muestra tal campo. Un containsString ausente también se lee como true, así que un campo de caché de texto puro se clasifica como data de strings. Lea cualquiera de los dos atributos como false y cada pivot guardado por Excel pierde sus subtotales automáticos al cargar, mientras que los campos de texto se convierten en algún otro tipo. Un escritor que siempre deletrea cada atributo nunca toca este camino; solo los archivos de otro productor lo ejercitan
Un workbook cargado de Excel es la entrada de prueba correcta aquí, porque es el único tipo de archivo que omite esos atributos. La verificación de abajo abre uno y recorre un campo de fila:
var
Book : TXLSXWorkbook;
Pivot: TXLSPivotTable;
Fld : TXLSPivotField;
I, J : Integer;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('saved-by-excel.xlsx');
Pivot := Book.Sheets[1].PivotTables.FindByName('PivotTable1');
if Pivot = nil then
Exit;
for I := 0 to Pivot.FieldCount - 1 do
begin
Fld := Pivot.Fields[I];
if Fld.Axis <> xlpfaRowField then
Continue;
// Un campo de Excel sin atributos de subtotal igual reporta el default
if xlpsDefault in Fld.Subtotals then
Writeln(Fld.Name, ': automatic subtotal');
// Las entradas de subtotal se saltaron, así que cada item mapea a un valor de caché
for J := 0 to Fld.ItemCount - 1 do
Assert(Fld.Items[J].CacheItemIndex >= 0); // verificación sugerida para sus propias pruebas
end;
finally
Book.Free;
end;
end;
Por qué numFmtId="General" es inválido en los cache fields
numFmtId="General" es inválido porque ST_NumFmtId es un entero sin signo, por más natural que sea pedir prestado el nombre que los usuarios ven en el diálogo Format Cells. Un parser que tipa atributos desde el esquema rechaza el nombre de plano, y esa clase de falla es exactamente la que se convierte en un diálogo de reparación en Excel; el artículo sobre las reglas OPC y de markup detrás del diálogo de reparación de Excel cubre cómo se disparan esos diálogos. HotXLS escribe cada cacheField con un numFmtId numérico tomado del NumberFormat del cache field, que es 0, el formato General incorporado, salvo que algo lo haya configurado
Las tablas dinámicas por debajo de la fila 65535 necesitan posiciones de 32 bits
Un modelo de pivot compartido por los motores XLS y XLSX se ve tentado a pedir prestados sus anchos de campo del registro SxView de BIFF8, donde 16 bits cubren cada fila que una hoja clásica puede contener. Una hoja XLSX llega a 1,048,576 filas. Guarde FirstRow, LastRow, FirstHeaderRow, FirstDataRow y las contrapartes de columna como Word, recórtelos con Min(Value, High(Word)), y un pivot anclado en la fila 70001 queda cortado en 65535
En TXLSPivotTable esas propiedades son Integer, y solo el escritor BIFF8 las angosta en el límite del registro. TXLSXWorksheet.AddPivotTable devuelve nil para un ancla fuera de 1..1048576 por 1..16384, y AddPivotTableCopy devuelve nil cuando la extensión de la copia se saldría de la grilla
var
Book, Check: TXLSXWorkbook;
Pivot : TXLSPivotTable;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('orders.xlsx');
// La fila 70001 está más allá del rango de 16 bits
Pivot := Book.Sheets[1].AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
if Pivot = nil then
Exit; // ancla fuera de la hoja o rango fuente irresoluble
Pivot.AddRowField('Region');
Pivot.AddDataFieldByName('Revenue', xlpaSum);
Book.SaveAs('late.xlsx');
finally
Book.Free;
end;
Check := TXLSXWorkbook.Create;
try
Check.Open('late.xlsx');
Pivot := Check.Sheets[1].PivotTables.FindByName('LateTotals');
// Assert es una verificación sugerida para sus propias pruebas
Assert((Pivot <> nil) and (Pivot.FirstRow = 70001));
finally
Check.Free;
end;
end;
El motor XLS clásico sigue la misma convención base 1. Los registros SxView y DConRef almacenan valores base 0, mientras que el modelo, como el motor XLSX y Cells[Row, Col], conserva posiciones base 1: el lector BIFF8 suma 1 y el escritor resta 1 en el límite del registro. El AddPivotTable clásico devuelve nil para un ancla fuera de 1..65536 por 1..256, así que ancle los pivots en (1, 1) en lugar de (0, 0). El layout del registro en sí está descrito en los registros SX de BIFF8 detrás de las tablas dinámicas del .xls clásico
Probar contra el esquema en lugar de su propio lector
Cada trampa de arriba sobrevive a un round trip cuando el lado tolerante y el lado defectuoso viven en la misma biblioteca. Tres clases de verificación los atrapan. Una validación de esquema de las partes generadas pivotTableN.xml y pivotCacheDefinitionN.xml atrapa tokens inventados, elementos items vacíos e ids de formato con nombre. Archivos guardados por Excel, pasados por su lector, ejercitan los atributos que Excel omite en sus valores por defecto. Aserciones que fijan el token exacto en el XML escrito, como axis="axisRow", atrapan a un escritor que deriva hacia una grafía que solo su lector entiende; aser sobre el axis parseado no puede
Los pivots construidos a través del API, incluidos los calculated fields, calculated items y layouts de porcentaje del total que muestra construir y refrescar tablas dinámicas XLSX con calculated fields, salen con estas reglas aplicadas y sin código extra de su lado. El trabajo que le queda es elegir qué campos llevan subtotales y qué función de conteo quiere decir
El HotXLS Delphi spreadsheet component construye, lee y guarda tablas dinámicas XLS y XLSX desde código Delphi y C++Builder, sin instalación de Excel ni automatización COM en la máquina que lo ejecuta