Crear y actualizar tablas dinámicas en Delphi con HotXLS

HotXLS crea y actualiza tablas dinámicas XLSX nativas desde Delphi y C++Builder sin Excel instalado ni automatización COM en la máquina. Usted llama a AddPivotTable con un rango de origen, coloca campos en los ejes de fila, columna, página y datos, agrega elementos calculados o una visualización de porcentaje del total, y el componente escribe las partes pivotCacheDefinition y pivotTableDefinition que Excel abre como una tabla dinámica viva y actualizable

El escenario que hace que esto valga la pena es un servidor de informes. Usted genera cientos de libros de trabajo por noche, cada uno con una tabla dinámica que resume una cuenta, y el mes siguiente los números cambian y cada archivo tiene que reflejar las nuevas filas de origen. Manejar Excel desde un servicio de Windows es frágil y no está licenciado para uso en servidor, y escribir a mano el XML de la tabla dinámica es un proyecto de arqueología de especificaciones que nunca termina. HotXLS se sitúa entre esos dos callejones sin salida: un modelo de objetos tipado sobre las partes OOXML de la tabla dinámica, de modo que el mismo Pascal que llena las celdas también declara la tabla dinámica y reconstruye su caché en el mismo proceso

¿Cómo se crea una tabla dinámica en Delphi sin Excel?

Se crea con una sola llamada y luego se colocan campos en los ejes. AddPivotTable recibe el rango de origen en notación A1, la celda de destino donde se ancla la tabla y un nombre; analiza el rango, escanea cada columna para inferir su tipo de datos, construye una caché dinámica (o reutiliza una ya vinculada al mismo rango) y devuelve un TXLSPivotTable cuyos campos empiezan todos fuera de los ejes. Desde ahí los métodos de conveniencia AddRowField, AddColumnField, AddPageField y AddDataFieldByName conectan el diseño, y cada campo de datos toma una de once agregaciones del enum TXLSPivotAggregation (xlpaSum, xlpaCount, xlpaAverage, xlpaMax, xlpaMin, xlpaProduct, xlpaCountNums, xlpaStdDev, xlpaStdDevP, xlpaVar, xlpaVarP)

uses
  lxHandleX, lxPivot;

var
  Book : TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Pivot: TXLSPivotTable;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('sales.xlsx');
    Sheet := Book.Sheets[1];                 // hoja de informe (base 1 en el motor XLSX)

    // Origen A1:E500 en la hoja 'Data'; anclar la tabla dinámica en fila 3, col 1.
    Pivot := Sheet.AddPivotTable('Data!$A$1:$E$500', 3, 1, 'SalesByRegion');
    if Pivot <> nil then
    begin
      Pivot.AddRowField('Region');
      Pivot.AddColumnField('Quarter');
      Pivot.AddPageField('Year');
      Pivot.AddDataFieldByName('Revenue', xlpaSum);
      Pivot.AddDataFieldByName('Units', xlpaAverage);
      Book.SaveAs('sales-pivot.xlsx');
    end;
  finally
    Book.Free;
  end;
end;

Por qué la caché y la tabla son dos partes separadas

Una tabla dinámica es en realidad dos artefactos que se referencian mutuamente, y entender esa división es lo que mantiene claro el resto. El pivotCacheDefinition es la instantánea de datos: un worksheetSource que apunta al rango de origen, y un cacheField por columna que guarda los valores distintos de esa columna (sus sharedItems) más los límites derivados. El pivotTableDefinition es la vista: qué campo de caché va en qué eje, los campos de datos y sus agregaciones, los conmutadores de diseño. La vista se vincula a la caché por cacheId a través de la relación pivotCaches del libro de trabajo, exactamente como lo describen ECMA-376 Parte 1 §18.10 y [MS-XLSX]

Esa indirección no es burocracia, compra dos cosas. Una caché puede respaldar varias tablas, así que actualizar la caché una vez refresca todas las vistas derivadas de ella. Y la caché almacena los valores de cada columna deduplicados con una tabla de índices por registro en lugar de la cuadrícula en bruto, y por eso construir una tabla dinámica significa escanear el origen, no copiar celdas. HotXLS modela esto de la misma manera para ambos motores, así que el código anterior es casi idéntico a la ruta clásica documentada junto a la disposición binaria de registros SX detrás de las tablas dinámicas .xls clásicas. Si su origen vive en otra hoja o lo direcciona mediante un nombre, el prefijo de rango y los nombres definidos y referencias entre hojas siguen las reglas A1 habituales, con nombres de hoja entre comillas permitidos para nombres que contienen espacios

Los elementos calculados no son campos calculados

Estos tres términos nombran tres cosas distintas, y confundirlos es el error clásico de las tablas dinámicas. Un elemento calculado vive dentro de un campo y combina los propios elementos de ese campo por nombre, así que dentro de un campo Region puede definir una fila sintética CoreMarkets igual a North más South; HotXLS lo expone como TXLSPivotField.AddCalculatedItem y escribe un <calculatedItem> bajo los <calculatedItems> de ese campo. Un campo calculado es distinto: es un valor nuevo derivado de otras columnas, como Margin a partir de Revenue y Cost, y se transporta como fórmula en un campo de caché mediante TXLSPivotCacheField.Formula. Un miembro calculado, agregado con TXLSPivotTable.AddCalculatedMember, es un miembro personalizado a nivel de tabla que puede actuar como medida (tipo de miembro data) o como miembro de dimensión, de interés principalmente para tablas dinámicas con forma OLAP

var
  Region: TXLSPivotField;
  Member: TXLSPivotCalculatedMember;
begin
  // Un ELEMENTO calculado combina elementos de un solo campo por nombre.
  Region := Pivot.AddRowField('Region');
  Region.AddCalculatedItem('CoreMarkets', '=North+South');

  // Un MIEMBRO calculado se declara a nivel de tabla. El tipo de miembro
  // 'data' lo marca como medida; un tipo vacío es un miembro de dimensión.
  Member := Pivot.AddCalculatedMember('AvgTicket', '=Revenue/Units');
  Member.MemberType := 'data';
end;

Un límite honesto aplica a los tres. HotXLS emite el texto de la fórmula en el XML de definición; no la evalúa. Excel calcula el elemento, campo o miembro calculado cuando abre el archivo, de la misma manera que calcula cada agregado de la cuadrícula. HotXLS escribe las instrucciones, no los resultados, así que las fórmulas que usted proporcione tienen que ser fórmulas de tabla dinámica válidas en el propio dialecto de Excel, referenciando nombres de campo y elemento como lo haría Excel

¿Cómo se muestran los valores como porcentaje del total?

Se fija el modo de visualización de valores en el campo de datos en lugar de transformar los números usted mismo. TXLSPivotDataField.ShowDataAs recibe el enum TXLSPivotShowDataAs, que refleja los valores OOXML de ST_ShowDataAs: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal y xlpsdaIndex. Un truco común es colocar la misma columna de origen dos veces en el eje de datos, una como suma en bruto y otra como participación en el total general, para que el informe muestre tanto el número como su peso

var
  Rev, Share: TXLSPivotDataField;
begin
  Rev := Pivot.AddDataFieldByName('Revenue', xlpaSum);

  Share := Pivot.AddDataFieldByName('Revenue', xlpaSum);
  Share.DisplayName := 'Share of total';
  Share.ShowDataAs  := xlpsdaPercentOfTotal;

  // Los modos relativos a elementos necesitan una base de comparación. Un total
  // acumulado a lo largo de 'Quarter' (índice de campo de caché 3) se leería:
  //   Share.ShowDataAs := xlpsdaRunTotal;
  //   Share.BaseField  := 3;         // índice del campo de caché sobre el que corre la transformación
  //   Share.BaseItem   := $7FFD;     // $7FFD = "(All)"
end;

xlpsdaPercentOfTotal no necesita base porque es relativo al total general, pero los modos relativos a elementos sí. xlpsdaDifference, xlpsdaPercentDiff y xlpsdaRunTotal requieren BaseField (el índice del campo de caché sobre el que corre la comparación) y, para las formas ancladas a un elemento, un índice BaseItem, donde $7FFD representa el centinela (All). HotXLS los escribe como los atributos <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> y, como con las fórmulas calculadas, deja la aritmética a Excel

Agrupar fechas y números, y filtros de campo de página

La agrupación se configura en el campo de caché, no en el campo dinámico, porque cambia cómo se agrupa en cubetas el dominio de origen. Fije HasGroup := True en el campo de caché que devuelve Cache.FindFieldByName, y luego elija las perillas de rango numérico (GroupStartNum, GroupEndNum, GroupInterval) o las banderas de jerarquía de fechas (GroupByDate con GroupMonths, GroupQuarters, GroupYears y un intervalo GroupStartDate / GroupEndDate). HotXLS emite el elemento <fieldGroup><rangePr groupBy="months"/> correspondiente, de modo que un campo agrupado por mes o por una banda numérica de ancho 1000 se abre tal como Excel lo habría agrupado

Los campos de página son los desplegables de filtro sobre la tabla. AddPageField coloca un campo en el eje de página, y PageItemIndex preselecciona un único elemento de caché, con xlPageItemAll ($7FFD, que significa (All)) como valor predeterminado. Para que un lector marque varios elementos a la vez, fije MultipleItemSelectionAllowed := True, que HotXLS escribe como <pivotField multipleItemSelectionAllowed="1"/>. Para criterios más allá de una selección manual, cada campo lleva una colección Filters tipada que abarca las familias OOXML de ST_FilterType (filtros de conteo, porcentaje, suma, leyenda, valor y fecha), cada entrada emparejando un tipo de filtro con sus valores de comparación

¿Cómo se actualiza una caché dinámica cuando cambia el origen?

RefreshPivotCache en TXLSXWorkbook vuelve a escanear el rango de origen y reconstruye la caché en el lugar, que es lo que necesita un pipeline por lotes después de editar las filas subyacentes. Pase el id de la caché, y el método vuelve a leer cada celda del rango de origen (encabezado en la primera fila, datos desde la segunda), reconstruye el dominio de elementos compartidos de cada campo volviendo a deduplicar los valores y a derivar los límites por tipo, y reescribe los índices de elemento por registro. Devuelve 1 en caso de éxito y -1 cuando el id de caché es desconocido o falta la hoja de origen

var
  Rc: Integer;
begin
  // ...los datos de origen cambiaron desde que se construyó la tabla dinámica...
  Sheet := Book.Sheets[1];
  Sheet.Cells[2, 5].Value := 128000;             // una cifra de Revenue corregida

  // Reconstruir los elementos compartidos y los índices de registro de la caché desde el origen.
  Rc := Book.RefreshPivotCache(Pivot.CacheId);   // 1 = actualizada, -1 = falta la caché o el origen
  if Rc = 1 then
    Book.SaveAs('sales-pivot-refreshed.xlsx');
end;

El límite semántico aquí merece enunciarse con claridad. Antes de que existiera este método, HotXLS dependía de la bandera refreshOnLoad="1" y dejaba todo el trabajo de actualización a Excel en la siguiente apertura, lo cual está bien cuando un humano abrirá el archivo pero es inútil en un pipeline sin interfaz que tiene que entregar datos correctos. RefreshPivotCache pone al día la caché guardada en proceso, y como las tablas se vinculan a una caché por id, cada tabla dinámica derivada de esa caché ve la actualización. Lo que no hace es distribuir ni agregar la cuadrícula visible: Excel sigue recalculando la tabla renderizada a partir de la caché actualizada cuando el archivo se abre

Qué escribe HotXLS y qué calcula Excel

Mantenga a la vista la división del trabajo y nada le sorprenderá. HotXLS es un escritor de definiciones: produce el pivotCacheDefinition, los registros de caché y el pivotTableDefinition, completos con ejes, agregaciones, elementos y miembros calculados, visualizaciones de valores, agrupación y filtros. Excel es la calculadora: al abrir, materializa las cubetas de grupo, evalúa las fórmulas calculadas, aplica las transformaciones showDataAs y agrega el cuerpo. Los valores que ve un usuario son de Excel, producidos a partir de las instrucciones que escribió HotXLS, y por eso cada fórmula e índice base tiene que ser correcto en el momento de escribir y no comprobado en el momento de renderizar

Dos límites conviene conocer antes de diseñar en torno a esta función. AddPivotTable analiza un rango A1 rectangular con un prefijo de hoja opcional; los orígenes de rango con nombre y de libro externo quedan fuera de lo que el constructor resuelve, aunque las cachés leídas de un archivo creado en Excel conservan su origen de rango con nombre en la ida y vuelta. Y las tablas dinámicas creadas en Excel que HotXLS lee del disco se reproducen byte a byte al guardar, así que las ediciones tipadas descritas aquí aplican limpiamente a las tablas dinámicas que construye en código mientras que las existentes permanecen sin pérdidas. Para el lado de entrada de un informe (las celdas validadas y las tablas filtradas que la tabla dinámica resume), vea validación de datos, AutoFilter y tablas estructuradas

El modelo de tabla dinámica mostrado aquí forma parte del HotXLS Delphi Excel Component estándar para Delphi y C++Builder, que lee y escribe tanto las partes tipadas de tabla dinámica XLSX como los registros BIFF8 clásicos desde el mismo modelo de objetos