Un nome definito è un'etichetta che sta al posto di una costante, di un intervallo di celle o di un'espressione di formula, memorizzata una sola volta nella cartella di lavoro e richiamata simbolicamente ovunque serva. Scrivete TaxRate in una formula e il motore lo risolve in ciò che la definizione del nome contiene, sia il letterale 0.08 sia l'intervallo Data!$A$2:$D$100. Un riferimento fra fogli è l'idea ortogonale: Data!D2 raggiunge una cella su un altro foglio qualificando l'indirizzo con un nome di foglio. Mettete insieme le due cose e un foglio di riepilogo può sommare un foglio di dettaglio attraverso un nome che non menziona mai un indirizzo letterale, che è esattamente quello che volete in una cartella di lavoro che un generatore assembla e che un contabile poi verifica
HotXLS, la libreria Delphi nativa di losLab per i file XLS e XLSX, espone la tabella dei nomi di entrambi i formati con accesso in creazione, ricerca ed eliminazione, più un motore di formule che risolve nomi e riferimenti fra fogli nel processo. I due formati mantengono gerarchie di classi separate, e le differenze fra le loro API dei nomi sono la parte che fa inciampare il codice portato dall'uno all'altro
Due archivi di nomi che non condividono un'interfaccia
Sul lato XLS, TXLSWorkbook.GetNames restituisce una collezione IXLSNames il cui overload Add(Name, RefersTo, Visible) scrive un nome nella tabella dei nomi BIFF. Le singole voci tornano come oggetti IXLSName che portano Name, RefersTo, un RefersToRange risolto e un metodo Delete. Sul lato XLSX, TXLSXWorkbook.DefinedNames è una collezione TXLSXDefinedNames con Add, FindByName e DeleteByName
Le convenzioni di ricerca divergono in un modo che emerge durante il porting anziché in compilazione. La proprietà predefinita Item della collezione XLS accetta un Variant, quindi sia Names[0] sia Names['TaxRate'] si risolvono su di essa. La collezione XLSX non ha una proprietà predefinita di quel tipo; chiamate FindByName('TaxRate'), che restituisce nil quando il nome non esiste. Il codice scritto per una facciata compila contro l'altra solo per caso, e il guasto tende a mostrarsi come accesso a nil a runtime invece che come sottolineatura rossa nell'IDE
L'ambito è la prima decisione, non un flag da aggiungere dopo
Un nome definito ha ambito di cartella di lavoro, visibile alle formule di ogni foglio, oppure ambito di foglio, visibile solo alle formule del foglio che lo possiede. Nella API XLSX la distinzione è un unico parametro opzionale. DefinedNames.Add(AName, AFormula) crea un nome a livello di cartella di lavoro, mentre Add(AName, AFormula, ASheetIndex) lo lega a un solo foglio. Rileggendolo, TXLSXDefinedName.SheetIndex restituisce -1 per l'ambito di cartella e altrimenti l'indice di foglio in base 0
L'ambito funziona anche da politica sulle collisioni, ed è la ragione per stabilirlo prima di scrivere il primo nome. Excel permette un Total locale su ogni foglio più un Total a livello di cartella, e una formula su un dato foglio risolve prima quello locale. Le cartelle di lavoro generate dovrebbero appoggiarsi a questo di proposito. Le ipotesi di business che diversi fogli consumano, come le aliquote fiscali, i tassi di cambio e il periodo di rendicontazione, appartengono all'ambito di cartella. Gli intervalli di appoggio che solo le formule di un foglio richiamano stanno più al sicuro con ambito di foglio, dove nulla può oscurarli e loro non possono oscurare nulla
var
Book: TXLSXWorkbook;
Data, Summary: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Data := Book.Sheets.Add('Data');
Summary := Book.Sheets.Add('Summary');
// ... riempite Data!A2:D100 con le righe di dettaglio ...
Book.DefinedNames.Add('TaxRate', '0.08'); // ambito cartella, una costante
Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100'); // ambito cartella, un intervallo
Book.DefinedNames.Add('LocalNote', 'Summary!$B$1', 1); // limitato al solo foglio di indice 1
// le formule XLSX non prendono il prefisso '='
Summary.Cells[2, 2].Formula := 'SUM(Data!D2:D100)*TaxRate';
Book.SaveAs('model.xlsx');
finally
Book.Free;
end;
end;Un nome definito non deve per forza puntare a un intervallo. TaxRate qui sopra si riferisce alla nuda costante 0.08, ed è il modo più pulito per pubblicare un'ipotesi di business. Compare una sola volta in Gestione nomi di Excel, ogni formula lo richiama simbolicamente, e il cambio di aliquota del trimestre prossimo è una modifica di una riga nel generatore invece di una ricerca fra quattordici stringhe di formula assemblate
Il segno di uguale che sta da un lato solo
Il canale di inserimento delle formule è il punto in cui il codice portato si rompe più spesso, perché le due facciate non concordano sul segno di uguale. Le celle XLS ricevono le formule tramite Value con un = iniziale. Le celle XLSX hanno una proprietà dedicata Formula che accetta l'espressione senza il prefisso. Scrivete '=SUM(A1:A10)' dentro TXLSXCell.Formula e il segno di uguale diventa parte del testo dell'espressione memorizzata invece che un marcatore, e il file non si comporterà come la stessa stringa faceva sul lato XLS
var
Book: IXLSWorkbook; // conteggiata come interfaccia: non chiamate Free
Names: IXLSNames;
begin
Book := TXLSWorkbook.Create;
// si assume che un foglio chiamato 'Data' contenga già le righe di dettaglio
Names := Book.GetNames;
Names.Add('TaxRate', '0.08');
Names.Add('Helper', 'Data!$A$2:$A$100', False); // False = nascosto in Gestione nomi
// le formule XLS passano da Value, con il prefisso '='
Book.Sheets[1].Cells.Item[2, 2].Value := '=SUM(Data!A2:A100)*TaxRate';
Book.SaveAs('model.xls');
end;Quel frammento mostra altre due stranezze del lato XLS. La collezione dei fogli parte da 1, quindi Sheets[1] è il primo foglio, contro lo Sheets[0] in base 0 di XLSX. E il terzo parametro di Add crea un nome nascosto: presente nel file e utilizzabile dalle formule, eppure invisibile in Gestione nomi di Excel. I nomi nascosti sono il veicolo giusto per l'impianto interno del generatore che gli utenti finali non dovrebbero mai modificare o cancellare per sbaglio
Riferimenti fra fogli, e cosa succede quando le righe si spostano
Entrambi i motori di formule accettano la sintassi standard fra fogli. I nomi di foglio semplici qualificano direttamente come Data!A1; un nome con spazi o punteggiatura richiede gli apici singoli, come in 'Sheet With Space'!A1. Dentro il testo RefersTo di un nome, ricorrete quasi sempre a riferimenti assoluti come Data!$A$2:$D$100. Un riferimento relativo dentro un nome definito si risolve rispetto alla cella che lo usa, che è una funzionalità voluta di Excel e una fonte affidabile di confusione quando scatta per sbaglio
Le modifiche strutturali sono il punto in cui la contabilità fra fogli ripaga, e il lato XLSX mantiene i nomi coerenti attraverso di esse. InsertRows e DeleteRows spostano gli intervalli dei nomi definiti insieme a celle, unioni, collegamenti ipertestuali e ancoraggi dei grafici, quindi un nome che punta a Data!$A$2:$D$100 copre ancora il blocco dati dopo che il generatore ha aperto uno spazio sopra di esso. Le formule arrivano con un avvertimento documentato: l'inserimento di righe corregge solo i riferimenti che puntano al foglio in modifica. Una formula su Summary che richiama Data!D2:D100 viene riscritta quando si inseriscono righe in Data, che è il caso che di solito volete. Verificatelo invece di darlo per scontato, perché il motore ve lo dice a poco prezzo:
// il motore di calcolo risolve nomi e riferimenti fra fogli nel processo
V := Book.Calculate('SUM(Data!D2:D100)*TaxRate');
if VarIsNumeric(V) then
Log('net total checks out: ' + FloatToStr(V));Calculate valuta un'espressione arbitraria contro lo stato corrente della cartella di lavoro senza salvare nulla, il che ne fa la primitiva naturale di asserzione per i test del generatore. Calcolate in Pascal l'aggregato atteso dai dati sorgente, valutate la formula della cartella di lavoro stessa e confrontate i due valori. L'articolo sul motore di formule spiega cosa valuta il motore, quando lo fa e come estenderlo con funzioni personalizzate
I nomi _xlnm che appartengono al livello delle proprietà
Aprite la tabella dei nomi di un file generato con un ispettore di basso livello e troverete voci che non avete mai scritto: _xlnm.Print_Area, _xlnm.Print_Titles e i loro parenti. È così che OOXML (ECMA-376 / ISO 29500) memorizza le aree di stampa e le righe di titolo ripetute, come nomi definiti con identificatori riservati. HotXLS li gestisce tramite proprietà dedicate del foglio, quindi impostare PrintArea o PrintTitleRows scrive per voi la corrispondente voce _xlnm.*
La trappola è mettere le mani in quello spazio di nomi riservato. Aggiungete una voce _xlnm.Print_Area tramite DefinedNames.Add mentre impostate anche la proprietà PrintArea e la cartella di lavoro porta due definizioni in conflitto per un solo nome riservato, uno stato che Excel risolve in modi su cui nessun prodotto dovrebbe contare. Trattate ogni identificatore che inizia con _xlnm. come appartenente al livello delle proprietà. Per ispezionare la configurazione di stampa, leggete le proprietà, non la tabella dei nomi. L'articolo su protezione e impostazione della pagina tratta nel contesto le proprietà dell'area di stampa
Due limiti da conoscere prima di fissare un progetto
I nomi definiti non viaggiano attraverso il ponte di comodo da XLS a XLSX. SaveXLSWorkbookAsXLSX copia il contenuto delle celle e la formattazione di base, e la tabella dei nomi non è nel suo elenco documentato di copia, quindi una cartella di lavoro che dipendeva dai propri nomi li perde nel passaggio. Ricreate i nomi tramite DefinedNames.Add dopo la conversione. Quel passo è meno gravoso di quanto sembri, perché vi offre un momento per normalizzarne gli ambiti invece di riportare qualunque cosa il file XLS avesse
L'altro limite è lo scostamento fra le stringhe di formula e i nomi dei fogli. Excel riscrive i riferimenti ai fogli dentro formule e nomi durante una rinomina interattiva, quindi i file che un utente modifica in Excel restano coerenti da soli. L'esposizione sta dal lato del generatore: quando il codice Pascal assembla stringhe di formula da un letterale con il nome del foglio, rinominare il foglio in un punto e dimenticare l'altro produce un riferimento a un foglio che non esiste più. Tenete il nome del foglio in una sola costante Delphi e passatela sia a Sheets.Add sia alla vostra composizione delle formule, e le due non potranno mai discordare. È lo stesso istinto che consiglia di dare un nome alle celle di output di un report invece di codificarne gli indirizzi: un modello la cui cella del totale è denominata continua a funzionare dopo che un progettista inserisce tre righe sopra di essa, mentre un generatore che scrive su un letterale B17 deposita in silenzio il suo numero nel posto sbagliato. L'articolo sulla generazione di report da modelli si basa esattamente su quello schema
La API completa dei nomi definiti per entrambi i formati, insieme al riferimento del motore di formule, è distribuita con HotXLS Delphi Component