HotXLS, la libreria Excel nativa per Delphi e C++Builder, salva un workbook BIFF8 .xls classico partendo dalla cache: TXLSWorksheet.WriteFormula chiede a TXLSWorkbook.TryGetCachedFormulaValue il valore che Excel ha memorizzato accanto a ogni formula e chiama il valutatore solo quando quella cache manca o è invalidata. Un workbook che hai aperto e non hai mai toccato risalva gli stessi numeri, e i risultati freschi richiedono una chiamata esplicita a Recalculate invece di essere un effetto collaterale nascosto di SaveAs
Il bug che ha portato questo contratto allo scoperto era imbarazzantemente piccolo. Un file del corpus chiamato nested-subtotals.xls contiene un totale generale in R2C4 il cui valore in cache è 37. Aprilo con HotXLS, chiedi TryGetCachedFormulaValue per quella cella, ottieni 37. Salvalo senza cambiare una singola cella, apri la copia salvata, fai la stessa domanda, ottieni 67. All'API non era stato chiesto di calcolare nulla, eppure un numero nel file si era spostato esattamente di 30 — e 30 è per l'appunto la somma dei due subtotali di gruppo, 10 e 20, che stanno dentro l'intervallo coperto dal totale generale
Perché salvare un file XLS cambia il valore di una formula?
Perché quel 37 diventasse 67 dovevano allinearsi due difetti indipendenti, e correggerne solo uno avrebbe nascosto l'altro. Il primo era strutturale: il writer classico ricalcolava ogni formula a ogni salvataggio. Il secondo era un controllo di tipo che non poteva mai essere vero per una formula caricata da disco, e che faceva contare due volte al valutatore le celle SUBTOTAL annidate. Il file del corpus era semplicemente il primo input in cui un ricalcolo al salvataggio produceva una risposta diversa da Excel e qualcuno confrontava le due. Il difetto strutturale è facile da enunciare: prima della v2.382.3, TXLSWorksheet.WriteFormula e il suo fratello per le formule condivise WriteFormulaWithTExp ottenevano il campo FormulaValue da otto byte di ogni record Formula chiamando TXLSWorkbook.GetFormulaValue, che è il valutatore. La cache che ParseFormula aveva decodificato con cura dal file sorgente al caricamento non veniva mai consultata in uscita. In pratica ogni salvataggio era un ricalcolo completo con l'API di ricalcolo a livello di workbook aggirata, quindi nulla di ciò che potevi impostare sul workbook l'avrebbe fermato. Ogni punto in cui il valutatore di HotXLS era in disaccordo con Excel, che fosse una funzione legittimamente non supportata o un semplice bug, diventava una modifica silenziosa dei dati al salvataggio
Il secondo difetto viveva nella callback dei subtotali annidati usata dal valutatore. Excel definisce ogni forma di SUBTOTAL come ignorante verso le celle la cui formula è a sua volta un SUBTOTAL, quindi il calculator in lxCalc.pas arma FIgnoreSubtotalCells durante l'aggregazione e chiede al workbook, tramite TXLSWorkbook.GetClassicIsSubtotalCell, se ogni cella dell'intervallo lo sia. Quella callback recuperava il testo della formula come Variant e lo testava con VarType(f) = varOleStr. Il testo torna da GetUnCompiledFormula come String Delphi, e una String assegnata a un Variant è varUString, mai varOleStr. Il predicato era falso per ogni cella di ogni file caricato, i subtotali di gruppo venivano sommati nel totale generale una seconda volta, e su un salvataggio che ricalcolava tutto, 10 + 20 + 7 diventava 67
// HotXLS 2.381 e precedenti: un Variant di formula costruito da una String
// è varUString, quindi questo confronto non riusciva mai
Result := (VarType(f) = varOleStr) and
(SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
SameText(Copy(f, 1, 10), '=SUBTOTAL('));
// HotXLS 2.382.0: VarIsStr accetta varString, varOleStr e varUString,
// e AGGREGATE è escluso dai subtotali che lo contengono come fa Excel
if VarIsStr(f) then
Result := SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
SameText(Copy(f, 1, 10), '=SUBTOTAL(') or
SameText(Copy(f, 1, 10), 'AGGREGATE(') or
SameText(Copy(f, 1, 11), '=AGGREGATE(');La v2.382.0 ha rilasciato la correzione VarIsStr e, già che si trovava nella stessa funzione, ha insegnato alla callback che anche le celle AGGREGATE sono escluse dai subtotali che le contengono. Già solo questo faceva passare l'asserzione sul corpus, perché il 37 ricalcolato ora corrispondeva al 37 caricato. Non rendeva onesta la libreria: il salvataggio ricalcolava ancora, e il test era verde solo perché il valutatore concordava per caso con Excel su quel particolare file. Le regole su quali celle SUBTOTAL e AGGREGATE saltino, righe nascoste comprese, sono trattate nell'articolo su SUBTOTAL, AGGREGATE e le righe nascoste; quello che conta qui è che nessun valutatore dovrebbe avere voce in capitolo su un file che non gli hai chiesto di calcolare
Che cosa garantisce Excel sui valori in cache al salvataggio?
Excel tratta il salvataggio come un'istantanea, non come un evento di calcolo. Il valore scritto nel campo FormulaValue di un record Formula ([MS-XLS] §2.4.127, layout in §2.5.133) è quello che la cella mostra in quel momento, che in modalità di calcolo manuale può essere vecchio di anni, ed Excel lo scrive comunque fedelmente. Il ricalcolo è un'operazione separata, con il suo trigger. HotXLS ora segue la stessa regola per i salvataggi classici: WriteFormula e WriteFormulaWithTExp chiamano prima TryGetCachedFormulaValue, prendono CacheInfo.Value quando lo stato è xlfcsLoaded o xlfcsCalculated, e ricadono su GetFormulaValue solo per xlfcsMissing e xlfcsInvalidated. La metà di questo contratto che sta sul lato lettura, incluso il significato di ogni stato e il motivo per cui un vuoto o un False in cache contano comunque come valore, è descritta in Leggere i valori in cache delle formule Excel in Delphi senza ricalcolo
Il percorso di fallback è tenuto di proposito, non rimosso. Una formula che hai assegnato in questa sessione tramite Cells[Row, Col].Formula arriva senza cache, e una formula che hai sostituito su una cella caricata viene marcata xlfcsInvalidated da _SetCompiledFormula; entrambe vengono valutate al salvataggio esattamente come prima, quindi un workbook generato si apre comunque in Excel con dentro i numeri. Quando nemmeno il valutatore riesce a produrre un valore, il writer emette un payload a zero e imposta fAlwaysCalc (grbit bit 0 di §2.4.127), così Excel ricalcola la cella all'apertura invece di fidarsi del segnaposto
procedure RoundTripWithoutRecalc(const Source, Target: string);
var
Book: TXLSWorkbook;
Before, After: TXLSFormulaCacheInfo;
begin
Book := TXLSWorkbook.Create;
try
Book.Open(Source);
// foglio, riga e colonna con base 1: R2C4 sul primo foglio
if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
raise Exception.Create('R2C4 carries no usable cache');
Book.SaveAs(Target); // nessun valutatore coinvolto per le celle in cache
finally
Book.Free;
end;
Book := TXLSWorkbook.Create;
try
Book.Open(Target);
Book.TryGetCachedFormulaValue(1, 2, 4, After);
// Before.Value = After.Value = 37 per nested-subtotals.xls
// Un salvataggio che ricalcolasse avrebbe scritto 67 qui
finally
Book.Free;
end;
end;Dove tiene il valore in cache la radice di una formula condivisa BIFF?
Nel proprio record Formula, come ogni altra cella con formula, ed è esattamente questo che rendeva la cella radice di un gruppo condiviso l'unico punto in cui il salvataggio cache-first perdeva ancora. Una formula condivisa in BIFF8 è memorizzata come record ShrFmla ([MS-XLS] §2.4.260) che segue il record Formula della cella in alto a sinistra, e ogni cella membro, radice compresa, porta un rgce formato da un unico token PtgExp (§2.5.198): il primo byte dell'espressione analizzata è $01, seguito dalla riga e dalla colonna della cella radice. Le celle follower sono autosufficienti: HotXLS legge il FormulaValue di ciascuna e risolve l'espressione cercando la formula compilata della radice. La cella radice è diversa, perché quando il suo record Formula viene analizzato l'espressione non esiste ancora; arriva un record più tardi
È in quel vuoto di un record che è finita la cache. TXLSReader.ParseFormula decodifica il valore in cache e, vedendo un PtgExp le cui coordinate coincidono con quelle della cella stessa, ricorda la cella in FSharedFormulaRow e FSharedFormulaCol e pubblica la cache sulla cella. Quando arriva il record ShrFmla ($04BC), ParseSharedFormula compila l'espressione e la installa con _SetCompiledFormula, e _SetCompiledFormula fa ciò che deve fare per qualsiasi modifica di formula: azzera FCachedFormulaValue e riporta lo stato a xlfcsMissing. Il 37 caricato della radice veniva quindi buttato via prima che qualcuno potesse leggerlo, TryGetCachedFormulaValue segnalava la radice come priva di cache, e il writer cache-first ricadeva diligentemente sul valutatore proprio per la cella che tutti stavano guardando. Il record Array (§2.4.4) condivide lo stesso ordinamento e aveva lo stesso buco
La correzione nella v2.382.3 aggiunge un terzo campo, FSharedFormulaCachedValue, accanto alle coordinate in sospeso della radice. ParseFormula vi mette da parte la cache decodificata quando riconosce una radice, e sia ParseSharedFormula sia ParseArrayFormula la riproducono tramite _SetCellCachedFormulaValue subito dopo aver installato l'espressione compilata, poi riportano il deposito a Unassigned. La variante String della cache non è toccata da tutto questo, perché il suo payload arriva in un record String separato ed è instradato per coordinate di cella, non per ordine dei record. Se lavori sul lato OOXML dello stesso concetto, l'articolo sull'espansione si delle formule condivise XLSX spiega perché il formato a pacchetto non ha un problema di ordinamento equivalente ma ha le sue trappole di espansione
Perché le follower di una formula condivisa hanno bisogno di uno scostamento relativo?
Perché l'espressione memorizzata in ShrFmla è scritta in forma relativa alla cella radice, e una follower che la riusa alla lettera valuta i riferimenti della radice invece dei propri. Il vecchio reader installava Value.GetCopy() su ogni follower, una copia profonda senza spostamento, quindi un gruppo con radice in B1 e =A1*3 dava a ogni follower =A1*3 anch'esso. Il salvataggio cache-first mascherava in effetti il problema per i file caricati, dato che le follower avevano un proprio FormulaValue e non avevano mai bisogno dell'espressione per salvarsi correttamente; è venuto fuori nel momento in cui qualcosa ha ricalcolato. Il reader ora installa TXLSCompiledFormula.GetCopy(row - srow, col - scol), che percorre l'albero sintattico e scosta ogni riferimento relativo della distanza della follower dalla radice, così la follower in B2 possiede un vero =A2*3
Il test di regressione che fissa entrambi i comportamenti vale la pena di leggerlo, perché si rifiuta di lasciar passare una coincidenza. Costruisce un workbook con =A1*3 e =A2*3 sugli input 2 e 4, poi inietta le cache volutamente sbagliate 999 e 888 tramite _SetCellCachedFormulaValue, una volta con UseSharedFormulas attivo e una con esso disattivato. Dopo un salvataggio e una ricarica, entrambe le celle devono ancora riportare 999 e 888 — prova che il salvataggio non ha toccato né la cache della radice né quella della follower. Solo dopo un Recalculate esplicito devono diventare 6 e 12, prova che l'espressione scostata della follower è corretta. Un test che seminasse i valori veri sarebbe passato anche con il vecchio writer, ed è tutto qui il senso di seminarne di sbagliati
var
Book: TXLSWorkbook;
Info: TXLSFormulaCacheInfo;
begin
Book := TXLSWorkbook.Create;
try
Book.Open('quarterly-model.xls');
Book.Sheets[1].Cells[1, 1].Value := 5; // cambia un input
// Le cache caricate delle formule dipendenti NON vengono invalidate da
// una modifica letterale, quindi un semplice SaveAs terrebbe i vecchi numeri.
// Chiedi un ricalcolo quando vuoi davvero risultati freschi:
Book.Recalculate;
if Book.TryGetCachedFormulaValue(1, 1, 2, Info) then
Writeln('B1 now ', VarToStr(Info.Value),
', state ordinal ', Ord(Info.State)); // xlfcsCalculated
Book.SaveAs('quarterly-model-updated.xls');
finally
Book.Free;
end;
end;Che cosa non fa per te il contratto cache-first
Il salvataggio cache-first conserva ciò che è stato caricato; non tiene traccia se ciò che è stato caricato sia ancora vero. Modificare un letterale da cui una formula dipende segna il grafo delle dipendenze come sporco per il valutatore, ma lascia in piedi la cache xlfcsLoaded della cella dipendente, e il writer classico scriverà volentieri quel valore obsoleto a meno che tu non chiami Recalculate o non legga prima il Value della cella, che lo calcola e porta lo stato a xlfcsCalculated. È lo stesso compromesso che fa Excel in modalità di calcolo manuale, ed è quello giusto per una pipeline che apre file di terzi, modifica qualche etichetta e salva: ma significa che un workbook che modifica gli input deve gestire il proprio passo di ricalcolo in modo esplicito. La politica RecalcBeforeSave del writer XLSX non è toccata da questo lavoro e ha una propria modalità manuale che conserva le cache nello stesso spirito. Ne derivano due confini più piccoli: il percorso cache-first aiuta solo le celle il cui stato è xlfcsLoaded o xlfcsCalculated; un generatore che scrive formule e non le valuta mai paga comunque una valutazione per cella al salvataggio, esattamente come prima. E la correzione dei subtotali annidati sistema quali celle il valutatore salta, non ogni funzione che il valutatore implementa: un file le cui formule HotXLS non sa calcolare in modo identico a Excel ora è sicuro da trattare in round trip senza toccarlo, ma un Recalculate deliberato su quel file produrrà comunque la risposta della libreria e non quella di Excel, e conviene confrontare le due prima di fidarsi di un salvataggio ricalcolato
I salvataggi classici cache-first, le cache ripristinate delle radici delle formule condivise e ad array, lo scostamento dei riferimenti relativi per le follower condivise e le regole corrette di annidamento di SUBTOTAL e AGGREGATE sono tutti inclusi nel componente spreadsheet HotXLS per Delphi standard per Delphi e C++Builder, senza alcuna dipendenza da Excel o da un server di automazione OLE; la pagina di prodotto contiene il riferimento API completo per il workbook, il lettore di cache e i punti di ingresso del ricalcolo usati qui