Um trabalho de relatório funciona bem por um ano. Ele constrói uma pasta de trabalho, preenche uma planilha com o que a consulta retorna e a salva. Então um cliente com cinco anos de histórico pede uma exportação completa, a contagem de linhas ultrapassa um milhão, e o processo morre com um erro de falta de memória muito antes do arquivo chegar ao disco. Nada estava errado com o código. Ele estava mantendo toda a pasta de trabalho na RAM para serializá-la no final, e a memória que precisava crescia no mesmo ritmo que o número de linhas que era solicitado a escrever
A correção não é uma máquina maior. É um modelo de escrita diferente. O escritor direto de streaming do HotXLS emite o pacote OOXML incrementalmente à medida que as linhas chegam, portanto a memória que usa não depende de quantas linhas você escreve. É a contraparte do lado de escrita para o leitor de streaming: onde o leitor percorre uma planilha enorme sem construir uma árvore de células, o escritor produz uma sem construir uma árvore de células também
Por que o caminho de salvamento normal cresce com os dados
O caminho regular TXLSXWorkbook constrói primeiro um modelo de objeto completo. Cada célula, com seu valor, tipo e referência de estilo, vive como um objeto na memória até você chamar salvar, ponto em que toda a árvore é serializada no pacote. Esse modelo é o correto quando você quer ler uma planilha, editá-la, recalculá-la e escrevê-la de volta, porque o acesso aleatório a qualquer célula é exatamente o que a edição precisa. É o errado quando você está despejando linhas em uma direção e nunca olhando para trás, porque você paga para manter cada linha residente sem nenhum benefício. Um milhão de linhas de objetos é um milhão de linhas de objetos, quer você as revisite ou não
O escritor de streaming remove a árvore. Assim que uma célula é escrita ela se torna bytes na parte da planilha, e esses bytes são entregues à saída zip. O stream da planilha é o único buffer que cresce, e ele cresce no lado de saída, não como objetos Delphi ativos no heap. O que permanece residente é uma quantidade fixa de contabilidade: os nomes das planilhas, alguns flags, o número da linha atual, um contador de células. Esse conjunto não muda entre a linha um e a linha dez milhões
A tabela de strings compartilhadas é a armadilha, e strings inline são a saída
A maioria dos escritores XLSX de streaming se sai bem até encontrar texto. O formato OOXML normalmente armazena strings em uma tabela de strings compartilhadas: cada string distinta é escrita uma vez em uma parte separada, e cada célula que contém essa string carrega um índice na tabela em vez do texto. É uma boa otimização de espaço para arquivos cheios de rótulos repetidos, e é o padrão que o caminho de salvamento regular usa. O problema para um escritor de streaming é brutal. Para deduplicar, a tabela tem que permanecer residente durante todo o trabalho, porque qualquer linha ainda por vir pode repetir uma string de uma linha já escrita, e apenas um mapa completo em memória de strings vistas pode atribuir o índice correto. Portanto, a única estrutura que um escritor de streaming não pode transmitir em streaming é exatamente a estrutura que deveria tornar o arquivo pequeno. Dados com muito texto derrotam o streaming para o qual você veio
O escritor direto contorna a tabela completamente. Strings são escritas inline, como células t="inlineStr" cujo texto fica diretamente dentro da célula com um elemento <is><t>. Não há tabela para acumular e nenhum mapa de strings vistas para manter, portanto colunas de texto não custam mais memória do que colunas numéricas. A troca é explícita e vale a pena declarar claramente. Strings inline repetem o mesmo texto onde quer que ocorra, portanto um arquivo com muitos rótulos idênticos é maior no disco do que o equivalente com strings compartilhadas. Você gasta tamanho de arquivo para comprar memória constante. Para uma exportação de uma passagem essa é a troca certa, e a compressão zip absorbe grande parte da repetição na saída de qualquer forma
A tabela de estilos chega no final, com um formato de data
Os estilos apresentam a mesma tensão que as strings. Uma pasta de trabalho referencia sua formatação através de uma parte de estilos, e um escritor de streaming não pode manter uma paleta crescente de estilos em sincronia com células que já foram liberadas. O escritor direto responde a isso mantendo a tabela de estilos pequena e fixa, e emitindo-a no fechamento em vez de antecipadamente. Um formato de célula padrão cobre células comuns. Um formato de número de data cobre datas, registrado com um código de formato de yyyy-mm-dd em uma posição conhecida na lista de formatos de célula
Esse formato de data é a razão pela qual WriteDateTime existe como sua própria chamada. O Excel não tem tipo de data nativo; uma data é um número usando um formato de data. WriteDateTime escreve o valor como um número serial simples e marca a célula com o único estilo de data, para que a planilha o renderize como uma data em vez de um inteiro de cinco dígitos. O serial que escreve importa para round-tripping. Ele armazena o valor TDateTime diretamente sob o sistema de datas de 1900, que é a mesma convenção que o caminho de salvamento regular TXLSXWorkbook usa. Como ambos os caminhos concordam no serial, um arquivo que o escritor de streaming produz lê de volta através do leitor HotXLS e abre no Excel com datas que correspondem ao que você pretendia, sem deslocamento de um dia ou surpresa de época entre o escritor e o leitor
A ordem é obrigatória, porque os bytes já foram
O streaming compra seu perfil de memória com uma regra que você tem que respeitar. A saída é emitida conforme você vai e não pode ser revisitada, portanto tudo deve ser escrito na ordem em que aparece no arquivo. Dentro de uma linha, as células vão em ordem ascendente de coluna. Dentro de uma planilha, as linhas vão em ordem ascendente. Não há buffer que permita ao escritor ordenar suas células depois do fato, porque a linha que você fechou há pouco já são bytes no stream zip e não é mais alcançável. Passe a coluna 5 e depois a coluna 2 na mesma linha e a saída está malformada, já que o escritor simplesmente emite o que você lhe dá na sequência em que você o dá
A API de linha tem uma pequena conveniência para o caso comum. AddRow recebe um índice de linha baseado em 1, mas passar 0 significa tomar a próxima linha após a anterior, portanto um preenchimento sequencial não precisa rastrear e passar um contador incrementando. Cada AddRow fecha a linha antes dela, e cada AddSheet fecha a planilha antes dela, portanto você nunca explicitamente termina uma linha ou uma planilha. Você começa a próxima e o escritor finaliza a estrutura aberta para você
O escape é tratado onde o texto entra no XML
Qualquer texto que você escreve se torna parte de um documento XML, portanto as cinco entidades XML predefinidas devem ser escapadas ou o pacote é inválido no momento em que um valor contém um e comercial ou um colchete angular. O escritor escapa &, <, >, " e ' para você tanto no texto de string inline quanto no texto de fórmula, os dois lugares onde caracteres fornecidos pelo chamador aparecem dentro da marcação. Você passa uma WideString bruta e o escritor a torna segura. Um nome de produto como Smith & Co <Ltd> ou uma fórmula referenciando um nome de planilha entre aspas sai como XML bem formado sem nenhum escape da sua parte
Ciclo de vida, e por que Destroy ainda fecha
Finalizar o pacote é o que escreve a parte da pasta de trabalho, a parte de estilos, as partes de tipos de conteúdo e relacionamento, e finalmente o diretório central do zip. Esse trabalho acontece em Close. Um pacote que nunca é fechado é um zip incompleto que nenhum programa de planilha abrirá, portanto fechar não é limpeza opcional, é o passo que torna o arquivo válido. Para proteger contra um Close esquecido em um caminho de erro, Destroy realiza um fechamento de melhor esforço se o pacote ainda estiver aberto, portanto liberar o escritor não vaza o objeto zip subjacente mesmo quando uma exceção pulou a chamada explícita. O padrão confiável ainda é o Delphi comum: escreva dentro de um try, chame Close e libere no finally
Transmitindo uma planilha grande do início ao fim
A forma do trabalho é: iniciar, adicionar uma planilha, derramar linhas, fechar. O exemplo abaixo escreve uma linha de cabeçalho e depois uma longa sequência de linhas de dados tipados, misturando strings, números, uma fórmula sem resultado em cache e uma data. A memória que usa para dez linhas e para dez milhões de linhas é a mesma, porque cada célula parte para o stream zip assim que é escrita
uses
lxDirectWrite;
procedure StreamReport(const Path: string; RowCount: Integer);
var
W: TXLSDirectWriter;
I: Integer;
begin
W := TXLSDirectWriter.Create;
try
W.BeginFile(Path);
W.AddSheet('Sales');
// Header row, written in ascending column order
W.AddRow(1);
W.WriteString(1, 'Item');
W.WriteString(2, 'Qty');
W.WriteString(3, 'Price');
W.WriteString(4, 'Total');
W.WriteString(5, 'Date');
// Data rows; pass 0 to AddRow to take the next row automatically
for I := 1 to RowCount do
begin
W.AddRow(0);
W.WriteString(1, 'Item ' + IntToStr(I));
W.WriteNumber(2, I);
W.WriteNumber(3, 1.5 + (I mod 10));
W.WriteFormula(4, Format('B%d*C%d', [I + 1, I + 1]));
W.WriteDateTime(5, EncodeDate(2026, 1, 1) + I);
end;
W.Close; // finalises the package
finally
W.Free;
end;
end;Uma segunda planilha é simplesmente outro AddSheet antes de continuar, e o escritor fecha a primeira planilha ao abrir a segunda. Flags booleanos usam WriteBoolean, que escreve uma célula booleana tipada em vez do texto "True". Se você quiser confirmar que o arquivo está correto e faz round-trip, a propriedade CellCount reporta quantas células foram escritas, e ler o resultado de volta com o leitor de streaming deve reportar o mesmo total
// A second sheet of typed flags after the data sheet above
W.AddSheet('Flags');
W.AddRow(1);
W.WriteString(1, 'Name');
W.WriteString(2, 'Active');
W.AddRow(0);
W.WriteString(1, 'alpha');
W.WriteBoolean(2, True);
WriteLn(Format('wrote %d cells', [W.CellCount]));Escrever para um stream em vez de um arquivo é o mesmo código com BeginStream no lugar de BeginFile, o que permite que um servidor envie a pasta de trabalho para uma resposta HTTP ou um stream de memória sem um arquivo temporário no disco. O escritor não possui o stream que você passa, portanto você mantém o controle do seu tempo de vida
Quando o trabalho é um endpoint de servidor que constrói pastas de trabalho sob demanda, os padrões em escritas de streaming para trabalhos de servidor e em lote mostram como conectar isso a um manipulador de solicitação e uma exportação programada. Quando a questão é o custo mais amplo de pastas de trabalho muito grandes, tanto leitura quanto escrita, desempenho de pasta de trabalho grande no Delphi cobre onde o tempo e a memória realmente vão. O escritor direto de streaming é fornecido como parte do HotXLS Component para Delphi e C++Builder, ao lado das APIs completas de leitura, edição e salvamento cobertas em outros lugares neste blog