HotXLS compila le formule scritte nella notazione di riferimento R1C1 tramite il metodo TXLSCalculator.GetCompiledFormulaR1C1, che accetta forme come R2C3 (assoluto), R[-1]C[2] (offset relativo) e RC (la cella corrente), le converte nella notazione A1 in base alla riga e alla colonna della cella in cui risiede la formula, 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, questo singolo metodo elimina un'intera classe di bug legati alla concatenazione di stringhe
Questa classe di bug è familiare a chiunque abbia popolato una colonna a livello di programmazione. Si esegue un ciclo sulle righe e, per ciascuna riga, si compila una stringa di formula A1 con Format('D%d*E%d', [Row, Row]). Ogni iterazione inserisce i numeri di riga nel testo, e questi numeri rappresentano l'unica parte che cambia. Se si sbaglia l'offset anche una sola volta, si mescola un contatore di ciclo a base zero con i numeri di riga A1 a base 1, o si sposta il blocco dati di una riga di intestazione, ogni formula nella colonna punterà alla riga sbagliata. Non viene sollevata alcuna eccezione; i numeri risultano semplicemente errati. La formula che si intendeva effettivamente, ovvero "moltiplica le due celle alla mia sinistra", non menziona affatto un numero di riga, e la notazione R1C1 consente di scriverla proprio così
Cos'è la notazione R1C1 e quando dovrebbe essere utilizzata?
La notazione R1C1 fa riferimento alle celle tramite il numero di riga e colonna invece della lettera di colonna seguita dal numero di riga, e indica i riferimenti relativi come offset espliciti rispetto alla cella della formula. R2C3 è la cella assoluta alla riga 2, colonna 3, che in notazione A1 si scrive $C$2. R[-1]C[2] si trova una riga sopra e due colonne a destra rispetto alla cella in cui risiede la formula. RC rappresenta la cella della formula stessa. Gli offset tra parentesi quadre sono l'elemento fondamentale: un riferimento relativo in R1C1 rimane identico indipendentemente dalla cella in cui risiede, mentre la rappresentazione A1 di quello stesso riferimento cambia con ogni riga
Questa notazione si rivela particolarmente utile in uno scenario specifico e molto comune: la generazione di modelli, in cui la stessa formula relativa deve essere inserita in ogni riga di un'area dati. In notazione A1 è necessario rigenerare il testo della formula per ciascuna riga. In R1C1, il testo è una costante. Questo approccio riflette più da vicino il modo in cui i formati di file dei fogli di calcolo operano internamente: i record delle formule condivise memorizzano i riferimenti relativi come offset di riga e colonna rispetto alla cella ospite, per cui una stringa A1 generata per riga è un elemento sintetizzato dal codice al solo scopo di essere scomposto nuovamente dal parser in offset. La notazione R1C1 evita questo passaggio superfluo. Per le formule interattive scritte dagli utenti, la notazione A1 rimane la scelta naturale, motivo per cui è il valore predefinito ovunque in HotXLS
In che modo HotXLS compila una formula R1C1?
HotXLS espone questa funzionalità a due livelli, introdotti nella versione v2.175.0. Il metodo TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula è quello utilizzato più frequentemente: restituisce una formula compilata pronta per la valutazione, proprio come il suo equivalente A1 GetCompiledFormula, per cui l'analizzatore riceve ed elabora due parametri aggiuntivi che specificano la riga e la colonna (a base zero) della cella a cui appartiene la formula. Sotto di esso, TXLSFormula.GetCompiledR1C1 produce l'albero sintattico grezzo, e una funzione indipendente R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String esegue l'effettiva conversione di notazione. Il processo è volutamente semplice: traduce il testo R1C1 nel testo A1 equivalente utilizzando le coordinate della cella ospite, quindi compila il testo A1 tramite il motore esistente — lo stesso che risolve i nomi definiti e i riferimenti tra fogli diversi e gestisce le funzioni personalizzate del foglio di lavoro
Poiché la conversione avviene prima della compilazione, tutto ciò che si trova a valle si comporta come se la formula A1 fosse stata scritta manualmente. La funzione R1C1ToA1('R2C3', 4, 3) restituisce '$C$2' indipendentemente dalla cella ospite, poiché entrambe le coordinate sono assolute. R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3) — una formula situata in D5, poiché CurRow = 4 e CurCol = 3 sono a base zero — restituisce 'SUM(D2:D4)': gli offset sono stati risolti rispetto alla riga 5, colonna D, ed emessi come riferimenti A1 relativi semplici. Gli intervalli non richiedono una gestione speciale; i due punti vengono conservati e ciascun punto finale viene convertito in modo indipendente
// The formula lives in D5: CurRow = 4, CurCol = 3 (both 0-based)
S := R1C1ToA1('R2C3', 4, 3);
// S = '$C$2' (absolute row and column)
S := R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3);
// S = 'SUM(D2:D4)' (offsets resolved against D5)
S := R1C1ToA1('ROUND(R[-1]C[0], 2)', 4, 3);
// S = 'ROUND(D4, 2)' (the R in ROUND is left alone)Popolare una colonna con un'unica formula relativa
Il vantaggio principale si nota all'interno del ciclo. Confronta la versione A1, che rigenera il testo della formula ad ogni iterazione, con la versione R1C1, in cui la formula è una costante e si spostano unicamente le coordinate ospiti. Entrambe vengono compilate tramite TXLSCalculator e valutate con GetValue; il calcolatore accetta una callback per la fornitura delle celle in fase di creazione in modo che l'analizzatore possa recuperare i valori delle celle dalla sorgente dati
// A1 style: a different formula string for every row
for Row := 1 to 500 do
begin
FormulaText := Format('D%d*E%d', [Row + 1, Row + 1]); // 1-based A1 rows
Compiled := Calc.GetCompiledFormula(FormulaText, 0);
// ... evaluate, store, free ...
end;const
AmountFormula = 'RC[-2]*RC[-1]'; // two cells to the left, same row
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 presenta alcuna aritmetica di riga all'interno del testo della formula. La stringa 'RC[-2]*RC[-1]' significa "stessa riga, due colonne a sinistra, moltiplicato per stessa riga, una colonna a sinistra" sia alla riga 2 che alla riga 500, e se il blocco dati si sposta successivamente a causa di una riga di intestazione, la costante della formula non cambia — cambiano solo i limiti del ciclo. La versione A1 offre due opportunità di errore nella gestione dell'adeguamento + 1; la versione R1C1 nessuna
Quali forme R1C1 sono accettate dal convertitore?
Il convertitore R1C1ToA1 riconosce le forme documentate per la versione v2.175.0: R[n]C[m] per offset relativi in entrambe le direzioni, RnCm per righe e colonne assolute, R[-n]C[m] con offset negativi, la forma semplice RC per la cella stessa della formula, e intervalli come R1C1:R3C3 o R[-1]C:R[1]C. I componenti di riga e colonna vengono analizzati in modo indipendente, quindi funzionano anche forme miste come R[1]C3 — riga relativa, colonna assoluta — e le lettere sono insensibili alle maiuscole, per cui r[-1]c[2] viene compilato esattamente come il suo equivalente in maiuscolo. Le parti tra parentesi quadre diventano coordinate A1 non ancorate (relative); i numeri semplici diventano coordinate assolute ancorate con il simbolo $
Una questione interessante è come il convertitore eviti di alterare altri elementi della formula, dato che R e C sono lettere comuni. La regola di discriminazione si basa sui token: una lettera R viene considerata l'inizio di un riferimento solo se non è preceduta da un'altra lettera, e il candidato deve poi essere analizzato completamente — riga opzionale, C obbligatorio, colonna opzionale — altrimenti il testo viene conservato inalterato. Questo è il motivo per cui ROUND(R[-1]C[0], 2) converte solo il riferimento interno: la lettera R in ROUND è seguita da O invece che da una cifra, una parentesi o la lettera C, quindi l'analisi fallisce e il nome della funzione viene ignorato. La stessa logica protegge la funzione ROW(), e i nomi di funzioni che iniziano con C non vengono mai considerati candidati, poiché solo R avvia un riferimento. Le note di rilascio della versione v2.175.0 indicano che vengono saltati anche i valori letterali stringa e gli identificatori; tuttavia, se un valore stringa racchiuso tra virgolette nella formula contiene testo strutturato esattamente come un riferimento R1C1, vale la pena verificare l'output compilato una prima volta prima di utilizzarlo in produzione
Basi delle coordinate e dettagli di ancoraggio utili
Due convenzioni si incontrano in questa API, ed essere precisi evita l'unico potenziale problema. I parametri CurRow and CurCol di GetCompiledFormulaR1C1 sono a base zero, seguendo l'API del calcolatore, mentre i numeri all'interno della notazione stessa sono a base 1, rispecchiando ciò che visualizza Excel: R2C3 indica la riga 2, colonna 3, che corrisponde a $C$2 e non a $D$3. Se il contatore del ciclo è già a base zero, lo si passa direttamente come CurRow; il valore + 1 è gestito internamente dal convertitore, non dal tuo codice
Un ulteriore dettaglio è importante se la formula compilata sopravviverà alla cella per cui è stata compilata. Quando si omette un componente di riga o colonna — ad esempio le forme RC[-1] o R[2]C — la coordinata omessa si risolve nella cella ospite e viene emessa come coordinata assoluta ancorata con $ nel testo A1 convertito. Al momento della valutazione questo è impercettibile, poiché il valore calcolato è lo stesso in entrambi i casi. Tuttavia, l'ancoraggio relativo o assoluto determina il modo in cui i riferimenti si spostano in caso di inserimento o eliminazione successivi di righe o colonne, come trattato nell'articolo correlato sull'adeguamento dei riferimenti di formula. Se si desidera che una coordinata rimanga relativa durante le modifiche strutturali, scrivere l'offset in modo esplicito — R[0]C[-1] invece di RC[-1] — in modo che il convertitore emetta un riferimento A1 non ancorato
Ricalcolare dopo lo spostamento
La compilazione R1C1 fa parte del motore delle formule del Componente HotXLS Delphi Excel, insieme al compilatore A1, al grafo di ricalcolo e all'API di valutazione mostrati sopra. Se il tuo codice crea fogli di calcolo eseguendo cicli di formule lungo le colonne, convertire tali cicli da espressioni A1 composte a una singola costante R1C1 rappresenta uno dei miglioramenti di affidabilità più immediati e convenienti disponibili