O HotXLS define modos "Show Values As" de tabela dinâmica do Excel a partir do Delphi por duas propriedades no TXLSPivotDataField: ShowDataAs para os modos clássicos como running total, diferença e percentual da linha, e ExtendedShowDataAs para rank, percentual do parent e percentual de running total. O TXLSXWorkbook.MaterializeNativePivotTable então escreve a grade calculada do jeito que o Excel faria. As duas propriedades são mutuamente exclusivas, e as estendidas dependem de um URI de extensão que o Excel compara diferenciando maiúsculas de minúsculas
Esse último detalhe é o motivo de este post existir. Uma tabela dinâmica com uma coluna de rank parece uma coisinha pequena de gerar, e na primeira abertura geralmente é. O problema começa quando um usuário clica em Refresh no Excel e a coluna de rank volta a ser uma soma simples sem nenhum aviso. Acertar o Show Values As significa acertar dois vocabulários XML, mais um punhado de regras numéricas que o Excel nunca documenta na UI: como empates recebem rank, o que um percentual cumulativo de uma média significa, e qual célula vazia vira em branco versus um erro
Por que existem duas propriedades para um menu do Excel?
O Excel mostra um menu "Show Values As", mas o arquivo o guarda em dois lugares diferentes, então o HotXLS expõe duas propriedades. Os modos originais do Excel 2007 moram no atributo showDataAs do elemento dataField dentro de pivotTableDefinition, usando a enumeração ST_ShowDataAs do ECMA-376. Os modos que o Excel 2010 adicionou, rank e os percentuais de parent e cumulativos, não cabiam nessa enumeração, então foram para um elemento x14:dataField com um atributo pivotShowAs, embrulhados em um bloco de extensão extLst no mesmo data field
O TXLSPivotShowDataAs espelha o primeiro grupo: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal e xlpsdaIndex. O TXLSPivotExtendedShowDataAs cobre o segundo: xlpesdaPercentOfParent, xlpesdaPercentOfParentRow, xlpesdaPercentOfParentColumn, xlpesdaPercentOfRunningTotal, xlpesdaRankAscending e xlpesdaRankDescending, com xlpesdaNone como valor neutro. Um data field usa um grupo ou outro, nunca os dois. Se você deixar o ShowDataAs em um valor não normal e também definir um modo estendido, o MaterializeNativePivotTable retorna -1 e deixa a saída existente exatamente como estava, células e limites de pivot incluídos
BaseField e BaseItem carregam o alvo de comparação. O BaseField é um índice zero-based na lista de campos da tabela dinâmica, não nos nomes de cabeçalho das colunas de origem, e ele comanda comparações, running totals, percentual de um parent selecionado e ranks. O BaseItem é uma posição de item zero-based dentro desse campo, ou uma das constantes xlPivotBaseItemPrevious e xlPivotBaseItemNext para "comparado com o vizinho". Os percentuais de parent row e parent column ignoram o BaseField completamente; eles escolhem o campo mais interno no eixo correspondente
Construindo uma pivot com rank em Delphi
O caminho funcional mais curto é construir a pivot, definir o modo de exibição no data field e materializá-la antes de salvar. O exemplo abaixo classifica os valores regionais por categoria em ordem decrescente, então o maior valor de cada linha fica com 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';
// ... preencha as linhas 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; // modo básico fica desligado
Measure.ExtendedShowDataAs := xlpesdaRankDescending;
Measure.BaseField := 1; // rank ao longo 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;Dois hábitos compensam aqui. Primeiro, defina ShowDataAs := xlpsdaNormal explicitamente quando escolher um modo estendido, mesmo em um campo novo, porque o mesmo código acaba reutilizado contra uma pivot importada cujo data field já carrega um modo básico. Segundo, verifique o valor de retorno. Uma materialização rejeitada é atômica, o que é bom, mas também significa que uma verificação esquecida embarca um arquivo com a grade anterior dentro
O BaseField sobrevive ao salvamento mesmo se você nunca chamar MaterializeNativePivotTable. O HotXLS escreve o atributo baseField para percentual do parent, percentual de running total e os dois modos de rank assim que o modo estendido é definido, de modo que uma pasta de trabalho salva logo após a configuração ainda diz ao Excel qual campo usar de base no rank quando ele atualizar
O que dá errado com o URI de extensão?
O bloco de extensão que carrega o pivotShowAs é identificado pelo URI {E15A36E0-9728-4e99-A89B-3F7291B0FE68}, e o Excel compara essa string diferenciando maiúsculas de minúsculas durante o refresh. Note o e minúsculo em 4e99. GUIDs normalmente são case-insensitive e muitos escritores de XML os colocam em maiúsculas, que é exatamente o modo de falha: um arquivo com 4E99 abre no Excel com todas as células em cache corretas, porque o Excel só pinta os valores armazenados. Depois do RefreshTable, porém, o Excel deixa de reconhecer a extensão, descarta o modo estendido e recalcula a coluna como uma soma comum. Nenhum prompt de reparo, nenhum erro, só uma coluna de rank que virou totais em silêncio
O HotXLS escreve o URI literal como o Excel espera, então isso só morde você se algo a jusante reescrever o pacote, por exemplo um passo de pós-processamento que normaliza a caixa de GUIDs ou reconstrói elementos extLst. O teste que pega isso é barato: abra o arquivo gerado no Excel, atualize a pivot e confirme que a coluna estendida ainda mostra ranks ou percentuais em vez de agregados brutos. Um teste que só lê as células em cache passa em um arquivo quebrado toda vez
Como o Excel dá rank a empates e ordena running totals?
O rank de pivot do Excel é um dense rank, e o HotXLS o reproduz. Com valores decrescentes de 30, 30 e 20, o resultado é 1, 1 e 2, não o estilo competição 1, 1 e 3 que você obtém do RANK.EQ em uma planilha. Um empate conta como um distinto valor precedente. Se um relatório precisa de ranking estilo competição, calcule-o nos dados de origem ou em uma fórmula de planilha ao lado da pivot, porque nenhum modo de exibição de pivot o produz
Running totals, percentuais de running total e comparações anterior ou próximo seguem a ordem em que os membros realmente aparecem, não a ordem da lista de itens do campo. Essa distinção importa assim que um chamador muda a ordenação depois de o cache ter sido construído
// Linhas ordenadas decrescente: o running total acumula na ordem de exibição
Pivot.Fields[0].SortType := xlpfstDescending;
Measure.ExtendedShowDataAs := xlpesdaNone;
Measure.ShowDataAs := xlpsdaPercentDiff; // % de diferença de...
Measure.BaseField := 0; // ...o campo Category
Measure.BaseItem := xlPivotBaseItemPrevious; // ...membro visível anterior
if Book.MaterializeNativePivotTable(Pivot) <> 1 then
raise Exception.Create('Pivot display mode was rejected');Depois que o SortType muda para xlpfstDescending, "anterior" significa o membro exibido acima, e o running total começa do topo da lista ordenada. Uma implementação que percorre a lista de itens armazenada em vez disso produz números que parecem plausíveis e discordam do Excel no momento em que alguém atualiza. Membros de borda também pedem cuidado: no primeiro membro não há vizinho anterior, e só o modo percent-of compara o membro de borda com ele mesmo; os modos de diferença deixam essa célula para o comportamento de borda do próprio Excel em vez de inventar um zero
Por que um percentual cumulativo pode passar de 100 por cento?
Um percentual de running total sobre médias pode legitimamente passar de 100 por cento, porque o Excel acumula os agregados dos membros exibidos e divide por um agregado que não é a soma deles. Para somas os dois são a mesma coisa, então o último membro cai exatamente em 100 por cento. Para médias, somar três médias de categoria e dividir pela média geral não é fração de coisa nenhuma, e a linha final pode ficar bem acima de 100 por cento. O HotXLS casa com o Excel aqui de propósito em vez de travar o valor, porque um número travado discordaria do arquivo atualizado
O divisor também depende de onde o campo base senta. Quando o campo base é o campo mais interno no seu eixo, o divisor é o agregado original para aquela posição de eixo. Quando o campo base é um campo externo em um layout aninhado, o divisor é a soma pelos membros correspondentes do eixo restante. As duas posições foram verificadas independentemente contra a saída do Excel, porque uma regra que funciona para um nível de aninhamento e falha em silêncio no outro é o clássico bug de pivot
Percentuais simples se comportam de forma diferente do cumulativo. Percentual da linha, coluna e total geral dividem pelo agregado original no escopo relevante, inclusive para agregações não aditivas como médias, então um percentual do total sobre médias é a média da célula dividida pela média geral, e não uma fatia de um pool somado
Em branco, #NULL! e #DIV/0! nos modos de comparação
Comparações percent-of-base distinguem três situações vazias, e cada uma produz uma célula diferente. Quando a interseção do numerador não tem linhas de origem, a célula mostra #NULL!. Quando o item base não tem dados naquela interseção, a célula fica em branco. Quando o item base existe e o seu agregado é zero, a célula mostra #DIV/0!. Código que lê a grade materializada deve testar com VarIsEmpty e VarIsError em vez de assumir que toda célula de valor guarda um número
Os percentuais simples são mais complacentes: um numerador ausente conta como zero ali. Os percentuais de parent row e parent column adicionam mais um caso, já que uma pivot sem campo no eixo correspondente não tem parent pelo qual dividir, então esses resultados ficam em branco. Os totais gerais seguem a semântica de cada modo também, o que inclui totais em branco ao longo do eixo por onde uma comparação ou running total anda; uma linha de total cheia de em brancos sob um running total é correto, não uma falha de materialização
Trocando modos em uma pivot importada
Mudar o modo de exibição de uma pivot que veio de uma pasta de trabalho existente funciona do mesmo jeito, com uma flag extra. Passe True como AAdoptExisting para que o MaterializeNativePivotTable possa assumir as células que o Excel já escreveu para aquela pivot
Pivot := Book.Sheets.SheetByName('Sales').PivotTables[0];
with Pivot.DataFields[0] do
begin
ShowDataAs := xlpsdaNormal; // limpe o modo básico
ExtendedShowDataAs := xlpesdaPercentOfParentRow; // sem BaseField necessário
end;
if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
raise Exception.Create('Could not re-materialize the pivot');Quando o modo muda, o HotXLS remove atributos obsoletos showDataAs, pivotShowAs e baseItem da definição preparada, de modo que o arquivo salvo nunca carrega um modo básico velho ao lado de um estendido novo. O mesmo passo de staging mantém as flags compact e outline em campos de dados e inativos em sintonia com o layout do relatório, o que importa porque o Excel deriva as legendas de dados atualizadas dessas flags e não apenas dos campos de linha e coluna. Valores de enum inválidos também são rejeitados: fazer cast de um byte 255 em qualquer um dos enums pode fazer sign-extension para -1 em Delphi, então o HotXLS verifica tanto o limite inferior quanto o superior e retorna -1 sem tocar na grade
Se você também adiciona calculated fields ou calculated items ao mesmo relatório, leia antes construção e atualização de tabelas dinâmicas com calculated fields no HotXLS, e para as regras de XML em nível de campo que evitam que o Excel repare o arquivo, veja validade de schema de campos de pivot XLSX em Delphi
FAQ
Um data field do HotXLS pode usar ShowDataAs e ExtendedShowDataAs juntos?
Não. O TXLSPivotDataField.ShowDataAs e o ExtendedShowDataAs são mutuamente exclusivos; com os dois definidos, o MaterializeNativePivotTable retorna -1 e deixa a saída anterior inalterada
Por que o rank da minha pivot virou uma soma depois do refresh no Excel?
O URI de extensão provavelmente foi reescrito com caixa diferente. O Excel casa {E15A36E0-9728-4e99-A89B-3F7291B0FE68} diferenciando maiúsculas de minúsculas e volta à agregação simples depois do RefreshTable quando não casa
O rank de pivot do HotXLS pula números depois de um empate?
Não. O HotXLS segue o dense rank de pivot do Excel, então 30, 30 e 20 recebem rank 1, 1 e 2
Suporte a Show Values As, materialização nativa de pivots e o resto do conjunto de recursos XLSX e XLS vêm no componente de planilha HotXLS Delphi para Delphi e C++Builder