Definovaný názov je menovka, ktorá nahrádza konštantu, rozsah buniek alebo výraz vzorca, pričom je v zošite uložená iba raz a referencuje sa symbolicky všade tam, kde je potrebná. Napíšte TaxRate do vzorca a výpočtový nástroj ho vyhodnotí na čokoľvek, čo definícia názvu obsahuje, či už ide o literál 0.08 alebo rozsah Data!$A$2:$D$100. Odkaz naprieč hárkami je komplementárny koncept: výraz Data!D2 pristupuje k bunke na inom hárku tým, že adresu kvalifikuje názvom hárka. Spojte tieto dve možnosti dohromady a súhrnný hárok môže spočítať údaje z detailného hárka pomocou názvu, ktorý nikdy nezmieňuje konkrétnu adresu. To je presne to, čo chcete v zošite, ktorý zostavuje generátor a neskôr kontroluje účtovník.
HotXLS, natívna delphiovská knižnica od losLab pre súbory XLS a XLSX, sprístupňuje tabuľku názvov pre oba formáty s možnosťou vytvárania, vyhľadávania a odstraňovania názvov, a navyše obsahuje nástroj na vyhodnocovanie vzorcov, ktorý priamo počas behu spracováva názvy a odkazy naprieč hárkami. Tieto dva formáty si zachovávajú oddelené hierarchie tried, pričom rozdiely medzi ich rozhraniami API pre názvy sú často dôvodom, prečo kód portovaný z jedného formátu do druhého zlyháva.
Dve úložiská názvov bez spoločného rozhrania
Na strane XLS metóda TXLSWorkbook.GetNames vracia kolekciu IXLSNames, ktorej preťaženie Add(Name, RefersTo, Visible) zapisuje názov do tabuľky názvov BIFF. Jednotlivé položky sa vracajú ako objekty IXLSName s vlastnosťami Name, RefersTo, vyhodnoteným rozsahom RefersToRange a metódou Delete. Na strane XLSX predstavuje TXLSXWorkbook.DefinedNames kolekciu TXLSXDefinedNames s metódami Add, FindByName a DeleteByName.
Konvencie vyhľadávania sa líšia spôsobom, ktorý sa prejaví až počas behu programu, a nie pri kompilácii. Predvolená vlastnosť Item kolekcie XLS prijíma typ Variant, takže zápisy Names[0] aj Names['TaxRate'] sa vyhodnotia správne. Kolekcia XLSX však takúto predvolenú vlastnosť nemá; musíte volať FindByName('TaxRate'), čo v prípade neexistencie názvu vráti hodnotu nil. Kód napísaný pre jedno rozhranie sa skompiluje pre druhé len náhodou, pričom chyba sa zvyčajne prejaví ako prístup k neplatnému ukazovateľu (nil) za behu, a nie ako červené podčiarknutie v IDE.
Rozsah je prvým rozhodnutím, nie vlastnosťou, ktorú pridáte neskôr
Definovaný názov má buď rozsah pre celý zošit (je viditeľný pre vzorce na každom hárku), alebo rozsah pre konkrétny hárok (je viditeľný iba pre vzorce na hárku, ktorý ho vlastní). V rozhraní API pre XLSX je tento rozdiel vyjadrený jediným voliteľným parametrom. Volanie DefinedNames.Add(AName, AFormula) vytvorí názov na úrovni celého zošita, zatiaľ čo Add(AName, AFormula, ASheetIndex) ho naviaže na jeden konkrétny hárok. Pri načítaní spätne vracia vlastnosť TXLSXDefinedName.SheetIndex hodnotu -1 pre rozsah zošita, inak vracia index hárka začínajúci od nuly.
Rozsah zároveň definuje vaše pravidlá pre predchádzanie kolíziám názvov, čo je hlavný dôvod, prečo si ho vyjasniť ešte pred zapísaním prvého názvu. Excel povoľuje lokálny názov Total na každom hárku popri globálnom názve Total na úrovni celého zošita, pričom vzorec na konkrétnom hárku vyhodnotí najskôr lokálny názov. Generované zošity by mali tento princíp využívať zámerne. Globálne biznis parametre, ktoré využíva viacero hárkov (napríklad daňové sadzby, menové kurzy alebo vykazované obdobie), patria do rozsahu celého zošita. Pomocné rozsahy, na ktoré odkazujú iba vzorce jedného hárka, sú bezpečnejšie s rozsahom hárka, kde ich nič nemôže prekryť a ani ony samy neprekryjú iné názvy.
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;Definovaný názov nemusí ukazovať iba na rozsah buniek. Premenná TaxRate vo vyššie uvedenom príklade odkazuje na samotnú konštantu 0.08, čo predstavuje najčistejší spôsob, ako definovať biznis parameter. Zobrazí sa raz v Správcovi názvov v Exceli, každý vzorec naň symbolicky odkazuje a zmena sadzby v ďalšom štvrťroku znamená úpravu na jednom riadku v generátore namiesto pracného vyhľadávania a nahrádzania v desiatkach vygenerovaných reťazcov vzorcov.
Znak rovná sa, ktorý patrí iba na jednu stranu
Spôsob zadávania vzorcov je miestom, kde sa portovaný kód láme najčastejšie, pretože obe rozhrania sa nezhodujú v používaní znaku rovná sa. Bunky XLS prijímajú vzorce cez vlastnosť Value s počiatočným znakom =. Bunky XLSX majú naopak vyhradenú vlastnosť Formula, ktorá prijíma výraz bez tohto znaku. Ak zapíšete '=SUM(A1:A10)' do vlastnosti TXLSXCell.Formula, znak rovná sa sa stane súčasťou uloženého textu výrazu namiesto toho, aby slúžil ako indikátor vzorca, a súbor sa nebude správať tak, ako sa rovnaký reťazec správal na strane XLS.
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;Tento fragment kódu ukazuje ďalšie dve zvláštnosti na strane XLS. Kolekcia hárkov používa indexovanie od 1, takže Sheets[1] predstavuje prvý hárok, na rozdiel od XLSX, kde kolekcia Sheets[0] začína od nuly. Tretí parameter metódy Add navyše vytvára skrytý názov, ktorý je v súbore prítomný a vzorce ho môžu používať, no v Správcovi názvov v Exceli sa nezobrazí. Skryté názvy sú ideálnym prostriedkom pre internú logiku generátora, ktorú by koncoví používatelia nemali omylom upraviť alebo vymazať.
Odkazy naprieč hárkami a čo sa stane pri presune riadkov
Oba výpočtové nástroje podporujú štandardnú syntax odkazovania naprieč hárkami. Jednoduché názvy hárkov sa kvalifikujú priamo ako Data!A1; názov s medzerami alebo interpunkciou vyžaduje jednoduché úvodzovky, napríklad 'Sheet With Space'!A1. Vnútri textu RefersTo pre definovaný názov takmer vždy používajte absolútne odkazy, ako napríklad Data!$A$2:$D$100. Relatívny odkaz vnútri definovaného názvu sa totiž vyhodnocuje relatívne k bunke, ktorá ho používa, čo je síce zámerná funkcia Excelu, no zároveň spoľahlivý zdroj chýb, ak sa použije neúmyselne.
Štrukturálne úpravy sú oblasťou, kde sa interná správa odkazov naprieč hárkami plne prejaví, a strana XLSX udržiava názvy počas týchto zmien konzistentné. Metódy InsertRows a DeleteRows posúvajú rozsahy definovaných názvov spolu s bunkami, zlúčeniami, odkazmi a ukotveniami grafov, takže názov ukazujúci na Data!$A$2:$D$100 stále pokrýva správny dátový blok aj po tom, čo generátor nad ním vytvorí medzeru. Vzorce majú jedno zdokumentované upozornenie: vkladanie riadkov upravuje iba tie odkazy, ktoré cielia na upravovaný hárok. Vzorec na hárku Summary, ktorý odkazuje na Data!D2:D100, sa pri vložení riadkov do hárka Data prepíše, čo je zvyčajne požadované správanie. Overte si to namiesto spoliehania sa na predpoklady, keďže výpočtový nástroj vám to umožní zistiť veľmi jednoducho:
// 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));Metóda Calculate vyhodnocuje ľubovoľný výraz voči aktuálnemu stavu zošita bez toho, aby čokoľvek ukladala, čo z nej robí prirodzený prostriedok pre testovanie výstupov generátora. Vypočítajte očakávaný súčet zo zdrojových dát v Pascale, vyhodnoťte samotný vzorec zošita a porovnajte tieto dva výsledky. Článok o nástroji pre vzorce podrobne popisuje, čo a kedy tento nástroj vyhodnocuje a ako ho rozšíriť o vlastné funkcie.
Názvy _xlnm, ktoré vlastní vrstva vlastností
Ak otvoríte tabuľku názvov vygenerovaného súboru v nízkoúrovňovom inšpektore, nájdete v nej položky, ktoré ste nikdy sami nezapísali: _xlnm.Print_Area, _xlnm.Print_Titles a im podobné. Ide o spôsob, akým formát OOXML (ECMA-376 / ISO 29500) ukladá oblasti tlače a opakujúce sa riadky záhlavia, a to formou definovaných názvov s vyhradenými identifikátormi. HotXLS ich spravuje prostredníctvom vyhradených vlastností hárka, takže nastavenie vlastností PrintArea alebo PrintTitleRows zapíše príslušný záznam _xlnm.* automaticky za vás.
Rizikom je manuálny zásah do tohto vyhradeného menného priestoru. Ak pridáte položku _xlnm.Print_Area cez metódu DefinedNames.Add a zároveň nastavíte vlastnosť PrintArea, zošit bude obsahovať dve konfliktné definície pre ten istý vyhradený názov, čo je stav, ktorý Excel rieši nepredvídateľnými spôsobmi. Považujte každý identifikátor začínajúci na _xlnm. za majetok vrstvy vlastností. Ak chcete skontrolovať nastavenie tlače, čítajte vlastnosti, nie tabuľku názvov. Článok o ochrane a nastavení stránky rozoberá vlastnosti tlačových oblastí v širšom kontexte.
Dva dôležité limity, ktoré by ste mali poznať pred návrhom systému
Definované názvy sa neprenášajú cez zjednodušené premostenie XLS-to-XLSX. Funkcia SaveXLSWorkbookAsXLSX kopíruje obsah buniek a základné formátovanie, no tabuľka názvov nie je na jej zozname kopírovaných položiek. Zošit, ktorý na tieto názvy spoliehal, ich preto pri konverzii stratí. Po konverzii znova vytvorte názvy pomocou DefinedNames.Add. Tento krok je jednoduchší, než sa zdá, pretože vám dáva príležitosť normalizovať ich rozsahy namiesto prostého prebratia všetkého, čo súbor XLS náhodne obsahoval.
Druhým limitom je nesúlad medzi reťazcami vzorcov a názvami hárkov. Excel prepisuje odkazy na hárky vo vzorcoch a názvoch pri interaktívnom premenovaní, takže súbory, ktoré používateľ upravuje priamo v Exceli, zostávajú konzistentné samy o sebe. Riziko však vzniká na strane generátora: ak kód v Pascale skladá vzorce s pevne zadaným názvom hárka, premenovanie hárka na jednom mieste a opomenutie druhého miesta vytvorí odkaz na neexistujúci hárok. Uložte názov hárka do jedinej konštanty Delphi a použite ju pri volaní Sheets.Add aj pri skladaní vzorcov, čím zabránite vzniku nesúladu. Ide o rovnaký prístup, ktorý hovorí v prospech pomenovania výstupných buniek reportu namiesto pevného kódovania adries: šablóna, ktorej výsledná bunka je pomenovaná, bude naďalej fungovať aj potom, čo dizajnér vloží tri riadky nad ňu, zatiaľ čo generátor zapisujúci na pevnú adresu B17 potichu zapíše hodnotu na nesprávne miesto. Článok o generovaní reportov zo šablón stavia presne na tomto vzore.
Kompletné API pre definované názvy pre oba formáty, spolu s referenciou pre výpočtový nástroj vzorcov, sa dodáva s balíkom HotXLS Component.