Tracciare la valutazione delle formule Excel passo dopo passo in Delphi

Excel nasconde un piccolo debugger in bella vista. Selezionando una cella, aprendo Formule e facendo clic su Valuta formula, una finestra di dialogo mostra la formula con una sottoespressione sottolineata. Premendo Valuta, quella sottoespressione collassa nel suo valore, poi viene sottolineata la successiva e si osserva una lunga espressione ridursi a un singolo numero, una riduzione alla volta. È il modo più rapido per scoprire quale ramo di un IF nidificato si sia effettivamente attivato, o quale riferimento abbia fornito un totale errato. HotXLS riproduce esattamente questo comportamento tramite TXLSFormulaTracer, cosicché un programma Delphi o C++Builder può generare lo stesso elenco di passaggi per controllare una cartella di lavoro, eseguire il debug di una formula generata o mostrare a qualcuno perché un risultato è uscito in un certo modo. Ogni passaggio registrato riporta il testo della sottoespressione e il valore a cui si riduce

Come il motore di riduzione percorre l'espressione

Il tracciante non entra nel motore di calcolo. Tokenizza la formula e la analizza con un parser a discesa ricorsiva, poi riduce l'albero in profondità (depth-first), partendo dalla sottoespressione valutabile più interna. Quando un nodo si riduce a un valore, tale valore viene sostituito nell'espressione circostante come letterale, e il motore chiede al calcolatore reale di ricalcolare l'espressione ora più semplice. Poiché ogni passaggio viene valutato attraverso il metodo pubblico Calculate del foglio di lavoro anziché con una scorciatoia privata, ogni passaggio concorda esattamente con ciò che produrrebbe un ricalcolo completo della cella. Il parser è progettato per non essere invasivo, il che gli consente di funzionare su qualsiasi foglio di lavoro senza disturbarne lo stato

Il parser segue una scala di precedenza degli operatori, con un livello ricorsivo per ogni fascia di precedenza. Dal vincolo più basso al più alto, le fasce sono: confronto di livello 0 (=, <>, <, >, <=, >=), concatenazione di stringhe di livello 1 (&), addizione e sottrazione di livello 2, moltiplicazione e divisione di livello 3, esponenziazione di livello 4 e infine più e meno unari al di sotto. Ogni livello analizza il livello superiore per i suoi operandi, perciò una fascia più alta ha un vincolo più stretto. Questa è la stessa precedenza applicata da Excel, motivo per cui A1*B1+A2*B1 riduce i due prodotti prima della somma: la moltiplicazione si trova al livello 3, l'addizione al livello 2, perciò le moltiplicazioni si trovano più in profondità nell'albero e si riducono prima

Tracciare una formula e percorrere i passaggi

L'utilizzo rispecchia la demo fornita in Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Si costruisce un foglio di lavoro (o si apre una cartella di lavoro esistente), si crea un tracciante sul foglio, si chiama Trace e si itera l'array restituito. Ogni TXLSFormulaStep espone Depth per l'indentazione, Source per la sottoespressione originale, Expression per quella sottoespressione con i suoi operandi già sostituiti e Value per il risultato del passaggio

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 unità
    Sheet.Cells[1, 2].Value := 25;    // B1 prezzo unitario
    Sheet.Cells[1, 3].Value := 0.08;  // C1 aliquota fiscale

    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;

I riferimenti alle celle si risolvono per primi e appaiono come passaggi a sé stanti, poi si riducono i prodotti, poi il fattore fiscale tra parentesi, e la moltiplicazione finale chiude il tutto. Il campo Depth consente di indentare in modo che le riduzioni più interne si trovino visibilmente più in profondità, esattamente come Excel sottolinea il termine più interno prima di qualsiasi termine esterno

La trappola del letterale indipendente dal locale

Il dettaglio più pericoloso in tutto questo schema è invisibile su una macchina inglese e si rompe in modo clamoroso su una tedesca. Quando un numero calcolato viene sostituito nel testo della formula, deve essere scritto come una stringa e poi rianalizzato dal motore di calcolo, che tratta . come punto decimale. Se la sostituzione usasse le impostazioni locali del sistema, un TFormatSettings tedesco scriverebbe 1,08 per il fattore fiscale, la virgola verrebbe letta come separatore di argomenti, e il ricalcolo di A1*B1*1,08 verrebbe analizzato in modo errato o fallirebbe del tutto

Il tracciante evita questo problema formattando ogni letterale numerico tramite un TFormatSettings privato che fissa al momento della costruzione, con DecimalSeparator forzato a . e ThousandSeparator impostato su #0 in modo che non venga mai emesso alcun carattere di raggruppamento. FloatToStr produce quindi un letterale che il motore può sempre rileggere, indipendentemente dalle impostazioni regionali dell'operatore

// Concettualmente ciò che il tracer fissa una volta, alla costruzione
FFloatFmt := FormatSettings;
FFloatFmt.DecimalSeparator := '.';
FFloatFmt.ThousandSeparator := #0;
// ogni numero ridotto viene scritto con: FloatToStr(Double(V), FFloatFmt)

Questo è il tipo di bug che non compare mai nei test dell'autore e affiora solo quando un cliente in un altro locale esegue lo stesso codice, quindi vale la pena affermarlo chiaramente: compiere un viaggio di andata e ritorno di un valore attraverso il testo della formula è un problema di serializzazione, e la serializzazione deve essere indipendente dalle impostazioni locali

I booleani si riducono a 1 e 0

Una decisione di sostituzione correlata riguarda i valori logici. Quando una sottoespressione viene valutata come un booleano, il tracciante la riscrive come 1 o 0, non come TRUE o FALSE. Il motivo è che il letterale ridotto deve essere rianalizzato in modo pulito in qualsiasi contesto lo circondi, e l'aritmetica è il caso più esigente. Se un confronto come A1>A2 si riducesse al testo TRUE e quel testo finisse all'interno di TRUE*B1, il ricalcolo dipenderebbe dall'accettazione da parte del motore di una semplice parola chiave booleana in una moltiplicazione. Sostituire con 1 elude completamente la questione, poiché 1*B1 è inequivocabile in qualsiasi posizione aritmetica. Corrisponde anche alla coercizione di Excel, in cui TRUE si comporta come 1 e FALSE come 0 nel momento in cui ci si aspetta un numero

Le chiamate di funzione si riducono in modo atomico

Un motore passo-passo ingenuo ridurrebbe prima gli argomenti di una funzione e poi la chiamata. Questo è sbagliato per Excel, e il tracciante evita deliberatamente di farlo. Una chiamata di funzione viene valutata nel suo insieme, a partire dal suo testo originale, in un singolo passaggio. Il motivo è la semantica di corto circuito. IF, CHOOSE e IFERROR valutano solo il ramo che selezionano, e ridurre prima gli argomenti costringerebbe il motore a calcolare rami che Excel non tocca mai. La vittima classica è una protezione contro la divisione per zero come IF(B1=0,0,A1/B1): se il tracciante riducesse A1/B1 prima di valutare l'IF, la protezione fallirebbe e solleverebbe proprio l'errore che esiste per prevenire. Valutando l'intera chiamata in modo atomico, il tracciante preserva la valutazione pigra (lazy evaluation) che fa funzionare tali protezioni

// IF è un singolo passo atomico; viene valutato solo il ramo selezionato
Final := Tracer.Trace('IF(A1>A2,A1*B1,A2*B1)', Steps);
// A1>A2 è vero, quindi il passo registra A1*B1 come risultato scelto;
// A2*B1 non viene mai calcolato, esattamente come farebbe Excel.

Il compromesso è che non si vede all'interno della chiamata di funzione come passaggi separati, ma questo è il comportamento corretto. Mostrare riduzioni di argomenti che Excel non esegue mai sarebbe una traccia più fuorviante rispetto al trattare la chiamata come la singola unità di valutazione che è in realtà

Separatori di argomenti e intervalli intatti

Altre due normalizzazioni mantengono il ricalcolo onesto. Il compilatore del motore di calcolo si aspetta ; come separatore degli argomenti della funzione, quindi quando il tracciante ricostruisce una chiamata di funzione dal suo albero analizzato, unisce gli argomenti con ;, anche se l'utente aveva originariamente digitato ,. Una formula scritta come SUM(A1,A2,A3) viene ricalcolata come SUM(A1;A2;A3), che il motore accetta. La sostituzione dei valori è ciò che rende necessaria questa ricostruzione, e azzeccare il separatore è ciò che rende possibile l'analisi della ricostruzione

I riferimenti agli intervalli (range) sono l'altro caso. Un intervallo come A1:A3 non è uno scalare e non deve essere diviso in tre valori separati, perché la funzione che lo consuma si aspetta un argomento intervallo. Il tracciante mantiene un intervallo intatto come nel suo testo originale e lascia che la funzione racchiudente si riduca nel suo complesso. In SUM(A1:A3)*B1 l'intervallo rimane intero, SUM(A1:A3) si riduce a un numero in un passaggio atomico, e solo allora viene eseguita la moltiplicazione esterna. Questo è lo stesso confine che Excel traccia tra un operando intervallo e lo scalare a cui alla fine contribuisce

// L'intervallo A1:A3 non viene mai spezzato; SUM è una singola riduzione atomica,
// poi il prodotto con B1 si riduce sopra di essa.
Final := Tracer.Trace('SUM(A1:A3)*B1', Steps);
for I := 0 to High(Steps) do
  Writeln(Steps[I].Source, ' = ', VarToStr(Steps[I].Value));

Messe insieme, queste regole rendono l'elenco dei passaggi uno specchio fedele del comando Valuta formula di Excel piuttosto che una sua approssimazione. Le riduzioni avvengono nell'ordine in cui le esegue Excel, i letterali sostituiti sopravvivono a qualsiasi locale, i booleani subiscono la coercizione nel modo in cui Excel li costringe e le funzioni pigre rimangono pigre. Se si desidera spingere il motore oltre con le proprie funzioni, l'articolo sul motore delle formule e le funzioni personalizzate mostra come registrarle, e per lavori numerici più pesanti l'articolo sulle funzioni di distribuzione statistica in Delphi copre la libreria integrata contro cui il tracciante viene valutato. Tutto ciò viene fornito come parte di HotXLS Delphi spreadsheet component per Delphi e C++Builder, insieme alle API di lettura, scrittura, formattazione e calcolo trattate altrove su questo blog