HotXLS kompilerer formler skrevet i R1C1-referancenotation via 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 befinder sig i, og føder resultatet til den samme kompilator, der håndterer almindelige A1-formler. For Delphi- og C++Builder-kode, der genererer the samme formel på tværs af hundredvis af rækker, fjerner denne ene metode en hel klasse af strengsammenkædningsfejl
Fejlklassen er velkendt for enhver, der programmatisk har udfyldt en kolonne. Du løber gennem rækker, 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 teksten, og rækkenumrene er den eneste del, der ændrer sig. Får du forskydningen forkert én gang, blander en 0-baseret løkketæller med 1-baserede A1-rækkenumre eller forskyder datablokken med en overskriftsrække, vil hver formel i kolonnen pege en række forkert. Intet udløser fejl; tallene er simpelthen forkrøblede. Formlen, du rent faktisk mente, "multiplicer 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, hvor formlen befinder sig. RC er selve formelcellen. Forskydningerne i parentes er pointen: En relativ reference i R1C1 læses ens, uanset hvilken celle der er vært for den, mens A1-gengivelsen af den samme reference ændrer sig for hver række
Notationen beviser sit værd i netop ét scenarie, og det er et almindeligt et
Skabelongenerering, hvor den samme relative formel skal placeres i hver række i et dataområde. I A1 skal du re-rendere formelteksten pr. række. I R1C1 er teksten en konstant. Dette er også tættere på, hvordan regnearksfilformater tænker internt: Poster med delte formler gemmer relative referencer som række- og kolonneforskydninger fra værtscellen, så en A1-streng pr. række er noget, din kode syntetiserer, kun for at parseren kan dekomponere den direkte tilbage til forskydninger. R1C1 springer den omvej over. For interaktive formler skrevet af mennesker forbliver A1 det naturlige valg, hvorfor det forbliver standarden overalt i HotXLS
Hvordan kompilerer HotXLS een 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, som det meste kode kalder: Den returnerer en kompileret formel klar til evaluering, præcis som dens A1-søskende GetCompiledFormula, men med to ekstra parametre, der angiver den 0-baserede række og kolonne for den celle, formlen tilhører. Under den producerer TXLSFormula.GetCompiledR1C1 det rå syntakstræ, og en selvstændig funktion R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String udfører den faktiske notationskonvertering. Pipelinen er bevidst simpel: Oversæt R1C1-tekst to tilsvarende A1-tekst ved hjælp af værtscellens koordinater, og kompiler derefter A1-teksten via den eksisterende motor — den samme motor, der løser definerede navne og referencer på tværs af ark og sender brugerdefinerede regnearksfunktioner af sted
Fordi konvertering sker før kompilering, 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 i D5, da CurRow = 4 and CurCol = 3 er 0-baserede — returnerer 'SUM(D2:D4)': Forskydningerne blev løst mod række 5, kolonne D og udsendt som almindelige relative A1-referencer. Intervaller kræver ingen specialbehandling; kolonet passerer igennem, og hvert slutpunkt konverteres uafhængigt
// 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)Udfyldning af en kolonne med én relativ formel
A1-stil: En forskellig formelstreng for hver række
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-løkken har overhovedet ingen række-aritmetik i formelteksten. 'RC[-2]*RC[-1]' betyder "samme række, to kolonner til venstre, ganget med samme række, én kolonne til venstre" i række 2 og i række 500 ligeledes, og hvis datablokken senere flytter ned med en overskriftsrække, ændres formelkonstanten ikke — kun løkkegrænserne gør. A1-versionen har to steder, hvor man kan få + 1-justeringen forkert; 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, råt RC for selve formelcellen og intervaller som f.eks. R1C1:R3C3 eller R[-1]C:R[1]C. Række- og kolonnedelene fortolkes uafhængigt, så blandede former som R[1]C3 — relativ række, absolut kolonne — fungerer også, og bogstaverne er ufølsomme over for store og små bogstaver, så r[-1]c[2] kompilerer ens med dens store tvilling. Dele i parentes bliver til uforankrede (relative) A1-koordinater; rå tal bliver til $-forankrede absolutte koordinater
Det interessante spørgsmål er, hvordan konverteren undgår at spolere alt andet i formlen, da R og C er almindelige bogstaver. Dens diskriminationsregel 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 fortolkes fuldstændigt — valgfri række-del, obligatorisk C, valgfri kolonne-del — ellers gendannes teksten uberørt. Derfor konverterer ROUND(R[-1]C[0], 2) kun den indre reference: R i ROUND efterfølges af O snarere end af et ciffer, en parentes eller C, så fortolkningen mislykkes, og funktionsnavnet passerer ordret igennem. Den samme logik beskytter ROW(), og funktionsnavne, der begynder med C, er aldrig kandidater overhovedet, da kun R starter en reference. Versionsnoterne til v2.175.0 angiver, at strengliteraler og identifikatorer også springes over; alligevel er det værd, hvis en citeret literal i din formel tilfældigvis indeholder tekst formet præcis som en R1C1-reference, at kontrollere det kompilerede output én gang, før du stoler på det i produktion
Koordinatbaser og forankringsdetaljer, der er værd at kende
To konventioner mødes i denne API, og at holde dem adskilt forhindrer den eneste reelle fælde. Parametrene CurRow og CurCol i GetCompiledFormulaR1C1 er 0-baserede og følger beregner-API'en, mens tallene i selve notationen er 1-baserede, hvilket matcher det, Excel viser: R2C3 er række 2, kolonne 3, som er $C$2, ikke $D$3. Hvis din løkketæller allerede er 0-baseret, sender du den direkte videre som CurRow; + 1 bor i konverteren, ikke i din kode
Yderligere én detalje betyder noget, hvis den kompilerede formel vil 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. Ved evaluering er dette usynligt, da værdien er den samme under alle omstændigheder. Men relativ versus absolut forankring afgør, hvordan referencer forskyder sig, når rækker eller kolonner indsættes eller slettes senere, som dækket i den tilhørende artikel om formelreferencejustering. Hvis du har brug for, at en koordinat forbliver relativ under strukturelle redigeringer, skal du skrive 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-kompilatoren, genberegningsgrafen og evaluerings-API'en vist ovenfor. Hvis din kode bygger regneark ved at løbe formler ned af kolonner, er flytning af disse løkker fra streng-splejset A1 to en enkelt R1C1-konstant en af de billigste pålidelighedsopgraderinger, der findes