Transmitir Ficheiros XLSX Enormes em Delphi sem os Carregar

Um ficheiro XLSX com um milhão de linhas não cabe comodamente na memória como um workbook em memória. Carregar um ficheiro assim com o leitor de workbook padrão requer que toda a folha de cálculo seja descomprimida, analisada e mantida como objetos até o processamento estar concluído. Para ficheiros de exportação grandes, feeds de dados ou relatórios em lote onde o código precisa de fazer uma única passagem para a frente sobre o conteúdo, essa abordagem é tanto mais lenta quanto requer muito mais memória do que é necessário. O TXLSDirectReader trata desse caso com uma digitalização SAX de passagem única e memória quase constante

Como funciona a transmissão

Um ficheiro XLSX é um arquivo ZIP que contém documentos XML. A parte principal dos dados de uma folha está em xl/worksheets/sheet1.xml e assim por diante. O TXLSDirectReader abre o arquivo, seleciona os fluxos de folha, e percorre o XML com um analisador SAX. Em vez de construir uma árvore de objetos, lança eventos à medida que encontra cada linha e célula. A sua aplicação liga-se a esses eventos e processa cada célula à medida que chega, depois descarta-a. A pegada de memória em qualquer momento é a da célula atual mais a sobrecarga do analisador, não proporcional ao tamanho do ficheiro

O custo é que o acesso aleatório não está disponível. Não se pode solicitar a célula na linha 500 000 sem primeiro passar pelas linhas 1 a 499 999. Para a maioria dos casos de uso de processamento em lote — validar dados, calcular totais, transformar para outro formato, importar para uma base de dados — isso é exatamente a troca certa

Os eventos OnCell e OnSheet

O TXLSDirectReader expõe dois eventos. OnSheet é disparado quando o leitor encontra o início de uma folha, fornecendo o índice e o nome da folha. OnCell é disparado para cada célula, passando um objeto TXLSDirectCell que contém o endereço da célula (linha e coluna), o tipo de valor, o valor como cadeia de caracteres, e um valor tipado convenientemente quando o tipo é conhecido

var
  Reader: TXLSDirectReader;
begin
  Reader := TXLSDirectReader.Create;
  try
    Reader.OnSheet := procedure(Sender: TObject; SheetIndex: Integer; const SheetName: WideString)
    begin
      WriteLn('Sheet: ', SheetName);
    end;

    Reader.OnCell := procedure(Sender: TObject; Cell: TXLSDirectCell)
    begin
      // Cell.Row, Cell.Col are 1-based
      // Cell.ValueType indicates string, number, boolean, formula result, etc.
      // Cell.Value is always the string representation
      WriteLn(Cell.Row, ',', Cell.Col, ': ', Cell.Value);
    end;

    Reader.Open('large-export.xlsx');
    Reader.Read;
  finally
    Reader.Free;
  end;
end;

O código chamador não gere alocações de objetos de células ou a libertação de memória — a mesma instância TXLSDirectCell é reutilizada para cada evento, o que é outra razão pela qual o consumo de memória permanece estável ao longo de um ficheiro de qualquer dimensão

Deteção de datas

O XLSX armazena datas como números de série em ponto flutuante. Um ficheiro exportado de Excel contém datas armazenadas como, por exemplo, 45291.5, onde a parte inteira é o número de dias desde 1 de janeiro de 1900 e a parte decimal é a fração do dia. Sem o contexto do estilo aplicado a essa célula, um leitor de transmissão não pode distinguir esse número de um valor numérico ordinário

A propriedade DetectDates instrui o leitor a inspecionar as informações de estilo de célula para determinar se um valor numérico é uma data. Quando DetectDates está ativo, o campo ValueType de um TXLSDirectCell reporta xlsCellDate para essas células, e a propriedade DateTimeValue devolve o valor TDateTime convertido. Sem ele, todos os números chegam como xlsCellNumber, que é adequado quando a folha não contém datas ou quando o código downstream trata a conversão de data ele próprio

Reader.DetectDates := True;

Reader.OnCell := procedure(Sender: TObject; Cell: TXLSDirectCell)
begin
  if Cell.ValueType = xlsCellDate then
    WriteLn(FormatDateTime('yyyy-mm-dd', Cell.DateTimeValue))
  else
    WriteLn(Cell.Value);
end;

Contar células antes de processar

Algumas aplicações precisam de saber quantas células de dados uma folha contém antes de iniciar o processamento — para alocar um buffer de destino, atualizar uma barra de progresso ou validar que o ficheiro tem o número esperado de linhas. A propriedade CellCount suporta isso. Definir CellCount para True faz com que o leitor faça uma primeira passagem rápida que conta as células sem disparar eventos OnCell, depois a passagem de processamento real segue-se. O custo é uma segunda passagem no ficheiro, que é tipicamente aceitável quando o número de células é necessário antecipadamente

Reader.CellCount := True;
Reader.Open('large-export.xlsx');
// After Open, TotalCells is available per sheet
WriteLn('Total cells in sheet 1: ', Reader.SheetCellCount[1]);
Reader.Read;  // now fire OnCell events

Quando usar a transmissão versus o carregamento em memória

O TXLSDirectReader é a escolha certa quando o ficheiro é grande e o padrão de acesso é sequencial. Importações de base de dados, validações de dados, transformações ETL e pipelines de relatório que percorrem cada linha uma vez enquadram-se todos nesta categoria. O leitor de workbook em memória TXLSFile é melhor quando o código precisa de acesso aleatório, reavaliação de fórmulas, modificação do workbook, ou quando o tamanho do ficheiro é gerível em memória. A maioria das aplicações usa os dois: o leitor de transmissão para ingestão e o workbook em memória para edição interativa ou relatórios mais pequenos

A escrita em modo de transmissão para grandes ficheiros de saída é o outro lado da mesma moeda, descrita em escrita XLSX em modo de transmissão em Delphi. Ambas as funcionalidades fazem parte do Componente HotXLS para Delphi e C++Builder