Notazione di formule R1C1 in Delphi con HotXLS

HotXLS compila le formule scritte nella notazione di riferimento R1C1 tramite TXLSCalculator.GetCompiledFormulaR1C1, che accetta forme come R2C3 (assoluta), R[-1]C[2] (scostamento relativo) e RC (la cella corrente), le converte in notazione A1 rispetto alla riga e alla colonna della cella in cui la formula risiede, e passa il risultato allo stesso compilatore che gestisce le normali formule A1. Per il codice Delphi e C++Builder che genera la stessa formula su centinaia di righe, quel singolo metodo elimina un'intera classe di difetti da concatenazione di stringhe

La classe di difetti è familiare a chiunque abbia riempito una colonna in modo programmatico. Ciclate sulle righe, e per ogni riga costruite una stringa di formula A1 con Format('D%d*E%d', [Row, Row]). Ogni iterazione innesta numeri di riga nel testo, e i numeri di riga sono l'unica parte che cambia. Sbagliate lo scostamento una volta, mescolate un contatore di ciclo in base 0 con i numeri di riga A1 in base 1, oppure spostate il blocco dati di una riga di intestazione, e ogni formula della colonna punta una riga più in là. Non viene sollevato nulla; i numeri sono semplicemente sbagliati. La formula che intendevate davvero, "moltiplica le due celle alla mia sinistra", non menziona affatto un numero di riga, e la notazione R1C1 vi permette di scriverla proprio così

Che cos'è la notazione R1C1 e quando conviene usarla?

La notazione R1C1 indirizza le celle per numero di riga e di colonna invece che per lettera di colonna più numero di riga, e marca i riferimenti relativi come scostamenti espliciti dalla cella della formula. R2C3 è la cella assoluta alla riga 2, colonna 3, che in A1 si scrive $C$2. R[-1]C[2] è una riga sopra e due colonne a destra di dove si trova la formula. RC è la cella della formula stessa. Gli scostamenti fra parentesi quadre sono il punto: un riferimento relativo in R1C1 si legge allo stesso modo qualunque cella lo ospiti, mentre la resa A1 di quello stesso riferimento cambia a ogni riga

La notazione ripaga in un solo scenario, ed è uno scenario comune: la generazione da modello, dove la stessa formula relativa va piantata in ogni riga di una regione dati. In A1 dovete rirendere il testo della formula riga per riga. In R1C1 il testo è una costante. È anche più vicino al modo in cui i formati di file per fogli di calcolo ragionano internamente: i record di formula condivisa memorizzano i riferimenti relativi come scostamenti di riga e colonna dalla cella ospite, quindi una stringa A1 per riga è qualcosa che il vostro codice sintetizza solo perché il parser la scomponga subito di nuovo in scostamenti. R1C1 salta il giro di andata e ritorno. Per le formule interattive scritte da persone A1 resta la scelta naturale, ed è per questo che rimane il valore predefinito ovunque in HotXLS

Come compila HotXLS una formula R1C1?

HotXLS espone la funzionalità a due livelli, aggiunti nella v2.175.0. TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula è quello che la maggior parte del codice chiama: restituisce una formula compilata pronta per la valutazione, esattamente come il suo gemello A1 GetCompiledFormula, ma con due parametri in più che indicano la riga e la colonna in base 0 della cella a cui la formula appartiene. Sotto di esso, TXLSFormula.GetCompiledR1C1 produce l'albero sintattico grezzo, e una funzione autonoma R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String esegue la conversione vera e propria della notazione. La pipeline è deliberatamente semplice: tradurre il testo R1C1 in testo A1 equivalente usando le coordinate della cella ospite, poi compilare il testo A1 con il motore esistente — lo stesso motore che risolve nomi definiti e riferimenti fra fogli e smista le funzioni personalizzate di foglio

Poiché la conversione avviene prima della compilazione, tutto ciò che sta a valle si comporta come se aveste scritto voi stessi la formula A1. R1C1ToA1('R2C3', 4, 3) restituisce '$C$2' a prescindere dalla cella ospite, perché entrambe le coordinate sono assolute. R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3) — una formula che vive in D5, dato che CurRow = 4 e CurCol = 3 sono in base 0 — restituisce 'SUM(D2:D4)': gli scostamenti sono stati risolti rispetto alla riga 5, colonna D, ed emessi come semplici riferimenti A1 relativi. Gli intervalli non richiedono un trattamento speciale; i due punti passano oltre e ciascun estremo si converte in modo indipendente

// La formula vive in D5: CurRow = 4, CurCol = 3 (entrambi in base 0)
S := R1C1ToA1('R2C3', 4, 3);
// S = '$C$2'  (riga e colonna assolute)

S := R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3);
// S = 'SUM(D2:D4)'  (scostamenti risolti rispetto a D5)

S := R1C1ToA1('ROUND(R[-1]C[0], 2)', 4, 3);
// S = 'ROUND(D4, 2)'  (la R di ROUND resta intatta)

Riempire una colonna con una sola formula relativa

Il vantaggio si vede nel ciclo. Confrontate la versione A1, che rirende il testo della formula a ogni iterazione, con la versione R1C1, dove la formula è una costante e si muovono solo le coordinate ospiti. Entrambe compilano attraverso TXLSCalculator e si valutano con GetValue; il calcolatore riceve alla costruzione una callback fornitrice di celle, così il valutatore può prelevare i valori delle celle dalla vostra sorgente dati

// Stile A1: una stringa di formula diversa per ogni riga
for Row := 1 to 500 do
begin
  FormulaText := Format('D%d*E%d', [Row + 1, Row + 1]);  // righe A1 in base 1
  Compiled := Calc.GetCompiledFormula(FormulaText, 0);
  // ... valutate, memorizzate, liberate ...
end;

const
  AmountFormula = 'RC[-2]*RC[-1]';  // due celle a sinistra, stessa riga
var
  Calc: TXLSCalculator;
  Compiled: TXLSCompiledFormula;
  Value: Variant;
  Row: Integer;
begin
  Calc := TXLSCalculator.Create(nil, Provider.GetValue);
  try
    for Row := 1 to 500 do
    begin
      Compiled := Calc.GetCompiledFormulaR1C1(AmountFormula, 0, Row, 5);
      try
        if Calc.GetValue(0, Compiled, Row, 5, Value, 1) = lxOk then
          StoreResult(Row, 5, Value);
      finally
        Compiled.Free;
      end;
    end;
  finally
    Calc.Free;
  end;
end;

Il ciclo R1C1 non ha alcuna aritmetica di riga nel testo della formula. 'RC[-2]*RC[-1]' significa "stessa riga, due colonne a sinistra, per stessa riga, una colonna a sinistra" tanto alla riga 2 quanto alla riga 500, e se in seguito il blocco dati scende di una riga di intestazione, la costante della formula non cambia — cambiano solo i limiti del ciclo. La versione A1 ha due punti in cui sbagliare la correzione + 1; la versione R1C1 non ne ha nessuno

Quali forme R1C1 accetta il convertitore?

Il convertitore R1C1ToA1 riconosce le forme documentate per la v2.175.0: R[n]C[m] per scostamenti relativi in entrambe le direzioni, RnCm per riga e colonna assolute, R[-n]C[m] con scostamenti negativi, il semplice RC per la cella della formula stessa e intervalli come R1C1:R3C3 o R[-1]C:R[1]C. Le parti di riga e di colonna si analizzano in modo indipendente, quindi funzionano anche forme miste come R[1]C3 — riga relativa, colonna assoluta — e le lettere non distinguono maiuscole e minuscole, quindi r[-1]c[2] compila come il suo gemello maiuscolo. Le parti fra parentesi quadre diventano coordinate A1 non ancorate (relative); i numeri nudi diventano coordinate assolute ancorate con $

La domanda interessante è come il convertitore eviti di storpiare tutto il resto della formula, dato che R e C sono lettere comuni. La sua regola di discriminazione è basata sui token: una R viene considerata inizio di un riferimento solo quando non è preceduta da un'altra lettera, e il candidato deve poi analizzarsi completamente — parte di riga facoltativa, C obbligatoria, parte di colonna facoltativa — altrimenti il testo viene ripristinato intatto. È per questo che ROUND(R[-1]C[0], 2) converte solo il riferimento interno: la R di ROUND è seguita da O anziché da una cifra, una parentesi quadra o una C, quindi l'analisi fallisce e il nome della funzione passa oltre letteralmente. La stessa logica protegge ROW(), e i nomi di funzione che iniziano con C non sono mai candidati, dato che solo R apre un riferimento. Le note di rilascio della v2.175.0 dichiarano che vengono saltati anche i letterali stringa e gli identificatori; anche così, se un letterale fra apici nella vostra formula contiene per caso testo sagomato esattamente come un riferimento R1C1, vale la pena controllare una volta l'output compilato prima di fidarsene in produzione

Basi delle coordinate e dettagli di ancoraggio da conoscere

Dentro questa API si incontrano due convenzioni, e tenerle distinte evita l'unica trappola reale. I parametri CurRow e CurCol di GetCompiledFormulaR1C1 sono in base 0, seguendo la API del calcolatore, mentre i numeri dentro la notazione stessa sono in base 1, come li mostra Excel: R2C3 è riga 2, colonna 3, cioè $C$2, non $D$3. Se il vostro contatore di ciclo è già in base 0, lo passate direttamente come CurRow; il + 1 vive dentro il convertitore, non nel vostro codice

Un altro dettaglio conta se la formula compilata sopravviverà alla cella per cui è stata compilata. Quando omettete una parte di riga o di colonna — le forme RC[-1] o R[2]C — la coordinata omessa si risolve sulla cella ospite e viene emessa come coordinata assoluta ancorata con $ nel testo A1 convertito. Al momento della valutazione questo è invisibile, dato che il valore è lo stesso in entrambi i casi. Ma l'ancoraggio relativo contro assoluto decide come si spostano i riferimenti quando in seguito si inseriscono o si eliminano righe o colonne, come tratta l'articolo di accompagnamento sulla correzione dei riferimenti nelle formule. Se vi serve che una coordinata resti relativa attraverso le modifiche strutturali, scrivete lo scostamento in modo esplicito — R[0]C[-1] invece di RC[-1] — così il convertitore emette un riferimento A1 non ancorato

La compilazione R1C1 fa parte del motore di formule di HotXLS Delphi Excel Component, insieme al compilatore A1, al grafo di ricalcolo e alla API di valutazione mostrata sopra. Se il vostro codice costruisce fogli di calcolo ciclando formule lungo le colonne, spostare quei cicli dall'A1 innestato in stringhe a una sola costante R1C1 è uno dei miglioramenti di affidabilità più economici a disposizione