LБлог розробників losLab SoftwareТехнічні нотатки для розробників PDF, Delphi та програмного забезпечення для роботи з документами.

Технічна стаття

Формули динамічних масивів з розливом в Delphi за допомогою HotXLS

HotXLS Component

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

Ця модель є важливою, оскільки повсякденні завдання не є абстрактними. Клієнт надсилає файл .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) повертає сітку 3 на 2, 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 та сучасними динамічними масивами, але в 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 Excel Component, довідник якого містить повний перелік функцій та підтримуваних режимів аргументів.