Uma planilha com um milhão de linhas e uma dúzia de colunas é uma exportação perfeitamente comum de um trabalho de relatório de banco de dados. Abra-a da maneira habitual, carregando a pasta de trabalho inteira em um TXLSWorkbook, e o processo tem que materializar cada uma dessas doze milhões de células como um objeto ativo antes de sua primeira linha de lógica de negócios ser executada. O arquivo no disco pode ter sessenta megabytes de XML comprimido. A árvore de objetos para a qual ele se expande é várias vezes isso, e tudo tem que estar residente de uma vez porque o modelo é de acesso aleatório por design. Para um relatório que você pretende ler de cima para baixo e descartar, isso é muita memória gasta em uma estrutura que você nunca precisou
Existe um segundo caminho pelo mesmo arquivo. Em vez de construir um modelo, você varre o XML da planilha somente para frente, uma célula de cada vez, e deixa cada célula passar depois de tê-la examinado. Nada se acumula. A memória permanece quase constante quer a planilha tenha mil linhas ou dez milhões, porque o leitor nunca está segurando mais do que a parte que está analisando no momento mais alguns pequenos dicionários de pesquisa. Isso é o que o leitor direto do HotXLS faz, e o restante deste artigo explica por que ele permanece pequeno e o que ele oferece em troca
Por que o modelo em memória não escala
Um arquivo XLSX é um pacote ZIP de partes XML descritas pelo ECMA-376. Cada planilha é sua própria parte, xl/worksheets/sheetN.xml, e dentro dela cada linha é um elemento <row> contendo elementos de célula <c>. O caminho de carregamento regular lê essa parte e constrói um objeto endereçável para cada célula para que você possa depois pedir Cells[12345, 7] e obter uma resposta em tempo constante. Acesso aleatório é o ponto central de um modelo de pasta de trabalho, e é exatamente o que torna a edição, avaliação de fórmulas e estilização convenientes
O custo é que o acesso aleatório exige que tudo esteja presente simultaneamente. Você não pode indexar em uma estrutura que só construiu parcialmente. Portanto, o pico de memória de um carregamento completo é uma função da contagem de células, e em uma planilha com milhões de células populadas essa função pousa em algum lugar onde seu serviço não quer estar, especialmente se vários trabalhos assim rodam ao mesmo tempo em uma máquina compartilhada. Quando o padrão de acesso que você realmente precisa é sequencial, pagar pelo acesso aleatório é pagar por uma capacidade que você não vai usar
Uma varredura SAX somente para frente que não constrói árvore
O leitor direto abre o pacote ZIP e percorre cada parte de planilha com um parser pull de estilo SAX. SAX aqui significa que o parser reporta eventos de análise à medida que os encontra, um elemento de início, uma sequência de texto, um elemento de fim, e depois segue em frente. Ele não mantém nenhuma árvore de nós atrás de si. O leitor rastreia a linha e coluna atuais a partir dos atributos r, reúne o tipo da célula, índice de estilo, valor e texto de fórmula à medida que os eventos chegam, e quando a tag de fechamento </c> é vista ele emite uma célula e a esquece. A próxima célula reutiliza o mesmo punhado de variáveis locais
Como nada é retido entre células, o consumo de memória não cresce com o número de células. Essa é a propriedade que vale a pena preservar. Uma planilha de duzentas linhas e uma de vinte milhões de linhas custam ao leitor a mesma memória residente, e a diferença entre elas é apenas quanto tempo a varredura leva. Você desiste do acesso aleatório, o recurso principal do modelo, e em troca recebe um teto de memória que a contagem de células não pode ultrapassar
O que permanece residente, e por que essas duas partes
A varredura não é completamente sem estado, e as exceções são instrutivas. Dois pequenos dicionários têm que ser mantidos na memória durante toda a varredura, porque uma célula por si só não carrega informações suficientes para ser interpretada sem eles
O primeiro é a tabela de strings compartilhadas. No SpreadsheetML, uma célula de texto não armazena seu próprio texto. Ela carrega t="s" e um payload numérico que é um índice para xl/sharedStrings.xml, uma única lista deduplicada de cada string distinta na pasta de trabalho. Esta é uma boa troca de espaço para arquivos onde os mesmos rótulos se repetem em milhares de linhas, mas significa que o leitor tem que carregar essa tabela de strings antecipadamente e mantê-la residente, porque qualquer célula em qualquer lugar em qualquer planilha pode referenciar qualquer entrada nela. A tabela é dimensionada pelo número de strings distintas, não pela contagem de células, portanto permanece modesta mesmo em planilhas enormes
O segundo é o mapeamento de formato de número da parte de estilos. Uma célula numérica e uma célula de data são idênticas byte a byte no fio: ambas são um número simples, porque uma data no SpreadsheetML é apenas uma contagem de dias seriais. A única coisa que as distingue é o estilo da célula, que aponta através de cellXfs em xl/styles.xml para um id de formato de número. Para reportar uma data como uma data em vez do número serial bruto, o leitor carrega esse dicionário de estilo para formato e o mantém residente. Todo o resto no arquivo, os dados de célula reais que compõem a maioria dos bytes, passa em streaming sem ser armazenado
Cada célula reporta um tipo e um valor
Cada célula emitida chega como um registro TXLSDirectCell. Ele carrega o índice e nome da planilha, a linha e coluna baseadas em 1, um Kind semântico, o Value como um Variant, o texto de Formula sem seu sinal de igual inicial, e o StyleIndex bruto. O tipo é um dos seguintes: xdkNumber, xdkString, xdkBoolean, xdkDate ou xdkError, para que você possa ramificar com base no que a célula significa em vez de re-derivar isso dos atributos. Uma célula de fórmula reporta o tipo de seu resultado em cache, com o texto da fórmula ao lado, para que um total calculado chegue como um número que também diz como foi produzido
type
TReportScan = class
procedure OnCell(Sender: TObject; const Cell: TXLSDirectCell;
var Abort: Boolean);
end;
procedure TReportScan.OnCell(Sender: TObject; const Cell: TXLSDirectCell;
var Abort: Boolean);
begin
case Cell.Kind of
xdkString: AccumulateLabel(Cell.Row, Cell.Col, VarToStr(Cell.Value));
xdkNumber: AddToTotals(Cell.Col, Double(Cell.Value));
xdkDate: NoteWhen(Cell.Row, VarToDateTime(Cell.Value));
xdkBoolean: FlagRow(Cell.Row, Boolean(Cell.Value));
xdkError: LogBadCell(Cell.Row, Cell.Col, VarToStr(Cell.Value));
end;
end;Distinguindo uma data de um número
A questão de data merece uma análise mais detalhada porque é onde a maioria dos scanners ingênuos erra. Não há tipo de data em uma célula numérica. Uma célula contendo o valor serial 46000 poderia ser uma quantidade, um preço, ou o dia 17 de fevereiro de 2025, e o arquivo diz qual apenas através do id de formato de número alcançado através do estilo da célula. O ECMA-376 reserva um bloco de ids de formato integrados cujo significado é fixo em todos os produtores em conformidade, e os ids com data ficam em dois intervalos: 14 a 22 para os formatos padrão de data e hora, e 45 a 47 para os formatos de tempo decorrido como [h]:mm:ss. Quando DetectDates está ativado, que é o padrão, o leitor resolve o estilo de cada célula numérica para seu id de formato, e uma célula cujo id cai nesses intervalos reservados é reportada como xdkDate com seu Value já convertido para um TDateTime do Delphi. Formatos personalizados também são verificados, inspecionando o código de formato para tokens de data e hora, mas os intervalos reservados são a espinha dorsal confiável. Desative DetectDates e a tabela de estilos nem é carregada, cada célula numérica passa como xdkNumber, e a varredura é ligeiramente mais leve
Pule planilhas e aborte cedo
A varredura sequencial tem uma vantagem silenciosa que o acesso aleatório não pode igualar: você pode parar. O evento OnSheet dispara antes de cada planilha ser aberta, e ele lhe dá dois controles. Defina SkipSheet e toda essa parte nunca é analisada, que é como você varre apenas as planilhas que lhe interessam em uma pasta de trabalho de várias planilhas sem pagar para ler o restante. Defina Abort e toda a varredura termina imediatamente. O evento OnCell carrega seu próprio Abort, para que você possa parar no momento em que encontrou o que estava procurando, uma linha específica, um valor sentinela, o fim de um bloco de cabeçalho, sem ler os milhões de células restantes. Em uma varredura somente para frente, abortar é genuinamente gratuito, porque o trabalho que você pula é trabalho que ainda não havia acontecido
procedure TReportScan.OnSheet(Sender: TObject; SheetIndex: Integer;
const SheetName: WideString; var SkipSheet: Boolean; var Abort: Boolean);
begin
// Scan only the "Data" sheet; leave the rest unread
SkipSheet := SheetName <> 'Data';
end;Contando células sem um handler
Um refinamento recente vale a pena destacar porque transforma uma pergunta comum em uma única chamada barata. O leitor conta cada célula populada que passa, e faz isso com ou sem um handler OnCell anexado. Anteriormente, sem handler definido, a contagem de células populadas retornava como zero, já que a contagem era um efeito colateral da emissão. Agora a contagem é independente da emissão. Isso significa que você pode fazer uma pergunta, quantas células populadas esta pasta de trabalho realmente contém, e obter a resposta pelo preço de uma varredura sem callbacks. ReadFile e ReadStream retornam esse total como um Int64, e o mesmo número está disponível depois como a propriedade CellCount. Um retorno de -1 sinaliza que o arquivo não pôde ser aberto ou não é um pacote OOXML
var
Reader: TXLSDirectReader;
Populated: Int64;
begin
Reader := TXLSDirectReader.Create;
try
// No OnCell handler: a pure populated-cell census, still near-constant memory
Populated := Reader.ReadFile('quarterly_export.xlsx');
if Populated < 0 then
raise Exception.Create('Not a readable XLSX package')
else
Writeln(Format('%d populated cells (CellCount = %d)',
[Populated, Reader.CellCount]));
finally
Reader.Free;
end;
end;Para a varredura completa, você anexa o handler e chama ReadFile exatamente da mesma forma. O contraste com um carregamento completo é o ponto central: onde carregar quarterly_export.xlsx em uma pasta de trabalho expandiria cada célula em um objeto residente e manteria tudo, o leitor direto mantém apenas as strings compartilhadas e a tabela de estilo enquanto as doze milhões de células passam pelo seu OnCell uma de cada vez. A aritmética executada por célula não deixa nada para trás, portanto o pico de memória é definido pela contagem de strings distintas da pasta de trabalho, não pela contagem de linhas
O leitor direto é a ferramenta certa quando o trabalho é ler uma pasta de trabalho grande uma vez e extrair ou resumir ela. Quando você precisa do acesso aleatório do modelo completo mas quer que ele se comporte em arquivos grandes, o ajuste em nossas notas sobre desempenho de pasta de trabalho grande no Delphi cobre esse caminho. E quando a direção é revertida, produzindo saída grande em vez de consumi-la, o guia do escritor de streaming para trabalhos em lote de servidor aplica a mesma disciplina de memória constante à escrita. Os três são fornecidos como parte do HotXLS Component para Delphi e C++Builder, ao lado das APIs de leitura, escrita, fórmula e formatação cobertas em outros lugares neste blog