Живые межкнижные формулы в Delphi с HotXLS

HotXLS умеет вычислять формулу вроде =[Rates.xlsx]FX!B4*C10 по другой книге, открытой в памяти, а не по устаревшему кэшированному значению. Вы регистрируете вторую книгу по имени через RegisterExternalWorkbook, и каждый пересчёт читает ссылаемые ячейки прямо из этого объекта. Отмените регистрацию — и вычисление переходит на кэш внешних ссылок, который файл уже несёт в себе

Это закрывает конкретный и досадный пробел. Книги Excel регулярно ссылаются друг на друга: таблица курсов, план счетов, прайс-лист, который ведёт другая команда. Когда такой файл открывает библиотека, а не Excel, эти ссылки обычно разрешаются в те значения, что были кэшированы при последнем пересчёте в Excel, а это может быть месячной давности, и никакой повторный пересчёт этого не исправит, потому что данных там попросту нет

Как выглядит межкнижная ссылка внутри файла?

Внешняя ссылка хранится не как текст, который вы видите в строке формул. Книга хранит таблицу вспомогательных книг, каждый токен формулы указывает на индекс в этой таблице, а отдельная часть с кэшированными значениями хранит последний известный результат для каждой ссылаемой ячейки. Именно поэтому файл, открытый без своих спутников, всё равно показывает числа

У разбора текстовой формы есть свои тонкости. Лексер трактует имя книги в квадратных скобках как часть единого идентификатора, поэтому [Book.xlsx]Sheet!A1 становится одним токеном, а не несбалансированной скобкой с последующей ссылкой на лист. Затем имена нормализуются для поиска: кавычки, префиксы пути, расширение файла и числовая индексная форма — всё это схлопывается в имя базового файла в верхнем регистре, поэтому 'C:\Rates\[Rates.xlsx]FX'!B4 и [rates.xlsx]FX!B4 разрешаются в одну и ту же зарегистрированную книгу

Регистрация книги и пересчёт

Регистрация выполняется по имени, и это то имя, что фигурирует в формулах ссылающейся книги:

uses
  lxHandleX;

var
  Main, Rates: TXLSXWorkbook;
begin
  Main := TXLSXWorkbook.Create;
  Rates := TXLSXWorkbook.Create;
  try
    if (Main.Open('valuation.xlsx') <> 1) or
       (Rates.Open('rates.xlsx') <> 1) then
      Exit;

    // Формулы в valuation.xlsx, указывающие [Rates.xlsx]..., теперь читают
    // из живого объекта вместо кэшированных внешних значений
    Main.RegisterExternalWorkbook('Rates.xlsx', Rates);
    Main.Recalculate;

    Writeln(VarToStr(Main.Sheets[0].Cells[10, 3].Value));

    // Меняем источник и пересчитываем снова: новое значение проходит насквозь
    Rates.Sheets[0].Cells[4, 2].Value := 1.0842;
    Main.Recalculate;
    Writeln(VarToStr(Main.Sheets[0].Cells[10, 3].Value));
  finally
    Main.UnregisterExternalWorkbook('Rates.xlsx');
    Rates.Free;
    Main.Free;
  end;
end;

Регистрация удерживает слабую ссылку. HotXLS не берёт на себя владение зарегистрированной книгой и не освобождает её, а значит время жизни объекта — на вашей ответственности, и порядок действий важен: отменяйте регистрацию перед освобождением исходной книги или как минимум не пересчитывайте после её освобождения

Что происходит, если книга не зарегистрирована?

Вычисление переходит на кэш внешних ссылок, хранящийся в файле, — именно так ведёт себя книга, открытая без своих спутников. Именно этот откат и делает отмену регистрации безопасной, а не разрушительной

Поведение после отмены регистрации стоит описать точно, потому что оно удивляет людей. Когда ссылку невозможно разрешить ни из зарегистрированной книги, ни из кэша, вычисление даёт ошибку, и эта ошибка не записывается обратно в ячейку. Ячейка сохраняет своё последнее вычисленное значение. Зарегистрируйте источник заново и пересчитайте — значение снова обновится

Это правило «сохранять при сбое» — осознанное решение. Альтернатива — записывать значение ошибки в каждую зависимую ячейку в момент недоступности источника — превратила бы временно отсутствующую таблицу курсов в книгу, полную маркеров ошибок, которые придётся исправлять последующим успешным пересчётом. Сохранение последнего хорошего значения означает, что прерванный конвейер оставляет документ, который всё ещё читается осмысленно

Распространение «грязного» состояния останавливается на границе книги

Редактирование ячейки в зарегистрированном источнике не помечает зависимые ячейки в основной книге как «грязные». Инкрементный пересчёт отслеживает зависимости внутри одной книги; он не наблюдает за границей. Excel ведёт себя так же, поэтому его внешние ссылки обновляются при открытии или по явному запросу обновления, а не непрерывно

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

Три сценария, для которых подходит эта возможность

Консолидация — очевидный случай. Родительская книга ссылается на двенадцать месячных файлов; зарегистрируйте все двенадцать, пересчитайте один раз и читайте консолидированные цифры, ни разу не открыв Excel. Регистрационное имя для каждой книги должно совпадать с тем, что реально указано в формулах родителя, — это стоит проверять по тексту формулы, а не полагаться на совпадение с именем файла

Сценарный анализ — второй случай. Зарегистрируйте источник, запишите в него набор допущений, пересчитайте зависимую модель, зафиксируйте результаты, затем повторите. Поскольку источник — живой объект, между итерациями не записывается ни один файл, и сто сценариев обходятся в сто пересчётов, а не в сто циклов сохранения и повторной загрузки

Проверка перед поставкой — третий и наименее очевидный случай. Откройте книгу, зарегистрируйте её спутников, пересчитайте и сравните результаты со значениями, которые файл уже нёс. Расхождение означает, что кэшированные значения устарели, — это дефект, который стоит поймать до того, как файл получит клиент. Такое сравнение — естественный компаньон сравнения книг Excel, описанного в статье сравнение двух книг Excel

Гигиена ссылок перед тем, как на неё полагаться

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

var
  Issues: TArray<TXLSXFormulaIssue>;
  I: Integer;
begin
  Issues := Main.AnalyzeFormulaIssues;
  for I := 0 to High(Issues) do
    if Issues[I].Kind = xlsxFormulaIssueExternalReference then
      Writeln(Format('sheet %d R%dC%d needs [%s] (%s)',
        [Issues[I].Sheet, Issues[I].Row, Issues[I].Col,
         Issues[I].ExternalWorkbook, Issues[I].SourceFileName]));
end;

Ещё две привычки экономят время. Держите регистрационное имя каноничным — регистрируйте под простым именем файла и позвольте нормализации самой обрабатывать варианты с кавычками и префиксами пути в формулах. И относитесь к книге, ссылающейся на файлы, которые вы не можете предоставить, как к артефакту только для чтения: пересчёт сохранит кэшированные значения, но любая правка, меняющая зависимую ячейку, оставит документ в состоянии, где одни значения актуальны, а другие исторические, причём в самом файле нет ничего, что подсказало бы, какие именно. Копирование формул между книгами — отдельная операция со своими правилами перепривязки, описанная в статье межкнижное копирование и перепривязка формул, а именованные диапазоны добавляют ещё один слой, описанный в статье именованные диапазоны и межлистовые формулы

Живое межкнижное вычисление, инкрементный пересчёт и анализ формул работают на одном и том же движке для Delphi и C++Builder; полный список возможностей — на странице компонента HotXLS Delphi Spreadsheet Component