Articolo tecnico

LAMBDA e LET in Delphi: closure di formula in HotXLS

La cella contiene =NetOf(B2, C2), B2 vale 1250, C2 vale 0.19, Excel mostra 1012.5, e la cartella di lavoro non contiene né un modulo VBA né un add-in. NetOf vive nel Name Manager: è un nome definito il cui testo è LAMBDA(amount, rate, amount*(1-rate)). Un calcolatore lato server che tratta ogni nome definito come un intervallo o una costante non trova nulla da chiamare lì, e la cella torna indietro come #NAME?

Far andare quella cella richiede più di un parser che accetta la parola LAMBDA. Un valutatore di fogli di calcolo classico conosce cinque tipi di valore: numeri, testo, booleani, errori e riferimenti a celle che li contengono. LAMBDA ne aggiunge un sesto, un valore funzione che porta con sé i nomi dei parametri, il body e i nomi visibili dove è stata scritta. LET è l'altra metà della storia, perché LET è dove gli utenti legano quelle funzioni a nomi locali brevi e le chiamano pochi token dopo, spesso tre livelli di profondità

Tre posti in cui il nome di una funzione può risolversi

Quando il motore di formule di HotXLS incontra una chiamata a un nome che non è una funzione incorporata, prova tre sorgenti in un ordine fisso. La prima è lo scope LET o LAMBDA corrente: se f è un binding locale che contiene una closure, f(21) la applica. La seconda è un nome definito della cartella di lavoro il cui testo inizia con LAMBDA, così NetOf(1250, 0.19) compila il body del nome e lo applica ai due argomenti. La terza è il gestore di funzioni utente che forse già usi per le funzioni Delphi personalizzate, descritto in funzioni personalizzate nel motore di formule di HotXLS. Solo quando tutti e tre passano la cella diventa #NAME?

L'ordine conta in una situazione precisa. Un nome locale vince sempre su un nome della cartella di lavoro con la stessa grafia, anche quando il binding locale contiene un numero qualunque. In quel caso la ricerca tra i nomi definiti viene saltata del tutto, la chiamata va dritta al gestore di funzioni utente, e una LAMBDA della cartella di lavoro con lo stesso nome non viene mai consultata per quella chiamata

Diagramma dei tre percorsi di risoluzione delle chiamate HotXLS in Delphi provati in ordine per un nome di funzione sconosciuto come f(21): un binding di scope LET o LAMBDA locale che contiene una closure, un nome definito della cartella di lavoro il cui testo RefersTo inizia con LAMBDA, e il classico gestore di funzioni utente, mentre un binding locale che contiene un numero non viene mai applicato come funzione e la chiamata finisce in #NAME? quando nessun gestore di funzioni utente rivendica il nome
HotXLS prova prima lo scope locale, poi i nomi definiti LAMBDA, poi il classico handler di funzioni utente, e rifiuta di chiamare un binding che non custodisce una closure

Il lato Delphi è breve. Una funzione con nome è semplicemente un nome definito aggiunto con TXLSXDefinedNames.Add, e una closure dentro una sola formula non richiede nessuna registrazione:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Model');

    // Add a due argomenti = scope della cartella di lavoro, che è ciò che la chiamata per nome cerca
    Book.DefinedNames.Add('NetOf', 'LAMBDA(amount, rate, amount*(1-rate))');

    // 1250 * (1 - 0.19) = 1012.5
    Sheet.Cells[2, 2].Formula := 'NetOf(1250, 0.19)';

    // Una closure legata e applicata dentro una sola formula: 42
    Sheet.Cells[3, 2].Formula := 'LET(double, LAMBDA(x, x*2), double(21))';

    // bump cattura base = 100; bump(5) = 105, step*2 = 210
    Sheet.Cells[4, 2].Formula :=
      'LET(base, 100, bump, LAMBDA(v, v+base), LET(step, bump(5), step*2))';

    // Applicazione in forma postfix, nessun nome: 42
    Sheet.Cells[5, 2].Formula := 'LAMBDA(x, x*2)(21)';

    Book.Recalculate;
    Book.SaveAs('lambda-model.xlsx');
  finally
    Book.Free;
  end;
end;

L'ultima cella mostra una forma che si tende a dimenticare. Excel ti lascia applicare direttamente un'espressione LAMBDA scrivendo la lista degli argomenti subito dopo, e HotXLS parsa LAMBDA(x, x*2)(21) allo stesso modo. Il parser accetta quella lista di argomenti in coda solo dopo qualcosa che può produrre una funzione, come un nome, un'espressione LET o IF, o un'altra applicazione. Scrivere (1+2)(3) fallisce in fase di compilazione anziché in fase di valutazione

Cosa ricorda una closure, e perché vincono i parametri

Valutare un'espressione LAMBDA in HotXLS produce un valore closure: i nomi dei parametri, una copia del body, il foglio su cui è stata scritta, e un'istantanea di ogni binding LET o LAMBDA visibile in quel momento. Nella quarta cella qui sopra, bump viene creato mentre base è già legato a 100, quindi la closure porta con sé base = 100. Quando bump(5) gira dentro il LET interno, il body vede ancora quel 100, anche se il LET interno non sa niente di base per nome

La chiamata poi avviene in tre passi. Le espressioni degli argomenti vengono valutate per prime, nello scope del chiamante. Poi viene impilato l'ambiente catturato, poi i parametri sopra di esso, e il body viene valutato. Le ricerche procedono dall'alto verso il basso, quindi un parametro chiamato rate nasconde un binding catturato chiamato rate, e un binding catturato nasconde tutto ciò che il chiamante per caso ha legato sotto lo stesso nome. Quando il body restituisce, o fallisce, lo scope viene riportato al punto in cui la chiamata era partita

Diagramma di una chiamata di chiusura LAMBDA HotXLS in Delphi: il valore di chiusura porta il suo parametro, il corpo e il binding di base catturato dal LET circostante, la chiamata impila l'ambiente catturato prima dell'argomento v = 5, il corpo valuta a 105, e lo stack degli scope si tronca al suo segno d'ingresso dentro una guardia finally
Chiamare una closure impila l'ambiente catturato prima dei binding degli argomenti, e quell'ordine è esattamente ciò che fa prevalere i parametri sui nomi esterni

Valutare gli argomenti prima che venga installato un qualsiasi binding sembra un dettaglio finché non scrivi LET(x, 10, f, LAMBDA(x, x+1), f(x*2)). L'argomento x*2 deve indicare la x esterna, che dà 20, e il risultato è 21. Se il parametro venisse installato per primo, l'argomento leggerebbe il parametro che sta per definire. La regola che ne scende è quella che gli utenti Excel si aspettano: ciò che una funzione con nome calcola dipende dai suoi argomenti e da ciò che ha catturato quando è stata scritta, e gli altri nomi locali del chiamante non vi filtrano dentro

Perché f(21) fallisce quando f contiene un numero?

Un binding locale che contiene qualcosa di diverso da una closure non viene mai applicato come funzione. LET(f, 3, f(21)) non moltiplica 3 per 21, e non ricade nemmeno su un nome della cartella di lavoro chiamato f. La chiamata passa al gestore di funzioni utente, e se nessun gestore rivendica il nome, la cella finisce come #NAME?. Quell'esito è quello giusto per un foglio di calcolo: un refuso che trasformasse in silenzio una chiamata di funzione in una moltiplicazione produrrebbe un numero plausibile che nessuno mette in dubbio

L'applicazione postfix è ancora più severa. Qualunque cosa stia davanti alla lista degli argomenti viene valutata, e se il risultato non è una closure la cella prende #VALUE!, così LET(g, 3, (g)(21)) e LET(g, 3, g(21)) falliscono con errori diversi. Altri fallimenti che puoi riprodurre in una cartella di lavoro zero:

  • Numero di argomenti sbagliato: LET(add, LAMBDA(a, b, a+b), add(1)) restituisce #VALUE! prima che l'espressione dell'argomento venga valutata
  • Gli errori dentro gli argomenti viaggiano come valori: in LET(safe, LAMBDA(x, IFERROR(x, 0)), safe(1/0)) il #DIV/0! viene legato a x e l'IFERROR dentro il body lo trasforma in 0
  • I riferimenti restano riferimenti: un argomento come A1 arriva al body come riferimento di cella, così le funzioni che guardano all'identità del riferimento, ISREF tra queste, vedono un riferimento invece del valore della cella
  • Una definizione LAMBDA accetta fino a 253 parametri, lo stesso tetto che Excel documenta per LAMBDA
  • La ricorsione sfrenata si ferma: una lambda con nome che chiama se stessa condivide un tetto predefinito di 256 livelli annidati con l'annidamento ordinario delle formule, e oltre quello la chiamata finisce con un errore di limite di risorse invece di esaurire lo stack Delphi
// Log è una tua routine di logging, non fa parte di HotXLS
procedure ProbeLambdaErrors(Sheet: TXLSXWorksheet; Book: TXLSXWorkbook);
begin
  Sheet.Cells[2, 4].Formula := 'LET(f, 3, f(21))';                       // #NAME? senza un gestore
  Sheet.Cells[3, 4].Formula := 'LET(g, 3, (g)(21))';                     // #VALUE!, postfix
  Sheet.Cells[4, 4].Formula := 'LET(add, LAMBDA(a, b, a+b), add(1))';     // #VALUE!, arità
  Sheet.Cells[5, 4].Formula := 'LET(safe, LAMBDA(x, IFERROR(x, 0)), safe(1/0))'; // 0
  Book.Recalculate;
  Log(Sheet.Cells[2, 4].FormattedText);
  Log(Sheet.Cells[3, 4].FormattedText);
  Log(Sheet.Cells[4, 4].FormattedText);
  Log(Sheet.Cells[5, 4].FormattedText);
end;

Binding LET in HotXLS: annidati e parziali

HotXLS valuta LET da sinistra a destra: ogni binding viene valutato con tutti i binding precedenti in scope, impilato, e l'ultimo argomento viene valutato sotto l'insieme completo. Così LET(a, 1, b, a+1, LET(c, b*2, c)) restituisce 4, e il LET interno vede sia a sia b. Quando il LET esterno finisce, i suoi binding sono già spariti, il che significa che una formula sorella nella cella successiva parte da uno scope vuoto

Due regole di forma vengono applicate prima che qualcosa venga eseguito. LET richiede un numero dispari di argomenti, almeno tre, quindi LET(a, 1) è #VALUE!. E ogni slot del nome deve essere un nome semplice: LET(A1, 5, A1*2) viene rifiutato perché A1 è un riferimento di cella, mentre nomi brevi come x o ab, che scrivono anche lettere di colonna, vengono letti come nomi locali dentro LET e LAMBDA. Un binding a un intervallo conserva l'intervallo, così LET(r, A1:A10, ROWS(r)) restituisce 10 e SUM(r) somma le celle

Gli stessi valori closure nutrono le funzioni di supporto costruite per loro: MAP, REDUCE, SCAN, MAKEARRAY, BYROW e BYCOL. MAP(A2:A6, LAMBDA(v, v*2)) produce cinque valori raddoppiati, e REDUCE(0, A2:A6, LAMBDA(acc, v, acc+v)) li ripiega in una somma sola. L'ambiente catturato viaggia dentro quelle chiamate esattamente come per una chiamata diretta, quindi un tasso legato con LET resta visibile dentro il body del MAP

Virgole, punti e virgola e cosa scrive SaveAs

Il parser di formule di HotXLS accetta virgola e punto e virgola come separatori di argomenti in modo intercambiabile. Il testo delle formule arriva da posti che non controlli: un ticket di supporto scritto su una macchina con locale tedesco, documentazione che usa le virgole canoniche di Excel, uno script che genera stringhe di formula, codice HotXLS più vecchio che usava i punti e virgola ovunque. SUM(A1,A2) e SUM(A1;A2) compilano allo stesso modo, e così anche una LAMBDA scritta in uno dei due modi

Le costanti di array inline sono l'eccezione. Dentro le graffe il punto e virgola conserva il suo significato Excel di separatore di riga, così {1,2;3,4} è un array due per due, non una lista di quattro argomenti. Se generi formule con punti e virgola come separatori di argomenti, tieni le costanti di array nella sintassi di Excel e i due non collidono mai

Cell.Formula restituisce il testo che hai assegnato. Quando SaveAs scrive un file XLSX, i punti e virgola usati come separatori di argomenti fuori dalle graffe vengono scritti come virgole, perché il formato file OOXML memorizza le formule nella sintassi a virgole di Excel, e le funzioni che Excel memorizza con un prefisso _xlfn. prendono quel prefisso in uscita. Una formula caricata da un file e lasciata intatta viene riscritta dal suo testo XML originale

Leggere lambda con nome dalle cartelle di lavoro dei clienti

Le cartelle di lavoro scritte in Excel memorizzano i nomi LAMBDA nel modo in cui Excel li scrive: con un prefisso _xlfn. sul nome della funzione e spesso _xlpm. sui parametri. HotXLS toglie quei prefissi quando valuta il nome, ma TXLSXDefinedName.Formula restituisce il testo della definizione così come il file lo fornisce. Un controllo scritto come Copy(Name.Formula, 1, 6) = 'LAMBDA' passa per i nomi creati via codice e perde ogni nome uscito da Excel. Quella discrepanza è facile da non vedere, perché i valori calcolati sono corretti in entrambi i casi e sono solo le tue verifiche a discordare

// IsLambdaDefinition è un helper che aggiungi tu, non un'API di HotXLS
function IsLambdaDefinition(const Text: WideString): Boolean;
var
  S: WideString;
begin
  S := UpperCase(Trim(Text));
  if (S <> '') and (S[1] = '=') then
    Delete(S, 1, 1);
  if Copy(S, 1, 6) = '_XLFN.' then
    Delete(S, 1, 6);
  Result := Copy(S, 1, 7) = 'LAMBDA(';
end;

procedure AuditLambdas(const FileName: string);
var
  Book: TXLSXWorkbook;
  Name: TXLSXDefinedName;
  I: Integer;
begin
  // Log è una tua routine di logging
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(FileName) = 1 then
    begin
      for I := 0 to Book.DefinedNames.Count - 1 do
      begin
        Name := Book.DefinedNames[I];
        if IsLambdaDefinition(Name.Formula) and (Name.SheetIndex >= 0) then
          Log('Sheet-scoped lambda, not callable by name: ' + Name.Name);
      end;
      Book.Recalculate;
      Log(VarToStr(Book.Sheets[1].Cells[2, 2].Value));
    end;
  finally
    Book.Free;
  end;
end;

Il controllo di scope in quel ciclo c'è perché la chiamata per nome in HotXLS cerca il callee tra i nomi a livello di cartella di lavoro. Tieni le funzioni con nome allo scope della cartella di lavoro. Un nome LAMBDA con scope di foglio si può comunque leggere tramite FindByName(AName, ASheetIndex), ma una funzione che la gente chiama da più fogli appartiene alla cartella di lavoro, e le regole di scope dietro questo sono esposte in nomi definiti e formule cross-sheet

Modificare una lambda in seguito costa poco. Assegnare un nuovo testo a TXLSXDefinedName.Formula invalida il grafo delle dipendenze, quindi il prossimo Recalculate lo ricostruisce e rivaluta le celle che chiamano la funzione; la meccanica è in ricalcolo incrementale e grafo delle dipendenze. Una cella la cui formula valuta a una closure nuda, come =LAMBDA(x, x*2) senza chiamata, non riceve alcun valore in cache, dato che non c'è né numero né testo da memorizzare; Excel mostra #CALC! per la stessa cella. Per i report che devono aprirsi in strumenti senza supporto LAMBDA, congela i risultati con Sheet.Cells.ConvertFormulasToValues dopo l'ultimo Recalculate, che conserva ogni valore in cache e butta il testo della formula

LAMBDA, LET, le funzioni di supporto e l'API dei nomi definiti viaggiano tutti nella stessa libreria nativa per Delphi e C++Builder, che legge e scrive XLS, XLSX e ODS senza Excel installato. La lista completa delle funzioni di formula è sulla pagina del componente foglio di calcolo HotXLS