A abordagem segura para produzir um relatório Excel formatado em Delphi consiste em começar com um livro de cálculo já estruturado por um designer. Alguém na área financeira desenha a fatura no Excel: o logótipo, os cabeçalhos das colunas, os limites (borders) no bloco de detalhe, a linha de totais a negrito e a formatação monetária. O seu código abre esse ficheiro, insere os dados reais nas células reservadas pelo designer e grava o resultado. O aspeto visual é da responsabilidade deles; os dados são seus. O HotXLS, uma biblioteca nativa Delphi e C++Builder que lê e escreve livros XLS e XLSX sem recorrer ao Excel, disponibiliza as três operações necessárias nesta abordagem: pesquisar uma célula pelo seu texto, copiar um intervalo preservando os seus estilos e fórmulas, e inserir linhas para deslocar o conteúdo inferior à medida que os dados são introduzidos
A regra fundamental para que um gerador sobreviva a alterações nos modelos (templates) consiste em nunca aceder às células através de coordenadas fixas de linha e coluna. O modelo é um documento editado por terceiros: a equipa financeira pode adicionar uma linha de imposto, alterar a altura do cabeçalho ou reordenar o bloco de endereços, e o formato do ficheiro em nada ajuda (uma gravação BIFF ou OOXML conclui-se com sucesso quer a linha 10 corresponda ou não ao mesmo dado do trimestre passado). Se o gerador escrever a primeira linha de detalhe numa coordenada fixa como a linha 10, a primeira inserção de linhas acima do bloco de detalhe resultará na escrita de dados sobre as células erradas e a soma de totais deixará de abranger a informação em uso. Nenhuma exceção é gerada, todas as gravações reportam sucesso e o único sinal de erro será o cliente confrontar-se com uma fatura incorreta
Vincular todas as coordenadas a um marcador (placeholder token)
A solução consiste em fazer o modelo conter as suas próprias coordenadas. O designer escreve marcadores como {{CUSTOMER}}, {{DATE}} e {{DETAIL_START}} nas células que o gerador deve alterar, e este calcula cada posição em tempo de execução a partir do local onde encontra esses marcadores. As alterações de layout deixam de ser um obstáculo porque o marcador desloca-se com a célula respetiva. A outra face do protocolo é a regra de erro: se um marcador obrigatório estiver em falta, o processamento deve ser interrompido antes de qualquer dado de cliente ser escrito no ficheiro. Um modelo corrompido ou desatualizado deve originar um erro de processamento e não um documento com erros
Localizar os marcadores: FindText e ReplaceText
Ambas as famílias de classes do HotXLS disponibilizam pesquisas ao nível da folha de cálculo. O método FindText devolve a linha e a coluna da primeira célula cujo texto coincide com a pesquisa, contendo uma sobrecarga que diferencia maiúsculas de minúsculas. O método ReplaceText substitui todas as ocorrências e devolve a quantidade de substituições efetuadas. Estes tratam os dois tipos de marcadores comuns: um marcador único (como o nome do cliente) que localiza uma vez e escreve na célula vizinha; e um marcador que deve surgir exatamente uma vez (como a data do relatório), o qual substitui e valida a contagem. No lado XLSX, a escrita baseada neste padrão de ancoragem 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]; // a propriedade TXLSXSheets.Items baseia-se 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 dos detalhes e a gravação seguem abaixo
finally
Book.Free;
end;
end;Dois aspetos são relevantes: em primeiro lugar, os métodos FindText e ReplaceText avaliam o valor de texto da célula; um marcador contido no interior de uma fórmula é invisível para estes métodos, pelo que devem residir em células de texto simples e nunca dentro de fórmulas. Em segundo lugar, a contagem de substituições serve como monitor de integridade: um modelo que devesse conter exatamente um marcador {{DATE}} mas reporta zero substituições foi alterado, e gerar uma exceção nesse momento é precisamente o que converte uma degradação silenciosa 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 o volume de dados. Escrever valores diretamente em linhas em branco abaixo da linha modelo descarta tudo o que o designer preparou: os limites (borders), os formatos numéricos e as fórmulas por linha. O padrão que preserva estes elementos consiste em manter uma linha modelo totalmente formatada no ficheiro de origem e cloná-la para cada registo. O método CopyRange duplica os estilos e fórmulas numa chamada, restando ao gerador apenas escrever os valores nas células correspondentes
const
DetailRow = 10; // a linha modelo formatada no ficheiro de origem
var
I: Integer;
begin
// Abrir primeiro espaço antes do bloco de totais, para que a soma
// abaixo do detalhe acompanhe o crescimento dos 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 // clonar estilos + fórmulas da linha modelo
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 o prefixo '='
end;
end;Preste particular atenção à atribuição da fórmula: a propriedade Formula da classe XLSX recebe a expressão sem o sinal de igual inicial, ao passo que a interface XLS espera a string '=B10*C10' associada através da propriedade Value. Misturar estas convenções é o erro de migração mais habitual entre as duas famílias de classes e conclui-se sem avisos, ficando a célula simplesmente com texto literal que o Excel apresenta como texto. Se o modelo incluir linhas unidas na secção de detalhe, lembre-se que apenas a célula superior esquerda de uma área unida armazena dados. As regras de layout descritas no artigo complementar sobre células unidas em modelos de relatórios explicam por que as áreas unidas devem ser posicionadas fora do bloco de dados
O que o InsertRows desloca e o que deixa para trás
Inserir linhas antes do bloco de totais é o que permite ao intervalo de uma fórmula SUM expandir-se à medida que a secção de detalhe cresce. No lado XLSX, o método InsertRows desloca uma vasta lista de estruturas em conjunto com as células: células unidas, alturas de linhas, hiperligações, comentários, painéis fixos (frozen panes), intervalos de autofiltro, formatação condicional, validação de dados, tabelas, nomes definidos e âncoras de gráficos e imagens. Há um limite nesta lista a ter em conta: a reescrita de fórmulas abrange apenas referências dentro da própria folha de cálculo. Uma fórmula na folha Summary que aponte para Data!D2:D100 é reescrita quando são inseridas linhas em Data, que corresponde ao comportamento pretendido na maioria dos cenários. Valide esta situação em vez de a assumir como garantida, pois o motor permite verificá-lo facilmente:
// o motor de cálculo resolve nomes e referências entre folhas internamente
V := Book.Calculate(Format('SUM(Invoice!D%d:D%d)',
[DetailRow, LastDetail]));
if VarIsNumeric(V) then
Log('o total líquido está correto: ' + FloatToStr(V));O método Calculate avalia uma expressão arbitrária contra o estado atual do livro sem efetuar gravações, servindo como rotina de validação em testes de geração de relatórios. Calcule o valor agregado esperado a partir dos dados de origem em Pascal, execute a fórmula do livro e compare ambos. O artigo sobre o motor de fórmulas aborda o que o motor avalia, quando o faz e como estendê-lo com funções personalizadas
Recalcular antes da entrega ou compreender por que o omitiu
O HotXLS não calcula fórmulas no método SaveAs. Quando um utilizador abre o ficheiro, o Excel recalcula todo o conteúdo (a interface XLS expõe as propriedades CalculationMode e RecalcOnSave para gerir esse comportamento); assim, um relatório destinado a ser aberto por humanos não requer intervenção adicional. Contudo, o cenário muda se o livro alimentar outro programa: a exportação para CSV grava as fórmulas como texto literal e não as avalia, e qualquer analisador a jusante que confie nos valores em cache lerá dados desatualizados ou vazios. Para estes fluxos, execute a avaliação no servidor via Calculate, que processa a expressão 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('o total da fatura não corresponde ao registo da encomenda');
if Book.SaveAs('invoice-2026-0611.xlsx') <> 1 then
raise Exception.Create('A gravação falhou: valide o caminho de saída e as permissões');
end;Validar o total calculado contra o registo da encomenda antes da gravação constitui uma salvaguarda simples com excelente retorno, pois converte uma fatura com erros numa falha de processamento. Um operador consegue reiniciar uma tarefa falhada em segundos, ao passo que uma fatura incorreta já enviada ao cliente exige explicações adicionais
Duas famílias de classes, um algoritmo
A mesma lógica é portável entre formatos, mas não o mesmo código. A classe TXLSWorkbook para o formato legado .xls baseia-se em interfaces com contagem de referências, tem índices de folhas baseados em 1 e nunca é libertada manualmente. Pelo contrário, TXLSXWorkbook para .xlsx é um objeto comum que exige libertação numa instrução try..finally, com indexação de folhas baseada em 0 e a convenção de escrita de fórmulas descrita acima. Os métodos FindText, ReplaceText, CopyRange e InsertRows estão presentes em ambas as interfaces, pelo que o fluxo de ancoragem, clonagem e cálculo é comum. A recomendação prática consiste em adotar um único formato por fluxo de processamento, ou encapsular os dois ciclos de vida dos objetos sob um adaptador (wrapper) próprio para evitar dispersar estas diferenças no gerador
O tamanho raramente é um obstáculo no tipo de relatórios produzidos por este padrão. Clonar uma linha formatada algumas milhares de vezes é irrelevante para o hardware atual. O fluxo de gravação torna-se no gargalo apenas quando a secção de detalhe atinge centenas de milhares de linhas; nesse caso, ativar StreamingWrite envia o XML da folha diretamente para o pacote de saída sem carregamento prévio na memória (o artigo sobre escrita por streaming para processamento em lote aborda este cenário). Os gráficos comportam-se como o resto da estrutura: no lado XLSX, tanto a âncora do gráfico quanto as referências das séries são deslocadas quando InsertRows é executado acima deles, mantendo o gráfico associado aos dados corretos. No XLS, os gráficos residem em folhas próprias e não sofrem deslocamentos. Este comportamento reforça a vantagem de manter folhas de apresentação separadas da folha expandida pelo gerador
Esta abordagem de ancoragem, clonagem e cálculo permite ao designer gerir o aspeto visual do livro enquanto o seu código gere os dados apresentados, facilitando a manutenção dos relatórios gerados. Os métodos de pesquisa, cópia e inserção descritos, juntamente com o motor de cálculo para validações, são fornecidos com o HotXLS Component para Delphi e C++Builder