Converter o resultado de uma consulta num relatório Excel envolve resolver três problemas em simultâneo: cada tipo de campo do Delphi deve ser mapeado na célula com o tipo de dados correto do Excel; a linha de cabeçalho deve ser estruturada como um relatório e não como uma listagem técnica do esquema da base de dados; e os números, datas e valores monetários devem conter formatação que resista à conversão. Se falhar um destes aspetos, o arquivo abrirá sem erros e parecerá correto, mas falhará assim que um usuário da área financeira selecionar uma coluna e aguardar por uma soma automática que nunca é apresentada. Isto ocorre porque os valores foram guardados como texto, fazendo com que o Excel os trate como etiquetas (labels) e não ocorrem exceções no código para o avisar
O HotXLS é uma biblioteca nativa Object Pascal de folhas de cálculo que grava arquivos XLS e XLSX diretamente a partir de Delphi e C++Builder, sem necessitar da automação do Excel. Disponibiliza duas vias para exportar de um TDataset para um livro de cálculo: o componente TDataToXLS pronto a usar, e a construção manual de um ciclo iterativo sobre a API do livro. Estes métodos não são permutáveis. O componente foi concebido para o ambiente VCL e assenta na interface XLS; assim, a escolha acertada depende do local onde o código corre e do formato que o destinatário final espera. Segue-se a descrição de ambas as abordagens, os limites onde o componente deixa de ser a ferramenta recomendada e como preservar a integridade dos tipos de dados em qualquer uma das opções
Os tipos de dados dos campos são o verdadeiro protocolo de exportação
Antes de efetuar qualquer chamada à API, determine como cada tipo de campo do Delphi deve ser escrito na célula. Uma célula que receba uma string Delphi mantém-se como texto. O HotXLS não tenta deduzir se a string '1,234.50' devia ser um número; tal comportamento é correto, pois a conversão dependente de definições regionais (locale) é a razão pela qual uma vírgula decimal alemã pode ser convertida num separador de milhares num servidor em inglês. A abordagem segura consiste em efetuar a atribuição através de propriedades tipadas: AsFloat ou AsCurrency para campos numéricos; AsDateTime para datas, garantindo que a célula contenha um número de série de data real do Excel em vez de texto formatado; e AsString exclusivamente para campos que sejam realmente de texto
O tratamento de valores nulos (Null) merece uma definição clara de início e não apenas a adoção do comportamento padrão. Converter o valor de um campo com VarToStr transforma o NULL do SQL numa string vazia (uma célula de texto), ao passo que omitir a atribuição deixa a célula realmente vazia, que é o comportamento esperado para funções como AVERAGE, COUNT e tabelas dinâmicas (pivot tables). Para colunas de valores monetários, decida antes de desenhar o ciclo se NULL representa zero ou um valor desconhecido. Ambos são exibidos de forma idêntica quando a coluna é formatada, mas a diferença afeta todos os valores agregados calculados a jusante
A via do componente: TDataToXLS em aplicações VCL
Para uma aplicação VCL clássica com uma consulta já associada a um módulo de dados, o componente TDataToXLS oferece a solução mais direta. Este percorre qualquer descendente de TDataset (seja FireDAC, ADO, IBX ou qualquer outro que implemente a interface abstrata de dataset) e produz uma folha de cálculo formatada com títulos de cabeçalho, tipos de letra, limites (borders), subtotais opcionais por agrupamento e divisão automática de folhas para conjuntos de dados de grandes dimensões
Duas propriedades desempenham aqui um papel crucial: HeaderSource := hsDisplayLabel grava a propriedade DisplayLabel de cada campo em vez do nome técnico da coluna SQL, gerando cabeçalhos legíveis como "Customer Name" em vez de CUST_NM. A propriedade RowsPerSheet é relevante porque o componente grava no formato BIFF8, cuja grelha está limitada a 65.536 linhas por 256 colunas; configurar este valor para 50.000 divide um conjunto volumoso de dados por várias folhas antes que o limite do formato trunque a informação. O aspeto visual é gerido pelas propriedades de HeaderFont, DetailFont, GroupColor e pelo estilo dos limites; adicionalmente, o conjunto DisableFormat permite desativar categorias completas de formatação caso pretenda células simples. Para personalizações avançadas, os eventos AfterCell e AfterRow disponibilizam o intervalo acabado de escrever para processamento posterior
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;Os limites do componente
O componente TDataToXLS apresenta três limitações estruturais, e conhecê-las antecipadamente evita a necessidade de reescrever código no futuro
- Trata-se de um componente VCL completo. A sua unit importa
Forms,ControlseDialogs, pelo que a sua inclusão numa aplicação de consola ou num serviço Windows arrastará a biblioteca VCL para o executável. As units principais de manipulação de livros de cálculo não contêm esta dependência: requerem apenasWindows,Classes,SysUtilseVariants, razão pela qual o código de servidor deve adotar o ciclo iterativo descrito abaixo - Assenta na faceta XLS. O componente povoa um objeto
IXLSWorkbooke gera arquivos .xls (BIFF8). Não existe qualquer propriedade que mude o destino para saída OOXML - Os seus eventos utilizam a convenção XLS. O parâmetro
Cell: IXLSRangeno eventoAfterCellpertence ao modelo de objetos XLS, pelo que qualquer personalização por célula escrita nesse ponto segue a lógica XLS, mesmo que o arquivo seja posteriormente convertido para .xlsx
Gerar arquivos .xlsx a partir da saída do componente
Quando o destinatário exige o formato .xlsx mas a lógica de exportação já foi implementada através do componente TDataToXLS, a função de conversão rápida na unit lxXlsxExport realiza o processamento numa chamada:
Considere a conversão rápida como uma forma de transferir dados tabelados e não como uma conversão de classificação de fidelidade total. Esta copia valores, fórmulas, formatos numéricos, cores de preenchimento, propriedades de tipos de letra, larguras de colunas e definições de visualização. Contudo, não copia limites (borders), células unidas, comentários, gráficos ou formatação condicional. Para uma grelha simples contendo cabeçalho e linhas, esta conversão é suficiente. Para um relatório formatado complexo, não o é, e a solução recomendada consiste em gerar o arquivo XLSX diretamente em vez de tentar corrigir o arquivo convertido
uses lxXlsxExport;
Exporter.SaveDatasetAs('orders.xls');
// the component exposes the IXLSWorkbook it populated
SaveXLSWorkbookAsXLSX(Exporter.Workbook, 'orders.xlsx');O ciclo manual para serviços e tarefas em lote
O código de servidor deve interagir com o objeto TXLSXWorkbook diretamente. Note a diferença de gestão de ciclo de vida entre as duas facetas antes de copiar qualquer trecho de código: a classe TXLSWorkbook no lado XLS é mantida sob uma interface com contagem de referências e não deve ser libertada manualmente; pelo contrário, TXLSXWorkbook é uma classe comum e requer uma instrução try..finally Free. Misturar estas convenções causará perdas de memória ou erros de libertação dupla (double-free)
As instruções relevantes são as atribuições tipadas e a validação de IsNull. As datas são escritas como números de série de data, os valores monetários como doubles e as datas nulas permanecem vazias em vez de se tornarem em strings vazias. Definir StreamingWrite := True altera apenas o fluxo de gravação: o XML da folha de cálculo é enviado diretamente para o contentor zip à medida que é gerado, eliminando picos de consumo de memória no momento da chamada de SaveAs para volumes de centenas de milhares de linhas. Todos os métodos de gravação contam com uma sobrecarga de TStream, permitindo enviar o livro diretamente numa resposta HTTP sem passar pelo disco. O artigo sobre escrita por streaming e processamento em lote detalha este padrão de servidor, e o artigo sobre desempenho com grandes livros de cálculo aborda as medidas a tomar quando a quantidade de linhas aumenta
Este ciclo constitui também o caminho para escalar o processamento em threads. Ambos os motores são escritores nativos em Object Pascal (fluxos de registro BIFF8 de um lado e XML com compressão zip OOXML do outro), pelo que nenhuma fase da exportação recorre à automação COM ou exige licenças do Excel no servidor. Isto viabiliza a execução paralela de tarefas sem gargalos, desde que cada thread instancie o seu próprio livro. Os objetos do livro de cálculo não são seguros para partilha entre threads; por isso, a regra define uma instância por exportação, sem partilhar objetos geridos por trincos (locks)
Há um limite relevante a ter em conta: a grelha XLSX termina em 1.048.576 linhas por 16.384 colunas, pelo que a divisão por várias folhas (gerida por RowsPerSheet no XLS) raramente é necessária neste formato. Um livro de cálculo com um milhão de linhas também não costuma ser a opção pretendida por usuários humanos; quando o volume de dados atinge essa escala, um arquivo delimitado constitui geralmente a melhor abordagem, e o artigo sobre exportação para CSV e TSV aborda delimitadores, codificação BOM e a validação de avaliação de fórmulas relevante neste cenário
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;Escolher um ponto de partida
Se a exportação se destina a uma ferramenta VCL desktop e o formato .xls é aceitável, comece com TDataToXLS e o seu suporte para agrupamentos. Esta opção requer menos código, e a conversão com SaveXLSWorkbookAsXLSX está disponível se necessitar do formato .xlsx no futuro (respeitando os limites de conversão descritos). Se o código corre em servidor (unattended) ou o importador exige o formato .xlsx desde o início, construa o ciclo iterativo manual. Ambas as vias contam com projetos de demonstração prontos a correr no pacote do HotXLS Component