HotXLS beantwoordt de vraag die elke spreadsheetpipeline vroeg of laat moet stellen: komen de getallen die in een workbook staan nog overeen met de formules die ze produceerden. CalculateAndVerify berekent de hele dependency graph opnieuw in een geïsoleerde overlay, vergelijkt elk resultaat met de cachewaarde die al in de cel zit en meldt de afwijkingen. Standaard verandert hij niets
De reden dat dit telt, is dat een spreadsheetfile per formulecel twee dingen bewaart: de formule en de laatste waarde die iemand ervoor berekende. Excel houdt die synchroon. De rest van de wereld hoeft dat niet. Een file die langs een oudere library kwam, een partiële herberekening, een met de hand bewerkte XML-part of een tool die waarden wegschreef zonder te herberekenen, presenteert braaf een totaal dat niet meer uit zijn invoer volgt, en niets in het bestandsformaat vlagt dat
Waarom is een cachewaarde die afwijkt van zijn formule zo gevaarlijk?
Omdat hij onzichtbaar is in elke gewone leesroute. Open de file in een viewer, lees de cel via een API, exporteer hem naar CSV of PDF, en u krijgt het gecachte getal. De formule staat er vlak naast in dezelfde cel, en niemand vergelijkt ze. De mismatch komt pas boven als iemand de workbook opent in Excel, dat bij de meeste instellingen bij het laden herberekent, en plotseling laat een rapport dat vorig kwartaal is afgetekend andere totalen zien
De audit bestaat om die vergelijking een bewuste, geplande operatie te maken in plaats van een toevalstreffer. Het is het spreadsheet-equivalent van een checksum controleren: goedkoop genoeg om in een intake-pipeline te draaien, en het enige dat een stilletjes data-integriteitsprobleem omzet in een rapport waar u iets mee kunt
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;Er zijn drie overloads en ze beantwoorden drie verschillende vragen. De parameterloze CalculateAndVerify geeft een aantal mismatches terug, en dat is alles wat een gezondheidscheck nodig heeft. De overload met een out-array van mismatches geeft u de cellen. De overload met een TXLSRecalcAuditOptions geeft een volledige TXLSCalculationAuditReport terug, en dat is degene om te pakken als u niet alleen wilt weten dat een waarde afwijkt, maar ook waarom de audit iets niet kon evalueren
De overlay, en waarom de audit niet schrijft
Elke herberekende waarde belandt in een overlay in plaats van in de celcache, en de overlay wordt in beide workbook-engines vooraan in de cell-read callback geïnjecteerd. Die plaatsing maakt de audit self-consistent: als B1 wordt herberekend en C1 van B1 afhangt, ziet C1 de waarde uit deze auditpas, niet de verouderde cachewaarde. Zonder dat zou één fout stroomopwaarts één keer worden gemeld en daarna worden opgeslokt, en zou elke cel stroomafwaarts met een verkeerde invoer lijken te accorderen
Cellen waarvan de herberekende waarde matcht met de cache, komen helemaal niet in de overlay. Dat is geen micro-optimalisatie, het is wat de audit betaalbaar houdt. Een schone workbook met honderdduizend formules doet nul overlay-schrijfacties en de pas blijft binnen een budget van 1,35x tegenover een volledige herberekening, en dat is het verschil tussen iets dat u op elke intake kunt draaien en iets dat u een keer per kwartaal draait
De evaluatie volgt een seriële topologische volgorde afgeleid uit de dependency graph, met elke node eerst als dirty gemarkeerd, zodat elke cel precies één keer wordt berekend na zijn invoer. Wilt u de incrementele machinery die een live workbook actueel houdt in plaats van een opgeslagen te auditen, dat is een ander mechanisme, beschreven in incrementele herberekening en de dependency graph
Failures worden geclassificeerd, niet op een hoop gegooid
Een cel die de audit niet kan evalueren is niet dezelfde bevinding als een cel waarvan de waarde afwijkt, en TXLSCalculationAuditIssueKind houdt de categorieën uit elkaar. xlcaiCacheMismatch is de waardafwijking. xlcaiMissingFunction en xlcaiMissingName zeggen dat de evaluator iets tegenkwam dat hij niet implementeert of niet kan resolveren. xlcaiUnsupportedArguments dekt argumentvormen buiten de ondersteunde subset. xlcaiExternalReferenceDenied en xlcaiExternalReferenceMissing scheiden een beleidsweigering van een afwezige workbook. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled en xlcaiInternalFailure maken de set compleet
Eén onderscheid is het benoemen waard omdat het een gangbare aanname omdraait. Een positieve Excel-foutcode is een resultaat, geen failure. Een cel die legitiem op #DIV/0! uitkomt, heeft correct gerekend, dus de audit slaat die fout op in de overlay en vergelijkt hem met de cache als elke andere waarde. Een workbook vol cellen die bewust op een fout uitkomen, levert nul bevindingen op, en een workbook waarin een fout verscheen of verdween sinds de waarden waren gecached, levert precies de bevindingen op die u wilt
Circulaire referenties krijgen een eigen behandeling. Nodes in een cyclus komen nooit in de topologische volgorde, dus elke node wordt apart gemeld als xlcaiCircularReference, en de audit draait de iteratieve solver niet. Dat is een bewust read-only contract: of iteratie aanstaat beïnvloedt hoe de resultaatcode moet worden geïnterpreteerd, niet wat de audit doet. De mechaniek van iteratieve evaluatie wordt apart behandeld in iteratieve berekening en circulaire referenties
Een failure chain lezen
Als een formule niet evalueert, is weten welke cel faalde zelden genoeg, want de failure zit meestal drie niveaus diep in een keten van verwijzingen. Elke issue draagt daarom een Stack-string, van buitenste frame naar binnen gerenderd, in de vorm Sheet1!A1 > Sheet1!B2 > Data!C7, zodat het rapport naar de cel wijst die werkelijk brak in plaats van naar de cel waar u toevallig keek
De recorder is begrensd. MaxStackFrames staat standaard op 64 met een ondergrens van 8, en de diepste falende keten is degene die bewaard blijft: een binnenste frame legt de keten vast als de failure daar ontstaat, en buitenste frames die daarna uitrollen overschrijven hem niet. Als een keten het budget overschreed, wordt Report.StackTruncated gezet, en dat vertelt u het verschil tussen een korte keten en een keten waarvan u niet alles zag
// Standaard read-only. ApplyResults commit de overlay pas na een
// volledig geslaagde audit, onder een write guard die de commit weigert
// als de workbookstructuur veranderde terwijl de audit draaide
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0; // exacte vergelijking, brengt drift boven
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 stopt bij de volgende nodegrens
end;Wanneer laat u de audit de workbook repareren?
Alleen als de audit helemaal schoon terugkwam op failure-klasse-issues, en dat is precies de voorwaarde die ApplyResults voor u afdwingt. De commit gebeurt na een volledig geslaagde pas die niet is geannuleerd en die een structurele guard passeert: de binaire engine bewaakt een workbook change identifier, de OOXML-engine neemt per worksheet een structuregeneratie op. Bewoog er iets terwijl de audit draaide, dan beschrijven de resultaten een workbook die niet meer bestaat en wordt de commit geweigerd
Merk de bewuste asymmetrie op. Cache-mismatches blokkeren de toepassing niet, want precies daarvoor is de commit er om te repareren. Failure-klasse-issues blokkeren hem wel, want een workbook waarin sommige formules niet konden worden geëvalueerd zou half gerepareerd worden, en een half gerepareerde workbook is erger dan een niet-gerepareerde waarvan u weet dat u haar moet wantrouwen
Tolerantie is een beleidskeuze, geen default
De standaardvergelijking is een absolute tolerantie van 1E-6 met relatieve tolerantie uitgeschakeld, wat het klassieke gedrag bewaart en een drift van 4E-7 stilletjes accepteert. Dat is meestal goed: verschillen in floating-point-evaluatievolgorde tussen wat de file produceerde en de huidige evaluator leveren bij lange sommen verschillen van die grootte op, en die als integriteitsbevindingen rapporteren is ruis
Zet beide toleranties op nul als de vraag anders is: als u wilt achterhalen of een evaluator tussen versies van gedrag veranderde of dat een third-party tool waarden op subtiele wijze anders wegschrijft. Op nul wordt dezelfde drift van 4E-7 zichtbaar, en al het andere ook. Kies de tolerantie op basis van welke vraag u stelt, en leg de keuze naast het rapport vast, want een rapport zonder zijn tolerantie is niet interpreteerbaar
Twee naburige capaciteiten maken het plaatje compleet. Wilt u weten waarom één formule de waarde produceert die hij produceert, dan is de stapsgewijze weergave in de formula evaluation tracer het juiste gereedschap. Wilt u bewust cachewaarden gevolgd laten zonder enige herberekening, bijvoorbeeld op een intake-route die de file exact moet reproduceren zoals hij binnenkwam, dan staat die modus beschreven in gecachte formulewaarden lezen zonder te herberekenen. De audit zit precies tussen die twee in: hij vertelt u of het vertrouwen van de cache veilig is. Hij wordt geleverd met de HotXLS Delphi spreadsheet component, voor zowel de binaire als de OOXML-engine