Движок формул и пользовательские функции 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