Geração de Relatórios Excel Baseada em Modelos em Delphi com o HotXLS

A forma mais fiável de produzir um relatório Excel estilizado a partir de Delphi consiste em começar com um livro de cálculo que um designer já tenha construído. Alguém na área financeira desenha a fatura no Excel: o logótipo, os cabeçalhos das colunas, as margens na faixa de detalhe, a linha de totais em negrito, os formatos de moeda. O seu código abre esse ficheiro, insere os dados em tempo real nas células que o designer reservou para o efeito e guarda o resultado. A aparência é deles; os números são seus. O HotXLS, uma biblioteca nativa para Delphi e C++Builder que lê e escreve livros XLS e XLSX sem necessidade de executar o Excel, fornece-lhe as três operações de que esta abordagem necessita: procurar uma célula pelo seu texto, copiar um intervalo com os seus estilos e fórmulas intactos e inserir linhas de forma a que tudo o que esteja abaixo se desloque para baixo com os dados.

A regra de ouro que distingue um gerador que sobrevive a edições de modelos de um que falha à primeira alteração é nunca endereçar as células por números literais de linha e coluna. Um modelo é um documento que outras pessoas editam. A equipa financeira adiciona uma linha de imposto, aumenta a altura da linha do logótipo, reordena o bloco de endereço, e o formato do ficheiro não o ajuda em nada: uma gravação BIFF ou OOXML é bem-sucedida quer a linha 10 ainda signifique ou não o que significava no trimestre passado. Um gerador que escreva a primeira linha de detalhe numa linha 10 hardcoded irá, na primeira vez que alguém inserir um bloco acima da secção de detalhe, registar itens sobre as células erradas e somar um intervalo de totais que já não cobre os dados. Nenhum erro é gerado, cada gravação reporta sucesso, e o único sinal de alerta é um cliente detetar uma fatura incorreta.

Ancorar cada coordenada a um marcador de posição (token)

A solução consiste em fazer com que o modelo transporte as suas próprias coordenadas. O designer escreve marcadores como {{CUSTOMER}}, {{DATE}} e {{DETAIL_START}} nas células que o gerador deve aceder, e o gerador calcula cada posição em tempo de execução a partir do local onde encontra esses marcadores. As edições de layout deixam de importar, porque o marcador desloca-se juntamente com a célula onde está inserido. A segunda parte do contrato é a regra de falha: se um marcador obrigatório estiver em falta, a tarefa é interrompida antes que qualquer dado do cliente chegue ao ficheiro. Um modelo desajustado deve gerar um alerta de falha de tarefa, e não um documento entregue ao cliente.

Encontrar os marcadores: FindText e ReplaceText

Ambas as famílias de classes do HotXLS expõem pesquisa ao nível da folha de cálculo. O FindText devolve a linha e a coluna da primeira célula cujo texto coincide com a pesquisa, com uma sobrecarga que adiciona diferenciação de maiúsculas e minúsculas. O ReplaceText substitui todas as ocorrências e devolve a quantidade de alterações efetuadas. Ambos cobrem os dois tipos de marcadores que costuma ter: uma âncora única, como o nome do cliente, que localiza uma vez e escreve ao lado; e um marcador que deve surgir exatamente uma vez, como a data do relatório, que substitui e valida a contagem. No lado do XLSX, um preenchimento ancorado desta forma assemelha-se a isto:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  R, C: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open('invoice-template.xlsx') <> 1 then
      raise Exception.Create('Cannot open invoice template');
    Sheet := Book.Sheets[0];               // TXLSXSheets.Items is 0-based

    if not Sheet.FindText('{{CUSTOMER}}', R, C) then
      raise Exception.Create('Template drift: {{CUSTOMER}} anchor missing');
    Sheet.Cells[R, C].Value := 'ACME Corp';

    if Sheet.ReplaceText('{{DATE}}',
         FormatDateTime('yyyy-mm-dd', Date)) = 0 then
      raise Exception.Create('Template drift: {{DATE}} token missing');
    // detail expansion and save follow below
  finally
    Book.Free;
  end;
end;

Dois detalhes importam. Primeiro, o FindText e o ReplaceText correspondem ao valor de texto de uma célula; um marcador incorporado dentro de uma string de fórmula é invisível para eles, pelo que os marcadores de posição pertencem a células comuns, nunca dentro de fórmulas. Segundo, a contagem de substituições é o seu detetor de desvios. Um modelo que devia conter exatamente um marcador {{DATE}} mas que reporta zero substituições foi editado, e gerar uma exceção nesse momento é precisamente o que transforma um desvio silencioso de layout numa falha visível.

Clonar a linha de detalhe sem perder estilos ou fórmulas

A secção de detalhe de uma fatura cresce com os dados. Escrever valores diretamente em linhas em branco abaixo da linha de amostra deita fora tudo o que o designer preparou: as margens, os formatos numéricos, as fórmulas por linha. O padrão que preserva tudo isto consiste em manter uma linha de amostra totalmente formatada no modelo e cloná-la para cada item. O CopyRange duplica estilos e fórmulas numa única chamada, após o que o gerador sobrescreve apenas as células de valor.

const
  DetailRow = 10;            // the formatted sample row in the template
var
  I: Integer;
begin
  // Open space before the totals block first, so the SUM range
  // below the detail band stretches together with the data.
  if Length(Items) > 1 then
    Sheet.InsertRows(DetailRow + 1, Length(Items) - 1);

  for I := 0 to High(Items) do
  begin
    if I > 0 then              // clone styles + formulas from the sample row
      Sheet.CopyRange(DetailRow, 1, DetailRow, 5, DetailRow + I, 1);
    Sheet.Cells[DetailRow + I, 1].Value := Items[I].Name;
    Sheet.Cells[DetailRow + I, 2].Value := Items[I].Qty;
    Sheet.Cells[DetailRow + I, 3].Value := Items[I].UnitPrice;
    Sheet.Cells[DetailRow + I, 4].Formula :=
      Format('B%d*C%d', [DetailRow + I, DetailRow + I]);  // no '=' prefix
  end;
end;

Preste muita atenção à atribuição de fórmulas. A propriedade Formula do XLSX recebe a expressão sem um sinal de igual no início, ao passo que a fachada XLS espera '=B10*C10' atribuído através de Value. Misturar as duas convenções é o erro de portabilidade mais comum entre as famílias de classes, e em caso de engano falha sem qualquer aviso: a célula simplesmente assume uma string literal que o Excel exibe como texto. Se o modelo decorar a faixa de detalhe com linhas de título unidas, lembre-se de que apenas a célula superior esquerda de uma área unida contém um valor. As regras de layout descritas no artigo de acompanhamento sobre células unidas em modelos de relatórios orientados a layout explicam por que razão as regiões unidas devem ficar completamente fora da faixa de dados.

O que o InsertRows move, e o que deixa para trás

Inserir linhas antes do bloco de totais é o que permite a um intervalo SUM estender-se à medida que a secção de detalhe cresce. No lado do XLSX, o InsertRows arrasta consigo uma longa lista de estruturas dependentes juntamente com as células: intervalos unidos, alturas de linhas, hiperligações, comentários, painéis fixos, intervalos de autofiltro, formatações condicionais, validações de dados, tabelas, nomes definidos e âncoras de imagem e gráfico. Há um limite nessa lista que vale a pena reter na memória: a reescrita de fórmulas abrange apenas referências dentro da própria folha. Uma fórmula numa folha de resumo que aponte para a região deslocada mantém as suas coordenadas antigas e lê silenciosamente as células erradas, razão pela qual os totais obtidos entre folhas de cálculo são expressos de forma mais segura através de nomes ao nível do livro. O artigo de acompanhamento sobre nomes definidos e fórmulas cruzadas entre folhas detalha esse padrão.

O formato legacy XLS coloca este limite num local mais estrito. O HotXLS mantém tabelas dinâmicas, tabelas de consulta e ligações de dados externos em ficheiros BIFF como blocos de bytes brutos. Sobrevivem a processos de abertura e gravação inalterados, mas não são modelados, pelo que a inserção de linhas nunca os afeta. Um modelo que posicione uma tabela dinâmica abaixo de um bloco de detalhe em expansão será guardado sem qualquer aviso, enquanto o retângulo de origem da tabela dinâmica se desvia dos dados. A solução é estrutural e não defensiva: mantenha o conteúdo dinâmico e de consultas em folhas nas quais o gerador nunca faça inserções, evitando que este desfasamento ocorra.

Recalcular antes da entrega, ou saber por que razão o ignorou

O HotXLS não avalia fórmulas durante o SaveAs. Quando um utilizador abre o ficheiro, o Excel recalcula tudo (a fachada XLS expõe o CalculationMode e o RecalcOnSave se necessitar de controlar isso), pelo que um relatório destinado a um destinatário humano não requer mais nada da sua parte. O cenário altera-se no momento em que o livro de cálculo serve para alimentar outro programa. A exportação para CSV escreve as fórmulas como o seu texto literal e nunca as calcula, e qualquer leitor a jusante que confie em valores em cache lerá números desatualizados ou espaços em branco. Para esses caminhos, realize o cálculo no servidor com o Calculate, que avalia uma expressão arbitrária contra o livro carregado e devolve o resultado:

var
  Total: Variant;
  LastDetail: Integer;
begin
  LastDetail := DetailRow + Length(Items) - 1;
  Total := Book.Calculate(Format('SUM(Invoice!D%d:D%d)',
    [DetailRow, LastDetail]));
  if (not VarIsNumeric(Total)) or
     (Abs(Total - ExpectedTotal) > 0.005) then
    raise Exception.Create('Invoice total does not match the order record');

  if Book.SaveAs('invoice-2026-0611.xlsx') <> 1 then
    raise Exception.Create('Save failed: check output path and permissions');
end;

Validar o total calculado contra o registo da encomenda antes de guardar é um seguro barato com um excelente retorno. Transforma uma fatura errada numa tarefa falhada. Um operador consegue repetir uma tarefa falhada em segundos; uma fatura errada na caixa de correio de um cliente custa a um gestor de conta um pedido de desculpas e um documento de retificação.

Duas famílias de classes, um único algoritmo

A mesma lógica migra entre formatos, mas não o mesmo código. O TXLSWorkbook para o formato legado .xls baseia-se em interfaces e é controlado por contagem de referências, com indexação de folhas baseada em 1, e nunca o liberta manualmente. O TXLSXWorkbook para .xlsx é um objeto simples que deve libertar num bloco try..finally, com indexação de folhas baseada em 0 e a convenção de fórmulas apresentada acima. O FindText, o ReplaceText, o CopyRange e o InsertRows existem em ambos os lados, pelo que a estrutura âncora-clonagem-recálculo transita de forma limpa. O conselho prático é comprometer-se com um único formato por pipeline, ou ocultar os dois ciclos de vida dos objetos sob um adaptador simples seu, em vez de espalhar essa diferença pelo gerador.

O tamanho raramente importa para o tipo de relatório que este padrão produz. Clonar uma linha estilizada algumas milhares de vezes é irrelevante para o hardware atual. O caminho de gravação só se torna no gargalo quando uma faixa de detalhe atinge seis dígitos de linhas, e nesse ponto a ativação de StreamingWrite envia o XML da folha de cálculo diretamente para o pacote de saída em vez de o colocar em buffer; o artigo sobre gravações em fluxo para tarefas em lote em servidores cobre os cenários onde essa troca compensa. Os gráficos comportam-se da mesma forma que o resto do layout: no lado do XLSX, tanto a âncora do gráfico como as suas referências de séries deslocam-se quando o InsertRows corre acima delas, mantendo o gráfico sob a linha de totais associado aos dados corretos, enquanto no lado do XLS os gráficos residem nas suas próprias folhas de gráficos e, à semelhança das tabelas dinâmicas, nunca se movem. Este é mais um argumento para manter as folhas de apresentação afastadas da folha que o gerador expande.

Esta abordagem de âncora-clonagem-recálculo permite que um designer controle a aparência do livro de cálculo enquanto o seu código gere o seu conteúdo, o que costuma justificar o esforço de manutenção de ficheiros Excel gerados. As chamadas de pesquisa, cópia e inserção apresentadas aqui, juntamente com o motor de fórmulas utilizado para a validação de totais pré-entrega, acompanham o Componente HotXLS para Delphi e C++Builder.