Criar e Atualizar Tabelas Dinâmicas do Excel no Delphi com o HotXLS

O HotXLS cria e atualiza tabelas dinâmicas XLSX nativas a partir do Delphi e C++Builder, sem necessidade de instalação do Excel e sem automação COM no computador. O utilizador chama o AddPivotTable com um intervalo de origem, coloca campos nos eixos de linha, coluna, página e dados, aplica itens calculados ou uma exibição de percentagem do total, e o componente escreve as partes pivotCacheDefinition e pivotTableDefinition que o Excel abre como uma tabela dinâmica dinâmica e atualizável

O cenário que faz com que isto valha a pena é um servidor de relatórios. Gera centenas de livros de trabalho (workbooks) por noite, cada um contendo uma tabela dinâmica que resume uma conta e, no mês seguinte, os números mudam e cada ficheiro tem de refletir as novas linhas de origem. Controlar o Excel a partir de um serviço Windows é frágil e não está licenciado para utilização em servidores, e escrever manualmente o XML da tabela dinâmica é um projeto de arqueologia de especificações sem fim. O HotXLS situa-se entre essas duas opções inviáveis: um modelo de objetos tipado sobre as partes de tabelas dinâmicas do OOXML, pelo que o mesmo código Pascal que preenche as células também declara a tabela dinâmica e reconstrói a sua cache no mesmo processo

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

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

    // Origem A1:E500 na folha 'Data'; fixa 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 a cache e a tabela são duas partes separadas

Uma tabela dinâmica é, na verdade, constituída por dois artefactos que se referenciam mutuamente, e compreender a divisão é o que mantém a clareza sobre o assunto. A pivotCacheDefinition é o instantâneo (snapshot) dos dados: uma worksheetSource que aponta para o intervalo de origem, e um cacheField por coluna contendo os valores distintos dessa coluna (os seus sharedItems) mais limites derivados. A pivotTableDefinition é a vista: qual o campo de cache que fica em cada eixo, os campos de dados e as suas agregações, e as opções de layout. A vista vincula-se à cache por cacheId através do relacionamento pivotCaches do livro de trabalho, exatamente como a especificação ECMA-376 Parte 1 §18.10 e o [MS-XLSX] definem

Esse redirecionamento não é burocracia; garante duas vantagens. Uma cache pode servir de base a várias tabelas, pelo que atualizar a cache uma vez atualiza todas as vistas criadas a partir dela. E a cache armazena os valores de cada coluna sem duplicados com uma tabela de índice por registo, em vez de uma grelha em bruto, razão pela qual criar uma tabela dinâmica significa analisar a origem e não copiar células. O HotXLS modela isto da mesma forma para ambos os motores, pelo que o código acima é quase idêntico ao caminho clássico documentado em conjunto com o layout de registos SX binários por trás de tabelas dinâmicas .xls clássicas. Se a sua origem estiver noutra folha ou se a referenciar através de um nome, o prefixo de intervalo e as referências cruzadas de folhas e nomes definidos seguem as regras habituais de A1, permitindo nomes de folhas entre aspas para nomes que contenham espaços

Itens calculados não são campos calculados

Estes três termos identificam três coisas diferentes, e confundi-los é o erro clássico das tabelas dinâmicas. Um item calculado reside num único campo e combina os próprios itens desse campo por nome, de modo que dentro de um campo de Região pode definir uma linha sintética CoreMarkets igual a Norte mais Sul; o HotXLS expõe esta operação como TXLSPivotField.AddCalculatedItem e escreve um <calculatedItem> sob as <calculatedItems> desse campo. Um campo calculado é diferente: é um novo valor derivado de outras colunas, como Margem a partir de Receita e Custo, e é transportado como uma fórmula num campo de cache através de TXLSPivotCacheField.Formula. Um membro calculado, adicionado com o TXLSPivotTable.AddCalculatedMember, é um membro personalizado ao nível da tabela que pode funcionar como uma medida (tipo de membro data) ou um membro de dimensão, sendo de especial interesse para tabelas dinâmicas com estrutura 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 aplica-se aos três. O HotXLS emite o texto da fórmula no XML de definição; não o avalia. O Excel calcula o item, campo ou membro calculado quando abre o ficheiro, da mesma forma que calcula qualquer agregação na grelha. O HotXLS está a escrever as instruções, não os resultados, pelo que as fórmulas que fornecer têm de ser fórmulas de tabela dinâmica válidas no próprio dialeto do Excel, referenciando nomes de campos e itens como o Excel faria

Como mostrar valores como uma percentagem do total?

O utilizador define o modo de exibição de valores no campo de dados, em vez de transformar os números por si mesmo. O TXLSPivotDataField.ShowDataAs recebe a enumeração TXLSPivotShowDataAs, que reflete os valores OOXML de ST_ShowDataAs: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal e xlpsdaIndex. Um truque comum é colocar a mesma coluna de origem duas vezes no eixo de dados, uma como soma em bruto e outra como parcela do total geral, de modo que o relatório mostre tanto o número como 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;

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

O xlpsdaPercentOfTotal não precisa de base por ser relativo ao total geral, ao contrário dos modos relativos ao item. O xlpsdaDifference, o xlpsdaPercentDiff e o xlpsdaRunTotal necessitam do BaseField (o índice do campo de cache sobre o qual a comparação corre) e, para as formas ancoradas ao item, um índice BaseItem, onde $7FFD representa a sentinela (Tudo). O HotXLS escreve estes como os atributos <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> e, tal como nas fórmulas calculadas, deixa a aritmética ao cuidado do Excel

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

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

Os campos de página são as listas suspensas (dropdowns) de filtros por cima da tabela. O AddPageField coloca um campo no eixo de página, e o PageItemIndex pré-seleciona um único item de cache, com o padrão xlPageItemAll ($7FFD, que significa (Tudo)). Para permitir que um leitor marque vários itens de uma só vez, configure MultipleItemSelectionAllowed := True, que o HotXLS escreve como <pivotField multipleItemSelectionAllowed="1"/>. Para critérios além de uma escolha manual, cada campo possui uma coleção Filters tipada que abrange as famílias OOXML de ST_FilterType — filtros de contagem, percentagem, soma, legenda, valor e data —, com cada entrada a associar um tipo de filtro aos seus valores de comparação

Como atualizar a cache da tabela dinâmica quando a origem muda?

O RefreshPivotCache em TXLSXWorkbook analisa novamente o intervalo de origem e reconstrói a cache no local, que é o que um pipeline em lote necessita depois de editar as linhas subjacentes. Passe o ID da cache, e o método lerá novamente cada célula no intervalo de origem (cabeçalho na primeira linha, dados a partir da segunda), reconstrói o domínio de itens partilhados de cada campo através da nova eliminação de duplicados de valores e nova derivação de limites por tipo, e reescreve os índices de itens por registo. Devolve 1 em caso de sucesso e -1 quando o ID da cache é desconhecido ou a folha (sheet) de origem está em falta

var
  Rc: Integer;
begin
  // ...os dados de origem mudaram desde que a tabela dinâmica foi criada...
  Sheet := Book.Sheets[1];
  Sheet.Cells[2, 5].Value := 128000;             // um valor de Receita corrigido

  // Reconstrói os itens partilhados e os índices de registo da cache a partir da origem.
  Rc := Book.RefreshPivotCache(Pivot.CacheId);   // 1 = atualizada, -1 = cache/origem em falta
  if Rc = 1 then
    Book.SaveAs('sales-pivot-refreshed.xlsx');
end;

O que o HotXLS escreve e o que o Excel calcula

Mantenha a divisão de trabalho em mente e nada o surpreenderá. O HotXLS escreve a definição: produz o pivotCacheDefinition, os registos da cache e a pivotTableDefinition, completos com eixos, agregações, itens e membros calculados, exibições de valores, agrupamentos e filtros. O Excel é a calculadora: ao abrir, materializa os segmentos de grupo, avalia as fórmulas calculadas, aplica as transformações showDataAs e agrega o corpo. Os valores que um utilizador visualiza pertencem ao Excel, gerados a partir das instruções que o HotXLS escreveu, razão pela qual cada fórmula e índice base devem estar corretos no momento da gravação, em vez de serem verificados no momento da renderização

Dois limites devem ser conhecidos antes de programar em torno desta funcionalidade. O AddPivotTable analisa um intervalo A1 retangular com um prefixo de folha opcional; as origens de intervalos nomeados e de livros de trabalho externos estão fora do que o construtor resolve, embora caches lidas de um ficheiro criado no Excel preservem a sua origem de intervalo nomeado no percurso de ida e volta. E as tabelas dinâmicas criadas pelo Excel que o HotXLS lê do disco são replicadas byte a byte ao guardar, pelo que as edições tipadas aqui descritas aplicam-se sem problemas a tabelas dinâmicas que crie em código, enquanto as 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, AutoFilter e tabelas estruturadas

O modelo de tabela dinâmica apresentado aqui faz parte do HotXLS Delphi Excel Component padrão para Delphi e C++Builder, que lê e escreve as partes de tabelas dinâmicas tipadas XLSX e os registos clássicos BIFF8 a partir do mesmo modelo de objetos