Notace vzorců R1C1 v Delphi pomocí HotXLS

Knihovna HotXLS kompiluje vzorce napsané v referenční notaci R1C1 pomocí metody TXLSCalculator.GetCompiledFormulaR1C1, která akceptuje tvary jako R2C3 (absolutní), R[-1]C[2] (relativní posun) a RC (aktuální buňka), převádí je na notaci A1 vůči řádku a sloupci buňky, ve které se vzorec nachází, a předává výsledek stejnému kompilátoru, který zpracovává běžné vzorce A1. Pro kód v Delphi a C++Builderu, který generuje stejný vzorec napříč stovkami řádků, tato jediná metoda odstraňuje celou třídu chyb spojených se spojováním řetězců

Co je to notace R1C1 a kdy ji použít?

Notace R1C1 adresuje buňky číslem řádku a sloupce namísto písmene sloupce a čísla řádku, a označuje relativní odkazy jako explicitní posuny od buňky se vzorcem. R2C3 je absolutní odkaz na buňku na řádku 2, sloupci 3, což A1 zapisuje jako $C$2. Odkaz R[-1]C[2] je o jeden řádek výše a dva sloupce vpravo od místa, kde se vzorec nachází. RC je samotná buňka se vzorcem. Posuny v hranatých závorkách jsou klíčové: relativní odkaz v R1C1 se čte stejně bez ohledu na to, která buňka jej hostí, zatímco podoba stejného odkazu v A1 se mění s každým řádkem

Tato notace se uplatňuje v jednom konkrétním scénáři, který je však velmi běžný: generování šablon, kde musí být stejný relativní vzorec vložen do každého řádku datové oblasti. V notaci A1 musíte text vzorce generovat pro každý řádek znovu. V notaci R1C1 je text konstantou. To je také blíže tomu, jak formáty tabulkových souborů uvažují vnitřně: záznamy sdílených vzorců ukládají relativní odkazy jako řádkové a sloupcové posuny od hostitelské buňky, takže řetězec A1 generovaný pro každý řádek je něco, co váš kód sestavuje pouze proto, aby jej parser následně rozložil zpět na posuny. R1C1 tento převod obchází. Pro interaktivní, lidmi psané vzorce zůstává přirozenou volbou notace A1, což je důvod, proč zůstává výchozí všude v knihovně HotXLS

Jak HotXLS kompiluje vzorec R1C1?

Knihovna HotXLS zpřístupňuje tuto funkci na dvou úrovních zavedených ve verzi v2.175.0. Metoda TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula je tou, kterou volá většina kódu: vrací zkompilovaný vzorec připravený k vyhodnocení, přesně jako její sourozenec A1 GetCompiledFormula, ale s dvěma parametry navíc určujícími řádek a sloupec (indexováno od nuly) buňky, které vzorec patří. Pod ní pak metoda TXLSFormula.GetCompiledR1C1 produkuje čistý syntaktický strom a samostatná funkce R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String provádí samotný převod notace. Celý postup je záměrně jednoduchý: přeložit text R1C1 na ekvivalentní text A1 pomocí souřadnic hostitelské buňky a poté zkompilovat text A1 stávajícím jádrem — stejným jádrem, které vyhodnocuje definované názvy a odkazy napříč listy a spouští vlastní funkce listu

Vzhledem k tomu, že převod probíhá před kompilací, vše dále v řetězci se chová, jako byste vzorec A1 zapsali sami. Volání R1C1ToA1('R2C3', 4, 3) vrací '$C$2' bez ohledu na hostitelskou buňku, protože obě souřadnice jsou absolutní. R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3) — což je vzorec umístěný v D5, protože CurRow = 4 a CurCol = 3 jsou indexovány od nuly — vrací 'SUM(D2:D4)': posuny byly vyhodnoceny vůči řádku 5, sloupci D, a vygenerovány jako běžné relativní odkazy A1. Rozsahy nevyžadují žádné speciální zacházení; dvojtečka prochází a každý koncový bod se převádí samostatně

Vyplnění sloupce jedním relativním vzorcem

Přínos se projeví v cyklu. Porovnejte verzi A1, která generuje text vzorce při každé iteraci znovu, s verzí R1C1, kde je vzorec konstantou a mění se pouze souřadnice hostitele. Obě verze se kompilují pomocí TXLSCalculator a vyhodnocují pomocí GetValue; kalkulátor při konstrukci přijímá zpětné volání (callback) poskytovatele buněk, takže vyhodnocovač může stahovat hodnoty buněk z vašeho zdroje dat

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

Cyklus R1C1 neobsahuje v textu vzorce vůbec žádnou řádkovou aritmetiku. Výraz 'RC[-2]*RC[-1]' znamená „stejný řádek, o dva sloupce vlevo, krát stejný řádek, o jeden sloupec vlevo“ na řádku 2 stejně jako na řádku 500. A pokud se datový blok později posune dolů kvůli záhlaví, konstanta vzorce se nezmění — změní se pouze meze cyklu. Verze A1 má dvě místa, kde se lze splést v úpravě + 1; verze R1C1 nemá žádné

Které tvary R1C1 převaděč přijímá?

Převaděč R1C1ToA1 rozpoznává tvary dokumentované pro verzi v2.175.0: R[n]C[m] pro relativní posuny v obou směrech, RnCm pro absolutní řádek a sloupec, R[-n]C[m] se zápornými posuny, samotné RC pro buňku se vzorcem a rozsahy jako R1C1:R3C3 nebo R[-1]C:R[1]C. Řádkové a sloupcové části se analyzují nezávisle, takže fungují i smíšené tvary jako R[1]C3 — relativní řádek, absolutní sloupec. Písmena jsou navíc malá i velká, takže r[-1]c[2] se kompiluje stejně jako jeho velký tvar. Části v závorkách se stávají neukotvenými (relativními) souřadnicemi A1; samotná čísla se stávají absolutními souřadnicemi ukotvenými pomocí $

Zajímavou otázkou je, jak se převaděč vyhýbá poškození všeho ostatního ve vzorci, protože R a C jsou běžná písmena. Jeho diskriminační pravidlo je založeno na tokenech: znak R je považován za začátek odkazu pouze tehdy, když mu nepředchází jiné písmeno, a kandidát pak musí jít zcela analyzovat — volitelná řádková část, povinné C, volitelná sloupcová část — jinak se text obnoví beze změny. To je důvod, proč výraz ROUND(R[-1]C[0], 2) převádí pouze vnitřní odkaz: po R v ROUND následuje O namísto číslice, závorky nebo C, takže analýza selže a název funkce projde beze změny. Stejná logika chrání funkci ROW() a názvy funkcí začínající na C nejsou kandidáty vůbec, protože odkaz začíná pouze na R. Poznámky k verzi v2.175.0 uvádějí, že řetězcové literály a identifikátory jsou rovněž přeskakovány; přesto, pokud citovaný literál ve vašem vzorci náhodou obsahuje text tvarovaný přesně jako odkaz R1C1, stojí za to před nasazením do produkce jednou zkontrolovat kompilovaný výstup

Souřadnicové základy a detaily ukotvení, které stojí za to znát

V tomto rozhraní API se setkávají dvě konvence a jejich dodržení předchází jediné skutečné pasti. Parametry CurRow a CurCol metody GetCompiledFormulaR1C1 are 0-based, following the calculator API, while the numbers inside the notation itself are 1-based, matching what Excel displays: R2C3 is row 2, column 3, which is $C$2, not $D$3. If your loop counter is already 0-based you pass it straight through as CurRow; the + 1 lives inside the converter, not in your code

Jedním dalším detailu záleží, pokud má kompilovaný vzorec přežít buňku, pro kterou byl zkompilován. Pokud vynecháte řádkovou nebo sloupcovou část — tvary RC[-1] nebo R[2]C —, vynechaná souřadnice se vyhodnotí jako hostitelská buňka a v převedeném textu A1 se vygeneruje jako absolutní souřadnice ukotvená pomocí $. V době vyhodnocení je to neviditelné, protože hodnota je v obou případech stejná. Relativní versus absolutní ukotvení však rozhoduje o tom, jak se odkazy posunou při pozdějším vkládání nebo mazání řádků či sloupců, což je popsáno v doprovodném článku o úpravě odkazů ve vzorcích. Pokud potřebujete, aby souřadnice zůstala relativní i po strukturálních úpravách, zapište posun explicitně — R[0]C[-1] namísto RC[-1] —, aby převaděč vygeneroval neukotvený odkaz A1

Kompilace R1C1 je součástí jádra vzorců v produktu HotXLS Delphi Excel Component, společně s kompilátorem A1, grafem přepočtu a vyhodnocovacím API popsaným výše. Pokud váš kód sestavuje tabulky cyklickým zápisem vzorců do sloupců, přechod těchto cyklů ze spojovaných řetězců A1 na jedinou konstantu R1C1 je jednou z nejlevnějších možností zvýšení spolehlivosti