Como Gerar Ficheiros Excel em Delphi Sem Automação do Office

Se a única função de um servidor é emitir ficheiros Excel, não há qualquer razão para executar o Excel nele. Instalar o Office num agente de build ou num serviço de relatórios para o controlar através de automação COM é uma abordagem de design incorreta, e tem-no sido desde que a prática surgiu. A própria Microsoft o afirma, em diretrizes que não mudaram em vinte anos: o Office não foi concebido nem licenciado para ser automatizado a partir de processos autónomos no lado do servidor. A resposta correta é escrever os bytes BIFF e OOXML diretamente, sem qualquer presença do Excel. Essa é toda a premissa do HotXLS, uma biblioteca nativa em Object Pascal que lê e escreve os formatos de folha de cálculo autonomamente, eliminando assim aplicações de desktop que possam bloquear, ter fugas de memória ou exigir o pagamento de licenças por utilizador

Por que razão controlar o EXCEL.EXE a partir de um serviço falha

A automação COM controla remotamente um programa de desktop, e um programa de desktop pressupõe silenciosamente três coisas que um serviço Windows não lhe consegue fornecer: um perfil de utilizador carregado, uma estação de janela interativa e um humano a olhar para o ecrã. Remova estes elementos e as falhas surgirão num formato que nenhuma máquina de desenvolvimento consegue reproduzir. Uma mensagem de recuperação de ficheiro, um erro de suplemento ou um diálogo de ativação de licença abre-se num desktop que ninguém consegue ver, e a chamada de automação que o espoletou nunca retorna. O chamador acaba por sofrer um timeout e terminar; a instância do Excel frequentemente não o faz, sobrevivendo como um processo órfão que retém bloqueios de ficheiros e corrompe a execução seguinte. Qualquer pessoa que tenha visto onze processos EXCEL.EXE perdidos acumularem-se sob uma conta de serviço conhece o resto desta história

O cenário de escalabilidade não é melhor, mesmo quando nada falha. Uma instância do Excel é um pipeline de livro único, cada acesso a uma propriedade paga o custo do marshaling COM entre processos, e o computador que executa o código carrega uma licença do Office cujos termos excluem especificamente este tipo de utilização. A maioria das equipas depara-se com estes limites uma interrupção de serviço de cada vez, que é mais ou menos como a tarefa de 'eliminar a camada COM' acaba por ir parar a um plano de desenvolvimento

Antes de iniciar essa reescrita, defina uma questão de âmbito, pois ela determina que parte do trabalho é realmente útil. O código COM raramente se limita a definir valores de células. Ele chama Workbook.SaveAs com constantes de formato, força o recálculo, configura a impressão e, por vezes, acede à área de transferência (clipboard). Analise o código antigo e registe quais desses comportamentos se traduzem efetivamente em alterações no output final, uma vez que cada um deles se enquadra numa parte diferente de uma biblioteca nativa, e alguns (como a interoperabilidade com a área de transferência) não têm qualquer significado no lado do servidor e devem ser descartados em vez de migrados

Dois motores nativos, dois modelos de propriedade

O HotXLS substitui o processo do Excel por duas implementações diretas de formato. Um motor de fluxo de registo BIFF8 (TXLSWorkbook, unit lxHandle) trata do formato .xls. Um escritor de pacotes OOXML (TXLSXWorkbook, unit lxHandleX) produz ficheiros .xlsx em conformidade com as normas ECMA-376 / ISO/IEC 29500. Não há nada a registar nem a instalar no servidor, e pode manter tantos livros abertos em simultâneo quanto a memória permitir

O que costuma confundir os programadores logo no início é que as duas fachadas gerem a sua memória de forma diferente, e essa diferença é silenciosa até ocorrer uma falha crítica:

var
  Book: IXLSWorkbook;          // interface reference: released automatically
  Sheet: IXLSWorksheet;
  BookX: TXLSXWorkbook;        // plain object: you free it
  SheetX: TXLSXWorksheet;
begin
  // BIFF8 .xls output - no Free; the interface refcount owns it
  Book := TXLSWorkbook.Create;
  Sheet := Book.Sheets.Add;
  Sheet.Name := 'Report';
  Sheet.Cells.Item[1, 1].Value := 'Generated without Excel';
  Book.SaveAs('report.xls');

  // OOXML .xlsx output - explicit lifetime
  BookX := TXLSXWorkbook.Create;
  try
    SheetX := BookX.Sheets.Add('Report');
    SheetX.Cells[1, 1].Value := 'Generated without Excel';
    BookX.SaveAs('report.xlsx');
  finally
    BookX.Free;
  end;
end;

A fachada XLS é controlada por contagem de referências através da interface IXLSWorkbook. Declare a variável com o tipo de interface e nunca chame Free nela; se mantiver o mesmo objeto numa variável de objeto simples e o libertar manualmente, a contagem de referências irá libertá-lo uma segunda vez, gerando um erro. A fachada XLSX é um objeto comum que requer o habitual bloco try..finally. A indexação de células baseia-se em 1 em ambos os lados, sendo esse o único ponto de concordância. As coleções de folhas diferem: Entries no lado do XLS baseia-se em 1, enquanto o indexador Items do XLSX baseia-se em 0, e esse desvio de um compila sem qualquer aviso, independentemente de como se enganar, manifestando-se apenas em tempo de execução

Gravar um livro de cálculo diretamente numa resposta HTTP

Uma exportação do lado do servidor geralmente não tem motivos para tocar no disco. Ficheiros temporários exigem uma política de limpeza, causam colisões sob pedidos concorrentes e deixam dados de clientes alojados em volumes que ninguém pensou em auditar. Ambas as fachadas aceitam um TStream através das suas sobrecargas de SaveAs, pelo que o livro de cálculo pode ir diretamente para a resposta:

Mem := TMemoryStream.Create;
Book := TXLSXWorkbook.Create;
try
  Sheet := Book.Sheets.Add('Data');
  Sheet.Cells[1, 1].Value := 'Generated ' + DateTimeToStr(Now);
  Book.SaveAs(Mem);          // writes from the CURRENT stream position
  Mem.Position := 0;         // rewind before handing the stream over
  Response.ContentType :=
    'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
  Response.ContentStream := Mem;   // the framework now owns Mem
finally
  Book.Free;
end;

O reposicionamento é a linha que justifica o seu comentário. O SaveAs(Stream) escreve a partir da posição atual do fluxo e nunca retrocede para zero no final. Se se esquecer de Mem.Position := 0, o cliente obterá um download de zero bytes, ou o Excel reportará o ficheiro como corrompido. Este é o erro mais comum em código de folhas de cálculo exposto à web, e o mais traiçoeiro, porque passa em qualquer teste unitário que apenas valide se o fluxo tem um comprimento superior a zero

Uma única rotina de construção de livro serve todos os outros formatos de entrega sem necessidade de reestruturação. SaveAsCSV responde ao pedido de 'apenas dados brutos', SaveAsHTML trata de 'colocar numa página de portal', SaveAsRTF alimenta pipelines de documentos e SaveAsODS cumpre o mandato de OpenDocument, tudo com sobrecargas para ficheiro e fluxo (stream). Uma única rotina de exportação combinada com um parâmetro de formato substitui o que costumavam ser quatro macros COM separadas. As TXLSXHtmlExportOptions do exportador HTML incluem título, classe CSS e uma opção de fragmento ou documento completo, o que poupa o caso do portal de ter de fazer edições baseadas em regex no markup exportado

Valores de fórmulas sem um processo do Excel para os calcular

Sob automação COM, o Excel recalculava tudo gratuitamente, e abandonar o COM revoga essa facilidade de forma silenciosa. O SaveAs armazena as fórmulas como texto sem as avaliar; os números apenas surgem quando o Excel abre o ficheiro e recalcula, comportamento que a fachada XLS permite ajustar através de RecalcOnSave e CalculationMode. Para um ficheiro enviado a uma pessoa, isto é perfeitamente adequado. Mas é incorreto para um serviço que tem de confirmar um total antes do envio, e incorreto para a exportação em CSV, que escreve o texto da fórmula em vez do seu resultado. Em ambos os casos, a avaliação tem de ocorrer no servidor com o motor integrado:

SheetX.Cells[1, 1].Value := 1200;
SheetX.Cells[2, 1].Value := 950;
SheetX.Cells[3, 1].Formula := 'SUM(A1:A2)';   // XLSX facade: no '=' prefix
Total := BookX.Calculate('SUM(A1:A2)');       // evaluate on the server, now
if Total <> 2150 then
  raise Exception.Create('reconciliation failed before delivery');

A convenção de fachada causa impacto novamente aqui. O lado XLSX atribui expressões através de Cell.Formula sem o sinal de igual; o lado XLS escreve-as através de Cell.Value com um '=' no início. Transporte o código de uma para a outra sem alterações e a convenção errada armazenará uma string de texto que apenas se assemelha a uma fórmula, sem qualquer erro que a aponte. Quando as fórmulas de um livro de cálculo necessitam de aceder à sua lógica de negócio, o callback OnUserFunction permite que o motor entregue nomes de funções desconhecidos ao código Delphi no momento da avaliação. Este é o substituto nativo para os suplementos UDF que tendem a esconder-se dentro das próprias folhas de cálculo em torno das quais um sistema de automação COM se desenvolveu

Aspetos de implementação que apenas surgem no servidor

Alguns detalhes determinam se o lançamento é limpo ou problemático, sendo o primeiro o grafo de units. O exportador de datasets drag-and-drop TDataToXLS arrasta consigo as units Forms, Controls e Dialogs da VCL. Inofensivo numa ferramenta de desktop; num serviço de consola, ele arrasta toda a VCL atrás de si. As units principais lxHandle and lxHandleX utilizam apenas Windows, Classes, SysUtils e Variants, pelo que um serviço puro está melhor servido se escrever o seu próprio loop de dataset contra a API principal em vez de importar o componente por conveniência

Depois, há a questão do threading. As instâncias de livros de cálculo não são thread-safe, mas também não partilham qualquer estado global, pelo que o padrão escalável é o mais simples: um objeto de livro de cálculo por trabalho, ou por thread de trabalho. Isso permite a geração paralela de relatórios, algo que uma única instância partilhada do Excel nunca conseguiria fazer. Um manipulador de pedidos que cria, preenche, guarda e liberta o seu próprio livro não necessita de qualquer lock, e o raio de destruição de uma falha colapsa de 'a instância partilhada do Excel bloqueou para toda a gente' para 'este pedido específico gerou uma exceção', com a qual o seu tratamento de erros existente já sabe lidar

A escolha do formato é o último destes aspetos. O TXLSWorkbook.SaveAs escreve BIFF (xlExcel97) por padrão, e forçar o conteúdo XLS para .xlsx corre através da ponte de exportação SaveXLSWorkbookAsXLSX com fidelidade reduzida. Escolha a fachada com base no formato que pretende entregar, em tempo de design, em vez de construir num formato e converter no final do pipeline

Para a componente de carregamento de dados num projeto típico de substituição, os padrões de exportação de base de dados para livro de cálculo cobrem tanto o componente como o loop escrito manualmente, e assim que o número de linhas atinge os seis dígitos, as técnicas de desempenho para grandes livros de cálculo fazem a diferença entre minutos e segundos. Os relatórios criados a partir de layouts mantidos por designers são abordados no passo a passo de geração de relatórios baseada em modelos

O HotXLS é fornecido como código fonte Object Pascal para Delphi e C++Builder; edições, licenciamento e a referência completa da API encontram-se na página do Componente HotXLS