Auditering af Excel-formelcacher med HotXLS Deep Recalc

HotXLS besvarer det spørgsmål, enhver spreadsheet-pipeline før eller siden må stille, nemlig om tallene gemt i en projektmappe stadig matcher formlerne, der producerede dem. CalculateAndVerify beregner hele afhængighedsgrafen om til et isoleret overlay, sammenligner hvert resultat med den cachede værdi, der allerede ligger i cellen, og melder uenighederne. Som default ændrer den intet

Grunden til, at det betyder noget, er, at en spreadsheet-fil gemmer to ting per formelcelle: formlen og den sidste værdi, nogen beregnede til den. Excel holder dem synkroniserede. Alt andet i verden gør det måske ikke. En fil, der har passeret et ældre bibliotek, en delvis genberegning, en håndredigeret XML-del eller et værktøj, der skrev værdier uden at beregne dem om, præsenterer gerne en total, der ikke længere følger af sine input, og intet i filformatet flagger det

Hvorfor er en cached værdi, der er uenig med sin formel, så farlig?

Fordi den er usynlig i enhver almindelig læsevej. Åbn filen i en viewer, læs cellen gennem en API, eksportér den til CSV eller PDF, og du får det cachede tal. Formlen ligger lige der i samme celle, og ingen sammenligner dem. Uenigheden kommer kun til overfladen, når nogen åbner projektmappen i Excel, som beregner om ved indlæsning under de fleste indstillinger, og pludselig viser en rapport, der blev godkendt sidste kvartal, andre totaler

Auditens formål er at gøre den sammenligning til en bevidst, planlagt operation snarere end en ulykke. Det er spreadsheet-modstykket til at verificere et tjeksum: billigt nok til at køre i en indgangspipeline, og det eneste, der forvandler et lydløst dataintegritetsproblem til en rapport, du kan handle på

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;

Der er tre overloads, og de besvarer tre forskellige spørgsmål. Den parameterløse CalculateAndVerify returnerer et mismatch-antal, hvilket er alt, et sundhedstjek behøver. Overloaden med et out-array af mismatches giver dig cellerne. Overloaden, der tager TXLSRecalcAuditOptions, returnerer en fuld TXLSCalculationAuditReport, og det er den, du rækker efter, når du behøver at vide ikke blot, at en værdi er uenig, men hvorfor auditen ikke kunne evaluere noget

Overlayet, og hvorfor auditen ikke skriver

Hver omberegnet værdi lander i et overlay snarere end i celle-cachen, og overlayet injiceres helt forrest i cell-read-callbacken i begge workbook-engines. Den placering er det, der gør auditen selvkonsistent: når B1 beregnes om, og C1 afhænger af B1, ser C1 værdien fra denne audit-omgang, ikke den forældede cachede. Uden det ville en enkelt upstream-fejl blive meldt én gang og derefter absorberet, og hver downstream-celle ville se ud til at være enig med et forkert input

Celler, hvis omberegnede værdi matcher cachen, kommer slet ikke ind i overlayet. Det er ikke en mikrooptimering, det er det, der holder auditen overkommelig. En ren projektmappe med hundrede tusind formler udfører nul overlay-skrivninger, og omgangen holder sig inden for et 1,35x-budget i forhold til en fuld genberegning, hvilket er forskellen på noget, du kan køre ved hver indgang, og noget, du kører én gang om kvartalet

Evaluering følger en serial topologisk orden afledt af afhængighedsgrafen, med hver node markeret dirty først, så hver celle beregnes præcis én gang efter sine input. Vil du have det inkrementelle maskineri, der holder en live-projektmappe aktuel, i stedet for at auditere en gemt én, er det en anden mekanisme, beskrevet i inkrementel genberegning og afhængighedsgrafen

Fejl klassificeres, ikke hældes i én bunke

En celle, auditen ikke kan evaluere, er ikke samme fund som en celle, hvis værdi er uenig, og TXLSCalculationAuditIssueKind holder kategorierne adskilt. xlcaiCacheMismatch er værdiuenigheden. xlcaiMissingFunction og xlcaiMissingName siger, at evaluatoren mødte noget, den ikke implementerer eller ikke kan resolve. xlcaiUnsupportedArguments dækker argumentformer uden for det understøttede subset. xlcaiExternalReferenceDenied og xlcaiExternalReferenceMissing adskiller en politiknægtelse fra en fraværende projektmappe. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled og xlcaiInternalFailure fuldender sættet

Én skelnen er værd at fremhæve, for den vender en almindelig antagelse på hovedet. En positiv Excel-fejlkode er et resultat, ikke en fejl. En celle, der legitimit evaluerer til #DIV/0!, har beregnet korrekt, så auditen gemmer den error i overlayet og sammenligner den med cachen som enhver anden værdi. En projektmappe fuld af bevidste error-celler producerer nul fund, og en projektmappe, hvor en error er opstået eller forsvundet, siden værdierne blev cachet, producerer præcis de fund, du vil have

Cirkulære referencer får deres egen behandling. Noder i en cyklus kommer aldrig ind i den topologiske orden, så hver meldes individuelt som xlcaiCircularReference, og auditen kører ikke den iterative solver. Det er en bevidst read-only-kontrakt: om iteration er slået til påvirker, hvordan resultatkoden skal fortolkes, ikke hvad auditen gør. Mekanikken i iterativ evaluering er dækket separat i iterativ beregning og cirkulære referencer

Læse en fejlkæde

Når en formel fejler at evaluere, er det sjældent nok at vide, hvilken celle der fejlede, for fejlen ligger som regel tre niveauer nede i en kæde af referencer. Hvert issue bærer derfor en Stack-streng renderet med yderste frame først, i formen Sheet1!A1 > Sheet1!B2 > Data!C7, så rapporten peger på cellen, der reelt brød, snarere end cellen, du tilfældigvis kiggede på

Optageren er bounded. MaxStackFrames er default 64 med et gulv på 8, og den dybeste fejlende kæde er den, der bevares: en indre frame optager kæden, når fejlen udspringer dér, og ydre frames, der senere wikler af, overskriver den ikke. Overskred nogen kæde budgettet, sættes Report.StackTruncated, hvilket fortæller dig forskellen på en kort kæde og en kæde, du ikke så hele

// Read-only som default. ApplyResults committer overlayet først efter en
// fuldstændig vellykket audit, under en write guard, der afviser commiten,
// hvis projektmappestrukturen ændredes, mens auditen kørte
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // eksakt sammenligning, blotlægger 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;   // audit stopper ved næste nodegrænse
end;

Hvornår bør du lade auditen reparere projektmappen?

Kun når auditen kom tilbage helt ren for fejlklasse-issues, hvilket netop er betingelsen, ApplyResults håndhæver for dig. Commit sker efter en fuldstændig vellykket omgang, er ikke blevet annulleret, og passerer en strukturel guard: binary-enginen overvåger en projektmappe-ændringsidentifikator, OOXML-enginen tager snapshot af en pr.-worksheet-strukturgeneration. Flyttede noget som helst, mens auditen kørte, beskriver resultaterne en projektmappe, der ikke længere eksisterer, og commit nægtes

Læg mærke til den bevidste asymmetri. Cache-mismatches blokerer ikke anvendelsen, for de er præcis det, committen er der for at reparere. Fejlklasse-issues blokerer den derimod, for en projektmappe, hvor nogle formler ikke kunne evalueres, ville blive halvt repareret, og en halvt repareret projektmappe er værre end en urepareret, du ved, du ikke skal stole på

Tolerance er en politikbeslutning, ikke en default

Sammenligningen som default er en 1E-6 absolut tolerance med relativ tolerance slået fra, hvilket bevarer den klassiske adfærd og stille accepterer en drift på 4E-7. Det er som regel rigtigt: forskelle i floating-point evalueringsrækkefølge mellem det, der producerede filen, og den aktuelle evaluator giver forskelle af den størrelse på lange summer, og at melde dem som integritetsfund er støj

Sæt begge tolerancer til nul, når spørgsmålet er et andet, når du forsøger at finde ud af, om en evaluator ændrede adfærd mellem versioner, eller om et tredjepartsværktøj omskriver værdier på en diskret anderledes måde. Ved nul bliver samme 4E-7-drift synlig, og det gør alt andet også. Vælg tolerancen ud fra, hvilket spørgsmål du stiller, og notér valget ved siden af rapporten, for en rapport uden sin tolerance er ikke fortolkelig

To nabokapabiliteter fuldender billedet. Vil du vide, hvorfor en enkelt formel producerer den værdi, den gør, er trin-for-trin-visningen i formula evaluation tracer det rigtige værktøj. Vil du bevidst have cachede værdier æret uden nogen genberegning, for eksempel på en indgangsvej, der skal reproducere filen præcis, som den ankom, er den tilstand beskrevet i læsning af cachede formelværdier uden genberegning. Auditen er det, der sidder mellem de to: den fortæller dig, om det er sikkert at stole på cachen. Den følger med HotXLS Delphi spreadsheet-komponenten til både binary- og OOXML-engines