HotXLS fija los modos "Show Values As" de las tablas dinámicas de Excel desde Delphi mediante 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 porcentual. TXLSXWorkbook.MaterializeNativePivotTable escribe luego la rejilla calculada tal como lo haría Excel. Las dos propiedades son mutuamente excluyentes, y las extendidas dependen de un URI de extensión que Excel casa distinguiendo mayúsculas
Ese último detalle es la razón de que exista este post. Una pivot table con columna de rank parece una cosita que generar, y para la primera apertura suele serlo. El problema empieza cuando un usuario pulsa Actualizar en Excel y la columna de rank vuelve a ser una suma simple sin ningún aviso. Clavar Show Values As significa clavar dos vocabularios XML, más un puñado de reglas numéricas que Excel nunca documenta en la UI: cómo se rankea un empate, qué significa un porcentaje acumulado de un promedio y qué celda vacía se vuelve blank y cuál 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 sitios 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 añadió Excel 2010, rank y los porcentajes de padre y acumulados, no cabían en esa enumeración, así que fueron a un elemento x14:dataField con un atributo pivotShowAs, envuelto en un bloque de extensión extLst del 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 u otro, nunca ambos. Si dejas ShowDataAs en un valor distinto de normal y además fijas un modo extendido, MaterializeNativePivotTable devuelve -1 y deja la salida existente exactamente como estaba, celdas y límites de la pivot incluidos
BaseField y BaseItem llevan el objetivo de comparación. BaseField es un índice de base cero en la lista de campos de la pivot table, no en los nombres de cabecera de las columnas fuente, y gobierna comparaciones, running totals, porcentaje de un padre seleccionado y ranks. BaseItem es una posición de elemento 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; eligen el campo más interno del eje que corresponda
Construir una pivot con rank en Delphi
El camino más corto que funciona es construir la pivot, fijar el modo de visualización en el data field y materializarla antes de guardar. El ejemplo de abajo rankea importes regionales por categoría en orden descendente, así que el mayor importe 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';
// ... rellena las filas 2..7 ...
Report := Book.Sheets.Add('Report');
Pivot := Report.AddPivotTable('Data!A1:C7', 4, 3, 'RegionRank');
Pivot.AddRowField('Category'); // pivot field 0
Pivot.AddColumnField('Region'); // pivot field 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 que pagan aquí. Primero, fija ShowDataAs := xlpsdaNormal explícitamente cuando elijas un modo extendido, incluso en un campo recién creado, porque el mismo código acaba muchas veces reutilizado contra una pivot importada cuyo data field ya lleva un modo básico. Segundo, comprueba el valor de retorno. Una materialización rechazada es atómica, lo cual es bueno, pero también significa que una comprobación olvidada envía un archivo con la rejilla anterior dentro
BaseField sobrevive al guardado aunque nunca llames a MaterializeNativePivotTable. HotXLS escribe el atributo baseField para percent of parent, running total porcentual y ambos modos de rank en cuanto se fija el modo extendido, así que un workbook guardado justo tras la configuración todavía le dice a Excel qué campo rankear cuando refresque
¿Qué falla con el URI de extensión?
El bloque de extensión que lleva pivotShowAs se identifica por el URI {E15A36E0-9728-4e99-A89B-3F7291B0FE68}, y Excel compara esa cadena distinguiendo mayúsculas durante el refresh. Fíjate en la e minúscula de 4e99. Los GUID son normalmente insensibles a mayúsculas y muchos escritores XML los ponen en mayúsculas, que es exactamente el modo de fallo: un archivo con 4E99 abre en Excel con cada celda cacheada correcta, porque Excel solo pinta los valores almacenados. Tras RefreshTable, en cambio, Excel ya no reconoce la extensión, suelta el modo extendido y recalcula la columna como suma ordinaria. Ni aviso de reparación ni error, solo una columna de rank que se convirtió tranquilamente en totales
HotXLS escribe el URI literal tal como Excel lo espera, así que esto solo te muerde si algo posterior reescribe el paquete, por ejemplo un paso de post-proceso que normalice las mayúsculas de los GUID o reconstruya elementos extLst. El test que lo caza es barato: abre el archivo generado en Excel, refresca la pivot y confirma que la columna extendida sigue mostrando ranks o porcentajes en lugar de agregados en bruto. Un test que solo lee las celdas cacheadas pasará sobre un archivo roto siempre
¿Cómo rankea Excel los empates y ordena los running totals?
El rank de pivot de Excel es un dense rank, y HotXLS lo reproduce. Con importes descendentes de 30, 30 y 20, el resultado es 1, 1 y 2, no el estilo competición 1, 1 y 3 que obtienes con RANK.EQ en una hoja. Un empate cuenta como un solo valor previo distinto. Si un informe necesita ranking de competición, calcúlalo en los datos fuente o en una fórmula de hoja junto a la pivot, porque ningún modo de visualización de pivot lo produce
Los running totals, los porcentajes de running total y las comparaciones de anterior o siguiente siguen el orden en que los miembros aparecen realmente, no el orden de la lista de elementos del campo. Esa distinción importa en cuanto un caller cambia el orden después de que el cache se construyó
// Filas ordenadas descendente: el running total acumula en orden de visualización
Pivot.Fields[0].SortType := xlpfstDescending;
Measure.ExtendedShowDataAs := xlpesdaNone;
Measure.ShowDataAs := xlpsdaPercentDiff; // % de diferencia respecto a...
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');Tras cambiar SortType a xlpfstDescending, "anterior" significa el miembro mostrado arriba, y el running total arranca desde arriba de la lista ordenada. Una implementación que recorre la lista de elementos almacenada produce en cambio números que parecen plausibles y discrepan de Excel en cuanto alguien refresca. Los miembros del borde también necesitan 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 inventarse un cero
¿Por qué un porcentaje acumulado puede pasar del 100 por ciento?
Un porcentaje de running total sobre promedios puede legítimamente pasar del 100 por ciento, porque Excel acumula los agregados mostrados de los miembros y divide por un agregado que no es su suma. Para sumas ambas cosas son lo mismo, así que el último miembro aterriza exactamente en 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 quedar muy por encima del 100 por ciento. HotXLS iguala a 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 una maquetación anidada, el divisor es la suma a través de los miembros coincidentes del eje restante. Ambas posiciones se comprobaron por separado contra la salida de Excel, porque una regla que funciona en un nivel de anidamiento y falla en silencio en el otro es el bug clásico de pivots
Los porcentajes simples se comportan distinto del acumulado. El porcentaje de fila, columna y total general divide por el agregado original en el alcance relevante, incluso para agregaciones no aditivas como 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 fondo sumado
Blanks, #NULL! y #DIV/0! en los modos de comparación
Las comparaciones de porcentaje sobre 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 elemento base no tiene datos en esa intersección, la celda queda blank. Cuando el elemento base existe y su agregado es cero, la celda muestra #DIV/0!. El código que lee la rejilla materializada debería comprobar con VarIsEmpty y VarIsError en lugar de asumir que toda celda de valor guarda un número
Los porcentajes simples son más indulgentes: un numerador ausente cuenta como cero ahí. Los porcentajes de fila padre y columna padre añaden un caso más, ya que una pivot sin campo en el eje correspondiente no tiene padre por el que dividir, así que esos resultados quedan blank. Los totales generales siguen además la semántica de cada modo, lo que incluye totales blank a lo largo del eje que una comparación o running total recorre; una fila de total llena de blanks bajo un running total es correcta, no un fallo de materialización
Cambiar modos en una pivot importada
Cambiar el modo de visualización de una pivot que vino de un workbook existente funciona igual, con un flag extra. Pasa True como AAdoptExisting para que MaterializeNativePivotTable pueda tomar las celdas que Excel ya escribió para esa pivot
Pivot := Book.Sheets.SheetByName('Sales').PivotTables[0];
with Pivot.DataFields[0] do
begin
ShowDataAs := xlpsdaNormal; // limpia el modo básico
ExtendedShowDataAs := xlpesdaPercentOfParentRow; // no hace falta BaseField
end;
if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
raise Exception.Create('Could not re-materialize the pivot');Cuando cambia el modo, HotXLS quita los atributos obsoletos showDataAs, pivotShowAs y baseItem de la definición en staging, así que el archivo guardado nunca lleva un modo básico rancio junto a uno extendido nuevo. El mismo paso de staging mantiene los flags compact y outline en campos de datos e inactivos al paso con la maquetación del informe, lo que importa porque Excel deriva las etiquetas de datos refrescadas de esos flags y no solo de los campos de fila y columna. Los valores de enum inválidos se rechazan igualmente: hacer cast de un byte 255 a cualquiera de los enums puede sign-extend a -1 en Delphi, así que HotXLS comprueba el límite inferior y el superior y devuelve -1 sin tocar la rejilla
Si además añades campos calculados o elementos calculados al mismo informe, lee primero construir y refrescar pivot tables con campos calculados en HotXLS, y para las reglas XML a nivel de campo que evitan que Excel repare el archivo, véase validez de esquema de campos pivot XLSX en Delphi
FAQ
¿Puede un data field de HotXLS usar ShowDataAs y ExtendedShowDataAs a la vez?
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 convirtió en una suma tras el refresh en Excel?
Probablemente el URI de extensión fue reescrito con otras mayúsculas. Excel casa {E15A36E0-9728-4e99-A89B-3F7291B0FE68} distinguiendo mayúsculas y recurre a la agregación simple tras RefreshTable cuando no casa
¿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 rankean 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 HotXLS Delphi spreadsheet component para Delphi y C++Builder