Rastrear a Avaliação de Fórmulas Excel Passo a Passo em Delphi

Uma folha de cálculo devolve um valor errado. A fórmula está correta — verificou a sintaxe, as referências de células resolvem e o Excel calcula o resultado esperado. Mas o seu código Delphi, usando o HotXLS para avaliar a mesma fórmula, obtém um número diferente. Para diagnosticar isso, precisa de ver cada passo intermédio da avaliação: o que cada sub-expressão produz, em que ordem as funções são chamadas e que valor cada referência de intervalo resolve. Esse é o problema que TXLSFormulaTracer resolve

A estrutura de um passo de rastreio

Cada passo de avaliação é representado por um objeto TXLSFormulaStep com quatro campos. Depth é um inteiro que indica o nível de aninhamento — zero para a expressão de nível superior, um para os seus operandos diretos, dois para os operandos desses, e assim por diante. Source é uma cadeia de caracteres legível por humanos que nomeia o que está a ser avaliado: um nome de função como SUM, um literal como 42 ou "text", ou uma referência de intervalo como B2:B10. Expression é a sub-expressão que este passo avalia, na notação de fórmula A1. Value é o resultado após a avaliação

Uma passagem de rastreio devolve uma lista desses objetos na ordem de avaliação. Percorrê-los diz exatamente o que o motor fez para calcular o resultado final

Configurar e executar um rastreio

Criar um tracer e chamar o seu método de trace produz a lista de passos. O tracer precisa de uma referência ao workbook carregado para que possa resolver referências de células durante a avaliação. A fórmula passada é a expressão a rastrear, sem o sinal = inicial que aparece no Excel

var
  XLS: TXLSFile;
  Tracer: TXLSFormulaTracer;
  Steps: TXLSFormulaStepList;
  Step: TXLSFormulaStep;
  i: Integer;
begin
  XLS := TXLSFile.Create;
  try
    XLS.Open('budget.xlsx');
    XLS.ActiveSheet := 1;

    Tracer := TXLSFormulaTracer.Create(XLS);
    try
      Steps := Tracer.Trace('SUM(B2:B10)*C1');
      for i := 0 to Steps.Count - 1 do
      begin
        Step := Steps[i];
        WriteLn(
          StringOfChar(' ', Step.Depth * 2),
          Step.Source, ' -> ', Step.Value
        );
      end;
    finally
      Tracer.Free;
    end;
  finally
    XLS.Free;
  end;
end;

O recuo pelo campo Depth produz uma vista em árvore da avaliação que torna imediatamente visível a que nível cada operando contribui para o resultado

Formatação de literais independente de locale

Um problema que assombra a depuração de fórmulas é a formatação de números dependente de locale. Se o seu sistema usa vírgula como separador decimal e o ficheiro Excel usa ponto, os literais numa fórmula podem ser analisados de forma diferente dependendo das definições de sistema onde o código está a executar. O TXLSFormulaTracer usa formatação de literais invariante de locale internamente: os números são analisados e formatados com o ponto como separador decimal independentemente das definições de locale do sistema. Isto significa que um ficheiro budget.xlsx produzido numa máquina alemã (decimal com vírgula) rastreia com os mesmos valores numa máquina inglesa (decimal com ponto)

Chamadas de função atómicas

Uma função como VLOOKUP ou INDEX/MATCH envolve múltiplos passos internos: procurar um intervalo, verificar uma condição, devolver um valor de uma coluna diferente. O TXLSFormulaTracer trata as chamadas de função como passos atómicos para as funções integradas do Excel — o passo mostra a chamada de função e o seu resultado, sem expandir os seus internos de implementação. Isso mantém a lista de passos legível. Para funções definidas pelo utilizador, cada chamada interna dentro da função é igualmente rastreada se a função chamar o motor de avaliação recursivamente

Referências de intervalos na lista de passos

Quando uma fórmula faz referência a um intervalo como B2:B10, o passo de rastreio correspondente mostra a referência como Source e o valor que resolve como um array de valores de célula. Isso permite ver exatamente que célula contribuiu com que valor para uma função de agregação como SUM ou AVERAGE. Se uma célula no intervalo contiver texto quando se espera um número, é visível no valor do passo desse intervalo antes de a função agregadora o ver

// Example output for SUM(B2:B10)*C1 where B2:B10 sums to 450 and C1 = 1.1:
//   SUM(B2:B10)*C1 -> 495
//     SUM(B2:B10) -> 450
//       B2:B10 -> {50,60,40,55,45,50,40,60,50}
//     C1 -> 1.1

A saída confirma que a multiplicação está a ser aplicada ao resultado SUM, não a cada elemento, e que os nove valores do intervalo somam 450 conforme esperado

Integrar o rastreio num fluxo de trabalho de depuração

O caso de uso mais comum é comparar a saída do tracer com o que o Excel mostra. Abra o ficheiro no Excel, use o "Mostrar Passos de Cálculo" da janela de avaliação de fórmulas e compare passo a passo com a saída do tracer. As discrepâncias apontam para o passo exato onde as duas implementações diferem. A causa é tipicamente uma referência de intervalo que se resolve de forma diferente porque a folha ativa está definida incorretamente, uma referência de folha cruzada, ou um tipo de função que uma implementação trata de forma diferente de outra

O tracer também é útil em testes automatizados. Uma suite de testes pode carregar um livro de referência, rastrear um conjunto de fórmulas conhecidas e afirmar que cada passo de rastreio produz o valor esperado. Isso deteta regressões no motor de avaliação antes de chegarem à produção

O rastreador de fórmulas é uma funcionalidade de diagnóstico no Componente HotXLS para Delphi e C++Builder. As capacidades de avaliação de fórmulas subjacentes, incluindo o motor de cálculo que o tracer expõe, são abrangidas nos artigos de fórmulas do componente. A leitura e escrita em modo de transmissão, que complementa a avaliação em memória para grandes workbooks, é descrita em leitura em modo de transmissão e escrita em modo de transmissão