R1C1 notacija formula u Delphiju pomoću HotXLS-a

HotXLS prevodi formule napisane u R1C1 notaciji referenci putem funkcije TXLSCalculator.GetCompiledFormulaR1C1, koja prihvaća oblike kao što su R2C3 (apsolutno), R[-1]C[2] (relativni pomak) i RC (trenutna ćelija), pretvara ih u A1 notaciju na temelju retka i stupca ćelije u kojoj se formula nalazi, te prosljeđuje rezultat istom prevoditelju (compileru) koji rukuje običnim A1 formulama. Za Delphi i C++Builder kôd koji generira istu formulu kroz stotine redaka, ta jedna metoda uklanja cijelu klasu pogrešaka spajanja nizova znakova (string-concatenation)

Klasa pogrešaka je poznata svima koji su programski popunjavali stupac. Prolazite kroz retke u petlji i za svaki redak gradite A1 niz formule pomoću Format('D%d*E%d', [Row, Row]). Svaka iteracija umeće brojeve redaka u tekst, a brojevi redaka su jedini dio koji se mijenja. Pogriješite li u pomaku samo jednom, pomiješate li brojač petlje koji počinje od 0 s 1-baziranim brojevima redaka A1, ili pomaknete blok podataka za redak zaglavlja, svaka formula u stupcu pokazivat će jedan redak dalje. Ništa ne javlja iznimku, brojevi su jednostavno pogrešni. Formula koju ste zapravo željeli, 'pomnoži dvije ćelije s moje lijeve strane', uopće ne spominje broj retka, a R1C1 notacija vam omogućuje da je zapišete upravo tako

Što je R1C1 notacija i kada je trebate koristiti?

Notacija R1C1 adresira ćelije prema broju retka i stupca umjesto slova stupca i broja retka, te označava relativne reference kao eksplicitne pomake od ćelije formule. R2C3 je apsolutna ćelija u retku 2, stupcu 3, što A1 piše kao $C$2. R[-1]C[2] je jedan redak iznad i dva stupca desno od mjesta gdje se formula nalazi. RC je sama ćelija formule. Zagradama označeni pomaci su ključ: relativna referenca u R1C1 glasi isto bez obzira na to koja je ćelija ugošćuje, dok se prikaz te iste reference u A1 mijenja sa svakim retkom

Notacija opravdava svoje postojanje u točno jednom scenariju, i to uobičajenom: generiranje predložaka, gdje se ista relativna formula mora postaviti u svaki redak područja podataka. U A1 morate ponovno generirati tekst formule po retku. U R1C1 je tekst konstanta. To je također bliže načinu na koji formati datoteka proračunskih tablica razmišljaju iznutra: zapisi dijeljenih formula pohranjuju relativne reference kao pomake retka i stupca od ćelije domaćina, tako da je A1 niz po retku se sintetizira samo da bi ga analizator (parser) odmah rastavio natrag u pomake. R1C1 preskače taj krug. Za interaktivne formule koje pišu ljudi, A1 ostaje prirodan izbor, zbog čega ostaje zadana postavka svugdje u HotXLS

Kako HotXLS prevodi R1C1 formulu?

HotXLS izlaže ovu značajku na dvije razine, dodane u verziji v2.175.0. TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula je ona koju većina kôda poziva: vraća prevedenu formulu spremnu za evaluaciju, točno poput svog A1 srodnika GetCompiledFormula, ali s dva dodatna parametra koji navode 0-bazirani redak i stupac ćelije kojoj formula pripada. Ispod toga, TXLSFormula.GetCompiledR1C1 proizvodi sirovo stablo sintakse, a samostalna funkcija R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String izvodi stvarnu pretvorbu notacije. Cjevovod je namjerno jednostavan: prevodi R1C1 tekst u ekvivalentni A1 tekst pomoću koordinata ćelije domaćina, a zatim prevodi A1 tekst kroz postojeći mehanizam — isti mehanizam koji razrješava definirane nazive i reference na više listova te šalje prilagođene funkcije radnog lista

Budući da se pretvorba događa prije prevođenja, sve dalje se ponaša kao da ste sami napisali formulu A1. R1C1ToA1('R2C3', 4, 3) vraća '$C$2' bez obzira na ćeliju domaćina, jer su obje koordinate apsolutne. R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3) — formula koja živi u D5, jer su CurRow = 4 i CurCol = 3 0-bazirani — vraća 'SUM(D2:D4)': pomaci su razriješeni u odnosu na redak 5, stupac D i emitirani kao obične relativne A1 reference. Za raspone nije potrebno posebno rukovanje; kosa crta (dvotočka) prolazi i svaka se krajnja točka pretvara neovisno

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

Popunjavanje stupca jednom relativnom formulom

Isplativost se očituje u petlji. Usporedite verziju A1, koja ponovno iscrtava tekst formule u svakoj iteraciji, s verzijom R1C1, gdje je formula konstanta, a pomiču se samo koordinate domaćina. Obje se prevode kroz TXLSCalculator i evaluiraju pomoću GetValue; kalkulator prima povratni poziv (callback) pružatelja ćelije pri izradi kako bi evaluator mogao povući vrijednosti ćelija iz vašeg izvora podataka

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

Petlja R1C1 uopće nema aritmetiku redaka u tekstu formule. 'RC[-2]*RC[-1]' znači 'isti redak, dva stupca lijevo, puta isti redak, jedan stupac lijevo' u retku 2 i retku 500 podjednako, a ako se blok podataka kasnije pomakne prema dolje za redak zaglavlja, konstanta formule se ne mijenja — samo se granice petlje mijenjaju. Verzija A1 ima dva mjesta na kojima se može pogriješiti u prilagodbi za + 1; verzija R1C1 ima nula

Koje oblike R1C1 konverter prihvaća?

Konverter R1C1ToA1 prepoznaje oblike dokumentirane za verziju v2.175.0: R[n]C[m] za relativne pomake u oba smjera, RnCm za apsolutni redak i stupac, R[-n]C[m] s negativnim pomacima, čisti RC za samu ćeliju formule, te raspone kao što su R1C1:R3C3 ili R[-1]C:R[1]C. Dijelovi retka i stupca raščlanjuju se neovisno, pa mješoviti oblici poput R[1]C3 — relativni redak, apsolutni stupac — također rade, a slova su neosjetljiva na velika i mala slova (case-insensitive), pa se r[-1]c[2] prevodi jednako kao i njegova verzija s velikim slovima. Dijelovi u zagradama postaju nesidrene (relativne) A1 koordinate; čisti brojevi postaju apsolutne koordinate sidrene znakom $

Zanimljivo je pitanje kako konverter izbjegava kvarenje svega ostalog u formuli, budući da su R i C uobičajena slova. Njegovo pravilo razlikovanja temelji se na tokenima: R se smatra početkom reference samo kada mu ne prethodi drugo slovo, a kandidat se zatim mora u potpunosti raščlaniti — neobavezni dio retka, obavezno C, neobavezni dio stupca — inače se tekst vraća netaknut. Zato ROUND(R[-1]C[0], 2) pretvara samo unutarnju referencu: iza R u ROUND slijedi O, a ne znamenka, zagrada ili C, pa raščlamba ne uspijeva i naziv funkcije prolazi doslovno. Ista logika štiti ROW(), a nazivi funkcija koji počinju s C nikada nisu kandidati, budući da samo R pokreće referencu. Napomene uz izdanje v2.175.0 navode da se tekstualni literali i identifikatori također preskaču; unatoč tome, ako navodima zaštićeni literal u vašoj formuli slučajno sadrži tekst oblikovan točno poput R1C1 reference, vrijedi jednom provjeriti prevedeni izlaz prije nego što mu povjerite rad u produkciji

Baze koordinata i detalji sidrenja koje vrijedi znati

Dvije se konvencije susreću unutar ovog API-ja, a njihovo točno razlikovanje izbjegava jedinu stvarnu zamku. Parametri CurRow and CurCol funkcije GetCompiledFormulaR1C1 su 0-bazirani, prateći API kalkulatora, dok su brojevi unutar same notacije 1-bazirani, što odgovara onome što Excel prikazuje: R2C3 je redak 2, stupac 3, što je $C$2, a ne $D$3. Ako je vaš brojač petlje već 0-baziran, prosljeđujete ga izravno kao CurRow; prilagodba + 1 živi unutar konvertera, a ne u vašem kodu

Još je jedan detalj važan ako će prevedena formula nadživjeti ćeliju za koju je prevedena. Kada izostavite dio retka ili stupca — oblike RC[-1] ili R[2]C — izostavljena koordinata razrješava se u ćeliju domaćina i emitira kao apsolutna koordinata sidrena znakom $ u pretvorenom A1 tekstu. U vrijeme evaluacije to je nevidljivo, jer je vrijednost u oba slučaja ista. No, relativno naspram apsolutnog sidrenja odlučuje o tome kako se reference pomiču kada se retci ili stupci kasnije umetnu ili obrišu, kao što je pokriveno u pratećem članku o prilagodbi referenci formula. Ako trebate da koordinata ostane relativna kroz strukturalne izmjene, zapišite pomak eksplicitno — R[0]C[-1] umjesto RC[-1] — tako da konverter emitira nesidrenu A1 referencu

Prevođenje R1C1 dio je mehanizma formula u HotXLS Delphi Excel komponenti, uz A1 prevoditelj, grafikon ponovnog izračuna i API za evaluaciju prikazan gore. Ako vaš kôd gradi proračunske tablice petljom formula niz stupce, premještanje tih petlji s nizom spojenih A1 na jednu konstantu R1C1 jedna je od najjeftinijih dostupnih nadogradnji pouzdanosti