Il metodo AddCopy di HotXLS copia un foglio di lavoro da una cartella di lavoro Excel a un'altra decompilando ogni formula su quel foglio in testo in stile A1 e ricompilando il testo dentro la cartella di lavoro di destinazione, invece di copiare direttamente l'albero di formula compilato, perché i riferimenti delle serie dei grafici, gli indici di font del rich text e la numerazione dei link esterni sono tutti assegnati in modo indipendente dentro ogni file di cartella di lavoro
Il fallimento emerge esattamente nella cartella di lavoro che ti aspetteresti: un job di fine mese che estrae un foglio da ogni report di filiale e lo appende a un file di riepilogo. Apri il risultato e un grafico di subtotale traccia i numeri di una filiale completamente diversa, una nota che nel sorgente era grassetto e rossa torna testo nero semplice, e una formula che una volta prelevava un'aliquota fiscale da una cartella di lavoro di lookup collegata ora mostra un numero congelato che nessuno riesce a spiegare. Qui nulla solleva un'eccezione — il file si apre, i numeri sembrano plausibili, e il danno resta lì finché qualcuno non nota un grafico con il titolo sbagliato accanto
Perché AddCopy non può semplicemente copiare l'albero di formula compilato?
AddCopy non può spostare invariato l'albero di formula compilato, perché una formula BIFF compilata non è testo autonomo — è una sequenza di token, e diversi di quei token sono piccoli interi che si risolvono correttamente solo dentro la cartella di lavoro che li ha prodotti. Un riferimento 3D come Sheet2!A1:A10 non porta il nome letterale Sheet2 una volta compilato; porta un campo che la specifica BIFF chiama ixti (HotXLS mantiene lo stesso valore nel proprio albero compilato sotto il nome di campo FExternID), un indice nella tabella privata EXTERNSHEET di quella cartella di lavoro, numerato in qualunque ordine quella particolare cartella di lavoro abbia registrato i propri fogli. Sposta il token invariato in una cartella di lavoro la cui tabella EXTERNSHEET è stata costruita in un ordine diverso e l'indice 3 non significa più Sheet2 — significa qualunque foglio occupi lo slot 3 di là, ed Excel non ha modo di segnalare l'errore, perché per quanto riguarda il formato file la formula è perfettamente ben formata. Questo è esattamente il fallimento che TXLSWorksheets.AddCopy esiste per evitare: chiamato dalla collezione di fogli propria di una delle due cartelle di lavoro in codice Delphi o C++Builder, copia un foglio di lavoro — valori delle celle, formati, formule, grafici, commenti, unioni, impostazione pagina e altro — da una cartella di lavoro sorgente che può o meno essere quella su cui lo stai chiamando, e appende il risultato alla destinazione sotto un nome a tua scelta o una copia disambiguata dell'originale
var
Summary, Branch: IXLSWorkbook; // interface-counted: do not Free
begin
Summary := TXLSWorkbook.Create;
Branch := TXLSWorkbook.Create;
Branch.Open('branch-east.xls');
// Appends a copy of Branch's first sheet onto Summary, renamed to
// stay unique inside the destination workbook
Summary.Sheets.AddCopy(Branch.Sheets[1], 'East Detail');
Summary.SaveAs('consolidated.xls');
end;La soluzione: decompilare in testo, ricompilare nella destinazione
HotXLS risolve il problema dell'indicizzazione non lasciando mai che l'albero compilato stesso attraversi il confine tra cartelle di lavoro. Per ogni cella con formula in una copia tra cartelle di lavoro, AddCopy decompila la formula sorgente nello stesso testo in stile A1 che un utente vedrebbe nella barra della formula di Excel, quindi passa quel testo alla cartella di lavoro di destinazione, che lo analizza di nuovo in un albero usando le proprie tabelle da zero — un riferimento qualificato per foglio come Data!D2:D100 a quel punto è solo una stringa, e una stringa significa la stessa cosa in qualunque cartella di lavoro, quindi se la destinazione ha già un foglio chiamato Data il riferimento si risolve correttamente senza alcuna traduzione di indice, perché non c'era mai un indice grezzo in transito da tradurre. HotXLS paga per questo andata-e-ritorno solo quando deve: copiare un foglio dentro la stessa cartella di lavoro segue un percorso più economico dove l'albero compilato viene semplicemente duplicato in memoria, poiché ogni indice al suo interno è già valido dove resta, e la deviazione tramite testo viene eseguita solo una volta che AddCopy rileva che sorgente e destinazione sono realmente istanze di cartella di lavoro diverse. Vale la pena essere precisi anche su cosa questa riscrittura non sia. Non ha nulla a che fare con lo spostamento di righe e colonne che avviene quando inserisci o elimini righe dentro un singolo foglio, trattato in dettaglio in un articolo di approfondimento — quel motore riscrive il testo A1 sul posto per tracciare celle che si sono spostate di qualche riga su o giù dentro una cartella di lavoro, mentre questo viene eseguito quando una formula lascia del tutto la cartella di lavoro che l'ha compilata, dove le righe spostate non sono il problema e la numerazione privata della cartella di lavoro lo è
// Conceptually, this is what AddCopy does for each formula cell: turn
// the compiled tree back into text using the source workbook's own
// tables, then let the destination workbook parse that text back into
// a tree using its own tables, from scratch
FormulaText := SourceBook.GetUnCompiledFormula(SourceFormula, Row, Col, SourceSheetID);
DestFormula := DestBook.GetCompiledFormula(FormulaText, DestSheetID);Cosa succede se la destinazione non ha ancora quel foglio, o quel nome?
La ricompilazione di AddCopy riesce solo quando la cartella di lavoro di destinazione ha già tutto ciò a cui il testo della formula fa riferimento, e le due lacune che si presentano in pratica sono un foglio con lo stesso nome che non è stato ancora copiato in questo batch, e un nome definito a livello di cartella di lavoro che non è mai esistito nella destinazione. HotXLS non solleva un'eccezione quando la ricompilazione fallisce a metà di una copia di foglio — l'assegnazione Value della cella memorizza invece silenziosamente il testo della formula come una semplice stringa, una modalità di fallimento deliberata e ispezionabile piuttosto che silenziosa, poiché una cella con formula che inaspettatamente mostra testo letterale come =SUM(Q1!B2:B12) invece di un numero calcolato è l'indizio che qualcosa a monte nella copia non si è risolto. Prima di rinunciare, AddCopy tenta una riparazione: percorre l'albero sintattico della formula fallita raccogliendo ogni ID di nome definito che la formula tocca, e per ogni nome a livello di cartella di lavoro che esiste nella sorgente ma non ancora nella destinazione, copia il nome e ricompila lo stesso testo una seconda volta. I nomi a livello di foglio restano fuori da ciò che questa riparazione può risolvere, poiché un nome visibile solo alle formule su un foglio della cartella di lavoro sorgente non ha uno slot equivalente in cui migrare, e una destinazione che possiede già un nome con la stessa grafia viene lasciata intatta anziché sovrascritta, sul presupposto che un nome che il chiamante ha deliberatamente pre-creato sia quello che vuole vedere rispettato. Dentro una singola cartella di lavoro, la ricerca del nome di una formula cross-foglio risale automaticamente dall'ambito del foglio a quello della cartella di lavoro, il meccanismo trattato nell'articolo di HotXLS su nomi definiti e formule cross-foglio; attraversare un vero confine tra cartelle di lavoro rimuove del tutto quella rete di sicurezza, e un nome deve essere deliberatamente portato attraverso oppure la formula che ne dipende degrada a testo
I riferimenti delle serie dei grafici richiedono la stessa correzione, ma un percorso di codice diverso
Una serie di grafico HotXLS che traccia un intervallo di celle incontra esattamente lo stesso problema di numerazione di una normale formula di cella, perché anche il riferimento all'intervallo dati di un grafico è un flusso di token di formula compilato — la specifica BIFF chiama il record che lo porta BRAI ([MS-XLS] sezione 2.4.51) — ma AddCopy non può risolverlo riutilizzando il normale percorso di caricamento dei grafici, perché quel percorso è esattamente ciò che crea il bug. Quando un record di grafico viene analizzato dal disco nel normale corso dell'apertura di un file, il suo albero di formula viene costruito traducendo i byte grezzi attraverso qualunque istanza calcolatrice stia effettuando l'analisi; passa invece i byte BRAI grezzi di un grafico sorgente attraverso il normale caricatore di record proprio della cartella di lavoro di destinazione, e l'ixti incorporato in quei byte viene risolto contro la tabella EXTERNSHEET della destinazione, quindi la serie punta silenziosamente a qualunque foglio occupi quello slot di là — la stessa classe di errore della copia invariata dell'albero compilato di una cella, solo più difficile da notare perché nessuno legge le formule delle serie dei grafici come legge le formule delle celle. HotXLS evita la trappola con un percorso di clonazione dedicato invece: TXLSCustomChart.AssignFrom copia i byte di intestazione non-formula propri di ogni record di grafico alla lettera, poi ricostruisce l'intervallo collegato tramite la stessa primitiva di decompilazione-e-ricompilazione usata per le celle ordinarie, cosicché il nuovo albero venga costruito contro la tabella EXTERNSHEET della destinazione da zero invece che reinterpretato contro di essa a posteriori
Lo stesso problema di numerazione, un indice di font alla volta
Non ogni numero locale alla cartella di lavoro dentro un grafico o una cella con rich text è una formula, e un indice di font è la stessa classe di problema in miniatura. I run di rich text, insieme ad altri due tipi di record di grafico che portano un font di didascalia o di asse, memorizzano un riferimento a font come un intero grezzo indice nella tabella dei font propria della cartella di lavoro proprietaria, e quell'indice non significa nulla nella tabella di una cartella di lavoro diversa — potrebbe altrettanto facilmente puntare a un carattere tipografico, dimensione o colore completamente diverso di là. HotXLS risolve questo per valore anziché per numero: cerca gli attributi di font effettivi a quell'indice nella tabella sorgente, trova o crea una voce corrispondente nella tabella dei font della destinazione, e riscrive l'indice memorizzato per puntare a quel nuovo slot. Una particolarità del formato rende la ricerca stessa delicata — l'indice su file salta lo slot 4, un vuoto di numerazione che [MS-XLS] sezione 2.5.339 documenta, quindi il codice deve spostare l'indice indietro di uno prima di confrontare i font e in avanti di uno prima di scrivere il risultato
// The file-numbered font index skips slot 4 (MS-XLS section 2.5.339);
// shift into the in-memory slot, migrate the font by value if the
// destination differs, then shift back before writing the result
if Ifnt >= 5 then
Dec(Ifnt);
if DestFonts.Key[Ifnt] <> SourceFonts.Key[Ifnt] then
Ifnt := DestFonts.SetKey(0, SourceFonts.Key[Ifnt]);
if Ifnt >= 4 then
Inc(Ifnt);Cosa succede a una formula che già punta al di fuori della cartella di lavoro?
Una formula che raggiunge una terza cartella di lavoro prima ancora che tu chiami AddCopy è l'unico caso che l'andata-e-ritorno tramite testo non può portare, perché il decompilatore da formula a testo proprio di HotXLS deliberatamente non sintetizza il testo tra parentesi [Book]Sheet! per un riferimento esterno, e il compilatore dall'altra parte non accetta nemmeno quella sintassi come input — quindi questo unico caso passa attraverso un secondo meccanismo che non tocca mai il testo. Quando la riparazione tramite migrazione dei nomi descritta sopra lascia comunque una cella come stringa, e la cartella di lavoro sorgente ha un vero nome file, AddCopy cambia strategia: copia in profondità l'albero di formula compilato stesso anziché il suo testo, quindi passa la copia a un passaggio di riassociazione dedicato, RebindExternRefsInTree, che lo percorre nodo per nodo. Per ogni riferimento a intervallo che trova, quel passaggio risolve la voce EXTERNSHEET della sorgente restituendola come una coppia di nomi di foglio, e registra, o riutilizza, una voce equivalente nelle tabelle di riferimento esterno proprie della destinazione, creando un nuovo link a cartella di lavoro esterna se la destinazione non ha mai fatto riferimento a quel file sorgente prima
È qui che il problema di numerazione locale alla cartella di lavoro è più letterale, perché un token di riferimento esterno raggruppa tre coordinate separate in un unico campo e ognuna di esse è privata della cartella di lavoro che l'ha scritta: quale cartella di lavoro esterna, uno slot nell'elenco proprio della destinazione di cartelle di lavoro esterne assegnato in qualunque ordine quella cartella di lavoro le abbia registrate; quale foglio dentro l'elenco fogli proprio di quella cartella di lavoro esterna, memorizzato come un indice a base 1 con ambito specifico al libro esterno, un dominio di numerazione interamente diverso dagli ID di foglio interni propri della destinazione; e l'intervallo di celle stesso, semplici coordinate di riga e colonna che non necessitano di alcuna traduzione perché non erano mai relative alla cartella di lavoro in primo luogo. Sbaglia uno dei primi due ed Excel apre comunque il file, mostra comunque una formula, e la valuta contro le celle esterne sbagliate senza protestare. Un tipo di nodo sconfigge persino questa riassociazione a livello di albero: un riferimento a un nome definito, un indice nella tabella dei nomi privata della propria cartella di lavoro esattamente come un indice di foglio è privato del proprio EXTERNSHEET, senza alcuna riparazione equivalente a livello di albero disponibile — nel momento in cui il percorso di riassociazione incontra un riferimento a nome in qualunque punto dell'albero, abbandona l'intera formula invece di scriverne una parzialmente corretta. Anche quando la riassociazione riesce, la cella di destinazione non mostra un numero appena ricalcolato; mostra il valore che la cella sorgente già deteneva al momento della copia, mantenuto in uno slot di cache nello stesso modo in cui Excel stesso mette in cache l'ultimo valore noto di qualsiasi riferimento esterno finché non aggiorni esplicitamente i collegamenti, che è il comportamento predefinito corretto, poiché ricalcolare attraverso un collegamento vivo verso un altro file è esattamente il tipo di operazione che vuoi scatenare una volta, deliberatamente, non a ogni apertura
Cosa costa questo design
Il meccanismo di decompilazione-e-ricompilazione di AddCopy non è gratuito, e il costo vale la pena pianificarlo prima, non dopo, di scriptare un grande job di consolidamento. Copiare un foglio dentro la stessa cartella di lavoro segue il percorso economico, una diretta duplicazione in memoria dell'albero compilato, perché ogni indice al suo interno è già valido nella cartella di lavoro in cui resta; una copia tra cartelle di lavoro paga invece per una vera analisi su ogni cella con formula, decompila in testo e poi compila di nuovo quel testo da zero, e mentre la differenza non vale la pena misurarla su un foglio con qualche dozzina di formule, una cartella di lavoro sorgente con decine di migliaia di celle con formula, copiata come un foglio tra dozzine in un job batch, dovrebbe aspettarsi che la ricompilazione domini il tempo di esecuzione piuttosto che l'I/O sul file circostante. L'ordine di copia conta per un secondo motivo oltre alla velocità: una formula che fa riferimento a un foglio che AddCopy non ha ancora raggiunto in questo batch fallisce la propria ricompilazione per lo stesso motivo per cui lo fa una formula che fa riferimento a un foglio genuinamente inesistente, quindi un job che copia il foglio B prima del foglio A da cui la sua formula dipende vedrà quella formula degradare esattamente come descritto sopra, testo stringa o un fallback a link esterno che punta proprio indietro al file sorgente da cui è appena arrivata. E poiché ogni cartella di lavoro sorgente in un batch di consolidamento è di solito creata in modo indipendente, vale la pena testare esplicitamente l'unica modalità di fallimento di cui nessun singolo file sorgente avrebbe mai potuto avvertirti — cinque cartelle di lavoro di filiale che ciascuna totalizza i numeri di una filiale pari possono combinarsi in un vero riferimento circolare dentro la cartella di lavoro di riepilogo senza che nessun singolo file sorgente ne contenga mai uno, un ciclo che esiste solo una volta che ogni foglio è atterrato nello stesso posto e il ricalcolo viene eseguito sull'insieme combinato
La copia di fogli di lavoro tra cartelle di lavoro è comportamento standard di AddCopy nel componente Excel Delphi HotXLS per Delphi e C++Builder; la pagina del prodotto porta il riferimento API completo per fogli di lavoro e cartelle di lavoro, incluso il comportamento su grafici, rich text e riferimenti esterni descritto qui