Definisana imena i formule između radnih listova u Delphi-ju pomoću HotXLS

Definisano ime je oznaka koja zamenjuje konstantu, opseg ćelija ili izraz formule, sačuvana jednom u radnoj svesci i referencirana simbolički svuda gde je to potrebno. Napišite TaxRate u formuli i mašina za izračunavanje će to razrešiti na vrednost koju definicija imena sadrži, bilo da je to literal 0.08 ili opseg Data!$A$2:$D$100. Referenca između radnih listova je ortogonalni koncept: Data!D2 pristupa ćeliji na drugom listu tako što kvalifikuje adresu nazivom lista. Kada spojite ovo dvoje, rezime list može sabrati detaljni list preko imena koje nikada ne pominje doslovnu adresu, što je upravo ono što želite u radnoj svesci koju generator sastavlja, a računovođa kasnije proverava.

HotXLS, losLab-ova nativna Delphi biblioteka za XLS i XLSX fajlove, izlaže tabelu imena za oba formata sa pravom pristupa za kreiranje, pronalaženje i brisanje, uz mašinu za formule koja razrešava imena i reference između listova tokom samog procesa. Ova dva formata zadržavaju zasebne hijerarhije klasa, a razlike između njihovih API-ja za imena predstavljaju deo koji često pravi probleme u kodu portovanom sa jednog formata na drugi.

Dva skladišta imena koja ne dele isti interfejs

Na XLS strani, TXLSWorkbook.GetNames vraća kolekciju IXLSNames čije preopterećenje Add(Name, RefersTo, Visible) upisuje ime u BIFF tabelu imena. Pojedinačni unos se vraća kao objekat IXLSName koji sadrži Name, RefersTo, razrešeni RefersToRange i metodu Delete. Na XLSX strani, TXLSXWorkbook.DefinedNames je kolekcija TXLSXDefinedNames sa metodama Add, FindByName i DeleteByName.

Konvencije pretraživanja se razlikuju na način koji se manifestuje tokom portovanja pre nego u vreme kompajliranja. Podrazumevano svojstvo Item u XLS kolekciji prihvata Variant, pa se i Names[0] i Names['TaxRate'] razrešavaju preko njega. XLSX kolekcija nema takvo podrazumevano svojstvo; poziva se FindByName('TaxRate'), što vraća nil kada ime ne postoji. Kod napisan za jednu fasadu kompajlira se za drugu samo slučajno, a greška se obično manifestuje kao nil pristup u vreme izvršavanja (runtime nil access), a ne kao crveno podvučena linija u IDE-u.

Opseg je prva odluka, a ne opcija koju dodajete kasnije

Definisano ime može imati opseg na nivou radne sveske (workbook-scoped), čime je vidljivo formulama na svakom listu, ili na nivou radnog lista (sheet-scoped), čime je vidljivo samo formulama na listu kojem pripada. U XLSX API-ju ova razlika se svodi na jedan opcioni parametar. DefinedNames.Add(AName, AFormula) kreira ime na nivou radne sveske, dok Add(AName, AFormula, ASheetIndex) povezuje ime sa jednim listom. Kada se vrednost čita nazad, TXLSXDefinedName.SheetIndex vraća -1 za opseg na nivou radne sveske, a u suprotnom indeks radnog lista (koji počinje od 0).

Opseg služi i kao vaša politika izbegavanja konflikata, i to je razlog zašto treba da ga definišete pre nego što napišete prvo ime. Excel dozvoljava lokalno ime lista Total na svakom listu plus Total na nivou radne sveske, a formula na određenom listu prvo razrešava lokalno ime. Generisane radne sveske bi trebalo namerno da se oslanjaju na ovo ponašanje. Poslovne pretpostavke koje koristi više listova, kao što su poreske stope, kursne liste i izveštajni periodi, pripadaju opsegu radne sveske. Pomoćni opsezi koje referenciraju samo formule jednog lista bezbedniji su sa opsegom lista, gde ih ništa ne može preklopiti niti oni mogu preklopiti nešto 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;

Definisano ime ne mora da ukazuje na opseg. TaxRate iz gornjeg primera se odnosi na čistu konstantu 0.08, i to je najčistiji način da se objavi poslovna pretpostavka. Ono se pojavljuje samo jednom u Excel-ovom menadžeru imena (Name Manager), svaka formula ga referencira simbolički, a promena stope u sledećem kvartalu zahteva izmenu samo jedne linije koda u generatoru, umesto pretrage kroz četrnaest sastavljenih nizova formula.

Znak jednakosti koji pripada samo jednoj strani

Kanal za unos formula je mesto gde portovani kod najčešće puca, jer se dve fasade ne slažu oko znaka jednakosti. XLS ćelije primaju formule preko svojstva Value sa početnim znakom =. XLSX ćelije imaju namensko svojstvo Formula koje prihvata izraz bez prefiksa. Ako upišete '=SUM(A1:A10)' u TXLSXCell.Formula, znak jednakosti postaje deo sačuvanog teksta izraza umesto markera, i fajl se neće ponašati na isti način kao što se isti taj string 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 isečak koda pokazuje još dve specifičnosti XLS strane. Kolekcija listova počinje od 1, pa je Sheets[1] prvi list, za razliku od XLSX Sheets[0] gde indeksiranje počinje od 0. Takođe, treći parametar metode Add kreira skriveno ime: prisutno u fajlu i upotrebljivo za formule, ali nevidljivo u Excel-ovom menadžeru imena. Skrivena imena su pravi izbor za generator-interno funkcionisanje koje krajnji korisnici nikada ne bi trebalo da menjaju ili brišu greškom.

Reference između listova i šta se dešava kada se redovi pomeraju

Obe mašine za formule prihvataju standardnu sintaksu za povezivanje radnih listova. Jednostavni nazivi listova se kvalifikuju direktno kao Data!A1; naziv sa razmacima ili znakovima interpunkcije zahteva jednostruke navodnike, kao u 'Sheet With Space'!A1. Unutar RefersTo teksta imena, gotovo uvek koristite apsolutne reference kao što je Data!$A$2:$D$100. Relativna referenca unutar definisanog imena se razrešava u odnosu na ćeliju koja je koristi, što je namerna funkcionalnost Excel-a i pouzdan izvor zabune kada se aktivira slučajno.

Strukturne izmene su mesto gde vođenje evidencije između listova pokazuje svoju vrednost, a XLSX strana održava imena konzistentnim tokom njih. Metode InsertRows i DeleteRows pomeraju opsege definisanih imena zajedno sa ćelijama, spajanjima, hiperlinkovima i sidrima grafikona, tako da ime koje ukazuje na Data!$A$2:$D$100 i dalje pokriva blok podataka nakon što generator otvori prazan prostor iznad njega. Formule dolaze sa jednim dokumentovanim upozorenjem: umetanje redova prilagođava samo reference koje ciljaju list koji se menja. Formula Summary koja referencira Data!D2:D100 biće prepisana kada se redovi umetnu u Data, što je slučaj koji obično želite. Verifikujte to umesto da pretpostavljate, jer će vam mašina to jeftino potvrditi:

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

Calculate procenjuje proizvoljan izraz u odnosu na trenutno stanje radne sveske bez čuvanja bilo čega, što ga čini prirodnim primitivom za proveru (assertion) u testovima generatora. Izračunajte očekivani agregat iz izvornih podataka u Pascal-u, procenite sopstvenu formulu radne sveske i uporedite to dvoje. Članak o mašini za formule pokriva šta mašina procenjuje, kada i kako je proširiti prilagođenim funkcijama.

_xlnm imena koja poseduje sloj svojstava

Otvorite tabelu imena generisanog fajla u niskonaponskom inspektoru i naći ćete unose koje nikada niste napisali: _xlnm.Print_Area, _xlnm.Print_Titles i slične. Ovo je način na koji OOXML (ECMA-376 / ISO 29500) čuva oblasti za štampu i ponovljene redove naslova, kao definisana imena sa rezervisanim identifikatorima. HotXLS upravlja njima preko namenskih svojstava radnog lista, tako da podešavanje PrintArea ili PrintTitleRows automatski upisuje odgovarajući _xlnm.* unos za vas.

Zamka je u ručnom posezanju za tim rezervisanim namenskim prostorom. Ako dodate unos _xlnm.Print_Area preko DefinedNames.Add dok istovremeno podešavate svojstvo PrintArea, radna sveska će nositi dve konfliktne definicije za jedno rezervisano ime, što je stanje koje Excel rešava na načine na koje se nijedan proizvod ne bi trebao oslanjati. Tretirajte svaki identifikator koji počinje sa _xlnm. kao deo koji pripada sloju svojstava. Da biste pregledali podešavanja štampe, čitajte svojstva, a ne tabelu imena. Članak o zaštiti i podešavanju stranice pokriva svojstva oblasti za štampu u kontekstu.

Dve granice koje vredi znati pre nego što se opredelite za dizajn

Definisana imena se ne prenose preko praktičnog mosta za konverziju XLS u XLSX. Metoda SaveXLSWorkbookAsXLSX kopira sadržaj ćelija i osnovno formatiranje, a tabela imena nije na njenoj dokumentovanoj listi za kopiranje, tako da radna sveska koja zavisi od svojih imena gubi ta imena prilikom prelaska. Ponovo kreirajte imena preko DefinedNames.Add nakon konverzije. Taj korak je lakši nego što zvuči, jer vam pruža priliku da normalizujete njihove opsege umesto da prenosite šta god je XLS fajl slučajno imao.

Druga granica je odstupanje između stringova formula i naziva listova. Excel prepisuje reference listova unutar formula i imena tokom interaktivnog preimenovanja, tako da fajlovi koje korisnik menja u Excel-u ostaju konzistentni sami po sebi. Problem je na strani generatora: kada Pascal kod sastavlja stringove formula od literala naziva lista, preimenovanje lista na jednom mestu i zaboravljanje na drugom stvara referencu na list koji više ne postoji. Držite naziv lista u jednoj Delphi konstanti i prosledite ga i metodi Sheets.Add i vašem sklopu formula, i to dvoje se nikada neće razlikovati. To je isti instinkt koji nalaže imenovanje izlaznih ćelija izveštaja umesto hardkodiranja adresa: šablon čija je ćelija za ukupnu vrednost imenovana nastavlja da radi nakon što dizajner umetne tri reda iznad nje, dok generator koji piše u doslovnu adresu B17 tiho upisuje svoj broj na pogrešno mesto. Članak o generisanju izveštaja na osnovu šablona nadograđuje se upravo na taj šablon.

Kompletan API za definisana imena za oba formata, zajedno sa referencom mašine za formule, isporučuje se uz HotXLS komponentu.