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

O HotXLS responde à pergunta que qualquer pipeline de folhas de cálculo acaba por ter de fazer, que é saber se os números armazenados num livro ainda batem certo com as fórmulas que os produziram. CalculateAndVerify recalcula todo o grafo de dependências para uma overlay isolada, compara cada resultado com o valor já em cache na célula, e reporta os desacordos. Por predefinição não muda nada

A razão de isto importar é que um ficheiro de folha de cálculo 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-nas em sincronia. Todo o resto do mundo pode não manter. Um ficheiro que passou por uma biblioteca antiga, por uma recalculação parcial, por uma parte XML editada à mão ou por uma ferramenta que escreveu valores sem os recalcular apresenta de boa vontade um total que já não decorre das suas entradas, e nada no formato de ficheiro o sinaliza

Porque é um valor em cache em desacordo com a sua fórmula tão perigoso?

Porque é invisível em todos os caminhos de leitura ordinários. Abra o ficheiro num visualizador, leia a célula através de uma API, exporte-a para CSV ou PDF, e recebe o número em cache. A fórmula está ali mesmo na mesma célula, e ninguém as compara. O desacordo só vem à superfície quando alguém abre o livro no Excel, que recalcula ao carregar na maioria das configurações, e de repente um relatório que foi aprovado no trimestre passado mostra totais diferentes

A auditoria existe para tornar essa comparação uma operação deliberada e agendada em vez de um acidente. É o equivalente, em folhas de cálculo, de verificar uma checksum: barata o suficiente para correr num pipeline de entrada, e a única coisa que transforma um problema silencioso de integridade de dados num relatório sobre o qual se 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 respondem a três perguntas diferentes. O CalculateAndVerify sem parâmetros devolve uma contagem de desacordos, que é tudo de que uma verificação de saúde precisa. O overload com um array de saída de desacordos dá-lhe as células. O overload que recebe TXLSRecalcAuditOptions devolve um TXLSCalculationAuditReport completo, que é o a alcançar quando precisa de saber não só que um valor discrepa mas porque é que a auditoria não conseguiu avaliar algo

A overlay, e porque é que a auditoria não escreve

Todo o valor recalculado vai para uma overlay em vez de para a cache da célula, e a overlay é injetada no próprio início do callback de leitura de células em ambos os motores de livro. Essa colocação é o que torna a auditoria autoconsistente: quando B1 é recalculada e C1 depende de B1, C1 vê o valor desta passagem de auditoria, e não o valor velho em cache. Sem isso, um único erro a montante seria reportado uma vez e depois absorvido, e todas as células a jusante pareceriam concordar com uma entrada errada

As células cujo valor recalculado casa com a cache não entram de todo na overlay. Isso não é uma micro-otimização, é o que mantém a auditoria acessível. Um livro limpo com cem mil fórmulas efetua zero escritas na overlay e a passagem fica dentro de um orçamento de 1.35x face a uma recalculação completa, que é a diferença entre algo que se pode correr a cada entrada e algo que se corre uma vez por trimestre

A avaliação segue uma ordem topológica em série derivada do grafo de dependências, com todos os nós marcados como dirty à partida, por isso cada célula é calculada exatamente uma vez depois das suas entradas. Se quer a maquinaria incremental que mantém um livro vivo atualizado em vez de auditar um armazenado, essa é um mecanismo diferente, descrito em recalculação incremental e o grafo de dependências

As falhas são classificadas, não amontoadas

Uma célula que a auditoria não consegue avaliar não é o mesmo achado do que uma célula cujo valor discrepa, e TXLSCalculationAuditIssueKind mantém as categorias separadas. xlcaiCacheMismatch é o desacordo de valor. xlcaiMissingFunction e xlcaiMissingName dizem que o avaliador encontrou algo que não implementa ou não consegue resolver. xlcaiUnsupportedArguments cobre formas de argumentos fora do subconjunto suportado. xlcaiExternalReferenceDenied e xlcaiExternalReferenceMissing separam uma recusa de política de um livro ausente. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled e xlcaiInternalFailure completam o conjunto

Uma distinção vale a pena enunciar 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, por isso a auditoria guarda esse erro na overlay e compara-o com a cache como qualquer outro valor. Um livro cheio de células com erro intencional produz zero achados, e um livro em que um erro apareceu ou desapareceu desde que os valores foram postos em cache produz exatamente os achados que quer

As referências circulares têm tratamento próprio. Os nós num ciclo nunca entram na ordem topológica, por isso cada um é reportado individualmente como xlcaiCircularReference, e a auditoria não corre o solver iterativo. Esse é um contrato deliberado de só leitura: se a iteração está ativada afeta como o código de resultado deve ser interpretado, e não o que a auditoria faz. A mecânica da avaliação iterativa está coberta separadamente em cálculo iterativo e referências circulares

Ler uma cadeia de falha

Quando uma fórmula falha a avaliação, saber que célula falhou raramente chega, porque a falha costuma estar três níveis abaixo numa cadeia de referências. Cada achado traz por isso uma string Stack renderizada com o quadro mais externo primeiro, na forma Sheet1!A1 > Sheet1!B2 > Data!C7, para o relatório apontar para a célula que realmente partiu e não para a célula que calhou estar a olhar

O gravador é limitado. MaxStackFrames tem a predefinição 64 com um piso de 8, e a cadeia falhada mais funda é a que fica retida: um quadro interior regista a cadeia quando a falha aí tem origem, e os quadros exteriores a desenrolar depois não a sobrescrevem. Se alguma cadeia excedeu o orçamento, Report.StackTruncated é definido, o que lhe diz a diferença entre uma cadeia curta e uma cadeia de que não viu tudo

// Só de leitura por predefinição. ApplyResults faz commit da overlay apenas
// depois de uma auditoria totalmente bem-sucedida, sob um guarda de escrita
// que rejeita o commit se a estrutura do livro mudou enquanto a auditoria corria
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // comparação exata, expõe a deriva
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 fronteira do próximo nó
end;

Quando deve deixar a auditoria reparar o livro?

Só quando a auditoria voltou completamente limpa de achados da classe de falha, que é precisamente a condição que ApplyResults lhe impõe. O commit acontece depois de uma passagem totalmente bem-sucedida, não ter sido cancelada, e passar um guarda estrutural: o motor binário vigia um identificador de mudança do livro, o motor OOXML tira um snapshot de geração de estrutura por folha. Se alguma coisa se mexeu enquanto a auditoria corria, os resultados descrevem um livro que já não existe e o commit é recusado

Note a assimetria deliberada. Desacordos de cache não bloqueiam a aplicação, porque são exatamente o que o commit existe para reparar. Achados da classe de falha bloqueiam, porque um livro em que algumas fórmulas não puderam ser avaliadas ficaria meio reparado, e um livro meio reparado é pior do que um não reparado de que sabe que não deve confiar

A tolerância é uma decisão de política, não uma predefinição

A comparação predefinida é uma tolerância absoluta de 1E-6 com a tolerância relativa desativada, que preserva o comportamento clássico e aceita silenciosamente uma deriva de 4E-7. Isso costuma estar certo: diferenças de ordem de avaliação em vírgula flutuante entre o que quer que tenha produzido o ficheiro e o avaliador atual vão produzir diferenças daquela dimensão em somas longas, e reportá-las como achados de integridade é ruído

Ponha ambas as tolerâncias a zero quando a pergunta é outra, quando está a tentar descobrir se um avaliador mudou de comportamento entre versões, ou se uma ferramenta de terceiros reescreve valores de forma subtilmente diferente. A zero, a mesma deriva de 4E-7 torna-se visível, e tudo o resto também. Escolha a tolerância com base na pergunta que está a fazer, e registe a escolha ao lado do relatório, porque um relatório sem a sua tolerância não é interpretável

Duas capacidades vizinhas completam o quadro. Quando quer saber porque uma única fórmula produz o valor que produz, a vista passo a passo em o tracer de avaliação de fórmulas é a ferramenta certa. Quando deliberadamente quer os valores em cache respeitados sem qualquer recalculação, por exemplo num caminho de entrada que tem de reproduzir o ficheiro exatamente como chegou, esse modo está descrito em ler valores de fórmula em cache sem recalcular. A auditoria é o que se senta entre os dois: diz-lhe se confiar na cache é seguro. É distribuída com o componente de folhas de cálculo Delphi HotXLS para ambos os motores binário e OOXML