Criar e atualizar tabelas dinâmicas do Excel no Delphi com o HotXLS

O HotXLS cria e atualiza tabelas dinâmicas XLSX nativas do Delphi e C++Builder sem a necessidade de instalação do Excel e sem automação COM na máquina. Você chama o AddPivotTable com um intervalo de origem, posiciona campos nos eixos de linha, coluna, página e dados, adiciona itens calculados ou uma exibição de porcentagem do total, e o componente grava as partes pivotCacheDefinition e pivotTableDefinition que o Excel abre como uma tabela dinâmica ativa e atualizável

O cenário que faz isso valer a pena é um servidor de relatórios. Você gera centenas de pastas de trabalho por noite, cada uma contendo uma tabela dinâmica que resume uma conta e, no mês seguinte, os números mudam e cada arquivo precisa refletir as novas linhas de origem. Controlar o Excel a partir de um serviço do Windows é frágil e não licenciado para uso em servidor, e gravar manualmente o XML da tabela dinâmica é um projeto de arqueologia de especificações que nunca termina. O HotXLS se posiciona entre esses dois caminhos sem saída: um modelo de objeto tipado sobre as partes da tabela dinâmica OOXML, de modo que o mesmo Pascal que preenche as células também declara a tabela dinâmica e reconstrói seu cache no mesmo processo

Como criar uma tabela dinâmica no Delphi sem o Excel?

Você cria uma com uma única chamada e depois posiciona os campos nos eixos. O AddPivotTable recebe o intervalo de origem na notação A1, a célula de destino onde a tabela se ancora e um nome; ele analisa o intervalo, varre cada coluna para inferir seu tipo de dados, constrói um cache de tabela dinâmica (ou reutiliza um já vinculado ao mesmo intervalo) e retorna um TXLSPivotTable cujos campos começam fora do eixo. A partir daí, os métodos de conveniência AddRowField, AddColumnField, AddPageField e AddDataFieldByName configuram o layout, e cada campo de dados assume uma das onze agregações do enum 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];                 // planilha do relatório (baseada em 1 no motor XLSX)

    // Origem A1:E500 na planilha 'Data'; ancora a tabela dinâmica na linha 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 que o cache e a tabela são duas partes separadas

Uma tabela dinâmica é, na verdade, dois artefatos que se referenciam mutuamente, e entender essa divisão é o que mantém o resto claro. O pivotCacheDefinition é o instantâneo dos dados: um worksheetSource que aponta para o intervalo de origem e um cacheField por coluna contendo os valores distintos daquela coluna (seus sharedItems) mais os limites derivados. O pivotTableDefinition é a visualização: qual campo de cache fica em qual eixo, os campos de dados e suas agregações, e as opções de layout. A visualização se vincula ao cache por meio do cacheId através do relacionamento pivotCaches da pasta de trabalho, exatamente como a ECMA-376 Parte 1 §18.10 e o [MS-XLSX] definem

Essa indireção não é burocracia, ela garante duas coisas. Um cache pode dar suporte a várias tabelas, portanto, atualizar o cache uma vez atualiza cada visualização extraída dele. E o cache armazena os valores de cada coluna desduplicados com uma tabela de índice por registro em vez da grade bruta, razão pela qual criar uma tabela dinâmica significa varrer a origem, não copiar células. O HotXLS modela isso da mesma forma para ambos os motores, de modo que o código acima é quase idêntico ao caminho clássico documentado ao lado do layout de registros SX binários por trás das tabelas dinâmicas .xls clássicas. Si a sua origem residir em outra planilha ou se você a referenciar por meio de um nome, o prefixo de intervalo e as fórmulas com nomes definidos e referências entre planilhas seguem as regras usuais de A1, com nomes de planilha entre aspas permitidos para nomes que contêm espaços

Itens calculados não são campos calculados

Estes três termos nomeiam três coisas diferentes, e confundi-los é o erro clássico em tabelas dinâmicas. Um item calculado reside dentro de um único campo e combina os itens desse campo por nome; assim, em um campo Região, você pode definir uma linha sintética CoreMarkets igual a Norte mais Sul. O HotXLS expõe isso como TXLSPivotField.AddCalculatedItem e grava um <calculatedItem> sob o <calculatedItems> desse campo. Um campo calculado é diferente: é um novo valor derivado de outras colunas, como Margem a partir de Receita e Custo, e é carregado como uma fórmula em um campo de cache por meio de TXLSPivotCacheField.Formula. Um membro calculado, adicionado com TXLSPivotTable.AddCalculatedMember, é um membro personalizado em nível de tabela que pode atuar como uma medida (tipo de membro data) ou um membro de dimensão, de interesse principalmente para tabelas dinâmicas no formato OLAP

var
  Region: TXLSPivotField;
  Member: TXLSPivotCalculatedMember;
begin
  // Um ITEM calculado combina itens de um único campo por nome.
  Region := Pivot.AddRowField('Region');
  Region.AddCalculatedItem('CoreMarkets', '=North+South');

  // Um MEMBRO calculado é declarado ao nível da tabela. O tipo de membro
  // 'data' marca-o como uma medida; um tipo vazio é um membro de dimensão.
  Member := Pivot.AddCalculatedMember('AvgTicket', '=Revenue/Units');
  Member.MemberType := 'data';
end;

Um limite honesto se aplica a todos os três. O HotXLS emite o texto da fórmula no XML de definição; ele não o avalia. O Excel calcula o item, campo ou membro calculado quando abre o arquivo, da mesma forma que calcula cada agregação na grade. O HotXLS está gravando as instruções, não os resultados, portanto, as fórmulas fornecidas devem ser fórmulas de tabela dinâmica válidas no dialeto do próprio Excel, referenciando nomes de campos e itens como o Excel faria

Como exibir valores como uma porcentagem do total?

Você define o modo de exibição de valor no campo de dados em vez de transformar os números você mesmo. O TXLSPivotDataField.ShowDataAs recebe o enum TXLSPivotShowDataAs, que espelha os valores de ST_ShowDataAs do OOXML: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal e xlpsdaIndex. Um truque comum é colocar a mesma coluna de origem no eixo de dados duas vezes, uma como uma soma bruta e outra como uma parcela do total geral, para que o relatório mostre o número e o seu peso

var
  Rev, Share: TXLSPivotDataField;
begin
  Rev := Pivot.AddDataFieldByName('Revenue', xlpaSum);

  Share := Pivot.AddDataFieldByName('Revenue', xlpaSum);
  Share.DisplayName := 'Share of total';
  Share.ShowDataAs  := xlpsdaPercentOfTotal;

  // Modos relativos ao item precisam de uma base para comparação. Um total acumulado
  // sob 'Quarter' (índice 3 do campo de cache) seria escrito:
  //   Share.ShowDataAs := xlpsdaRunTotal;
  //   Share.BaseField  := 3;         // índice do campo de cache sobre o qual a transformação é executada
  //   Share.BaseItem   := $7FFD;     // $7FFD = "(All)"
end;

O xlpsdaPercentOfTotal não precisa de base porque é relativo ao total geral, mas os modos relativos ao item precisam. O xlpsdaDifference, o xlpsdaPercentDiff e o xlpsdaRunTotal exigem o BaseField (o índice do campo de cache sobre o qual a comparação é executada) e, para as formas ancoradas ao item, um índice BaseItem, onde $7FFD representa o sentinel (All). O HotXLS grava esses valores como os atributos <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> e, assim como ocorre com as fórmulas calculadas, deixa a aritmética para o Excel

Agrupamento de datas e números, e filtros de campos de página

O agrupamento é configurado no campo de cache, não no campo da tabela dinâmica, porque altera a forma como o domínio de origem é segmentado. Defina HasGroup := True no campo de cache retornado pelo Cache.FindFieldByName, e depois escolha os controles de intervalo numérico (GroupStartNum, GroupEndNum, GroupInterval) ou as flags de hierarquia de data (GroupByDate com GroupMonths, GroupQuarters, GroupYears e um intervalo GroupStartDate / GroupEndDate). O HotXLS emite o elemento <fieldGroup><rangePr groupBy="months"/> correspondente para que um campo agrupado por mês ou por uma faixa numérica de 1000 se abra da forma como o Excel o teria agrupado

Campos de página são as listas suspensas de filtro acima da tabela. O AddPageField posiciona um campo no eixo de página, e o PageItemIndex pré-seleciona um único item de cache, assumindo como padrão o xlPageItemAll ($7FFD, significando (All)). Para permitir que um leitor selecione vários itens ao mesmo tempo, defina MultipleItemSelectionAllowed := True, o que o HotXLS grava como <pivotField multipleItemSelectionAllowed="1"/>. Para critérios além de uma seleção manual, cada campo carrega uma coleção Filters tipada que abrange as famílias ST_FilterType do OOXML — filtros de contagem, porcentagem, soma, legenda, valor e data —, cada entrada associando um tipo de filtro aos seus valores de comparação

Como atualizar o cache de uma tabela dinâmica quando a origem muda?

O RefreshPivotCache no TXLSXWorkbook varre novamente o intervalo de origem e reconstrói o cache no local, que é o que um pipeline em lote precisa após editar as linhas subjacentes. Passe o ID do cache, e o método lerá novamente cada célula no intervalo de origem (cabeçalho na primeira linha, dados a partir da segunda), reconstruirá o domínio de itens compartilhados de cada campo desduplicando os valores e derivando novamente os limites por tipo, e regravará os índices de itens por registro. Ele retorna 1 em caso de sucesso e -1 quando o ID do cache é desconhecido ou a planilha de origem está ausente

var
  Rc: Integer;
begin
  // ...os dados de origem foram alterados desde a criação da tabela dinâmica...
  Sheet := Book.Sheets[1];
  Sheet.Cells[2, 5].Value := 128000;             // um valor de Receita corrigido

  // Reconstrói os itens compartilhados do cache e os índices de registros a partir da origem.
  Rc := Book.RefreshPivotCache(Pivot.CacheId);   // 1 = atualizado, -1 = cache/origem ausente
  if Rc = 1 then
    Book.SaveAs('sales-pivot-refreshed.xlsx');
end;

O limite semântico aqui merece ser declarado claramente. Antes deste método existir, o HotXLS dependia do sinalizador refreshOnLoad="1" e deixava todo o trabalho de atualização para o Excel na próxima inicialização, o que é ótimo quando um ser humano abrirá o arquivo, mas inútil em um pipeline em modo headless que precisa entregar dados corretos. O RefreshPivotCache torna o cache salvo atualizado no processo e, como as tabelas se vinculam a um cache por ID, cada tabela dinâmica extraída desse cache vê a atualização. O que ele não faz é dispor ou agregar a grade visível — o Excel ainda recomputa a tabela renderizada a partir do cache atualizado quando o arquivo é aberto

O que o HotXLS grava e o que o Excel calcula

Mantenha a divisão de trabalho em mente e nada o surpreenderá. O HotXLS escreve as definições: ele produz o pivotCacheDefinition, os registros de cache e o pivotTableDefinition, completos com eixos, agregações, itens e membros calculados, exibições de valor, agrupamento e filtros. O Excel é o calculador: ao abrir, ele materializa os grupos, avalia as fórmulas calculadas, aplica as transformações showDataAs e agrega o corpo. Os valores que o usuário vê são do Excel, produzidos a partir das instruções que o HotXLS gravou, razão pela qual cada fórmula e índice base deve estar correto no momento da gravação, em vez de ser verificado no momento da renderização

Dois limites valem a pena conhecer antes de projetar em torno desse recurso. O AddPivotTable analisa um intervalo A1 retangular com um prefixo de planilha opcional; fontes de intervalo nomeado e de pasta de trabalho externa estão fora do que o construtor resolve, embora caches lidos de um arquivo criado pelo Excel preservem sua origem de intervalo nomeado no processo de ida e volta. E tabelas dinâmicas criadas pelo Excel que o HotXLS lê do disco são reproduzidas byte a byte ao salvar, de modo que as edições tipadas descritas aqui se aplicam de forma limpa a tabelas dinâmicas que você cria no código, enquanto tabelas dinâmicas existentes permanecem sem perdas. Para o lado de entrada de um relatório — as células validadas e tabelas filtradas que a tabela dinâmica resume —, consulte validação de dados, AutoFiltro e tabelas estruturadas

O modelo de tabela dinâmica mostrado aqui faz parte do Componente HotXLS para Delphi padrão para Delphi e C++Builder, que lê e grava tanto as partes da tabela dinâmica tipada do XLSX quanto os registros BIFF8 clássicos a partir do mesmo modelo de objeto