HotXLS képletmotor és egyéni függvények Delphiben

Az a táblázatkezelő könyvtár, amely csak képletkarakterláncokat tárol, és az, amelyikben működő képletmotor van, két különböző termék, amelyek egészen addig azonosnak látszanak, amíg az egyiktől számot nem kér. A legtöbb Delphi táblázatkezelő kód sosem veszi észre a szakadékot, mert az Excel átragasztja: írja a SUM(B2:B501) képletet egy cellába, mentsen, és az Excel abban a pillanatban újraszámolja az összeget, amint egy ember megnyitja a fájlt. Vegye ki az embert a körből, futtassa ugyanezt a munkafüzetet egy olyan kiszolgálói folyamaton át, amely egyenesen CSV-be exportál, és a különbség megszűnik elméleti lenni. A CSV a szó szerinti =SUM(B2:B501) szöveget viszi ott, ahová szám tartozott, mert egyetlen ponton sem értékelte ki semmi ténylegesen a képletet

Ennek a vonalnak a HotXLS a helyes oldalán áll. Úgy kezeli a képletet, ahogy a fájlformátumok teszik, vagyis tárolt szövegként plusz egy választható gyorsítótárazott eredményként, így a puszta CSV export a receptet adja vissza, nem a fogást. De hordoz egy közvetlenül hívható számolómotort is, ugyanazt mindkét, az XLS és az XLSX homlokzatban, mellette pedig egy horgot olyan függvénynevek feloldására, amelyekről a motor még sosem hallott. A HotXLS natív Object Pascal könyvtár, amely Excel automatizálás nélkül olvas és ír XLS és XLSX fájlokat Delphiből és C++Builderből, a számoló fele pedig az, ami a tárolt képleteket igény szerint értékké alakítja vissza

A képletek tárolódnak, nem értékelődnek ki mohón

Egy képlet cellába írása nem számol ki semmit. Mentéskor a munkafüzet rögzíti a képletszöveget. Az XLS oldalon rögzíti azokat a jelzőket is, amelyeket a RecalcOnSave vezérel, amely alapértelmezés szerint True, és megnyitáskori újraszámolásra utasítja az Excelt. Ez a modell helyes az Excelnek szánt fájlokhoz, és helytelen azokhoz a folyamatokhoz, amelyek közvetlenül fogyasztják a cellaértékeket, legyen szó CSV exportról, HTML exportról vagy a saját, cellákat visszaolvasó kódjáról. Azokhoz kifejezetten a Calculate hívással értékeljen ki. Négy belépési ponton létezik: a TXLSWorkbook, az IXLSWorksheet, a TXLSXWorkbook és a TXLSXWorksheet mind elérhetővé teszi a function Calculate(const Formula: WideString): Variant alakot

// kiértékelés a folyamaton belül, majd az érték szállítása a recept helyett
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // a CSV most már a számot viszi

A Calculate hívásnak átadott kifejezés közönséges Excel képletszöveg. A lapok közötti hivatkozások, a definiált nevek és az egymásba ágyazott függvények mind a memóriában lévő aktuális munkafüzet ellen oldódnak fel, ami a CSV exportok foltozásán jóval túl is hasznossá teszi a hívást. Tekintse állításmechanizmusnak. Az a generátor, amely épp ötszáz részletsort írt ki, elkérheti a munkafüzettől a saját végösszegét, és összevetheti azzal a számmal, amelyet Pascalban függetlenül számolt ki, így elkapja a tartomány eggyel elcsúszó hibáját, mielőtt egy ügyfél könyvvizsgálója tenné

Ez a helyes tesztelési stratégiát is kijelöli a képletgazdag kimenethez. Az Excel marad a képletnyelv referenciamegvalósítása, ezért arra a maroknyi képletre, amely üzleti következményt hordoz, tartson jóváhagyott rögzítőfájlt, amelynek várt értékeit maga az Excel állította elő, és a fordítási folyamat a Calculate hívással értékelje ki az előállított munkafüzet képleteit e rögzítőértékekhez mérve. Az eltérések így Delphiben bukó tesztként jelennek meg, nem pedig két jelentést összehasonlító ügyfél által felfedezett ellentmondásként

Üzleti függvények hozzáadása az OnUserFunction eseménnyel

Amikor a motor olyan függvénynévvel találkozik, amelyet nem ismer fel, kereken elbukás helyett eseményt vált ki. Rendelje hozzá az OnUserFunction eseményt bármelyik munkafüzet-osztályon, és maga oldhatja fel a hívást:

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;   // az Args Variant tömbként érkezik
    Handled := True;
  end;
end;

// bekötés és használat
Book.OnUserFunction := HandleUserFunction;
Sheet.Cells[1, 1].Value := 200;
Sheet.Cells[1, 2].Formula := 'DISCOUNT(A1)';
Net := Book.Calculate('DISCOUNT(A1) + SUM(A1:A1)');

Három részlet érdemel figyelmet. Először, a Handled := True értéket csak akkor állítsa be, ha valóban felismerte a nevet. False értéken hagyva a motor folytatja a szokásos ismeretlenfüggvény-kezelést, így egyetlen kezelő több munkafüzetet is kiszolgálhat anélkül, hogy mindenre rátenné a kezét, ami átmegy rajta. Másodszor, a neveket kis- és nagybetűtől függetlenül hasonlítsa össze a SameText hívással, mert a képletek szerzői felváltva gépelnek discount( és DISCOUNT( alakot. Harmadszor, az argumentumok előre kiértékelve érkeznek: a DISCOUNT(A1) az A1 értékét adja a kezébe, nem a hivatkozást, így a függvény nem tudja megmondani, honnan jöttek a bemenetei. Ez az utolsó pont vezet fel ahhoz a korláthoz, amelyről a következő szakasz szól

A kezelő törzsét ugyanolyan védekezőn kezelje, mint bármely külső belépési pontot. Az Args tömb azt tükrözi, amit a képlet szerzője gépelt, ezért indexelés előtt ellenőrizze az argumentumok számát és típusát, és előre döntse el, mit ad vissza egy érvénytelen hívás: Variant hibaértéket vagy kiváltott kivételt. A választás számít, mert a kezelőn belül dobott kivétel kifelé, azon a Calculate híváson át terjed, amely a kiértékelést indította. Ez elfogadható egy szorosan felügyelt generátorban, és modortalan egy olyan szolgáltatásban, amely felhasználók által írt munkafüzeteket értékel ki, ahol egyetlen rossz képlet ledöntené a kérést. Abban a helyzetben kapja el a kezelőn belül, és adjon vissza olyan jelzőértéket, amelyet a körülvevő munkafolyamat felismer és naplóz

A pozíciófüggő függvényeknek az Ex változat kell

Egyes függvények jogosan függenek attól, hol értékelik ki őket. Laponként eltérő kulcs, sorhoz viszonyított keresés, régiónkénti szorzó, amely csak a regionális lapokon érvényes: ezek egyikére sem lehet pusztán az argumentumértékekből válaszolni. Az egyszerű esemény ezt nem tudja kifejezni, ezért a motor kínálja az OnUserFunctionEx eseményt, amely egyetlen többletparaméteren kívül azonos:

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
    // ugyanaz a képlet minden regionális lapon más kulcsot ad
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

A TXLSUserFunctionContext a kiértékelő cella SheetIndex, Row és Col adatait hordozza. Ha egy függvény eredménye akár csak kicsit is függ a helyétől, kezdettől az Ex eseményt kösse be. A kontextus utólagos beszerelése egy olyan kezelőbe, amelyet már harminc képlet hív, sokkal ragacsosabb, mint az első napon a helyes szignatúrát választani, a két esemény pedig egyébként annyira hasonló, hogy kevés ok szól a szűkebbel való kezdés mellett

Az egyéni függvények nem utaznak át az Excelbe

Az egyéni függvény teljes egészében a saját folyamatán belül él. A DISCOUNT név csak addig jelent valamit, amíg a Delphi kódja és annak eseménykezelője fut. Nyissa meg a mentett fájlt Excelben, és a DISCOUNT csupán fel nem ismert név; a cella #NAME? hibát mutat, hacsak történetesen nem létezik egyező VBA függvény vagy bővítmény a felhasználó gépén. Ez az a tervezési tény, amely elválasztja a bemutatót a szállítható terméktől, és olyan választásra kényszerít, amelyet szándékosan kell meghoznia, nem utólag felfedeznie

Cellánként döntse el, a két szerződés közül melyiket szállítja. Azokat a cellákat, amelyeket a felhasználó várhatóan az Excelen belül számol újra, kizárólag az Excel saját függvényszókincséből kell felépíteni. Azokat a cellákat, amelyek logikája szellemi tulajdon, a folyamaton belül, a Calculate hívással kell kiértékelni és sima értékként megőrizni, így az egyéni függvény belső számolási szabályként viselkedik, nem fájltartalomként. Az a hibamód, amely megbízhatóan termel támogatási jegyeket, a köztes út: egyéni függvényt tartalmazó képletet megőrizni, és azt várni, hogy az Excel tiszteletben tartja

A csak értékeket tartalmazó szerződésnek van egy csendes előnye: védi a szellemi tulajdont. Az a árazási szabály, amelyet a Delphi folyamatában értékelnek ki, és számként szállítanak, nem fejthető vissza a munkafüzetből úgy, ahogyan egy látható képlet, és a felhasználó sem tudja elrontani egy köztes cella szerkesztésével. A számlagenerátorok, jutalékkimutatások és díjszabások szinte mindig ebbe a táborba tartoznak. Az az eset, amelynek valóban élő képletekre van szüksége, az interaktív mi lenne, ha modell, ahol az ügyféltől azt várják, hogy bemeneteket változtasson, és nézze mozogni az összegeket, ezeket pedig az Excel saját szókincséből és definiált nevekből kell felépíteni

Számolási módok, iteráció és R1C1: az XLS homlokzat szabályzói

Az XLS homlokzat teszi elérhetővé azokat a BIFF szintű számolási beállításokat, amelyeket az Excel a fájlból olvas. A CalculationMode elfogadja az xlCalcManual, xlCalcAutomatic (az alapértelmezett) vagy xlCalcAutomaticExceptTables értéket, és azt határozza meg, hogyan viselkedik az Excel a fájl megnyitása után. Egy több ezer képletet tartalmazó modellmunkafüzet gyakran barátságosabb kézi módban szállítva, így a címzett dönti el, mikor legyen az újraszámolási vihar. Az EnableIteration (alapértelmezés False) a MaxIterations (alapértelmezés 100) és a MaxIterationChange (alapértelmezés 0,001) beállításokkal együtt oldja fel azokat a szándékos körkörös hivatkozásokat, amelyek az iteratív konvergencia fajtájából egyes pénzügyi modellekben felbukkannak. A ReferenceStyle az A1 és az R1C1 megjelenítés között vált, az UseFullPrecision pedig az Excel megjelenítés szerinti pontosság beállítását tükrözi

Ezek a tulajdonságok azért élnek az XLS homlokzaton, mert BIFF rekordokra képződnek le; .xlsx előállításakor úgy tervezze a képleteket, hogy ne függjenek iteratív beállításoktól, vagy számolja ki a konvergált értékeket Delphiben, és az eredményeket írja ki

Tömbképletek: a nyilvános belépési pont az XLSX

Az örökölt, CSE stílusú tömbképleteket a TXLSXRange.SetArrayFormula hozza létre:

// egyetlen tömbképlet az A2:A4 tartományra
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

A megfelelő metódus létezik az XLS osztályhierarchiában is, de privát szakaszban ül, így nincs támogatott mód új tömbképletek írására .xls fájlokba. A megnyitott fájlokban meglévők épen teszik meg az oda-vissza utat; amit nem tehet, az az új létrehozása. Az ebből következő szabály elég egyszerű: amikor a tömbszemantika a követelmény része, .xlsx formátumot célozzon. Ha egy örökölt .xls szállítmánynak valóban tömbviselkedésre van szüksége, a gyakorlatias út a tömberedmény kiszámolása Delphiben, és az egyes értékek cellákba írása

Két kapcsolódó olvasnivaló ezen az oldalon: a definiált nevek és a lapok közötti képletek tárgyalja azt a névfeloldást, amelyet a motor végez, a CSV és TSV exportról szóló cikk pedig részletezi azt az exportviselkedést, amely szükségessé teszi a kifejezett számolást. A támogatott függvénykészletet is tartalmazó teljes motorhivatkozás a HotXLS Delphi Component csomaggal érkezik