Um total geral que o Excel mostra como 37 pode voltar como 67 depois que um programa abre o arquivo .xls e o salva sem editar uma única célula. Os 30 extras não são aleatórios. São exatamente a soma dos dois subtotais de grupo, 10 e 20, que estão dentro da faixa que o total geral cobre. Duas coisas precisam dar errado juntas para esse número se mover: o salvamento precisa recalcular todas as fórmulas na saída, e quem recalcula precisa esquecer que um SUBTOTAL ignora outras células SUBTOTAL. Remova qualquer uma das duas e o arquivo sobrevive, e é por isso que o primeiro problema pode ficar escondido atrás do segundo
Por que salvar um arquivo XLS muda o valor de uma fórmula?
Um salvamento muda o valor de uma fórmula quando o escritor calcula o número que armazena ao lado de cada fórmula em vez de copiar o que carregou. Todo registro Formula do BIFF8 carrega um campo FormulaValue de oito bytes ([MS-XLS] §2.4.127, layout do valor em §2.5.133) que guarda o último resultado que o Excel exibiu para aquela célula. O escritor precisa pôr algo nesses oito bytes, e o caminho de código mais curto é entregar toda fórmula ao motor de cálculo. Feito isso, cada salvamento é um recálculo completo que ninguém pediu, e cada ponto em que esse motor e o Excel divergem vira um número mudado num arquivo que o usuário acredita intocado
A divergência do SUBTOTAL dá um bom exemplo, porque a regra de planilha é simples e a armadilha está na tipagem Variant do Delphi. O Excel define que todo número de função SUBTOTAL ignora células cuja própria fórmula é outro SUBTOTAL. Para uma planilha cujas linhas de detalhe somam 37 (as linhas sob dois grupos que subtotalizam 10 e 20, mais 7 fora de qualquer grupo), o total geral é 37. Um motor que não percebe os subtotais aninhados conta o 10 e o 20 uma segunda vez e chega a 67. Percebê-los normalmente significa uma verificação curta sobre o texto da fórmula, e a versão Delphi óbvia compila, roda e nunca encontra nada:
// Um helper que você mesmo escreve, por exemplo ao varrer uma planilha
// em busca de fórmulas que um SUBTOTAL externo precisa ignorar
function IsNestedSubtotal(const F: Variant): Boolean;
begin
// O teste tentador nunca funciona no Delphi Unicode: uma String
// atribuída a um Variant é varUString, não varOleStr
// Result := (VarType(F) = varOleStr) and ...
Result := False;
if VarIsStr(F) then // aceita varString, varOleStr e varUString
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(');
end;
// Uso com o HotXLS (planilhas, linhas e colunas são base 1):
// if IsNestedSubtotal(Book.Sheets[1].Cells[12, 4].Formula) then ...VarIsStr é o teste que o avaliador do HotXLS aplica à mesma pergunta, e ele também trata células AGGREGATE como excluídas de um subtotal externo, do jeito que o Excel faz. Corrigir o predicado não é a história toda, porém. Com a verificação correta, o salvamento que recalcula grava 37 só porque o motor por acaso concorda com o Excel neste arquivo em particular. A próxima fórmula que o motor tratar um pouco diferente vai se mover de novo. As regras de quais células SUBTOTAL e AGGREGATE ignoram, linhas ocultas incluídas, estão cobertas em o artigo sobre linhas ocultas de SUBTOTAL e AGGREGATE. Com salvamento cache primeiro, um arquivo que você nunca pediu para calcular volta ao disco com 37 na célula, fosse o que fosse que o motor faria com ele
O que um salvamento XLS clássico grava em cada registro Formula
O HotXLS salva uma pasta BIFF8 com o cache primeiro: para cada célula de fórmula, TXLSWorkbook.SaveAs grava o valor em cache sempre que TryGetCachedFormulaValue reportaria um, e só roda o avaliador quando não reportaria. O estado do cache decide. Uma célula em estado xlfcsLoaded (o valor veio do arquivo fonte) ou xlfcsCalculated (o HotXLS calculou nesta sessão) tem o CacheInfo.Value dela gravado byte a byte. Uma célula em estado xlfcsMissing ou xlfcsInvalidated é avaliada na hora do salvamento. Isso reflete como o Excel trata um salvamento: um retrato do que cada célula mostra, que no modo de cálculo manual pode estar bem desatualizado, gravado fielmente mesmo assim
O caminho de avaliação continua existindo por boas razões. Uma fórmula que você atribui em código via Cells[Row, Col].Formula ainda não tem cache, e atribuir uma fórmula nova a uma célula carregada a move para xlfcsInvalidated, porque o resultado velho pertence à expressão velha. Ambas são calculadas durante o salvamento, então uma pasta gerada do zero abre no Excel mostrando números calculados em vez de zeros. Se nem o avaliador consegue produzir um valor (uma função não suportada, um link externo quebrado), o escritor armazena zero e arma fAlwaysCalc, bit 0 das flags do registro em §2.4.127, para o Excel recalcular a célula ao abrir em vez de confiar no placeholder
Uma propriedade parece relacionada e não é. TXLSWorkbook.RecalcOnSave espelha a opção "Recalculate workbook before saving" do Excel, do registro CalcSaveRecalc ([MS-XLS] §2.4.37). Ela só importa para o Excel, só no modo de cálculo manual, e o HotXLS simplesmente a grava no arquivo. O SaveAs não a consulta ao decidir o que vai para o FormulaValue. O round trip abaixo é o caminho mais rápido para ver o contrato num arquivo seu:
procedure CheckRoundTrip(const Source, Target: string;
Row, Col: Integer);
var
Book: TXLSWorkbook;
Before, After: TXLSFormulaCacheInfo;
begin
Book := TXLSWorkbook.Create;
try
if Book.Open(Source) <> 1 then
raise Exception.Create('Cannot open ' + Source);
// Planilha, linha e coluna são base 1
if not Book.TryGetCachedFormulaValue(1, Row, Col, Before) then
raise Exception.Create('Cell carries no usable cached value');
if Book.SaveAs(Target) <> 1 then // células em cache são gravadas como estão
raise Exception.Create('Save failed');
finally
Book.Free;
end;
Book := TXLSWorkbook.Create;
try
Book.Open(Target);
Book.TryGetCachedFormulaValue(1, Row, Col, After);
// Esperado: After.Value igual a Before.Value, e After.State de
// novo xlfcsLoaded, porque veio do arquivo salvo
Writeln(VarToStr(Before.Value), ' -> ', VarToStr(After.Value));
finally
Book.Free;
end;
end;O lado de leitura desse contrato, incluindo o que cada estado significa e por que um branco em cache ou False ainda conta como valor, está descrito em Read Excel Cached Formula Values in Delphi Without Recalc. Rode a verificação acima na célula do total geral de uma pasta com subtotais aninhados e as duas linhas imprimem o mesmo número
Onde a raiz de uma fórmula compartilhada do BIFF guarda seu valor em cache?
No registro Formula dela, como qualquer outra célula de fórmula, mas a expressão chega um registro depois, e é nesse intervalo que um leitor descuidado perde o cache. O BIFF8 guarda uma fórmula compartilhada como um registro ShrFmla ([MS-XLS] §2.4.260) colocado logo depois do registro Formula da célula do canto superior esquerdo do grupo. Toda célula membro, a raiz incluída, carrega um rgce feito de um único token PtgExp (§2.5.198): o byte $01 seguido da linha e da coluna da raiz. As células seguidoras são fáceis, porque quando aparecem a expressão da raiz já existe e o FormulaValue delas está no registro delas. A raiz não é fácil. Quando o registro Formula dela é lido, ela tem um valor em cache de 37 e nenhuma expressão ainda
Instalar uma expressão numa célula limpa o cache dela, e isso precisa continuar verdade, porque uma fórmula substituída realmente torna o resultado velho sem sentido. Aplicada às cegas ao registro ShrFmla, porém, a mesma regra joga fora o 37 da raiz no momento em que a expressão chega, TryGetCachedFormulaValue reporta a raiz como xlfcsMissing, e o salvamento cai no avaliador exatamente para a célula que o usuário tem mais chance de olhar. O leitor do HotXLS segura o valor decodificado da raiz enquanto espera o registro ShrFmla, instala a expressão e depois recoloca o valor carregado com estado xlfcsLoaded. Fórmulas de matriz (o registro Array, §2.4.4) seguem a mesma ordem de registros e recebem o mesmo tratamento. A forma String do cache nunca teve esse problema, porque o texto dela chega num registro String separado e é casado com a célula pela linha e pela coluna, então a posição no stream nunca importa
Se você também cuida do lado OOXML, o artigo sobre expansão de si em fórmulas compartilhadas XLSX explica por que o formato de pacote não tem o intervalo de ordem de registros, mas traz as próprias armadilhas de expansão
As células seguidoras de uma fórmula compartilhada têm uma cópia deslocada da expressão
A expressão num registro ShrFmla é escrita relativa à célula raiz, então cada seguidora precisa da própria cópia deslocada pela distância dela até a raiz. Copie-a literal e um grupo com raiz em B1 com =A1*3 entrega o mesmo =A1*3 para B2 e B3. O HotXLS dá a cada seguidora uma cópia cujas referências relativas são deslocadas pela distância de linha e coluna da seguidora, então B2 tem de verdade um =A2*3 e B3 tem um =A3*3
É também por isso que um round trip simples não diz se as expressões das seguidoras estão certas. As seguidoras mantêm os valores carregados através de um salvamento, então uma cópia literal passaria. Uma verificação que prove algo precisa mudar uma entrada e recalcular. A rotina abaixo escreve um grupo compartilhado com UseSharedFormulas ligado (o padrão é False, que escreve um registro Formula por célula), reabre o arquivo, edita A2 e observa B2:
procedure CheckSharedFollower(const FileName: string);
var
Book: TXLSWorkbook;
Sheet: TXLSWorksheet;
Info: TXLSFormulaCacheInfo;
Row: Integer;
begin
Book := TXLSWorkbook.Create;
try
Sheet := Book.Sheets.Add;
for Row := 1 to 3 do
begin
Sheet.Cells[Row, 1].Value := Row * 2; // 2, 4, 6
Sheet.Cells[Row, 2].Formula := '=A' + IntToStr(Row) + '*3';
end;
Book.UseSharedFormulas := True; // B1:B3 viram um grupo ShrFmla
Book.SaveAs(FileName); // sem caches ainda: avaliado para 6, 12, 18
finally
Book.Free;
end;
Book := TXLSWorkbook.Create;
try
Book.Open(FileName);
Book.Sheets[1].Cells[2, 1].Value := 10; // edita a entrada da seguidora
Book.TryGetCachedFormulaValue(1, 2, 2, Info);
Writeln('B2 before: ', VarToStr(Info.Value)); // Esperado: 12, ainda carregado
Book.Recalculate;
Book.TryGetCachedFormulaValue(1, 2, 2, Info);
Writeln('B2 after: ', VarToStr(Info.Value)); // Esperado: 30, de =A2*3
finally
Book.Free;
end;
end;Uma seguidora que tivesse reusado =A1*3 imprimiria 6 na segunda linha. A primeira linha importa tanto quanto: ela mostra que editar um literal deixa o valor carregado da fórmula dependente no lugar até algo recalcular
O que o salvamento cache primeiro deixa com você
O salvamento cache primeiro preserva o que foi carregado. Ele não acompanha se o que foi carregado ainda é verdade. Mudar uma célula de que uma fórmula depende marca o grafo de dependências para o próximo recálculo, mas a célula dependente mantém o valor xlfcsLoaded dela, e o SaveAs grava esse número velho a menos que você chame Recalculate antes ou leia o Value da célula, o que calcula a fórmula e a move para xlfcsCalculated. O Recalculate devolve o número de células de fórmula que ele não conseguiu avaliar, então um resultado não nulo vale um log antes do salvamento
No pipeline comum, um programa abre uma pasta que outra pessoa construiu, preenche alguns rótulos ou um cabeçalho e salva. Nada nesse arquivo deveria mudar de valor, e com o salvamento cache primeiro nada muda. Um programa que edita entradas é o dono do passo de recálculo, e esse passo pertence a uma chamada Recalculate visível ao lado das edições, onde qualquer um lendo o código a vê. O Excel faz a mesma troca no modo de cálculo manual. O escritor XLSX tem a própria política, RecalcBeforeSave com os modos xrmManual, xrmIfDirty e xrmAlways, definida na classe de pasta XLSX e independente de tudo acima
Duas fronteiras decorrem do design. O caminho do cache só ajuda células em estado xlfcsLoaded ou xlfcsCalculated, então um gerador que escreve milhares de fórmulas novas e nunca as avalia ainda paga uma avaliação por célula durante o salvamento. E o contrato governa o que um salvamento grava, não o que um recálculo explícito computa: um arquivo intocado mantém os números que o Excel armazenou, enquanto um Recalculate grava os resultados do avaliador do HotXLS. Compare os dois em algumas pastas representativas antes de trocar um pipeline para salvamentos recalculados
O HotXLS Delphi Spreadsheet Component lê e escreve pastas BIFF8 clássicas e XLSX a partir de Delphi e C++Builder sem Excel nem automação OLE instalados. A página do produto documenta as chamadas de pasta, valor em cache e recálculo usadas nestes exemplos