HotXLS-kaavamoottori ja mukautetut funktiot Delphissä

Laskentataulukkokirjasto, joka vain tallentaa kaavamerkkijonot, ja kirjasto, jossa on toimiva kaavamoottori, ovat kaksi eri tuotetta, jotka näyttävät samanlaisilta siihen hetkeen asti, kun pyydät toiselta niistä lukua. Useimmat Delphillä tehdyt laskentataulukkosovellukset eivät koskaan huomaa eroa, koska Excel peittää sen: kirjoita soluun SUM(B2:B501), tallenna ja Excel laskee summan uudelleen heti, kun ihminen avaa tiedoston. Kun ihminen poistetaan kierrosta ja sama työkirja kulkee palvelinliukuhihnan läpi, joka vie sen suoraan CSV-muotoon, ero lakkaa olemasta akateeminen. CSV sisältää kirjaimellisen tekstin =SUM(B2:B501) kohdassa, johon numeron olisi pitänyt tulla, koska mikään ei missään vaiheessa oikeasti arvioinut kaavaa

HotXLS on oikealla puolella tätä rajaa. Se käsittelee kaavaa tiedostomuotojen tavoin: tallennettuna tekstinä ja valinnaisena välimuistituloksena, joten pelkkä CSV-vienti toistaa reseptin eikä valmista ruokaa. Mutta siinä on myös laskentamoottori, jota voit kutsua suoraan. Se on sama sekä XLS- että XLSX-julkisivuissa, ja lisäksi siinä on koukku sellaisille funktioille, joiden nimiä moottori ei ennestään tunne. HotXLS on natiivi Object Pascal -kirjasto, joka lukee ja kirjoittaa XLS- ja XLSX-tiedostoja Delphistä ja C++Builderista ilman Excel-automaatiota, ja juuri sen laskentaosa muuttaa tallennetut kaavat tarvittaessa jälleen arvoiksi

Kaavat tallennetaan, niitä ei arvioida välittömästi

Kaavan kirjoittaminen soluun ei laske mitään. Tallennusvaiheessa työkirja kirjaa kaavatekstin. XLS-puolella se kirjaa myös RecalcOnSave-ominaisuuden ohjaamat liput, joiden oletusarvo on True ja jotka käskevät Exceliä laskemaan uudelleen avattaessa. Malli on oikea Exceliin tarkoitetuille tiedostoille ja väärä liukuhihnoille, jotka käyttävät soluarvoja suoraan, olipa kyse CSV-viennistä, HTML-viennistä tai omasta koodistasi, joka lukee solut takaisin. Niitä varten arvioi kaava nimenomaisesti Calculate-kutsulla. Se on käytettävissä neljässä kohdassa: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook ja TXLSXWorksheet tarjoavat kaikki funktion function Calculate(const Formula: WideString): Variant

// arvioi suoraan prosessissa ja toimita arvo reseptin sijaan
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // CSV sisältää nyt luvun

Calculate-kutsulle annettu lauseke on tavallista Excelin kaavatekstiä. Ristiviittaukset, määritetyt nimet ja sisäkkäiset funktiot ratkaistaan kaikki nykyisen muistissa olevan työkirjan perusteella, mikä tekee kutsusta paljon muutakin kuin keinon paikata CSV-vientejä. Käsittele sitä varmennusmekanismina. Generaattori, joka on juuri kirjoittanut viisisataa yksityiskohtariviä, voi pyytää työkirjalta sen omaa loppusummaa ja verrata sitä Pascalilla itsenäisesti laskemaansa lukuun. Näin se löytää yhden rivin verran virheellisen alueen ennen asiakkaan tarkastajaa

Se myös kehystää oikean testausstrategian paljon kaavoja sisältävälle tulosteelle. Excel on edelleen kaavakielen viitetoteutus, joten säilytä niille harvoille liiketoiminnallisesti merkittäville kaavoille hyväksytty mallityökirja, jonka odotetut arvot on tuotettu Excelillä, ja anna koontiprosessin arvioida luodun työkirjan kaavat näitä mallityökirjoja vasten Calculate-kutsulla. Erot tulevat silloin esiin Delphin epäonnistuvina testeinä eivätkä asiakkaan kahden raportin vertailussa löytäminä poikkeamina

Liiketoimintafunktioiden lisääminen OnUserFunction-käsittelijällä

Kun moottori kohtaa funktion nimen, jota se ei tunnista, se nostaa tapahtuman sen sijaan, että epäonnistuisi suoraan. Määritä OnUserFunction kummalle tahansa työkirjaluokalle, niin voit ratkaista kutsun itse:

procedure TReportBuilder.HandleUserFunction(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'DISCOUNT') then
  begin
    Value := Args[0] * 0.9;   // Args saapuu Variant-taulukkona
    Handled := True;
  end;
end;

// kytkentä ja käyttö
Book.OnUserFunction := HandleUserFunction;
Sheet.Cells[1, 1].Value := 200;
Sheet.Cells[1, 2].Formula := 'DISCOUNT(A1)';
Net := Book.Calculate('DISCOUNT(A1) + SUM(A1:A1)');

Kolme yksityiskohtaa vaatii huomiota. Ensinnäkin aseta Handled := True vain silloin, kun todella tunnistit nimen. Kun jätät sen arvoon False, moottori jatkaa tavallista tuntemattoman funktion käsittelyään, joten yksi käsittelijä voi palvella useita työkirjoja väittämättä omakseen kaikkea sen läpi kulkevaa. Toiseksi vertaa nimiä kirjainkoosta välittämättä SameText-kutsulla, sillä kaavojen kirjoittajat käyttävät muotoja discount( ja DISCOUNT( vaihdellen. Kolmanneksi argumentit saapuvat valmiiksi arvioituina: DISCOUNT(A1) antaa sinulle A1:n arvon, ei viittausta, joten funktio ei voi tietää, mistä sen syötteet tulivat. Tämä viimeinen kohta johtaa seuraavan osion rajoitukseen

Käsittele käsittelijän runkoa yhtä puolustavasti kuin mitä tahansa ulkoista sisääntulopistettä. Args-taulukko heijastaa sitä, mitä kaavan kirjoittaja syötti, joten tarkista argumenttien määrä ja tyypit ennen indeksointia ja päätä etukäteen, mitä virheellinen kutsu palauttaa: Variant-virhearvon vai nostetun poikkeuksen. Valinnalla on merkitystä, koska käsittelijän sisällä heitetty poikkeus etenee sitä arvioinnin käynnistänyttä Calculate-kutsua pitkin. Se on hyväksyttävää tarkasti hallitussa generaattorissa, mutta töykeää palvelussa, joka arvioi käyttäjän laatimia työkirjoja, sillä yksi huono kaava kaataisi pyynnön. Tällaisessa tilanteessa ota poikkeus kiinni käsittelijän sisällä ja palauta merkkiarvo, jonka ympäröivä työnkulku voi tunnistaa ja kirjata lokiin

Sijaintitietoiset funktiot tarvitsevat Ex-version

Jotkin funktiot riippuvat oikeutetusti siitä, missä niitä arvioidaan. Arvo, joka vaihtelee taulukkokohtaisesti, riviin suhteutettu haku tai aluekohtainen kerroin, joka pätee vain alueellisilla taulukoilla: mitään näistä ei voi ratkaista pelkkien argumenttiarvojen perusteella. Tavallinen tapahtuma ei pysty ilmaisemaan tätä, joten moottori tarjoaa OnUserFunctionEx-tapahtuman, joka on muuten samanlainen mutta sisältää yhden lisäparametrin:

procedure TReportBuilder.HandleUserFunctionEx(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  const Context: TXLSUserFunctionContext;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'REGIONRATE') then
  begin
    // sama kaava tuottaa eri verokannan jokaisella alueellisella arkilla
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

TXLSUserFunctionContext sisältää arvioitavan solun SheetIndex-, Row- ja Col-arvot. Jos funktion tulos riippuu vähäisessäkin määrin sen sijainnista, kytke Ex-tapahtuma alusta asti. Kontekstin jälkiasentaminen käsittelijään, jota jo kolmekymmentä kaavaa kutsuu, on paljon hankalampaa kuin oikean allekirjoituksen valitseminen ensimmäisenä päivänä, eikä tapahtumien välillä muuten ole niin suurta eroa, että kapeammalla kannattaisi aloittaa

Mukautetut funktiot eivät siirry Exceliin

Mukautettu funktio elää kokonaan prosessissasi. Nimi DISCOUNT tarkoittaa jotakin vain niin kauan kuin Delphi-koodisi ja sen tapahtumakäsittelijä ovat käynnissä. Kun avaat tallennetun tiedoston Excelissä, DISCOUNT on pelkkä tunnistamaton nimi; solussa näkyy #NAME?, ellei käyttäjän koneessa sattumalta ole vastaavaa VBA-funktiota tai apuohjelmaa. Tämä suunnittelutieto erottaa demon toimituskelpoisesta tuotteesta, ja se pakottaa tekemään valinnan tarkoituksella sen sijaan, että löytäisit asian vasta myöhemmin

Päätä solukohtaisesti, kumman kahdesta sopimuksesta toimitat. Solut, joiden käyttäjän on tarkoitus nähdä laskevan uudelleen Excelissä, on rakennettava vain Excelin omasta funktiosanastosta. Solut, joiden logiikka on omistettua, pitäisi arvioida prosessin sisällä Calculate-kutsulla ja tallentaa tavallisina arvoina, jolloin mukautettu funktio toimii sisäisenä laskentasääntönä eikä tiedoston sisältönä. Tukipyyntöjä luotettavasti synnyttävä virhetila on näiden välimuoto: mukautetun funktion kaavan tallentaminen ja Excelin odottaminen kunnioittavan sitä

Pelkkien arvojen sopimuksella on hiljainen etu: se suojaa immateriaalioikeuksia. Delphi-prosessissasi arvioitua ja numerona toimitettua hinnoittelusääntöä ei voi takaisinmallintaa työkirjasta samalla tavoin kuin näkyvää kaavaa, eikä käyttäjä voi rikkoa sitä muokkaamalla välisolua. Laskugeneraattorit, provisiolaskelmat ja hinnastot kuuluvat lähes aina tähän ryhmään. Tapaus, joka aidosti tarvitsee eläviä kaavoja, on vuorovaikutteinen mitä jos -malli, jossa asiakkaan odotetaan muuttavan syötteitä ja seuraavan summien muuttumista. Ne on rakennettava Excelin omalla sanastolla sekä määritetyillä nimillä

Laskentatilat, iterointi ja R1C1: XLS-julkisivun säätimet

XLS-julkisivu tarjoaa BIFF-tason laskenta-asetukset, jotka Excel lukee tiedostosta. CalculationMode hyväksyy arvot xlCalcManual, xlCalcAutomatic (oletus) tai xlCalcAutomaticExceptTables, ja se määrittää, miten Excel käyttäytyy tiedoston avaamisen jälkeen. Tuhansia kaavoja sisältävä mallityökirja on usein käyttäjäystävällisempi toimitettuna manuaalisessa tilassa, jolloin vastaanottaja päättää, milloin uudelleenlaskennan myrsky tapahtuu. EnableIteration (oletus False) yhdessä MaxIterations-arvon (oletus 100) ja MaxIterationChange-arvon (oletus 0.001) kanssa avaa käyttöön harkitut iteratiivisen konvergenssin tyyppiset kehäviittaukset, joita esiintyy joissakin rahoitusmalleissa. ReferenceStyle vaihtaa A1- ja R1C1-näytön välillä, ja UseFullPrecision vastaa Excelin tarkkuus näytön mukaan -asetusta

Nämä ominaisuudet ovat XLS-julkisivussa, koska ne vastaavat BIFF-tietueita. Kun luot .xlsx-tiedostoja, suunnittele kaavat siten, etteivät ne riipu iterointiasetuksista, tai laske konvergoituneet arvot Delphillä ja kirjoita tulokset

Matriisikaavat: julkinen aloituspiste on XLSX

Vanhantyyliset CSE-matriisikaavat luodaan TXLSXRange.SetArrayFormula-kutsulla:

// yksi taulukkokaava kattaen A2:A4
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

Vastaava menetelmä on XLS-luokkahierarkiassa, mutta yksityisessä osassa, joten uusien matriisikaavojen laatimiseen .xls-tiedostoihin ei ole tuettua tapaa. Avatuissa tiedostoissa olevat kaavat säilyvät avaamis- ja tallennuskierroksella ehjinä; et vain voi luoda niitä. Tästä seuraava sääntö on yksinkertainen: kun matriisisemantiikka kuuluu vaatimuksiin, kohdista toteutus .xlsx-muotoon. Jos vanha .xls-toimitus todella tarvitsee matriisikäyttäytymistä, käytännöllinen ratkaisu on laskea matriisin tulos Delphillä ja kirjoittaa yksittäiset arvot soluihin

Kaksi aiheeseen liittyvää artikkelia tällä sivustolla: määritettyjä nimiä ja ristiviittauksia käsittelevä artikkeli kattaa moottorin suorittaman nimien ratkaisun, ja CSV- ja TSV-vientiä käsittelevä artikkeli kuvaa vientikäyttäytymisen, joka tekee nimenomaisesta laskennasta tarpeellisen. Täydellinen moottoriviite, mukaan lukien tuettu funktiojoukko, toimitetaan HotXLS Delphi Component -komponentin mukana