A HotXLS a TXLSCalculator.GetCompiledFormulaR1C1 segítségével fordítja le az R1C1 hivatkozási jelöléssel írt képleteket, amely elfogadja az olyan formákat, mint az R2C3 (abszolút), az R[-1]C[2] (relatív eltolás) és az RC (az aktuális cella), átalakítja azokat A1 jelöléssé a képletet tartalmazó cella sora és oszlopa alapján, majd átadja az eredményt ugyanannak a fordítónak, amely a normál A1 képleteket kezeli. Az olyan Delphi és C++Builder kódok esetében, amelyek ugyanazt a képletet generálják sorok százaiban, ez az egyetlen metódus a karakterlánc-összefűzési hibák egy egész osztályát küszöböli ki.
Ez a hibatípus ismerős mindenkinek, aki már töltött fel programozottan oszlopokat. Végigfut a sorokon, és minden sorhoz felépít egy A1 képletsort a Format('D%d*E%d', [Row, Row]) segítségével. Minden iteráció beilleszti a sorszámokat a szövegbe, és a sorszám az egyetlen rész, ami változik. Ha egyszer elrontja az eltolást, összekeveri a 0-alapú ciklusszámlálót az 1-alapú A1 sorszámokkal, vagy eltolja az adatblokkot egy fejléccel, a hasábban lévő összes képlet egy sorral elcsúszik. Semmi sem vált ki hibát; a számok egyszerűen hibásak lesznek. A ténylegesen kívánt képlet, 'szorozd meg a tőlem balra lévő két cellát', egyáltalán nem említ sorszámot, és az R1C1 jelölés lehetővé teszi, hogy így írja le.
Mi az az R1C1 jelölés, és mikor érdemes használni?
Az R1C1 jelölés a cellákat sorszám és oszlopszám alapján címzi meg az oszlopbetű és sorszám helyett, és a relatív hivatkozásokat a képlet cellájától számított explicit eltolásokként jelöli. Az R2C3 az abszolút cella a 2. sorban és a 3. oszlopban, amit az A1 $C$2-ként ír. Az R[-1]C[2] egy sorral feljebb és két oszloppal jobbra található attól a cellától, amelyben a képlet található. Az RC maga a képlet cellája. A szögletes zárójelben lévő eltolások jelentik a lényeget: egy relatív hivatkozás az R1C1-ben ugyanúgy néz ki, függetlenül attól, hogy melyik cella tartalmazza, míg ugyanezen hivatkozás A1-es ábrázolása minden sorral változik.
Ez a jelölés pontosan egy forgatókönyvben bizonyítja be a hasznosságát, amely viszont igen gyakori: a sablonok generálásakor, amikor ugyanazt a relatív képletet kell elhelyezni egy adatrégió minden sorában. Az A1-ben soronként újra kell generálni a képletszöveget. Az R1C1-ben a szöveg állandó. Ez közelebb áll ahhoz is, ahogyan a táblázatkezelő fájlformátumok belsőleg gondolkodnak: a megosztott képletek rekordjai a relatív hivatkozásokat a gazdacellához képesti sor- és oszlopeltolásként tárolják, így a soronkénti A1 karakterlánc olyasmi, amit a kód csak azért szintetizál, hogy az értelmező azonnal visszafejtse eltolásokká. Az R1C1 megspórolja ezt a felesleges kört. Az interaktív, ember által írt képleteknél továbbra is az A1 a kézenfekvő választás, ezért ez marad az alapértelmezett mindenhol a HotXLS-ben.
Hogyan fordítja le a HotXLS az R1C1 képleteket?
A HotXLS a v2.175.0 verzióban bevezetett két szinten teszi elérhetővé ezt a funkciót. A legtöbb kód a TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula metódust hívja meg: ez egy kiértékelésre kész, lefordított képletet ad vissza, pontosan úgy, mint az A1-es párja, a GetCompiledFormula, de két további paraméterrel, amelyek megadják a képlethez tartozó cella 0-alapú sorát és oszlopát. Ez alatt a TXLSFormula.GetCompiledR1C1 előállítja a nyers szintaxisfát, egy önálló R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String függvény pedig elvégzi a tényleges jelöléskonverziót. A folyamat szándékosan egyszerű: fordítsa le az R1C1 szöveget a megfelelő A1 szövegre a gazdacella koordinátái alapján, majd fordítsa le az A1 szöveget a meglévő motorral — ugyanazzal a motorral, amely feloldja a definiált neveket és a munkalapok közötti hivatkozásokat, valamint elosztja a kérvényezett egyéni munkalap-függvényeket.
Mivel a konverzió a fordítás előtt történik, a folyamat minden későbbi lépése úgy viselkedik, mintha saját maga írta volna az A1 képletet. A R1C1ToA1('R2C3', 4, 3) a '$C$2' értéket adja vissza, függetlenül a gazdacellától, mivel mindkét koordináta abszolút. A R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3) — egy D5-ben lévő képlet, mivel a CurRow = 4 és a CurCol = 3 0-alapúak — a 'SUM(D2:D4)' értéket adja vissza: az eltolások az 5. sor, D oszlop alapján lettek feloldva, és egyszerű relatív A1 hivatkozásokként lettek kibocsátva. A tartományok nem igényelnek speciális kezelést; a kettőspont áthalad, és az egyes végpontok egymástól függetlenül konvertálódnak.
// A képlet a D5 cellában található: CurRow = 4, CurCol = 3 (mindkettő 0-alapú)
S := R1C1ToA1('R2C3', 4, 3);
// S = '$C$2' (abszolút sor és oszlop)
S := R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3);
// S = 'SUM(D2:D4)' (a D5 cellára feloldott eltolások)
S := R1C1ToA1('ROUND(R[-1]C[0], 2)', 4, 3);
// S = 'ROUND(D4, 2)' (a ROUND-ban lévő R érintetlen marad)Oszlop feltöltése egyetlen relatív képlettel
A megtérülés a ciklusban mutatkozik meg. Hasonlítsa össze az A1-es verziót, amely minden iterációban újra előállítja a képletszöveget, az R1C1 verzióval, ahol a képlet állandó, és csak a gazdakoordináták változnak. Mindkettő a TXLSCalculator-on keresztül fordul le, és a GetValue segítségével kerül kiértékelésre; a kalkulátor a létrehozásakor egy cella-szolgáltató visszahívást (callback) vesz át, így a kiértékelő le tudja kérni a cellák értékeit az Ön adatforrásából.
// A1 stílus: eltérő képletsor minden egyes sorhoz
for Row := 1 to 500 do
begin
FormulaText := Format('D%d*E%d', [Row + 1, Row + 1]); // 1-alapú A1 sorok
Compiled := Calc.GetCompiledFormula(FormulaText, 0);
// ... kiértékelés, mentés, felszabadítás ...
end;const
AmountFormula = 'RC[-2]*RC[-1]'; // két cellával balra, ugyanabban a sorban
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;A R1C1 ciklusban egyáltalán nincs soraritmetika a képletszövegben. Az 'RC[-2]*RC[-1]' azt jelenti, hogy 'ugyanaz a sor, két oszloppal balra, megszorozva ugyanazzal a sorral, egy oszloppal balra' a 2. sorban és az 500. sorban egyaránt, és ha az adatblokk később lejjebb kerül egy fejlécsorral, a képletállandó nem változik — csak a ciklus határai. Az A1-es verziónak két helye is van, ahol el lehet rontani a + 1-es korrekciót; az R1C1-es verziónál ez nulla.
Mely R1C1 formátumokat fogadja el a konverter?
A R1C1ToA1 konverter a v2.175.0 verzióhoz dokumentált formákat ismeri fel: R[n]C[m] a relatív eltolásokhoz mindkét irányban, RnCm az abszolút sorhoz és oszlophoz, R[-n]C[m] negatív eltolásokkal, a sima RC magához a képletcellához, valamint a tartományokat, mint például a R1C1:R3C3 vagy a R[-1]C:R[1]C. A sor és oszlop részek függetlenül kerülnek értelmezésre, így a vegyes formák is működnek, mint például a R[1]C3 — relatív sor, abszolút oszlop —, és a betűk nem érzékenyek a kis- és nagybetűkre, így az r[-1]c[2] ugyanúgy fordul le, mint a nagybetűs megfelelője. A szögletes zárójeles részek rögzítetlen (relatív) A1 koordinátákká válnak; a zárójel nélküli számok pedig $ jellel rögzített abszolút koordinátákká.
Az érdekes kérdés az, hogyan kerüli el a konverter, hogy a képletben minden mást megrongáljon, mivel az R és a C gyakori betűk. A megkülönböztetési szabálya token-alapú: egy R betű csak akkor minősül egy hivatkozás kezdetének, ha nem áll előtte másik betű, és a jelöltnek ezután teljesen értelmezhetőnek kell lennie — opcionális sor rész, kötelező C, opcionális oszlop rész —, különben a szöveg érintetlenül áll vissza. Ezért a ROUND(R[-1]C[0], 2) csak a belső hivatkozást konvertálja: a ROUND-ban lévő R-t az O követi a számjegy, szögletes zárójel vagy C helyett, így az értelmezés sikertelen, és a függvény neve szó szerint átmegy. Ugyanez a logika védi a ROW() függvényt is, a C betűvel kezdődő függvénynevek pedig egyáltalán nem jöhetnek szóba, mivel csak az R indít el hivatkozást. A v2.175.0 kiadási megjegyzései szerint a karakterlánc-literálok és az azonosítók szintén kimaradnak; ennek ellenére, ha a képletben lévő idézőjeles literál véletlenül pontosan olyan szöveget tartalmaz, amely úgy néz ki, mint egy R1C1 hivatkozás, érdemes ellenőrizni a lefordított eredményt, mielőtt éles környezetben megbízna benne.
Koordináta-alapok és rögzítési részletek, amelyeket érdemes tudni
Két konvenció találkozik ezen az API-n belül, és ezek tisztán tartása segít elkerüli az egyetlen igazi csapdát. A GetCompiledFormulaR1C1 CurRow és CurCol paraméterei 0-alapúak, követve a kalkulátor API-t, míg magán a jelölésen belüli számok 1-alapúak, igazodva az Excel által megjelenítetthez: az R2C3 a 2. sor, 3. oszlop, ami a $C$2, nem pedig a $D$3. Ha a ciklusszámlálója már 0-alapú, akkor közvetlenül átadhatja a CurRow paraméternek; a + 1-es eltolás a konverteren belül történik meg, nem a kódjában.
Még egy részlet számít, ha a lefordított képlet tovább él, mint a cella, amelyhez lefordították. Ha elhagy egy sor- vagy oszloprészt — az RC[-1] vagy R[2]C formákban —, az elhagyott koordináta a gazdacellára oldódik fel, és abszolút, $-rögzített koordinátaként jelenik meg a konvertált A1 szövegben. A kiértékelés idején ez láthatatlan, mivel az érték mindkét esetben azonos. A relatív vagy abszolút rögzítés viszont meghatározza, hogyan tolódnak el a hivatkozások, ha később sorokat vagy oszlopokat szúrnak be vagy törölnek, amint azt a képlethivatkozások igazításáról szóló kísérőcikk tárgyalja. Ha azt szeretné, hogy a koordináta a strukturális szerkesztések során is relatív maradjon, írja ki az eltolást explicit módon — R[0]C[-1] az RC[-1] helyett —, így a konverter rögzítetlen A1 hivatkozást bocsát ki.
Az R1C1 fordítás a HotXLS Delphi Excel komponens képletmotorjának része, az A1 fordítóval, az újraszámítási gráffal és a fent bemutatott kiértékelő API-val együtt. Ha a kódja úgy épít táblázatokat, hogy képleteket ciklikusan futtat le oszlopokban, akkor ezen ciklusok átvezetése a karakterlánc-összefűzött A1-ről egyetlen R1C1 konstansra az egyik legolcsóbb megbízhatósági fejlesztés, ami elérhető.