Audit delle cache delle formule Excel con HotXLS Deep Recalc

HotXLS risponde alla domanda che ogni pipeline di fogli di calcolo prima o poi deve porsi, cioè se i numeri memorizzati in un workbook corrispondano ancora alle formule che li hanno prodotti. CalculateAndVerify ricalcola l'intero grafo delle dipendenze in un overlay isolato, confronta ogni risultato col valore in cache già presente nella cella, e riporta i disaccordi. Per default non cambia niente

Il motivo per cui conta è che un file di fogli di calcolo memorizza due cose per ogni cella con formula: la formula e l'ultimo valore che qualcuno ha calcolato per lei. Excel li tiene sincronizzati. Tutto il resto del mondo può non farlo. Un file passato per una libreria più vecchia, un ricalcolo parziale, una parte XML modificata a mano o uno strumento che ha scritto valori senza ricalcolarli presenterà allegramente un totale che non segue più dai suoi input, e niente nel formato file lo segnala

Perché un valore in cache in disaccordo con la sua formula è così pericoloso?

Perché è invisibile in ogni ordinario percorso di lettura. Apri il file in un viewer, leggi la cella tramite un'API, esportala in CSV o PDF, e ottieni il numero in cache. La formula sta lì nella stessa cella, e nessuno le confronta. Il disallineamento emerge solo quando qualcuno apre il workbook in Excel, che ricalcola al caricamento con la maggior parte delle impostazioni, e all'improvviso un report firmato lo scorso trimestre mostra totali diversi

L'audit esiste per rendere quel confronto un'operazione deliberata e pianificata invece di un incidente. È l'equivalente foglio di calcolo della verifica di un checksum: abbastanza economico da girare in una pipeline di intake, e l'unica cosa che trasforma un problema silenzioso di integrità dei dati in un report su cui agire

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;

Ci sono tre overload e rispondono a tre domande diverse. Il CalculateAndVerify senza parametri restituisce un conteggio di disaccordi, che è tutto ciò che serve a un health check. L'overload con un array out di disaccordi ti dà le celle. L'overload che prende TXLSRecalcAuditOptions restituisce un TXLSCalculationAuditReport completo, che è quello a cui tendere quando devi sapere non solo che un valore è in disaccordo ma anche perché l'audit non è riuscito a valutare qualcosa

L'overlay, e perché l'audit non scrive

Ogni valore ricalcolato atterra in un overlay invece che nella cache della cella, e l'overlay viene iniettato al primissimo posto della callback di lettura cella in entrambi i motori workbook. Quella collocazione è ciò che rende l'audit auto-coerente: quando B1 viene ricalcolata e C1 dipende da B1, C1 vede il valore di questa passata di audit, non quello stantio in cache. Senza di ciò, un singolo errore a monte verrebbe riportato una volta e poi assorbito, e ogni cella a valle sembrerebbe d'accordo con un input sbagliato

Le celle il cui valore ricalcolato corrisponde alla cache non entrano affatto nell'overlay. Non è una micro-ottimizzazione, è ciò che tiene l'audit a portata di mano. Un workbook pulito con centomila formule esegue zero scritture sull'overlay e la passata resta dentro un budget di 1,35x rispetto a un ricalcolo completo, che è la differenza tra qualcosa che puoi girare a ogni intake e qualcosa che giri una volta a trimestre

La valutazione segue un ordine topologico seriale derivato dal grafo delle dipendenze, con ogni nodo marcato dirty in anticipo, così ogni cella viene calcolata esattamente una volta dopo i suoi input. Se vuoi la macchina incrementale che tiene aggiornato un workbook vivo invece di verificarne uno memorizzato, quella è un meccanismo diverso, descritto in ricalcolo incrementale e grafo delle dipendenze

I guasti sono classificati, non ammucchiati

Una cella che l'audit non riesce a valutare non è lo stesso reperto di una cella il cui valore è in disaccordo, e TXLSCalculationAuditIssueKind tiene le categorie separate. xlcaiCacheMismatch è il disaccordo di valore. xlcaiMissingFunction e xlcaiMissingName dicono che il valutatore ha incontrato qualcosa che non implementa o non riesce a risolvere. xlcaiUnsupportedArguments copre le forme di argomento fuori dal sottoinsieme supportato. xlcaiExternalReferenceDenied e xlcaiExternalReferenceMissing separano un rifiuto di policy da un workbook assente. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled e xlcaiInternalFailure completano l'insieme

Una distinzione vale la pena enunciarla perché rovescia un'assunzione comune. Un codice errore Excel positivo è un risultato, non un fallimento. Una cella che valuta legittimamente a #DIV/0! ha calcolato correttamente, quindi l'audit memorizza quell'errore nell'overlay e lo confronta con la cache come qualunque altro valore. Un workbook pieno di celle d'errore intenzionali produce zero reperti, e un workbook dove un errore è comparso o sparito da quando i valori sono stati messi in cache produce esattamente i reperti che vuoi

I riferimenti circolari hanno un trattamento loro. I nodi in un ciclo non entrano mai nell'ordine topologico, quindi ognuno viene riportato individualmente come xlcaiCircularReference, e l'audit non esegue il solver iterativo. È un contratto deliberato di sola lettura: se l'iterazione è abilitata influenza come il codice risultato va interpretato, non ciò che l'audit fa. La meccanica della valutazione iterativa è trattata a parte in calcolo iterativo e riferimenti circolari

Leggere una catena di guasto

Quando una formula non riesce a valutarsi, sapere quale cella è fallita raramente basta, perché il guasto di solito sta tre livelli più in fondo a una catena di riferimenti. Ogni issue porta quindi una stringa Stack renderizzata con il frame più esterno per primo, nella forma Sheet1!A1 > Sheet1!B2 > Data!C7, così il report punta alla cella che davvero si è rotta invece che alla cella che per caso stavi guardando

Il recorder è limitato. MaxStackFrames ha un default di 64 con un pavimento di 8, e la catena di guasto più profonda è quella conservata: un frame interno registra la catena quando il guasto ha origine lì, e i frame esterni che si srotolano dopo non la sovrascrivono. Se una catena qualsiasi ha superato il budget, Report.StackTruncated viene impostato, il che ti dice la differenza tra una catena corta e una catena di cui non hai visto tutto

// Sola lettura per default. ApplyResults committa l'overlay solo dopo un
// audit interamente riuscito, sotto una guardia di scrittura che rifiuta
// il commit se la struttura del workbook è cambiata mentre l'audit girava
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // confronto esatto, fa emergere la deriva
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;   // l'audit si ferma al prossimo confine di nodo
end;

Quando lasciare che l'audit ripari il workbook?

Solo quando l'audit è tornato completamente pulito dai problemi di classe guasto, che è precisamente la condizione che ApplyResults ti fa rispettare. Il commit avviene dopo una passata interamente riuscita, non è stato cancellato, e supera una guardia strutturale: il motore binario osserva un identificatore di modifica del workbook, il motore OOXML fa uno snapshot di una generazione di struttura per foglio. Se qualcosa si è mosso mentre l'audit girava, i risultati descrivono un workbook che non esiste più e il commit viene rifiutato

Nota l'asimmetria deliberata. I disaccordi di cache non bloccano l'applicazione, perché sono esattamente ciò che il commit esiste per riparare. I problemi di classe guasto lo bloccano, perché un workbook dove alcune formule non hanno potuto essere valutate sarebbe riparato a metà, e un workbook riparato a metà è peggiore di uno non riparato verso cui sai di doverti guardare intorno

La tolleranza è una decisione di policy, non un default

Il confronto predefinito è una tolleranza assoluta di 1E-6 con tolleranza relativa disabilitata, che conserva il comportamento classico e accetta in silenzio una deriva di 4E-7. Di solito è la scelta giusta: le differenze di ordine di valutazione in virgola mobile tra ciò che ha prodotto il file e il valutatore corrente produrranno differenze di quella taglia sulle somme lunghe, e riportarle come reperti di integrità è rumore

Imposta entrambe le tolleranze a zero quando la domanda è diversa, quando stai cercando di scoprire se un valutatore ha cambiato comportamento tra versioni, o se uno strumento di terze parti riscrive i valori in un modo sottilmente diverso. A zero, la stessa deriva di 4E-7 diventa visibile, e così tutto il resto. Scegli la tolleranza in base alla domanda che stai facendo, e registra la scelta accanto al report, perché un report senza la sua tolleranza non è interpretabile

Due capacità vicine completano il quadro. Quando vuoi sapere perché una singola formula produce il valore che produce, la vista passo per passo in il tracer di valutazione delle formule è lo strumento giusto. Quando deliberatamente vuoi che i valori in cache siano onorati senza alcun ricalcolo, per esempio su un percorso di intake che deve riprodurre il file esattamente come è arrivato, quella modalità è descritta in leggere i valori delle formule in cache senza ricalcolare. L'audit è ciò che sta in mezzo a quelle due: ti dice se fidarsi della cache è sicuro. Viene spedito con il HotXLS Delphi spreadsheet component per entrambi il motore binario e quello OOXML