Uma pasta de trabalho financeira mantida à mão acumula caches de tabela dinâmica, slicers, formatos condicionais e anos de estilos de célula, mas o job noturno só precisa trocar os números de uma aba de dados. Um load e save completos interpretam, reconstroem e comprimem de novo cada parte. Reescrever só aquela parte de planilha e carregar todas as outras entradas do zip intocadas pula quase todo esse trabalho
O que torna isso possível é o próprio formato de pacote. Um arquivo .xlsx é um arquivo zip de partes XML organizadas pelas Open Packaging Conventions (ECMA-376 Parte 2), e cada entrada do zip registra seu próprio método de compressão, CRC-32 e tamanho comprimido no cabeçalho local e de novo no diretório central. Uma entrada pode, portanto, passar de um arquivo para outro sem nunca ser descomprimida, desde que quem copia escreva esses três valores junto com os bytes brutos. O cache de tabela dinâmica não precisa ser entendido para ser preservado. Só precisa ser copiado
Por que copiar bytes comprimidos em vez de salvar a pasta de trabalho de novo?
Copiar no nível comprimido transforma quase todo o patch em uma cópia de stream simples. No HotXLS, o TXLSDirectWriter.BeginPatch abre o pacote de origem somente leitura e entrega à TZipArchive.CopyCompressedFrom cada entrada que o patch deixa em paz, e ela escreve os bytes comprimidos, o CRC e os tamanhos da entrada de origem direto no arquivo de destino. Um caminho de escrita convencional paga um inflate na entrada e um deflate na saída, e o deflate é a metade cara. Em uma pasta de trabalho cujo volume é um cache de tabela dinâmica grande mais algumas dezenas de imagens embutidas, o tempo vai para a planilha que você está escrevendo de fato
A cópia bruta cobre entradas guardadas sem compressão (método zip 0) e entradas deflate (método 8). Uma entrada que usa outro método de compressão continua sendo carregada: o writer a lê através de um stream descompressor e a grava de volta, então a parte chega ao destino de qualquer forma. Entradas de marcador de diretório não guardam conteúdo e são puladas, já que o writer produz as dele. Um punhado de entradas nunca é copiado, porque é regenerado ou editado: a planilha alvo e a parte _rels dela, xl/calcChain.xml, xl/_rels/workbook.xml.rels e [Content_Types].xml, mais xl/workbook.xml quando uma planilha é inserida
Substituindo uma planilha in-place com BeginPatch
A sobrecarga de dois argumentos BeginPatch(FileName, SheetName) substitui uma planilha existente e grava o resultado de volta no mesmo caminho. Ela resolve o nome pelo registro de planilhas no xl/workbook.xml e pelo mapa de relationships que liga cada planilha à sua parte, compara nomes sem distinguir maiúsculas, e levanta uma exceção contendo "worksheet not found" quando nada bate. A partir daí a planilha é construída com as mesmas chamadas AddSheet, AddRow e Write* que o writer em streaming usa para arquivos novos
var
W: TXLSDirectWriter;
begin
W := TXLSDirectWriter.Create;
try
W.BeginPatch('monthly-dashboard.xlsx', 'Data'); // substitui in-place
W.AddSheet('Data'); // o nome é apenas informativo em modo patch
W.AddRow(1);
W.WriteString(1, 'Region');
W.WriteString(2, 'Revenue');
W.AddRow(2);
W.WriteString(1, 'North');
W.WriteNumber(2, 184320.55);
W.AddRow(3);
W.WriteString(1, 'South');
W.WriteNumber(2, 97410.20);
W.AddRow(4);
W.WriteString(1, 'Total');
W.WriteFormula(2, 'SUM(B2:B3)'); // sem '=' inicial
W.Close;
finally
W.Free;
end;
end;
Dois detalhes dessa listagem são fáceis de ler errado. O nome passado ao AddSheet não renomeia nada: a aba mantém o nome, o sheetId e a posição que já tem no xl/workbook.xml, então AddSheet('Totals') ainda escreveria em Data. E um segundo AddSheet levanta exceção, porque um patch escreve exatamente uma planilha. Atualizar três abas significa três patches, cada um copiando o pacote mais uma vez
A fórmula entra sem o sinal de igual. O WriteFormula coloca o texto diretamente no elemento <f> da célula, e a ECMA-376 Parte 1 §18.3.1.40 guarda fórmulas ali sem o "=" que o Excel mostra na barra de fórmulas; escrever '=SUM(B2:B3)' coloca um sinal de igual literal no XML. O WriteFormula também não emite resultado em cache. Se algo a jusante lê valores em cache em vez de recalcular, o WriteFormulaNumber escreve a fórmula junto com o número que você já sabe que ela avalia
A sobrecarga in-place nunca escreve no original enquanto trabalha. O pacote corrigido é construído em um arquivo irmão nomeado a partir da origem com .hxlpatch.tmp anexado. No Close o handle da origem é liberado e o arquivo preparado substitui o original por um único rename. Se esse rename falhar, o Close levanta uma exceção cuja mensagem cita os dois caminhos, mantém a cópia preparada e deixa o original onde estava. Um processo morto antes do Close deixa o original intacto e um .hxlpatch.tmp perdido ao lado, que o próximo BeginPatch nesse arquivo apaga antes de começar
Anexando uma nova planilha a uma cópia do template
A sobrecarga de quatro argumentos BeginPatch(SourceFileName, TargetFileName, SheetName, InsertSheet) deixa o arquivo de origem em paz e grava um pacote novo. Com InsertSheet = False ela substitui a planilha nomeada na cópia; com True anexa uma nova planilha sob esse nome e levanta "worksheet already exists" se o template já tiver uma aba assim
// template.xlsx fica intocado; output.xlsx ganha uma aba extra
W.BeginPatch('template.xlsx', 'output.xlsx', 'Extra', True);
W.AddSheet('Extra');
W.AddRow(1);
W.WriteString(1, 'Appended by the nightly job');
W.Close;
A inserção é onde a contabilidade do pacote é editada. O writer varre as entradas xl/worksheets/sheetN.xml existentes atrás do maior número de parte, os elementos <sheet> atrás do maior sheetId, e as relationships da pasta de trabalho atrás do maior rId, e então toma o próximo valor de cada. O novo elemento <sheet> entra pouco antes do </sheets>, então a aba nova aparece por último. O atributo de relationship reutiliza o prefixo que a pasta de trabalho de origem usa para planilhas, que normalmente é r:id mas não é obrigatório ser
Os tipos de relationship também seguem o pacote de origem. Quando as relationships da pasta de trabalho já usam os namespaces ISO/IEC 29500 Strict sob purl.oclc.org, a relationship da nova planilha é gravada com a URI de tipo Strict; uma pasta de trabalho Transitional recebe a Transitional. Uma pasta de trabalho Strict corrigida, portanto, não acaba com uma relationship Transitional no meio das Strict
Estilos, strings e as partes que um patch deixa em paz
Dentro de um patch, tudo a que a nova planilha aponta já precisa existir no pacote de origem. Strings são portanto gravadas inline na planilha em vez de no xl/sharedStrings.xml, porque a tabela de shared strings atravessa byte a byte e não pode crescer. Definir UseSharedStrings como True faz o AddSheet levantar exceção em modo patch
A mesma regra muda o que StyleIndex significa. Em um patch é uma posição na lista cellXfs da pasta de trabalho de origem (ECMA-376 Parte 1 §18.8.10), e o writer só confere se está entre 0 e 65534. Ele não pode dizer se o índice 14 existe naquele arquivo em particular, então um número errado vai direto para a saída. O jeito mais confiável de achar o certo é descompactar a versão atual da planilha e ler o atributo s de uma célula que já carrega o formato que você quer; esse número é a posição no cellXfs. O Excel pode adicionar entradas a essa lista sempre que alguém formata células no template, então consulte o índice de novo depois de cada edição do template em vez de confiar em uma constante escrita há um ano
const
// posição no cellXfs de um formato de data NESTE template, tirada do
// atributo s="..." de uma célula de data existente; reconfira após editar o template
TemplateDateXf = 5;
begin
...
W.AddRow(5);
W.WriteString(1, 'Refreshed');
W.WriteDateTime(2, Now, TemplateDateXf); // índice explícito: aceito
// W.WriteDateTime(2, Now); levanta exceção em modo patch: o estilo de data implícito dele
// vive em uma parte de styles que o patch nunca grava
end;
Tudo o que adicionaria partes novas ao pacote é recusado. AddTable, AddChart, a família AddImage, AddComment, AddDefinedName e AddCellStyle levantam exceção assim que chamados, e o Close confere de novo antes de commitar, o que também pega formatos condicionais que definem preenchimento, cor de fonte, negrito ou itálico próprios, já que esses precisam de entradas novas no styles.xml. Uma chamada recusada é melhor que um pacote em que a planilha referencia uma parte de tabela que ninguém registrou
O modo replace também começa a planilha do zero. A parte de relationships da planilha antiga não é copiada, então um drawing, comentários ou hyperlinks externos ligados à versão anterior não ficam ligados à nova, e larguras de coluna, painéis congelados, intervalos mesclados e validação de dados guardados no XML da planilha antiga vão embora também. Os que vivem no XML da planilha podem ser escritos de novo com SetColumnWidth, FreezePanes, MergeCells, AddDataValidation e AddHyperlink. Um gráfico ou imagem ancorado na aba de dados não pode voltar por um patch, então mantenha gráficos em uma planilha própria e aponte as séries deles para a aba de dados
A cadeia de cálculo é descartada nos dois modos. O xl/calcChain.xml lista células com fórmula na ordem em que o Excel as avaliou por último; depois de um replace as entradas dele descrevem células que não existem mais naquela forma, e depois de um insert os índices de planilha a que se refere mudaram de posição. A parte fica de fora da cópia, e a relationship dela e o Override dela no [Content_Types].xml são removidos, para o pacote não referenciar uma parte ausente. O Excel reconstrói a cadeia na próxima vez que recalcular
Quando o laço de linhas levanta exceção no meio do caminho
O destrutor do TXLSDirectWriter finaliza o pacote quando o Close nunca foi chamado, então um Close esquecido não deixa um zip pela metade jogado por aí. Como ele finaliza o que quer que o writer segure, uma exceção que se propaga pelo Free sob a sobrecarga in-place ainda commita as linhas escritas até ali e as troca sobre o original. Um laço que busca e grava linhas, levanta exceção no meio e libera o writer em um bloco finally termina exatamente assim
Para qualquer coisa que roda sem supervisão, escreva para um alvo explícito, abra o resultado, confira, e só então substitua o arquivo em produção você mesmo. Uma exceção do laço de linhas ainda deixa o Free finalizar o arquivo preparado, mas também pula a troca, então a pasta de trabalho em produção mantém os números de ontem em vez de metade dos de hoje
uses
Windows, SysUtils, Variants, lxDirectWrite, lxHandleX;
procedure RefreshDashboard;
const
Live = 'monthly-dashboard.xlsx';
Staged = 'monthly-dashboard.next.xlsx';
var
W: TXLSDirectWriter;
Check: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
W := TXLSDirectWriter.Create;
try
W.BeginPatch(Live, Staged, 'Data', False);
W.AddSheet('Data');
WriteRefreshedRows(W); // seu próprio laço de linhas, não faz parte do HotXLS
W.Close;
finally
W.Free;
end;
Check := TXLSXWorkbook.Create;
try
if Check.Open(Staged) <> 1 then
raise Exception.Create('Staged workbook does not open');
Sheet := Check.Sheets.SheetByName('Data');
if (Sheet = nil) or (VarToStr(Sheet.Cells[2, 1].Value) <> 'North') then
raise Exception.Create('Data sheet did not come back as written');
finally
Check.Free;
end;
// API do Windows, não HotXLS: substitua o arquivo em produção só depois das checagens
if not MoveFileEx(PChar(Staged), PChar(Live), MOVEFILE_REPLACE_EXISTING) then
RaiseLastOSError;
end;
O TXLSXWorkbook.Open retorna 1 em caso de sucesso, e tanto Sheets quanto Cells têm base 1: Check.Sheets[1] é a primeira aba e Cells[2, 1] é A2. Além de um valor pontual, duas checagens estruturais pegam a maioria dos erros. O Check.Sheets.Count deve ser igual à contagem do template depois de um replace e um a mais depois de um insert, e uma listagem do arquivo de qualquer ferramenta zip não deve mostrar xl/calcChain.xml nem nome de entrada que aparece duas vezes. Nomes duplicados importam porque alguns leitores pegam a última entrada com um dado nome, o que faz duas planilhas parecerem com conteúdos trocados
Patch, streaming ou carregar a pasta de trabalho?
O patch serve quando a pasta de trabalho é grande, a mudança fica confinada a uma planilha e o resto do arquivo precisa sobreviver bit a bit. É a ferramenta errada quando a mudança atravessa várias planilhas, quando os dados novos precisam de formatos que o template ainda não define, quando gráficos ou tabelas precisam ser criados, ou quando o arquivo é pequeno o bastante para um load e save normais custar nada que valha medição. Para geração em massa do zero, o writer direto em streaming é o mais adequado, e como ele compartilha as mesmas chamadas AddRow e Write*, mover código entre os dois é na maior parte trocar BeginFile por BeginPatch
Quando o job realmente precisa do modelo de objetos completo, trabalho em nível de planilha dentro de uma pasta de trabalho carregada é coberto em duplicação de planilhas em pacotes XLSX. E se o patch está na mesa só porque processar a pasta de trabalho inteira ficou lento, o artigo sobre desempenho com pastas de trabalho grandes olha para onde vai de fato o tempo e a memória de load e save antes de você se comprometer com uma abordagem
O writer de patch, o writer em streaming e o modelo de objetos completo TXLSXWorkbook vêm todos em uma única biblioteca para Delphi e C++Builder; edições e a lista completa de recursos estão na página do componente de planilhas HotXLS para Delphi