HotXLS crea y actualiza tablas dinámicas XLSX nativas desde Delphi y C++Builder sin necesidad de instalar Excel ni de automatización COM en la máquina. Llama a AddPivotTable con un rango de origen, arrastra campos a los ejes de fila, columna, página y datos, añade 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 activa y actualizable
El escenario que hace que esto valga la pena es un servidor de reportes. 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. Ejecutar Excel desde un servicio de Windows es frágil y no tiene licencia para uso en servidores, 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 tipados sobre las partes de tabla dinámica de OOXML, de modo que el mismo Pascal que rellena las celdas también declara la tabla dinámica y reconstruye su caché en el mismo proceso
¿Cómo crear una tabla dinámica en Delphi sin Excel?
Crea una con una sola llamada y luego coloca los campos en los ejes. AddPivotTable toma 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, crea una caché de tabla dinámica (o reutiliza una ya vinculada al mismo rango) y devuelve un TXLSPivotTable cuyos campos comienzan todos fuera del eje. A partir de ahí, los métodos prácticos AddRowField, AddColumnField, AddPageField y AddDataFieldByName configuran el diseño, y cada campo de datos toma una de las once agregaciones de la enumeración 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]; // report sheet (1-based in the XLSX engine)
// Source A1:E500 on the 'Data' sheet; anchor the pivot at row 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 comprender la división es lo que permite tener todo claro. El pivotCacheDefinition es la instantánea de datos: un worksheetSource que apunta al rango de origen y un cacheField por columna que contiene los valores distintos de esa columna (sus sharedItems) más los límites derivados. El pivotTableDefinition es la vista: qué campo de caché se ubica en qué eje, los campos de datos y sus agregaciones, y los modificadores de diseño. La vista se vincula a la caché mediante cacheId a través de la relación pivotCaches del libro de trabajo, exactamente como lo establecen ECMA-376 Parte 1 §18.10 y [MS-XLSX]
Esa indirección no es burocracia, aporta dos ventajas. Una caché puede respaldar varias tablas, por lo que actualizar la caché una vez actualiza cada vista extraída de ella. Y la caché almacena los valores de cada columna de forma deduplicada con una tabla de índices por registro en lugar de la cuadrícula bruta, que es por lo que construir una tabla dinámica significa escanear el origen, no copiar celdas. HotXLS modela esto de la misma manera para ambos motores, por lo que el código anterior es casi idéntico a la ruta clásica documentada junto con el diseño binario de registros SX detrás de las tablas dinámicas clásicas de .xls. Si su origen se encuentra en otra hoja o si se dirige a él mediante un nombre, el prefijo de rango y las referencias cruzadas y nombres definidos siguen las reglas habituales de A1, admitiéndose nombres de hoja entre comillas para nombres que contengan espacios
Los elementos calculados no son campos calculados
Estos tres términos nombran tres cosas diferentes, y confundirlos es el clásico error de las tablas dinámicas. Un elemento calculado reside dentro de un campo y combina los propios elementos de ese campo por nombre, por lo 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 diferente: es un nuevo valor derivado de otras columnas, como Margen a partir de Ingresos y Costos, y se transporta como una fórmula en un campo de caché a través de TXLSPivotCacheField.Formula. Un miembro calculado, añadido con TXLSPivotTable.AddCalculatedMember, es un miembro personalizado a nivel de tabla que puede actuar como una medida (tipo de miembro data) o un miembro de dimensión, de interés principalmente para tablas dinámicas de estructura OLAP
var
Region: TXLSPivotField;
Member: TXLSPivotCalculatedMember;
begin
// A calculated ITEM combines items of a single field by name.
Region := Pivot.AddRowField('Region');
Region.AddCalculatedItem('CoreMarkets', '=North+South');
// A calculated MEMBER is declared at the table level. Member type
// 'data' marks it as a measure; an empty type is a dimension member.
Member := Pivot.AddCalculatedMember('AvgTicket', '=Revenue/Units');
Member.MemberType := 'data';
end;Un límite honesto se aplica a los tres. HotXLS emite el texto de la fórmula en el XML de definición; no lo evalúa. Excel calcula el elemento, campo o miembro calculado cuando abre el archivo, de la misma manera que calcula cada agregado en la cuadrícula. HotXLS escribe las instrucciones, no los resultados, por lo que las fórmulas que proporcione deben ser fórmulas de tabla dinámica válidas en el propio dialecto de Excel, haciendo referencia a los nombres de campos y elementos como lo haría Excel
¿Cómo mostrar valores como un porcentaje del total?
Establece el modo de visualización de valores en el campo de datos en lugar de transformar los números usted mismo. TXLSPivotDataField.ShowDataAs toma la enumeración TXLSPivotShowDataAs, que refleja los valores ST_ShowDataAs de OOXML: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal, y xlpsdaIndex. Un truco común es colocar la misma columna de origen en el eje de datos dos veces, una como una suma bruta y otra como una proporción del gran total, de modo que el reporte 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;
// Item-relative modes need a base to compare against. A running total
// down 'Quarter' (cache-field index 3) would read:
// Share.ShowDataAs := xlpsdaRunTotal;
// Share.BaseField := 3; // cache-field index the transform runs over
// Share.BaseItem := $7FFD; // $7FFD = "(All)"
end;xlpsdaPercentOfTotal no necesita base porque es relativo al gran total, pero los modos relativos al elemento sí. xlpsdaDifference, xlpsdaPercentDiff y xlpsdaRunTotal requieren BaseField (el índice de campo de caché sobre el cual se realiza la comparación) y, para las formas ancladas al elemento, un índice BaseItem, donde $7FFD representa el centinela (All). HotXLS escribe estos como los atributos <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> y, al igual que con las fórmulas calculadas, deja la aritmética en manos de Excel
Agrupación de fechas y números, y filtros de campos de página
La agrupación se configura en el campo de caché, no en el campo de la tabla dinámica, porque cambia la forma en que se agrupa el dominio de origen. Establezca HasGroup := True en el campo de caché devuelto por Cache.FindFieldByName, luego elija los controles de rango numérico (GroupStartNum, GroupEndNum, GroupInterval) o las banderas de jerarquía de fechas (GroupByDate con GroupMonths, GroupQuarters, GroupYears, y un lapso GroupStartDate / GroupEndDate). HotXLS emite el elemento correspondiente <fieldGroup><rangePr groupBy="months"/> de modo que un campo agrupado por mes o por una banda numérica de ancho 1000 se abra de la forma en que Excel lo habría agrupado
Los campos de página son los menús desplegables de filtro situados sobre la tabla. AddPageField coloca un campo en el eje de la página, y PageItemIndex preselecciona un único elemento de caché, con el valor predeterminado xlPageItemAll ($7FFD, que significa (All)). Para permitir que un lector marque varios elementos a la vez, establezca MultipleItemSelectionAllowed := True, que HotXLS escribe como <pivotField multipleItemSelectionAllowed="1"/>. Para criterios que van más allá de una selección manual, cada campo contiene una colección Filters tipada que abarca las familias ST_FilterType de OOXML —filtros de recuento, porcentaje, suma, título, valor y fecha—, emparejando cada entrada un tipo de filtro con sus valores de comparación
¿Cómo actualizar una caché de tabla dinámica cuando cambia el origen?
RefreshPivotCache en TXLSXWorkbook vuelve a escanear el rango de origen y reconstruye la caché en su lugar, que es lo que necesita una canalización por lotes después de editar las filas subyacentes. Pase el ID de caché y el método volverá a leer cada celda en el rango de origen (encabezado en la primera fila, datos en la segunda), reconstruirá el dominio de elementos compartidos de cada campo al volver a deduplicar los valores y volver a derivar los límites por tipo, y reescribirá los índices de elementos 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
// ...the source data has changed since the pivot was built...
Sheet := Book.Sheets[1];
Sheet.Cells[2, 5].Value := 128000; // a corrected Revenue figure
// Rebuild the cache's shared items and record indices from the source.
Rc := Book.RefreshPivotCache(Pivot.CacheId); // 1 = refreshed, -1 = cache/source missing
if Rc = 1 then
Book.SaveAs('sales-pivot-refreshed.xlsx');
end;El límite semántico aquí vale la pena exponerse claramente. Antes de que existiera este método, HotXLS dependía de la bandera refreshOnLoad="1" flag y dejaba todo el trabajo de actualización a Excel en la próxima apertura, lo cual está bien cuando una persona va a abrir el archivo pero es inútil en una canalización headless que tiene que entregar datos correctos. RefreshPivotCache hace que la caché guardada esté al día en el proceso y, dado que las tablas se vinculan a una caché por ID, cada tabla dinámica extraída de esa caché ve la actualización. Lo que no hace es diseñar o agregar la cuadrícula visible —Excel sigue recalculando la tabla renderizada a partir de la caché actualizada cuando se abre el archivo
Lo que escribe HotXLS y lo que 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, con sus correspondientes ejes, agregaciones, elementos y miembros calculados, visualizaciones de valores, agrupación y filtros. Excel es el calculador: al abrirse, materializa los contenedores de grupo, evalúa las fórmulas calculadas, aplica las transformaciones showDataAs y agrega el cuerpo. Los valores que ve un usuario son los de Excel, producidos a partir de las instrucciones que escribió HotXLS, razón por la cual cada fórmula y cada índice base deben ser correctos en el momento de la escritura en lugar de comprobarse al renderizar
Vale la pena conocer dos límites antes de diseñar en torno a esta característica. AddPivotTable analiza un rango rectangular A1 con un prefijo de hoja opcional; los orígenes de rango con nombre y de libros externos quedan fuera de lo que el constructor resuelve, aunque las cachés leídas de un archivo creado con Excel conservan su origen de rango con nombre en la ida y vuelta. And Excel-created pivots that HotXLS reads from disk are replayed byte-for-byte on save, so the typed edits described here apply cleanly to pivots you build in code while existing pivots stay lossless. Para el lado de entrada de un reporte —las celdas validadas y las tablas filtradas que resume la tabla dinámica— consulte validación de datos, AutoFilter y tablas estructuradas
El modelo de tabla dinámica que se muestra aquí forma parte de HotXLS Delphi Excel Component estándar para Delphi y C++Builder, que lee y escribe tanto las partes de tabla dinámica tipadas de XLSX como los registros BIFF8 clásicos a partir del mismo modelo de objetos