Artículo técnico

Campos de tabla dinámica XLSX válidos en Delphi con HotXLS

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>
XML pivotField de HotXLS antes y después de la v2.384.33 donde el valor de axis inventado rowAxis y un elemento items vacío violan CT_PivotField hasta que el escritor emite tokens ST_Axis como axisRow con entradas item reales, un flag hidden conservado y un subtotal default final que el esquema acepta
El lector tolerante aceptaba ambas escrituras, así que cada round trip pasaba mientras el archivo reventaba cualquier chequeo estricto de esquema — escriba solo los cuatro tokens ST_Axis y deje que CT_Items lleve al menos un item

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

Anatomía de la lista de items de HotXLS donde las entradas de items de datos van seguidas de ítems finales de subtotal derivados de TXLSPivotField.Subtotals como t=default y t=avg y contados dentro del items count, con la trampa de nombres xlpsCount a countA y xlpsCountNums a count explicada
Los campos de AddPivotTable arrancan con un set Subtotals vacío, que escribe defaultSubtotal=0 y ningún ítem final — pida las funciones que quiere y el escritor deriva un item por función dentro del conteo

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

Ancla de pivot de HotXLS en la fila 70001 contra el techo de 16 bits donde FirstRow y LastRow se guardaban como Word y se recortaban con Min contra High(Word) en 65535, cortando pivots en o bajo la línea hasta que la v2.384.37 movió el modelo a campos Integer con devolución de nil fuera de la grilla
Los campos Word eran un sobrante del SxView de BIFF8 en un formato cuyas hojas llegan a 1048576 filas — un ancla más allá de la fila 65536 daba la vuelta al rango de 16 bits y perdía su pivot al guardar
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