Формулы динамических массивов (spill) в Delphi с помощью HotXLS

HotXLS вычисляет формулы динамических массивов (spill) Excel 365 непосредственно в Delphi и C++Builder: вы передаете ему книгу, ячейки которой содержат функции XLOOKUP или FILTER, а его вычислитель формул рассчитывает результирующий набор данных и распределяет его по выходному прямоугольнику, возвращая те же значения, которые сгенерировал бы Excel. Компонент HotXLS для Delphi моделирует формулу динамического массива как одну корневую ячейку, владеющую вычислением, и набор зависимых ячеек, которые считывают ее результаты — это ключевое представление, которое необходимо усвоить перед написанием кода

Эта модель важна, поскольку решаемая практическая задача вполне конкретна. Клиент присылает файл .xlsx, созданный в Excel 365, таблицы которого заполнены функциями =FILTER(...) и =XLOOKUP(...), и ваша служба должна воспроизвести те же результаты в автономном режиме без установки Excel, а затем либо прочитать сгенерированные значения, либо записать собственную область динамического массива. Динамические массивы изменили логику вычислений с «одна формула — одна ячейка» на «одна формула — прямоугольник ячеек», и вычислитель HotXLS следует этой логике, а не имитирует ее на предварительно расширенной сетке

Как вычислять XLOOKUP и FILTER в Delphi?

HotXLS направляет вычисление каждой функции динамического массива через единую точку входа CalcDynArrayFunc в модуле lxCalc.pas, поэтому все они используют один и тот же алгоритм. Поддерживаемый набор включает XLOOKUP и FILTER (оригинальные две функции), а также XMATCH, SORT, UNIQUE и SEQUENCE. Каждая из них возвращает двумерный массив variant вместо скалярного значения: вызов SEQUENCE(3;2;1;1) дает сетку 3x2, SORT переупорядочивает строки по любой ключевой колонке по возрастанию или убыванию, UNIQUE убирает дублирующиеся строки с сохранением первого вхождения, а XMATCH сообщает позицию значения (начиная с 1) в тех же режимах точного совпадения, поиска по шаблону, следующего меньшего и следующего большего значений, что и XLOOKUP. В отличие от скалярных функций статистического распределения, которые возвращают одно число за один вызов, эти функции возвращают целую двумерную область

Чтобы рассчитать книгу, уже содержащую такие формулы, откройте ее, один раз вызовите Recalculate и прочитайте ячейки, в которые попали вычисленные данные. Метод TXLSXWorkbook.Recalculate обходит дерево зависимостей и вычисляет каждую изменившуюся формулу в топологическом порядке, благодаря чему корень динамического массива рассчитывается ровно один раз, а его элементы записываются непосредственно в ячейки

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  r, c: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('from-customer.xlsx');
    Book.Recalculate;                 // evaluate every formula, spills included
    Sheet := Book.Sheets[1];          // Sheets is 1-based
    // Read back the block the XLOOKUP / FILTER spilled into, say D2:F9.
    for r := 2 to 9 do
      for c := 4 to 6 do
        Writeln(Sheet.Cells[r, c].Text);
  finally
    Book.Free;
  end;
end;

Если книге требуется функция, отсутствующая в движке по умолчанию, тот же вычислитель позволяет подключить вашу собственную логику через механизм пользовательских функций листа, а пользовательская функция может возвращать массив variant, чтобы вести себя абсолютно аналогично встроенным динамическим массивам

Фиксация диапазона массива с помощью SetArrayFormula

HotXLS никогда не пытается угадать размер результирующего массива: вы сами указываете выходной прямоугольник, и метод TXLSRange.SetArrayFormula привязывает к нему формулу. Этот метод компилирует формулу один раз, сохраняет дерево разбора в левой верхней корневой ячейке вместе с целевым диапазоном и присваивает каждой другой ячейке в прямоугольнике облегченную формулу со ссылкой на корень. При пересчете корень вычисляется один раз, а элементы его матрицы транслируются непосредственно на каждую ячейку диапазона, благодаря чему корень динамического массива отображается как один узел в графе зависимостей инкрементного пересчета. Этот способ следует использовать, когда задача состоит в записи области динамического массива обратно в файл, а не в ее чтении

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('report.xlsx');
    Sheet := Book.Sheets[1];
    // Size the rectangle to the result: a 3-row by 2-column grid.
    if Sheet.Range['A1:B3'].SetArrayFormula('=SEQUENCE(3;2;1;1)') > 0 then
      Book.Recalculate;               // fill A1:B3 with 1..6 down the rows
    Book.SaveAs('report-out.xlsx');
  finally
    Book.Free;
  end;
end;

Следствием явного указания диапазона является важное ограничение: HotXLS не изменяет размер области динамического массива автоматически, как это делает интерактивный Excel. Excel увеличивает и уменьшает область динамического массива по мере изменения входных данных и выдает ошибку #SPILL!, если целевые ячейки заняты. В HotXLS прямоугольник, который вы передаете в SetArrayFormula, фиксирован, поэтому вам нужно задать его под ожидаемый результат. Если функция возвращает больше строк, чем вы указали, лишним данным просто некуда будет записаться

Поэлементная трансляция массивов в операторах

HotXLS применяет арифметические операторы поэлементно, когда хотя бы одна из сторон операции является массивом. Вспомогательный метод ApplyArrayBinaryOp поддерживает операторы +, -, *, / и ^ над одномерными и двумерными массивами variant: скалярный операнд транслируется на каждый элемент, оба массива должны совпадать по форме (иначе операция отклоняется), а деление на ноль возвращает ошибку, не вызывая сбоя программы. Скалярный арифметический путь остается незатронутым, поэтому поэлементная логика активируется только тогда, когда хотя бы один операнд действительно является массивом (например, вычисленным диапазоном или результатом матричной функции). Это означает, что формула вида =D2:D13*1.1, привязанная к колонке, последовательно перемножает каждый ее элемент, а =D2:D13*E2:E13 перемножает две колонки одинаковой формы поэлементно

// Scalar broadcast: every cell of the anchor gets D(n) * 1.1.
Sheet.Range['F2:F13'].SetArrayFormula('=D2:D13*1.1');
// Two arrays of identical shape multiply element by element.
Sheet.Range['G2:G13'].SetArrayFormula('=D2:D13*E2:E13');
Book.Recalculate;

Что делает оператор @ по сравнению с устаревшим массивом CSE?

HotXLS считывает явный символ @ как оператор пересечения ссылок, а не как неявное пересечение по строкам, характерное для динамического Excel. В движке выражение A1:A3 @ B1:B3 возвращает одну ячейку на пересечении двух диапазонов, причем приоритет этого оператора выше, чем у ^, и ниже, чем у %. Важное замечание: неявное пересечение через пробел пока не токенизируется, так как лексер пропускает пробелы, поэтому пересечение работает только через явный символ @

Более глубокое различие существует между устаревшей формулой массива CSE (вводимой через Ctrl+Shift+Enter) и современным динамическим массивом. В HotXLS оба варианта используют один и тот же механизм фиксации диапазона. Классическая формула массива представляла собой блок {=...}, вводимый с помощью Ctrl+Shift+Enter поверх заранее выбранной области, где каждая ячейка использовала одну скомпилированную формулу. Динамический массив — это единая формула, результат которой определяет ее форму. HotXLS объединяет их в методе SetArrayFormula: вы всегда указываете прямоугольник диапазона, корень владеет скомпилированным деревом, а зависимые ячейки ссылаются на него, независимо от того, является ли формула старым матричным выражением или новым вызовом SORT. Вы не получаете автоматического изменения размеров области, как в Excel, и понимание этого принципа помогает четко разделять эти модели вычислений

Ограничения поддержки динамических массивов

Понимание ограничений убережет вас от долгой отладки. Метод SORT поддерживает направление сортировки для каждого ключа: по возрастанию по умолчанию и по убыванию, когда аргумент порядка отрицательный. Сравнение направления сортировки — вещь, которую стоит протестировать на ваших ключевых колонках по сравнению с Excel, поскольку противоположное сравнение просто вернет строки в обратном порядке вместо генерации ошибки. Метод UNIQUE сохраняет первое вхождение каждой уникальной строки, что движок подтверждает сканированием предыдущих строк, а не простым подсчетом дубликатов. Функция TABLE (таблицы данных анализа «что-если») распознается при чтении файла, но вычисляется в заглушку, поскольку реальными результатами подстановки являются кэшированные значения, уже сохраненные Excel, и HotXLS не выполняет расчет сетки «что-если» заново

Функция LET имеет наиболее строгие ограничения. HotXLS регистрирует LET и поддерживает ее вычисление, но парсер отклоняет простое имя, если оно не было определено заранее, поэтому выражение =LET(x;10;x) падает на этапе разбора до того, как вычислитель получит управление. Относитесь к LET как к неподдерживаемой функции, пока парсер не получит возможность распознавать связанные имена в локальной области видимости. Небольшое замечание по совместимости: в строках формул в этих примерах в качестве разделителя списков используется точка с запятой — используйте тот разделитель, который настроен в вашей сборке. Функции динамических массивов, от XLOOKUP и FILTER до SORT, UNIQUE, SEQUENCE и XMATCH, поставляются в составе компонента HotXLS для Delphi, справочное руководство к которому содержит полный список функций и поддерживаемых режимов аргументов