HotXLS può valutare una formula come =[Rates.xlsx]FX!B4*C10 rispetto a un'altra cartella di lavoro aperta in memoria, non rispetto a un valore in cache non aggiornato. Registrate la seconda cartella di lavoro per nome con RegisterExternalWorkbook, e ogni ricalcolo legge le celle referenziate direttamente da quell'oggetto. Deregistratela e la valutazione ricade sulla cache dei collegamenti esterni che il file già porta con sé
Questo colma una lacuna specifica e frustrante. Le cartelle di lavoro Excel si referenziano regolarmente a vicenda: una tabella dei tassi, un piano dei conti, un listino prezzi mantenuto da un altro team. Quando un file del genere viene aperto da una libreria anziché da Excel, quei riferimenti si risolvono normalmente in qualunque valore fosse in cache l'ultima volta che Excel ha ricalcolato, che può risalire a mesi prima, e nessuna quantità di ricalcoli lo risolve perché i dati semplicemente non ci sono
Come appare internamente un riferimento tra cartelle di lavoro?
Un riferimento esterno non viene memorizzato come il testo che vedete nella barra della formula. La cartella di lavoro mantiene una tabella dei libri di supporto, ogni token di formula punta a un indice in quella tabella, e una parte separata di valori in cache conserva l'ultimo risultato noto per ogni cella referenziata. Ecco perché un file aperto senza i suoi compagni mostra comunque dei numeri
L'analisi della forma testuale ha le sue sottigliezze. Il lexer tratta un nome di libro tra parentesi quadre come parte di un unico identificatore, così [Book.xlsx]Sheet!A1 diventa un solo token anziché una parentesi non bilanciata seguita da un riferimento a foglio. I nomi vengono poi normalizzati per la ricerca: la citazione tra apici, i prefissi di percorso, l'estensione del file e la forma a indice numerico collassano tutti in un nome base in maiuscolo, così 'C:\Rates\[Rates.xlsx]FX'!B4 e [rates.xlsx]FX!B4 si risolvono nello stesso libro registrato
Registrare una cartella di lavoro e ricalcolare
La registrazione avviene per nome, e il nome è quello che compare nelle formule della cartella di lavoro che effettua il riferimento:
uses
lxHandleX;
var
Main, Rates: TXLSXWorkbook;
begin
Main := TXLSXWorkbook.Create;
Rates := TXLSXWorkbook.Create;
try
if (Main.Open('valuation.xlsx') <> 1) or
(Rates.Open('rates.xlsx') <> 1) then
Exit;
// Le formule in valuation.xlsx che dicono [Rates.xlsx]... ora leggono
// dall'oggetto live invece che dai valori esterni in cache
Main.RegisterExternalWorkbook('Rates.xlsx', Rates);
Main.Recalculate;
Writeln(VarToStr(Main.Sheets[0].Cells[10, 3].Value));
// Cambia la sorgente e ricalcola di nuovo: il nuovo valore si propaga
Rates.Sheets[0].Cells[4, 2].Value := 1.0842;
Main.Recalculate;
Writeln(VarToStr(Main.Sheets[0].Cells[10, 3].Value));
finally
Main.UnregisterExternalWorkbook('Rates.xlsx');
Rates.Free;
Main.Free;
end;
end;La registrazione mantiene un riferimento debole. HotXLS non assume la proprietà della cartella di lavoro registrata e non la libererà, il che significa che il ciclo di vita spetta a voi gestirlo e l'ordine conta: deregistrate prima di liberare la cartella di lavoro sorgente, o quantomeno non ricalcolate dopo averla liberata
Cosa succede quando un libro non è registrato?
La valutazione ricade sulla cache dei collegamenti esterni memorizzata nel file, che è esattamente il comportamento di una cartella di lavoro aperta senza i suoi compagni. Questo fallback è il motivo per cui deregistrare è sicuro anziché distruttivo
Vale la pena descrivere con precisione il comportamento dopo la deregistrazione, perché sorprende le persone. Quando un riferimento non può essere risolto né da un libro registrato né dalla cache, la valutazione produce un errore e quell'errore non viene scritto nella cella. La cella mantiene il suo ultimo valore calcolato. Registrate di nuovo la sorgente e ricalcolate, e il valore si aggiorna nuovamente
Questa regola di conservazione in caso di fallimento è deliberata. L'alternativa, scrivere un valore di errore in ogni cella dipendente nel momento in cui una sorgente diventa non disponibile, trasformerebbe una tabella dei tassi temporaneamente mancante in una cartella di lavoro piena di marcatori di errore che un successivo ricalcolo riuscito deve riparare. Mantenere l'ultimo valore valido significa che una pipeline interrotta lascia un documento che si legge comunque in modo sensato
La propagazione dello stato dirty si ferma al confine della cartella di lavoro
Modificare una cella nella sorgente registrata non marca come dirty le celle dipendenti nella cartella di lavoro principale. Il ricalcolo incrementale traccia le dipendenze all'interno di una cartella di lavoro; non sorveglia oltre il confine. Excel si comporta allo stesso modo, motivo per cui i suoi riferimenti esterni si aggiornano all'apertura o a un aggiornamento esplicito piuttosto che in modo continuo
La conseguenza pratica è che cambiare un valore sorgente richiede un ricalcolo esplicito della cartella di lavoro che effettua il riferimento, come nell'esempio sopra. Se la vostra applicazione modifica entrambi i libri in un ciclo, ricalcolate una sola volta alla fine anziché dopo ogni modifica; il motore incrementale descritto in ricalcolo incrementale e grafo delle dipendenze esegue poi il minimo lavoro necessario all'interno di ciascuna cartella di lavoro
Tre scenari in cui questa funzionalità si adatta
Il consolidamento è quello ovvio. Una cartella di lavoro principale referenzia dodici file mensili; registrate tutti e dodici, ricalcolate una volta sola, e leggete le cifre consolidate senza mai aprire Excel. Il nome di registrazione per ciascuno deve corrispondere a ciò che le formule della cartella principale dicono realmente, cosa che vale la pena verificare rispetto al testo della formula anziché presumere che corrisponda al nome del file
L'analisi di scenario è il secondo. Registrate una sorgente, scrivete un set di ipotesi al suo interno, ricalcolate il modello dipendente, registrate i risultati, poi ripetete. Poiché la sorgente è un oggetto live, nessun file viene scritto tra un'iterazione e l'altra, e cento scenari costano cento ricalcoli invece di cento cicli di salvataggio e ricaricamento
La validazione prima della consegna è il terzo e meno ovvio. Aprite una cartella di lavoro, registrate i suoi compagni, ricalcolate, e confrontate i risultati con i valori che il file già portava con sé. Una discrepanza significa che i valori in cache sono obsoleti, il che è un difetto che vale la pena scoprire prima che un cliente riceva il file. Questo confronto è un naturale complemento al confronto tra cartelle di lavoro descritto in confrontare due cartelle di lavoro Excel
Igiene dei riferimenti prima di affidarvici
Eseguite prima un passaggio di analisi delle formule. Riporta funzioni mancanti, riferimenti esterni e componenti di dipendenza circolare in un solo attraversamento, il che vi dice esattamente quali nomi di libro un documento si aspetta prima di andare a cercare i file:
var
Issues: TArray<TXLSXFormulaIssue>;
I: Integer;
begin
Issues := Main.AnalyzeFormulaIssues;
for I := 0 to High(Issues) do
if Issues[I].Kind = xlsxFormulaIssueExternalReference then
Writeln(Format('sheet %d R%dC%d needs [%s] (%s)',
[Issues[I].Sheet, Issues[I].Row, Issues[I].Col,
Issues[I].ExternalWorkbook, Issues[I].SourceFileName]));
end;Altre due abitudini fanno risparmiare tempo. Mantenete il nome di registrazione canonico — registrate con il nome file semplice e lasciate che la normalizzazione gestisca le varianti tra apici e con prefisso di percorso nelle formule. E trattate una cartella di lavoro che referenzia file che non potete fornire come un artefatto di sola lettura: ricalcolarla preserverà i valori in cache, ma qualsiasi modifica che cambia una cella dipendente lascia il documento in uno stato in cui alcuni valori sono aggiornati e altri storici, senza nulla nel file che indichi quale sia quale. Copiare formule tra cartelle di lavoro è un'operazione diversa con proprie regole di ri-collegamento, trattata in copia tra cartelle di lavoro e ri-collegamento delle formule, e gli intervalli con nome aggiungono un ulteriore livello descritto in nomi definiti e formule tra fogli
La valutazione live tra cartelle di lavoro, il ricalcolo incrementale e l'analisi delle formule girano sullo stesso motore per Delphi e C++Builder; l'elenco completo delle funzionalità è disponibile sulla pagina del componente foglio di calcolo HotXLS Delphi