HotXLS definiált nevek és lapok közötti képletek Delphiben

A definiált név olyan címke, amely egy konstans, egy cellatartomány vagy egy képletkifejezés helyett áll, egyszer tárolódik a munkafüzetben, és mindenütt jelképesen hivatkoznak rá, ahol szükség van rá. Írja be a TaxRate nevet egy képletbe, és a motor arra oldja fel, amit a név definíciója tart, legyen az a 0.08 literál vagy a Data!$A$2:$D$100 tartomány. A lapok közötti hivatkozás az erre merőleges gondolat: a Data!D2 úgy ér el egy cellát egy másik lapon, hogy a címet lapnévvel minősíti. Tegye össze a kettőt, és egy összesítő lap olyan néven át összegezhet egy részletező lapot, amely soha nem említ szó szerinti címet, márpedig pontosan ez kell egy olyan munkafüzetben, amelyet egy generátor rak össze, és később egy könyvelő auditál

A HotXLS, a losLab natív Delphi könyvtára XLS és XLSX fájlokhoz, mindkét formátum névtábláját elérhetővé teszi létrehozási, keresési és törlési hozzáféréssel, mellette pedig olyan képletmotorral, amely a neveket és a lapok közötti hivatkozásokat a folyamaton belül oldja fel. A két formátum külön osztályhierarchiát tart, és a névkezelő API-k közötti eltérések buktatják el az egyikről a másikra átvitt kódot

Két névtár, amely nem osztozik felületen

Az XLS oldalon a TXLSWorkbook.GetNames egy IXLSNames gyűjteményt ad vissza, amelynek Add(Name, RefersTo, Visible) túlterhelése nevet ír a BIFF névtáblába. Az egyes bejegyzések IXLSName objektumként jönnek vissza, amelyek Name, RefersTo, feloldott RefersToRange tagokat és Delete metódust hordoznak. Az XLSX oldalon a TXLSXWorkbook.DefinedNames egy TXLSXDefinedNames gyűjtemény Add, FindByName és DeleteByName tagokkal

A keresési egyezmények úgy térnek el, hogy az fordításkor nem, csak átvitel közben bukkan felszínre. Az XLS gyűjtemény alapértelmezett Item tulajdonsága Variant értéket fogad, így a Names[0] és a Names['TaxRate'] egyaránt feloldódik ellene. Az XLSX gyűjteménynek nincs ilyen alapértelmezett tulajdonsága; a FindByName('TaxRate') hívást használja, amely nil értéket ad vissza, ha a név hiányzik. Az egyik homlokzatra írt kód csak véletlenül fordul le a másikkal, a hiba pedig inkább futásidejű nil hozzáférésként jelenik meg, mint piros hullámvonalként az IDE-ben

A hatókör az első döntés, nem utólag hozzáadott jelző

A definiált név vagy munkafüzet-szintű, vagyis minden lap képletei számára látható, vagy lapszintű, vagyis csak az őt birtokló lap képletei látják. Az XLSX API-ban a különbség egyetlen választható paraméter. A DefinedNames.Add(AName, AFormula) munkafüzet-szintű nevet hoz létre, míg az Add(AName, AFormula, ASheetIndex) egy laphoz köti. Visszaolvasáskor a TXLSXDefinedName.SheetIndex munkafüzet-hatókörnél -1 értéket, egyébként a 0-alapú lapindexet adja vissza

A hatókör egyben az ütközési szabályzata is, és ezért kell eldönteni, mielőtt az első nevet megírja. Az Excel megengedi, hogy minden lapon legyen helyi Total név egy munkafüzet-szintű Total mellett, és egy adott lap képlete előbb a helyit oldja fel. Az előállított munkafüzeteknek szándékosan érdemes erre támaszkodniuk. Azok az üzleti feltevések, amelyeket több lap fogyaszt, például az adókulcsok, a devizaárfolyamok és a jelentési időszak, munkafüzet-szintre valók. Azok a segédtartományok, amelyekre csak egyetlen lap képletei hivatkoznak, biztonságosabbak lapszinten, ahol semmi nem árnyékolhatja le őket, és ők sem árnyékolhatnak le semmit

var
  Book: TXLSXWorkbook;
  Data, Summary: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Summary := Book.Sheets.Add('Summary');
    // ... a Data!A2:D100 feltöltése részletsorokkal ...

    Book.DefinedNames.Add('TaxRate', '0.08');                // munkafüzet-hatókör, konstans
    Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100');  // munkafüzet-hatókör, tartomány
    Book.DefinedNames.Add('LocalNote', 'Summary!$B$1', 1);   // csak az 1-es lapindexre szűkítve

    // Az XLSX képletek nem kapnak kezdő '=' jelet
    Summary.Cells[2, 2].Formula := 'SUM(Data!D2:D100)*TaxRate';
    Book.SaveAs('model.xlsx');
  finally
    Book.Free;
  end;
end;

A definiált névnek nem kell tartományra mutatnia. A fenti TaxRate a puszta 0.08 konstansra hivatkozik, és ez a legtisztább mód egy üzleti feltevés közzétételére. Egyszer jelenik meg az Excel Névkezelőjében, minden képlet jelképesen hivatkozik rá, a jövő negyedévi kulcsváltozás pedig egysoros szerkesztés a generátorban tizennégy összeállított képletkarakterlánc átfésülése helyett

Az egyenlőségjel, amely csak az egyik oldalra való

A képletbeviteli csatorna az a pont, ahol az átvitt kód a leggyakrabban törik el, mert a két homlokzat nem ért egyet az egyenlőségjelben. Az XLS cellák a Value tagon át kapják a képleteket, kezdő = jellel. Az XLSX celláknak külön Formula tulajdonságuk van, amely az előtag nélkül veszi a kifejezést. Írja a '=SUM(A1:A10)' értéket a TXLSXCell.Formula tagba, és az egyenlőségjel jelölő helyett a tárolt kifejezésszöveg részévé válik, a fájl pedig nem úgy viselkedik, ahogy ugyanez a karakterlánc az XLS oldalon tette

var
  Book: IXLSWorkbook;   // felületszámlált: ne hívjon rá Free-t
  Names: IXLSNames;
begin
  Book := TXLSWorkbook.Create;
  // tegyük fel, hogy egy 'Data' nevű lap már tartja a részletsorokat
  Names := Book.GetNames;
  Names.Add('TaxRate', '0.08');
  Names.Add('Helper', 'Data!$A$2:$A$100', False);  // False = rejtve a Névkezelő elől

  // Az XLS képletek a Value tagon át mennek, '=' előtaggal
  Book.Sheets[1].Cells.Item[2, 2].Value := '=SUM(Data!A2:A100)*TaxRate';
  Book.SaveAs('model.xls');
end;

Ez a részlet két további XLS oldali furcsaságot mutat. A lapgyűjtemény 1-alapú, így a Sheets[1] az első lap, szemben a 0-alapú XLSX Sheets[0] hivatkozással. A harmadik Add paraméter pedig rejtett nevet hoz létre: jelen van a fájlban, a képletek használhatják, mégis láthatatlan az Excel Névkezelőjében. A rejtett nevek a helyes eszköz a generátor belső szerelvényeihez, amelyeket a végfelhasználóknak soha nem szabad véletlenül szerkeszteniük vagy törölniük

Lapok közötti hivatkozások, és mi történik, amikor a sorok mozdulnak

Mindkét képletmotor elfogadja a szokásos lapok közötti szintaxist. Az egyszerű lapnevek közvetlenül minősítenek Data!A1 alakban; a szóközt vagy írásjelet tartalmazó név aposztrófot igényel, mint a 'Sheet With Space'!A1 esetben. Egy név RefersTo szövegében szinte mindig abszolút hivatkozásokhoz nyúljon, például Data!$A$2:$D$100 alakhoz. A definiált néven belüli relatív hivatkozás az őt használó cellához képest oldódik fel, ami az Excel szándékos képessége, és megbízható zavarforrás, amikor véletlenül sül el

A szerkezeti szerkesztéseknél térül meg a lapok közötti könyvelés, és az XLSX oldal ezeken át következetesen tartja a neveket. Az InsertRows és a DeleteRows a cellákkal, egyesítésekkel, hiperhivatkozásokkal és diagramhorgonyokkal együtt tolja el a definiált nevek tartományait, így a Data!$A$2:$D$100 értékre mutató név akkor is lefedi az adatblokkot, ha a generátor rést nyit fölötte. A képletekhez egy dokumentált kikötés jár: a sorbeszúrás csak azokat a hivatkozásokat igazítja, amelyek a szerkesztett lapra irányulnak. Egy Data!D2:D100 értékre hivatkozó Summary képlet átíródik, amikor sorok kerülnek a Data lapra, és rendszerint ezt akarja. Ezt inkább ellenőrizze, mint feltételezze, mert a motor olcsón megmondja:

// a számolómotor a folyamaton belül oldja fel a neveket és a lapok közötti hivatkozásokat
V := Book.Calculate('SUM(Data!D2:D100)*TaxRate');
if VarIsNumeric(V) then
  Log('net total checks out: ' + FloatToStr(V));

A Calculate tetszőleges kifejezést értékel ki a munkafüzet aktuális állapotán, mentés nélkül, ami a generátortesztek természetes állításprimitívjévé teszi. Számolja ki a várt összesítést a forrásadatokból Pascalban, értékelje ki a munkafüzet saját képletét, és hasonlítsa össze a kettőt. A képletmotorról szóló cikk tárgyalja, mit értékel ki a motor, mikor, és hogyan bővíthető egyéni függvényekkel

Az _xlnm nevek, amelyeket a tulajdonságréteg birtokol

Nyissa meg egy előállított fájl névtábláját alacsony szintű vizsgálóban, és olyan bejegyzéseket talál, amelyeket sosem írt: _xlnm.Print_Area, _xlnm.Print_Titles és rokonaik. Az OOXML (ECMA-376 / ISO 29500) így tárolja a nyomtatási területeket és az ismételt címsorokat, vagyis fenntartott azonosítójú definiált nevekként. A HotXLS ezeket célzott munkalap-tulajdonságokon át kezeli, így a PrintArea vagy a PrintTitleRows beállítása Ön helyett írja ki a megfelelő _xlnm.* bejegyzést

A csapda az, ha kézzel nyúl ebbe a fenntartott névtérbe. Adjon hozzá egy _xlnm.Print_Area bejegyzést a DefinedNames.Add hívással, miközben a PrintArea tulajdonságot is beállítja, és a munkafüzet két ütköző definíciót hordoz egyetlen fenntartott névre, ezt az állapotot pedig az Excel olyan módokon oldja fel, amelyekre semmilyen terméknek nem szabad építenie. Minden _xlnm. előtaggal kezdődő azonosítót tekintsen a tulajdonságréteghez tartozónak. A nyomtatási beállítás vizsgálatához a tulajdonságokat olvassa, ne a névtáblát. A védelemről és az oldalbeállításról szóló cikk összefüggésében tárgyalja a nyomtatási terület tulajdonságait

Két határ, amelyet érdemes ismerni, mielőtt egy tervezés mellett elköteleződik

A definiált nevek nem utaznak együtt a kényelmi XLS-ből XLSX-be vezető hídon. A SaveXLSWorkbookAsXLSX a cellatartalmat és az alapvető formázást másolja, a névtábla pedig nincs a dokumentált másolási listáján, így az a munkafüzet, amely a neveire épült, elveszíti őket az átkelésben. Az átalakítás után hozza létre újra a neveket a DefinedNames.Add hívással. Ez a lépés kevésbé nyűg, mint amilyennek hangzik, mert alkalmat ad a hatókörök szabályosítására ahelyett, hogy átvinné azt, ami az XLS fájlban éppen volt

A másik határ a képletkarakterláncok és a lapnevek elsodródása. Az Excel az interaktív átnevezés során átírja a képletekben és nevekben lévő laphivatkozásokat, így azok a fájlok, amelyeket a felhasználó Excelben szerkeszt, maguktól következetesek maradnak. A kitettség a generátor oldalán van: amikor Pascal kód lapnév-literálból állít össze képletkarakterláncokat, a lap átnevezése az egyik helyen és elfelejtése a másikon olyan lapra hivatkozik, amely már nem létezik. Tartsa a lapnevet egyetlen Delphi konstansban, és adja át mind a Sheets.Add hívásnak, mind a képletösszeállításnak, így a kettő soha nem mondhat ellent egymásnak. Ugyanez az ösztön szól amellett, hogy egy jelentés kimeneti celláit inkább nevezze el, mint hogy címeket égessen a kódba: az a sablon, amelynek összegcellája névvel bír, azután is működik, hogy egy tervező három sort szúr fölé, míg az a generátor, amely a szó szerinti B17 cellába ír, csendben rossz helyre teszi a számát. A sablonalapú jelentéskészítésről szóló cikk pontosan erre a mintára épít

A mindkét formátumra vonatkozó teljes definiáltnév-API a képletmotor hivatkozásával együtt a HotXLS Delphi Component csomaggal érkezik