HotXLS компилира формули, написани в референтна нотация R1C1, чрез метода TXLSCalculator.GetCompiledFormulaR1C1, който приема форми като R2C3 (абсолютна), R[-1]C[2] (относително отместване) и RC (текущата клетка). Методът ги преобразува в нотация A1 на базата на реда и колоната на клетката, в която живее формулата, и подава резултата на същия компилатор, който обработва обикновените A1 формули. При Delphi и C++Builder код, който генерира една и съща формула в стотици редове, този единствен метод премахва цял клас от грешки, свързани със съединяването на низове
Този клас грешки е познат на всеки, който е попълвал колона програмно. Вие обхождате редовете в цикъл и за всеки ред изграждате низ с формула A1 чрез Format('D%d*E%d', [Row, Row]). Всяка итерация вмъква номера на реда в текста и тези номера са единствената часть, която се променя. Сбъркайте отместването само веднъж – като смесите брояч на цикъл с база 0 с номера на редове с база 1 в A1, или като отместите блока с данни с един заглавен ред – и всяка формула в колоната ще сочи с един ред встрани. Нищо не предизвиква грешка, но стойностите са просто грешни. Формулата, която всъщност имате предвид („умножи двете клетки вляво от мен“), изобщо не споменава номер на ред, а нотацията R1C1 ви позволява да я запишете точно по този начин
Какво представлява нотацията R1C1 и кога трябва да се използва?
Нотацията R1C1 адресира клетките по номер на ред и колона вместо по буква на колоната и номер на реда, и отбелязва относителните препратки като конкретни отмествания спрямо клетката с формулата. R2C3 е абсолютната клетка на ред 2, колона 3, която в нотация A1 се записва като $C$2. R[-1]C[2] е клетката един ред нагоре и две колони надясно от клетката на формулата. RC е самата клетка с формула. Квадратните скоби за отместване са ключови: относителна препратка в R1C1 изглежда еднакво, независимо в коя клетка се намира, докато изобразяването на същата препратка в нотация A1 се променя за всеки ред
Нотацията показва своята стойност в един конкретен и много често срещан сценарий: генериране на шаблони, при които една и съща относителна формула трябва да бъде вмъкната във всеки ред от областта с данни. При A1 трябва да пренаписвате текста на формулата за всеки ред. При R1C1 текстът е константа. Това съответства по-точно и на начина, по който форматите на електронните таблици работят вътрешно: споделените записи на формули съхраняват относителните препратки като отмествания за ред и колона от водещата клетка, така че низът A1 за всеки ред е нещо, което вашият код изгражда само за да бъде разглобено от анализатора обратно до отмествания. R1C1 избягва този двупосочен процес. За интерактивни, създавани от хора формули, A1 остава естественият избор, поради което е и подразбиращата се нотация в HotXLS
Как HotXLS компилира R1C1 формула?
HotXLS предоставя тази възможност на две нива, добавени във версия v2.175.0. Методът TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula е този, който се извиква най-често: той връща компилирана формула, готова за изчисляване, точно както своя събрат GetCompiledFormula в A1, но с два допълнителни параметъра, указващи реда и колоната с база 0 на клетката, към която принадлежи формулата. Под него методът TXLSFormula.GetCompiledR1C1 генерира дървото на синтаксиса, а самостоятелната функция R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String извършва реалното преобразуване на нотацията. Процесът е умишлено опростен: R1C1 текстът се превежда до еквивалентен A1 текст въз основа на координатите на клетката домакин, след което A1 текстът се компилира през съществуващото ядро – същото, което обработва дефинираните имена и препратките между страници и извиква персонализираните функции на работния лист
Тъй като конвертирането се случва преди компилацията, всичко след това работи по същия начин, по който би работило, ако бяхте написали A1 формула. Извикването R1C1ToA1('R2C3', 4, 3) връща '$C$2' независимо от клетката домакин, тъй като и двете координати са абсолютни. R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3) – формула в D5, тъй като CurRow = 4 and CurCol = 3 са с база 0 – връща 'SUM(D2:D4)': отместванията са изчислени спрямо ред 5, колона D, и са записани като чисти относителни A1 препратки. Диапазоните не се нуждаят от специална обработка; двоеточието се запазва и всяка крайна точка се преобразува независимо
// 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)Попълване на колона с една относителна формула
Ползата се вижда в цикъла. Сравнете A1 версията, която рендира отново текста на формулата при всяка стъпка, с R1C1 версията, където формулата е константа и се променят само координатите домакин. И двете се компилират чрез TXLSCalculator и се оценяват с GetValue; калкулаторът приема функция за обратна връзка при създаването си, за да може модулът за изчисление да извлича стойности на клетки от вашия източник на данни
// 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 цикъла изобщо липсва аритметика с номера на редове в текста на формулата. Изразът 'RC[-2]*RC[-1]' означава „същия ред, две колони вляво, по същия ред, една колона вляво“ както на ред 2, така и на ред 500, и ако областта с данни по-късно се отмести надолу заради заглавен ред, константата на формулата не се променя – променят се само границите на цикъла. В A1 версията има две места, където можете да сбъркате отместването + 1; в R1C1 версията те са нула
Кои R1C1 формати приема конверторът?
Конверторът R1C1ToA1 разпознава форматите, документирани за версия v2.175.0: R[n]C[m] за относителни отмествания в двете посоки, RnCm за абсолютни ред и колона, R[-n]C[m] с отрицателни отмествания, чисто RC за самата клетка с формула и диапазони като R1C1:R3C3 или R[-1]C:R[1]C. Частите за ред и колона се анализират независимо, така че смесени форми като R[1]C3 – относителен ред, абсолютна колона – също работят, а буквите не зависят от регистъра, така че r[-1]c[2] се компилира еднакво със своя главен вариант. Частите в скоби се превръщат в относителни A1 координати; чистите числа се превръщат в абсолютни координати със знак $
Интересният въпрос е как конверторът избягва повреждането на всичко останало във формулата, тъй като R и C са често срещани букви. Правилото за разпознаване е базирано на токени: буквата R се разглежда като начало на препратка само когато пред нея няма друга буква и самата препратка може да бъде анализирана напълно – незадължителна част за ред, задължително C, незадължителна част за колона – в противен случай текстът се оставя непроменен. Поради тази причина ROUND(R[-1]C[0], 2) преобразува само вътрешната препратка: след буквата R в ROUND следва O, а не цифра, скоба или C, така че анализът се прекратява и името на функцията се запазва. Същата логика предпазва и функцията ROW(), а имената на функции, започващи с C, никога не се разглеждат като кандидати, тъй като само R може да започне препратка. Бележките към версия v2.175.0 посочват, че текстовите литерали и идентификаторите също се пропускат; въпреки това, ако цитиран литерал във вашата формула съдържа текст, оформен точно като R1C1 препратка, си струва да проверите резултата, преди да го използвате в реална среда
Бази на координатите и детайли за свързването, които трябва да се знаят
Две конвенции се срещат в този приложен програмен интерфейс (API) и точното им спазване предпазва от единствения реален капан. Параметрите CurRow и CurCol на метода GetCompiledFormulaR1C1 са с база 0, следвайки логиката на калкулатора, докато числата в самата нотация са с база 1, съответствайки на изгледа в Excel: R2C3 е ред 2, колона 3, което е $C$2, а не $D$3. Ако вашият брояч на цикъл е с база 0, можете да го подадете директно като CurRow; добавянето на + 1 се извършва вътре в самия конвертор, а не във вашия код
Още един детайл е важен, ако компилираната формула ще продължи да съществува извън клетката, за която е била компилирана. Когато пропуснете част за ред или колона – формите RC[-1] или R[2]C – пропуснатата координата се приравнява към клетката домакин и се записва като абсолютна координата със знак $ в преобразувания A1 текст. При изчислението това е невидимо, тъй като стойността е една и съща. Но относителното спрямо абсолютното свързване определя как се изместват препратките при последващо вмъкване или изтриване на редове или колони, тема на съпътстващата статия за коригиране на препратки във формули. Ако желаете дадена координата да остане относителна при структурни промени, запишете отместването изрично – R[0]C[-1] вместо RC[-1] – така че конверторът да генерира несвързана A1 препратка
Компилирането на R1C1 е част от изчислителното ядро във HotXLS Delphi Excel Component, заедно с компилатора за A1, графа за преизчисляване и приложния програмен интерфейс за изчисление, показан по-горе. Ако вашият код генерира електронни таблици чрез обхождане на формули по колони, преминаването от ръчно съединявани низове в A1 към единна R1C1 константа е едно от най-лесните подобрения в сигурността на кода