Exportar Resultados de Base de Dados Delphi para Relatórios Excel com o HotXLS

Transformar o resultado de uma consulta num relatório de Excel é resolver três problemas em simultâneo. Cada tipo de campo do Delphi tem de ser registado na célula com o tipo de Excel correto, a linha de cabeçalho deve apresentar-se como a de um relatório e não como uma listagem técnica do esquema, e os números, datas e valores monetários devem incluir formatos estáveis. Se descurar qualquer um destes pontos, o ficheiro continuará a abrir, manterá o seu aspeto plausível, mas falhará no momento em que um utilizador da área financeira selecionar uma coluna e esperar pela soma que nunca chega a aparecer. Os valores foram gravados como texto, o Excel processa-os como rótulos (labels) e nenhuma exceção é gerada para o alertar.

O HotXLS é uma biblioteca nativa em Object Pascal para folhas de cálculo que grava ficheiros XLS e XLSX diretamente a partir do Delphi e C++Builder, sem necessitar de automatização do Excel. Esta disponibiliza duas vias para passar de um TDataset para um livro de Excel: o componente pronto a usar TDataToXLS, e um ciclo (loop) de escrita manual com a API do livro. Estes não são equivalentes. O componente pertence à biblioteca VCL e assenta na fachada XLS, pelo que a escolha correta depende de onde o código é executado e de qual o formato de ficheiro esperado pelo destinatário. De seguida, abordamos ambas as opções, o limite a partir do qual o componente deixa de ser a melhor opção e como preservar a integridade dos tipos de dados em qualquer cenário.

Os tipos de dados constituem o verdadeiro contrato de exportação

Antes de qualquer chamada à API, defina como cada tipo de campo do Delphi é registado na célula. Uma célula que receba uma string do Delphi permanecerá como string. O HotXLS não tenta deduzir que '1,234.50' deveria ser um número, nem o deve fazer, pois a análise baseada em configurações regionais é exatamente o motivo pelo qual uma vírgula decimal alemã pode ser convertida num separador de milhares num servidor em inglês. A abordagem recomendável é atribuir os valores através dos métodos de leitura tipificados: AsFloat ou AsCurrency para campos numéricos, AsDateTime para datas (para que a célula contenha um número de série de data do Excel válido, em vez de uma string formatada) e AsString apenas para os campos que sejam realmente texto.

O tratamento de valores nulos (NULL) exige uma política definida. Converter um campo nulo com VarToStr transforma o NULL do SQL numa string vazia, gerando uma célula de texto, enquanto omitir a atribuição mantém a célula genuinamente vazia, o que é o esperado por funções como AVERAGE, COUNT e tabelas dinâmicas. Para colunas de valores monetários, determine antes da escrita do ciclo se o NULL significa zero ou um valor desconhecido. Ambos são apresentados de forma idêntica após a formatação da coluna, mas a diferença afeta todos os cálculos agregados efetuados posteriormente.

A via do componente: TDataToXLS em aplicações VCL

Para uma aplicação VCL tradicional com uma consulta já associada a um módulo de dados, o TDataToXLS é a via de chamada única. Este percorre qualquer descendente de TDataset (seja FireDAC, ADO, IBX ou qualquer outro que implemente a interface abstrata de conjuntos de dados) e produz uma folha de cálculo formatada com títulos de cabeçalhos, fontes, limites, subtotais de grupos opcionais e divisão automática de folhas para conjuntos de dados extensos.

var
  Exporter: TDataToXLS;
begin
  Exporter := TDataToXLS.Create(nil);
  try
    Exporter.Dataset := OrdersQuery;          // any TDataset descendant
    Exporter.WorksheetName := 'Orders';
    Exporter.HeaderSource := hsDisplayLabel;  // captions, not raw column names
    Exporter.GroupFields.Add('CustomerID');   // subtotal block per customer
    Exporter.RowsPerSheet := 50000;           // stay below the BIFF8 row ceiling
    Exporter.OnlyVisible := True;             // respect Field.Visible
    Exporter.SaveDatasetAs('orders.xls');
  finally
    Exporter.Free;
  end;
end;

Duas propriedades assumem a maior parte das configurações práticas nesta etapa. HeaderSource := hsDisplayLabel escreve a propriedade DisplayLabel de cada campo em vez do nome da coluna SQL, para que o livro apresente "Customer Name" em vez de CUST_NM. O RowsPerSheet existe porque o componente escreve em formato BIFF8, cuja grelha está limitada a 65 536 linhas por 256 colunas; defini-lo para 50 000 divide conjuntos de dados extensos em várias folhas antes que o limite do formato resulte no truncamento dos dados. O aspeto visual é gerido pelas propriedades HeaderFont, DetailFont, GroupColor e estilos de limites, e o conjunto DisableFormat permite desativar categorias de formatação completas quando o utilizador requer apenas células simples. Para qualquer personalização adicional, os eventos AfterCell e AfterRow disponibilizam o intervalo acabado de escrever para processamento posterior.

Os limites do componente

O TDataToXLS inclui três limitações por conceção, sendo importante conhecê-las para evitar alterações estruturais complexas no futuro.

• É um componente VCL no sentido estrito. A sua unidade importa as referências Forms, Controls e Dialogs, pelo que associá-lo a uma tarefa de consola ou a um serviço do Windows arrastará a biblioteca VCL para o binário. As unidades principais do livro não possuem esta dependência. Apenas necessitam de Windows, Classes, SysUtils e Variants, razão pela qual o código do lado do servidor deve utilizar o ciclo manual indicado abaixo.
• Assenta na fachada XLS. O componente preenche um IXLSWorkbook e gera ficheiros .xls (BIFF8). Não existe nenhuma propriedade que mude a saída para o formato OOXML.
• Os seus eventos utilizam a arquitetura XLS. O parâmetro Cell: IXLSRange no evento AfterCell pertence ao modelo de objetos XLS, pelo que a personalização por célula aí configurada segue o estilo XLS mesmo que o ficheiro seja convertido para .xlsx posteriormente.

Produzir ficheiros .xlsx a partir da saída do componente

Quando o utilizador exigir o formato .xlsx, mas a lógica de exportação já assentar em TDataToXLS, a função de ponte na unidade lxXlsxExport converte o livro preenchido numa única chamada:

uses lxXlsxExport;

Exporter.SaveDatasetAs('orders.xls');
// the component exposes the IXLSWorkbook it populated
SaveXLSWorkbookAsXLSX(Exporter.Workbook, 'orders.xlsx');

Trate esta conversão de ponte como um transportador de dados tabulares e não como um conversor completo. Este copia valores, fórmulas, formatos de números, cores de preenchimento, atributos de fontes, larguras de colunas e definições de visualização. Contudo, não copia limites, intervalos unidos, comentários, gráficos ou formatação condicional. Para uma grelha de dados simples com cabeçalho e linhas, isto é suficiente. Para um relatório estilizado não o é, e a solução correta consiste em gerar o XLSX diretamente em vez de tentar ajustar o ficheiro convertido.

O ciclo manual para serviços e tarefas em lote

O código no lado do servidor deve utilizar a classe TXLSXWorkbook diretamente. Observe a diferença de tempo de vida dos objetos entre as duas fachadas antes de copiar qualquer código. O TXLSWorkbook do lado XLS é gerido por uma interface com contagem de referências e não deve ser libertado manualmente, ao passo que o TXLSXWorkbook é uma classe comum que exige a libertação com um bloco try..finally Free. Confundir estas convenções é um caminho seguro para criar fugas de memória (leaks) ou duplas libertações de objetos.

procedure ExportOrders(Q: TDataSet; const FileName: string);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Orders');
    Sheet.Cells[1, 1].Value := 'Order No';
    Sheet.Cells[1, 2].Value := 'Customer';
    Sheet.Cells[1, 3].Value := 'Ordered';
    Sheet.Cells[1, 4].Value := 'Amount';

    Row := 2;
    Q.First;
    while not Q.Eof do
    begin
      Sheet.Cells[Row, 1].Value := Q.FieldByName('OrderNo').AsInteger;
      Sheet.Cells[Row, 2].Value := Q.FieldByName('Customer').AsString;
      if not Q.FieldByName('Ordered').IsNull then
        Sheet.Cells[Row, 3].Value := Q.FieldByName('Ordered').AsDateTime;
      Sheet.Cells[Row, 4].Value := Q.FieldByName('Amount').AsFloat;
      Inc(Row);
      Q.Next;
    end;

    Book.StreamingWrite := True;  // stream sheet XML straight into the zip
    Book.SaveAs(FileName);
  finally
    Book.Free;
  end;
end;

Os aspeto principais são a atribuição tipificada e a proteção para campos nulos com IsNull. As datas entram como números de série de data, os valores numéricos entram como doubles e as datas de encomenda nulas permanecem vazias em vez de se tornarem strings vazias. A definição StreamingWrite := True altera apenas o processo de gravação: o XML da folha é transmitido diretamente para o contentor zip e não montado em memória como uma grande string primeiro, reduzindo picos de consumo de RAM ao gravar relatórios com centenas de milhares de linhas. Cada método de gravação dispõe de uma sobrecarga TStream, permitindo direcionar o livro diretamente para a resposta HTTP sem passar pelo disco. O artigo sobre gravação em fluxo e tarefas em lote detalha este padrão de implementação, e o artigo sobre desempenho em livros grandes descreve as ações para quando a dimensão dos dados aumenta.

Esta abordagem com ciclo manual é também a indicada para cenários multithreading. Ambos os motores são processos nativos em Object Pascal (fluxos de registos BIFF8 num lado e zip com XML no outro), pelo que nenhuma fase da exportação recorre a automatizações COM ou requer uma licença do Excel no servidor. Isto traduz-se em paralelismo real sem os limites de uma instância única, desde que cada thread crie o seu próprio objeto de livro. As instâncias de livros não são seguras para partilha (thread-safe), pelo que a regra é uma instância por exportação, e nunca um objeto partilhado e protegido por trincos (locks).

Existe um limite relevante no desenho do sistema. A grelha XLSX está limitada a 1 048 576 linhas por 16 384 colunas, pelo que a divisão em folhas efetuada pela propriedade RowsPerSheet no formato XLS raramente é necessária aqui. Contudo, um livro com um milhão de linhas raramente é o pretendido para leitura humana. Quando o conjunto de dados tem essa escala, um ficheiro de texto delimitado é normalmente a interface adequada, e o artigo sobre exportação para CSV e TSV aborda os delimitadores, o funcionamento do BOM e a avaliação de fórmulas necessária nesses formatos.

Escolher o ponto de partida

Se a exportação se destinar a uma aplicação desktop VCL e o formato .xls for aceite, comece por usar o TDataToXLS e as suas funções de agrupamento. Exige menos código, e a ponte baseada em SaveXLSWorkbookAsXLSX está disponível se mais tarde for solicitada a saída em .xlsx, desde que se aceitem as limitações de conversão descritas. Se o código for executado em segundo plano e sem interação (unattended) ou o destinatário exigir .xlsx desde o início, opte por desenvolver o ciclo manual. Ambas as abordagens disponibilizam projetos de demonstração prontos a usar e integram o pacote do HotXLS Component.