HotXLS compileert formules die zijn geschreven in R1C1-verwijzingsnotatie via TXLSCalculator.GetCompiledFormulaR1C1. Deze methode accepteert vormen als R2C3 (absoluut), R[-1]C[2] (relatieve offset) en RC (de huidige cel), converteert ze naar A1-notatie op basis van de rij en kolom van de cel waarin de formule zich bevindt, en stuurt het resultaat naar dezelfde compiler die gewone A1-formules verwerkt. Voor Delphi- en C++Builder-code die dezelfde formule over honderden rijen genereert, elimineert die ene methode een hele klasse van bugs bij het samenvoegen van strings
De klasse van bugs is bekend bij iedereen die programmatisch een kolom heeft gevuld. U doorloopt rijen en voor elke rij bouwt u een A1-formulestring met Format('D%d*E%d', [Row, Row]). Elke iteratie voegt rijnummers in de tekst, en de rijnummers zijn het enige dat verandert. Als u de offset één keer verkeerd hebt, een 0-gebaseerde lusteller verwisselt met 1-gebaseerde A1-rijnummers, of het gegevensblok verschuift met een koprij, verwijst elke formule in de kolom naar een verkeerde rij. Niets veroorzaakt een uitzondering; de getallen zijn simpelweg onjuist. De formule die u eigenlijk bedoelde, "vermenigvuldig de twee cellen links van mij", noemt helemaal geen rijnummer, en met de R1C1-notatie kunt u dat zo schrijven
Wat is R1C1-notatie en wanneer moet u deze gebruiken?
De R1C1-notatie adresseert cellen met rij- en kolomnummers in plaats van kolomletters plus rijnummers, en markeert relatieve verwijzingen als expliciete offsets ten opzichte van de formulecel. R2C3 is de absolute cel op rij 2, kolom 3, wat in A1 wordt geschreven als $C$2. R[-1]C[2] is één rij omhoog en twee kolommen naar rechts vanaf de plaats waar de formule staat. RC is de formulecel zelf. De offsets tussen haakjes zijn de kern: een relatieve verwijzing in R1C1 luidt hetzelfde, ongeacht welke cel deze host, terwijl de A1-weergave van diezelfde verwijzing bij elke rij verandert
De notatie bewijst haar nut in exact één scenario, en dat is een veelvoorkomende: sjabloongeneratie, waarbij dezelfde relatieve formule in elke rij van een gegevensgebied moet worden geplaatst. In A1 moet u de formuletekst per rij opnieuw genereren. In R1C1 is de tekst een constante. Dit ligt ook dichter bij hoe bestandsindelingen van spreadsheets intern denken: gedeelde formulerecords slaan relatieve verwijzingen op als rij- en kolomoffsets vanaf de hostcel, dus een A1-string per rij is iets dat uw code alleen synthetiseert zodat de parser het direct weer kan ontleden in offsets. R1C1 slaat die omweg over. Voor interactieve, door mensen geschreven formules blijft A1 de natuurlijke keuze, en daarom blijft dit overal in HotXLS the standaard
Hoe compileert HotXLS een R1C1-formule?
HotXLS stelt de functie op twee niveaus beschikbaar, toegevoegd in v2.175.0. TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula is degene die de meeste code aanroept: het retourneert een gecompileerde formule die klaar is voor evaluatie, net als haar A1-tegenhanger GetCompiledFormula, maar met twee extra parameters die de 0-gebaseerde rij en kolom noemen van de cel waartoe de formule behoort. Daaronder produceert TXLSFormula.GetCompiledR1C1 de ruwe syntaxisboom, en een losstaande functie R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String voert de feitelijke notatieconversie uit. De pijplijn is bewust eenvoudig: vertaal R1C1-tekst naar gelijkwaardige A1-tekst met behulp van de coördinaten van de hostcel, en compileer vervolgens de A1-tekst via de bestaande engine — dezelfde engine die gedefinieerde namen en formules over bladen heen oplost en aangepaste werkbladfuncties afhandelt
Omdat conversie plaatsvindt vóór compilatie, gedraagt alles stroomafwaarts zich alsof u de A1-formule zelf had geschreven. R1C1ToA1('R2C3', 4, 3) retourneert '$C$2' ongeacht de hostcel, omdat beide coördinaten absoluut zijn. R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3) — een formule in D5, aangezien CurRow = 4 en CurCol = 3 0-gebaseerd zijn — retourneert 'SUM(D2:D4)': de offsets zijn opgelost ten opzichte van rij 5, kolom D, en gegenereerd als gewone relatieve A1-verwijzingen. Bereiken hebben geen speciale behandeling nodig; de dubbele punt wordt doorgegeven en elk eindpunt converteert onafhankelijk
// De formule bevindt zich in D5: CurRow = 4, CurCol = 3 (beide 0-gebaseerd)
S := R1C1ToA1('R2C3', 4, 3);
// S = '$C$2' (absolute rij en kolom)
S := R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3);
// S = 'SUM(D2:D4)' (offsets opgelost ten opzichte van D5)
S := R1C1ToA1('ROUND(R[-1]C[0], 2)', 4, 3);
// S = 'ROUND(D4, 2)' (de R in ROUND blijft ongewijzigd)Een kolom vullen met één relatieve formule
Het voordeel blijkt in de lus. Vergelijk de A1-versie, die de formuletekst bij elke iteratie opnieuw genereert, met de R1C1-versie, waar de formule een constante is en alleen de hostcoördinaten verschuiven. Beide compileren via TXLSCalculator en evalueren met GetValue; de calculator krijgt bij de constructie een celprovider-callback, zodat de evaluator celwaarden uit uw gegevensbron kan ophalen
// A1-stijl: een andere formulestring voor elke rij
for Row := 1 to 500 do
begin
FormulaText := Format('D%d*E%d', [Row + 1, Row + 1]); // 1-gebaseerde A1-rijen
Compiled := Calc.GetCompiledFormula(FormulaText, 0);
// ... evalueren, opslaan, vrijgeven ...
end;const
AmountFormula = 'RC[-2]*RC[-1]'; // twee cellen naar links, dezelfde rij
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;De R1C1-lus bevat helemaal geen rij-rekenkunde in de formuletekst. 'RC[-2]*RC[-1]' betekent "dezelfde rij, twee kolommen naar links, vermenigvuldigd met dezelfde rij, één kolom naar links" op zowel rij 2 als rij 500, en als het gegevensblok later omlaag verschuift door een koprij, verandert de formuleconstante niet — alleen de grenzen van de lus veranderen. De A1-versie heeft twee plaatsen waar de aanpassing + 1 fout kan gaan; de R1C1-versie heeft er nul
Welke R1C1-vormen accepteert de converter?
De R1C1ToA1-converter herkent de vormen die zijn gedocumenteerd voor v2.175.0: R[n]C[m] voor relatieve offsets in beide richtingen, RnCm voor absolute rij-en-kolom, R[-n]C[m] met negatieve offsets, een kale RC voor de formulecel zelf, en bereiken zoals R1C1:R3C3 of R[-1]C:R[1]C. De rij- en kolomdelen worden onafhankelijk geanalyseerd, dus gemengde vormen zoals R[1]C3 — relatieve rij, absolute kolom — werken ook, en de letters zijn hoofdletterongevoelig, dus r[-1]c[2] compileert hetzelfde als haar hoofdlettervariant. Delen tussen haakjes worden niet-verankerde (relatieve) A1-coördinaten; kale getallen worden met $ verankerde absolute coördinaten
De interessante vraag is hoe de converter voorkomt dat al het andere in de formule wordt verminkt, aangezien R en C veelvoorkomende letters zijn. De regel voor het onderscheid is gebaseerd op tokens: een R wordt alleen beschouwd als het begin van een verwijzing wanneer er geen andere letter aan voorafgaat, en de kandidaat moet vervolgens volledig kunnen worden ontleed — optioneel rijdeel, verplichte C, optioneel kolomdeel — anders wordt de tekst ongewijzigd hersteld. Daarom converteert ROUND(R[-1]C[0], 2) alleen de binnenste verwijzing: de R in ROUND wordt gevolgd door O in plaats van een cijfer, haakje of C, waardoor het ontleden mislukt en de functienaam ongewijzigd wordt doorgegeven. Dezelfde logica beschermt ROW(), en functienamen die beginnen met C zijn nooit kandidaten, aangezien alleen R een verwijzing start. De release-opmerkingen van v2.175.0 vermelden dat stringliterals en ID's ook worden overgeslagen; niettemin, als een geciteerde literal in uw formule tekst bevat die exact de vorm heeft van een R1C1-verwijzing, is het de moeite waard om de gecompileerde uitvoer eenmalig te checken voordat u deze in productie gebruikt
Coördinatenbases en verankeringsdetails die de moeite waard zijn om te weten
Twee conventies ontmoeten elkaar binnen deze API, en ze goed uit elkaar houden voorkomt de enige echte valkuil. De parameters CurRow en CurCol van GetCompiledFormulaR1C1 zijn 0-gebaseerd, volgens de calculator-API, terwijl de getallen binnen de notatie zelf 1-gebaseerd zijn, passend bij wat Excel weergeeft: R2C3 is rij 2, kolom 3, wat $C$2 is, niet $D$3. Als uw lusteller al 0-gebaseerd is, geeft u deze direct door als CurRow; de + 1 bevindt zich in de converter, niet in uw code
Nog een detail is belangrijk als de gecompileerde formule langer meegaat dan de cel waarvoor deze is gecompileerd. Wanneer u een rij- of kolomdeel weglaat — de vormen RC[-1] of R[2]C — wordt de weggelaten coördinaat opgelost naar de hostcel en gegenereerd als een absolute, met $ verankerde coördinaat in de geconverteerde A1-tekst. Bij de evaluatie is dit onzichtbaar, omdat de waarde in beide gevallen hetzelfde is. Maar relatieve versus absolute verankering bepaalt hoe verwijzingen verschuiven wanneer er later rijen of kolommen worden ingevoegd of verwijderd, zoals behandeld in het bijbehorende artikel over het aanpassen van formulereferenties. Als u wilt dat een coördinaat relatief blijft bij structurele bewerkingen, schrijf de offset dan expliciet — R[0]C[-1] in plaats van RC[-1] — zodat de converter een niet-verankerde A1-verwijzing genereert
R1C1-compilatie maakt deel uit van de formule-engine in het HotXLS Delphi Excel Component, samen met de A1-compiler, de herberekeningsgraaf en de evaluatie-API die hierboven zijn getoond. Als uw code spreadsheets bouwt door formules door kolommen te lussen, is het verplaatsen van die lussen van met strings samengevoegde A1 naar een enkele R1C1-constante een van de goedkoopste upgrades voor betrouwbaarheid die beschikbaar zijn