Audite caches de fórmulas do Excel com Deep Recalc do HotXLS

O HotXLS responde à pergunta que toda pipeline de planilhas mais cedo ou mais tarde precisa fazer: os números armazenados numa workbook ainda batem com as fórmulas que os produziram. O CalculateAndVerify recalcula o grafo de dependências inteiro num overlay isolado, compara cada resultado com o valor em cache já presente na célula, e reporta as divergências. Por padrão, ele não muda nada

A razão de isso importar é que um arquivo de planilha guarda duas coisas por célula de fórmula: a fórmula e o último valor que alguém calculou para ela. O Excel mantém os dois em sinc. Todo o resto do mundo pode não manter. Um arquivo que passou por uma biblioteca mais antiga, uma recalculação parcial, uma parte XML editada à mão ou uma ferramenta que gravou valores sem recalculá-los vai apresentar de bom grado um total que não decorre mais das suas entradas, e nada no formato de arquivo sinaliza isso

Por que um valor em cache que discorda da sua fórmula é tão perigoso?

Porque ele é invisível em todo caminho ordinário de leitura. Abra o arquivo num viewer, leia a célula por uma API, exporte para CSV ou PDF, e você recebe o número em cache. A fórmula está ali, na mesma célula, e ninguém as compara. A divergência só aparece quando alguém abre a workbook no Excel, que recalcula no load sob a maioria das configurações, e de repente um relatório aprovado no último trimestre mostra totais diferentes

A auditoria existe para tornar essa comparação uma operação deliberada e agendada, e não um acidente. É o equivalente, em planilhas, de verificar um checksum: barato o suficiente para rodar numa pipeline de entrada, e a única coisa que transforma um problema silencioso de integridade de dados num relatório sobre o qual você pode agir

var
  Book: TXLSWorkbook;
  Options: TXLSRecalcAuditOptions;
  Report: TXLSCalculationAuditReport;
  I: Integer;
begin
  Book := TXLSWorkbook.Create(nil);
  try
    Book.LoadFromFile('quarterly-close.xls');
    Options := TXLSRecalcAuditOptions.Default;
    Options.MaxIssues := 500;
    Report := Book.CalculateAndVerify(Options);
    try
      for I := 0 to Report.Count - 1 do
        if Report[I].Kind = xlcaiCacheMismatch then
          Writeln(Report[I].SheetName, '!',
                  Report[I].Row, ':', Report[I].Col, '  ',
                  Report[I].Formula,
                  '  cached=', VarToStr(Report[I].Actual),
                  '  recomputed=', VarToStr(Report[I].Expected));
      if Report.Truncated then
        Writeln('issue budget reached, raise MaxIssues');
    finally
      Report.Free;
    end;
  finally
    Book.Free;
  end;
end;

Há três overloads e eles respondem a três perguntas diferentes. O CalculateAndVerify sem parâmetros devolve uma contagem de divergências, que é tudo de que um health check precisa. O overload com um array out de divergências te dá as células. O overload que recebe TXLSRecalcAuditOptions devolve um TXLSCalculationAuditReport completo, que é o que você alcança quando precisa saber não só que um valor discorda, mas por que a auditoria não conseguiu avaliar algo

O overlay, e por que a auditoria não grava

Todo valor recalculado cai num overlay em vez de no cache da célula, e o overlay é injetado bem na frente do callback de leitura de célula nos dois engines de workbook. Esse posicionamento é o que torna a auditoria autoconsistente: quando B1 é recalculada e C1 depende de B1, C1 vê o valor desta passada da auditoria, não o valor velho em cache. Sem isso, um único erro upstream seria reportado uma vez e depois absorvido, e toda célula downstream pareceria concordar com uma entrada errada

Células cujo valor recalculado bate com o cache não entram no overlay de forma alguma. Não é uma micro-otimização, é o que mantém a auditoria acessível. Uma workbook limpa com cem mil fórmulas faz zero escritas no overlay e a passada fica dentro de um orçamento de 1,35x contra uma recalculação completa, que é a diferença entre algo que você pode rodar a cada entrada e algo que você roda uma vez por trimestre

A avaliação segue uma ordem topológica serial derivada do grafo de dependências, com todo nó marcado dirty primeiro, então cada célula é calculada exatamente uma vez, depois das suas entradas. Se você quer a maquinaria incremental que mantém uma workbook viva atualizada, em vez de auditar uma armazenada, esse é outro mecanismo, descrito em recalculação incremental e o grafo de dependências

As falhas são classificadas, não jogadas num bolo só

Uma célula que a auditoria não consegue avaliar não é a mesma constatação que uma célula cujo valor discorda, e o TXLSCalculationAuditIssueKind mantém as categorias separadas. xlcaiCacheMismatch é a divergência de valor. xlcaiMissingFunction e xlcaiMissingName dizem que o evaluator encontrou algo que não implementa ou não consegue resolver. xlcaiUnsupportedArguments cobre formas de argumento fora do subconjunto suportado. xlcaiExternalReferenceDenied e xlcaiExternalReferenceMissing separam uma recusa de política de uma workbook ausente. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled e xlcaiInternalFailure completam o conjunto

Uma distinção vale ser enunciada porque inverte uma suposição comum. Um código de erro positivo do Excel é um resultado, não uma falha. Uma célula que legitimamente avalia para #DIV/0! calculou corretamente, então a auditoria guarda esse erro no overlay e o compara com o cache como qualquer outro valor. Uma workbook cheia de células de erro intencionais produz zero constatações, e uma workbook em que um erro apareceu ou desapareceu desde que os valores foram cacheados produz exatamente as constatações que você quer

Referências circulares recebem tratamento próprio. Nós num ciclo nunca entram na ordem topológica, então cada um é reportado individualmente como xlcaiCircularReference, e a auditoria não roda o solver iterativo. Esse é um contrato read-only deliberado: se a iteração está habilitada afeta como o código de resultado deve ser interpretado, não o que a auditoria faz. A mecânica da avaliação iterativa está coberta à parte em cálculo iterativo e referências circulares

Lendo uma cadeia de falha

Quando uma fórmula falha em avaliar, saber qual célula falhou raramente basta, porque a falha geralmente está três níveis abaixo numa cadeia de referências. Cada issue carrega, portanto, uma string Stack renderizada com o frame mais externo primeiro, na forma Sheet1!A1 > Sheet1!B2 > Data!C7, para o relatório apontar a célula que de fato quebrou, e não a célula em que você por acaso olhou

O gravador tem limites. O MaxStackFrames tem padrão 64 com piso de 8, e a cadeia de falha mais profunda é a que fica retida: um frame interno registra a cadeia quando a falha origina ali, e os frames externos se desenrolando depois não a sobrescrevem. Se alguma cadeia excedeu o orçamento, Report.StackTruncated é marcado, o que te diz a diferença entre uma cadeia curta e uma cadeia que você não viu inteira

// Read-only por padrão. O ApplyResults faz o commit do overlay só depois de uma
// auditoria totalmente bem-sucedida, sob um write guard que rejeita o commit
// se a estrutura da workbook mudou enquanto a auditoria rodava
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // comparação exata, expõe o drift
Options.RelativeTolerance := 0;
Options.OnProgress := HandleProgress;

Report := Book.CalculateAndVerify(Options);
try
  if Report.Applied then
    Book.SaveToFile('quarterly-close-repaired.xls')
  else
    Writeln('not applied: ', Report.Count, ' issues blocked the commit');
finally
  Report.Free;
end;

procedure THarness.HandleProgress(ASender: TObject;
  ACurrent, ATotal: Integer; var ACancel: Boolean);
begin
  ACancel := FUserRequestedStop;   // a auditoria para na próxima fronteira de nó
end;

Quando deixar a auditoria reparar a workbook?

Só quando a auditoria voltou completamente limpa de issues da classe de falha, que é precisamente a condição que o ApplyResults impõe para você. O commit acontece depois de uma passada totalmente bem-sucedida, que não foi cancelada, e passa por um guard estrutural: o engine binário observa um identificador de mudança da workbook, o engine OOXML tira um snapshot de geração de estrutura por planilha. Se algo se moveu enquanto a auditoria rodava, os resultados descrevem uma workbook que não existe mais e o commit é recusado

Note a assimetria deliberada. Divergências de cache não bloqueiam a aplicação, porque são exatamente o que o commit existe para reparar. Issues da classe de falha bloqueiam, porque uma workbook em que algumas fórmulas não puderam ser avaliadas ficaria meia reparada, e uma workbook meia reparada é pior que uma não reparada que você sabe que deve desconfiar

Tolerância é uma decisão de política, não um padrão

A comparação padrão é uma tolerância absoluta de 1E-6 com tolerância relativa desabilitada, o que preserva o comportamento clássico e aceita discretamente um drift de 4E-7. Isso costuma ser o certo: diferenças de ordem de avaliação em ponto flutuante entre o que quer que tenha produzido o arquivo e o evaluator atual vão produzir diferenças desse tamanho em somas longas, e reportá-las como constatações de integridade é ruído

Zere as duas tolerâncias quando a pergunta é diferente, quando você está tentando descobrir se um evaluator mudou de comportamento entre versões, ou se uma ferramenta de terceiros regrava valores de um jeito sutilmente diferente. Em zero, o mesmo drift de 4E-7 fica visível, e todo o resto também. Escolha a tolerância com base na pergunta que você está fazendo, e registre a escolha junto do relatório, porque um relatório sem a sua tolerância não é interpretável

Duas capacidades vizinhas completam o quadro. Quando você quer saber por que uma única fórmula produz o valor que produz, a visão passo a passo em o tracer de avaliação de fórmulas é a ferramenta certa. Quando você deliberadamente quer que os valores em cache sejam honrados sem nenhuma recalculação, por exemplo num caminho de entrada que precisa reproduzir o arquivo exatamente como chegou, esse modo está descrito em leitura de valores de fórmula em cache sem recalcular. A auditoria é o que fica entre esses dois: ela diz se confiar no cache é seguro. Ela vem com o componente de planilha HotXLS para Delphi para os engines binário e OOXML