Zápis vzorců R1C1 v Delphi s HotXLS

HotXLS kompiluje vzorce zapsané v odkazovacím zápisu R1C1 metodou TXLSCalculator.GetCompiledFormulaR1C1, která přijímá tvary jako R2C3 (absolutní), R[-1]C[2] (relativní posun) a RC (aktuální buňka), převádí je do zápisu A1 vůči řádku a sloupci buňky, v níž vzorec leží, a výsledek předá témuž kompilátoru, který zpracovává běžné vzorce A1. Kódu v Delphi a C++Builderu, jenž generuje tentýž vzorec přes stovky řádků, tato jediná metoda odstraní celou třídu chyb ze skládání řetězců

Tuto třídu chyb zná každý, kdo někdy programově plnil sloupec. Procházíte řádky a pro každý řádek sestavujete textový vzorec A1 pomocí Format('D%d*E%d', [Row, Row]). Každá iterace vlepí do textu čísla řádků a právě čísla řádků jsou jediné, co se mění. Stačí se jednou splést v posunu, smíchat čítač cyklu počítaný od nuly s čísly řádků A1 počítanými od jedničky nebo posunout datový blok o řádek hlavičky a každý vzorec ve sloupci ukazuje o řádek vedle. Nic nevyvolá výjimku; čísla jsou prostě špatně. Vzorec, který jste ve skutečnosti mysleli, tedy „vynásob dvě buňky nalevo ode mě“, žádné číslo řádku vůbec nezmiňuje a zápis R1C1 vám ho dovolí takto napsat

Co je zápis R1C1 a kdy jej použít?

Zápis R1C1 adresuje buňky číslem řádku a sloupce místo písmene sloupce a čísla řádku a relativní odkazy značí jako výslovné posuny od buňky se vzorcem. R2C3 je absolutní buňka na řádku 2 ve sloupci 3, kterou A1 píše jako $C$2. R[-1]C[2] je o řádek výš a o dva sloupce vpravo od místa, kde vzorec zrovna sedí. RC je buňka se vzorcem sama. O hranaté závorky tu jde především: relativní odkaz v R1C1 se čte stejně bez ohledu na to, která buňka jej hostí, kdežto vykreslení téhož odkazu v A1 se s každým řádkem mění

Zápis se vyplatí přesně v jednom scénáři, a ten je běžný: při generování šablon, kdy se tentýž relativní vzorec musí zasadit do každého řádku datové oblasti. V A1 musíte text vzorce pro každý řádek vykreslit znovu. V R1C1 je ten text konstanta. Blíž je to i tomu, jak uvažují souborové formáty tabulek uvnitř: záznamy sdílených vzorců ukládají relativní odkazy jako posuny řádku a sloupce od hostitelské buňky, takže řetězec A1 pro každý řádek váš kód skládá jen proto, aby jej parser hned zase rozložil zpět na posuny. R1C1 tuto okliku přeskočí. Pro interaktivní vzorce psané člověkem zůstává přirozenou volbou A1, a proto je v HotXLS všude výchozí

Jak HotXLS zkompiluje vzorec R1C1?

HotXLS tuto funkci zpřístupňuje na dvou úrovních, přidaných ve verzi v2.175.0. TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula volá většina kódu: vrací zkompilovaný vzorec připravený k vyhodnocení, přesně jako jeho sourozenec pro A1 GetCompiledFormula, ale se dvěma parametry navíc, které pojmenovávají řádek a sloupec buňky, jíž vzorec patří, počítané od nuly. Pod ní TXLSFormula.GetCompiledR1C1 vytváří surový syntaktický strom a samostatná funkce R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String provádí vlastní převod zápisu. Ten postup je záměrně jednoduchý: přeložit text R1C1 na rovnocenný text A1 pomocí souřadnic hostitelské buňky a pak text A1 zkompilovat stávajícím enginem — tímtéž enginem, který rozřeší definovaná jména a odkazy napříč listy a rozesílá vlastní funkce listu

Protože k převodu dochází před kompilací, chová se všechno navazující tak, jako byste vzorec A1 napsali sami. 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) — vzorec žijící v D5, protože CurRow = 4 a CurCol = 3 se počítají od nuly — vrací 'SUM(D2:D4)': posuny se vyhodnotily vůči řádku 5 a sloupci D a vyšly jako prosté relativní odkazy A1. Oblasti nepotřebují žádnou výjimku; dvojtečka projde beze změny a každý krajní bod se převede samostatně

// Vzorec žije v D5: CurRow = 4, CurCol = 3 (obojí počítáno od nuly)
S := R1C1ToA1('R2C3', 4, 3);
// S = '$C$2'  (absolutní řádek i sloupec)

S := R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3);
// S = 'SUM(D2:D4)'  (posuny vyhodnocené vůči D5)

S := R1C1ToA1('ROUND(R[-1]C[0], 2)', 4, 3);
// S = 'ROUND(D4, 2)'  (písmeno R ve slově ROUND zůstává nedotčené)

Vyplnění sloupce jediným relativním vzorcem

Zisk se ukáže v cyklu. Porovnejte verzi s A1, která text vzorce vykresluje při každé iteraci znovu, s verzí R1C1, kde je vzorec konstanta a mění se jen souřadnice hostitele. Obě se kompilují přes TXLSCalculator a vyhodnocují metodou GetValue; kalkulátor při vytvoření přebírá callback poskytovatele buněk, aby si evaluátor mohl tahat hodnoty buněk z vašeho zdroje dat

// Styl A1: jiný řetězec vzorce pro každý řádek
for Row := 1 to 500 do
begin
  FormulaText := Format('D%d*E%d', [Row + 1, Row + 1]);  // řádky A1 od jedničky
  Compiled := Calc.GetCompiledFormula(FormulaText, 0);
  // ... vyhodnotit, uložit, uvolnit ...
end;

const
  AmountFormula = 'RC[-2]*RC[-1]';  // dvě buňky nalevo, tentýž řádek
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 s R1C1 nemá v textu vzorce žádnou aritmetiku s řádky. 'RC[-2]*RC[-1]' znamená „tentýž řádek, o dva sloupce vlevo, krát tentýž řádek, o jeden sloupec vlevo“ na řádku 2 stejně jako na řádku 500, a když se datový blok později posune o řádek hlavičky níž, konstanta vzorce se nemění — mění se jen meze cyklu. Verze s A1 má dvě místa, kde se dá úprava + 1 zkazit; verze s R1C1 nemá žádné

Které tvary R1C1 převodník přijímá?

Převodník R1C1ToA1 rozpoznává tvary zdokumentované pro v2.175.0: R[n]C[m] pro relativní posuny na obě strany, RnCm pro absolutní řádek a sloupec, R[-n]C[m] se zápornými posuny, samotné RC pro buňku se vzorcem a oblasti jako R1C1:R3C3 nebo R[-1]C:R[1]C. Řádková a sloupcová část se parsují nezávisle, takže fungují i smíšené tvary jako R[1]C3 — relativní řádek, absolutní sloupec — a na velikosti písmen nezáleží, takže r[-1]c[2] se zkompiluje stejně jako jeho verzálkové dvojče. Části v hranatých závorkách se stanou neukotvenými, tedy relativními souřadnicemi A1; holá čísla se stanou absolutními souřadnicemi ukotvenými znakem $

Zajímavá otázka je, jak se převodník vyhne zkomolení všeho ostatního ve vzorci, protože R a C jsou běžná písmena. Jeho rozlišovací pravidlo pracuje s tokeny: R se považuje za začátek odkazu jen tehdy, když mu nepředchází další písmeno, a kandidát se pak musí rozparsovat celý — volitelná řádková část, povinné C, volitelná sloupcová část — jinak se text vrátí nedotčený. Proto ROUND(R[-1]C[0], 2) převede jen vnitřní odkaz: po R ve slově ROUND následuje O, ne číslice, závorka ani C, takže parsování selže a název funkce projde doslova. Táž logika chrání ROW() a názvy funkcí začínající na C nejsou kandidáty vůbec, protože odkaz začíná jedině písmenem R. Poznámky k vydání v2.175.0 uvádějí, že se přeskakují i řetězcové literály a identifikátory; přesto stojí za to výstup kompilace jednou zkontrolovat, než mu v produkci uvěříte, kdyby literál v uvozovkách ve vašem vzorci náhodou obsahoval text tvarovaný přesně jako odkaz R1C1

Základy souřadnic a detaily ukotvení, které stojí za to znát

V tomto API se potkávají dvě konvence a jejich rozlišování vás uchrání jediné skutečné pasti. Parametry CurRow a CurCol metody GetCompiledFormulaR1C1 se počítají od nuly podle konvence API kalkulátoru, kdežto čísla uvnitř samotného zápisu se počítají od jedničky, stejně jako je zobrazuje Excel: R2C3 je řádek 2, sloupec 3, tedy $C$2, ne $D$3. Pokud váš čítač cyklu už začíná od nuly, předáte jej rovnou jako CurRow; ono + 1 žije uvnitř převodníku, ne ve vašem kódu

Jeden detail navíc má význam, pokud zkompilovaný vzorec přežije buňku, pro kterou byl zkompilován. Když vynecháte řádkovou nebo sloupcovou část — tvary RC[-1] nebo R[2]C — vynechaná souřadnice se vyhodnotí na hostitelskou buňku a do převedeného textu A1 se vypíše jako absolutní, ukotvená znakem $. Při vyhodnocení to není vidět, protože hodnota vyjde tak jako tak stejně. Jenže o tom, jak se odkazy posunou při pozdějším vkládání či mazání řádků a sloupců, rozhoduje právě ukotvení relativní versus absolutní, jak popisuje doprovodný článek o úpravě odkazů ve vzorcích. Pokud potřebujete, aby souřadnice zůstala napříč strukturálními úpravami relativní, napište posun výslovně — R[0]C[-1] místo RC[-1] — aby převodník vypsal neukotvený odkaz A1

Kompilace R1C1 je součástí enginu vzorců v komponentě HotXLS Delphi Excel Component vedle kompilátoru A1, grafu přepočtu a vyhodnocovacího API ukázaného výše. Pokud váš kód staví tabulky tak, že vzorce v cyklu sype dolů sloupci, je přesun těchto cyklů od skládaného A1 k jediné konstantě R1C1 jedním z nejlevnějších dostupných vylepšení spolehlivosti