Desempenho de Grandes Livros Excel em Delphi com HotXLS

Quando uma exportação de 300 000 linhas rebenta com o orçamento de memória, a culpa costuma ser atribuída ao número de linhas. Mas este costuma ser inocente. As partes mais dispendiosas de um livro grande são as que se criam como efeito secundário: um pool de estilos que cresce uma entrada por célula porque a formatação foi adicionada dentro do loop, o XML da folha de cálculo montado como uma única string gigante no momento de guardar, ou um milhão de corpos de fórmulas idênticos guardados um a um. O HotXLS, a biblioteca Delphi nativa da losLab para ficheiros XLS e XLSX, disponibiliza uma alavanca específica para cada um destes custos. Nenhuma delas está ativada por defeito, pois cada uma altera um trade-off, pelo que saber qual alavanca corresponde a cada sintoma é a verdadeira competência de desempenho.

Where a large workbook spends memory

Existem dois regimes de memória distintos para analisar. Durante a geração, o modelo de células em memória cresce com cada célula em que toca: valores, formatos e fórmulas tornam-se todos objetos ou entradas de pool. Durante a gravação, o caminho XLSX padrão processa adicionalmente o XML de cada folha de cálculo numa wide string antes de o comprimir no contentor zip, pelo que o pico de utilização é o modelo mais a forma serializada da maior folha. Um trabalho que sobrevive ao loop de construção e depois falha dentro de SaveAs está a atingir o segundo regime, não o primeiro, e a correção para um nada faz pelo outro.

O tamanho do ficheiro segue uma regra semelhante: as células são apenas um elemento contribuinte, a par de estilos, strings partilhadas, fórmulas, imagens e comentários. Uma passagem de auditoria com ForEachCell e as contagens de coleções por folha indica-lhe qual recurso domina realmente num ficheiro problemático antes de otimizar o aspeto errado. Uma subtileza de medição: Sheet.Cells.Count no lado do XLSX reporta o número de células instanciadas no armazenamento esparso, não a área do intervalo utilizado. Uma folha cujos dados ocupam um retângulo de 1000 por 50 com metade das células vazias conta aproximadamente 25 000, não 50 000. Essa distinção é importante quando compara um ficheiro "enorme" de um cliente com as suas fixtures, porque a área do intervalo utilizado e a população real de células podem diferir por uma ordem de grandeza em layouts financeiros esparsos.

O StreamingWrite corrige o caminho de gravação, não o de construção

Definir TXLSXWorkbook.StreamingWrite := True muda o método SaveAs para um serializador em fluxo (streaming) que escreve o XML da folha de cálculo diretamente no fluxo zip, eliminando o intermediário de string por folha. Por compatibilidade de comportamento, o padrão é False, e ativá-lo é uma alteração de apenas uma linha:

Book := TXLSXWorkbook.Create;
try
  Sheet := Book.Sheets.Add('Bulk');
  for R := 1 to 100000 do
  begin
    Sheet.Cells[R, 1].Value := R;
    Sheet.Cells[R, 2].Value := 'Row ' + IntToStr(R);
    Sheet.Cells[R, 3].Value := R * 1.5;
  end;
  Book.StreamingWrite := True;   // sheet XML streams into the zip container
  Book.SaveAs('bulk.xlsx');
finally
  Book.Free;
end;

Seja preciso sobre o que isto traz: o modelo de células construído pelo loop ocupa exatamente a mesma memória que antes. O StreamingWrite elimina o pico no momento de guardar, o que faz a diferença entre um trabalho em lote que é concluído e outro que falha na marca dos 95%. Se o próprio loop de construção esgotar a memória, as alavancas de que necessita são as duas seguintes.

Pools de estilos: adicionar uma vez, reutilizar o índice

A formatação XLSX no HotXLS é baseada em pools: Book.Fonts.Add(...), Fills.AddSolid(...) e Borders.Add(...) devolvem um índice de pool baseado em 0 que as células referenciam. Chamar Fonts.Add com parâmetros idênticos dentro de um loop é desduplicado, pelo que perde tempo mas não espaço. O Alignments.Add comporta-se de forma diferente: devolve um novo objeto por chamada, pelo que a criação de alinhamento por célula faz crescer o pool linearmente com o número de linhas. Um único hábito resolve ambos os casos: obtenha cada índice do pool uma vez, fora do loop, e atribua os índices dentro dele.

// hoist pool lookups out of the hot loop
HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);   // 0-based pool index
for C := 1 to 24 do
  Sheet.Cells[1, C].FontIndex := HeaderFont + 1;            // cells store 1-based; 0 = default

O + 1 não é um erro de digitação e esquecer-se dele é o bug clássico gerador de sintomas neste cenário: os pools distribuem índices baseados em 0, enquanto as propriedades do lado da célula tratam 0 como "padrão", pelo que cada índice de pool deve ser deslocado em uma unidade na atribuição. Se errar por omissão, os seus cabeçalhos serão renderizados silenciosamente com o tipo de letra padrão do livro de cálculo, um defeito que ninguém nota até à revisão de imagem da marca.

Substituir o tráfego de Variants por célula por callbacks de linha

Cada Sheet.Cells[R, C].Value := X envolve uma procura ou criação de célula mais uma atribuição de Variant. Ao fim de algumas centenas de milhares de células, esse custo fixo por acesso torna-se mensurável nos perfis de execução. O HotXLS fornece APIs de callback em lote em ambas as fachadas (ForEachCell e ForEachRow para leitura, WriteCells e WriteRows para escrita) que movem a iteração para dentro do motor e entregam linhas completas ao seu código de cada vez:

procedure TLedgerExport.FillRow(Sender: TObject;
  SheetIndex, Row, FirstCol, LastCol: Integer;
  var Values: Variant; var Skip: Boolean; var Cancel: Boolean);
begin
  if Row > FCount then
  begin
    Cancel := True;     // stop the whole write
    Exit;
  end;
  Values := VarArrayOf([FRows[Row - 1].Account,
                        FRows[Row - 1].PostedOn,
                        FRows[Row - 1].Amount]);
end;

// one engine call instead of hundreds of thousands of property hits
Sheet.WriteRows(1, 1, FCount, 3, FillRow);

A flag Skip do callback deixa uma linha intacta sem abortar a operação, e Cancel termina a operação mais cedo, o que é útil quando a origem é um leitor cujo comprimento descobre à medida que avança. Combine WriteRows para a construção com StreamingWrite para guardar e o caminho de geração deixará de ter pontos críticos por célula.

Alavancas do lado de leitura na fachada XLS

Grandes ficheiros legados .xls têm o seu próprio conjunto de ferramentas. _DisableGraphics := True antes de Open ignora completamente a análise da camada gráfica, o que acelera o carregamento de livros de cálculo que acumulam anos de formas geométricas e imagens incorporadas. A restrição é severa: a camada gráfica fica ausente do modelo, pelo que guardar esse livro resultará num ficheiro sem os seus desenhos. Reserve esta flag para trabalhos de análise de apenas leitura. O SetTempDir redireciona os ficheiros temporários do escritor BIFF, o que é relevante em servidores onde a localização temp padrão tem limite de quota ou reside em armazenamento lento. O UseSharedFormulas agrupa corpos de fórmulas repetidos em registos de fórmulas partilhadas, reduzindo o tamanho dos ficheiros onde uma coluna de fórmula se repete ao longo de sessenta mil linhas.

Os loops de leitura de dados XLS têm uma armadilha de indexação que vale a pena assinalar, pois duplica o trabalho se for tratada defensivamente e corrompe os resultados se passar despercebida: o UsedRange reporta os seus limites FirstRow, LastRow, FirstCol e LastCol com base em 0, enquanto Cells.Item[Row, Col] baseia-se em 1. Um varrimento que percorra o intervalo utilizado deve somar um a cada coordenada no acesso à célula, como em Cells.Item[Row + 1, Col + 1], caso contrário lerá uma grelha deslocada diagonalmente em uma célula, descartando silenciosamente a última linha e coluna e incluindo uma primeira linha/coluna fantasma. O callback ForEachCell contorna inteiramente esta discrepância, o que constitui mais uma razão para o preferir em varrimentos de folhas inteiras.

Sondar ficheiros antes de os carregar

A operação de livro grande mais barata é aquela que se evita. O GetSheetNames em ambas as fachadas lista as folhas de cálculo de um ficheiro sem carregar os dados das células. A implementação XLSX lê apenas o manifesto do livro dentro do zip e deixa explicitamente a instância do livro vazia, e a fachada XLS interrompe a leitura no primeiro limite de subfluxo (substream). Isso torna-o no teste prévio ideal para saber qual folha deve ser o alvo de um trabalho de importação, e CanReadEncrypted responde a 'este contentor está encriptado?' antes de uma tentativa fracassada de Open.

Names := TStringList.Create;
Book := TXLSXWorkbook.Create;
try
  if Book.GetSheetNames('big-unknown.xlsx', Names) <= 0 then
    raise Exception.Create('cannot enumerate sheets');   // failure clears the list
  // pick the target sheet, then decide whether a full Open is worth it
finally
  Book.Free;
  Names.Free;
end;

Note a convenção do código de retorno: estas funções de sondagem sinalizam falhas com valores iguais ou inferiores a zero e esvaziam a lista de saída, pelo que deve testar <= 0 em vez de comparar com um valor de sucesso específico.

Dimensionar a abordagem para o trabalho

Para pipelines automáticos que geram muitos ficheiros grandes em sequência, mais dois hábitos completam o cenário. Os objetos de livro de cálculo não são seguros para threads (thread-safe) para partilha, mas nada impede a existência de um livro independente por thread de trabalho, o que paraleliza a conversão em lote de forma limpa. E quando o output se destina a HTTP e não ao disco, as sobrecargas de gravação em TStream combinam-se com StreamingWrite para que uma resposta grande nunca se materialize como um ficheiro temporário. Aplica-se uma nota de rodapé operacional: a gravação em fluxo escreve a partir da posição atual sem retroceder, pelo que deve definir Position := 0 antes de entregar o fluxo à framework de resposta. O artigo sobre gravação em fluxo e trabalhos em lote detalha esse padrão do lado do servidor, e o artigo sobre exportação de base de dados mostra onde estas alavancas se encaixam num relatório orientado a conjuntos de dados.

Por fim, mantenha uma fixture de pior caso por família de relatórios e meça o seu tempo em integração contínua (CI). As regressões de desempenho na geração de documentos raramente se anunciam. Um estilo adicionado dentro de um loop ou uma sondagem substituída por um Open completo não altera nada funcionalmente, e o lote noturno demora simplesmente mais quarenta minutos. Um teste cronometrado numa fixture representativa de meio milhão de células transforma esse desvio numa build vermelha em vez de um incidente operacional.

Versões de avaliação, projetos de demonstração com um exemplo de geração em lote e a referência completa da API estão disponíveis na página do Componente HotXLS.