Določeno ime (defined name) je oznaka, ki nadomešča konstanto, obseg celic ali izraz formule. Shranjeno je enkrat v delovnem zvezku, nanj pa se simbolično sklicujemo povsod, kjer je to potrebno. Če v formulo zapišete TaxRate, jo pogon razreši v karkoli, kar določa definicija imena, pa naj bo to literal 0.08 or obseg Data!$A$2:$D$100. Sklic med listi (cross-sheet reference) je povezan koncept: Data!D2 doseže celico na drugem listu tako, da naslov opremi z imenom lista. Z združitvijo obeh konceptov lahko povzetek na enem listu sešteva podatke s podrobnega lista prek imena, ki nikoli ne omenja dobesednega naslova celic, kar je natanko tisto, kar želite v delovnem zvezku, ki ga sestavi generator, pozneje pa ga revidira računovodja.
HotXLS, losLabova izvorna knjižnica Delphi za datoteke XLS in XLSX, omogoča dostop do tabele imen obeh formatov z možnostmi ustvarjanja, iskanja in brisanja ter vsebuje pogon za formule, ki sproti razrešuje imena in sklice med listi. Oba formata ohranjata ločeni hierarhiji razredov, razlike med njunimi API-ji za imena pa so tisti del, ki najpogosteje povzroča težave pri prenosu kode iz enega formata v drugega.
Dve shrambi imen brez skupnega vmesnika
Na strani XLS funkcija TXLSWorkbook.GetNames vrne zbirko IXLSNames, katere preobremenitev Add(Name, RefersTo, Visible) zapiše ime v tabelo imen BIFF. Posamezni vnosi se vrnejo kot objekti IXLSName, ki vsebujejo lastnosti Name, RefersTo, razrešeni RefersToRange in metodo Delete. Na strani XLSX pa je TXLSXWorkbook.DefinedNames zbirka TXLSXDefinedNames z metodami Add, FindByName in DeleteByName.
Konvencije iskanja se razlikujejo na način, ki se pokaže med prenosu kode in ne med prevajanjem. Privzeta lastnost Item zbirke XLS sprejema tip Variant, zato se tako Names[0] kot Names['TaxRate'] razrešita znotraj nje. Zbirka XLSX nima takšne privzete lastnosti; poklicati morate FindByName('TaxRate'), ki vrne nil, če ime ne obstaja. Koda, napisana za eno fasado, se za drugo prevede le po naključju, napaka pa se običajno pokaže kot runtime dostop do vrednosti nil namesto rdečega podčrtanja v okolju IDE.
Doseg je prva odločitev, ne zastavica, ki jo dodate kasneje
Določeno ime ima lahko doseg na ravni delovnega zvezka (vidno za formule na vseh listih) ali na ravni lista (vidno le za formule na tem določenem listu). V API-ju XLSX je ta razlika določena z enim neobveznim parametrom. Metoda DefinedNames.Add(AName, AFormula) ustvari ime na ravni delovnega zvezka, medtem ko ga Add(AName, AFormula, ASheetIndex) poveže z enim listom. Pri branju lastnost TXLSXDefinedName.SheetIndex vrne -1 za doseg delovnega zvezka, sicer pa indeks lista (indeksiranje se začne z 0).
Doseg služi tudi kot vaša politika preprečevanja konfliktov, zato je to odločitev smiselno sprejeti pred pisanjem prvega imena. Excel dovoljuje lokalno ime Total na vsakem listu in hkrati ime Total na ravni delovnega zvezka, pri čemer formula na določenem listu najprej razreši lokalno ime. Generirani delovni zvezki bi morali to lastnost namerno izkoristiti. Poslovne predpostavke, ki jih uporablja več listov (kot so davčne stopnje, devizni tečaji in obdobje poročanja), sodijo na raven delovnega zvezka. Pomožni obsegi, na katere se nanašajo le formule posameznega lista, pa so varnejši z dosegom na ravni lista, kjer jih nič ne more prekriti in sami ne morejo prekriti ničesar.
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;Določenemu imenu ni treba kazati na obseg celic. Ime TaxRate zgoraj se nanaša na preprosto konstanto 0.08, kar je najbolj čist način za objavo poslovne predpostavke. Pojavi se enkrat v Excelovem upravitelju imen (Name Manager), vsaka formula se nanj simbolično sklicuje, sprememba stopnje v naslednjem četrtletju pa pomeni le urejanje ene vrstice v generatorju namesto iskanja po štirinajstih sestavljenih nizih formul.
Znak za enačaj, ki sodi le na eno stran
Vnos formul je mesto, kjer se prenesena koda najpogosteje zalomi, saj se fasadi razlikujeta glede uporabe enačaja. Celice XLS prejemajo formule prek lastnosti Value z vodilnim znakom =. Celice XLSX pa imajo namensko lastnost Formula, ki sprejme izraz brez te predpone. Če zapišete '=SUM(A1:A10)' v lastnost TXLSXCell.Formula, postane enačaj del shranjenega besedila izraza in ne označevalnik formule, zato se datoteka ne bo obnašala enako, kot se je isti niz na strani 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;Ta izsek kode prikazuje še dve posebnosti na strani XLS. Zbirka listov je indeksirana z 1, zato je Sheets[1] prvi list, medtem ko XLSX uporablja indeksiranje od 0 (Sheets[0]). Tretji parameter metode Add pa ustvari skrito ime: to je prisotno v datoteki in ga formule lahko uporabljajo, vendar je nevidno v Excelovem upravitelju imen. Skrita imena so primerna rešitev za notranje mehanizme generatorja, ki jih končni uporabniki ne bi smeli urejati ali nenamerno izbrisati.
Sklici med listi in kaj se zgodi ob premikanju vrstic
Oba pogona za formule sprejemata standardno skladnjo sklicev med listi. Enostavna imena listov se uporabljajo neposredno kot Data!A1; ime s presledki ali ločili pa potrebuje enojne narekovaje, na primer 'Sheet With Space'!A1. Znotraj besedila RefersTo določenega imena skoraj vedno uporabite absolutne sklice, kot je Data!$A$2:$D$100. Relativni sklic znotraj določenega imena se namreč razreši glede na celico, ki ga uporablja, kar je namerna Excelova funkcija in hkrati pogost vir zmede, ko se sproži po nesreči.
Strukturne spremembe so področje, kjer se vodenje sklicev med listi najbolj izkaže, pri čemer stran XLSX ohranja doslednost imen. Metodi InsertRows in DeleteRows premikata obsege določenih imen skupaj s celicami, združitvami, povezavami in sidri grafikonov, tako da ime, ki kaže na Data!$A$2:$D$100, še vedno pokriva podatkovni blok, potem ko generator nad njim ustvari praznino. Formule imajo eno dokumentirano opozorilo: vstavljanje vrstic prilagodi le sklice, ki ciljajo na list, ki ga urejate. Formula na listu Summary, ki se nanaša na Data!D2:D100, se prepiše, ko so vrstice vstavljene v list Data, kar je običajno želeno vedenje. To raje preverite, kot pa da le domnevate, saj vam pogon to pove zelo enostavno:
// 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 oceni poljuben izraz glede na trenutno stanje delovnega zvezka brez shranjevanja česarkoli, kaj jo dela za idealno orodje za preizkuse generatorja. Izračunajte pričakovano agregatno vrednost iz izvornih podatkov v jeziku Pascal, ocenite lastno formulo delovnega zvezka in primerjajte rezultata. Članek o pogonu za formule opisuje, kaj pogon ocenjuje, kdaj in kako ga razširiti z lastnimi funkcijami.
Imena _xlnm, ki so v lasti plasti lastnosti
Če odprete tabelo imen ustvarjene datoteke v nizkonivojskem pregledovalniku, boste našli vnose, ki jih niste nikoli napisali: _xlnm.Print_Area, _xlnm.Print_Titles in sorodne vnose. Tako OOXML (ECMA-376 / ISO 29500) shranjuje območja tiskanja in ponavljajoče se vrstice z naslovi kot določena imena z rezerviranimi identifikatorji. HotXLS jih upravlja prek namenskih lastnosti delovnega lista, zato nastavitev PrintArea ali PrintTitleRows samodejno zapiše ustrezni vnos _xlnm.* entry namesto vas.
Past se skriva v ročnem poseganju v to rezervirano imensko območje. Če dodate vnos _xlnm.Print_Area prek DefinedNames.Add in hkrati nastavite lastnost PrintArea, bo delovni zvezek vseboval dve nasprotujoči si definiciji za isto rezervirano ime. To je stanje, ki ga Excel razreši na načine, na katere se ne bi smeli zanašati. Vsak identifikator, ki se začne z _xlnm., obravnavajte kot del plasti lastnosti. Za pregled nastavitve tiskanja raje preberite lastnosti in ne tabele imen. Članek o zaščiti in nastavitvi strani podrobneje obravnava te lastnosti.
Dve meji, ki ju je dobro poznati pred načrtovanjem
Določena imena se ne prenesejo prek priročnega povezovalnega mostu iz XLS v XLSX. Metoda SaveXLSWorkbookAsXLSX kopira vsebino celic in osnovno oblikovanje, tabela imen pa ni na njenem dokumentiranem seznamu kopiranja, zato delovni zvezek, ki je bil odvisen od svojih imen, ta imena ob pretvorbi izgubi. Po pretvorbi znova ustvarite imena prek DefinedNames.Add. Ta korak je manj zamuden, kot se sliši, saj vam ponuja priložnost, da normalizirate njihove dosege, namesto da prenašate karkoli je datoteka XLS pač imela.
Druga omejitev je razhajanje med nizi formul in imeni listov. Excel med interaktivnim preimenovanjem prepiše sklice na liste znotraj formul in imen, tako da datoteke, ki jih uporabnik ureja v Excelu, ostanejo dosledne same po sebi. Tveganje pa obstaja na strani generatorja: ko koda Pascal sestavlja nize formul iz dobesednega imena lista, preimenovanje lista na enem mestu in pozabljanje na drugega ustvari sklic na list, ki ne obstaja več. Hranite ime lista v eni sami konstanti Delphi in jo posredujte tako metodi Sheets.Add kot svojemu sklopu formul; tako se vrednosti nikoli ne bosta razlikovali. To je enak instinkt, ki govori v prid poimenovanju izhodnih celic poročila namesto trdega kodiranja naslovov: predloga, v kateri je celica za skupno vsoto poimenovana, deluje tudi potem, ko oblikovalec nad njo vstavi tri vrstice, medtem ko generator, ki piše na dobesedni naslov B17, svojo številko tiho zapiše na napačno mesto. Članek o generiranju poročil s predlogami izhaja natanko iz tega vzorca.
Celoten API za določena imena za oba formata, skupaj z referenco za pogon za formule, je del paketa HotXLS Component.