Un totale complessivo che Excel mostra come 37 può tornare 67 dopo che un programma ha aperto il file .xls e lo ha salvato senza modificare una sola cella. I 30 in più non sono casuali. Sono esattamente la somma dei due subtotali di gruppo, 10 e 20, che stanno dentro il range coperto dal totale complessivo. Devono andare storte due cose insieme perché quel numero si muova: il salvataggio deve ricalcolare ogni formula in uscita, e chiunque ricalcoli deve dimenticare che una SUBTOTAL salta le altre celle SUBTOTAL. Eliminatene una delle due e il file se la passa, ed è per questo che il primo problema può restare non notato dietro il secondo
Perché salvare un file XLS cambia il valore di una formula?
Un salvataggio cambia il valore di una formula quando il writer calcola il numero che memorizza accanto a ogni formula invece di copiare quello caricato. Ogni record Formula BIFF8 porta un campo FormulaValue di otto byte ([MS-XLS] §2.4.127, layout del valore in §2.5.133) che contiene l'ultimo risultato che Excel ha mostrato per quella cella. Un writer deve metterci qualcosa in quei otto byte, e il percorso di codice più corto è consegnare ogni formula al motore di calcolo. Da quel momento ogni salvataggio è un ricalcolo completo che nessuno ha chiesto, e ogni punto in cui quel motore e Excel sono in disaccordo diventa un numero cambiato in un file che l'utente crede intatto
Il disaccordo sulla SUBTOTAL fa un buon esempio, perché la regola del foglio di calcolo è semplice e la trappola sta nella tipizzazione Variant di Delphi. Excel definisce ogni numero di funzione SUBTOTAL come ignorante delle celle la cui formula è a sua volta una SUBTOTAL. Per un foglio le cui righe di dettaglio sommano a 37 (le righe sotto due gruppi che fanno un subtotale di 10 e 20, più 7 fuori da ogni gruppo), il totale complessivo è 37. Un motore che non becca i subtotali annidati conta di nuovo il 10 e il 20 e arriva a 67. Beccarli di solito significa un breve controllo sul testo della formula, e la versione Delphi ovvia compila, gira e non trova mai nulla:
// Un helper che scrivete voi, per esempio quando scansionate un foglio
// in cerca delle formule che una SUBTOTAL racchiudente deve saltare
function IsNestedSubtotal(const F: Variant): Boolean;
begin
// Il test allettante non riesce mai su un Delphi Unicode: una String
// assegnata a un Variant è varUString, non varOleStr
// Result := (VarType(F) = varOleStr) and ...
Result := False;
if VarIsStr(F) then // accetta varString, varOleStr e varUString
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(');
end;
// Uso con HotXLS (fogli, righe e colonne sono 1-based):
// if IsNestedSubtotal(Book.Sheets[1].Cells[12, 4].Formula) then ...VarIsStr è il test che l'evaluator di HotXLS applica alla stessa domanda, e tratta anche le celle AGGREGATE come escluse da un subtotale racchiudente, come fa Excel. Sistemare il predicato non è tutta la storia però. Con un controllo corretto, il salvataggio che ricalcola scrive 37 solo perché il motore per caso concorda con Excel su questo particolare file. La prossima formula che il motore tratta in modo leggermente diverso si muoverà di nuovo. Le regole su quali celle SUBTOTAL e AGGREGATE saltano, righe nascoste incluse, sono trattate nell'articolo sulle righe nascoste di SUBTOTAL e AGGREGATE. Con il salvataggio cache-first, un file di cui non avete chiesto il calcolo torna su disco con 37 nella cella, qualunque cosa il motore ne avrebbe fatto
Che cosa un salvataggio XLS classico scrive in ogni record Formula
HotXLS salva un workbook BIFF8 partendo dalla cache: per ogni cella formula, TXLSWorkbook.SaveAs scrive il valore in cache ogniqualvolta TryGetCachedFormulaValue ne riferirebbe uno, e fa girare l'evaluator solo quando non ne riferirebbe. A decidere è lo stato della cache. Una cella in stato xlfcsLoaded (il valore veniva dal file sorgente) o xlfcsCalculated (HotXLS l'ha calcolato in questa sessione) ha il suo CacheInfo.Value scritto byte per byte. Una cella in stato xlfcsMissing o xlfcsInvalidated viene valutata al momento del salvataggio. Questo rispecchia come tratta Excel un salvataggio: uno snapshot di ciò che ogni cella mostra, che in modalità di calcolo manuale può essere molto indietro, scritto fedelmente comunque
Il percorso di valutazione resta per buone ragioni. Una formula assegnata in codice tramite Cells[Row, Col].Formula non ha ancora cache, e assegnare una nuova formula a una cella caricata la sposta in xlfcsInvalidated, perché il vecchio risultato appartiene alla vecchia espressione. Entrambe vengono calcolate durante il salvataggio, così un workbook generato da zero si apre in Excel mostrando numeri calcolati invece di zeri. Se persino l'evaluator non riesce a produrre un valore (una funzione non supportata, un collegamento esterno rotto), il writer memorizza uno zero e imposta fAlwaysCalc, bit 0 dei flag del record in §2.4.127, così Excel ricalcola la cella all'apertura invece di fidarsi del segnaposto
Una proprietà sembra collegata e non lo è. TXLSWorkbook.RecalcOnSave rispecchia l'opzione "Recalculate workbook before saving" di Excel dal record CalcSaveRecalc ([MS-XLS] §2.4.37). Interessa solo Excel, solo in modalità di calcolo manuale, e HotXLS la scrive semplicemente nel file. SaveAs non la consulta quando decide cosa mettere in FormulaValue. Il round trip qui sotto è il modo più rapido di vedere il contratto su uno dei vostri file:
procedure CheckRoundTrip(const Source, Target: string;
Row, Col: Integer);
var
Book: TXLSWorkbook;
Before, After: TXLSFormulaCacheInfo;
begin
Book := TXLSWorkbook.Create;
try
if Book.Open(Source) <> 1 then
raise Exception.Create('Cannot open ' + Source);
// Foglio, riga e colonna sono 1-based
if not Book.TryGetCachedFormulaValue(1, Row, Col, Before) then
raise Exception.Create('Cell carries no usable cached value');
if Book.SaveAs(Target) <> 1 then // le celle in cache vengono scritte come memorizzate
raise Exception.Create('Save failed');
finally
Book.Free;
end;
Book := TXLSWorkbook.Create;
try
Book.Open(Target);
Book.TryGetCachedFormulaValue(1, Row, Col, After);
// Atteso: After.Value uguale a Before.Value, e After.State di nuovo
// xlfcsLoaded, perché viene dal file salvato
Writeln(VarToStr(Before.Value), ' -> ', VarToStr(After.Value));
finally
Book.Free;
end;
end;Il lato lettura di questo contratto, incluso il significato di ogni stato e perché un blank o un False in cache contano comunque come valore, è descritto in Leggere i valori di formula in cache di Excel in Delphi senza ricalcolo. Eseguite il controllo qui sopra sulla cella del totale complessivo di un workbook con subtotali annidati ed entrambe le righe stampano lo stesso numero
Dove tiene il valore in cache la radice di una formula condivisa BIFF?
Nel suo stesso record Formula, come ogni altra cella formula, ma l'espressione arriva un record più tardi, e quel vuoto è dove un reader distratto perde la cache. BIFF8 memorizza una formula condivisa come un record ShrFmla ([MS-XLS] §2.4.260) piazzato subito dopo il record Formula della cella in alto a sinistra del gruppo. Ogni cella membro, radice inclusa, porta un rgce fatto di un singolo token PtgExp (§2.5.198): il byte $01 seguito da riga e colonna della radice. Le celle follower sono facili, perché quando compaiono l'espressione della radice esiste già e il loro FormulaValue sta nel loro stesso record. La radice non è facile. Quando il suo record Formula viene letto, ha un valore in cache di 37 e ancora nessuna espressione
Installare un'espressione su una cella ne azzera la cache, e questo deve restare vero, perché una formula sostituita rende davvero privo di senso il vecchio risultato. Applicata alla cieca al record ShrFmla però, la stessa regola butta via il 37 della radice nel momento in cui l'espressione arriva, TryGetCachedFormulaValue riferisce la radice come xlfcsMissing, e il salvataggio ricade sull'evaluator esattamente per la cella che un utente è più portato a guardare. Il reader di HotXLS trattiene il valore decodificato della radice mentre aspetta il record ShrFmla, installa l'espressione, poi rimette il valore caricato con stato xlfcsLoaded. Le formule array (il record Array, §2.4.4) seguono lo stesso ordine di record e ricevono lo stesso trattamento. La forma String della cache non ha mai avuto questo problema, perché il suo testo arriva in un record String separato e viene abbinato alla cella per riga e colonna, così la sua posizione nel flusso non conta mai
Se trattate anche il lato OOXML, l'articolo sull'espansione si delle formule condivise XLSX spiega perché il formato pacchetto non ha il vuoto di ordine dei record ma porta con sé le sue trappole di espansione
Le follower di una formula condivisa possiedono una copia spostata dell'espressione
L'espressione in un record ShrFmla è scritta relativa alla cella radice, quindi ogni follower ha bisogno di una propria copia spostata della sua distanza dalla radice. Copiatela alla lettera e un gruppo con radice in B1 con =A1*3 consegna la stessa =A1*3 a B2 e B3. HotXLS dà a ogni follower una copia i cui riferimenti relativi sono scostati della distanza di riga e colonna della follower, così B2 possiede una vera =A2*3 e B3 possiede =A3*3
È anche per questo che un semplice round trip non può dirvi se le espressioni delle follower sono giuste. Le follower conservano i loro valori caricati attraverso un salvataggio, quindi una copia alla lettera passerebbe. Un controllo che provi qualcosa deve cambiare un input e ricalcolare. La routine qui sotto scrive un gruppo condiviso con UseSharedFormulas attivo (il valore predefinito è False, che scrive un record Formula per cella), riapre il file, modifica A2 e osserva B2:
procedure CheckSharedFollower(const FileName: string);
var
Book: TXLSWorkbook;
Sheet: TXLSWorksheet;
Info: TXLSFormulaCacheInfo;
Row: Integer;
begin
Book := TXLSWorkbook.Create;
try
Sheet := Book.Sheets.Add;
for Row := 1 to 3 do
begin
Sheet.Cells[Row, 1].Value := Row * 2; // 2, 4, 6
Sheet.Cells[Row, 2].Formula := '=A' + IntToStr(Row) + '*3';
end;
Book.UseSharedFormulas := True; // B1:B3 diventano un gruppo ShrFmla
Book.SaveAs(FileName); // nessuna cache: valutate a 6, 12, 18
finally
Book.Free;
end;
Book := TXLSWorkbook.Create;
try
Book.Open(FileName);
Book.Sheets[1].Cells[2, 1].Value := 10; // modificare l'input della follower
Book.TryGetCachedFormulaValue(1, 2, 2, Info);
Writeln('B2 before: ', VarToStr(Info.Value)); // Atteso: 12, ancora caricato
Book.Recalculate;
Book.TryGetCachedFormulaValue(1, 2, 2, Info);
Writeln('B2 after: ', VarToStr(Info.Value)); // Atteso: 30, da =A2*3
finally
Book.Free;
end;
end;Una follower che avesse riusato =A1*3 stamperebbe 6 alla seconda riga. La prima riga conta quanto quella: mostra che modificare un letterale lascia al suo posto il valore caricato della formula dipendente finché qualcosa non ricalcola
Che cosa il salvataggio cache-first lascia a voi
Il salvataggio cache-first conserva ciò che è stato caricato. Non traccia se ciò che è stato caricato sia ancora vero. Modificare una cella da cui una formula dipende marca il grafo delle dipendenze per il prossimo ricalcolo, ma la cella dipendente conserva il suo valore xlfcsLoaded, e SaveAs scrive quel numero stantio a meno che chiamiate prima Recalculate o leggiate la Value della cella, il che calcola la formula e la sposta in xlfcsCalculated. Recalculate restituisce il numero di celle formula che non è riuscito a valutare, quindi un risultato diverso da zero vale la pena di essere loggato prima del salvataggio
Nella pipeline comune un programma apre un workbook costruito da qualcun altro, riempie qualche etichetta o un'intestazione, e salva. Nulla in quel file dovrebbe cambiare valore, e con il salvataggio cache-first nulla cambia. Un programma che modifica gli input possiede invece il passo di ricalcolo, e quel passo sta in una sola chiamata Recalculate visibile accanto alle modifiche, dove chiunque legga il codice può vederla. Excel fa lo stesso patto in modalità di calcolo manuale. Il writer XLSX ha la sua politica, RecalcBeforeSave con le modalità xrmManual, xrmIfDirty e xrmAlways, impostata sulla classe del workbook XLSX e indipendente da tutto quanto sopra
Dal progetto discendono due confini. Il percorso cache aiuta solo le celle in stato xlfcsLoaded o xlfcsCalculated, quindi un generatore che scrive migliaia di formule nuove senza mai valutarle paga comunque una valutazione per cella durante il salvataggio. E il contratto governa ciò che un salvataggio scrive, non ciò che un ricalcolo esplicito calcola: un file intatto conserva i numeri che Excel ha memorizzato, mentre un Recalculate scrive i risultati dell'evaluator di HotXLS. Confrontate i due su qualche workbook rappresentativo prima di commutare una pipeline sui salvataggi ricalcolati
L'HotXLS Delphi Spreadsheet Component legge e scrive workbook BIFF8 classici e XLSX da Delphi e C++Builder senza Excel o automazione OLE installati. La sua pagina di prodotto documenta le chiamate su workbook, valori in cache e ricalcolo usate in questi esempi