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

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

Чому це важливо: файл електронної таблиці зберігає дві речі на формульну комірку — формулу і останнє значення, яке хтось для неї обчислив. Excel тримає їх синхронізованими. Все інше у світі — не обов'язково. Файл, що пройшов крізь старішу бібліотеку, часткове перерахування, вручну відредаговану XML-частину чи інструмент, який писав значення без перерахування, охоче покаже суму, яка вже не випливає з його входів, — і ніщо в форматі файлу цього не позначає

Чому кешоване значення, що розходиться з формулою, таке небезпечне?

Бо воно невидиме в кожному звичайному шляху читання. Відкрийте файл у переглядачі, прочитайте комірку через API, експортуйте в CSV чи PDF — і ви отримаєте кешоване число. Формула отам, у тій самій комірці, і ніхто їх не порівнює. Розбіжність виявляється лише тоді, коли хтось відкриє книгу в Excel, який при більшості налаштувань перераховує при завантаженні, — і раптом звіт, підписаний минулого кварталу, показує інші суми

Аудит існує, щоб зробити те порівняння навмисною, запланованою операцією, а не нещасним випадком. Це табличний еквівалент перевірки контрольної суми: дешевий настільки, щоб запускатися в приймальному конвеєрі, і єдине, що перетворює німий проблему цілісності даних на звіт, з яким можна щось зробити

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 без параметрів повертає кількість розбіжностей — усього, що треба перевірці здоров'я. Перевантаження з out-масивом розбіжностей дає вам комірки. Перевантаження, що бере TXLSRecalcAuditOptions, повертає повний TXLSCalculationAuditReport — його беріть, коли треба знати не лише те, що значення розходиться, а й чому аудит не міг щось обчислити

Оверлей і те, чому аудит не пише

Кожне перераховане значення приземляється в оверлей, а не в кеш комірки, і оверлей інжектований на самому початку колбека читання комірки в обох двигунах книги. Саме це розташування робить аудит самозгодним: коли B1 перераховано, а C1 залежить від B1, C1 бачить значення з цього проходу аудиту, а не застаріле кешоване. Без цього одну помилку вгорі було б повідомлено раз, а потім поглинуто, і кожна комірка нижче виглядала б як згодна з неправильним входом

Комірки, чиє перераховане значення збігається з кешем, взагалі не потрапляють в оверлей. Це не мікрооптимізація — саме це тримає аудит доступним. Чиста книга зі ста тисяч формул виконує нуль записів оверлея, і прохід лишається в бюджеті 1.35x проти повного перерахування, — а це різниця між тим, що можна ганяти на кожному прийманні, і тим, що ганяють раз на квартал

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

Відмови класифіковані, а не зваляні в купу

Комірка, яку аудит не може обчислити, — не те саме відкриття, що комірка, чиє значення розходиться, і TXLSCalculationAuditIssueKind тримає категорії окремо. xlcaiCacheMismatch — це розбіжність значення. xlcaiMissingFunction і xlcaiMissingName кажуть, що оцінювач зустрів те, чого не реалізує або не може розв'язати. xlcaiUnsupportedArguments покриває форми аргументів поза підтримуваною підмножиною. xlcaiExternalReferenceDenied і xlcaiExternalReferenceMissing розділяють політичну відмову від відсутньої книги. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled і xlcaiInternalFailure завершують набір

Одну відмінність варто сформулювати, бо вона перевертає поширване припущення. Додатний код помилки Excel — це результат, а не відмова. Комірка, яка легітимно обчислюється в #DIV/0!, обчислена правильно, тож аудит зберігає ту помилку в оверлеї і порівнює її з кешем, як будь-яке інше значення. Книга, повна навмисних комірок-помилок, продукує нуль відкриттів, а книга, де помилка з'явилася чи зникла з моменту кешування значень, продукує рівно ті відкриття, які ви хочете

Циклічні посилання отримують власне поводження. Вузли в циклі ніколи не потрапляють у топологічний порядок, тож кожен повідомляється окремо як xlcaiCircularReference, і аудит не ганяє ітеративний розв'язувач. Це навмисний контракт лише-читання: чи увімкнена ітерація, впливає на те, як слід інтерпретувати код результату, а не на те, що робить аудит. Механіку ітеративного обчислення розглянуто окремо в ітеративному обчисленні та циклічних посиланнях

Читання ланцюга відмови

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

Рекордер обмежений. MaxStackFrames має дефолт 64 з мінімумом 8, і найглибший ланцюг, що впав, — той, який утримується: внутрішній кадр записує ланцюг, коли відмова зароджується там, а зовнішні кадри, що розкручуються після, його не перезаписують. Якщо якийсь ланцюг перевищив бюджет, виставляється Report.StackTruncated, що каже вам різницю між коротким ланцюгом і ланцюгом, який ви бачили не повністю

// За замовчуванням лише читання. ApplyResults підтверджує оверлей
// лише після цілком успішного аудиту, під write guard, який відхиляє
// підтвердження, якщо структура книги змінилася під час аудиту
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. Підтвердження відбувається після цілком успішного проходу, не скасованого, і проходить структурний guard: бінарний двигун стежить за ідентифікатором зміни книги, OOXML-двигун знімає знімок покоління структури на аркуш. Якщо щось зсунулося, поки аудит біг, результати описують книгу, яка більше не існує, і підтвердження відхиляється

Зверніть увагу на навмисну асиметрію. Розбіжності кешу не блокують застосування, бо вони рівно те, що підтвердження покликане лагодити. Відмовні відкриття блокують, бо книга, де деякі формули не вдалося обчислити, була б наполовину полагоджена, а наполовину полагоджена книга гірша за нелагоджену, про яку ви принаймні знаєте, що їй не можна довіряти

Допуск — це політичне рішення, а не дефолт

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

Виставте обидва допуски в нуль, коли питання інше — коли ви намагаєтеся дізнатися, чи змінив оцінювач поведінку між версіями, чи сторонній інструмент переписує значення ледь помітно інакше. За нуля той самий дрейф 4E-7 стає видимим, як і все інше. Обирайте допуск за тим, яке питання ви ставите, і записуйте вибір поруч зі звітом, бо звіт без свого допуску інтерпретувати не можна

Дві сусідні можливості завершують картину. Коли хочете знати, чому окрема формула продукує те значення, що продукує, правильний інструмент — покроковий погляд у трасувальнику обчислення формул. Коли ви навмисно хочете, щоб кешовані значення шанувалися без жодного перерахування — скажімо, на приймальному шляху, який мусить відтворити файл рівно таким, як він прибув, — той режим описаний у читанні кешованих значень формул без перерахування. Аудит — те, що сидить між цими двома: він каже, чи безпечно довіряти кешу. Він постачається з HotXLS Delphi spreadsheet component для обох двигунів — бінарного й OOXML