Audit cache formulí v Excelu přes HotXLS Deep Recalc

HotXLS odpovídá na otázku, kterou musí dřív nebo později položit každý spreadsheet pipeline, a to jestli čísla uložená v sešitu ještě sedí na formule, které je vyprodukovaly. CalculateAndVerify přepočítá celý dependency graph do izolovaného overlay, porovná každý výsledek s cached hodnotou, která už v buňce je, a nahlásí neshody. Defaultně nezmění nic

Proč na tom záleží: soubor spreadsheetu ukládá u každé formulové buňky dvě věci, formuli a poslední hodnotu, kterou pro ni někdo spočítal. Excel je drží v synchronizaci. Všechno ostatní na světě nemusí. Soubor, který prošel starší knihovnou, částečným přepočtem, ručně editovaným XML partem nebo nástrojem, který zapsal hodnoty bez přepočtu, vám s klidem ukáže součet, který už ze svých vstupů neplyne, a nic ve formátu souboru to neoznačí

Proč je cached hodnota, která nesedí se svou formulí, tak nebezpečná?

Protože je neviditelná v každé běžné čtecí cestě. Otevřete soubor ve vieweru, přečtěte buňku přes API, exportujte ji do CSV nebo PDF, a dostanete cached číslo. Formule je hned tamtéž ve stejné buňce a nikdo je nesrovnává. Neshoda vyjde najevo, až někdo otevře sešit v Excelu, který při načtení ve většině nastavení přepočítává, a najednou report schválený minulý kvartál ukazuje jiné součty

Audit existuje proto, aby tohle srovnání bylo záměrná, naplánovaná operace místo nehody. Je to spreadsheetová obdoba ověřování checksumu: dost levné na to, aby běželo v intake pipeline, a jediná věc, která z tichého problému s integritou dat udělá report, se kterým něco uděláte

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;

Overloady jsou tři a odpovídají na tři různé otázky. Bezparametrový CalculateAndVerify vrátí počet neshod, což je vše, co health check potřebuje. Overload s out polem neshod vám dá buňky. Overload beroucí TXLSRecalcAuditOptions vrátí plný TXLSCalculationAuditReport, po kterém sáhnete, když potřebujete vědět nejen to, že hodnota nesedí, ale i proč audit něco nedokázal vyhodnotit

Overlay, aneb proč audit nezapisuje

Každá přepočtená hodnota dopadne do overlay, ne do cache buněk, a overlay se vstřikuje na samém začátku cell-read callbacku v obou enginech sešitu. Tohle umístění je to, co dělá audit self-consistent: když se přepočítá B1 a C1 závisí na B1, C1 vidí hodnotu z tohoto audit passu, ne zastaralou cached. Bez toho by se jediná chyba proti proudu nahlásila jednou a pak se pohltila a každá buňka po proudu by vypadala, že souhlasí se špatným vstupem

Buňky, jejichž přepočtená hodnota sedí na cache, do overlay nevstupují vůbec. To není micro-optimalizace, to je to, co drží audit cenově přijatelný. Čistý sešit se sto tisíci formulěmi provede nula overlay zápisů a pass zůstane v rozpočtu 1,35x proti plnému přepočtu, což je rozdíl mezi něčím, co můžete pustit na každém intake, a něčím, co pustíte jednou za kvartál

Vyhodnocení jde v sériovém topologickém pořadí odvozeném z dependency graph, s každým nodeem nejdřív označeným za dirty, takže každá buňka se spočítá přesně jednou po svých vstupech. Chcete-li inkrementální stroj, který drží živý sešit aktuální místo auditu uloženého, to je jiný mechanismus, popsaný v článku inkrementální přepočet a dependency graph

Selhání se třídí, neházejí se do jedné hromady

Buňka, kterou audit neumí vyhodnotit, není tentýž nález jako buňka, jejíž hodnota nesedí, a TXLSCalculationAuditIssueKind drží kategorie od sebe. xlcaiCacheMismatch je neshoda hodnoty. xlcaiMissingFunction a xlcaiMissingName říkají, že evaluator potkal něco, co neimplementuje nebo nedokáže vyřešit. xlcaiUnsupportedArguments pokrývá tvary argumentů mimo podporovanou podmnožinu. xlcaiExternalReferenceDenied a xlcaiExternalReferenceMissing oddělují odmítnutí politikou od chybějícího sešitu. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled a xlcaiInternalFailure sadu doplňují

Jedno rozlišení stojí za vyslovení, protože obrací běžný předpoklad. Kladný error kód Excelu je výsledek, ne selhání. Buňka, která legitimně vyhodnotí na #DIV/0!, spočítala správně, takže audit ten error uloží do overlay a porovná ho s cache jako každou jinou hodnotu. Sešit plný záměrných chybových buněk produkuje nulu nálezů a sešit, ve kterém se od zacachování hodnot nějaký error objevil nebo zmizel, produkuje přesně ty nálezy, které chcete

Circular reference dostávají vlastní zacházení. Nody v cyklu se do topologického pořadí nedostanou nikdy, takže se každý hlásí individuálně jako xlcaiCircularReference a audit nespouští iterativní solver. To je záměrná read-only smlouva: to, jestli je iterace zapnutá, ovlivňuje, jak se má interpretovat result kód, ne to, co audit dělá. Mechaniku iterativního vyhodnocení pokrývá samostatně článek iterativní výpočet a circular references

Čtení řetězu selhání

Když formule nevyhodnotí, vědět, která buňka selhala, málokdy stačí, protože selhání bývá tři úrovně hluboko v řetězu referencí. Každý issue proto nese Stack string renderovaný nejvnější frame nejdřív, ve tvaru Sheet1!A1 > Sheet1!B2 > Data!C7, takže report míří na buňku, která se doopravdy rozbila, místo na buňku, na kterou jste se zrovna náhodou dívali

Recorder má strop. MaxStackFrames má default 64 s minimem 8 a zachovává se ten nejhlubší selhávající chain: vnitřní frame chain zaznamená, když tam selhání vznikne, a vnější framy unwindované potom ho nepřepíšou. Přesáhl-li nějaký chain rozpočet, nastaví se Report.StackTruncated, což vám řekne rozdíl mezi krátkým chainem a chainem, který jste neviděli celý

// Defaultně read-only. ApplyResults potvrdí overlay až po zcela
// úspěšném auditu, pod write guardem, který commit odmítne,
// pokud se struktura sešitu během auditu změnila
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // exaktní srovnání, nechá drift vybublat
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;   // audit se zastaví na hraně dalšího nodeu
end;

Kdy auditu dovolit sešit opravit?

Jen když audit vyšel zcela čistě na issue třídy selhání, což je přesně podmínka, kterou za vás vynucuje ApplyResults. Commit proběhne po zcela úspěšném passu, který nebyl zrušen, a projde strukturálním guardem: binary engine hlídá identifikátor změny sešitu, OOXML engine fotí snapshot generace struktury per worksheet. Pohlo se-li cokoliv, zatímco audit běžel, výsledky popisují sešit, který už neexistuje, a commit se odmítne

Všimněte si záměrné asymetrie. Cache mismatche commit neblokují, protože jsou přesně tím, k čemu tam commit na opravu je. Nálezy třídy selhání ho blokují, protože sešit, ve kterém se některé formule nepodařilo vyhodnotit, by byl napůl opravený a napůl opravený sešit je hůř než neopravený, u kterého víte, že mu nemáte věřit

Tolerance je záměrné rozhodnutí, ne default

Defaultní srovnání je absolutní tolerance 1E-6 s relativní tolerancí vypnutou, což zachovává klasické chování a tiše přijme drift 4E-7. Obvykle je to správně: rozdíly v pořadí floating-point vyhodnocení mezi tím, co soubor produkovalo, a současným evaluatorem vyprodukují na dlouhých součtech rozdíly téhle velikosti a hlásit je jako nálezy integrity je šum

Obě tolerance nastavte na nulu, když je otázka jiná, když zjišťujete, zda evaluator změnil chování mezi verzemi, nebo zda third-party tool přepisuje hodnoty subtílně jinak. Na nule se tentýž drift 4E-7 stane viditelným, a s ním všechno ostatní. Volbu tolerance proveďte podle otázky, kterou kladete, a zaznamenejte ji vedle reportu, protože report bez své tolerance není interpretovatelný

Dvě sousedící schopnosti dokončují obraz. Chcete-li vědět, proč jediná formule produkuje hodnotu, kterou produkuje, správným nástrojem je krok-za-krokem pohled v článku tracer vyhodnocení formulí. Chcete-li záměrně, aby se cached hodnoty braly bez jakéhokoli přepočtu, například na intake cestě, která musí soubor reprodukovat přesně tak, jak přišel, tenhle mód popisuje článek čtení cached hodnot formulí bez přepočtu. Audit je to, co sedí mezi nimi: řekne vám, zda je důvěra v cache bezpečná. Dodává se s HotXLS Delphi spreadsheet komponentou pro binary i OOXML engine