Рушій формул HotXLS та власні функції користувача у Delphi

Бібліотека електронних таблиць, яка лише зберігає рядки формул, і бібліотека з робочим рушієм формул — це два різні продукти, що виглядають однаково рівно до тієї миті, коли ви попросите в одного з них число. Більшість коду для електронних таблиць на Delphi цієї щілини ніколи не помічає, бо Excel її заклеює: запишіть SUM(B2:B501) у клітинку, збережіть — і Excel перерахує підсумок тієї ж миті, коли людина відкриє файл. Приберіть людину з ланцюжка, проженіть ту саму книгу через серверний конвеєр, що експортує просто в CSV, — і різниця перестане бути академічною. CSV несе буквальний текст =SUM(B2:B501) там, де мало бути число, бо ніщо й ніколи насправді не обчислювало цю формулу

Саме на правильному боці цієї межі й стоїть HotXLS. Він поводиться з формулою так, як це роблять самі формати файлів, — як зі збереженим текстом плюс необов’язковим кешованим результатом, — тож голий експорт у CSV відтворює рецепт, а не страву. Але він також несе рушій обчислень, який можна викликати напряму, той самий рушій в обох фасадах, XLS і XLSX, плюс зачіпку для розв’язання імен функцій, про які рушій ніколи не чув. HotXLS — це нативна бібліотека на Object Pascal, що читає й пише XLS та XLSX із Delphi й C++Builder без автоматизації Excel, і саме обчислювальна її половина обертає збережені формули назад на значення на вимогу

Формули зберігаються, а не обчислюються негайно

Запис формули в клітинку нічого не обчислює. Під час збереження книга фіксує текст формули. З боку XLS вона також фіксує прапорці, якими керує RecalcOnSave: усталено він має значення True й наказує Excel перерахувати все під час відкриття. Ця модель правильна для файлів, призначених для Excel, і хибна для конвеєрів, що споживають значення клітинок напряму, чи то експорт у CSV, чи в HTML, чи ваш власний код, який читає клітинки назад. Для них обчислюйте явно через Calculate. Він існує в чотирьох точках входу: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook і TXLSXWorksheet — усі відкривають function Calculate(const Formula: WideString): Variant

// обчислити в тому самому процесі, а тоді відправити значення, а не рецепт
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // тепер CSV несе число

Вираз, переданий у Calculate, — це звичайний текст формули Excel. Посилання між аркушами, визначені імена та вкладені функції розв’язуються проти поточної книги в пам’яті, що робить цей виклик корисним далеко за межами латання експортів у CSV. Ставтеся до нього як до механізму тверджень. Генератор, який щойно записав п’ятсот рядків деталей, може запитати в книги її власний загальний підсумок і порівняти його з числом, обчисленим незалежно на Pascal, зловивши похибку на одиницю в діапазоні раніше, ніж це зробить аудитор замовника

Це також задає правильну стратегію тестування для виводу, насиченого формулами. Excel лишається еталонною реалізацією мови формул, тож для тієї жменьки формул, що несуть ділові наслідки, тримайте затверджений файл-фікстуру, чиї очікувані значення виробив сам Excel, і хай конвеєр складання обчислює формули згенерованої книги через Calculate проти цих фікстур. Тоді розбіжності виринають як провалені тести в Delphi, а не як невідповідності, виявлені замовником під час порівняння двох звітів

Додавання ділових функцій через OnUserFunction

Коли рушій зустрічає назву функції, якої не впізнає, він викликає подію, а не падає навідруб. Призначте OnUserFunction на будь-якому з класів книги — і зможете розв’язати виклик самі:

procedure TReportBuilder.HandleUserFunction(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'DISCOUNT') then
  begin
    Value := Args[0] * 0.9;   // Args приходить як масив Variant
    Handled := True;
  end;
end;

// підключення й використання
Book.OnUserFunction := HandleUserFunction;
Sheet.Cells[1, 1].Value := 200;
Sheet.Cells[1, 2].Formula := 'DISCOUNT(A1)';
Net := Book.Calculate('DISCOUNT(A1) + SUM(A1:A1)');

На увагу заслуговують три деталі. По-перше, задавайте Handled := True лише тоді, коли ви справді впізнали назву. Лишивши його False, ви дасте рушієві продовжити звичайну обробку невідомих функцій, тож один обробник може обслуговувати кілька книг, не привласнюючи все, що крізь нього проходить. По-друге, порівнюйте назви без урахування регістру через SameText, бо автори формул набирають discount( і DISCOUNT( як заманеться. По-третє, аргументи приходять уже обчисленими: DISCOUNT(A1) подає вам значення A1, а не посилання, тож функція не може сказати, звідки взялися її вхідні дані. Останній пункт готує ґрунт для обмеження, про яке йдеться в наступному розділі

Ставтеся до тіла обробника з тією самою обачністю, що й до будь-якої зовнішньої точки входу. Масив Args віддзеркалює те, що набрав автор формули, тож перевіряйте кількість і типи аргументів, перш ніж у нього індексуватися, і вирішіть наперед, що повертає хибний виклик: значення-помилку Variant чи піднятий виняток. Цей вибір важить, бо виняток, кинутий усередині обробника, поширюється назовні крізь той виклик Calculate, що запустив обчислення. Це прийнятно в щільно контрольованому генераторі й нечемно у службі, що обчислює написані користувачами книги, де одна погана формула поклала б увесь запит. У такому середовищі перехоплюйте виняток усередині обробника й повертайте сигнальне значення, яке навколишній робочий процес зможе розпізнати й записати в журнал

Функції, обізнані про позицію, потребують варіанта Ex

Деякі функції законно залежать від того, де саме їх обчислюють. Ставка, що різниться від аркуша до аркуша, пошук, відносний до рядка, множник для регіону, що діє лише на регіональних аркушах, — на жодне з цього не відповісти самими лише значеннями аргументів. Проста подія цього виразити не може, тож рушій пропонує OnUserFunctionEx, однакову в усьому, крім одного зайвого параметра:

procedure TReportBuilder.HandleUserFunctionEx(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  const Context: TXLSUserFunctionContext;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'REGIONRATE') then
  begin
    // та сама формула дає іншу ставку на кожному регіональному аркуші
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

TXLSUserFunctionContext несе SheetIndex, Row і Col клітинки, яку обчислюють. Якщо результат функції бодай трохи залежить від її розташування, підключайте подію Ex від самого початку. Вбудовувати контекст постфактум в обробник, який уже викликають тридцять формул, куди брудніше, ніж обрати правильну сигнатуру першого дня, а в решті ці дві події настільки схожі, що починати з вужчої майже немає підстав

Власні функції не подорожують до Excel

Власна функція живе цілком усередині вашого процесу. Назва DISCOUNT щось означає лише поки виконується ваш код на Delphi та його обробник події. Відкрийте збережений файл в Excel — і DISCOUNT буде просто невпізнаною назвою; клітинка покаже #NAME?, якщо тільки на машині користувача випадково не знайдеться відповідної функції VBA чи надбудови. Це той факт проєктування, що відділяє демонстрацію від продукту, придатного до постачання, і він змушує зробити вибір свідомо, а не виявити його згодом

Вирішуйте для кожної клітинки, який із двох контрактів ви постачаєте. Клітинки, які користувач має бачити перерахованими всередині Excel, треба будувати лише з власного словника функцій Excel і ні з чого іншого. Клітинки, чия логіка є пропрієтарною, слід обчислювати в тому самому процесі через Calculate й зберігати як звичайні значення, щоб власна функція поводилася як внутрішнє правило обчислення, а не як вміст файлу. Режим збою, що надійно породжує звернення до підтримки, — це середина: зберегти формулу з власною функцією й чекати, що Excel її вшанує

У контракту «лише значення» є тиха перевага: він захищає інтелектуальну власність. Правило ціноутворення, обчислене у вашому процесі Delphi й відправлене як число, неможливо розкрутити назад із книги так, як це можна зробити з видимою формулою, а користувач не зламає його, відредагувавши проміжну клітинку. Генератори рахунків, звіти про комісії й тарифні картки майже завжди належать до цього табору. Випадок, якому справді потрібні живі формули, — це інтерактивна модель «що, якщо», де від замовника очікують зміни вхідних даних і спостереження за рухом підсумків, і такі моделі треба будувати з власного словника Excel плюс визначених імен

Режими обчислення, ітерація та R1C1: регулятори фасаду XLS

Фасад XLS відкриває налаштування обчислень рівня BIFF, які Excel читає з файлу. CalculationMode приймає xlCalcManual, xlCalcAutomatic (усталене) або xlCalcAutomaticExceptTables і визначає, як поводиться Excel, щойно файл відкрито. Книгу-модель із тисячами формул часто привітніше постачати в ручному режимі, щоб одержувач сам вирішував, коли зчиниться буря перерахунку. EnableIteration (усталено False) разом із MaxIterations (усталено 100) і MaxIterationChange (усталено 0.001) відмикає навмисні циклічні посилання того ґатунку з ітеративним збіганням, що трапляються в деяких фінансових моделях. ReferenceStyle перемикає відображення між A1 і R1C1, а UseFullPrecision віддзеркалює параметр Excel «точність як на екрані»

Ці властивості живуть на фасаді XLS, бо відображаються на записи BIFF; коли генеруєте .xlsx, плануйте формули так, щоб вони не залежали від ітеративних налаштувань, або обчисліть збіжні значення в Delphi й запишіть результати

Формули-масиви: публічна точка входу — це XLSX

Спадкові формули-масиви в стилі CSE створюються через TXLSXRange.SetArrayFormula:

// одна формула-масив, що охоплює A2:A4
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

Еквівалентний метод існує в ієрархії класів XLS, але сидить у приватній секції, тож підтримуваного способу створити нові формули-масиви у файлах .xls немає. Наявні у відкритих файлах переживають цикл читання-запису неушкодженими; чого не можна — це створювати їх. Правило, що з цього випливає, доволі просте: коли семантика масивів є частиною вимоги, цільтеся в .xlsx. Якщо спадковий результат у .xls справді потребує поведінки масиву, прагматичний шлях — обчислити результат масиву в Delphi й записати окремі значення в клітинки

Дві дотичні статті на цьому сайті: визначені імена та формули між аркушами розглядає розв’язання імен, яке виконує рушій, а стаття про експорт у CSV і TSV докладно описує ту поведінку експорту, через яку явне обчислення й стає потрібним. Повний довідник рушія разом із набором підтримуваних функцій постачається з HotXLS Delphi Component