XLS в Delphi: сохранение без молчаливого пересчёта формул

HotXLS, нативная библиотека для Excel на Delphi и C++Builder, сохраняет книгу классического формата BIFF8 .xls по принципу «сначала кеш»: TXLSWorksheet.WriteFormula спрашивает у TXLSWorkbook.TryGetCachedFormulaValue значение, которое Excel сохранил рядом с каждой формулой, и зовёт вычислитель только когда этот кеш отсутствует или помечен недействительным. Книга, которую вы открыли и не трогали, сохраняет обратно те же числа, а свежие результаты требуют одного явного вызова Recalculate вместо того, чтобы быть скрытым побочным эффектом SaveAs

Баг, который вытащил этот контракт наружу, был до неприличия мал. Файл из корпуса nested-subtotals.xls содержит общий итог в R2C4, чьё кешированное значение — 37. Откройте его в HotXLS, спросите TryGetCachedFormulaValue для этой ячейки, получите 37. Сохраните, не меняя ни единой ячейки, откройте сохранённую копию, задайте тот же вопрос, получите 67. Ничего в API не просили ничего вычислять, а число в файле уехало ровно на 30 — и 30 как раз равно сумме двух групповых промежуточных итогов, 10 и 20, лежащих внутри диапазона, который покрывает общий итог

Почему сохранение файла XLS меняет значение формулы?

Чтобы 37 стало 67, должны были совпасть два независимых дефекта, и починка любого одного спрятала бы другой. Первый был структурным: классический писатель пересчитывал каждую формулу при каждом сохранении. Второй — проверка типа, которая никогда не могла быть истинной для формулы, загруженной с диска, из-за чего вычислитель считал вложенные ячейки SUBTOTAL дважды. Файл из корпуса был просто первым входом, где пересчёт при сохранении дал ответ, отличный от Excel, и кто-то сравнил эти два ответа. Структурный дефект формулируется легко: до v2.382.3 TXLSWorksheet.WriteFormula и его собрат по общим формулам WriteFormulaWithTExp получали восьмибайтовое поле FormulaValue каждой записи Formula вызовом TXLSWorkbook.GetFormulaValue, то есть у вычислителя. Кеш, который ParseFormula аккуратно декодировал из исходного файла при загрузке, на выходе никто не спрашивал. По сути каждое сохранение было полным пересчётом в обход API пересчёта уровня книги, так что остановить его нельзя было ничем, что можно выставить на книге. Любое место, где вычислитель HotXLS расходился с Excel — законно неподдерживаемая функция или просто баг, — становилось тихой правкой данных при сохранении

Второй дефект жил в обратном вызове для вложенных промежуточных итогов, которым пользуется вычислитель. Excel определяет все формы SUBTOTAL так, что они игнорируют ячейки, чья собственная формула — тоже SUBTOTAL, поэтому калькулятор в lxCalc.pas взводит FIgnoreSubtotalCells на время агрегации и спрашивает у книги через TXLSWorkbook.GetClassicIsSubtotalCell, является ли такой каждая ячейка диапазона. Этот обратный вызов доставал текст формулы как Variant и проверял его через VarType(f) = varOleStr. Текст возвращается из GetUnCompiledFormula как String в Delphi, а String, присвоенная Variant, — это varUString, никогда не varOleStr. Предикат был ложным для каждой ячейки в каждом загруженном файле, групповые промежуточные итоги вкатывались в общий итог второй раз, и при сохранении, которое всё пересчитывало, 10 + 20 + 7 становилось 67

// HotXLS 2.381 и раньше: Variant с формулой, построенный из String,
// имеет тип varUString, так что это сравнение никогда не проходило
Result := (VarType(f) = varOleStr) and
  (SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
   SameText(Copy(f, 1, 10), '=SUBTOTAL('));

// HotXLS 2.382.0: VarIsStr принимает varString, varOleStr и varUString,
// а AGGREGATE исключается из объемлющих промежуточных итогов, как в Excel
if VarIsStr(f) then
  Result := SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
    SameText(Copy(f, 1, 10), '=SUBTOTAL(') or
    SameText(Copy(f, 1, 10), 'AGGREGATE(') or
    SameText(Copy(f, 1, 11), '=AGGREGATE(');

В v2.382.0 вышел фикс с VarIsStr, и заодно, оставаясь в той же функции, обратный вызов научили тому, что ячейки AGGREGATE тоже исключаются из объемлющих промежуточных итогов. Уже этого хватило, чтобы проверка по корпусу прошла: пересчитанные 37 теперь совпадали с загруженными 37. Честнее библиотека от этого не стала: сохранение всё ещё пересчитывало, а тест был зелёным лишь потому, что вычислитель случайно сошёлся с Excel на этом конкретном файле. О том, какие ячейки пропускают SUBTOTAL и AGGREGATE, включая скрытые строки, рассказывает статья про SUBTOTAL, AGGREGATE и скрытые строки; здесь важно то, что никакой вычислитель не должен получать голос по файлу, который вы не просили вычислять

Что Excel гарантирует насчёт кешированных значений при сохранении?

Excel относится к сохранению как к снимку, а не как к событию вычисления. Значение, записанное в поле FormulaValue записи Formula ([MS-XLS] §2.4.127, раскладка в §2.5.133), — это то, что ячейка показывает в данный момент, а в режиме ручного вычисления оно может быть устаревшим на годы, и Excel всё равно пишет его добросовестно. Пересчёт — отдельная операция со своим триггером. HotXLS теперь следует тому же правилу для классических сохранений: WriteFormula и WriteFormulaWithTExp сначала зовут TryGetCachedFormulaValue, берут CacheInfo.Value, когда состояние — xlfcsLoaded или xlfcsCalculated, и проваливаются к GetFormulaValue только для xlfcsMissing и xlfcsInvalidated. Сторона чтения этого контракта — включая то, что означает каждое состояние и почему кешированное пустое значение или False всё равно считается значением, — описана в статье Read Excel Cached Formula Values in Delphi Without Recalc

Путь отката оставлен намеренно, а не убран. Формула, присвоенная в этом сеансе через Cells[Row, Col].Formula, приходит без кеша, а формула, заменённая на загруженной ячейке, помечается как xlfcsInvalidated методом _SetCompiledFormula; обе вычисляются при сохранении ровно как раньше, так что сгенерированная книга всё равно открывается в Excel с числами. Когда и вычислитель не может выдать значение, писатель выдаёт нулевую нагрузку и выставляет fAlwaysCalc (бит 0 grbit из §2.4.127), чтобы Excel пересчитал ячейку при открытии, а не доверял заглушке

procedure RoundTripWithoutRecalc(const Source, Target: string);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open(Source);
    // Лист, строка и столбец с отсчётом от 1: R2C4 на первом листе
    if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
      raise Exception.Create('R2C4 carries no usable cache');
    Book.SaveAs(Target);        // для кешированных ячеек вычислитель не участвует
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(Target);
    Book.TryGetCachedFormulaValue(1, 2, 4, After);
    // Before.Value = After.Value = 37 для nested-subtotals.xls
    // Сохранение с пересчётом записало бы здесь 67
  finally
    Book.Free;
  end;
end;

Где корневая ячейка общей формулы BIFF хранит кешированное значение?

В своей собственной записи Formula, как и любая другая ячейка с формулой, — и именно из-за этого корневая ячейка общей группы оказалась единственным местом, где сохранение по принципу «сначала кеш» всё ещё теряло. Общая формула в BIFF8 хранится как запись ShrFmla ([MS-XLS] §2.4.260), идущая после записи Formula верхней левой ячейки, и каждая ячейка-участник, включая корневую, несёт rgce из единственного токена PtgExp (§2.5.198): первый байт разобранного выражения — $01, за ним строка и столбец корневой ячейки. Ячейки-последователи самодостаточны: HotXLS читает FormulaValue каждой из них и разрешает выражение, находя скомпилированную формулу корня. Корневая ячейка другая, потому что при разборе её записи Formula выражения ещё не существует; оно приходит одной записью позже

В этот разрыв в одну запись кеш и делся. TXLSReader.ParseFormula декодирует кешированное значение и, увидев PtgExp, чьи координаты совпадают с координатами самой ячейки, запоминает ячейку в FSharedFormulaRow и FSharedFormulaCol и публикует кеш в ячейку. Когда приходит запись ShrFmla ($04BC), ParseSharedFormula компилирует выражение и устанавливает его через _SetCompiledFormula, а _SetCompiledFormula делает то, что обязан делать при любом изменении формулы: очищает FCachedFormulaValue и сбрасывает состояние в xlfcsMissing. Загруженные 37 у корня тем самым выбрасывались прежде, чем кто-либо успевал их прочитать, TryGetCachedFormulaValue сообщал о корне как о ячейке без кеша, и писатель с «сначала кеш» послушно откатывался к вычислителю именно для той ячейки, на которую все и смотрели. Запись Array (§2.4.4) имеет тот же порядок и ту же дыру

Фикс в v2.382.3 добавляет третье поле, FSharedFormulaCachedValue, рядом с ожидающими координатами корня. ParseFormula складывает туда декодированный кеш, когда распознаёт корень, а ParseSharedFormula и ParseArrayFormula воспроизводят его через _SetCellCachedFormulaValue сразу после установки скомпилированного выражения, затем сбрасывают заначку в Unassigned. Строковый вариант кеша всего этого не касается: его нагрузка приходит в отдельной записи String и маршрутизируется по координатам ячейки, а не по порядку записей. Если вы работаете со стороной OOXML той же концепции, статья про раскрытие si общих формул в XLSX объясняет, почему у пакетного формата нет такой проблемы с порядком, но есть свои подводные камни при раскрытии

Зачем последователям общих формул относительный сдвиг?

Потому что выражение, хранимое в ShrFmla, записано относительно корневой ячейки, и последователь, переиспользующий его дословно, вычисляет ссылки корня, а не свои. Старый читатель устанавливал каждому последователю Value.GetCopy() — глубокую копию без смещения, — так что группа с корнем в B1 и формулой =A1*3 давала каждому последователю тоже =A1*3. Сохранение по принципу «сначала кеш» на деле маскировало это для загруженных файлов, поскольку у последователей было своё FormulaValue и выражение для корректного сохранения им не требовалось; всё всплыло в тот момент, когда что-либо пересчитывалось. Теперь читатель устанавливает TXLSCompiledFormula.GetCopy(row - srow, col - scol), который обходит синтаксическое дерево и смещает каждую относительную ссылку на расстояние от последователя до корня, так что последователь в B2 владеет настоящей =A2*3

Регрессионный тест, закрепляющий оба поведения, стоит прочитать, потому что он не даёт совпадению пройти незамеченным. Он строит книгу с =A1*3 и =A2*3 над входами 2 и 4, затем подкладывает заведомо неверные кеши 999 и 888 через _SetCellCachedFormulaValue — один раз с включённым UseSharedFormulas, другой с выключенным. После сохранения и перезагрузки обе ячейки должны по-прежнему сообщать 999 и 888: доказательство, что сохранение не тронуло ни кеш корня, ни кеш последователя. Только после явного Recalculate они должны стать 6 и 12 — доказательство, что смещённое выражение последователя верно. Тест, подложивший бы истинные значения, прошёл бы и на старом писателе, и в этом весь смысл подкладывать неверные

var
  Book: TXLSWorkbook;
  Info: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open('quarterly-model.xls');
    Book.Sheets[1].Cells[1, 1].Value := 5;   // меняем вход

    // Загруженные кеши зависимых формул НЕ помечаются недействительными
    // при правке литерала, так что обычный SaveAs оставил бы старые числа.
    // Просите пересчёт, когда вам действительно нужны свежие результаты:
    Book.Recalculate;

    if Book.TryGetCachedFormulaValue(1, 1, 2, Info) then
      Writeln('B1 now ', VarToStr(Info.Value),
        ', state ordinal ', Ord(Info.State));   // xlfcsCalculated
    Book.SaveAs('quarterly-model-updated.xls');
  finally
    Book.Free;
  end;
end;

Чего контракт «сначала кеш» за вас не делает

Сохранение по принципу «сначала кеш» сохраняет то, что было загружено; оно не следит за тем, верно ли загруженное до сих пор. Правка литерала, от которого зависит формула, помечает граф зависимостей для вычислителя как грязный, но оставляет кеш xlfcsLoaded у зависимой ячейки на месте, и классический писатель с удовольствием запишет это устаревшее значение, если только вы не вызовете Recalculate или не прочтёте Value ячейки, что её вычислит и переведёт состояние в xlfcsCalculated. Это тот же компромисс, что Excel делает в режиме ручных вычислений, и для конвейера, который открывает чужие файлы, правит пару подписей и сохраняет, он правильный — но он означает, что книга, правящая входные данные, обязана сама явно позаботиться о пересчёте. Политику RecalcBeforeSave у писателя XLSX эта работа не меняет, и у неё есть свой ручной режим, сохраняющий кеши в том же духе. Отсюда следуют две границы поменьше: путь «сначала кеш» помогает только ячейкам в состоянии xlfcsLoaded или xlfcsCalculated; генератор, который пишет формулы и никогда их не вычисляет, всё равно платит одним вычислением на ячейку при сохранении, ровно как и раньше. И фикс вложенных промежуточных итогов исправляет то, какие ячейки пропускает вычислитель, а не каждую реализуемую им функцию: файл, чьи формулы HotXLS не может вычислить в точности как Excel, теперь безопасно гонять туда-обратно нетронутым, но сознательный Recalculate на таком файле всё равно даст ответ библиотеки, а не Excel, и их стоит сравнить, прежде чем доверять пересчитанному сохранению

Классические сохранения по принципу «сначала кеш», восстановленные кеши корней общих и массивных формул, сдвиг относительных ссылок для последователей общих формул и исправленные правила вложенности SUBTOTAL и AGGREGATE — всё это входит в стандартный HotXLS Delphi Spreadsheet Component для Delphi и C++Builder, без зависимости от Excel или какого-либо сервера автоматизации OLE; на странице продукта есть полный справочник по API для книги, читателя кеша и точек входа пересчёта, использованных здесь