Rastrear Fórmulas Excel Passo a Passo em Delphi com HotXLS

O Excel esconde um pequeno depurador à vista de todos. Selecione uma célula, abra Fórmulas e clique em Avaliar Fórmula, e uma caixa de diálogo mostra a fórmula com uma subexpressão sublinhada. Prima Avaliar e essa subexpressão colapsa no seu valor, depois sublinha-se a seguinte, e vê-se uma expressão longa encolher até um único número, uma redução de cada vez. É a forma mais rápida de descobrir que ramo de um IF aninhado disparou na realidade, ou que referência alimentou um total errado. O HotXLS reproduz esse comportamento exato através de TXLSFormulaTracer, para que um programa Delphi ou C++Builder consiga apresentar a mesma lista de passos para auditar uma pasta de trabalho, depurar uma fórmula gerada, ou explicar a alguém porque é que um resultado saiu como saiu. Cada passo registado transporta o texto da subexpressão e o valor a que ela se reduz

Como o motor de redução percorre a expressão

O rastreador não mexe no motor de cálculo. Separa a fórmula em símbolos e analisa-a com um analisador de descida recursiva, e depois reduz a árvore em profundidade, primeiro a subexpressão avaliável mais interior. Quando um nó se reduz a um valor, esse valor é substituído de volta na expressão envolvente como literal, e o motor pede à calculadora verdadeira que recalcule a expressão agora mais simples. Como cada passo é avaliado através do método público Calculate da folha de cálculo e não por um atalho privado, cada passo concorda exatamente com o que um recálculo completo da célula produziria. O analisador é não invasivo por desenho, e é isso que lhe permite correr sobre qualquer folha de cálculo sem perturbar o seu estado

O analisador segue uma escada de precedência de operadores, com um nível recursivo por faixa de precedência. Da ligação mais fraca à mais forte, as faixas são: nível 0 comparação (=, <>, <, >, <=, >=), nível 1 concatenação de cadeias de caracteres (&), nível 2 adição e subtração, nível 3 multiplicação e divisão, nível 4 exponenciação, e por fim o mais e o menos unários abaixo disso. Cada nível analisa o nível acima dele para obter os seus operandos, pelo que uma faixa mais alta liga com mais força. Esta é a mesma precedência que o Excel aplica, e é por isso que A1*B1+A2*B1 reduz os dois produtos antes da soma: a multiplicação está no nível 3, a adição no nível 2, pelo que as multiplicações ficam mais fundo na árvore e reduzem primeiro

Rastrear uma fórmula e percorrer os passos

A utilização espelha a demonstração distribuída em Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Construa uma folha de cálculo (ou abra uma pasta de trabalho existente), crie um rastreador sobre a folha, chame Trace, e itere o array devolvido. Cada TXLSFormulaStep expõe Depth para a indentação, Source para a subexpressão original, Expression para essa subexpressão já com os operandos substituídos, e Value para o resultado do passo

uses
  SysUtils, Variants, lxHandle, lxHandleX, lxFormulaTrace;

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Tracer: TXLSFormulaTracer;
  Steps: TXLSFormulaStepArray;
  Final: Variant;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Order');
    Sheet.Cells[1, 1].Value := 10;    // A1 unidades
    Sheet.Cells[1, 2].Value := 25;    // B1 preço unitário
    Sheet.Cells[1, 3].Value := 0.08;  // C1 taxa de imposto

    Tracer := TXLSFormulaTracer.Create(Sheet);
    try
      Final := Tracer.Trace('A1*B1*(1+C1)', Steps);
      for I := 0 to High(Steps) do
        Writeln(StringOfChar(' ', Steps[I].Depth * 2),
                Steps[I].Source, ' -> ', Steps[I].Expression,
                ' = ', VarToStr(Steps[I].Value));
      Writeln('result = ', VarToStr(Final));
    finally
      Tracer.Free;
    end;
  finally
    Book.Free;
  end;
end;

As referências de célula resolvem-se primeiro e aparecem como passos próprios, depois reduzem-se os produtos, depois o fator de imposto entre parênteses, e a multiplicação final fecha o processo. O campo Depth permite indentar para que as reduções mais interiores fiquem visivelmente mais fundas, tal como o Excel sublinha o termo mais interior antes de qualquer termo exterior

A armadilha do literal independente da região

O detalhe mais perigoso de todo este esquema é invisível numa máquina inglesa e parte-se com estrondo numa alemã. Quando um número calculado é substituído de volta no texto da fórmula, tem de ser escrito como cadeia de caracteres e depois novamente analisado pelo motor de cálculo, que trata o . como separador decimal. Se a substituição usasse a região do sistema, um TFormatSettings alemão escreveria 1,08 para o fator de imposto, a vírgula seria lida como separador de argumentos, e o recálculo de A1*B1*1,08 ou seria analisado com a forma errada ou falharia por completo

O rastreador evita isto formatando cada literal numérico através de um TFormatSettings privado que fixa na construção, com DecimalSeparator forçado a . e ThousandSeparator definido como #0, para que nunca seja emitido um carácter de agrupamento. O FloatToStr produz então um literal que o motor consegue sempre voltar a ler, independentemente das definições regionais do operador

// Conceptualmente, o que o rastreador fixa uma vez, na construção
FFloatFmt := FormatSettings;
FFloatFmt.DecimalSeparator := '.';
FFloatFmt.ThousandSeparator := #0;
// cada número reduzido é escrito com: FloatToStr(Double(V), FFloatFmt)

Este é o tipo de erro que nunca aparece nos testes do próprio autor e só se manifesta quando um cliente noutra região corre o mesmo código, pelo que vale a pena dizê-lo com clareza: fazer um valor passar pelo texto da fórmula e voltar é um problema de serialização, e a serialização tem de ser independente da região

Os booleanos reduzem-se a 1 e 0

Uma decisão de substituição relacionada diz respeito aos valores lógicos. Quando uma subexpressão se avalia como booleano, o rastreador escreve-o de volta como 1 ou 0, e não como TRUE ou FALSE. A razão é que o literal reduzido tem de ser novamente analisado sem problemas em qualquer contexto que o rodeie, e a aritmética é o caso exigente. Se uma comparação como A1>A2 se reduzisse ao texto TRUE e esse texto aterrasse dentro de TRUE*B1, o recálculo dependeria de o motor aceitar uma palavra-chave booleana isolada numa multiplicação. Substituir por 1 contorna a questão por completo, porque 1*B1 é inequívoco em qualquer posição aritmética. Também corresponde à própria coerção do Excel, onde TRUE se comporta como 1 e FALSE como 0 no momento em que se espera um número

As chamadas de função reduzem-se atomicamente

Um motor de passos ingénuo reduziria primeiro os argumentos de uma função e só depois a chamada. Isso está errado para o Excel, e o rastreador deliberadamente não o faz. Uma chamada de função é avaliada como um todo, a partir do seu texto original, num único passo. A razão é a semântica de avaliação em curto-circuito. O IF, o CHOOSE e o IFERROR avaliam apenas o ramo que selecionam, e reduzir primeiro os argumentos obrigaria o motor a calcular ramos em que o Excel nunca toca. A vítima clássica é uma proteção contra divisão por zero como IF(B1=0,0,A1/B1): se o rastreador reduzisse A1/B1 antes de avaliar o IF, a proteção falharia e levantaria precisamente o erro que existe para evitar. Ao avaliar a chamada inteira de forma atómica, o rastreador preserva a avaliação preguiçosa que faz essas proteções funcionar

// O IF é um passo atómico; só o ramo selecionado é avaliado
Final := Tracer.Trace('IF(A1>A2,A1*B1,A2*B1)', Steps);
// A1>A2 é verdadeiro, pelo que o passo regista A1*B1 como resultado escolhido;
// A2*B1 nunca é calculado, exatamente como o Excel faria.

O compromisso é que não se vê o interior da chamada de função como passos separados, mas esse é o comportamento correto. Mostrar reduções de argumentos que o Excel nunca executa seria um rasto mais enganador do que tratar a chamada como a unidade de avaliação única que realmente é

Separadores de argumentos e intervalos intactos

Mais duas normalizações mantêm o recálculo honesto. O compilador do motor de cálculo espera ; como separador de argumentos de função, pelo que, quando o rastreador reconstrói uma chamada de função a partir da árvore analisada, junta os argumentos com ;, mesmo que o utilizador tenha originalmente escrito ,. Uma fórmula escrita como SUM(A1,A2,A3) é recalculada como SUM(A1;A2;A3), que o motor aceita. É a substituição de valores que torna esta reconstrução necessária, e é acertar no separador que faz a reconstrução ser analisada

As referências de intervalo são o outro caso. Um intervalo como A1:A3 não é um escalar e não pode ser dividido em três valores separados, porque a função que o consome espera um argumento de intervalo. O rastreador mantém o intervalo intacto no seu texto original e deixa a função envolvente reduzir-se como um todo. Em SUM(A1:A3)*B1 o intervalo mantém-se inteiro, SUM(A1:A3) reduz-se a um número num único passo atómico, e só então corre a multiplicação exterior. É a mesma fronteira que o Excel traça entre um operando de intervalo e o escalar com que ele acaba por contribuir

// O intervalo A1:A3 nunca é dividido; SUM é uma redução atómica,
// e só depois o produto com B1 se reduz por cima dela.
Final := Tracer.Trace('SUM(A1:A3)*B1', Steps);
for I := 0 to High(Steps) do
  Writeln(Steps[I].Source, ' = ', VarToStr(Steps[I].Value));

No conjunto, estas regras fazem da lista de passos um espelho fiel do comando Avaliar Fórmula do Excel, e não uma aproximação dele. As reduções acontecem pela ordem em que o Excel as executa, os literais substituídos sobrevivem a qualquer região, os booleanos convertem-se como o Excel os converte, e as funções preguiçosas continuam preguiçosas. Se quiser levar o motor mais longe com funções suas, o artigo sobre o motor de fórmulas e funções personalizadas mostra como as registar, e para trabalho numérico mais pesado o artigo sobre funções de distribuição estatística em Delphi descreve a biblioteca incorporada contra a qual o rastreador avalia. Tudo isto faz parte do componente de folha de cálculo HotXLS para Delphi para Delphi e C++Builder, a par das APIs de leitura, escrita, formatação e cálculo tratadas noutros artigos deste blogue