Definirani naziv (defined name) je oznaka koja zamjenjuje konstantu, raspon ćelija ili izraz formule, pohranjena jednom u radnoj knjizi i simbolički referencirana svugdje gdje je potrebna. Napišite TaxRate u formuli i mehanizam će ga razriješiti u ono što definicija naziva drži, bilo da se radi o doslovnoj vrijednosti (literal) 0.08 ili rasponu Data!$A$2:$D$100. Referenca među listovima (cross-sheet reference) je ortogonalna ideja: Data!D2 doseže ćeliju na drugom listu kvalificiranjem adrese nazivom lista. Spojite to dvoje i sažeti list (summary sheet) može zbrojiti detaljni list kroz naziv koji nikada ne spominje doslovnu adresu, što je točno ono što želite u radnoj knjizi koju generator sastavlja, a računovođa kasnije revidira.
HotXLS, losLab-ov izvorni Delphi paket za XLS i XLSX datoteke, izlaže tablicu naziva oba formata s pravima stvaranja, pronalaženja i brisanja, plus mehanizam formula koji razrješava nazive i reference između listova u samom procesu. Dva formata drže zasebne hijerarhije klasa, a razlike između njihovih API-ja za nazive su dio koji stvara probleme kodu prenesenom s jednog formata na drugi.
Dva spremišta naziva koja ne dijele sučelje
Na XLS strani, TXLSWorkbook.GetNames vraća kolekciju IXLSNames čije preopterećenje Add(Name, RefersTo, Visible) zapisuje naziv u BIFF tablicu naziva. Pojedinačni unosi vraćaju se kao objekti IXLSName koji nose Name, RefersTo, razriješeni RefersToRange i metodu Delete. Na XLSX strani, TXLSXWorkbook.DefinedNames je kolekcija TXLSXDefinedNames s metodama Add, FindByName i DeleteByName.
Konvencije pretraživanja se razilaze na način koji izbija na površinu tijekom prijenosa (porting) radnje nego u vrijeme prevođenja (compile time). Zadano svojstvo Item XLS kolekcije prihvaća Variant, pa se i Names[0] i Names['TaxRate'] razrješavaju prema njemu. XLSX kolekcija nema takvo zadano svojstvo; pozivate FindByName('TaxRate'), što vraća nil kada naziv nije prisutan. Kôd napisan za jednu fasadu prevodi se protiv druge samo slučajno, a neuspjeh se obično očituje kao pristup nil vrijednosti u izvođenju radije nego kao crvena vijugava crta u IDE-u.
Opseg je prva odluka, a ne zastavica koju dodajete kasnije
Definirani naziv je ili s opsegom radne knjige (workbook-scoped), vidljiv formulama na svakom listu, ili s opsegom lista (sheet-scoped), vidljiv samo formulama na listu koji ga posjeduje. U XLSX API-ju ta je razlika jedan neobavezni parametar. Metoda DefinedNames.Add(AName, AFormula) stvara naziv na razini radne knjige, dok je Add(AName, AFormula, ASheetIndex) vezuje za jedan list. Čitajući ga natrag, TXLSXDefinedName.SheetIndex vraća -1 za opseg radne knjige, a inače indeks lista na bazi 0.
Opseg služi i kao vaše pravilo za izbjegavanje konflikata (collision policy), i to je razlog da to riješite prije nego što zapišete prvi naziv. Excel dopušta lokalni list-specifični naziv Total na svakom listu plus naziv Total na razini radne knjige, a formula na određenom listu najprije rješava onaj lokalni. Generirane radne knjige trebale bi se namjerno oslanjati na to. Poslovne pretpostavke koje konzumira više listova, kao što su porezne stope, tečajevi valuta (FX rates) i izvještajno razdoblje, pripadaju opsegu radne knjige. Pomoćni rasponi (helper ranges) koje referenciraju formule samo jednog lista sigurniji su s opsegom lista, gdje ih ništa ne može zasjeniti niti oni mogu zasjeniti išta drugo.
var
Book: TXLSXWorkbook;
Data, Summary: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Data := Book.Sheets.Add('Data');
Summary := Book.Sheets.Add('Summary');
// ... fill Data!A2:D100 with detail rows ...
Book.DefinedNames.Add('TaxRate', '0.08'); // workbook scope, a constant
Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100'); // workbook scope, a range
Book.DefinedNames.Add('LocalNote', 'Summary!$B$1', 1); // scoped to sheet index 1 only
// XLSX formulas take no leading '='
Summary.Cells[2, 2].Formula := 'SUM(Data!D2:D100)*TaxRate';
Book.SaveAs('model.xlsx');
finally
Book.Free;
end;
end;Definirani naziv ne mora pokazivati na raspon. TaxRate gore odnosi se na čistu konstantu 0.08, i to je najčišći način za objavljivanje poslovne pretpostavke. Pojavljuje se jednom u Excelovom upravitelju naziva (Name Manager), svaka formula ga referencira simbolički, a promjena stope u sljedećem tromjesečju je uređivanje jedne linije u generatoru umjesto pretraživanja kroz četrnaest sastavljenih formula.
Znak jednakosti koji pripada samo jednoj strani
Kanal za unos formula je mjesto gdje preneseni kôd najčešće puca, jer se dvije fasade ne slažu oko znaka jednakosti. XLS ćelije primaju formule kroz svojstvo Value s vodećim znakom =. XLSX ćelije imaju namjensko svojstvo Formula koje prima izraz bez prefiksa. Upišite '=SUM(A1:A10)' u TXLSXCell.Formula i znak jednakosti postaje dio pohranjenog teksta izraza, a ne marker, pa se datoteka neće ponašati na način na koji se isti niz ponašao na XLS strani.
var
Book: IXLSWorkbook; // interface-counted: do not Free
Names: IXLSNames;
begin
Book := TXLSWorkbook.Create;
// assume a sheet named 'Data' already holds the detail rows
Names := Book.GetNames;
Names.Add('TaxRate', '0.08');
Names.Add('Helper', 'Data!$A$2:$A$100', False); // False = hidden from the Name Manager
// XLS formulas go through Value, with the '=' prefix
Book.Sheets[1].Cells.Item[2, 2].Value := '=SUM(Data!A2:A100)*TaxRate';
Book.SaveAs('model.xls');
end;Taj isječak koda prikazuje još dvije neobičnosti na XLS strani. Zbirka listova (sheets collection) je na bazi 1, pa je Sheets[1] prvi list, naspram XLSX-a koji je na bazi 0 (Sheets[0]). Također, treći parametar metode Add stvara skriveni naziv: prisutan u datoteci i upotrebljiv za formule, ali nevidljiv u Excelovom upravitelju naziva. Skriveni nazivi su pravi prijenosnik za interno generatorovo povezivanje koje krajnji korisnici nikada ne bi trebali slučajno urediti ili izbrisati.
Reference između listova i što se događa kada se retci pomaknu
Unutar teksta RefersTo definiranog naziva, gotovo svaki put posegnite za apsolutnim referencama kao što su Data!$A$2:$D$100. Relativna referenca unutar definiranog naziva razrješava se u odnosu na ćeliju koja je koristi, što je namjerna značajka Excela i pouzdan izvor zabune kada se aktivira slučajno.
Strukturna uređivanja su mjesto gdje knjigovodstvo među listovima opravdava svoj trud, a XLSX strana održava nazive dosljednima kroz njih. Operacije InsertRows i DeleteRows pomiču raspone definiranih naziva zajedno s ćelijama, spajanjima, hipervezama i sidrima grafikona, pa naziv koji pokazuje na Data!$A$2:$D$100 i dalje pokriva podatkovni blok nakon što generator otvori prazninu iznad njega. Formule komen s jednim dokumentiranim upozorenjem: umetanje redaka prilagođava samo reference koje ciljaju list koji se uređuje. Formula na listu Summary koja referencira Data!D2:D100 ponovno se zapisuje kada se retci umetnu u list Data, što je slučaj koji obično želite. Provjerite to radije nego da pretpostavljate, jer će vam mehanizam to jeftino reći:
// the calculation engine resolves names and cross-sheet references in-process
V := Book.Calculate('SUM(Data!D2:D100)*TaxRate');
if VarIsNumeric(V) then
Log('net total checks out: ' + FloatToStr(V));Metoda Calculate procjenjuje proizvoljan izraz u odnosu na trenutno stanje radne knjige bez spremanja ičega, što je čini prirodnim primitivom asercije (assertion primitive) za testove generatora. Izračunajte očekivani agregat iz izvornih podataka u Pascalu, procijenite vlastitu formulu radne knjige i usporedite to dvoje. Članak mehanizam formula pokriva što mehanizam procjenjuje, kada, i kako ga proširiti prilagođenim funkcijama.
Nazivi _xlnm koje posjeduje sloj svojstava
Otvorite tablicu naziva generirane datoteke u niskorazinskom inspektoru i naći ćete unose koje nikada niste napisali: _xlnm.Print_Area, _xlnm.Print_Titles i njihove srodnike. Na taj način OOXML (ECMA-376 / ISO 29500) pohranjuje područja ispisa i ponavljajuće retke zaglavlja, kao definirane nazive s rezerviranim identifikatorima. HotXLS upravlja njima kroz namjenska svojstva radnog lista, pa postavljanje PrintArea ili PrintTitleRows zapisuje odgovarajući unos _xlnm.* za vas.
Zamka je ručno posezanje u taj rezervirani prostor naziva. Dodajte unos _xlnm.Print_Area putem DefinedNames.Add dok istodobno postavljate svojstvo PrintArea property i radna knjiga će nositi dvije konfliktne definicije za jedan rezervirani naziv, stanje koje Excel rješava na načine na koje se nijedan proizvod ne bi trebao oslanjati. Tretirajte svaki identifikator koji počinje s _xlnm. kao da pripada sloju svojstava. Za pregled postavki ispisa pročitajte svojstva, a ne tablicu naziva. Članak zaštita i postavljanje stranice pokriva svojstva ispisnog područja u kontekstu.
Dvije granice koje vrijedi znati prije nego što se posvetite dizajnu
Definirani nazivi ne putuju kroz prikladni most XLS-u-XLSX. Metoda SaveXLSWorkbookAsXLSX kopira sadržaj ćelija i osnovno oblikovanje, a tablica naziva nije na njezinom dokumentiranom popisu kopiranja, pa radna knjiga koja je ovisila o svojim nazivima gubi ih pri prijelazu. Ponovno stvorite nazive putem DefinedNames.Add nakon pretvorbe. Taj je korak manji teret nego što zvuči jer vam daje trenutak da normalizirate njihove opsege umjesto prijenosa bilo čega što je XLS datoteka slučajno imala.
Druga granica je odstupanje između nizova formula i naziva listova. Excel prepisuje reference listova unutar formula i naziva tijekom interaktivnog preimenovanja, pa datoteke koje korisnik uređuje u Excelu ostaju dosljedne same po sebi. Izloženost je na strani generatora: kada Pascal kôd sastavlja nizove formula iz doslovnog naziva lista (sheet-name literal), preimenovanje lista na jednom mjestu i zaboravljanje drugog stvara referencu na list koji više ne postoji. Držite naziv lista u jednoj Delphi konstanti i proslijedite je metodi Sheets.Add i vašem sklopu formula, pa se to dvoje nikada ne može razilaziti. To je isti instinkt koji zagovara imenovanje izlaznih ćelija izvješća umjesto tvrdog kodiranja adresa: predložak čija je ćelija ukupnog iznosa imenovana nastavlja raditi nakon što dizajner umetne tri retka iznad nje, dok generator koji piše u doslovnu adresu B17 tiho upisuje svoj broj na pogrešno mjesto. Članak generiranje izvješća na temelju predložaka gradi se na upravo tom obrascu.
Cjelokupni API za definirane nazive za oba formata, zajedno s referencom mehanizma formula, isporučuje se s paketom HotXLS Component.