Escrever XLSX de Milhões de Linhas em Delphi com Memória Constante

Gerar um ficheiro XLSX com centenas de milhares de linhas a partir de uma consulta de base de dados ou de um pipeline de dados coloca um problema simples: o modelo de workbook em memória acumula todos os dados antes de escrever qualquer coisa no disco. Numa exportação com um milhão de linhas isso significa manter um milhão de objetos de célula em memória simultaneamente. O TXLSDirectWriter elimina esse custo escrevendo XLSX de forma incremental — cada linha é emitida assim que os seus dados ficam disponíveis, e a memória utilizada em qualquer momento é essencialmente constante independentemente do tamanho do ficheiro de saída

A sequência de escrita

O escritor segue um protocolo de abertura-sheet-linha-fechar. Chama-se BeginFile para iniciar o ficheiro de saída, AddSheet para criar cada folha, AddRow para começar uma linha, as chamadas de valor individuais para preencher as células dessa linha, e Close quando todas as linhas estão escritas. Não há chamada de "commit de linha" — passar para AddRow novamente ou chamar Close sinaliza o fim da linha atual

var
  Writer: TXLSDirectWriter;
begin
  Writer := TXLSDirectWriter.Create;
  try
    Writer.BeginFile('export.xlsx');
    Writer.AddSheet('Sales Data');

    // Header row
    Writer.AddRow;
    Writer.WriteString('Order ID');
    Writer.WriteString('Date');
    Writer.WriteString('Amount');
    Writer.WriteString('Status');

    // Data rows — loop over your query results here
    while not Query.Eof do
    begin
      Writer.AddRow;
      Writer.WriteString(Query.FieldByName('order_id').AsString);
      Writer.WriteDateTime(Query.FieldByName('order_date').AsDateTime);
      Writer.WriteNumber(Query.FieldByName('amount').AsFloat);
      Writer.WriteString(Query.FieldByName('status').AsString);
      Query.Next;
    end;

    Writer.Close;
  finally
    Writer.Free;
  end;
end;

A sequência escreve o XML OOXML necessário diretamente num fluxo de saída comprimido. Nenhum objeto de célula, nenhum modelo de folha, nenhuma tabela de strings partilhadas é construído — os valores de cadeia de caracteres são escritos como strings em linha, que é a razão pela qual a memória permanece constante em vez de crescer com o número único de strings

Os métodos de escrita de valor

O TXLSDirectWriter expõe métodos para cada tipo de valor que o Excel pode armazenar numa célula. WriteString escreve texto. WriteNumber escreve um valor em ponto flutuante. WriteDateTime escreve um valor TDateTime, convertendo-o para o número de série do Excel e aplicando o formato de número de data padrão para que o Excel o mostre como uma data em vez de um número bruto. WriteBoolean escreve TRUE ou FALSE. WriteFormula escreve uma fórmula como texto — o Excel recalcula a fórmula quando abre o ficheiro

Writer.AddRow;
Writer.WriteString('Q4 Total');           // text cell
Writer.WriteNumber(128450.75);            // numeric cell
Writer.WriteDateTime(Now);                // date/time cell, formatted automatically
Writer.WriteBoolean(True);               // boolean TRUE
Writer.WriteFormula('=SUM(C2:C1000)');   // formula, recalculated by Excel on open

Cada método avança o cursor de coluna internamente, por isso as chamadas dentro de um AddRow produzem células consecutivas sem especificar endereços de coluna. As células são sempre escritas da esquerda para a direita dentro de uma linha, da linha de cima para a linha de baixo dentro de uma folha

Strings em linha em vez de tabela de strings partilhadas

O formato OOXML suporta dois modelos de armazenamento de strings. O modelo padrão usa uma tabela de strings partilhadas: todas as strings únicas são recolhidas num único documento XML, e as células referenciam entradas nessa tabela por índice. Isso é ótimo para compressão quando as mesmas strings aparecem muitas vezes, mas exige que a tabela inteira seja construída em memória antes de qualquer saída poder ser escrita

O TXLSDirectWriter usa strings em linha: cada valor de string é escrito diretamente no elemento de célula XML à medida que a célula é emitida. Isso remove a dependência da tabela de strings partilhadas e é o que torna a saída incremental possível. O custo é que strings repetidas são armazenadas repetidamente, portanto ficheiros onde muitas células contêm o mesmo valor de string são ligeiramente maiores do que seriam com uma tabela de strings partilhadas. Para dados de exportação típicos — IDs de pedidos, valores monetários, nomes de estado — isso raramente é uma preocupação prática, e o benefício de memória na maioria dos casos compensa amplamente

Múltiplas folhas

Chamar AddSheet várias vezes cria múltiplas folhas no workbook de saída. Cada AddSheet fecha a folha anterior e começa uma nova. Não é possível voltar a uma folha anterior uma vez que uma nova tenha começado — a saída é apenas para a frente, tal como a leitura. Para dividir uma exportação grande em folhas por mês ou por região, ordene os dados antes de iniciar a escrita e emita um AddSheet quando a chave de agrupamento mudar

var
  CurrentMonth: Integer;
begin
  Writer.BeginFile('monthly.xlsx');
  CurrentMonth := -1;

  while not Query.Eof do
  begin
    if Query.FieldByName('month').AsInteger <> CurrentMonth then
    begin
      CurrentMonth := Query.FieldByName('month').AsInteger;
      Writer.AddSheet('Month ' + IntToStr(CurrentMonth));
      // write header row here
    end;
    Writer.AddRow;
    // write cells ...
    Query.Next;
  end;

  Writer.Close;
end;

Comparação com o workbook em memória

O TXLSDirectWriter não suporta estilos de células, larguras de colunas, fórmulas que referenciam células fora da linha atual, ou qualquer funcionalidade que exija acesso aleatório às células. Para saídas que precisem delas, o TXLSFile em memória é o caminho correto. A regra prática é: use o escritor de transmissão quando os dados vêm de uma fonte sequencial (uma consulta, um ficheiro de entrada, um cursor de base de dados) e o resultado não precisa de formatação elaborada; use o workbook em memória quando constrói um relatório com estilos, gráficos ou cálculos de fórmulas que dependem de dados de células anteriores

O leitor complementar para consumir XLSX grandes sem carregar tudo na memória está descrito em transmissão de ficheiros XLSX enormes em Delphi. Ambos fazem parte do Componente HotXLS para Delphi e C++Builder