Granska Excel-formelcacher med HotXLS Deep Recalc

HotXLS svarar på den fråga varje kalkylbladspipeline förr eller senare måste ställa, nämligen om talen som lagrats i en arbetsbok fortfarande matchar formlerna som producerade dem. CalculateAndVerify räknar om hela beroendegrafen till ett isolerat overlay, jämför varje resultat mot det cachade värde som redan ligger i cellen, och rapporterar avvikelserna. Som standard ändrar den ingenting

Skälet till att detta spelar roll är att en kalkylbladsfil lagrar två saker per formelcell: formeln och det senaste värde någon beräknade för den. Excel håller dem synkade. Allt annat i världen behöver inte göra det. En fil som passerat ett äldre bibliotek, en partiell omräkning, en handredigerad XML-del eller ett verktyg som skrev värden utan att räkna om dem presenterar med glädje en summa som inte längre följer av sina indata, och ingenting i filformatet flaggar det

Varför är ett cachat värde som inte stämmer med sin formel så farligt?

Därför att det är osynligt i varje vanlig läsväg. Öppna filen i en visare, läs cellen via ett API, exportera den till CSV eller PDF, och du får det cachade talet. Formeln ligger ju där i samma cell, och ingen jämför dem. Avvikelsen dyker bara upp när någon öppnar arbetsboken i Excel, som räknar om vid inläsning under de flesta inställningar, och plötsligt visar en rapport som signerades förra kvartalet andra summer

Granskningen finns för att göra den jämförelsen till en medveten, schemalagd operation snarare än en olycka. Det är kalkylbladsmotsvarigheten till att verifiera en checksumma: billig nog att köra i en intagspipeline, och det enda som förvandlar ett tyst dataintegritetsproblem till en rapport du kan agera 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;

Det finns tre overloads och de svarar på tre olika frågor. Den parameterlösa CalculateAndVerify returnerar ett antal avvikelser, vilket är allt en hälsokontroll behöver. Overload:en med en out-array av avvikelser ger dig cellerna. Overload:en som tar TXLSRecalcAuditOptions returnerar en fullständig TXLSCalculationAuditReport, vilket är den att sträcka sig efter när du behöver veta inte bara att ett värde avviker utan varför granskningen inte kunde utvärdera något

Overlayout:en, och varför granskningen inte skriver

Vartenda omräknat värde landar i ett overlay i stället för i cellcachen, och overlayout injiceras allra främst i cell-läscallback:en i båda arbetsboksmotorerna. Den placeringen är det som gör granskningen självkonsistent: när B1 räknas om och C1 beror på B1 ser C1 värdet från detta granskningspass, inte det inaktuella cachade. Utan det skulle ett enda uppströmsfel rapporteras en gång och sedan absorberas, och varje nedströmscell skulle verka hålla med om en felaktig indata

Celler vars omräknade värde matchar cachen kommer alls inte in i overlayout. Det är ingen mikrooptimering, det är det som håller granskningen prisvärd. En ren arbetsbok med hundra tusen formler presterar noll overlayskrivningar och passet stannar inom en 1,35x-budget mot en fullständig omräkning, vilket är skillnaden mellan något du kan köra vid varje intag och något du kör en gång per kvartal

Utvärderingen följer en seriell topologisk ordning härledd ur beroendegrafen, med varje nod markerad smutsig först, så varje cell beräknas exakt en gång efter sina indata. Om du vill ha det inkrementella maskineriet som håller en levande arbetsbok aktuell i stället för att granska en lagrad är det en annan mekanism, beskriven i inkrementell omräkning och beroendegrafen

Fel klassificeras, inte hopkas

En cell granskningen inte kan utvärdera är inte samma fynd som en cell vars värde avviker, och TXLSCalculationAuditIssueKind håller kategorierna isär. xlcaiCacheMismatch är värdavvikelsen. xlcaiMissingFunction och xlcaiMissingName säger att utvärderaren mötte något den inte implementerar eller inte kan lösa. xlcaiUnsupportedArguments täcker argumentformer utanför den stödda delmängden. xlcaiExternalReferenceDenied och xlcaiExternalReferenceMissing skiljer en policyavvisning från en frånvarande arbetsbok. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled och xlcaiInternalFailure fullbordar setet

En distinktion är värd att sägas eftersom den vänder en vanlig antagande. En positiv Excel-felkod är ett resultat, inte ett fel. En cell som legitimt utvärderas till #DIV/0! har beräknats korrekt, så granskningen lagrar det felet i overlayout och jämför det mot cachen som vilket annat värde som helst. En arbetsbok full av medvetna felceller ger noll fynd, och en arbetsbok där ett fel dykt upp eller försvunnit sedan värdena cachades ger exakt de fynd du vill ha

Cirkulära referenser får sin egen behandling. Noder i en cykel kommer aldrig in i den topologiska ordningen, så var och en rapporteras individuellt som xlcaiCircularReference, och granskningen kör inte den iterativa lösaren. Det är ett medvetet skrivskyddat kontrakt: huruvida iteration är aktiverad påverkar hur resultatkoden ska tolkas, inte vad granskningen gör. Mekaniken bakom iterativ utvärdering tas upp separat i iterativ beräkning och cirkulära referenser

Att läsa en felkedja

När en formel misslyckas att utvärdera räcker det sällan att veta vilken cell som fallerade, eftersom felet vanligen ligger tre nivåer ner i en referenskedja. Vartenda ärende bär därför en Stack-sträng renderad med yttersta ramen först, i formen Sheet1!A1 > Sheet1!B2 > Data!C7, så att rapporten pekar på cellen som faktiskt gick sönder i stället för cellen du råkade titta på

Inspelaren är avgränsad. MaxStackFrames har förvalet 64 med ett golv på 8, och den djupaste fallerande kedjan är den som behålls: en inre ram spelar in kedjan när felet uppstår där, och yttre ramar som vecklar ut sig efteråt skriver inte över den. Om någon kedja överskred budgeten sätts Report.StackTruncated, vilket talar om skillnaden mellan en kort kedja och en kedja du inte så hela

// Skrivskyddad som standard. ApplyResults committar overlayouten endast
// efter en helt framgångsrik granskning, under en skrivvakt som avvisar
// committen om arbetsbokens struktur ändrades medan granskningen körde
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // exakt jämförelse, blottlä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;   // granskningen stannar vid nästa nodgräns
end;

När bör du låta granskningen reparera arbetsboken?

Endast när granskningen kom tillbaka helt ren från felklassärenden, vilket är precis villkoret ApplyResults tvingar fram åt dig. Commiten sker efter ett fullständigt framgångsrikt pass, avbröts inte, och passerar en strukturell vakt: den binära motorn bevakar en arbetsboksändringsidentifierare, OOXML-motorn tar en ögonblicksbild av en strukturgeneration per kalkylblad. Om något flyttade medan granskningen körde beskriver resultaten en arbetsbok som inte längre finns och commiten avslås

Notera den medvetna asymmetrin. Cacheavvikelser blockerar inte tillämpningen, eftersom de är precis det committen finns för att reparera. Felklassärenden blockerar den däremot, eftersom en arbetsbok där vissa formler inte kunde utvärderas skulle bli halvreparerad, och en halvreparerad arbetsbok är värre än en oreparerad som du vet ska misstänkas

Tolerans är ett policybeslut, inte ett förval

Standardjämförelsen är en absolut tolerans på 1E-6 med relativ tolerans avstängd, vilket bevarar det klassiska beteendet och tyst accepterar en drift på 4E-7. Det är vanligen rätt: skillnader i utvärderingsordning för flyttal mellan vad som nu producerade filen och den aktuella utvärderaren ger skillnader av den storleken på långa summor, och att rapportera dem som integritetsfynd är brus

Sätt båda toleranserna till noll när frågan är en annan, när du försöker ta reda på om en utvärderare ändrade beteende mellan versioner, eller om ett tredjepartsverktyg skriver om värden på ett subtilt annat sätt. Vid noll blir samma 4E-7-drift synlig, och så gör allt annat. Välj toleransen utifrån vilken fråga du ställer, och registrera valet bredvid rapporten, för en rapport utan sin tolerans är inte tolkningsbar

Två grannförmågor fullbordar bilden. När du vill veta varför en enskild formel producerar värdet den gör är steg-för-steg-vyn i formelutvärderingsspåraren rätt verktyg. När du medvetet vill att cachade värden ska hedras utan någon omräkning, till exempel på en intagsväg som måste reproducera filen exakt som den anlände, beskrivs det läget i att läsa cachade formelvärden utan omräkning. Granskningen är det som sitter mellan de två: den talar om för dig huruvida det är säkert att lita på cachen. Den medföljer HotXLS Delphi spreadsheet component för både den binära och OOXML-motorn