Armazenamento em Blocos e Streaming de XLSX no HotXLS

No SpreadsheetML, uma coluna que carrega um formato de moeda precisa de exatamente um elemento: <col min="2" max="2" style="3"/>. A parte 1 do ECMA-376 anexa um estilo de célula em apenas três lugares: uma coluna (§18.3.1.13), uma linha (§18.3.1.73) e uma célula (§18.3.1.4). Estreite o formato para B2:D500000 e o SpreadsheetML não tem um único elemento para a forma: um estilo de coluna passaria da linha 500000 e um estilo de linha cobriria todas as colunas, então um gerador acaba emitindo como células somente de estilo, 1.499.997 delas

Um modelo de planilha em memória esbarra na mesma parede um passo antes. Se formatar significa definir uma propriedade em cada objeto de célula, um clique no cabeçalho de coluna aloca um milhão de objetos para carregar um pequeno inteiro, e um processo Delphi que só queria um formato de número fica sem espaço de endereçamento em uma pasta de trabalho que tem poucos kilobytes no disco. A pergunta real de design é onde a formatação vive enquanto você edita, e quando ela vira dados por célula

Como o HotXLS formata um intervalo sem criar células

O HotXLS guarda a formatação de intervalo como intervalos. Todo setter de estilo no TXLSXRange — SetNumberFormat, SetNumberFormatIndex, SetFontIndex, SetFillIndex, SetBorderIndex, SetAlignmentIndex — adiciona um registro à lista StyleOverlays da planilha: quatro coordenadas, as partes de estilo que a chamada possui e os índices de pool dessas partes. Células que já existem dentro do retângulo pegam as novas partes de imediato. Coordenadas vazias continuam vazias, e nada é alocado para elas

var
  Workbook: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Edge: TXLSXBorder;
  BorderIdx, Row: Integer;
  Stats: TXLSXCellStorageStatistics;
begin
  Workbook := TXLSXWorkbook.Create;
  try
    Sheet := Workbook.Sheets.Add('Ledger');
    Sheet.WriteStringRowValues(1, 2, ['Net', 'Tax', 'Gross']);
    for Row := 2 to 1001 do
      Sheet.WriteFloatRowValues(Row, 2, [Row * 1.5, Row * 0.3, Row * 1.8]);

    // Colunas B..D por todas as 1.048.576 linhas: um intervalo por chamada
    Sheet.RCRange[1, 2, XlsxMaxRow, 4].SetNumberFormat('#,##0.00 "EUR"');

    // Constrói a borda uma vez, depois aplica por índice
    Edge := TXLSXBorder.Create;
    try
      Edge.SetAll(xlsxBorderThin);
      BorderIdx := Workbook.Borders.LookupOrAdd(Edge); // 1-based
    finally
      Edge.Free;
    end;
    Sheet.RCRange[1, 2, XlsxMaxRow, 4].SetBorderIndex(BorderIdx);

    Stats := Sheet.Cells.GetStorageStatistics;
    // Espere 3.003: três células de cabeçalho mais 3.000 valores
    WriteLn('Cells: ', Stats.CellCount);
    if Workbook.SaveAs('ledger.xlsx') <> 1 then
      raise Exception.Create('Save failed');
  finally
    Workbook.Free;
  end;
end;

As duas chamadas de formatação cobriram 3.145.728 coordenadas e não acrescentaram nada a essa contagem. Os intervalos começam na linha 1 de propósito: um intervalo de coluna inteira é salvo como um elemento <col>, e os cabeçalhos de texto na linha 1 não são afetados por um formato de número de qualquer forma. Intervalos retornados por RCRange, Range['A1:C5'], EntireColumn e EntireRow pertencem à planilha, então o exemplo nunca os libera

O TXLSXFormatParts é o que impede vários intervalos de brigar. Os seus membros são xfpFont, xfpFill, xfpBorder, xfpNumberFormat, xfpAlignment, xfpProtection e xfpCellControl, e cada setter do TXLSXRange reivindica exatamente um deles. Quando uma célula é resolvida, os intervalos que a cobrem são aplicados na ordem em que foram adicionados, cada um escrevendo apenas as suas próprias partes, e formatação explícita na própria célula vence todos eles. Um intervalo de coluna que define o formato de número e um intervalo de linha que define um preenchimento chegam ambos intactos à célula B5

Quando um único registro deve carregar várias partes, o Sheet.StyleOverlays.Add(Row1, Col1, Row2, Col2, Parts, State) recebe um TXLSXCellStyleState com os índices de pool preenchidos. O Add prende as coordenadas à grade de 1.048.576 × 16.384, troca cantos invertidos e retorna nil sem registrar nada quando Parts está vazio. Edições estruturais mantêm os registros em passo: um InsertRows dentro de um bloco formatado alarga o intervalo pela contagem inserida, e inserir acima dele desloca o registro inteiro para baixo

Por que SetBorders cria células quando SetBorderIndex não cria?

O TXLSXRange.SetBorders(Kind, Style, Color) precisa olhar cada célula por si, porque a resposta depende da posição. Com xlsxEdgeOutline, a linha de cima ganha uma borda superior, a coluna da esquerda uma borda esquerda, e o interior nada; com xlsxEdgeInside é aproximadamente o contrário. A chamada também funde as novas bordas na borda que a célula já tem, então Outline seguido de Inside constrói uma grade completa. Essa composição por célula passa pelo Cells[R, C], que cria uma célula quando não existe nenhuma. Em um bloco de relatório de 10 × 40, é exatamente o certo. Em EntireColumn(3) com xlsxEdgeAll, são 1.048.576 novos objetos de célula

Então a divisão é simples. Para qualquer coisa mais alta que uma tela de dados, construa a borda uma vez e aplique por índice, como o primeiro exemplo faz: TXLSXBorder.SetAll, depois Workbook.Borders.LookupOrAdd para um índice 1-based, depois SetBorderIndex. Deixe o SetBorders para blocos cujo contorno e linhas internas diferem, como o bloco de relatório de 10 × 40 acima

Lendo formatos de volta sem desfazer o trabalho

A mesma armadilha pega código que só lê. O Cells[R, C] é um acessador que cria ao errar: pergunte a ele o formato de número de B500000 e você recebe a resposta certa mais uma célula nova, estilizada e vazia em B500000 que agora será guardada e salva. Faça isso em loop por uma coluna para auditar formatação e você reconstruiu o milhão de objetos que o intervalo deveria evitar, uma leitura de aparência inocente por vez

O TXLSXWorksheet.GetCellVisibleFormat resolve o estilo efetivo de uma coordenada sem adicionar célula. Ele funde o padrão da planilha, intervalos de linha e coluna, intervalos de retângulo, estilos de tabela e qualquer formatação explícita na célula. A sobrecarga com um parâmetro out ABuiltinNumberFormat reporta um formato Excel embutido como 14 (data curta) separadamente do NumberFormatIndex, que aponta para o pool customizado NumberFormats da pasta de trabalho. O Cells.HasCell(R, C) responde se uma célula física existe de fato

var
  Sheet: TXLSXWorksheet;
  Before, After: TXLSXCellStorageStatistics;
  State: TXLSXCellStyleState;
  Row, Builtin, Unformatted, Physical: Integer;
begin
  Sheet := Workbook.Sheets[1];  // Sheets é 1-based
  Before := Sheet.Cells.GetStorageStatistics;
  Unformatted := 0;
  Physical := 0;
  for Row := 1 to 200000 do
  begin
    // Estilo efetivo, resolvido sem adicionar célula à planilha
    State := Sheet.GetCellVisibleFormat(Row, 2, Builtin);
    if (State.NumberFormatIndex = 0) and (Builtin = 0) then
      Inc(Unformatted);
    if Sheet.Cells.HasCell(Row, 2) then
      Inc(Physical);
    // Sheet.Cells[Row, 2].NumberFormatIndex responde a mesma pergunta,
    // mas deixa um novo objeto de célula para trás a cada linha que visita
  end;
  After := Sheet.Cells.GetStorageStatistics;
  // Ambas as contagens devem ser idênticas antes e depois do loop
  WriteLn(Format('cells %d -> %d, styled blanks %d -> %d',
    [Before.CellCount, After.CellCount,
     Before.StyledBlankCellCount, After.StyledBlankCellCount]));
end;

Rode uma checagem assim uma vez contra a sua carga de trabalho real. O StyledBlankCellCount conta células que têm estilo e nenhum valor, fórmula ou rich text. Se ele sobe durante uma passada que deveria ser somente leitura, algum helper está criando células puramente para guardar estilo, tipicamente chamando Cells[R, C] para decidir se uma célula precisa de formatação

O que os blocos de 256 linhas significam para o seu código

Células físicas vivem em blocos de 256 linhas. Dentro de um bloco, cada linha populada mantém as suas células em uma lista ordenada por coluna; uma linha sem células é só um slot vazio, e um trecho de 256 linhas sem células não tem bloco nenhum. Uma planilha esparsa com dados nas linhas 3, 7 e 512 portanto guarda dois blocos. O save percorre a mesma estrutura em ordem linha a linha, que é a ordem que o SpreadsheetML exige para elementos <row> e os seus filhos <c>

A referência TXLSXCell que você segura continua sendo o mesmo objeto enquanto outras células são adicionadas ou removidas ao redor; os blocos reorganizam a própria contabilidade, não os seus handles. Geradores em massa como WriteFloatRowValues e WriteStringRowValues tomam uma rota mais rápida em uma planilha nova e anexam valores linha a linha em um store empacotado. O primeiro acesso por célula via Cells[R, C] converte esse store em objetos de célula uma vez. O GetStorageStatistics mostra em qual estado você está via PackedStorageActive e PackedCellCount, ao lado de RowBlockCount e PopulatedRowCount

Os pools de estilo são aparados na hora de salvar. Antes de o styles.xml ser escrito, o HotXLS reconstrói os pools de fonte, preenchimento, borda, formato de número, alinhamento e proteção para que guardem apenas entradas ainda em uso, e uma pasta de trabalho que um usuário reestilizou por uma hora não embarca todo preenchimento abandonado. Células presas a um FormatIndex absoluto mantêm o seu slot intocado

Saves com streaming por linha e os números que mostram isso

O TXLSXWorkbook.StreamingWrite assume True. Com ele ligado, o SaveAs gera cada <row> da planilha e escreve direto no stream deflate da sua entrada ZIP, então o maior pedaço de XML de planilha na memória em qualquer momento é uma única linha. Com ele desligado, o XML de cada planilha é construído como uma string completa primeiro e comprimido depois, e a memória de pico segue a maior planilha. As células somente de estilo em que intervalos de retângulo viram — o caso B2:D500000 da abertura — são geradas dentro desse loop por linha também, então elas somam ao arquivo mas nunca ficam na memória como um bloco único de XML

Shared strings e registros de pivot cache seguem o mesmo padrão. Cada entrada <si> ou <r> é gerada e escrita por si, então em uma pasta analítica larga a tabela de shared strings, frequentemente maior que qualquer planilha única, nunca precisa existir como um bloco único de XML

Nada disso precisa ser tomado por fé. Depois de todo save, a pasta de trabalho expõe quatro propriedades somente leitura: LastSaveUsedStreaming, LastSavePeakRowXmlChars (a maior linha única, em caracteres), LastSaveBufferedWorksheetXmlChars (o XML total de planilha retido na memória, zero com streaming) e LastSavePeakAuxiliaryXmlChars (a maior entrada de shared string ou pivot). O LastSaveUsedStreaming é True para saída .xlsx, .xltx e .xltm e para o gerador binário .xlsb; saída OpenDocument passa pelo gerador próprio e reporta False

procedure ReportSave(Workbook: TXLSXWorkbook; const FileName: string;
  Streaming: Boolean);
begin
  Workbook.StreamingWrite := Streaming;
  if Workbook.SaveAs(FileName) <> 1 then
    raise Exception.CreateFmt('Save failed: %s', [FileName]);
  WriteLn(Format('streaming=%s peakRow=%d buffered=%d peakAux=%d',
    [BoolToStr(Workbook.LastSaveUsedStreaming, True),
     Workbook.LastSavePeakRowXmlChars,
     Workbook.LastSaveBufferedWorksheetXmlChars,
     Workbook.LastSavePeakAuxiliaryXmlChars]));
end;

// Mesma pasta de trabalho, os dois modos; observe a memória do processo no seu profiler
ReportSave(Workbook, 'ledger-stream.xlsx', True);
ReportSave(Workbook, 'ledger-buffered.xlsx', False);

Nos seus próprios dados, a linha com buffer mostra um valor buffered da ordem do XML da planilha inteira, enquanto a linha com streaming mostra zero e um peakRow não maior que a sua linha mais larga. Deixe o StreamingWrite ligado. A flag decide apenas se o XML de planilha é montado por completo antes da compressão, e o LastSaveBufferedWorksheetXmlChars reporta o tamanho dessa montagem em caracteres

Para onde vai a memória restante

Uma vez que células e estilos estão compactos, os maiores consumidores seguintes em uma pasta grande são a tabela de shared strings e as partes satélites que o arquivo carregar: pivot caches, drawings e XML preservado de partes que o modelo de objetos não modela. Esses têm alavancas próprias, e nenhuma configuração única cobre todos

Se abrir é o gargalo em vez de salvar, o carregamento seletivo é o lugar para começar: o passo a passo de carregamento de planilhas somente metadados e seletivo cobre ler uma pasta de trabalho sem pagar pelas planilhas que você não vai tocar. Para throughput no caminho de leitura em arquivos muito grandes, veja as notas sobre parsing paralelo de XLSX e o alocador de memória, e para jobs apenas de saída que nunca precisam de modelo de objetos, escritas em streaming para jobs em lote de servidor serve melhor do que qualquer quantidade de ajuste aqui

O HotXLS lê e escreve XLS e XLSX de código Delphi e C++Builder nativo sem Excel nem automação OLE, então cada número acima vem do seu próprio processo e pode ser conferido nele. Formatos suportados e versões de RAD Studio estão listados na página do componente de planilhas Delphi da HotXLS