R1C1-formelnotation i Delphi med HotXLS

HotXLS kompilerer formler skrevet i R1C1-referencenotation gennem TXLSCalculator.GetCompiledFormulaR1C1, som accepterer former som R2C3 (absolut), R[-1]C[2] (relativ forskydning) og RC (den aktuelle celle), konverterer dem til A1-notation i forhold til rækken og kolonnen for den celle, formlen bor i, og sender resultatet til den samme compiler, der håndterer almindelige A1-formler. For Delphi- og C++Builder-kode, der genererer den samme formel på tværs af hundredvis af rækker, fjerner den ene metode en hel klasse af strengsammenkædningsfejl

Fejlklassen er velkendt for enhver, der har udfyldt en kolonne programmatisk. Du løber gennem rækkerne, og for hver række bygger du en A1-formelstreng med Format('D%d*E%d', [Row, Row]). Hver iteration splejser rækkenumre ind i tekst, og rækkenumrene er den eneste del, der ændrer sig. Tag fejl af forskydningen én gang, bland en 0-baseret løkketæller med 1-baserede A1-rækkenumre, eller forskyd datablokken med en overskriftsrække, og hver formel i kolonnen peger én række forkert. Intet kaster en fejl; tallene er simpelthen forkerte. Den formel, du egentlig mente, "gang de to celler til venstre for mig", nævner slet ikke et rækkenummer, og R1C1-notation lader dig skrive den på den måde

Hvad er R1C1-notation, og hvornår bør du bruge den?

R1C1-notation adresserer celler efter række- og kolonnenummer i stedet for kolonnebogstav plus rækkenummer, og den markerer relative referencer som eksplicitte forskydninger fra formelcellen. R2C3 er den absolutte celle i række 2, kolonne 3, som A1 staver $C$2. R[-1]C[2] er én række op og to kolonner til højre for det sted, formlen sidder. RC er selve formelcellen. Forskydningerne i kantede parenteser er pointen: en relativ reference i R1C1 læses ens, uanset hvilken celle der huser den, mens A1-gengivelsen af den samme reference ændrer sig med hver række

Notationen tjener sin plads i præcis ét scenarie, og det er et udbredt scenarie: skabelongenerering, hvor den samme relative formel skal plantes i hver række af et dataområde. I A1 skal du gengive formelteksten på ny pr. række. I R1C1 er teksten en konstant. Det ligger også tættere på, hvordan regnearksfilformater tænker internt: shared-formula-records gemmer relative referencer som række- og kolonneforskydninger fra værtscellen, så en A1-streng pr. række er noget, din kode syntetiserer, blot for at parseren kan dekomponere den tilbage til forskydninger igen. R1C1 springer den rundtur over. Til interaktive, menneskeskrevne formler forbliver A1 det naturlige valg, hvilket er grunden til, at det forbliver standarden overalt i HotXLS

Hvordan kompilerer HotXLS en R1C1-formel?

HotXLS eksponerer funktionen på to niveauer, tilføjet i v2.175.0. TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula er den, det meste kode kalder: den returnerer en kompileret formel klar til evaluering, præcis som sin A1-søskende GetCompiledFormula, men med to ekstra parametre, der navngiver den 0-baserede række og kolonne for den celle, formlen hører til. Under den producerer TXLSFormula.GetCompiledR1C1 det rå syntakstræ, og en selvstændig funktion R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String udfører selve notationskonverteringen. Pipelinen er bevidst enkel: oversæt R1C1-tekst til tilsvarende A1-tekst ved hjælp af værtscellens koordinater, og kompilér derefter A1-teksten gennem den eksisterende motor — den samme motor, der opløser definerede navne og referencer på tværs af ark og sender brugerdefinerede regnearksfunktioner videre

Fordi konverteringen sker før kompileringen, opfører alt nedstrøms sig, som om du selv havde skrevet A1-formlen. R1C1ToA1('R2C3', 4, 3) returnerer '$C$2' uanset værtscellen, fordi begge koordinater er absolutte. R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3) — en formel, der bor i D5, da CurRow = 4 og CurCol = 3 er 0-baserede — returnerer 'SUM(D2:D4)': forskydningerne blev opløst mod række 5, kolonne D, og udsendt som almindelige relative A1-referencer. Områder kræver ingen særbehandling; kolonet passerer igennem, og hvert endepunkt konverteres uafhængigt

// Formlen bor i D5: CurRow = 4, CurCol = 3 (begge 0-baserede)
S := R1C1ToA1('R2C3', 4, 3);
// S = '$C$2'  (absolut række og kolonne)

S := R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3);
// S = 'SUM(D2:D4)'  (forskydninger opløst mod D5)

S := R1C1ToA1('ROUND(R[-1]C[0], 2)', 4, 3);
// S = 'ROUND(D4, 2)'  (R'et i ROUND røres ikke)

Udfyldning af en kolonne med én relativ formel

Gevinsten viser sig i løkken. Sammenlign A1-versionen, som gengiver formelteksten på ny i hver iteration, med R1C1-versionen, hvor formlen er en konstant, og kun værtskoordinaterne flytter sig. Begge kompileres gennem TXLSCalculator og evalueres med GetValue; calculatoren tager et celle-provider-callback ved konstruktion, så evaluatoren kan hente celleværdier fra din datakilde

// A1-stil: en ny formelstreng for hver række
for Row := 1 to 500 do
begin
  FormulaText := Format('D%d*E%d', [Row + 1, Row + 1]);  // 1-baserede A1-rækker
  Compiled := Calc.GetCompiledFormula(FormulaText, 0);
  // ... evaluér, gem, frigiv ...
end;

const
  AmountFormula = 'RC[-2]*RC[-1]';  // to celler til venstre, samme række
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-løkken har slet ingen rækkearitmetik i formelteksten. 'RC[-2]*RC[-1]' betyder "samme række, to kolonner til venstre, gange samme række, én kolonne til venstre" i række 2 såvel som i række 500, og hvis datablokken senere flyttes ned med en overskriftsrække, ændrer formelkonstanten sig ikke — kun løkkegrænserne gør. A1-versionen har to steder, hvor + 1-justeringen kan gå galt; R1C1-versionen har nul

Hvilke R1C1-former accepterer konverteren?

Konverteren R1C1ToA1 genkender de former, der er dokumenteret for v2.175.0: R[n]C[m] for relative forskydninger i begge retninger, RnCm for absolut række og kolonne, R[-n]C[m] med negative forskydninger, det nøgne RC for selve formelcellen og områder som R1C1:R3C3 eller R[-1]C:R[1]C. Række- og kolonnedelene parses uafhængigt, så blandede former som R[1]C3 — relativ række, absolut kolonne — virker også, og bogstaverne skelner ikke mellem store og små, så r[-1]c[2] kompileres på samme måde som sin tvilling med store bogstaver. Dele i kantede parenteser bliver uforankrede (relative) A1-koordinater; nøgne tal bliver $-forankrede absolutte koordinater

Det interessante spørgsmål er, hvordan konverteren undgår at ødelægge alt andet i formlen, for R og C er almindelige bogstaver. Dens skelneregel er token-baseret: et R betragtes kun som starten på en reference, når det ikke er forudgået af et andet bogstav, og kandidaten skal derefter parses fuldstændigt — valgfri rækkedel, obligatorisk C, valgfri kolonnedel — ellers genoprettes teksten urørt. Det er derfor, ROUND(R[-1]C[0], 2) kun konverterer den indre reference: R'et i ROUND efterfølges af O frem for et ciffer, en parentes eller C, så parsingen fejler, og funktionsnavnet passerer ordret igennem. Den samme logik beskytter ROW(), og funktionsnavne, der begynder med C, er aldrig kandidater, da kun R starter en reference. Udgivelsesnoterne for v2.175.0 angiver, at strengliteraler og identifikatorer også springes over; alligevel er det værd at kontrollere det kompilerede output én gang, før du stoler på det i produktion, hvis en citeret literal i din formel tilfældigvis indeholder tekst formet præcis som en R1C1-reference

Koordinatbaser og forankringsdetaljer, der er værd at kende

To konventioner mødes inde i denne API, og at holde dem adskilt undgår den eneste reelle fælde. Parametrene CurRow og CurCol til GetCompiledFormulaR1C1 er 0-baserede efter calculator-API'en, mens tallene inde i selve notationen er 1-baserede og svarer til det, Excel viser: R2C3 er række 2, kolonne 3, hvilket er $C$2, ikke $D$3. Hvis din løkketæller allerede er 0-baseret, sender du den direkte videre som CurRow; + 1-justeringen bor inde i konverteren, ikke i din kode

Én detalje mere betyder noget, hvis den kompilerede formel skal overleve den celle, den blev kompileret til. Når du udelader en række- eller kolonnedel — formerne RC[-1] eller R[2]C — opløses den udeladte koordinat til værtscellen og udsendes som en absolut, $-forankret koordinat i den konverterede A1-tekst. På evalueringstidspunktet er dette usynligt, da værdien er den samme uanset hvad. Men forankring som relativ kontra absolut afgør, hvordan referencer forskydes, når rækker eller kolonner senere indsættes eller slettes, som beskrevet i den ledsagende artikel om justering af formelreferencer. Hvis du har brug for, at en koordinat forbliver relativ gennem strukturelle redigeringer, så skriv forskydningen eksplicit — R[0]C[-1] i stedet for RC[-1] — så konverteren udsender en uforankret A1-reference

R1C1-kompilering er en del af formelmotoren i HotXLS Delphi Excel Component, sammen med A1-compileren, genberegningsgrafen og den evaluerings-API, der er vist ovenfor. Hvis din kode bygger regneark ved at løbe formler ned ad kolonner, er det at flytte de løkker fra strengsplejset A1 til én enkelt R1C1-konstant en af de billigste pålidelighedsopgraderinger, der findes