HotXLS fija los modos "Show Values As" de las tablas dinámicas de Excel desde Delphi a través de dos propiedades de TXLSPivotDataField: ShowDataAs para los modos clásicos como running total, diferencia y porcentaje de fila, y ExtendedShowDataAs para rank, percent of parent y running total percent. TXLSXWorkbook.MaterializeNativePivotTable luego escribe la cuadrícula calculada de la forma en que Excel lo haría. Las dos propiedades son mutuamente excluyentes, y las extendidas dependen de un URI de extensión que Excel hace match de forma sensible a mayúsculas
Ese último detalle es la razón de que exista este post. Una tabla dinámica con una columna de rank parece algo pequeño de generar, y para la primera apertura usualmente lo es. El problema empieza cuando un usuario hace clic en Refresh en Excel y la columna de rank vuelve a ser una suma simple sin ningún aviso. Hacer Show Values As bien significa hacer bien dos vocabularios XML, más un puñado de reglas numéricas que Excel nunca documenta en la UI: cómo se rankan los empates, qué significa un porcentaje acumulado de un promedio, y qué celda vacía se vuelve blanco en lugar de un error
¿Por qué hay dos propiedades para un solo menú de Excel?
Excel muestra un solo menú "Show Values As", pero el archivo lo guarda en dos lugares distintos, así que HotXLS expone dos propiedades. Los modos originales de Excel 2007 viven en el atributo showDataAs del elemento dataField dentro de pivotTableDefinition, usando la enumeración ST_ShowDataAs de ECMA-376. Los modos que Excel 2010 agregó, rank y los porcentajes de padre y acumulados, no cupieron en esa enumeración, así que fueron a un elemento x14:dataField con un atributo pivotShowAs, envuelto en un bloque de extensión extLst sobre el mismo data field
TXLSPivotShowDataAs refleja el primer grupo: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal y xlpsdaIndex. TXLSPivotExtendedShowDataAs cubre el segundo: xlpesdaPercentOfParent, xlpesdaPercentOfParentRow, xlpesdaPercentOfParentColumn, xlpesdaPercentOfRunningTotal, xlpesdaRankAscending y xlpesdaRankDescending, con xlpesdaNone como valor neutro. Un data field usa un grupo o el otro, nunca ambos. Si usted deja ShowDataAs en un valor distinto de normal y además fija un modo extendido, MaterializeNativePivotTable devuelve -1 y deja la salida existente exactamente como estaba, celdas y límites del pivot incluidos
BaseField y BaseItem cargan el objetivo de comparación. BaseField es un índice de base cero dentro de la lista de campos de la tabla dinámica, no dentro de los nombres de encabezado de las columnas fuente, y maneja comparaciones, running totals, porcentaje de un padre seleccionado y ranks. BaseItem es una posición de ítem de base cero dentro de ese campo, o una de las constantes xlPivotBaseItemPrevious y xlPivotBaseItemNext para "comparado con el vecino". Los porcentajes de fila padre y columna padre ignoran BaseField por completo; ellos eligen el campo más interno del eje correspondiente
Construir una tabla dinámica con rank en Delphi
El camino más corto que funciona es construir el pivot, fijar el modo de despliegue en el data field y materializarlo antes de guardar. El ejemplo de abajo rankea montos regionales por categoría en orden descendente, así que el monto más grande de cada fila es rank 1
uses
SysUtils, lxHandleX, lxPivot;
var
Book: TXLSXWorkbook;
Data, Report: TXLSXWorksheet;
Pivot: TXLSPivotTable;
Measure: TXLSPivotDataField;
begin
Book := TXLSXWorkbook.Create;
try
Data := Book.Sheets.Add('Data');
Data.Cells[1, 1].Value := 'Category';
Data.Cells[1, 2].Value := 'Region';
Data.Cells[1, 3].Value := 'Amount';
// ... llene las filas 2..7 ...
Report := Book.Sheets.Add('Report');
Pivot := Report.AddPivotTable('Data!A1:C7', 4, 3, 'RegionRank');
Pivot.AddRowField('Category'); // campo pivot 0
Pivot.AddColumnField('Region'); // campo pivot 1
Measure := Pivot.AddDataFieldByName('Amount', xlpaSum);
Measure.DisplayName := 'Rank by Region';
Measure.ShowDataAs := xlpsdaNormal; // el modo básico queda apagado
Measure.ExtendedShowDataAs := xlpesdaRankDescending;
Measure.BaseField := 1; // rank a través de Region
if Book.MaterializeNativePivotTable(Pivot) <> 1 then
raise Exception.Create('Pivot display mode was rejected');
Book.SaveAs('region-rank.xlsx');
finally
Book.Free;
end;
end;Dos hábitos rinden aquí. Primero, fije ShowDataAs := xlpsdaNormal explícitamente cuando elija un modo extendido, incluso sobre un campo fresco, porque el mismo código suele terminar reusándose contra un pivot importado cuyo data field ya carga un modo básico. Segundo, revise el valor de retorno. Una materialización rechazada es atómica, lo cual es bueno, pero también significa que un chequeo omitido embarca un archivo con la cuadrícula anterior dentro
BaseField sobrevive al guardado aunque usted nunca llame MaterializeNativePivotTable. HotXLS escribe el atributo baseField para percent of parent, running total percent y ambos modos de rank tan pronto como el modo extendido se fija, así que un workbook guardado justo después de la configuración todavía le dice a Excel sobre qué campo rankear cuando refresque
¿Qué falla con el URI de extensión?
El bloque de extensión que carga pivotShowAs se identifica por el URI {E15A36E0-9728-4e99-A89B-3F7291B0FE68}, y Excel compara esa cadena de forma sensible a mayúsculas durante el refresh. Note la e minúscula en 4e99. Los GUID son normalmente insensibles a mayúsculas y muchos escritores XML los ponen en mayúsculas, que es exactamente el modo de falla: un archivo con 4E99 abre en Excel con cada celda cacheada correcta, porque Excel solo pinta los valores almacenados. Después de RefreshTable, sin embargo, Excel ya no reconoce la extensión, tira el modo extendido y recalcula la columna como una suma ordinaria. Sin aviso de reparación, sin error, solo una columna de rank que silenciosamente se volvió totales
HotXLS escribe el URI literal tal como Excel lo espera, así que esto solo lo muerde si algo aguas abajo reescribe el paquete, por ejemplo un paso de post-procesamiento que normaliza la capitalización de GUIDs o reconstruye elementos extLst. La prueba que lo atrapa es barata: abra el archivo generado en Excel, refresque el pivot y confirme que la columna extendida sigue mostrando ranks o porcentajes en lugar de agregados crudos. Una prueba que solo lee las celdas cacheadas pasará sobre un archivo roto todas las veces
¿Cómo rankea Excel los empates y ordena running totals?
El rank de pivot de Excel es un dense rank, y HotXLS lo reproduce. Con montos descendentes de 30, 30 y 20, el resultado es 1, 1 y 2, no el 1, 1 y 3 estilo competencia que usted obtiene de RANK.EQ en una hoja. Un empate cuenta como un único valor distintivo precedente. Si un reporte necesita ranking de competencia, calcule en los datos fuente o en una fórmula de hoja junto al pivot, porque ningún modo de despliegue de pivot lo produce
Los running totals, los porcentajes de running total y las comparaciones previous o next siguen el orden en que los miembros realmente aparecen, no el orden de la lista de ítems del campo. Esa distinción importa en cuanto un caller cambia el orden después de que el cache fue construido
// Filas ordenadas descendente: el running total acumula en orden de despliegue
Pivot.Fields[0].SortType := xlpfstDescending;
Measure.ExtendedShowDataAs := xlpesdaNone;
Measure.ShowDataAs := xlpsdaPercentDiff; // % de diferencia desde...
Measure.BaseField := 0; // ...el campo Category
Measure.BaseItem := xlPivotBaseItemPrevious; // ...el miembro visible anterior
if Book.MaterializeNativePivotTable(Pivot) <> 1 then
raise Exception.Create('Pivot display mode was rejected');Después de que SortType cambia a xlpfstDescending, "previous" significa el miembro desplegado arriba, y el running total arranca desde la punta de la lista ordenada. Una implementación que recorre la lista de ítems almacenada en su lugar produce números que se ven plausibles y discrepan de Excel en el momento en que alguien refresca. Los miembros del borde también requieren cuidado: en el primer miembro no hay vecino anterior, y solo el modo percent-of compara al miembro del borde consigo mismo; los modos de diferencia dejan esa celda al comportamiento de borde del propio Excel en lugar de inventar un cero
¿Por qué un porcentaje acumulado puede exceder 100 por ciento?
Un porcentaje de running total sobre promedios puede legítimamente pasar de 100 por ciento, porque Excel acumula los agregados de los miembros desplegados y divide por un agregado que no es su suma. Para sumas las dos cosas son lo mismo, así que el último miembro aterriza en exactamente 100 por ciento. Para promedios, sumar tres promedios de categoría y dividir por el promedio global no es fracción de nada, y la fila final puede aterrizar muy arriba de 100 por ciento. HotXLS coincide con Excel aquí a propósito en lugar de recortar el valor, porque un número recortado discreparía del archivo refrescado
El divisor también depende de dónde se sienta el campo base. Cuando el campo base es el campo más interno de su eje, el divisor es el agregado original para esa posición del eje. Cuando el campo base es un campo externo en un layout anidado, el divisor es la suma a través de los miembros correspondientes del eje restante. Ambas posiciones se verificaron independientemente contra salida de Excel, porque una regla que funciona para un nivel de anidamiento y falla silenciosamente en el otro es el bug clásico de pivots
Los porcentajes simples se comportan distinto al acumulado. Los porcentajes de fila, columna y gran total dividen por el agregado original en el alcance relevante, incluso para agregaciones no aditivas como los promedios, así que un percent of total sobre promedios es el promedio de la celda dividido por el promedio global, no una parte de un pool sumado
Blancos, #NULL! y #DIV/0! en los modos de comparación
Las comparaciones percent-of-base distinguen tres situaciones vacías, y cada una produce una celda distinta. Cuando la intersección del numerador no tiene filas fuente, la celda muestra #NULL!. Cuando el ítem base no tiene datos en esa intersección, la celda queda en blanco. Cuando el ítem base existe y su agregado es cero, la celda muestra #DIV/0!. El código que lee la cuadrícula materializada debería probar con VarIsEmpty y VarIsError en lugar de asumir que cada celda de valor carga un número
Los porcentajes simples son más indulgentes: allí un numerador ausente cuenta como cero. Los porcentajes de fila padre y columna padre agregan un caso más, ya que un pivot sin campo en el eje correspondiente no tiene padre entre quien dividir, así que esos resultados quedan en blanco. Los gran totals siguen también la semántica de cada modo, lo que incluye totales en blanco a lo largo del eje que una comparación o running total recorre; una fila de total llena de blancos bajo un running total es correcta, no una falla de materialización
Cambiar modos en una tabla dinámica importada
Cambiar el modo de despliegue de un pivot que vino de un workbook existente funciona igual, con una bandera extra. Pase True como AAdoptExisting para que MaterializeNativePivotTable pueda tomar las celdas que Excel ya escribió para ese pivot
Pivot := Book.Sheets.SheetByName('Sales').PivotTables[0];
with Pivot.DataFields[0] do
begin
ShowDataAs := xlpsdaNormal; // limpie el modo básico
ExtendedShowDataAs := xlpesdaPercentOfParentRow; // no se necesita BaseField
end;
if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
raise Exception.Create('Could not re-materialize the pivot');Cuando el modo cambia, HotXLS elimina los atributos obsoletos showDataAs, pivotShowAs y baseItem de la definición en staging, así que el archivo guardado nunca carga un modo básico viejo junto a uno extendido nuevo. El mismo paso de staging mantiene los flags compact y outline en data fields e inactive fields al paso con el layout del reporte, lo que importa porque Excel deriva los data captions refrescados de esos flags y no solo de los campos de fila y columna. Los valores de enum inválidos se rechazan también: hacer cast de un byte de 255 hacia cualquiera de los enums puede hacer sign-extend a -1 en Delphi, así que HotXLS revisa tanto el límite inferior como el superior y devuelve -1 sin tocar la cuadrícula
Si además agrega calculated fields o calculated items al mismo reporte, lea construir y refrescar tablas dinámicas con calculated fields en HotXLS primero, y para las reglas XML a nivel de campo que evitan que Excel repare el archivo, vea validez de esquema de campos pivot XLSX en Delphi
Preguntas frecuentes
¿Un data field de HotXLS puede usar ShowDataAs y ExtendedShowDataAs juntos?
No. TXLSPivotDataField.ShowDataAs y ExtendedShowDataAs son mutuamente excluyentes; con ambos fijados, MaterializeNativePivotTable devuelve -1 y deja la salida anterior sin cambios
¿Por qué mi rank de pivot se volvió una suma tras refrescar en Excel?
El URI de extensión probablemente fue reescrito con otra capitalización. Excel hace match de {E15A36E0-9728-4e99-A89B-3F7291B0FE68} de forma sensible a mayúsculas y cae de vuelta a la agregación simple después de RefreshTable cuando no hay match
¿El rank de pivot de HotXLS salta números tras un empate?
No. HotXLS sigue el dense rank de pivot de Excel, así que 30, 30 y 20 se rankan como 1, 1 y 2
El soporte de Show Values As, la materialización nativa de pivots y el resto del conjunto de funciones XLSX y XLS vienen en el componente de hojas de cálculo HotXLS para Delphi para Delphi y C++Builder