Definirana imena in formule med listi v Delphiju s HotXLS

Definirano ime je oznaka, ki nadomešča konstanto, območje celic ali izraz formule, shranjena enkrat v delovnem zvezku in simbolno uporabljena povsod, kjer je to potrebno. Če v formulo zapišete TaxRate, jo bo pogon razrešil v to, kar vsebuje definicija tega imena, naj bo to dobesedna vrednost 0.08 ali območje Data!$A$2:$D$100. Referenca med listi (cross-sheet reference) je druga ideja: Data!D2 doseže celico na drugem listu tako, da naslov označi z imenom lista. Združite oboje in zbirni list lahko sešteva podrobnosti z drugega lista prek imena, ki nikoli ne omenja dejanskega naslova, kar je natanko tisto, kar želite v delovnem zvezku, ki ga sestavi generator, pozneje pa revidira računovodja

HotXLS, losLabova izvorna knjižnica za Delphi za datoteke XLS in XLSX, izpostavlja tabelo imen obeh formatov z dostopom za ustvarjanje, iskanje in brisanje, poleg tega pa ponuja pogon za formule, ki sproti razrešuje imena in reference med listi. Formata ohranjata ločene razredne hierarhije, razlike med njunimi API-ji za imena pa so tisti del, ki povzroča težave pri prenosu kode iz enega v drugega

Dve shrambi imen, ki si ne delita vmesnika

Na strani XLS klic 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 nosijo lastnosti Name, RefersTo, razrešeni RefersToRange in metodo Delete. Na strani XLSX je TXLSXWorkbook.DefinedNames zbirka TXLSXDefinedNames z metodami Add, FindByName in DeleteByName

Konvencije iskanja se razlikujejo na način, ki se pojavi med prenosom kode in ne ob prevajanju. Privzeta lastnost Item zbirke XLS sprejema Variant, zato se tako Names[0] kot Names['TaxRate'] razrešita glede nanjo. Zbirka XLSX nima takšne privzete lastnosti; pokličete FindByName('TaxRate'), ki vrne nil, ko ime ne obstaja. Koda, napisana za eno stran, se prevede za drugo le po naključju, napaka pa se običajno pokaže kot dostop do vrednosti nil med izvajanjem in ne kot rdeča valovita črta v okolju IDE

Doseg (Scope) je prva odločitev, ne zastavica, ki jo dodate pozneje

Definirano ime je lahko bodisi na ravni delovnega zvezka (workbook-scoped), vidno formulam na vsakem listu, bodisi na ravni lista (sheet-scoped), vidno le formulam na listu, ki ga ima v lasti. V API-ju XLSX je ta razlika en sam neobvezen parameter. DefinedNames.Add(AName, AFormula) ustvari ime na ravni delovnega zvezka, medtem ko ga Add(AName, AFormula, ASheetIndex) veže na en list. Pri branju nazaj TXLSXDefinedName.SheetIndex vrne -1 za doseg delovnega zvezka in 0-baziran indeks lista v nasprotnem primeru

Doseg deluje tudi kot vaša politika preprečevanja trkov (collisions) in to je razlog, da jo določite, preden zapišete prvo ime. 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. Ustvarjeni delovni zvezek bi moral to izkoriščati namerno. Poslovne predpostavke, ki jih uporablja več listov, kot so davčne stopnje, devizni tečaji in obdobje poročanja, spadajo v doseg delovnega zvezka. Pomožna območja, na katera se nanašajo le formule enega lista, pa so varnejša v dosegu lista, kjer jih nič ne more zasenčiti in sama ne morejo zasenčiti 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;

Definiranemu imenu ni treba kazati na območje. TaxRate zgoraj se nanaša na golo 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 nanaša simbolno, sprememba stopnje v naslednjem četrtletju pa pomeni urejanje ene vrstice v generatorju namesto iskanja po štirinajstih sestavljenih nizih formul

Znak za enačaj, ki sodi le na eno stran

Vnosni kanal za formule je mesto, kjer se prenesena koda najpogosteje pokvari, saj se strani ne strinjata glede znaka za enačaj. Celice XLS prejemajo formule prek Value z vodilnim =. Celice XLSX imajo namensko lastnost Formula, ki sprejme izraz brez predpone. Če zapišete '=SUM(A1:A10)' v TXLSXCell.Formula, bo enačaj postal del shranjenega besedila izraza in ne označevalec, datoteka pa se ne bo obnašala tako, kot se je isti niz obnašal 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 prikazuje še dve posebnosti na strani XLS. Zbirka listov je 1-bazirana, zato je Sheets[1] prvi list, nasproti 0-baziranemu Sheets[0] v XLSX. In tretji parameter metode Add ustvari skrito ime: prisotno je v datoteki in uporabno v formulah, a nevidno v Excelovem Upravitelju imen. Skrita imena so pravo orodje za interno strukturo generatorja, ki je končni uporabniki ne bi smeli nikoli pomotoma urediti ali izbrisati

Reference med listi in kaj se zgodi, ko se vrstice premaknejo

Oba pogona za formule sprejemata standardno sintakso med listi. Navadna imena listov se označujejo neposredno kot Data!A1; ime s presledki ali ločili potrebuje enojne narekovaje, kot na primer 'Sheet With Space'!A1. Znotraj besedila RefersTo definiranega imena skoraj vedno uporabite absolutne reference, kot je Data!$A$2:$D$100. Relativna referenca znotraj definiranega imena se razreši glede na celico, ki jo uporablja, kar je namerna funkcija Excela in zanesljiv vir zmede, ko se sproži po nesreči

Strukturna urejanja so tisto mesto, kjer se obrestuje vodenje referenc med listi, stran XLSX pa skozi njih ohranja imena dosledna. Metode InsertRows in DeleteRows premikajo območja definiranih imen skupaj s celicami, združitvami, hiperpovezavami in sidri grafikonov, tako da ime, ki kaže na Data!$A$2:$D$100, še vedno pokriva podatkovni blok po tem, ko generator nad njim odpre vrzel. Formule imajo eno dokumentirano opozorilo: vstavljanje vrstic prilagodi le reference, ki ciljajo na list, ki se ureja. Formula na listu Summary, ki se nanaša na Data!D2:D100, se prepiše, ko se vrstice vstavijo v Data, kar je običajno tisto, kar želite. Preverite to, namesto da bi predvidevali, saj vam bo to pogon povedal zelo poceni:

// 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));

Klic Calculate ovrednoti poljuben izraz glede na trenutno stanje delovnega zvezka, ne da bi karkoli shranil, zaradi česar je to naravno orodje za preizkuse generatorja. Izračunajte pričakovani agregat iz izvornih podatkov v Pascalu, ovrednotite lastno formulo delovnega zvezka in primerjajte oboje. Članek o pogonu za formule opisuje, kaj pogon vrednoti, kdaj in kako ga razširiti s prilagojenimi funkcijami

Imena _xlnm, ki so v lasti plasti lastnosti

Odprite tabelo imen ustvarjene datoteke v nizkonivojskem pregledovalniku in našli boste vnose, ki jih niste nikoli napisali: _xlnm.Print_Area, _xlnm.Print_Titles in njim sorodne. Tako OOXML (ECMA-376 / ISO 29500) shranjuje območja tiskanja in ponovljene vrstice z naslovi, in sicer kot definirana imena z rezerviranimi identifikatorji. HotXLS jih upravlja prek namenskih lastnosti delovnega lista, tako da nastavitev PrintArea ali PrintTitleRows sama zapiše ustrezni vnos _xlnm.*

Past je ročno poseganje v ta rezervirani imenski prostor. Če dodate vnos _xlnm.Print_Area prek DefinedNames.Add in hkrati nastavite lastnost PrintArea, bo delovni zvezek vseboval dve nasprotujoči si definiciji za eno rezervirano ime, to pa je stanje, ki ga Excel razreši na načine, na katere se noben izdelek ne bi smel zanašati. Vsak identifikator, ki se začne z _xlnm., obravnavajte kot del, ki pripada plasti lastnosti. Za pregled nastavitve tiskanja preberite lastnosti in ne tabele imen. Članek o zaščiti in nastavitvi strani obravnava lastnosti območja tiskanja v ustreznem kontekstu

Dve meji, ki ju je vredno poznati pred načrtovanjem

Definirana imena se ne prenesejo prek priročnega mostu 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 izgubi pri prehodu. Po pretvorbi znova ustvarite imena prek DefinedNames.Add. Ta korak je manj težaven, kot se sliši, saj vam ponuja priložnost, da normalizirate njihove dosege, namesto da prenesete karkoli je datoteka XLS pač imela

Druga meja je razhajanje (drift) med nizi formul in imeni listov. Excel med interaktivnim preimenovanjem prepiše reference listov znotraj formul in imen, tako da datoteke, ki jih uporabnik ureja v Excelu, same ostanejo dosledne. Nevarnost je na strani generatorja: ko koda v Pascalu sestavlja nize formul iz dobesednega imena lista, preimenovanje lista na enem mestu in pozabljanje na drugem ustvari referenco na list, ki ne obstaja več. Ime lista shranite v eni sami Delphi konstanti in jo posredujete tako metodi Sheets.Add kot vašemu sestavljanju formul, pa se nikoli ne bosta razlikovala. To je enak instinkt, ki govori v prid poimenovanju izhodnih celic poročila namesto trdo kodiranih naslovov: predloga, katere celica s skupno vsoto je poimenovana, deluje še naprej po tem, ko oblikovalec nad njo vstavi tri vrstice, medtem ko generator, ki piše v dobesedno celico B17, tiho zapiše svojo številko na napačno mesto. Članek o ustvarjanju poročil na podlagi predlog gradi prav na tem vzorcu

Celoten API definiranih imen za oba formata, skupaj z referenco pogona za formule, se prinaša s komponento HotXLS Component