HotXLS компилирует формулы, написанные в синтаксисе ссылок R1C1, с помощью метода TXLSCalculator.GetCompiledFormulaR1C1, который принимает такие формы, как R2C3 (абсолютная), R[-1]C[2] (относительное смещение) и RC (текущая ячейка), преобразует их в нотацию A1 на основе строки и столбца ячейки, в которой находится формула, и передает результат тому же компилятору, который обрабатывает обычные формулы A1. Для кода Delphi и C++Builder, генерирующего одну и ту же формулу в сотнях строк, этот метод устраняет целый класс багов, связанных со слиянием строк
Этот класс багов знаком каждому, кто программно заполнял столбцы данных. Вы обходите строки в цикле и для каждой строки строите строку формулы A1 с помощью Format('D%d*E%d', [Row, Row]). Каждая итерация вставляет номера строк в текст, и эти номера — единственное, что меняется. Стоит один раз ошибиться со смещением, перепутать индекс цикла с отсчетом от нуля и номера строк 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 используется чаще всего: он возвращает скомпилированную формулу, готовую к вычислению, аналогично методу A1 GetCompiledFormula, но принимает два дополнительных параметра, указывающих строку и столбец ячейки (отсчет от нуля), к которой относится формула. Под ним метод 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 и 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 отсчитываются от нуля, следуя API калькулятора, тогда как числа внутри самого синтаксиса начинаются с единицы, в соответствии с отображением в Excel: R2C3 — это строка 2, столбец 3, что соответствует $C$2, а не $D$3. Если ваш счетчик цикла уже отсчитывается от нуля, вы передаете его напрямую в CurRow; смещение + 1 учитывается внутри конвертера, в вашем коде его писать не нужно
Еще одна деталь важна, если скомпилированная формула должна пережить ячейку, для которой она была скомпилирована. Когда вы опускаете часть строки или столбца (формы RC[-1] или R[2]C), опущенная координата разрешается относительно исходной ячейки и выводится как абсолютная координата с закреплением $ в преобразованном тексте A1. При вычислении это незаметно, так как значение в обоих случаях одинаково. Однако разделение на относительные и абсолютные ссылки определяет, как они будут смещаться при последующей вставке или удалении строк/столбцов, о чем рассказано в сопутствующей статье о корректировке ссылок в формулах. Если вам нужно, чтобы координата оставалась относительной при структурном редактировании, пишите смещение явно — R[0]C[-1] вместо RC[-1] — чтобы конвертер вывел незакрепленную ссылку A1
Компиляция R1C1 является частью движка формул в составе HotXLS Delphi Excel Component, наряду с компилятором A1, графом перерасчета и API вычислений, показанными выше. Если ваш код строит электронные таблицы путем прогонки формул по столбцам в цикле, переход от слияния строк A1 к единой константе R1C1 — одно из самых простых решений для повышения надежности кода