HotXLS crea y actualiza tablas dinámicas nativas de XLSX desde Delphi y C++Builder sin necesidad de tener Excel instalado ni automatización COM en el equipo. Usted llama a AddPivotTable con un rango de origen, coloca campos en los ejes de fila, columna, página y datos, añade capas de 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. Usted genera cientos de libros de trabajo por noche, cada uno con una tabla dinámica que resume una cuenta, y al mes siguiente los números cambian y cada archivo debe reflejar las nuevas filas de origen. Ejecutar Excel desde un servicio de Windows es inestable y no tiene licencia para uso en servidores, y escribir manualmente 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 de la tabla dinámica OOXML, de modo que el mismo código 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?
Usted 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, construye 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 estructuran 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 consta en realidad de dos elementos que se referencian mutuamente, y comprender esta división es lo que mantiene claro el resto. El pivotCacheDefinition es la captura instantánea de los 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 selectores 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 sola caché puede respaldar varias tablas, por lo que actualizar la caché una vez actualiza cada vista extraída de ella. Además, la caché almacena los valores de cada columna sin duplicados mediante 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 a la estructura binaria de registros SX detrás de las tablas dinámicas clásicas de .xls. Si su origen reside en otra hoja o si lo direcciona a través de un nombre, el prefijo de rango y los nombres definidos y referencias entre hojas siguen las reglas habituales de A1, permitiéndose nombres de hojas 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, de modo que dentro de un campo Región usted puede definir una fila sintética CoreMarkets igual a Norte más Sur; HotXLS lo expone como TXLSPivotField.AddCalculatedItem y escribe un <calculatedItem> bajo la etiqueta <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 maneja como una fórmula en un campo de caché a través de TXLSPivotCacheField.Formula. Un miembro calculado, agregado 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 estructuradas tipo 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 real se aplica a los tres casos. 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 usted proporcione deben ser fórmulas de tabla dinámica válidas en el dialecto propio de Excel, haciendo referencia a nombres de campos y elementos tal como lo haría Excel
¿Cómo se muestran los valores como porcentaje del total?
Usted configura el modo de visualización del valor en el campo de datos en lugar de transformar los números por su cuenta. TXLSPivotDataField.ShowDataAs toma la enumeración 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 en el eje de datos dos veces, una como una suma bruta y otra como una porción del gran total, de modo que el reporte muestre tanto el número como su peso relativo
xlpsdaPercentOfTotal no necesita una base porque es relativo al total general, pero los modos relativos a elementos sí la requieren. xlpsdaDifference, xlpsdaPercentDiff y xlpsdaRunTotal requieren BaseField (el índice del campo de caché sobre el cual se realiza la comparación) y, para las formas ancladas a elementos, 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 campo de página
La agrupación se configura en el campo de caché, no en el campo de la tabla dinámica, porque cambia cómo se segmenta 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 intervalo 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 1000 de ancho se abra de la forma en que Excel lo habría agrupado
Los campos de página son los menús 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 el valor predeterminado xlPageItemAll ($7FFD, que significa (All)). Para permitir que un lector marque varios elementos a la vez, configure MultipleItemSelectionAllowed := True, lo cual HotXLS escribe como <pivotField multipleItemSelectionAllowed="1"/>. Para criterios que superen una selección manual, cada campo contiene una colección Filters tipada que abarca las familias OOXML de ST_FilterType (filtros de conteo, porcentaje, suma, título, valor y fecha), emparejando cada entrada un tipo de filtro con sus valores de comparación
¿Cómo se actualiza 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 requiere un flujo de trabajo por lotes después de editar las filas subyacentes. Pase el ID de la caché, y el método volverá a leer cada celda en el rango de origen (encabezado en la primera fila, datos a partir de la segunda), reconstruirá el dominio de elementos compartidos de cada campo eliminando duplicados y volviendo a obtener los límites de cada tipo, y reescribirá los índices de elementos por registro. Devuelve 1 en caso de éxito y -1 cuando el ID de la 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;Lo que HotXLS escribe y lo que Excel calcula
Tenga en cuenta 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 ejes, agregaciones, elementos y miembros calculados, pantallas de valor, agrupaciones y filtros. Excel es el calculador: al abrirlo materializa los segmentos 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, generados a partir de las instrucciones que escribió HotXLS, razón por la cual cada fórmula e índice base debe ser correcto al momento de escribir en lugar de verificarse al renderizar
Vale la pena conocer dos límites antes de diseñar en torno a esta característica. AddPivotTable analiza un rango A1 rectangular con un prefijo de hoja opcional; los rangos con nombre y los orígenes de libros externos están fuera de lo que el generador resuelve, aunque las cachés leídas de un archivo creado en Excel conservan su origen de rango con nombre en el viaje de ida y vuelta. Y las tablas dinámicas creadas por Excel que HotXLS lee del disco se reproducen byte por byte al guardar, por lo que las ediciones tipadas descritas aquí se aplican de manera limpia a las tablas dinámicas que usted cree en el código, mientras que las existentes se mantienen sin pérdidas. 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 mostrado aquí forma parte de la versión estándar de HotXLS Delphi Excel Component para Delphi y C++Builder, el cual 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