LlosLab blog o razvoju softveraInženjerske beleške za programere PDF-a, Delphi-ja i softvera za dokumente

Tehnički članak

Definisana imena i međulistne formule u Delphi-ju pomoću HotXLS-a

HotXLS komponenta

Definisano ime (defined name) je oznaka (labela) koja zamenjuje konstantu, opseg ćelija ili izraz formule, a koja se čuva jednom u radnoj svesci i referencira simbolički svuda gde je to potrebno. Napišite TaxRate u formuli i mehanizam će to razrešiti na ono što definicija tog imena sadrži, bilo da je to doslovna vrednost 0.08 ili opseg Data!$A$2:$D$100. Međulistna referenca (cross-sheet reference) je ortogonalna ideja: izrazom Data!D2 pristupate ćeliji na drugom listu tako što adresu kvalifikujete nazivom lista. Spojite ove dve stvari i zbirni list može sabirati list sa detaljima preko imena koje nikada ne pominje doslovnu adresu, što je upravo ono što želite u radnoj svesci koju generator sklapa a računovođa kasnije proverava

HotXLS, losLab-ova izvorna Delphi biblioteka za XLS i XLSX datoteke, izlaže tabelu imena za oba formata sa mogućnostima kreiranja, pronalaženja i brisanja, uz mehanizam formula koji razrešava imena i međulistne reference u samom procesu. Ova dva formata zadržavaju odvojene hijerarhije klasa, a razlike između njihovih API-ja za imena su deo koji najčešće pravi probleme pri prenosu koda sa jednog 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 unosi se vraćaju kao objekti tipa IXLSName koji nose svojstva Name, RefersTo, razrešeni RefersToRange i metodu Delete. Na XLSX strani, TXLSXWorkbook.DefinedNames je kolekcija tipa TXLSXDefinedNames sa metodama Add, FindByName i DeleteByName

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

Opseg (scope) je prva odluka, a ne opcija koju dodajete kasnije

Definisano ime ima opseg ili na nivou radne sveske (workbook-scoped), gde je vidljivo formulama na svakom listu, ili na nivou radnog lista (sheet-scoped), gde je vidljivo samo formulama na listu kojem pripada. U XLSX API-ju ta razlika se svodi na jedan opcioni parametar. DefinedNames.Add(AName, AFormula) kreira ime na nivou radne sveske, dok ga Add(AName, AFormula, ASheetIndex) povezuje sa jednim listom. Prilikom čitanja, TXLSXDefinedName.SheetIndex vraća -1 za opseg radne sveske, a u suprotnom vraća indeks lista u bazi od 0

Opseg ujedno služi i kao vaša politika izbegavanja konflikata, i to je razlog da ga definišete pre nego što napišete prvo ime. Excel dozvoljava lokalno ime Total na svakom listu, kao i ime Total na nivou radne sveske, pri čemu formula na određenom listu prvo razrešava lokalno ime. Generisane radne sveske bi trebalo da se namerno oslanjaju na ovo pravilo. Poslovne pretpostavke koje koristi više listova, kao što su poreske stope, devizni kursevi i izveštajni period, pripadaju opsegu radne sveske. Pomoćni opsezi koje referenciraju samo formule jednog lista bezbedniji su na nivou tog lista, gde ih ništa ne može zaseniti (shadow), niti oni mogu zaseniti bilo š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;

Definisano ime ne mora da pokazuje na opseg. TaxRate iz gornjeg koda se odnosi na čistu konstantu 0.08, i to je najčistiji način da se definiše poslovna pretpostavka. Ono se pojavljuje jednom u Excel-ovom menadžeru imena (Name Manager), svaka formula ga referencira simbolički, a promena stope u sledećem kvartalu je izmena u jednoj liniji koda generatora umesto pretrage kroz četrnaest spojenih stringova formula

Znak jednakosti koji pripada samo jednoj strani

Kanal za unos formula je mesto gde preneseni kod najčešće puca, jer se dve fasade ne slažu oko znaka jednakosti. Ćelije u XLS formatu primaju formule kroz svojstvo Value sa vodećim znakom =. Ćelije u XLSX formatu imaju namensko svojstvo Formula koje uzima izraz bez prefiksa. Napišite '=SUM(A1:A10)' u TXLSXCell.Formula i znak jednakosti postaje deo sačuvanog teksta izraza umesto da bude marker, pa se fajl neće ponašati na isti način kao 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;

Ovaj isečak koda prikazuje još dve specifičnosti na XLS strani. Kolekcija listova počinje od 1, pa je Sheets[1] prvi list, nasuprot XLSX kolekciji Sheets[0] koja počinje od 0. Takođe, treći parametar metode Add kreira skriveno ime: ono postoji u fajlu i formule ga mogu koristiti, ali je nevidljivo u Excel-ovom menadžeru imena. Skrivena imena su pravi alat za interne potrebe generatora koje krajnji korisnici nikada ne bi trebali slučajno da izmene ili obrišu

Međulistne reference i šta se dešava kada se redovi pomere

Oba mehanizma formula prihvataju standardnu međulistnu sintaksu. Obični nazivi listova se kvalifikuju direktno kao Data!A1, dok naziv sa razmacima ili znakovima interpunkcije zahteva jednostruke navodnike, kao u 'Sheet With Space'!A1. Unutar RefersTo teksta definisanog imena, skoro 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 funkcija Excel-a i pouzdan izvor zabune kada se aktivira slučajno

Strukturne izmene su mesto gde vođenje međulistnih referenci opravdava svoj trud, a XLSX strana održava imena konzistentnim kroz njih. Metode InsertRows i DeleteRows pomeraju opsege definisanih imena zajedno sa ćelijama, spajanjima, hipervezama i sidrima grafikona, tako da ime koje pokazuje na Data!$A$2:$D$100 i dalje pokriva blok podataka nakon što generator otvori prazninu iznad njega. Formule dolaze sa jednim dokumentovanim upozorenjem: ubacivanje redova prilagođava samo one reference koje ciljaju list koji se uređuje. Formula na listu Summary koja referencira Data!D2:D100 se prepisuje kada se redovi ubace u list Data, što je slučaj koji obično i želite. Verifikujte to umesto da pretpostavljate, jer će vam mehanizam 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));

Metoda Calculate evaluira proizvoljan izraz u odnosu na trenutno stanje radne sveske bez čuvanja ičega, što je čini prirodnim primitivom za potvrdu u testovima generatora. Izračunajte očekivani agregat iz izvornih podataka u Pascal-u, evaluirajte sopstvenu formulu radne sveske i uporedite ta dva rezultata. Članak o mehanizmu formula pokriva šta mehanizam evaluira, kada, i kako ga možete proširiti prilagođenim funkcijama

Nazivi _xlnm koje poseduje sloj svojstava

Otvorite tabelu imena generisanog fajla u inspektoru niskog nivoa i naći ćete unose koje nikada niste napisali: _xlnm.Print_Area, _xlnm.Print_Titles i njihove rođake. Na ovaj način OOXML (ECMA-376 / ISO 29500) čuva opsege štampe i ponovljene redove naslova, kao definisana imena sa rezervisanim identifikatorima. HotXLS upravlja njima preko namenskih svojstava radnog lista, tako da podešavanje svojstva PrintArea ili PrintTitleRows automatski upisuje odgovarajući _xlnm.* unos za vas

Zamka je u ručnom diranju tog rezervisanog prostora imena (namespace). Ako dodate unos _xlnm.Print_Area preko DefinedNames.Add dok istovremeno podešavate svojstvo PrintArea, radna sveska će nositi dve konfliktne definicije za isto 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. Za pregled podešavanja štampe čitajte svojstva, a ne tabelu imena. Članak o zaštiti i podešavanju stranice pokriva svojstva opsega štampe u kontekstu

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

Definisana imena se ne prenose kroz brzi most za konverziju XLS u XLSX. Metoda SaveXLSWorkbookAsXLSX kopira sadržaj ćelija i osnovno formatiranje, a tabela imena se ne nalazi na njenoj dokumentovanoj listi kopiranja, tako da radna sveska koja je zavisila od svojih imena gubi ta imena prilikom prelaska. Ponovo kreirajte imena preko DefinedNames.Add nakon konverzije. Taj korak je manji napor nego što zvuči, jer vam daje trenutak da normalizujete njihove opsege umesto da prenosite šta god da 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 uređuje u Excel-u sami ostaju konzistentni. Izloženost riziku je na strani generatora: kada Pascal kod sklapa stringove formula iz doslovnog naziva lista, preimenovanje lista na jednom mestu i zaboravljanje na drugom proizvodi referencu na list koji više ne postoji. Držite naziv lista u jednoj Delphi konstanti i prosleđujte ga i metodi Sheets.Add i vašem sklopu formula, i to dvoje se nikada neće razlikovati. To je isti instinkt koji govori u prilog imenovanja izlaznih ćelija izveštaja umesto čvrstog kodiranja adresa: šablon čija ćelija sa ukupnim iznosom ima ime nastavlja da radi i nakon što dizajner ubaci tri reda iznad nje, dok generator koji piše u doslovnu ćeliju B17 tiho upisuje svoj broj na pogrešno mesto. Članak o generisanju izveštaja na osnovu šablona gradi se upravo na tom obrascu

Kompletan API za definisana imena za oba formata, zajedno sa referencom mehanizma formula, isporučuje se sa HotXLS komponentom