O HotXLS define os modos "Show Values As" de tabelas dinâmicas Excel a partir de Delphi através de duas propriedades em TXLSPivotDataField: ShowDataAs para os modos clássicos como running total, diferença e percentagem da linha, e ExtendedShowDataAs para rank, percentagem do pai e running total percent. O TXLSXWorkbook.MaterializeNativePivotTable depois escreve a grelha calculada da forma como o Excel a escreveria. As duas propriedades são mutuamente exclusivas, e as estendidas dependem de um URI de extensão que o Excel compara sensível a maiúsculas
Esse último detalhe é a razão pela qual este post existe. Uma pivot table com uma coluna de rank parece uma coisa pequena de gerar, e na primeira abertura normalmente é. O problema começa quando um utilizador clica em Refresh no Excel e a coluna de rank volta a uma soma simples sem aviso nenhum. Acertar no Show Values As significa acertar em dois vocabulários XML, mais um punhado de regras numéricas que o Excel nunca documenta na UI: como empates fazem rank, o que uma percentagem cumulativa de uma média significa, e que célula vazia se torna em branco versus um erro
Porque existem duas propriedades para um só menu do Excel?
O Excel mostra um único menu "Show Values As", mas o ficheiro guarda-o em dois sítios diferentes, por isso o HotXLS expõe duas propriedades. Os modos originais do Excel 2007 vivem no atributo showDataAs do elemento dataField dentro de pivotTableDefinition, usando a enumeração ST_ShowDataAs do ECMA-376. Os modos que o Excel 2010 acrescentou, rank e as percentagens de pai e cumulativas, não cabiam nessa enumeração, por isso foram para um elemento x14:dataField com um atributo pivotShowAs, embrulhado num 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 o outro, nunca ambos. Se deixar o ShowDataAs num valor não normal e também definir um modo estendido, o MaterializeNativePivotTable devolve -1 e deixa o output existente exatamente como estava, células e limites da pivot incluídos
O BaseField e o BaseItem transportam o alvo de comparação. O BaseField é um índice de base zero na lista de campos da pivot table, não nos nomes de cabeçalho das colunas de origem, e conduz comparações, running totals, percentagem de um pai selecionado e ranks. O BaseItem é uma posição de item de base zero dentro desse campo, ou uma das constantes xlPivotBaseItemPrevious e xlPivotBaseItemNext para "comparado com o vizinho". As percentagens de linha pai e de coluna pai ignoram o BaseField por completo; escolhem o campo mais interior no eixo correspondente
Construir uma pivot com rank em Delphi
O caminho funcional mais curto é construir a pivot, definir o modo de exibição no data field e materializar antes de gravar. O exemplo abaixo faz rank dos montantes regionais por categoria em ordem descendente, por isso o maior montante em cada linha é 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';
// ... preencher linhas 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; // 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 num campo novo, porque o mesmo código acaba muitas vezes reutilizado contra uma pivot importada cujo data field já traz um modo básico. Segundo, verifique o valor de retorno. Uma materialização recusada é atómica, o que é bom, mas também significa que uma verificação esquecida envia um ficheiro com a grelha anterior lá dentro
O BaseField sobrevive à gravação mesmo que nunca chame o MaterializeNativePivotTable. O HotXLS escreve o atributo baseField para percentagem do pai, running total percent e ambos os modos de rank logo que o modo estendido é definido, por isso um workbook gravado logo após a configuração ainda diz ao Excel que campo usar para o rank quando fizer refresh
O que corre mal com o URI da extensão?
O bloco de extensão que transporta o pivotShowAs é identificado pelo URI {E15A36E0-9728-4e99-A89B-3F7291B0FE68}, e o Excel compara essa string sensível a maiúsculas durante o refresh. Note o e minúsculo em 4e99. GUIDs são normalmente insensíveis a maiúsculas e muitos escritores de XML põem-nas em maiúsculas, que é exatamente o modo de falha: um ficheiro com 4E99 abre no Excel com todas as células em cache corretas, porque o Excel apenas pinta os valores armazenados. Depois do RefreshTable, porém, o Excel já não reconhece a extensão, deita fora o modo estendido e recalcula a coluna como uma soma ordinária. Sem aviso de reparação, sem erro, apenas uma coluna de rank que se tornou totais em silêncio
O HotXLS escreve o URI literal como o Excel espera, por isso isto só o morde se algo a jusante reescrever o pacote, por exemplo um passo de pós-processamento que normalize maiúsculas de GUIDs ou reconstrua elementos extLst. O teste que o apanha é barato: abra o ficheiro gerado no Excel, faça refresh da pivot e confirme que a coluna estendida ainda mostra ranks ou percentagens em vez de agregados brutos. Um teste que só lê as células em cache passa num ficheiro partido todas as vezes
Como é que o Excel faz rank de empates e ordena running totals?
O rank de pivot do Excel é um dense rank, e o HotXLS reproduz-no. Com montantes descendentes de 30, 30 e 20, o resultado é 1, 1 e 2, e não o estilo competição 1, 1 e 3 que se obtém de RANK.EQ numa folha. Um empate conta como um valor distinto precedente. Se um relatório precisa de ranking à moda competição, calcule-o nos dados de origem ou numa fórmula de folha ao lado da pivot, porque nenhum modo de exibição de pivot o produz
Running totals, running total percentages e comparações previous ou next seguem a ordem por que os membros realmente aparecem, não a ordem da lista de itens do campo. Essa distinção importa assim que um chamador mude a ordenação depois de a cache ter sido construída
// Linhas ordenadas descendentes: o running total acumula pela 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 de o SortType mudar para xlpfstDescending, "previous" significa o membro exibido acima, e o running total começa no topo da lista ordenada. Uma implementação que percorra a lista de itens armazenada produz números que parecem plausíveis e discordam do Excel no momento em que alguém faz refresh. Membros de borda também precisam de cuidado: no primeiro membro não há vizinho anterior, e só o modo percent-of compara o membro de borda consigo próprio; os modos de diferença deixam essa célula ao comportamento de borda do próprio Excel em vez de inventar um zero
Porque é que uma percentagem cumulativa pode passar 100 por cento?
Uma running total percentage sobre médias pode legitimamente passar 100 por cento, porque o Excel acumula os agregados exibidos dos membros e divide por um agregado que não é a sua soma. Para somas as duas coisas são a mesma, por isso o último membro aterra exatamente em 100 por cento. Para médias, somar três médias de categoria e dividir pela média global não é fração de coisa nenhuma, e a última linha pode aterrar bem acima de 100 por cento. O HotXLS segue o Excel aqui de propósito em vez de limitar o valor, porque um número limitado discordaria do ficheiro após refresh
O divisor também depende de onde o campo base assenta. Quando o campo base é o campo mais interior no seu eixo, o divisor é o agregado original para essa posição do eixo. Quando o campo base é um campo exterior num layout aninhado, o divisor é a soma pelos membros correspondentes do eixo restante. Ambas as posições foram verificadas independentemente contra output 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
As percentagens simples comportam-se de forma diferente da cumulativa. Percentagem de linha, de coluna e de grand total dividem pelo agregado original no âmbito relevante, incluindo para agregações não aditivas como médias, por isso uma percentagem do total sobre médias é a média da célula dividida pela média global, e não uma quota-parte de um conjunto somado
Vazios, #NULL! e #DIV/0! nos modos de comparação
As 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 nessa 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 grelha materializada deve testar com VarIsEmpty e VarIsError em vez de assumir que toda a célula de valor guarda um número
As percentagens simples são mais indulgentes: um numerador ausente conta como zero ali. As percentagens de linha pai e de coluna pai acrescentam um caso, já que uma pivot sem campo no eixo correspondente não tem pai pelo qual dividir, por isso esses resultados ficam em branco. Os grand totals seguem também a semântica própria de cada modo, o que inclui totais em branco ao longo do eixo por onde uma comparação ou running total caminha; uma linha de total cheia de vazios sob um running total está correta, não é uma falha de materialização
Mudar de modo numa pivot importada
Mudar o modo de exibição de uma pivot que veio de um workbook existente funciona da mesma forma, com uma flag extra. Passe True como AAdoptExisting para que o MaterializeNativePivotTable possa tomar conta das células que o Excel já escreveu para essa pivot
Pivot := Book.Sheets.SheetByName('Sales').PivotTables[0];
with Pivot.DataFields[0] do
begin
ShowDataAs := xlpsdaNormal; // limpar o modo básico
ExtendedShowDataAs := xlpesdaPercentOfParentRow; // não é preciso BaseField
end;
if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
raise Exception.Create('Could not re-materialize the pivot');Quando o modo muda, o HotXLS remove os atributos obsoletos showDataAs, pivotShowAs e baseItem da definição em preparação, por isso o ficheiro gravado nunca traz um modo básico caducado ao lado de um estendido novo. O mesmo passo de preparação mantém as flags compact e outline em data fields e campos inativos a par com o layout do relatório, o que importa porque o Excel deriva as legendas de dados após refresh dessas flags e não só dos campos de linha e coluna. Valores de enumeração inválidos também são recusados: converter um byte de 255 para qualquer uma das enumerações pode fazer sign-extend para -1 em Delphi, por isso o HotXLS verifica tanto o limite inferior como o superior e devolve -1 sem tocar na grelha
Se também acrescentar calculated fields ou calculated items ao mesmo relatório, leia primeiro construir e atualizar pivot tables com calculated fields no HotXLS, e para as regras XML ao nível do campo que impedem o Excel de reparar o ficheiro, veja validade de schema de campos pivot XLSX em Delphi
FAQ
Um data field do HotXLS pode usar ShowDataAs e ExtendedShowDataAs juntos?
Não. TXLSPivotDataField.ShowDataAs e ExtendedShowDataAs são mutuamente exclusivas; com ambas definidas, o MaterializeNativePivotTable devolve -1 e deixa o output anterior inalterado
Porque é que o rank da minha pivot virou uma soma depois do refresh no Excel?
O URI da extensão provavelmente foi reescrito com maiúsculas diferentes. O Excel compara {E15A36E0-9728-4e99-A89B-3F7291B0FE68} sensível a maiúsculas e recua para a agregação simples depois do RefreshTable quando não coincide
O rank de pivot do HotXLS salta números depois de um empate?
Não. O HotXLS segue o dense rank de pivot do Excel, por isso 30, 30 e 20 fazem rank como 1, 1 e 2
Suporte a Show Values As, materialização nativa de pivots e o resto do conjunto de funcionalidades XLSX e XLS vêm no HotXLS Delphi spreadsheet component para Delphi e C++Builder