Validez de esquema de campos pivot de XLSX en HotXLS

HotXLS escribe definiciones de tablas dinámicas XLSX cuyos elementos pivotField y cacheField validan contra el esquema ECMA-376 Parte 1 §18.10: los atributos axis usan los tokens ST_Axis axisRow, axisCol y axisPage, los campos del área de valores llevan dataField="1", las listas de items nunca están vacías, y los campos de caché guardan un numFmtId numérico. Desde v2.384.33 el lector también honra los defaults del esquema que solía torcer

Los bugs detrás de esta limpieza comparten un rasgo poco halagüeño: ninguno suspendió jamás un test. HotXLS escribía un pivot, HotXLS lo releía, cada campo aterrizaba en el axis correcto, y la suite de round-trip estuvo en verde durante años. El problema era que el escritor y el lector habían acordado en secreto un dialecto privado. Un pivot construido desde Delphi se veía bien ante el componente que lo hizo, mientras que una comprobación contra CT_PivotField y CT_CacheField destapaba tokens de enumeración inválidos, un elemento vacío que el esquema prohíbe y flags que Excel espera pero nunca recibió. Si generas pivots en un servidor y se los envías a gente que los abre en Excel o se los pasa a sus propios parsers, el único contrato que cuenta es el esquema, no lo que tu propio lector le pase por alto

¿Por qué los round trips de HotXLS nunca cazaron los tokens de axis equivocados?

Los round trips de HotXLS nunca cazaron los tokens de axis equivocados porque el lector aceptaba ambas grafías. El viejo XlsxPivotAxisAttr emitía axis="rowAxis", colAxis y pageAxis, que se leen con naturalidad en inglés pero no existen en el esquema; ST_Axis define exactamente cuatro valores, axisRow, axisCol, axisPage y axisValues. Entretanto, PivotAxisFromToken en lxPivotXml.pas aceptaba tanto el token del esquema como el inventado, así que todos los autotests pasaban. El escritor ahora emite solo los tokens del esquema, y el lector sigue aceptando las viejas grafías para que los archivos guardados por versiones anteriores de HotXLS sigan cargando con su layout intacto

<!-- antes de v2.384.33: valor ST_Axis inválido, CT_Items vacío -->
<pivotField axis="rowAxis" defaultSubtotal="1"><items count="0"></items></pivotField>

<!-- desde v2.384.33 -->
<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é exige CT_PivotField que el viejo escritor se saltaba?

CT_PivotField exige tres cosas que el viejo BuildPivotTableXml omitía o torcía. Primera, un campo agregado en el área de valores debe decirlo en su propia definición con dataField="1"; el escritor ahora pone ese flag en cada campo referenciado por una entrada de DataFields, y no solo en la lista <dataFields>. Segunda, CT_Items necesita al menos un item, así que un campo sin items ya no recibe un <items count="0"> vacío y el elemento entero simplemente se omite. Tercera, cada item conserva su estado: h="1" para un item oculto (TXLSPivotItem.IsHidden) y sd="0" para detalles plegados (IsDetailHidden), ambos caídos en cada guardado del viejo escritor

Lo sutil son los items de subtotal finales. Cuando un campo tiene items, Excel lista un item extra por función de subtotal después de los items de datos, tipados con ST_ItemType: <item t="default"/> para el subtotal automático, y luego sum, countA, avg, max, min, product, count, stdDev, stdDevP, var y varP para los explícitos. HotXLS deriva esas entradas de TXLSPivotField.Subtotals al guardar y las cuenta dentro de items count. Los campos creados por AddPivotTable arrancan con un conjunto Subtotals vacío, que escribe defaultSubtotal="0" y ningún item final, así que pide los subtotals explícitamente cuando el informe los necesita. Fíjate en la trampa de nombres: xlpsCount mapea a countA (todas las entradas) y xlpsCountNums mapea a count (solo números)

uses
  lxHandleX, lxPivot;

var
  Book  : TXLSXWorkbook;
  Sheet : TXLSXWorksheet;
  Pivot : TXLSPivotTable;
  Region: TXLSPivotField;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[1];                  // base 1, como el motor XLS
    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 tal campo
    if Region <> nil then
      Region.Subtotals := [xlpsDefault, xlpsAverage];  // -> t="default", t="avg"
    Pivot.AddColumnField('Quarter');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);      // marca Revenue con dataField="1"

    Book.SaveAs('orders-pivot.xlsx');
  finally
    Book.Free;
  end;
end;

¿Cómo lee HotXLS los items de subtotal y los defaults del esquema ahora?

El lector de HotXLS ahora se salta cualquier item cuyo atributo t esté presente y no sea data, porque las entradas de subtotal, gran total y vacío no llevan índice de caché. Antes de v2.384.34 esas entradas se cargaban como items ordinarios con CacheItemIndex a -1, así que un pivot hecho por Excel volvía con miembros fantasma que no apuntaban a ninguna parte, y cualquier código que recorriera Items tenía que filtrarlos a mano. Como el escritor reconstruye las entradas finales desde Subtotals, el trabajo del lector es traducirlas a ese conjunto, no conservarlas como datos

El segundo fix del lector va de atributos ausentes. En el esquema, defaultSubtotal en CT_PivotField y containsString en CT_SharedItems valen true por defecto, y Excel los omite cuando llevan ese default. HotXLS leía un atributo ausente como false, con lo que cada pivot guardado por Excel perdía en silencio su subtotal por defecto al cargar, y un campo de caché de texto plano se clasificaba como mixto en vez de cadena. Es la imagen especular del bug del axis: un escritor que siempre escribe todos los atributos jamás ejercita el camino del default, así que solo los archivos de otro productor lo exponen

¿Por qué era inválido numFmtId="General" en los cache fields?

El valor numFmtId="General" era inválido porque ST_NumFmtId es un entero sin signo, no un nombre de formato. El viejo escritor de caché metía esa cadena a fuego en cada cacheField, tomando prestado el nombre que los usuarios ven en el diálogo Formato de celdas. HotXLS escribe ahora el NumberFormat del campo de caché como número, que es 0 (el formato General integrado) salvo que algo lo haya cambiado. Un parser estricto que tipa los atributos desde el esquema rechaza el viejo valor sin más, y esa es exactamente la clase de fallo que degenera en un diálogo de reparación; el artículo sobre las reglas OPC y de markup detrás del aviso de reparación de Excel cubre cómo se disparan esos diálogos

¿Por qué se cortaban las tablas dinámicas en la fila 65535 o inferior?

Las tablas dinámicas XLSX colocadas en la fila 65536 o por debajo se cortaban porque el modelo compartido de pivot guardaba FirstRow, LastRow, FirstHeaderRow, FirstDataRow y sus equivalentes de columna como Word, y el código de desplazamiento de filas los recortaba con Min(.., High(Word)). Es un resto del record SxView de BIFF8, donde 16 bits alcanzan, pero una hoja XLSX llega a 1.048.576 filas. Desde v2.384.37 esas propiedades de TXLSPivotTable son Integer, los recortes han desaparecido, y solo el escritor BIFF8 estrecha los valores. TXLSXWorksheet.AddPivotTable y AddPivotTableCopy devuelven ahora nil para un ancla fuera de 1..1048576 por 1..16384, o para una copia cuya extensión se saldría de la cuadrícula

var
  Pivot: TXLSPivotTable;
  Check: TXLSXWorkbook;
begin
  // La fila 70001 se envolvía al rango de 16 bits; ahora sobrevive a guardar y cargar
  Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
  if Pivot = nil then
    Exit;  // ancla fuera de la hoja o rango origen irresoluble
  Pivot.AddRowField('Region');
  Pivot.AddDataFieldByName('Revenue', xlpaSum);
  Book.SaveAs('late.xlsx');

  Check := TXLSXWorkbook.Create;
  try
    Check.Open('late.xlsx');
    Pivot := Check.Sheets[1].PivotTables.FindByName('LateTotals');
    Assert((Pivot <> nil) and (Pivot.FirstRow = 70001));
  finally
    Check.Free;
  end;
end;

El motor XLS clásico recibió el fix correspondiente en v2.384.38. Su modelo guardaba los valores crudos base 0 de SxView y DConRef y dejaba pasar las anclas de AddPivotTable tal cual, mientras la documentación, las demos y el motor XLSX usaban celdas base 1 como Cells[Row, Col]. Ambos motores guardan ahora posiciones base 1 en el modelo, el lector BIFF8 suma 1 y el escritor resta 1 en el límite del record, así que el código que anclaba en (0, 0) debe pasar a (1, 1), porque el AddPivotTable clásico devuelve ahora nil para un ancla fuera de 1..65536 por 1..256; la nueva llamada escribe los mismos bytes que la vieja. El layout del record en sí no cambia y está descrito en los records SX de BIFF8 detrás de las tablas dinámicas .xls clásicas

Valida contra el esquema, no contra tu propio lector

La lección generaliza más allá de los pivots: un lector indulgente esconde violaciones del escritor, así que un round trip por tu propio código prueba consistencia, no corrección. Cada bug de aquí sobrevivió porque el lado tolerante y el lado defectuoso vivían en la misma biblioteca. Las comprobaciones que de verdad cazan esta clase de defecto son una validación de esquema de las partes generadas, archivos producidos por Excel pasados por tu lector con atributos omitidos en sus defaults, y fixtures que fijen el token exacto en vez del resultado parseado. Los pivots que construyes vía la API, incluidos los campos calculados, los items calculados y los layouts de porcentaje del total que se muestran en construir y refrescar tablas dinámicas XLSX con campos calculados, reciben el XML corregido sin cambio de código, mientras que los pivots cargados de archivos de Excel siguen reproduciendo sus partes originales hasta que los modifiques

Todos estos fixes vienen en el componente de hojas de cálculo HotXLS para Delphi actual, que lee y escribe XLS, XLSX y tablas dinámicas desde Delphi y C++Builder sin Excel ni automatización COM en la máquina