Аудит кэшей формул Excel через deep recalc HotXLS

HotXLS отвечает на вопрос, который каждому spreadsheet-пайплайну рано или поздно приходится задать: совпадают ли ещё числа, лежащие в книге, с формулами, которые их породили. CalculateAndVerify пересчитывает весь граф зависимостей в изолированный overlay, сравнивает каждый результат с кэшированным значением, уже лежащим в ячейке, и репортит расхождения. По умолчанию он ничего не меняет

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

Почему кэшированное значение, расходящееся со своей формулой, так опасно?

Потому что оно невидимо в каждом обычном пути чтения. Откройте файл во вьювере, прочитайте ячейку через API, экспортируйте в CSV или PDF — вы получите кэшированное число. Формула лежит там же, в той же ячейке, и никто их не сравнивает. Несоответствие всплывает только когда кто-то открывает книгу в Excel, который при большинстве настроек пересчитывает при загрузке, — и внезапно отчёт, подписанный в прошлом квартале, показывает другие итоги

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

var
  Book: TXLSWorkbook;
  Options: TXLSRecalcAuditOptions;
  Report: TXLSCalculationAuditReport;
  I: Integer;
begin
  Book := TXLSWorkbook.Create(nil);
  try
    Book.LoadFromFile('quarterly-close.xls');
    Options := TXLSRecalcAuditOptions.Default;
    Options.MaxIssues := 500;
    Report := Book.CalculateAndVerify(Options);
    try
      for I := 0 to Report.Count - 1 do
        if Report[I].Kind = xlcaiCacheMismatch then
          Writeln(Report[I].SheetName, '!',
                  Report[I].Row, ':', Report[I].Col, '  ',
                  Report[I].Formula,
                  '  cached=', VarToStr(Report[I].Actual),
                  '  recomputed=', VarToStr(Report[I].Expected));
      if Report.Truncated then
        Writeln('issue budget reached, raise MaxIssues');
    finally
      Report.Free;
    end;
  finally
    Book.Free;
  end;
end;

Перегрузок три, и они отвечают на три разных вопроса. CalculateAndVerify без параметров возвращает число расхождений — этого хватает health-чеку. Перегрузка с out-массивом расхождений даёт вам ячейки. Перегрузка, принимающая TXLSRecalcAuditOptions, возвращает полный TXLSCalculationAuditReport — это та, к которой идти, когда надо знать не только что значение расходится, но и почему аудит не смог что-то вычислить

Overlay и почему аудит не пишет

Каждое пересчитанное значение ложится в overlay, а не в кэш ячейки, и overlay подсовывается в самое начало колбэка чтения ячейки в обоих движках книги. Именно это размещение делает аудит самосогласованным: когда B1 пересчитана, а C1 зависит от B1, C1 видит значение из этого прохода аудита, а не протухший кэш. Без этого единственная ошибка выше по течению репортилась бы один раз и затем поглощалась, и каждая нижележащая ячейка выглядела бы согласной с неправильным входом

Ячейки, чьё пересчитанное значение совпадает с кэшем, в overlay вообще не попадают. Это не микрооптимизация — именно это делает аудит посильным. Чистая книга со ста тысячами формул выполняет ноль записей в overlay, и проход укладывается в бюджет 1.35x от полного пересчёта, — а это разница между тем, что можно гонять на каждом intake, и тем, что запускают раз в квартал

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

Отказы классифицируются, а не сваливаются в кучу

Ячейка, которую аудит не смог вычислить, — не то же самое, что ячейка, чьё значение расходится, и TXLSCalculationAuditIssueKind держит категории раздельно. xlcaiCacheMismatch — расхождение значений. xlcaiMissingFunction и xlcaiMissingName говорят, что вычислитель встретил то, что не реализует или не может разрешить. xlcaiUnsupportedArguments покрывает формы аргументов вне поддерживаемого подмножества. xlcaiExternalReferenceDenied и xlcaiExternalReferenceMissing разделяют отказ по политике и отсутствие книги. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled и xlcaiInternalFailure завершают набор

Одно различие стоит проговорить, потому что оно переворачивает частое допущение. Положительный код ошибки Excel — это результат, а не отказ. Ячейка, легитимно вычислившаяся в #DIV/0!, вычислилась правильно, поэтому аудит кладёт эту ошибку в overlay и сравнивает её с кэшем, как любое другое значение. Книга, полная намеренных ячеек с ошибками, даёт ноль находок, а книга, где ошибка появилась или исчезла с момента кэширования значений, даёт ровно те находки, что вам нужны

Циклические ссылки получают собственное обращение. Узлы в цикле никогда не попадают в топологический порядок, поэтому каждый репортится отдельно как xlcaiCircularReference, и аудит не запускает итеративный решатель. Это намеренный read-only контракт: включена ли итерация, влияет на то, как интерпретировать код результата, а не на то, что делает аудит. Механика итеративного вычисления разобрана отдельно в итеративном вычислении и циклических ссылках

Чтение цепочки отказа

Когда формула не вычисляется, знать, какая ячейка упала, редко достаточно, потому что отказ обычно на три уровня ниже по цепочке ссылок. Поэтому каждая находка несёт строку Stack, отрисованную от внешнего кадра, в виде Sheet1!A1 > Sheet1!B2 > Data!C7, так что отчёт указывает на ячейку, которая реально сломалась, а не на ячейку, на которую вы случайно смотрели

Рекордер ограничен. MaxStackFrames по умолчанию 64 с полом в 8, и сохраняется самая глубокая упавшая цепочка: внутренний кадр записывает цепочку, когда отказ зарождается в нём, а внешние кадры, разматываясь после, её не перезаписывают. Если какая-то цепочка превысила бюджет, ставится Report.StackTruncated, что отличает короткую цепочку от цепочки, которую вы видели не целиком

// По умолчанию только чтение. ApplyResults коммитит overlay лишь после
// полностью успешного аудита, под write-охраной, отвергающей коммит,
// если структура книги менялась, пока аудит шёл
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // точное сравнение, вылавливает дрейф
Options.RelativeTolerance := 0;
Options.OnProgress := HandleProgress;

Report := Book.CalculateAndVerify(Options);
try
  if Report.Applied then
    Book.SaveToFile('quarterly-close-repaired.xls')
  else
    Writeln('not applied: ', Report.Count, ' issues blocked the commit');
finally
  Report.Free;
end;

procedure THarness.HandleProgress(ASender: TObject;
  ACurrent, ATotal: Integer; var ACancel: Boolean);
begin
  ACancel := FUserRequestedStop;   // аудит остановится на границе следующего узла
end;

Когда позволить аудиту починить книгу?

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

Заметьте намеренную асимметрию. Несовпадения кэша коммиту не мешают, потому что это ровно то, что коммит и чинит. Отказные находки мешают, потому что книга, где часть формул не смогла вычислиться, оказалась бы починена наполовину, а наполовину починенная книга хуже не починенной, про которую вы знаете, что ей нельзя верить

Толерантность — политическое решение, а не дефолт

Сравнение по умолчанию — абсолютная толерантность 1E-6 с выключенной относительной, что сохраняет классическое поведение и тихо принимает дрейф в 4E-7. Обычно это правильно: различия порядка вычисления с плавающей точкой между тем, что произвело файл, и текущим вычислителем дадут на длинных суммах различия такого размера, и репортить их находками целостности — шум

Обнуляйте обе толерантности, когда вопрос другой: пытаетесь ли вы выяснить, не поменял ли вычислитель поведение между версиями, или не переписывает ли сторонний инструмент значения тонко иным способом. На нуле тот же дрейф 4E-7 становится видимым, как и всё остальное. Выбирайте толерантность по тому, какой вопрос задаёте, и записывайте выбор рядом с отчётом, потому что отчёт без его толерантности не интерпретируем

Две соседние возможности достраивают картину. Когда вы хотите знать, почему отдельная формула выдаёт именно это значение, правильный инструмент — пошаговый просмотр из трассировщика вычисления формул. Когда вы намеренно хотите, чтобы кэшированные значения почитались без какого-либо пересчёта, например на intake-пути, который обязан воспроизвести файл ровно в том виде, в каком он пришёл, — этот режим описан в чтении кэшированных значений формул без пересчёта. Аудит — то, что сидит между ними: он говорит, безопасно ли верить кэшу. Он поставляется с Delphi spreadsheet-компонентом HotXLS для обоих движков, бинарного и OOXML