Relatórios Excel a Partir de Modelos em Delphi com HotXLS

A forma fiável de produzir a partir do Delphi um relatório Excel com estilo é partir de uma pasta de trabalho que um designer já construiu. Alguém no departamento financeiro desenha a fatura no Excel: o logótipo, os cabeçalhos de coluna, os contornos da banda de detalhe, a linha de totais a negrito, os formatos de moeda. O seu código abre esse ficheiro, deixa cair dados reais nas células que o designer reservou para eles, e guarda o resultado. O aspeto é deles; os números são seus. O HotXLS, uma biblioteca nativa para Delphi e C++Builder que lê e escreve pastas de trabalho XLS e XLSX sem conduzir o Excel, dá-lhe as três operações de que esta abordagem precisa: procurar uma célula pelo seu texto, copiar um intervalo com os estilos e as fórmulas intactos, e inserir linhas para que tudo o que está abaixo desça com os dados

A única regra que separa um gerador que sobrevive a edições do modelo de outro que se parte à primeira é nunca endereçar células por números literais de linha e coluna. Um modelo é um documento que outras pessoas editam. A equipa financeira acrescenta uma linha de imposto, aumenta a altura da linha do logótipo, reordena o bloco de morada, e o formato do ficheiro não o ajuda em nada: uma gravação BIFF ou OOXML tem sucesso quer a linha 10 continue a significar o que significava no trimestre passado quer não. Um gerador que escreve a primeira linha de detalhe numa linha 10 fixa no código vai, na primeira vez que alguém inserir um bloco acima da secção de detalhe, carimbar itens sobre as células erradas e somar um intervalo de totais que já não cobre os dados. Nada lança exceção, todas as gravações devolvem sucesso, e o único sinal é um cliente a reparar numa fatura errada

Ancore cada coordenada a um marcador de posição

A solução é 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 tem de tocar, e o gerador calcula em tempo de execução cada posição a partir do sítio onde encontra esses marcadores. As edições de disposição deixam de importar, porque o marcador desloca-se com a célula em que assenta. A segunda metade do contrato é a regra de falha: se faltar um marcador obrigatório, o trabalho para antes de qualquer dado de cliente chegar ao ficheiro. Um modelo que derivou deve produzir um bilhete de trabalho falhado, não um documento entregue

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. FindText devolve a linha e a coluna da primeira célula cujo texto corresponde, com uma sobrecarga que acrescenta sensibilidade a maiúsculas. ReplaceText troca todas as ocorrências e devolve quantas alterou. As duas cobrem os dois tipos de marcador que costuma ter. Uma âncora única, como o nome do cliente, localiza-a uma vez e escreve ao lado; um marcador que deve aparecer exatamente uma vez, como a data do relatório, substitui-o e verifica a contagem. Do lado do XLSX, um preenchimento que se ancora desta forma tem este aspeto:

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 começa em 0

    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');
    // a expansão do detalhe e a gravação vêm a seguir
  finally
    Book.Free;
  end;
end;

Dois pormenores importam. Primeiro, FindText e ReplaceText comparam com o valor de texto de uma célula; um marcador embebido dentro de uma string de fórmula é invisível para eles, pelo que os marcadores de posição pertencem a células simples, nunca dentro de fórmulas. Segundo, a contagem de substituições é o seu detetor de deriva. Um modelo que devia conter exatamente um marcador {{DATE}} mas reporta zero substituições foi editado, e lançar uma exceção nesse momento é precisamente o que transforma a deriva silenciosa de disposição numa falha visível

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

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

const
  DetailRow = 10;            // a linha de exemplo formatada no modelo
var
  I: Integer;
begin
  // Abrir espaço antes do bloco de totais primeiro, para que o intervalo
  // do SUM abaixo da banda de detalhe se estique junto com os dados.
  if Length(Items) > 1 then
    Sheet.InsertRows(DetailRow + 1, Length(Items) - 1);

  for I := 0 to High(Items) do
  begin
    if I > 0 then              // clona estilos + fórmulas da linha de exemplo
      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]);  // sem prefixo '='
  end;
end;

Repare com atenção na atribuição da fórmula. A propriedade Formula do XLSX recebe a expressão sem sinal de igual inicial, 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 falha sem se queixar: a célula fica apenas com uma string literal que o Excel mostra como texto. Se o modelo decorar a banda de detalhe com linhas de título unidas, lembre-se de que apenas a célula superior esquerda de uma área unida transporta um valor. As regras de disposição em o artigo companheiro sobre células unidas em modelos de relatório orientados à disposição explicam por que razão as regiões unidas pertencem inteiramente fora da banda de dados

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

Inserir linhas antes do bloco de totais é o que mantém um intervalo de SUM a esticar-se à medida que a secção de detalhe cresce. Do lado do XLSX, InsertRows arrasta consigo uma longa lista de estruturas dependentes juntamente com as células: intervalos unidos, alturas de linha, hiperligações, comentários, painéis fixos, intervalos de filtro automático, formatos condicionais, validações de dados, tabelas, nomes definidos e âncoras de imagens e gráficos. Há nessa lista uma fronteira que vale a pena decorar. A reescrita de fórmulas só alcança referências dentro da mesma folha. Uma fórmula numa folha de resumo que aponta para a região deslocada mantém as coordenadas antigas e passa a ler discretamente as células erradas, razão pela qual os totais recolhidos entre folhas são mais seguros expressos através de nomes ao nível da pasta de trabalho. O artigo companheiro sobre nomes definidos e fórmulas entre folhas percorre esse padrão

O formato legado XLS traça a linha num sítio mais duro. O HotXLS mantém tabelas dinâmicas, tabelas de consulta e ligações a dados externos nos ficheiros BIFF como blocos de bytes em bruto. Sobrevivem à abertura e à gravação sem alteração, mas não são modeladas, pelo que a inserção de linhas nunca lhes toca. Um modelo que estaciona uma tabela dinâmica por baixo de um bloco de detalhe em expansão grava sem aviso nenhum enquanto o retângulo de origem da tabela dinâmica escorrega para fora dos dados. A saída é estrutural, não defensiva: mantenha o conteúdo dinâmico e de consulta em folhas onde o gerador nunca insere, e a desatualização não pode acontecer

Recalcule antes de entregar, ou saiba por que razão o dispensou

O HotXLS não avalia fórmulas durante o SaveAs. Quando uma pessoa abre o ficheiro, o Excel recalcula tudo (a fachada XLS expõe CalculationMode e RecalcOnSave se precisar de conduzir isso), pelo que um relatório destinado a uma caixa de correio humana não precisa de mais nada de si. O quadro muda no momento em que a pasta de trabalho alimenta outro programa. A exportação para CSV escreve as fórmulas como texto literal e nunca as calcula, e qualquer analisador a jusante que confie em valores em cache vai ler números desatualizados ou vazios. Para esses caminhos, calcule no servidor com Calculate, que avalia uma expressão arbitrária contra a pasta de trabalho carregada 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;

Verificar o total calculado contra o registo da encomenda antes da gravação é um seguro barato com bom retorno. Transforma uma fatura errada num trabalho falhado. Um operador repete um trabalho falhado em segundos; uma fatura errada já na caixa de correio de um cliente custa a um gestor de conta um pedido de desculpas e uma correção

Duas famílias de classes, um só algoritmo

A mesma lógica passa entre formatos, mas não o mesmo código. TXLSWorkbook, para o legado .xls, é baseado em interfaces e com contagem de referências, com indexação de folhas a começar em um, e nunca o liberta à mão. TXLSXWorkbook, para .xlsx, é um objeto vulgar que tem de libertar num try..finally, com indexação de folhas a começar em zero e a convenção de fórmulas mostrada acima. FindText, ReplaceText, CopyRange e InsertRows existem dos dois lados, pelo que a forma ancorar-clonar-recalcular transita sem atrito. O conselho prático é comprometer-se com um formato por pipeline, ou esconder os dois ciclos de vida de objeto atrás de um adaptador fino de sua autoria em vez de espalhar a diferença pelo gerador

A dimensão raramente importa para o tipo de relatório que este padrão produz. Clonar uma linha com estilo alguns milhares de vezes não é nada para o hardware atual. O caminho de gravação só se torna o estrangulamento quando uma banda de detalhe entra nas centenas de milhares de linhas, e nessa altura ativar StreamingWrite envia o XML da folha de cálculo diretamente para o pacote de saída em vez de o guardar em memória intermédia; o artigo sobre escritas em fluxo para trabalhos de servidor e em lote cobre quando vale a pena fazer essa troca. Os gráficos comportam-se como o resto da disposição: do lado do XLSX tanto a âncora do gráfico como as referências das suas séries se deslocam quando o InsertRows corre acima delas, pelo que um gráfico abaixo da linha de totais continua ligado aos dados certos, ao passo que do lado do XLS os gráficos assentam nas suas próprias folhas de gráfico e, tal como as tabelas dinâmicas, nunca se deslocam. É mais um argumento para manter as folhas de apresentação afastadas da folha que o gerador expande

Esta abordagem de ancorar-clonar-recalcular permite que um designer seja dono do aspeto de uma pasta de trabalho enquanto o seu código é dono do que ela diz, o que é normalmente o que torna uma saída Excel gerada digna de ser mantida. As chamadas de pesquisa, cópia e inserção aqui mostradas, juntamente com o motor de fórmulas usado na verificação do total antes da entrega, são fornecidas com o HotXLS Delphi Component para Delphi e C++Builder