HotXLS Deep Recalc auditira kešove formula

HotXLS odgovara na pitanje koje svaki spreadsheet pipeline prije ili kasnije mora postaviti, a to je slažu li se brojevi pohranjeni u radnoj knjizi još s formulama koje su ih proizvele. CalculateAndVerify preračunava cijeli dependency graf u izolirani overlay, svaki rezultat uspoređuje s keširanom vrijednošću koja je već u ćeliji, i izvještava o neslaganjima. Po defaultu ne mijenja ništa

Razlog zašto je to bitno je da spreadsheet datoteka po formulske ćeliji sprema dvije stvari: formulu i zadnju vrijednost koju je netko za nju izračunao. Excel ih drži u sinku. Sve ostalo na svijetu ne mora. Datoteka koja je prošla kroz stariju biblioteku, djelomični preračun, ručno uređen XML dio ili alat koji je zapisao vrijednosti bez ponovnog računanja slobodno će prikazati zbroj koji više ne slijedi iz svojih ulaza, i ništa u file formatu to ne označava

Zašto je keširana vrijednost koja odstupa od formule tako opasna?

Jer je nevidljiva u svakom uobičajenom putu čitanja. Otvorite datoteku u vieweru, pročitajte ćeliju kroz API, izvezite je u CSV ili PDF, i dobijete keširani broj. Formula je odmah tu u istoj ćeliji, i nitko ih ne uspoređuje. Neslaganje ispliva tek kad netko otvori radnu knjigu u Excelu, koji pod većinom postavki preračunava pri učitavanju, i odjednom izvještaj potpisan prošli kvartal pokazuje druge zbrojeve

Audit postoji da tu usporedbu učini namjernom, zakazanom operacijom umjesto nesreće. To je spreadsheet pandan provjeri checksuma: dovoljno jeftin da trči u intake pipelineu, i jedina stvar koja tihi problem integriteta podataka pretvara u izvještaj na kojem možete djelovati

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;

Postoje tri overloada i odgovaraju na tri različita pitanja. Bezparametarski CalculateAndVerify vraća broj neslaganja, a to je sve što health check treba. Overload s out arrayom neslaganja daje vam ćelije. Overload koji uzima TXLSRecalcAuditOptions vraća puni TXLSCalculationAuditReport, i to je onaj za koji se poseže kad trebate znati ne samo da vrijednost ne slaže nego i zašto audit nije mogao nešto evaluirati

Overlay, i zašto audit ne piše

Svaka preračunata vrijednost slijeće u overlay umjesto u ćelijski keš, i overlay je ubačen na sam prednji kraj cell-read callbacka u oba workbook enginea. Taj smještaj je ono što audit čini samokonzistentnim: kad se B1 preračuna i C1 ovisi o B1, C1 vidi vrijednost iz ovog audit prolaza, ne zastarjelu keširanu. Bez toga, jedna uzvodna greška bi se javila jednom i bila apsorbirana, i svaka nizvodna ćelija bi izgledala da se slaže s krivim ulazom

Ćelije čija preračunata vrijednost odgovara kešu uopće ne ulaze u overlay. To nije mikrooptimizacija, to je ono što audit drži pristupačnim. Čista radna knjiga sa sto tisuća formula izvodi nula overlay zapisa i prolaz ostaje unutar 1.35x budžeta naspram punog preračuna, a to je razlika između nečega što možete pokrenuti na svaki intake i nečega što pokrećete jednom u kvartalu

Evaluacija slijedi serijski topološki red izveden iz dependency grafa, sa svakim nodeom prvo označenim dirty, pa se svaka ćelija računa točno jednom nakon svojih ulaza. Ako želite inkrementalnu mašineriju koja živu radnu knjigu drži ažurnom umjesto da auditira pohranjenu, to je drugi mehanizam, opisan u inkrementalnom preračunu i dependency grafu

Neuspjesi su klasificirani, ne gurani u jednu hrpu

Ćeliju koju audit ne može evaluirati nije isti nalaz kao ćelija čija se vrijednost ne slaže, i TXLSCalculationAuditIssueKind drži kategorije razdvojene. xlcaiCacheMismatch je neslaganje vrijednosti. xlcaiMissingFunction i xlcaiMissingName kažu da je evaluator sreo nešto što ne implementira ili ne može razriješiti. xlcaiUnsupportedArguments pokriva oblike argumenata izvan podržanog podskupa. xlcaiExternalReferenceDenied i xlcaiExternalReferenceMissing razdvajaju odbijanje politikom od odsutne radne knjige. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled i xlcaiInternalFailure zaokružuju skup

Jedna razlika vrijedi da se izriče jer okreće uobičajenu pretpostavku. Pozitivni Excel error code je rezultat, a ne neuspjeh. Ćelija koja legitimno evaluira u #DIV/0! izračunala je ispravno, pa audit tu grešku sprema u overlay i uspoređuje je s kešom kao bilo koju drugu vrijednost. Radna knjiga puna namjernih error ćelija proizvodi nula nalaza, a radna knjiga u kojoj je greška od kad su vrijednosti keširane nestala ili se pojavila proizvodi točno nalaze koje želite

Kružne reference dobivaju vlastiti tretman. Nodeovi u ciklusu nikad ne ulaze u topološki red, pa se svaki pojedinačno javlja kao xlcaiCircularReference, i audit ne pokreće iterativni solver. To je namjerni read-only ugovor: to je li iteracija omogućena utječe na to kako se result code treba tumačiti, ne na to što audit radi. Mehanika iterativne evaluacije pokrivena je odvojeno u iterativnom računanju i kružnim referencama

Čitanje lanca neuspjeha

Kad formula ne uspije evaluirati, znati koja je ćelija pala rijetko je dovoljno, jer je neuspjeh obično tri razine niz lanac referenci. Svaki issue zato nosi Stack string renderiran s krajnje vanjskim frameom prvim, u obliku Sheet1!A1 > Sheet1!B2 > Data!C7, pa izvještaj pokazuje na ćeliju koja je stvarno pukla umjesto na ćeliju koju ste slučajno gledali

Rekorder je ograničen. MaxStackFrames je po defaultu 64 s podom od 8, i zadržava se najdublji lanac koji je pao: unutarnji frame zabilježi lanac kad tamo neuspjeh nastaje, a vanjski frameovi koji se odmotavaju poslije njega ne prebrišu. Ako je ijedan lanac prešao budžet, postavlja se Report.StackTruncated, što vam kaže razliku između kratkog lanca i lanca kojega niste vidjeli cijeloga

// Read-only po defaultu. ApplyResults komitira overlay tek nakon
// potpuno uspješnog audita, pod write guardom koji odbija commit
// ako se struktura radne knjige mijenjala dok je audit trčao
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // točna usporedba, izvlači 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 staje na sljedećoj granici nodea
end;

Kad pustiti audit da popravi radnu knjigu?

Tek kad je audit vratio potpuno čist od issueja klase neuspjeha, što je točno uvjet koji ApplyResults za vas nameće. Commit se odvija nakon potpuno uspješnog prolaza, nije otkazan, i prolazi strukturni guard: binary engine pazi na identifikator promjene radne knjige, OOXML engine snapshotira generaciju strukture po listu. Ako se išta pomaklo dok je audit trčao, rezultati opisuju radnu knjigu koja više ne postoji i commit se odbija

Primijetite namjernu asimetriju. Cache neslaganja ne blokiraju primjenu, jer su točno ono što commit postoji da popravi. Issueji klase neuspjeha je blokiraju, jer bi radna knjiga u kojoj neke formule nisu mogle biti evaluirane bila napola popravljena, a napola popravljena radna knjiga je gora od nepopravljene za koju znate da joj ne smijete vjerovati

Tolerancija je policy odluka, ne default

Defaultna usporedba je apsolutna tolerancija 1E-6 s isključenom relativnom tolerancijom, što čuva klasično ponašanje i tiho prihvaća drift od 4E-7. To je obično pravo: razlike u redu floating-point evaluacije između onoga što je proizvelo datoteku i trenutnog evaluatora proizvest će razlike te veličine na dugim zbrojevima, i javljati ih kao nalaze integriteta je šum

Postavite obje tolerancije na nulu kad je pitanje drugačije, kad pokušavate saznati je li evaluator promijenio ponašanje između verzija, ili treba li third-party alat prepisivati vrijednosti na suptilno drugačiji način. Na nuli isti taj drift od 4E-7 postane vidljiv, i sve ostalo također. Odaberite toleranciju prema pitanju koje postavljate, i zabilježite izbor uz izvještaj, jer izvještaj bez svoje tolerancije nije interpretabilan

Dvije susjedne mogućnosti zaokružuju sliku. Kad želite znati zašto jedna formula proizvodi vrijednost koju proizvodi, korak-po-korak pogled u traceru evaluacije formula je pravi alat. Kad namjerno želite da se keširane vrijednosti poštuju bez ikakva preračuna, primjerice na intake putu koji mora reproducirati datoteku točno onakvom kakva je stigla, taj je način opisan u čitanju keširanih vrijednosti formula bez preračunavanja. Audit je ono što stoji između ta dva: kaže vam je li vjerovati kešu sigurno. Isporučuje se uz HotXLS Delphi spreadsheet komponentu za binary i OOXML enginee