HotXLS kompilira formule, zapisane v referenčnem zapisu R1C1, prek metode TXLSCalculator.GetCompiledFormulaR1C1, ki sprejema oblike, kot so R2C3 (absolutna), R[-1]C[2] (relativni odmik) in RC (trenutna celica), jih pretvori v zapis A1 glede na vrstico in stolpec celice, v kateri formula živi, ter rezultat posreduje istemu prevajalniku, ki obravnava običajne formule A1. Za kodo v Delphiju in C++Builderju, ki ustvarja isto formulo v več sto vrsticah, ta edina metoda odpravi cel razred hroščev, povezanih s spajanjem nizov
Ta razred hroščev pozna vsakdo, ki je programsko zapolnjeval stolpec. Greste skozi zanko vrstic in za vsako vrstico zgradite niz formule A1 s Format('D%d*E%d', [Row, Row]). Vsaka iteracija v besedilo vgradi številke vrstic, pri čemer so številke vrstic edini del, ki se spreminja. Če se enkrat zmotite pri odmiku, pomešate števec zanke, ki se začne z 0, z 1-baziranimi številkami vrstic A1 ali premaknete podatkovni blok za vrstico z glavo, bo vsaka formula v stolpcu kazala eno vrstico mimo. Nič ne sproži izjeme, številke pa so preprosto napačne. Formula, ki ste jo dejansko imeli v mislih, "pomnoži dve celici na moji levi," sploh ne omenja številke vrstice, zapis R1C1 pa vam omogoča, da jo zapišete prav tako
Kaj je zapis R1C1 in kdaj ga uporabiti?
Zapis R1C1 naslavlja celice s številko vrstice in stolpca namesto s črko stolpca plus številko vrstice, relativne reference pa označuje kot eksplicitne odmike od celice s formulo. R2C3 je absolutna celica v 2. vrstici in 3. stolpcu, kar A1 zapiše kot $C$2. R[-1]C[2] je ena vrstica navzgor in dva stolpca desno od mesta, kjer se nahaja formula. RC je sama celica s formulo. Odmiki v oglatih oklepajih so bistvo: relativna referenca v R1C1 se bere enako, ne glede na to, katera celica jo gosti, medtem ko se zapis te iste reference v A1 spreminja z vsako vrstico
Zapis se izkaže za nepogrešljivega v natanko enem scenariju, ki pa je zelo pogost: ustvarjanje predlog, kjer je treba isto relativno formulo vstaviti v vsako vrstico podatkovnega območja. V A1 morate znova in znova ustvarjati besedilo formule za vsako vrstico posebej. V R1C1 je to besedilo konstanta. To je tudi bližje načinu, kako formati datotek preglednic razmišljajo interno: zapisi skupnih formul (shared-formula) shranjujejo relativne reference kot odmike vrstic in stolpcev od celice gostiteljice, tako da je niz A1 za vsako vrstico nekaj, kar vaša koda sintetizira le zato, da ga razčlenjevalnik razstavi nazaj v odmike. R1C1 preskoči to nepotrebno pot. Za interaktivne formule, ki jih piše človek, ostaja A1 naravna izbira, zato je privzeto nastavljen povsod v HotXLS
Kako HotXLS kompilira formulo R1C1?
HotXLS izpostavlja to funkcijo na dveh ravneh, dodanih v različici v2.175.0. TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula je tista, ki jo večina kode kliče: vrača kompilirano formulo, pripravljeno za vrednotenje, natanko tako kot njena sestra A1 GetCompiledFormula, vendar z dvema dodatnima parametroma, ki določata 0-bazirano vrstico in stolpec celice, ki ji formula pripada. Pod njo TXLSFormula.GetCompiledR1C1 ustvari surovo sintaktično drevo, samostojna funkcija R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String pa izvede dejansko pretvorbo zapisa. Cevovod je namerno preprost: prevede besedilo R1C1 v enakovredno besedilo A1 z uporabo koordinat celice gostiteljice, nato pa kompilira besedilo A1 prek obstoječega pogona — istega pogona, ki razrešuje definirana imena in reference med listi ter kliče prilagojene funkcije delovnih listov
// 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)Ker se pretvorba zgodi pred kompilacijo, se vse v nadaljevanju obnaša, kot da ste sami napisali formulo A1. Klic R1C1ToA1('R2C3', 4, 3) vrne '$C$2' ne glede na celico gostiteljico, saj sta obe koordinati absolutni. R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3) — formula, ki živi v D5, ker sta CurRow = 4 in CurCol = 3 0-bazirana — vrne 'SUM(D2:D4)': odmiki so bili razrešeni glede na 5. vrstico in stolpec D ter oddani kot navadne relativne reference A1. Območja ne potrebujejo posebne obdelave; dvopičje se prenese skozi, vsaka končna točka pa se pretvori neodvisno
Zapolnjevanje stolpca z eno relativno formulo
Prednost se pokaže v zanki. Primerjajte različico A1, ki znova ustvarja besedilo formule pri vsaki iteraciji, z različico R1C1, kjer je formula konstanta in se premikajo le koordinate gostiteljice. Obe se komapilirata prek TXLSCalculator in vrednotita z GetValue; kalkulator ob ustvarjanju sprejme povratni klic (callback) ponudnika celic, tako da lahko ocenjevalnik pridobi vrednosti celic iz vašega vira podatkov
// 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;Zanka R1C1 nima nobenega preračunavanja vrstic v besedilu formule. 'RC[-2]*RC[-1]' pomeni "ista vrstica, dva stolpca levo, krat ista vrstica, en stolpec levo" v 2. vrstici in v 500. vrstici enako, in če se podatkovni blok kasneje premakne navzdol za vrstico z glavo, se konstanta formule ne spremeni — spremenijo se le meje zanke. Različica A1 ima dve mesti, kjer se lahko zmotite pri prilagoditvi + 1; različica R1C1 pa nima nobenega
Katere oblike R1C1 pretvornik sprejema?
Pretvornik R1C1ToA1 prepozna oblike, dokumentirane za različico v2.175.0: R[n]C[m] za relativne odmike v katero koli smer, RnCm za absolutno vrstico in stolpec, R[-n]C[m] z negativnimi odmiki, goli RC za samo celico s formulo in območja, kot sta R1C1:R3C3 ali R[-1]C:R[1]C. Vrstični in stolpčni deli se razčlenjujejo neodvisno, tako da delujejo tudi mešane oblike, kot je R[1]C3 — relativna vrstica, absoluten stolpec —, črke pa niso občutljive na velikost, zato se r[-1]c[2] prevede enako kot njena ustreznica z velikimi črkami. Deli v oglatih oklepajih postanejo nesidrane (relativne) koordinate A1; gole številke postanejo absolutne koordinate, sidrane z znakom $
Zanimivo vprašanje je, kako se pretvornik izogne popačenju vsega ostalega v formuli, saj sta črki R in C zelo pogosti. Njegovo pravilo razlikovanja temelji na žetonih: R se obravnava kot začetek reference le, če pred njim ni druge črke, kandidat pa se mora nato v celoti razčleniti — neobvezen del vrstice, obvezen C, neobvezen del stolpca —, sicer se besedilo obnovi nedotaknjeno. Zato klic ROUND(R[-1]C[0], 2) pretvori le notranjo referenco: črki R v besedilu ROUND sledi O in ne številka, oklepaj ali C, zato razčlemba spodleti, ime funkcije pa se prenese dobesedno. Enaka logika ščiti ROW(), imena funkcij, ki se začnejo s C, pa sploh nikoli niso kandidati, saj le R začne referenco. Opombe ob izdaji različice v2.175.0 navajajo, da so preskočeni tudi nizi in identifikatorji; kljub temu pa je, če kateri od dobesednih nizov pod navedki v vaši formuli vsebuje besedilo, oblikovano natanko kot referenca R1C1, vredno enkrat preveriti kompilirani izhod, preden mu zaupate v produkciji
Koordinatna izhodišča in podrobnosti o sidranju, ki jih je vredno poznati
Znotraj tega API-ja se srečata dve konvenciji in če ju ne zamešate, se izognete edini pravi pasti. Parametra CurRow in CurCol metode GetCompiledFormulaR1C1 sta 0-bazirana, kar sledi API-ju kalkulatorja, medtem ko so številke v samem zapisu 1-bazirane, kar ustreza temu, kar prikazuje Excel: R2C3 je 2. vrstica in 3. stolpec, kar je $C$2 in ne $D$3. Če je vaš števec zanke že 0-baziran, ga posredujete neposredno kot CurRow; prilagoditev + 1 se nahaja znotraj pretvornika in ne v vaši kodi
Še ena podrobnost je pomembna, če bo kompilirana formula preživela celico, za katero je bila kompilirana. Ko izpustite del vrstice ali stolpca — obliki RC[-1] ali R[2]C —, se izpuščena koordinata razreši v celico gostiteljico in se odda kot absolutna koordinata, sidrana z znakom $, v pretvorjenem besedilu A1. Ob vrednotenju je to nevidno, saj je vrednost v obeh primerih enaka. Vendar pa relativno proti absolutnemu sidranju določa, kako se reference premikajo, ko se vrstice ali stolpci kasneje vstavijo ali izbrišejo, kot je obravnavano v spremljevalnem članku o prilagajanju referenc formul. Če želite, da koordinata ostane relativna skozi strukturna urejanja, zapišite odmik eksplicitno — R[0]C[-1] namesto RC[-1] —, tako da pretvornik odda nesidrano referenco A1
Kompilacija R1C1 je del pogona formul v komponenti HotXLS Delphi Excel Component, skupaj s prevajalnikom A1, grafom preračunavanja in zgoraj prikazanim API-jem za vrednotenje. Če vaša koda gradi preglednice s spuščanjem formul po stolpcih prek zank, je premik teh zank iz spojenih nizov A1 v eno samo konstanto R1C1 ena najcenejših nadgradenj zanesljivosti, ki so na voljo