R1C1 formulių žymėjimas Delphi aplinkoje su HotXLS

„HotXLS“ sukompiliuoja formules, parašytas R1C1 nuorodų žymėjimu, per metodą „TXLSCalculator.GetCompiledFormulaR1C1“, kuris priima tokias formas kaip „R2C3“ (absoliuti), „R[-1]C[2]“ (santykinis poslinkis) ir „RC“ (dabartinis langelis), konvertuoja jas į A1 žymėjimą pagal langelio eilutę bei stulpelį ir perduoda rezultatą tam pačiam kompiliatoriui, kuris apdoroja įprastas A1 formules. Delphi ir C++Builder kodui, generuojančiam tą pačią formulę šimtuose eilučių, šis vienas metodas pašalina visą eilutę klaidų, susijusių su eilučių sujungimu (string concatenation)

Ši klaidų klasė pažįstama visiems, kas programiškai pildė lentelės stulpelį. Jūs sukate ciklą per eilutes ir kiekvienai eilutei kuriate A1 formulės eilutę, pavyzdžiui, su „Format('D%d*E%d', [Row, Row])“. Kiekviena iteracija į tekstą įterpia eilučių numerius, ir eilučių numeriai yra vienintelė besikeičianti dalis. Suklyskite dėl poslinkio tik kartą — sumaišykite 0-pagrindu sukurtą ciklo skaitiklį su 1-pagrindu veikiančiais A1 eilučių numeriais arba pastumkite duomenų bloką dėl antraštės eilutės — ir kiekviena stulpelio formulė rodys viena eilute pro šalį. Jokios klaidos nekyla, tiesiog rezultatai yra neteisingi. Formulė, kurią iš tikrųjų turėjote omenyje — „padaugink du langelius iš mano kairės“ — niekur nemini eilutės numerio, ir R1C1 žymėjimas leidžia ją taip užrašyti

Kas yra R1C1 žymėjimas ir kada jį naudoti?

R1C1 žymėjimas adresuoja langelius pagal eilutės ir stulpelio numerį, o ne stulpelio raidę bei eilutės numerį, ir nurodo santykines nuorodas kaip aiškius poslinkius nuo formulės langelio. „R2C3“ yra absoliutus langelis 2 eilutėje, 3 stulpelyje, kurį A1 žymi „$C$2“. „R[-1]C[2]“ yra viena eilute aukščiau ir dviem stulpeliais į dešinę nuo formulės vietos. „RC“ yra pats formulės langelis. Laužtiniuose skliaustuose nurodyti poslinkiai yra svarbiausia dalis: santykinė nuoroda R1C1 žymėjime atrodo vienodai, nepriklausomai nuo to, kuriame langelyje ji yra, tuo tarpu tos pačios nuorodos A1 žymėjimas keičiasi su kiekviena eilute

Šis žymėjimas labiausiai pasiteisina viename dažname scenarijuje — šablonų generavime, kur tą pačią santykinę formulę reikia įrašyti į kiekvieną duomenų srities eilutę. A1 žymėjime turite iš naujo kurti formulės tekstą kiekvienai eilutei. R1C1 žymėjime šis tekstas yra konstanta. Tai taip pat artimiau tam, kaip skaičiuoklės failų formatai mąsto viduje: bendrųjų formulių įrašai saugo santykines nuorodas kaip eilučių ir stulpelių poslinkius nuo pagrindinio langelio, todėl kiekvienos eilutės A1 tekstas yra tai, ką jūsų kodas sukuria tik tam, kad analizatorius vėl išskaidytų į poslinkius. R1C1 išvengia šio papildomo kelio. Interaktyvioms, žmonių kuriamoms formulėms A1 išlieka natūralus pasirinkimas, todėl jis yra numatytasis visoje „HotXLS“ sistemoje

Kaip HotXLS sukompiliuoja R1C1 formulę?

„HotXLS“ pateikia šią funkciją dviem lygiais, pradedant v2.175.0 versija. „TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula“ yra tai, ką kviečia didžioji dalis kodo: ji grąžina sukompiliuotą formulę, paruoštą vertinimui, lygiai taip pat, kaip ir jos A1 analogas „GetCompiledFormula“, tačiau su dviem papildomais parametrais, nurodančiais 0-pagrindu veikiančią langelio eilutę bei stulpelį, kuriam priklauso formulė. Žemesniame lygyje „TXLSFormula.GetCompiledR1C1“ sukuria sintaksės medį, o atskira funkcija „R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String“ atlieka patį žymėjimo konvertavimą. Šis konvejeris yra paprastas: R1C1 tekstas išverčiamas į atitinkamą A1 tekstą, naudojant pagrindinio langelio koordinates, o tada A1 tekstas sukompiliuojamas per esamą variklį — tą patį variklį, kuris išsprendžia apibrėžtus pavadinimus bei tarpusavio lapų nuorodas ir kviečia nestandartines funkcijas

Kadangi konvertavimas vyksta prieš kompiliavimą, profesine prasme viskas toliau veikia taip, lyg patys būtumėte parašę A1 formulę. „R1C1ToA1('R2C3', 4, 3)“ grąžina „'$C$2'“ nepriklausomai nuo langelio vietos, nes abi koordinatės yra absoliučios. „R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3)“ — formulė, esanti D5 langelyje, kadangi „CurRow = 4“ ir „CurCol = 3“ (skaičiuojant nuo 0) — grąžina „'SUM(D2:D4)'“: poslinkiai buvo išspręsti pagal 5 eilutę bei stulpelį D ir pateikti kaip paprastos santykinės A1 nuorodos. Diapazonams nereikia jokių išimčių; dvitaškis išlaikomas, o kiekvienas taškas konvertuojamas nepriklausomai

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

Stulpelio užpildymas viena santykine formule

Nauda atsiskleidžia cikle. Palyginkite A1 versiją, kuri kuria formulės tekstą kiekvienoje iteracijoje, su R1C1 versija, kur formulė yra konstanta ir keičiasi tik langelio koordinatės. Abi versijos kompiliuojamos per „TXLSCalculator“ ir vertinamos su „GetValue“; skaičiuotuvas priima langelių tiekėjo iškvietimą (callback) kūrimo metu, todėl vertintojas gali pasiimti reikšmes tiesiai iš jūsų duomenų šaltinio

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

R1C1 cikle nėra jokios eilučių aritmetikos pačiame formulės tekste. „'RC[-2]*RC[-1]'“ reiškia „ta pati eilutė, dviem stulpeliais kairiau, padauginta iš tos pačios eilutės, vienu stulpeliu kairiau“ tiek 2 eilutėje, tiek 500 eilutėje. O jei duomenų blokas vėliau pastumiamas dėl antraštės eilutės, formulės konstanta nesikeičia — keičiasi tik ciklo ribos. A1 versija turi dvi vietas suklysti dėl „+ 1“ koregavimo; R1C1 versija turi nulį

Kurias R1C1 formas priima konvertavimo funkcija?

Konvertavimo funkcija „R1C1ToA1“ atpažįsta dokumentuotas v2.175.0 formas: „R[n]C[m]“ santykiniams poslinkiams abiem kryptimis, „RnCm“ absoliučioms eilutėms ir stulpeliams, „R[-n]C[m]“ su neigiamais poslinkiais, tiesiog „RC“ pačiam formulės langeliui ir diapazonus, tokius kaip „R1C1:R3C3“ arba „R[-1]C:R[1]C“. Eilutės ir stulpelio dalys analizuojamos atskirai, todėl veikia ir mišrios formos (pvz., „R[1]C3“ — santykinė eilutė, absoliutus stulpelis), o raidės yra nesvarbios (case-insensitive), todėl „r[-1]c[2]“ sukompiliuojama taip pat, kaip ir didžiosiomis raidėmis parašyta versija. Dalys laužtiniuose skliaustuose tampa nefiksuotomis (santykinėmis) A1 koordinatėmis; pliki skaičiai tampa absoliučiomis koordinatėmis su „$“ ženklu

Įdomus klausimas yra tai, kaip konvertavimo funkcija išvengia viso kito formulės teksto sugadinimo, nes R ir C yra dažnos raidės. Jos atpažinimo taisyklė yra pagrįsta ženklais (token-based): „R“ laikoma nuorodos pradžia tik tada, kai prieš ją nėra kitos raidės, ir visas elementas turi būti pilnai išanalizuotas — neprivaloma eilutės dalis, privaloma „C“, neprivaloma stulpelio dalis — kitaip tekstas grąžinamas nepakeistas. Štai kodėl „ROUND(R[-1]C[0], 2)“ pakeičia tik vidinę nuorodą: po „R“ žodyje „ROUND“ seka „O“, o ne skaičius, skliaustas ar „C“, todėl analizė nepavyksta ir funkcijos pavadinimas išlaikomas nepakeistas. Ta pati logika apsaugo funkciją „ROW()“, o funkcijos, prasidedančios raide „C“, apskritai nėra tikrinamos, nes nuoroda visada prasideda raide „R“. Versijos v2.175.0 pastabose nurodoma, kad tekstinės reikšmės kabutėse ir identifikatoriai taip pat yra praleidžiami; vis dėlto, jei jūsų formulės tekste kabutėse yra tekstas, tiksliai atitinkantis R1C1 struktūrą, verta patikrinti sukompiliuotą rezultatą prieš naudojant jį gamyboje

Koordinatės ir fiksavimo detalės, kurias verta žinoti

Šioje API susitinka dvi konvencijos, ir aiškus jų supratimas padeda išvengti vienintelio spąsto. „GetCompiledFormulaR1C1“ parametrai „CurRow“ ir „CurCol“ prasideda nuo 0 (pagal skaičiuotuvo API), o skaičiai pačiame žymėjime prasideda nuo 1 (kaip rodo „Excel“): „R2C3“ yra 2 eilutė, 3 stulpelis, kas atitinka „$C$2“, o ne „$D$3“. Jei jūsų ciklo skaitiklis prasideda nuo 0, perduodate jį tiesiai kaip „CurRow“; reikšmės padidinimas vienetu „+ 1“ vykdomas konvertavimo funkcijos viduje, o ne jūsų kode

Dar viena detalė yra svarbi, jei sukompiliuota formulė išgyvens ilgiau nei langelis, kuriam ji buvo sukurta. Kai praleidžiate eilutės ar stulpelio dalį — pavyzdžiui, „RC[-1]“ arba „R[2]C“ — praleista koordinatė priskiriama pagrindiniam langeliui ir A1 tekste pateikiama kaip absoliuti koordinatė su „$“ ženklu. Vertinimo metu tai nematoma, nes reikšmė bet kuriuo atveju yra ta pati. Tačiau santykinis arba absoliutus fiksavimas lemia, kaip nuorodos slinksis, kai vėliau bus įterptos ar ištrintos eilutės ar stulpeliai, kas aprašyta straipsnyje apie formulių nuorodų koregavimą. Jei norite, kad koordinatė liktų santykinė per struktūrinius redagavimus, užrašykite poslinkį aiškiai — „R[0]C[-1]“ vietoj „RC[-1]“ — kad konvertavimo funkcija sukurtų nefiksuotą A1 nuorodą

R1C1 kompiliavimas yra dalis formulių variklio „HotXLS Delphi Excel Component“ komponente, skirtame Delphi ir C++Builder, kartu su A1 kompiliatoriumi, perskaičiavimo grafu bei aukščiau parodytu vertinimo API. Jei jūsų kodas kuria lenteles sukdamas ciklą per stulpelio formules, šių ciklų perkėlimas iš sujungtų A1 eilučių į vieną R1C1 konstantą yra vienas greičiausių ir naudingiausių patikimumo patobulinimų