O HotXLS, a biblioteca Excel nativa para Delphi e C++Builder, grava um livro .xls BIFF8 clássico com prioridade à cache: o TXLSWorksheet.WriteFormula pede ao TXLSWorkbook.TryGetCachedFormulaValue o valor que o Excel guardou ao lado de cada fórmula e só chama o avaliador quando essa cache falta ou foi invalidada. Um livro que abriu e nunca tocou volta a gravar os mesmos números, e os resultados frescos exigem uma chamada explícita a Recalculate em vez de serem um efeito secundário escondido do SaveAs
O bug que obrigou a expor este contrato era embaraçosamente pequeno. Um ficheiro do corpus chamado nested-subtotals.xls tem um total geral em R2C4 cujo valor em cache é 37. Abra-o com o HotXLS, peça TryGetCachedFormulaValue para a célula, obtenha 37. Grave-o sem alterar uma única célula, abra a cópia gravada, faça a mesma pergunta, obtenha 67. Nada na API tinha sido mandado calcular coisa nenhuma, e no entanto um número no ficheiro tinha-se deslocado exatamente 30 — e 30 calha a ser a soma dos dois subtotais de grupo, 10 e 20, que estão dentro do intervalo que o total geral cobre
Porque é que gravar um ficheiro XLS altera o valor de uma fórmula?
Era preciso que dois defeitos independentes se alinhassem para que aquele 37 se tornasse 67, e corrigir apenas um deles teria escondido o outro. O primeiro era estrutural: o writer clássico recalculava todas as fórmulas em todas as gravações. O segundo era uma verificação de tipo que nunca podia ser verdadeira para uma fórmula carregada do disco, o que fazia o avaliador contar as células SUBTOTAL aninhadas duas vezes. O ficheiro do corpus foi simplesmente a primeira entrada em que um recálculo no momento de gravar produziu uma resposta diferente da do Excel e alguém comparou as duas. O defeito estrutural é fácil de enunciar: antes da v2.382.3, o TXLSWorksheet.WriteFormula e o seu irmão de fórmulas partilhadas WriteFormulaWithTExp obtinham o campo FormulaValue de oito bytes de cada registo Formula chamando TXLSWorkbook.GetFormulaValue, que é o avaliador. A cache que o ParseFormula tinha descodificado cuidadosamente do ficheiro de origem ao carregar nunca era consultada à saída. Na prática, cada gravação era um recálculo completo com a API de recálculo ao nível do livro contornada, pelo que nada que pudesse definir no livro o teria impedido. Qualquer ponto em que o avaliador do HotXLS discordasse do Excel, fosse uma função legitimamente não suportada ou um bug simples, tornava-se uma alteração silenciosa de dados ao gravar
O segundo defeito vivia no callback de subtotais aninhados que o avaliador usa. O Excel define todas as formas de SUBTOTAL como ignorando células cuja própria fórmula seja outro SUBTOTAL, pelo que o calculator em lxCalc.pas arma o FIgnoreSubtotalCells durante a agregação e pergunta ao livro, através do TXLSWorkbook.GetClassicIsSubtotalCell, se cada célula do intervalo é uma delas. Esse callback ia buscar o texto da fórmula como Variant e testava-o com VarType(f) = varOleStr. O texto volta do GetUnCompiledFormula como um String de Delphi, e um String atribuído a um Variant é varUString, nunca varOleStr. O predicado era falso para todas as células de todos os ficheiros carregados, os subtotais de grupo eram somados ao total geral uma segunda vez, e numa gravação que recalculava tudo, 10 + 20 + 7 dava 67
// HotXLS 2.381 e anteriores: um Variant de fórmula criado a partir de um String
// é varUString, pelo que esta comparação nunca era bem-sucedida
Result := (VarType(f) = varOleStr) and
(SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
SameText(Copy(f, 1, 10), '=SUBTOTAL('));
// HotXLS 2.382.0: VarIsStr aceita varString, varOleStr e varUString,
// e o AGGREGATE é excluído dos subtotais que o envolvem, como o Excel faz
if VarIsStr(f) then
Result := SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
SameText(Copy(f, 1, 10), '=SUBTOTAL(') or
SameText(Copy(f, 1, 10), 'AGGREGATE(') or
SameText(Copy(f, 1, 11), '=AGGREGATE(');A v2.382.0 lançou a correção com VarIsStr e, já que estava na mesma função, ensinou ao callback que as células AGGREGATE também são excluídas dos subtotais que as envolvem. Só isso fez passar a asserção do corpus, porque o 37 recalculado passou a coincidir com o 37 carregado. Não tornou a biblioteca honesta: a gravação continuava a recalcular, e o teste só estava verde porque o avaliador calhava a concordar com o Excel naquele ficheiro em particular. As regras sobre que células o SUBTOTAL e o AGGREGATE saltam, incluindo linhas ocultas, estão no artigo sobre linhas ocultas no SUBTOTAL e no AGGREGATE; o que aqui importa é que nenhum avaliador deve ter voto num ficheiro que não lhe pediu para calcular
O que é que o Excel garante sobre os valores em cache ao gravar?
O Excel trata uma gravação como um instantâneo e não como um evento de cálculo. O valor escrito no campo FormulaValue de um registo Formula ([MS-XLS] §2.4.127, disposição na §2.5.133) é aquilo que a célula mostra no momento, o que no modo de cálculo manual pode estar desatualizado há anos, e o Excel escreve-o fielmente mesmo assim. O recálculo é uma operação separada, com o seu próprio gatilho. O HotXLS segue agora a mesma regra nas gravações clássicas: WriteFormula e WriteFormulaWithTExp chamam primeiro TryGetCachedFormulaValue, ficam com CacheInfo.Value quando o estado é xlfcsLoaded ou xlfcsCalculated, e só caem no GetFormulaValue para xlfcsMissing e xlfcsInvalidated. A metade de leitura deste contrato, incluindo o que cada estado significa e porque é que um branco ou um False em cache continua a contar como valor, está descrita em Ler valores de fórmulas em cache do Excel em Delphi sem recalcular
O caminho de recurso é mantido de propósito, não removido. Uma fórmula atribuída nesta sessão através de Cells[Row, Col].Formula chega sem cache, e uma fórmula substituída numa célula carregada é marcada como xlfcsInvalidated pelo _SetCompiledFormula; ambas são avaliadas ao gravar exatamente como antes, para que um livro gerado continue a abrir no Excel com números lá dentro. Quando nem o avaliador consegue produzir um valor, o writer emite um payload zero e liga o fAlwaysCalc (bit 0 do grbit da §2.4.127) para que o Excel recalcule a célula ao abrir em vez de confiar no placeholder
procedure RoundTripWithoutRecalc(const Source, Target: string);
var
Book: TXLSWorkbook;
Before, After: TXLSFormulaCacheInfo;
begin
Book := TXLSWorkbook.Create;
try
Book.Open(Source);
// Folha, linha e coluna com base 1: R2C4 na primeira folha
if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
raise Exception.Create('R2C4 carries no usable cache');
Book.SaveAs(Target); // nenhum avaliador envolvido nas células em cache
finally
Book.Free;
end;
Book := TXLSWorkbook.Create;
try
Book.Open(Target);
Book.TryGetCachedFormulaValue(1, 2, 4, After);
// Before.Value = After.Value = 37 para o nested-subtotals.xls
// Uma gravação que recalculasse teria escrito 67 aqui
finally
Book.Free;
end;
end;Onde é que a raiz de uma fórmula partilhada BIFF guarda o valor em cache?
No seu próprio registo Formula, como qualquer outra célula de fórmula, e foi exatamente isso que fez da célula raiz de um grupo partilhado o único ponto onde a gravação com prioridade à cache ainda perdia. Uma fórmula partilhada em BIFF8 é guardada como um registo ShrFmla ([MS-XLS] §2.4.260) que se segue ao registo Formula da célula superior esquerda, e todas as células membro, incluindo a raiz, transportam um rgce composto por um único token PtgExp (§2.5.198): o primeiro byte da expressão analisada é $01, seguido da linha e da coluna da célula raiz. As células seguidoras são autónomas — o HotXLS lê o FormulaValue de cada uma e resolve a expressão procurando a fórmula compilada da raiz. A célula raiz é diferente, porque quando o seu registo Formula é analisado a expressão ainda não existe; chega um registo depois
É nesse intervalo de um registo que a cache se perdeu. O TXLSReader.ParseFormula descodifica o valor em cache e, ao ver um PtgExp cujas coordenadas coincidem com as da própria célula, memoriza a célula em FSharedFormulaRow e FSharedFormulaCol e publica a cache na célula. Quando o registo ShrFmla ($04BC) chega, o ParseSharedFormula compila a expressão e instala-a com _SetCompiledFormula, e o _SetCompiledFormula faz o que tem de fazer em qualquer alteração de fórmula: limpa o FCachedFormulaValue e repõe o estado em xlfcsMissing. O 37 carregado da raiz era por isso atirado fora antes de alguém o poder ler, o TryGetCachedFormulaValue reportava a raiz como não tendo cache, e o writer com prioridade à cache recuava obedientemente para o avaliador exatamente na célula que toda a gente estava a olhar. O registo Array (§2.4.4) tem a mesma ordenação e tinha o mesmo buraco
A correção da v2.382.3 acrescenta um terceiro campo, FSharedFormulaCachedValue, ao lado das coordenadas pendentes da raiz. O ParseFormula guarda lá a cache descodificada quando reconhece uma raiz, e tanto o ParseSharedFormula como o ParseArrayFormula a repetem através do _SetCellCachedFormulaValue imediatamente depois de instalarem a expressão compilada, repondo depois o depósito em Unassigned. A variante String da cache não é afetada por nada disto, porque o seu payload chega num registo String separado e é encaminhado pelas coordenadas da célula e não pela ordem dos registos. Se trabalha com o lado OOXML do mesmo conceito, o artigo sobre a expansão de si em fórmulas partilhadas XLSX explica porque é que o formato de pacote não tem um problema de ordenação equivalente, mas tem as suas próprias armadilhas de expansão
Porque é que os seguidores de fórmulas partilhadas precisam de um deslocamento relativo?
Porque a expressão guardada no ShrFmla é escrita em relação à célula raiz, e um seguidor que a reutilize tal como está avalia as referências da raiz em vez das suas. O reader antigo instalava Value.GetCopy() em cada seguidor, uma cópia profunda sem deslocamento, pelo que um grupo com raiz em B1 e =A1*3 dava =A1*3 a todos os seguidores. A gravação com prioridade à cache até disfarçava isto nos ficheiros carregados, já que os seguidores tinham o seu próprio FormulaValue e nunca precisavam da expressão para gravar corretamente; o problema aparecia no momento em que algo recalculasse. O reader passa agora a instalar TXLSCompiledFormula.GetCopy(row - srow, col - scol), que percorre a árvore sintática e desloca todas as referências relativas pela distância do seguidor à raiz, pelo que o seguidor em B2 passa a ter um =A2*3 genuíno
O teste de regressão que fixa os dois comportamentos vale a pena ler, porque se recusa a deixar passar uma coincidência. Constrói um livro com =A1*3 e =A2*3 sobre as entradas 2 e 4 e depois injeta as caches deliberadamente erradas 999 e 888 através do _SetCellCachedFormulaValue, uma vez com UseSharedFormulas ligado e outra desligado. Depois de gravar e recarregar, as duas células têm de continuar a reportar 999 e 888 — prova de que a gravação não tocou nem na cache da raiz nem na do seguidor. Só depois de um Recalculate explícito é que passam a 6 e 12, prova de que a expressão deslocada do seguidor está correta. Um teste que semeasse os valores verdadeiros também teria passado com o writer antigo, e é precisamente por isso que se semeiam valores errados
var
Book: TXLSWorkbook;
Info: TXLSFormulaCacheInfo;
begin
Book := TXLSWorkbook.Create;
try
Book.Open('quarterly-model.xls');
Book.Sheets[1].Cells[1, 1].Value := 5; // alterar uma entrada
// As caches carregadas das fórmulas dependentes NÃO são invalidadas por uma
// edição literal, pelo que um SaveAs simples manteria os números antigos.
// Peça um recálculo quando quer mesmo resultados frescos:
Book.Recalculate;
if Book.TryGetCachedFormulaValue(1, 1, 2, Info) then
Writeln('B1 now ', VarToStr(Info.Value),
', state ordinal ', Ord(Info.State)); // xlfcsCalculated
Book.SaveAs('quarterly-model-updated.xls');
finally
Book.Free;
end;
end;O que é que o contrato de prioridade à cache não faz por si
A gravação com prioridade à cache preserva o que foi carregado; não acompanha se o que foi carregado continua a ser verdade. Alterar um literal de que uma fórmula depende marca o grafo de dependências como sujo para o avaliador, mas deixa a cache xlfcsLoaded da célula dependente no lugar, e o writer clássico vai alegremente escrever esse valor desatualizado a menos que chame Recalculate ou leia primeiro o Value da célula, o que a calcula e passa o estado para xlfcsCalculated. É o mesmo compromisso que o Excel assume no modo de cálculo manual, e é o certo para um pipeline que abre ficheiros de terceiros, edita umas etiquetas e grava — mas significa que um livro que edita entradas tem de assumir explicitamente o seu passo de recálculo. A política RecalcBeforeSave do writer XLSX não é afetada por este trabalho e tem o seu próprio modo manual que preserva as caches no mesmo espírito. Daqui decorrem duas fronteiras mais pequenas: o caminho com prioridade à cache só ajuda células cujo estado seja xlfcsLoaded ou xlfcsCalculated; um gerador que escreve fórmulas e nunca as avalia continua a pagar uma avaliação por célula ao gravar, exatamente como antes. E a correção dos subtotais aninhados corrige que células o avaliador salta, não todas as funções que o avaliador implementa — um ficheiro cujas fórmulas o HotXLS não consegue calcular de forma idêntica ao Excel passa agora a ser seguro de tratar sem alterações, mas um Recalculate deliberado sobre esse ficheiro continuará a produzir a resposta da biblioteca e não a do Excel, e deve comparar as duas antes de confiar numa gravação recalculada
As gravações clássicas com prioridade à cache, as caches de raiz restauradas das fórmulas partilhadas e de array, o deslocamento de referências relativas dos seguidores partilhados e as regras corrigidas de aninhamento de SUBTOTAL e AGGREGATE vêm todas no HotXLS Delphi Spreadsheet Component padrão para Delphi e C++Builder, sem dependência do Excel nem de qualquer servidor de automação OLE; a página de produto traz a referência completa da API para o livro, o leitor de caches e os pontos de entrada de recálculo aqui usados