Notation de formule R1C1 en Delphi avec HotXLS

HotXLS compile les formules écrites en notation de références R1C1 par TXLSCalculator.GetCompiledFormulaR1C1, qui accepte des formes comme R2C3 (absolue), R[-1]C[2] (décalage relatif) et RC (la cellule courante), les convertit en notation A1 par rapport à la ligne et à la colonne de la cellule où vit la formule, et remet le résultat au même compilateur que celui des formules A1 ordinaires. Pour du code Delphi et C++Builder qui génère la même formule sur des centaines de lignes, cette seule méthode supprime toute une classe de bogues de concaténation de chaînes

Cette classe de bogues est familière à quiconque a rempli une colonne par programme. 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 insère des numéros de lignes dans du texte, et ces numéros sont la seule partie qui change. Trompez-vous une fois de décalage, mélangez un compteur de boucle en base 0 avec des numéros de lignes A1 en base 1, ou décalez le bloc de données d'une ligne d'en-tête, et toutes les formules de la colonne pointent une ligne à côté. Rien ne se déclenche ; les nombres sont simplement faux. La formule que vous vouliez réellement, « multiplier les deux cellules à ma gauche », ne mentionne aucun numéro de ligne, et la notation R1C1 vous permet de l'écrire ainsi

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

La notation R1C1 adresse les cellules par numéro de ligne et de colonne au lieu de la lettre de colonne suivie du numéro de ligne, et elle marque les références relatives comme des décalages explicites depuis la cellule de la formule. R2C3 est la cellule absolue à la ligne 2, colonne 3, que A1 écrit $C$2. R[-1]C[2] est une ligne au-dessus et deux colonnes à droite de l'endroit où siège la formule. RC est la cellule de la formule elle-même. Les décalages entre crochets sont tout l'enjeu : une référence relative en R1C1 se lit pareil quelle que soit la cellule qui l'héberge, alors que le rendu A1 de cette même référence change à chaque ligne

La notation gagne son salaire dans exactement un scénario, et il est courant : la génération par modèle, où la même formule relative doit être plantée dans chaque ligne d'une région de données. En A1, vous devez régénérer le texte de la formule ligne par ligne. En R1C1, ce texte est une constante. C'est aussi plus proche de la façon dont les formats de fichiers tableurs pensent en interne : les enregistrements de formules partagées rangent les références relatives comme des décalages de ligne et de colonne depuis la cellule hôte, si bien qu'une chaîne A1 par ligne est quelque chose que votre code fabrique uniquement pour que l'analyseur la redécompose aussitôt en décalages. R1C1 évite cet aller-retour. Pour les formules interactives écrites par des humains, A1 reste le choix naturel, ce qui explique qu'elle reste la valeur par défaut partout dans HotXLS

Comment HotXLS compile-t-il une formule R1C1 ?

HotXLS expose la fonctionnalité à deux niveaux, ajoutés en v2.175.0. TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula est celle que la plupart du code appelle : elle renvoie une formule compilée prête à l'évaluation, exactement comme sa sœur A1 GetCompiledFormula, mais avec deux paramètres supplémentaires qui nomment la ligne et la colonne en base 0 de la cellule à laquelle appartient la formule. En dessous, 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 pipeline est délibérément simple : traduire le texte R1C1 en texte A1 équivalent à l'aide des coordonnées de la cellule hôte, puis compiler ce texte A1 par le moteur existant — le même moteur qui résout les noms définis et les références inter-feuilles et qui aiguille les fonctions de feuille personnalisées

Comme la conversion précède la compilation, tout ce qui se trouve en aval se comporte comme si vous aviez écrit la formule A1 vous-même. R1C1ToA1('R2C3', 4, 3) renvoie '$C$2' quelle que soit la cellule hôte, puisque les deux coordonnées sont absolues. R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3) — une formule qui vit en D5, puisque CurRow = 4 et CurCol = 3 sont en base 0 — renvoie 'SUM(D2:D4)' : les décalages ont été résolus par rapport à la ligne 5, colonne D, et émis en références A1 relatives ordinaires. Les plages ne demandent aucun traitement particulier ; le deux-points passe tel quel et chaque extrémité se convertit indépendamment

// La formule vit en D5 : CurRow = 4, CurCol = 3 (les deux en base 0)
S := R1C1ToA1('R2C3', 4, 3);
// S = '$C$2'  (ligne et colonne absolues)

S := R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3);
// S = 'SUM(D2:D4)'  (décalages résolus par rapport à D5)

S := R1C1ToA1('ROUND(R[-1]C[0], 2)', 4, 3);
// S = 'ROUND(D4, 2)'  (le R de ROUND est laissé tranquille)

Remplir une colonne avec une seule formule relative

Le bénéfice se voit dans la boucle. Comparez la version A1, qui régénère le texte de la formule à chaque itération, avec la version R1C1, où la formule est une constante et où seules les coordonnées hôtes bougent. Les deux compilent par TXLSCalculator et s'évaluent avec GetValue ; le calculateur reçoit à la construction une fonction de rappel fournisseuse de cellules, afin que l'évaluateur puisse tirer les valeurs de cellules de votre source de données

// Style A1 : une chaîne de formule différente pour chaque ligne
for Row := 1 to 500 do
begin
  FormulaText := Format('D%d*E%d', [Row + 1, Row + 1]);  // lignes A1 en base 1
  Compiled := Calc.GetCompiledFormula(FormulaText, 0);
  // ... évaluer, ranger, libérer ...
end;

const
  AmountFormula = 'RC[-2]*RC[-1]';  // deux cellules à gauche, même ligne
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 lignes 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 » aussi bien à la ligne 2 qu'à la ligne 500, et si le bloc de données descend plus tard d'une ligne d'en-tête, la constante de formule ne change pas — seules les bornes de la boucle changent. La version A1 offre deux endroits où se tromper d'ajustement + 1 ; la version R1C1 en offre zéro

Quelles formes R1C1 le convertisseur accepte-t-il ?

Le convertisseur R1C1ToA1 reconnaît les formes documentées pour la v2.175.0 : R[n]C[m] pour les décalages relatifs dans les deux sens, RnCm pour la paire ligne-colonne absolue, R[-n]C[m] avec des décalages négatifs, RC nu pour la cellule de la formule elle-même, et les plages telles que R1C1:R3C3 ou R[-1]C:R[1]C. Les parties ligne et colonne s'analysent indépendamment, si bien que des formes mixtes comme R[1]C3 — ligne relative, colonne absolue — fonctionnent aussi, et les lettres sont insensibles à la casse, si bien que r[-1]c[2] compile comme son jumeau en majuscules. Les parties entre crochets deviennent des coordonnées A1 non ancrées (relatives) ; les nombres nus deviennent des coordonnées absolues ancrées par $

La question intéressante est de savoir comment le convertisseur évite de massacrer tout le reste de la formule, car R et C sont des lettres courantes. Sa règle de discrimination repose sur les jetons : un R n'est considéré comme le début d'une référence que s'il n'est pas précédé d'une autre lettre, et le candidat doit ensuite s'analyser complètement — partie ligne facultative, C obligatoire, partie colonne facultative — faute de quoi le texte est restitué intact. Voilà pourquoi ROUND(R[-1]C[0], 2) ne convertit que la référence intérieure : le R de ROUND est suivi d'un O plutôt que d'un chiffre, d'un crochet ou d'un C, si bien que l'analyse échoue et que le nom de 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 ouvre une référence. Les notes de version de la v2.175.0 indiquent que les littéraux de chaîne et les identifiants sont sautés eux aussi ; malgré cela, si un littéral entre apostrophes de votre formule contient par hasard un texte de forme exactement identique à une référence R1C1, il vaut la peine de contrôler une fois la sortie compilée avant de lui faire confiance en production

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

Deux conventions se rencontrent dans cette API, et les garder distinctes évite le seul vrai piège. Les paramètres CurRow et CurCol de GetCompiledFormulaR1C1 sont en base 0, suivant l'API du calculateur, tandis que les nombres à l'intérieur de la notation elle-même sont en base 1, conformément à ce qu'affiche Excel : R2C3 désigne la ligne 2, colonne 3, soit $C$2 et non $D$3. Si votre compteur de boucle est déjà en base 0, vous le passez directement en CurRow ; le + 1 vit à l'intérieur du convertisseur, pas dans votre code

Un détail de plus compte si la formule compilée doit survivre à la cellule pour laquelle elle a été compilée. Quand vous omettez une partie ligne ou colonne — les formes RC[-1] ou R[2]C —, la coordonnée omise se résout vers la cellule hôte et est émise comme coordonnée absolue ancrée par $ dans le texte A1 converti. À l'évaluation, cela reste invisible, puisque la valeur est la même dans les deux cas. Mais l'ancrage relatif ou absolu décide de la façon dont les références se décalent quand des lignes ou des colonnes sont insérées ou supprimées plus tard, comme le traite l'article compagnon sur l'ajustement des références de formule. Si vous avez besoin qu'une coordonnée reste relative à travers les modifications structurelles, écrivez le décalage explicitement — R[0]C[-1] au lieu de RC[-1] — pour que le convertisseur émette une référence A1 non ancrée

La compilation R1C1 fait partie du moteur de formules du HotXLS Delphi Excel Component, aux côtés du compilateur A1, du graphe de recalcul et de l'API d'évaluation montrée plus haut. Si votre code construit des feuilles de calcul en bouclant des formules le long des colonnes, faire passer ces boucles de l'A1 assemblé par chaînes à une unique constante R1C1 est l'un des gains de fiabilité les moins coûteux qui soient