Määritetyt nimet ja taulukoiden väliset kaavat Delphissä

Määritetty nimi on tunniste, joka korvaa vakion, solualueen tai kaavalausekkeen. Se tallennetaan työkirjaan kerran ja siihen viitataan nimellä kaikkialla, missä sitä tarvitaan. Kun kirjoitat kaavaan TaxRate, moottori ratkaisee sen nimen määrityksen mukaiseksi arvoksi, olipa se literaali 0.08 tai alue Data!$A$2:$D$100. Ristiviittaus on tästä erillinen käsite: Data!D2 hakee solun toiselta laskentataulukolta lisäämällä osoitteeseen taulukon nimen. Kun yhdistät nämä kaksi, yhteenvetotaulukko voi laskea yksityiskohtataulukon summan nimellä, jossa ei koskaan mainita kirjaimellista osoitetta. Juuri tätä tarvitset työkirjassa, jonka generaattori kokoaa ja jonka kirjanpitäjä tarkastaa myöhemmin

HotXLS, losLabin natiivi Delphi-kirjasto XLS- ja XLSX-tiedostoille, tarjoaa molempien muotojen nimitauluihin luonti-, haku- ja poistotoiminnot sekä kaavamoottorin, joka ratkaisee nimet ja ristiviittaukset prosessin sisällä. Muodoilla on erilliset luokkahierarkiat, ja niiden nimirajapintojen erot ovat juuri se kohta, jossa muodosta toiseen siirretty koodi tavallisesti kompastuu

Kaksi nimisäilöä, joilla ei ole yhteistä rajapintaa

XLS-puolella TXLSWorkbook.GetNames palauttaa IXLSNames-kokoelman, jonka Add(Name, RefersTo, Visible)-ylikuormitus kirjoittaa nimen BIFF-nimitauluun. Yksittäiset merkinnät palautuvat IXLSName-olioina, joilla on Name, RefersTo, ratkaistu RefersToRange ja Delete-menetelmä. XLSX-puolella TXLSXWorkbook.DefinedNames on TXLSXDefinedNames-kokoelma, jossa on Add, FindByName ja DeleteByName

Hakukäytännöt eroavat tavalla, joka tulee esiin siirrossa eikä käännösaikana. XLS-kokoelman oletusarvoinen Item-ominaisuus hyväksyy Variant-arvon, joten sekä Names[0] että Names['TaxRate'] ratkaistaan sen kautta. XLSX-kokoelmalla ei ole vastaavaa oletusominaisuutta, vaan käytät kutsua FindByName('TaxRate'), joka palauttaa nil-arvon, kun nimeä ei ole. Yhdelle julkisivulle kirjoitettu koodi kääntyy toista vasten vain sattumalta, ja virhe näkyy tavallisesti ajonaikaisena nil-viittauksena eikä IDE:n punaisena alleviivauksena

Laajuus on ensimmäinen päätös, ei myöhemmin lisättävä lippu

Määritetty nimi on joko työkirjanlaajuinen, jolloin se näkyy kaavoissa kaikilla taulukoilla, tai taulukkokohtainen, jolloin se näkyy vain omistavalla taulukollaan. XLSX-rajapinnassa ero ilmaistaan yhdellä valinnaisella parametrilla. DefinedNames.Add(AName, AFormula) luo työkirjatason nimen, kun taas Add(AName, AFormula, ASheetIndex) sitoo sen yhteen taulukkoon. Kun luet nimen takaisin, TXLSXDefinedName.SheetIndex palauttaa arvon -1 työkirjan laajuudelle ja muussa tapauksessa 0-pohjaisen taulukkoindeksin

Laajuus toimii samalla törmäyskäytäntönä, minkä vuoksi se on päätettävä ennen ensimmäisen nimen kirjoittamista. Excel sallii taulukkolokaalin Total-nimen jokaisella taulukolla sekä työkirjanlaajuisen Total-nimen, ja tietyn taulukon kaava ratkaisee ensin paikallisen nimen. Generoitujen työkirjojen kannattaa hyödyntää tätä tarkoituksella. Usean taulukon käyttämät liiketoimintaoletukset, kuten veroprosentit, valuuttakurssit ja raportointikausi, kuuluvat työkirjan laajuuteen. Apualueet, joihin vain yhden taulukon kaavat viittaavat, ovat turvallisempia taulukkokohtaisina, jolloin ne eivät voi varjostaa mitään eikä mikään voi varjostaa niitä

Kaavio työkirja- ja taulukkoalueisista määritetyistä nimistä HotXLS:ssä Delphi scope -parametrilla ja paikallisen nimen törmäyssäännöllä — Skoopin parametri on suunnittelupäätös: liiketoiminta-oletukset elävät työkirjan skoobissa, kun taas yksittäisten taulukoiden apurit pysyvät taulukkokohtaisina, joissa paikallinen nimi ratkeaa ensin

var
  Book: TXLSXWorkbook;
  Data, Summary: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Summary := Book.Sheets.Add('Summary');
    // ... täytä Data!A2:D100-tiedot rivitarkenteilla ...

    Book.DefinedNames.Add('TaxRate', '0.08');                // työkirjatason, vakio
    Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100');  // työkirjatason, alue
    Book.DefinedNames.Add('LocalNote', 'Summary!$B$1', 1);   // rajattu vain arkin indeksiin 1

    // XLSX-kaavat eivät ota johtavaa '='-merkkiä
    Summary.Cells[2, 2].Formula := 'SUM(Data!D2:D100)*TaxRate';
    Book.SaveAs('model.xlsx');
  finally
    Book.Free;
  end;
end;

Määritetyn nimen ei tarvitse osoittaa aluetta. Yllä oleva TaxRate viittaa paljaaseen vakioon 0.08, ja se on siistein tapa julkaista liiketoimintaoletus. Se näkyy kerran Excelin nimien hallinnassa, jokainen kaava viittaa siihen symbolisesti, ja seuraavan neljänneksen verokannan muutos on yhden rivin muokkaus generaattoriin sen sijaan, että neljästätoista kootusta kaavamerkkijonosta etsittäisiin osumia

Yhtäsuuruusmerkki, joka kuuluu vain toiselle puolelle

Kaavan syöttökanava on kohta, jossa siirretty koodi hajoaa useimmin, koska julkisivut ovat eri mieltä yhtäsuuruusmerkistä. XLS-solut vastaanottavat kaavat Value-ominaisuuden kautta alussa olevalla =-merkillä. XLSX-soluilla on erillinen Formula-ominaisuus, joka ottaa lausekkeen ilman etuliitettä. Jos kirjoitat '=SUM(A1:A10)'-tekstin TXLSXCell.Formula-ominaisuuteen, yhtäsuuruusmerkistä tulee osa tallennettua lauseketekstiä eikä merkki, eikä tiedosto käyttäydy kuten sama merkkijono XLS-puolella

Kaavio vertailee Delphi-kaavansyöttökanavia HotXLS:ssä, jossa XLS Value vaatii johtavan yhtäsuuruusmerkin ja XLSX Formula kieltää sen — Sama lauseke saapuu Value:n kautta yhtäsuuruusmerkin kanssa XLS-puolella ja Formula:n kautta ilman sitä XLSX-puolella — käytäntöjen sekoittaminen tallentaa merkin tekstinä

var
  Book: IXLSWorkbook;   // liittymälaskennallinen: älä kutsu Free
  Names: IXLSNames;
begin
  Book := TXLSWorkbook.Create;
  // oleta, että 'Data'-niminen arkki sisältää jo rivitarkenteet
  Names := Book.GetNames;
  Names.Add('TaxRate', '0.08');
  Names.Add('Helper', 'Data!$A$2:$A$100', False);  // False = piilotettu Name Managerista

  // XLS-kaavat kulkevat Value-arvon kautta, etuliitteellä '='
  Book.Sheets[1].Cells.Item[2, 2].Value := '=SUM(Data!A2:A100)*TaxRate';
  Book.SaveAs('model.xls');
end;

Katkelma näyttää kaksi muutakin XLS-puolen erikoisuutta. Taulukkokokoelma on 1-pohjainen, joten Sheets[1] on ensimmäinen taulukko, kun XLSX:n Sheets[0] on 0-pohjainen. Kolmas Add-parametri luo piilotetun nimen: se on tiedostossa ja kaavojen käytettävissä, mutta ei näy Excelin nimien hallinnassa. Piilotetut nimet sopivat generaattorin sisäiseen infrastruktuuriin, jota loppukäyttäjän ei pitäisi koskaan muokata tai poistaa vahingossa

Ristiviittaukset ja se, mitä tapahtuu rivien liikkuessa

Molemmat kaavamoottorit hyväksyvät tavallisen ristiviittaussyntaksin. Tavalliset taulukkonimet määrittävät solun suoraan muodossa Data!A1; välilyöntejä tai välimerkkejä sisältävä nimi tarvitsee yksinkertaiset lainausmerkit, kuten 'Sheet With Space'!A1. Nimen RefersTo-tekstissä kannattaa lähes aina käyttää absoluuttisia viittauksia, kuten Data!$A$2:$D$100. Määritetyn nimen suhteellinen viittaus ratkaistaan sitä käyttävän solun suhteen, mikä on tarkoituksellinen Excel-ominaisuus ja luotettava hämmennyksen aiheuttaja, kun se laukeaa vahingossa

Rakenteelliset muokkaukset ovat tilanteita, joissa ristiviittausten ylläpito ansaitsee paikkansa, ja XLSX-puoli pitää nimet yhdenmukaisina niiden läpi. InsertRows ja DeleteRows siirtävät määritettyjä nimialueita solujen, yhdistämisten, hyperlinkkien ja kaavioankkurien mukana, joten Data!$A$2:$D$100-alueeseen osoittava nimi kattaa yhä tietolohkon sen jälkeen, kun generaattori avaa sen yläpuolelle välin. Kaavoihin liittyy yksi dokumentoitu varaus: rivin lisäys säätää vain muokattavaan taulukkoon kohdistuvia viittauksia. Summary-taulukon kaava, joka viittaa alueeseen Data!D2:D100, kirjoitetaan uudelleen, kun rivejä lisätään Data-taulukkoon, mikä on tavallisesti haluttu tapaus. Varmista se sen sijaan, että olettaisit sen, sillä moottori kertoo asian pienellä kustannuksella:

// laskentamoottori ratkaisee nimet ja arkkien väliset viittaukset suorassa käsittelyssä
V := Book.Calculate('SUM(Data!D2:D100)*TaxRate');
if VarIsNumeric(V) then
  Log('net total checks out: ' + FloatToStr(V));

Calculate arvioi mielivaltaisen lausekkeen nykyistä työkirjan tilaa vasten tallentamatta mitään, joten se on luonteva varmennuskeino generaattoritesteihin. Laske odotettu kooste lähdetiedoista Pascalilla, arvioi työkirjan oma kaava ja vertaa näitä kahta. Kaavamoottoria käsittelevä artikkeli kertoo, mitä moottori arvioi, milloin se arvioi ja miten sitä laajennetaan mukautetuilla funktioilla

Ominaisuustason hallitsemat _xlnm-nimet

Kun avaat luodun tiedoston nimitaulun matalan tason tarkastelijassa, löydät merkintöjä, joita et koskaan kirjoittanut: _xlnm.Print_Area, _xlnm.Print_Titles ja niiden sukulaisia. Näin OOXML (ECMA-376 / ISO 29500) tallentaa tulostusalueet ja toistuvat otsikkorivit: määritettyinä niminä, joilla on varatut tunnisteet. HotXLS hallitsee niitä erityisten laskentataulukko-ominaisuuksien kautta, joten PrintArea- tai PrintTitleRows-ominaisuuden asettaminen kirjoittaa vastaavan _xlnm.*-merkinnän puolestasi

Ansana on puuttua tähän varattuun nimiavaruuteen käsin. Jos lisäät _xlnm.Print_Area-merkinnän DefinedNames.Add-kutsulla ja asetat samalla PrintArea-ominaisuuden, työkirjassa on kaksi ristiriitaista määritystä yhdelle varatulle nimelle. Excel ratkaisee tällaisen tilan tavoilla, joista minkään tuotteen ei pitäisi riippua. Käsittele jokaista _xlnm.-alkuista tunnistetta ominaisuustasolle kuuluvana. Kun haluat tarkastaa tulostusasetukset, lue ominaisuudet äläkä nimitaulua. Suojausta ja sivun asetuksia käsittelevä artikkeli käsittelee tulostusalueominaisuuksia asiayhteydessään

Kaksi rajaa, jotka kannattaa tuntea ennen mallin sitomista

Määritetyt nimet eivät kulje mukana kätevässä XLS-XLSX-sillassa. SaveXLSWorkbookAsXLSX kopioi solujen sisällön ja perusmuotoilun, eikä nimitaulu kuulu sen dokumentoituun kopiointiluetteloon, joten nimistä riippunut työkirja menettää ne ylityksessä. Luo nimet uudelleen muunnoksen jälkeen DefinedNames.Add-kutsulla. Vaihe on vähemmän työläs kuin miltä se kuulostaa, sillä se antaa mahdollisuuden yhdenmukaistaa laajuudet sen sijaan, että siirtäisit XLS-tiedoston mahdollisesti sisältämät laajuudet sellaisinaan

Toinen raja on kaavamerkkijonojen ja taulukkonimien välinen ajautuminen erilleen. Excel kirjoittaa taulukkoviittaukset kaavoissa ja nimissä uudelleen vuorovaikutteisen uudelleennimeämisen aikana, joten käyttäjän Excelissä muokkaamat tiedostot pysyvät itsestään yhdenmukaisina. Altistus on generaattoripuolella: kun Pascal-koodi kokoaa kaavamerkkijonoja taulukkonimen literaalista, taulukon nimen muuttaminen yhdessä paikassa ja toisessa unohtaminen tuottaa viittauksen taulukkoon, jota ei enää ole. Säilytä taulukkonimi yhdessä Delphi-vakiossa ja syötä se sekä Sheets.Add-kutsuun että kaavan kokoamiseen, jolloin ne eivät voi olla eri mieltä. Sama ajatus puoltaa raportin tulossolujen nimeämistä osoitteiden kovakoodauksen sijaan: malli, jonka kokonaissolu on nimetty, toimii yhä sen jälkeen, kun suunnittelija lisää kolme riviä sen yläpuolelle, kun taas generaattori, joka kirjoittaa literaaliin B17, sijoittaa lukunsa hiljaa väärään paikkaan. Mallipohjaista raporttien luontia käsittelevä artikkeli rakentuu täsmälleen tämän mallin varaan

Molempien muotojen täydellinen määritettyjen nimien API sekä kaavamoottorin viite toimitetaan HotXLS Delphi Component -komponentin mukana