Pavadintos sritys ir formulės tarp lapų „Delphi“ programoje

Pavadinta sritis (angl. defined name) yra etiketė, atstovaujanti konstantą, langelių rėžį ar formulės išraišką, įrašyta vieną kartą darbo knygoje ir naudojama kaip simbolinė nuoroda visur, kur jos reikia. Įrašykite TaxRate formulėje, ir skaičiavimo variklis ją pakeis tuo, kas nurodyta pavadinimo apibrėžime, pavyzdžiui, konstanta 0.08 arba rėžiu Data!$A$2:$D$100. Nuoroda tarp lapų (angl. cross-sheet reference) yra panaši idėja: Data!D2 pasiekia kitame lape esantį langelį, prieš adresą nurodant lapo pavadinimą. Sujungus šias dvi funkcijas, suvestinės lapas gali apskaičiuoti detalios ataskaitos lapo sumą per pavadinimą, kuriame visiškai nenaudojami tiesioginiai adresai. Tai yra būtent tai, ko tikimasi iš darbo knygos, kurią sugeneruoja programa, o vėliau tikrina buhalteris

„HotXLS“, vietinė „losLab“ sukurta „Delphi“ biblioteka, skirta XLS ir XLSX failams, atveria abiejų formatų pavadinimų lentelę su galimybe juos kurti, rasti bei trinti, taip pat siūlo formulių skaičiavimo variklį, kuris apdoroja pavadinimus ir nuorodas tarp lapų programos vykdymo metu. Šie du formatai naudoja skirtingas klasių hierarchijas, todėl jų pavadinimų API skirtumai yra ta dalis, kuri dažniausiai sukelia problemų perkeliant kodą iš vieno formato į kitą

Dvi pavadinimų saugyklos be bendros sąsajos

XLS sąsajoje funkcija TXLSWorkbook.GetNames grąžina IXLSNames rinkinį, kurio perkrova Add(Name, RefersTo, Visible) įrašo pavadinimą į BIFF pavadinimų lentelę. Kiekvienas įrašas grąžinamas kaip IXLSName objektas su savybėmis Name, RefersTo, išspręstu rėžiu RefersToRange ir metodu Delete. XLSX pusėje TXLSXWorkbook.DefinedNames is a TXLSXDefinedNames rinkinys su metodais Add, FindByName ir DeleteByName

Paieškos konvencijos išsiskiria taip, kad tai pastebima tik vykdymo metu, o ne kompiliavimo metu. XLS rinkinio numatytoji savybė Item priima Variant tipo parametrą, todėl tiek Names[0], tiek Names['TaxRate'] veikia sėkmingai. XLSX rinkinys tokios savybės neturi: čia reikia iškviesti FindByName('TaxRate'), kuris grąžina nil, jei pavadinimo nėra. Vienam fasadui parašytas kodas kitame gali susikompiliuoti tik atsitiktinai, o klaida pasireikš kaip nil nuorodos klaida programos veikimo metu, o ne kaip klaidos pabraukimas IDE aplinkoje

Galiojimo sritis yra pirminis sprendimas, o ne vėliau pridedama vėliavėlė

Pavadinta sritis gali galioti visos darbo knygos lygmeniu (matoma visų lapų formulėms) arba tik konkretaus lapo lygmeniu (matoma tik to lapo formulėms). XLSX API šis skirtumas nustatomas vienu papildomu parametru. Iškvietimas DefinedNames.Add(AName, AFormula) sukuria knygos lygmens pavadinimą, o Add(AName, AFormula, ASheetIndex) susieja jį su konkrečiu lapu. Nuskaitant reikšmę, TXLSXDefinedName.SheetIndex grąžina -1, jei galiojimo sritis yra visa darbo knyga, ir lapo indeksą (nuo 0) kitais atvejais

Galiojimo sritis taip pat padeda spręsti pavadinimų konfliktus, todėl ją reikėtų pasirinkti prieš sukuriant pirmąjį pavadinimą. „Excel“ leidžia turėti vietinį pavadinimą Total kiekviename lape bei bendrą knygos lygmens pavadinimą Total, o formulė konkrečiame lape pirmiausia naudos vietinį pavadinimą. Sugeneruotose darbo knygose verta tuo pasinaudoti. Verslo taisyklės, kurias naudoja keli lapai (pavyzdžiui, mokesčių tarifai, valiutų kursai ir ataskaitinis laikotarpis), turėtų galioti visos darbo knygos lygmeniu. Pagalbiniai rėžiai, kuriuos naudoja tik vieno lapo formulės, yra saugesni, kai apibrėžiami tik to lapo lygmeniu: taip išvengiama konfliktų su kitais lapais

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;

Pavadinta sritis nebūtinai turi rodyti į rėžį. Pavyzdyje naudotas TaxRate rodo į paprastą konstantą 0.08, ir tai yra pats švariausias būdas apibrėžti verslo taisyklę. Ji rodoma vieną kartą „Excel“ vardų tvarkyklėje, kiekviena formulė į ją kreipiasi simboliškai, o mokesčių tarifo pakeitimas kitam ketvirčiui reikalauja pakeisti tik vieną kodo eilutę generatoriuje, užuot ieškojus reikšmės keliolikoje sugeneruotų formulių eilučių

Lygybės ženklas, kuris reikalingas tik vienoje pusėje

Formulės įvedimas yra ta vieta, kur perkeltas kodas sugenda dažniausiai, nes abu fasadai skirtingai traktuoja lygybės ženklą. XLS langeliai priima formules per Value savybę, kurioje turi būti lygybės ženklas pradžioje =. XLSX langeliai turi atskirą savybę Formula, kuri priima išraišką be jokio priešdėlio. Jei įrašysite '=SUM(A1:A10)' į TXLSXCell.Formula, lygybės ženklas taps saugomos išraiškos teksto dalimi, o ne formulės pradžios žymekliu, ir failas neveiks taip, kaip tikėtasi pagal XLS logiką

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;

Šis pavyzdys atskleidžia dar du XLS pusės ypatumus. Lapų rinkinys indeksuojamas nuo 1, todėl Sheets[1] yra pirmasis lapas, priešingai nei XLSX sąsajoje, kur naudojamas indeksas nuo 0 (Sheets[0]). Trečiasis Add parametras sukuria paslėptą pavadinimą: jis yra faile ir gali būti naudojamas formulėse, tačiau lieka nematomas „Excel“ vardų tvarkyklėje. Paslėpti pavadinimai yra puikus įrankis vidinei generatoriaus logikai, kurios galutiniai vartotojai neturėtų keisti ar netyčia ištrinti

Nuorodos tarp lapų ir kas nutinka perkeliant eilutes

Abu formulių varikliai priima standartinę nuorodų tarp lapų sintaksę. Paprasti lapų pavadinimai nurodomi tiesiogiai, pavyzdžiui, Data!A1. Pavadinimas su tarpais ar skyrybos ženklais reikalauja viengubų kabučių: 'Sheet With Space'!A1. Pavadinimo RefersTo tekste beveik visada naudokite absoliučiąsias nuorodas, tokias kaip Data!$A$2:$D$100. Reliatyvi nuoroda pavadintoje srityje yra interpretuojama priklausomai nuo ją naudojančio langelio padėties – tai yra numatyta „Excel“ funkcija, tačiau ji dažnai sukelia painiavos, kai suveikia netyčia

Struktūriniai pakeitimai yra ta sritis, kur nuorodų sekimas atskleidžia savo vertę, o XLSX sąsaja išlaiko pavadinimų nuoseklumą atliekant šiuos veiksmus. Funkcijos InsertRows ir DeleteRows perkelia pavadintų sričių rėžius kartu su langeliais, sujungimais, nuorodomis bei diagramų inkarais, todėl pavadinimas, rodantis į Data!$A$2:$D$100, vis tiek teisingai apims duomenų bloką, kai virš jo bus įterptos naujos eilutės. Formulėms galioja vienas svarbus aspektas: eilučių įterpimas koreguoja tik tas nuorodas, kurios nukreiptos į redaguojamą lapą. Summary formulė, rodanti į Data!D2:D100, bus atnaujinta, kai eilutės bus įterpiamos į Data lapą – būtent to dažniausiai ir reikia. Tuo galite lengvai įsitikinti, nes skaičiavimo variklis pateiks rezultatą:

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

Funkcija Calculate įvertina bet kokią išraišką pagal dabartinę darbo knygos būseną nieko neišsaugodama, todėl tai yra puikus įrankis automatizuotiems generatoriaus testams. Apskaičiuokite laukiamą reikšmę „Pascal“ kode, įvertinkite darbo knygos formulę ir palyginkite abu rezultatus. Straipsnyje apie formulių skaičiavimo variklį aprašoma, ką ir kada variklis vertina bei kaip jį papildyti nestandartinėmis funkcijomis

Vidiniai _xlnm pavadinimai, priklausantys savybių sluoksniui

Atidarę sugeneruoto failo pavadinimų lentelę su žemo lygio inspektoriumi, rasite įrašų, kurių patys niekada nerašėte: _xlnm.Print_Area, _xlnm.Print_Titles ir panašius. Taip OOXML (ECMA-376 / ISO 29500) formatas saugo spausdinimo sritis ir pasikartojančias antraščių eilutes, naudodamas sisteminius pavadinimus su rezervuotais identifikatoriais. „HotXLS“ šiuos nustatymus valdo per atskiras darbalapio savybes, todėl nustačius PrintArea arba PrintTitleRows, atitinkamas _xlnm.* įrašas sugeneruojamas automatiškai

Klaida būtų bandyti šią rezervuotą sritį redaguoti rankiniu būdu. Pridėjus _xlnm.Print_Area įrašą per DefinedNames.Add ir tuo pačiu metu nustačius savybę PrintArea, darbo knygoje bus sukurti du konfliktuojantys to paties rezervuoto pavadinimo apibrėžimai, o „Excel“ tokius konfliktus sprendžia nenuspėjamai. Laikykite, kad visi identifikatoriai, prasidedantys _xlnm., priklauso savybių sluoksniui. Norėdami patikrinti spaudinio nustatymus, skaitykite savybes, o ne pavadinimų lentelę. Ši tema plačiau nagrinėjama straipsnyje apie apsaugą ir puslapio nustatymus

Dvi ribos, kurias verta žinoti prieš kuriant architektūrą

Pavadintos sritys nėra nukopijuojamos naudojant XLS-to-XLSX tiltą. Funkcija SaveXLSWorkbookAsXLSX perkelia langelių turinį ir pagrindinį formatavimą, tačiau pavadinimų lentelė nėra įtraukta į palaikomų kopijuoti elementų sąrašą, todėl darbo knygoje buvusios pavadintos sritys bus prarastos. Po konvertavimo sukurkite pavadinimus iš naujo naudodami DefinedNames.Add. Šis žingsnis yra paprastesnis nei atrodo, be to, tai suteikia galimybę suvienodinti jų galiojimo sritis, užuot aklai nešiojus tai, kas buvo XLS faile

Kita rizika yra nesutapimai tarp formulių eilučių ir lapų pavadinimų. „Excel“ automatiškai atnaujina lapų nuorodas formulėse ir pavadinimuose, kai vartotojas keičia lapo pavadinimą programoje. Tačiau generatoriaus pusėje situacija kitokia: kai „Pascal“ kodas surenka formulės eilutę naudodamas tekstinę lapo pavadinimo konstantą, pakeitus lapo pavadinimą vienoje vietoje ir pamiršus kitoje, bus sukurta nuoroda į neegzistuojantį lapą. Saugokite lapo pavadinimą viename „Delphi“ kintamajame ar konstantoje ir perduokite jį tiek Sheets.Add iškvietimui, tiek formulių surinkimo logikai – taip išvengsite nesutapimų. Dėl tos pačios priežasties rekomenduojama ataskaitose naudoti pavadintus langelius, o ne fiksuotus adresus: šablonas, kuriame sumos langelis yra pavadintas, veiks teisingai net dizaineriui įterpus kelias eilutes virš jo, o generatorius, rašantis į fiksuotą langelį B17, tyliai įrašys skaičių ne ten, kur reikia. Šis modelis išsamiau aprašytas straipsnyje apie ataskaitų generavimą pagal šablonus

Pilną abiejų formatų pavadintų sričių API aprašymą kartu su formulių skaičiavimo variklio dokumentacija rasite „HotXLS Component“ produkto puslapyje HotXLS Component