Deep recalc одит в HotXLS с изолиран overlay

HotXLS отговаря на въпроса, който всеки spreadsheet pipeline рано или късно трябва да зададе: съвпадат ли числата, съхранени в workbook-а, с формулите, произвели ги. CalculateAndVerify преизчислява цялата графа на зависимостите в изолиран overlay, сравнява всеки резултат с кешираната стойност, вече седяща в клетката, и докладва разминаванията. По подразбиране не мени нищо

Причината това да има значение е, че spreadsheet файл съхранява по две неща на формулна клетка: формулата и последната стойност, някой е сметнал за нея. Excel ги държи синхронизирани. Всичко останало на света – може и да не. Файл, минал през по-стара библиотека, частично преизчисление, ръчно редактиран XML part или инструмент, записал стойности без да ги преизчисли, ще ви поднесе с охота сбор, който вече не следва от входовете си, и нищо във файловия формат не го маркира

Защо кеширана стойност, разминаваща се с формулата си, е толкова опасна?

Защото е невидима във всеки обикновен път на четене. Отворете файла в viewer, прочетете клетката през API, експортирайте в CSV или PDF – получавате кешираното число. Формулата е точно там, в същата клетка, и никой не ги сравнява. Разминаването излиза наяве едва когато някой отвори workbook-а в Excel, който преизчислява при зареждане при повечето настройки, и изведнъж доклад, подписан миналото тримесечие, показва различни сборове

Одитът съществува, за да превърне това сравнение в съзнателна, планирана операция, а не в случайност. Това е spreadsheet еквивалентът на проверка на checksum: достатъчно евтин, за да върви в intake pipeline, и единственото нещо, превръщащо тих проблем с целостта на данните в доклад, върху който можете да действате

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;

Има три overload-а и отговарят на три различни въпроса. Безпараметричният CalculateAndVerify връща брой разминавания – всичко, от което health check се нуждае. Overload-ът с out масив от разминавания ви дава клетките. Overload-ът, приемащ TXLSRecalcAuditOptions, връща пълен TXLSCalculationAuditReport – този, към който посегате, когато трябва да знаете не просто че стойност се разминава, а защо одитът не успя да оцени нещо

Overlay-ът и защо одитът не пише

Всяка преизчислена стойност каца в overlay, а не в клетъчния кеш, а overlay-ът е впрыскнат в самото начало на cell-read callback-а и в двата workbook двигателя. Това разположение прави одита самосъгласуван: когато B1 е преизчислен и C1 зависи от B1, C1 вижда стойността от този одитен пас, не остарялата кеширана. Без това една-единствена грешка нагоре по веригата би била докладвана веднъж и после погълната, а всяка клетка надолу би изглеждала съгласна с грешен вход

Клетки, чиято преизчислена стойност съвпада с кеша, изобщо не влизат в overlay-а. Това не е микро-оптимизация, а това, което пази одита достъпен. Чиста workbook със сто хиляди формули извършва нула overlay записвания и пасът остава в бюджет от 1.35x спрямо пълно преизчисление, което е разликата между нещо, което можете да въртите на всяко вкарване, и нещо, което въртите веднъж на тримесечие

Оценяването следва сериен топологичен ред, извлечен от графата на зависимостите, като всеки възел първо е маркиран dirty, така че всяка клетка се смята точно веднъж след входовете си. Ако искате инкременталната машина, пазеща жива workbook актуална, вместо да одитирате съхранена – това е друг механизъм, описан в инкрементално преизчисление и графата на зависимостите

Провалите са класифицирани, не сляти в куп

Клетка, която одитът не може да оцени, не е същото откритие като клетка, чиято стойност се разминава, а TXLSCalculationAuditIssueKind държи категориите разделени. xlcaiCacheMismatch е разминаването на стойност. xlcaiMissingFunction и xlcaiMissingName казват, че оценителят е срещнал нещо, което не имплементира или не може да разреши. xlcaiUnsupportedArguments покрива форми на аргументи извън поддържаното подмножество. xlcaiExternalReferenceDenied и xlcaiExternalReferenceMissing разделят отказ по политика от отсъстваща workbook. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled и xlcaiInternalFailure допълват набора

Едно разграничение заслужава да бъде изречено, защото обръща често допускане. Положителен Excel код за грешка е резултат, не провал. Клетка, легитимно смятаща се до #DIV/0!, е сметнала коректно, така че одитът съхранява тази грешка в overlay-а и я сравнява с кеша като всяка друга стойност. Workbook, пълна с нарочни error клетки, ражда нула открития, а workbook, в която грешка се е появила или изчезнала откакто стойностите са кеширани, ражда точно откритията, които искате

Циркулярните референции получават собствено лечение. Възли в цикъл никога не влизат в топологичния ред, така че всеки се докладва поотделно като xlcaiCircularReference, а одитът не пуска итеративния решател. Това е нарочен read-only договор: дали итерацията е включена влияе на това как кодът на резултата трябва да бъде тълкуван, не на това какво прави одитът. Механиката на итеративното оценяване е обхваната отделно в итеративно изчисление и циркулярни референции

Четене на верига от провали

Когато формула се провали да се оцени, знанието коя клетка се е провалила рядко стига, защото провалът обикновено е три нива надолу по верига от референции. Всяко откритие затова носи Stack низ, рендиран с най-външната рамка пръв, във формата Sheet1!A1 > Sheet1!B2 > Data!C7, така че докладът сочи клетката, реално счупила се, а не клетката, случайно погледната

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

// Read-only по подразбиране. ApplyResults приема overlay-а само след
// напълно успешен одит, под write guard, отхвърлящ приема, ако структурата
// на workbook-а е сменяла, докато одитът е вървял
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // точно сравнение, изважда drift наяве
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;

Кога да оставите одита да поправи workbook-а?

Само когато одитът се е върнал напълно чист от провал-клас открития, което е точно условието, което ApplyResults налага вместо вас. Приемът става след напълно успешен пас, не е бил отменен и минава структурен guard: binary двигателят следи идентификатор за промяна на workbook, OOXML двигателят прави snapshot на generation на структурата по worksheet. Ако нещо се е помръднало, докато одитът е вървял, резултатите описват workbook, която вече не съществува, и приемът се отказва

Забележете нарочната асиметрия. Разминаванията на кеш не блокират приложението, защото са точно това, заради което приемът съществува – да поправи. Провал-клас открития го блокират, защото workbook, в която някои формули не са могли да бъдат оценени, би била наполовина поправена, а наполовина поправена workbook е по-лоша от непоправена, за която знаете, че не бива да вярвате

Толерансът е решение по политика, не по подразбиране

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

Задайте и двата толеранса на нула, когато въпросът е друг – опитвате се да разберете дали оценител е сменил поведението между версии, или дали инструмент на трета страна пренаписва стойности по финно различен начин. На нула същият drift от 4E-7 става видим, както и всичко останало. Изберете толеранса според въпроса, който задавате, и запишете избора до доклада, защото доклад без своя толеранс не е интерпретируем

Две съседни възможности дописват картината. Когато искате да знаете защо една формула произвежда стойността, която произвежда, стъпка-по-стъпка изгледът в формулния evaluation tracer е правилният инструмент. Когато нарочно искате кешираните стойности да бъдат почитани без никакво преизчисление – примерно на intake път, който трябва да възпроизведе файла точно както е пристигнал – този режим е описан в четене на кеширани формулни стойности без преизчисление. Одитът е това, което седи между двете: той ви казва дали доверието в кеша е безопасно. Идва с HotXLS Delphi spreadsheet компонента и за двата двигателя, binary и OOXML