R1C1 Formula Notation in Delphi with HotXLS

HotXLS compile des formules écrites en notation de référence R1C1 via TXLSCalculator.GetCompiledFormulaR1C1, qui accepte des structures comme R2C3 (absolu), R[-1]C[2] (décalage relatif) et RC (la cellule active), les convertit en notation A1 selon la ligne et la colonne de la cellule où réside la formule, et transmet le résultat au même compilateur qui gère les formules A1 ordinaires. Pour le code Delphi et C++Builder qui génère la même formule sur des centaines de lignes, cette méthode élimine toute une classe de bugs de concaténation de chaînes

Cette classe de bugs est familière à quiconque a rempli une colonne par programmation. Vous bouclez sur les lignes et, pour chaque ligne, vous construisez une chaîne de formule A1 avec Format('D%d*E%d', [Row, Row]). Chaque itération injecte des numéros de ligne dans le texte, et ces numéros constituent la seule partie variable. Un seul décalage erroné, le mélange d'un compteur de boucle basé sur 0 avec des numéros de ligne A1 basés sur 1, ou le décalage du bloc de données par une ligne d'en-tête, et chaque formule de la colonne pointe vers la mauvaise ligne. Rien ne lève d'erreur ; les nombres obtenus sont simplement faux. La formule que vous vouliez réellement, « multiplier les deux cellules à ma gauche », ne mentionne jamais aucun numéro de ligne, et la notation R1C1 permet de l'écrire ainsi

Qu'est-ce que la notation R1C1 et quand faut-il l'utiliser ?

La notation R1C1 cible les cellules par leur numéro de ligne et de colonne au lieu de la lettre de colonne complétée par le numéro de ligne, et elle marque les références relatives sous forme de décalages explicites par rapport à la cellule contenant la formule. R2C3 est la cellule absolue à la ligne 2, colonne 3, que la notation A1 écrit $C$2. R[-1]C[2] se situe une ligne au-dessus et deux colonnes à droite de l'emplacement de la formule. RC représente la cellule de la formule elle-même. Ces décalages entre crochets constituent tout l'intérêt de la chose : une référence relative en R1C1 s'écrit de la même manière quelle que soit la cellule qui l'héberge, alors que le rendu A1 de cette même référence change pour chaque ligne

Cette notation se justifie pleinement dans un scénario précis, très courant : la génération de modèles, où la même formule relative doit être insérée dans chaque ligne d'une région de données. En A1, vous devez reconstruire le texte de la formule pour chaque ligne. En R1C1, le texte est une constante. C'est d'ailleurs plus proche de la façon dont les formats de fichiers de feuilles de calcul sont conçus en interne : les enregistrements de formules partagées stockent les références relatives sous forme de décalages de lignes et de colonnes par rapport à la cellule hôte. Une chaîne A1 par ligne est donc une structure que votre code synthétise uniquement pour que l'analyseur la décompose à nouveau en décalages. La notation R1C1 élimine cet aller-retour. Pour les formules irréprochables saisies par des humains, la notation A1 demeure le choix naturel, c'est pourquoi elle reste la notation par défaut partout dans HotXLS

Comment HotXLS compile-t-il une formule R1C1 ?

HotXLS expose cette fonctionnalité à deux niveaux, ajoutée dans la version v2.175.0. TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula est la fonction la plus appelée : elle renvoie une formule compilée prête pour l'évaluation, à l'instar de son équivalent A1 GetCompiledFormula, mais avec deux paramètres supplémentaires indiquant la ligne et la colonne (basées sur 0) de la cellule à laquelle appartient la formule. Sous cette fonction, TXLSFormula.GetCompiledR1C1 produit l'arbre syntaxique brut, et une fonction autonome R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String effectue la conversion de notation proprement dite. Le processus est délibérément simple : traduire le texte R1C1 en texte A1 équivalent en utilisant les coordonnées de la cellule hôte, puis compiler le texte A1 via le moteur existant — le même moteur qui résout les noms définis et les références croisées entre feuilles et distribue les fonctions personnalisées de feuille de calcul

La conversion se déroulant avant la compilation, tout le traitement en aval se déroule comme si vous aviez écrit la formule A1 vous-même. R1C1ToA1('R2C3', 4, 3) renvoie '$C$2' quelles que soient les coordonnées de la cellule hôte, car les deux coordonnées sont absolues. R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3) — une formule résidant en D5, puisque CurRow = 4 and CurCol = 3 sont basés sur 0 — renvoie 'SUM(D2:D4)' : les décalages ont été résolus par rapport à la ligne 5, colonne D, et émis sous forme de références A1 relatives simples. Les plages ne requièrent aucun traitement particulier ; le deux-points passe à travers et chaque extrémité est convertie indépendamment

Remplir une colonne avec une seule formule relative

Le gain se manifeste dans la boucle. Comparez la version A1, qui reconstruit le texte de la formule à chaque itération, avec la version R1C1, où la formule est une constante et seules les coordonnées de l'hôte changent. Les deux versions se compilent via TXLSCalculator et s'évaluent avec GetValue ; le calculateur prend une fonction de rappel de fournisseur de cellules à sa création afin que l'évaluateur puisse récupérer les valeurs des cellules depuis votre source de données

// 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;

La boucle R1C1 ne contient aucune arithmétique de ligne dans le texte de la formule. 'RC[-2]*RC[-1]' signifie « même ligne, deux colonnes à gauche, multiplié par même ligne, une colonne à gauche » à la ligne 2 comme à la ligne 500, et si le bloc de données est ultérieurement déplacé vers le bas par une ligne d'en-tête, la constante de formule ne change pas — seules les limites de la boucle changent. La version A1 présente deux opportunités de se tromper dans l'ajustement de la ligne + 1 ; la version R1C1 n'en présente aucune

Quelles formes R1C1 le convertisseur accepte-t-il ?

Le convertisseur R1C1ToA1 reconnaît les formes documentées pour la version v2.175.0 : R[n]C[m] pour les décalages relatifs dans les deux directions, RnCm pour les lignes et colonnes absolues, R[-n]C[m] avec des décalages négatifs, la forme brute RC pour la cellule de la formule elle-même, ainsi que des plages comme R1C1:R3C3 ou R[-1]C:R[1]C. Les parties ligne et colonne s'analysent indépendamment, de sorte que des formes mixtes comme R[1]C3 — ligne relative, colonne absolue — fonctionnent également, et les lettres sont insensibles à la casse (r[-1]c[2] se compile de la même manière que son équivalent en majuscules). Les parties entre crochets deviennent des coordonnées A1 relatives (sans ancre) ; les nombres nus deviennent des coordonnées absolues ancrées avec $

La question intéressante est de savoir comment le convertisseur évite de déformer tout le reste de la formule, car R et C sont des lettres courantes. Sa règle de distinction est basée sur les jetons : un R n'est considéré comme le début d'une référence que lorsqu'il n'est pas précédé d'une autre lettre, et la structure candidate doit ensuite être analysée entièrement — partie de ligne facultative, C obligatoire, partie de colonne facultative — sinon le texte est restitué inchangé. C'est pourquoi ROUND(R[-1]C[0], 2) ne convertit que la référence interne : le R de ROUND est suivi de O plutôt que d'un chiffre, d'un crochet ou de C, l'analyse échoue donc et le nom de la fonction passe textuellement. La même logique protège ROW(), et les noms de fonctions commençant par C ne sont jamais candidats puisque seul R commence une référence. Les notes de version v2.175.0 indiquent que les littéraux de chaînes et les identifiants sont également ignorés. Malgré cela, si un littéral entre guillemets dans votre formule contient du texte ayant précisément la forme d'une référence R1C1, il convient de vérifier la sortie compilée une fois avant de l'utiliser en production

Bases de coordonnées et détails d'ancrage à connaître

Deux conventions se rencontrent au sein de cette API, et les distinguer permet d'éviter le seul piège réel. Les paramètres CurRow et CurCol de GetCompiledFormulaR1C1 sont basés sur 0, suivant l'API du calculateur, tandis que les numéros dans la notation elle-même sont basés sur 1, correspondant à ce qu'affiche Excel : R2C3 désigne la ligne 2, colonne 3, qui correspond à $C$2 (et non $D$3). Si votre compteur de boucle est déjà basé sur 0, vous le transmettez directement comme CurRow ; le décalage + 1 est géré au sein du convertisseur, pas dans votre code

Un détail supplémentaire importe si la formule compilée doit survivre à la cellule pour laquelle elle a été compilée. Lorsque vous omettez une partie de ligne ou de colonne — les formes RC[-1] ou R[2]C — la coordonnée omise correspond à la cellule hôte et est émise sous forme de coordonnée absolue ancrée avec $ dans le texte A1 converti. Lors de l'évaluation, cela est invisible puisque la valeur reste la même. Toutefois, l'ancrage relatif ou absolu détermine la façon dont les références se décalent lors de l'insertion ou de la suppression ultérieure de lignes ou de colonnes, comme décrit dans l'article complémentaire sur l'ajustement des références de formule. Si vous avez besoin qu'une coordonnée reste relative lors des modifications de structure, écrivez explicitement le décalage — R[0]C[-1] au lieu de RC[-1] — afin que le convertisseur émette une référence A1 non ancrée

La compilation R1C1 fait partie du moteur de formule de HotXLS Delphi Excel Component, aux côtés du compilateur A1, du graphe de recalcul et de l'API d'évaluation présentée ci-dessus. Si votre code construit des feuilles de calcul en étirant des formules le long des colonnes, migrer ces boucles d'un texte A1 concaténé vers une constante R1C1 unique est l'une des améliorations de fiabilité les plus simples à réaliser