Výpočtový engine HotXLS a vlastné funkcie v Delphi

Knižnica pre prácu s tabuľkami, ktorá ukladá iba reťazce vzorcov, a knižnica s funkčným výpočtovým enginom sú dva odlišné produkty, ktoré vyzerajú identicky až do momentu, kým od jedného z nich nepožadujete číslo. Väčšina kódu pre prácu s tabuľkami v Delphi si tento rozdiel nikdy nevšimne, pretože Excel ho zakrýva: zapíšte SUM(B2:B501) do bunky, uložte ju a Excel prepočíta celkový súčet v okamihu, keď používateľ otvorí súbor. Ak však vylúčite používateľa z tohto procesu a spustíte rovnaký zošit cez serverový skript, ktorý exportuje priamo do formátu CSV, rozdiel prestane byť čisto akademický. Súbor CSV bude obsahovať doslovný text =SUM(B2:B501) namiesto skutočného čísla, pretože vzorec nebol nikde vyhodnotený.

HotXLS stojí na tej správnej strane tejto hranice. So vzorcami zaobchádza tak, ako to vyžadujú súborové formáty, teda ako s uloženým textom s voliteľnou kešovanou hodnotou výsledku, takže čistý export do CSV reprodukuje hodnoty a nie samotný predpis vzorca. Obsahuje však aj výpočtový engine, ktorý môžete volať priamo (rovnaký engine pre rozhrania XLS aj XLSX), a tiež udalosť na vyhodnocovanie názvov funkcií, ktoré engine doposiaľ nepozná. HotXLS je natívna knižnica pre Object Pascal, ktorá číta a zapisuje formáty XLS a XLSX v prostrediach Delphi a C++Builder bez nutnosti automatizácie Excelu, pričom jej výpočtová časť slúži na spätnú premenu uložených vzorcov na konkrétne hodnoty na vyžiadanie.

Vzorce sa ukladajú, nevyhodnocujú sa hneď

Zápis vzorca do bunky nič nepočíta. Pri ukladaní zošit zaznamená text vzorca. Na strane XLS tiež zaznamená príznaky riadené vlastnosťou RecalcOnSave, ktorá je predvolene nastavená na True a prikazuje Excelu prepočítať zošit pri otvorení. Tento model je správny pre súbory určené pre Excel, ale nesprávny pre procesy, ktoré priamo spracovávajú hodnoty buniek, či už ide o export do CSV, export do HTML alebo váš vlastný kód na čítanie hodnôt z buniek. V takýchto prípadoch musíte vzorce vyhodnotiť explicitne pomocou metódy Calculate. Tá je dostupná v štyroch vstupných bodoch: triedy TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook a TXLSXWorksheet zhodne poskytujú funkciu function Calculate(const Formula: WideString): Variant.

// evaluate in-process, then ship the value rather than the recipe
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // the CSV now carries the number

Výraz odovzdaný metóde Calculate je bežný text vzorca Excelu. Odkazy na iné hárky, definované názvy a vnorené funkcie sa vyhodnocujú voči aktuálnemu zošitu v pamäti, čo robí toto volanie užitočným aj mimo opráv exportu do CSV. Môžete ho použiť napríklad ako overovací mechanizmus. Generátor, ktorý práve zapísal päťsto riadkov s podrobnosťami, sa môže opýtať zošita na jeho vlastný celkový súčet a porovnať ho s hodnotou vypočítanou nezávisle v Pascale, čím odhalí chybu posunu o jeden riadok skôr, ako to urobí audítor zákazníka.

Predstavuje to tiež správnu testovaciu stratégiu pre výstupy s veľkým množstvom vzorcov. Excel zostáva referenčnou implementáciou jazyka vzorcov, takže pre vzorce s obchodnými dôsledkami udržiavajte schválený testovací súbor, ktorého očakávané hodnoty vygeneroval samotný Excel. Následne nechajte zostavovací server vyhodnotiť vzorce vygenerovaného zošita pomocou metódy Calculate voči týmto očakávaným hodnotám. Prípadné rozdiely sa potom prejavia ako zlyhané testy v Delphi, a nie ako nezrovnalosti zistené zákazníkom pri porovnávaní dvoch reportov.

Pridávanie obchodných funkcií pomocou OnUserFunction

Keď engine narazí na názov funkcie, ktorý nerozpozná, namiesto okamžitého zlyhania vyvolá udalosť. Priraďte udalosť OnUserFunction v ktorejkoľvek triede zošita a volanie môžete spracovať sami:

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 arrives as a Variant array
    Handled := True;
  end;
end;

// wiring and use
Book.OnUserFunction := HandleUserFunction;
Sheet.Cells[1, 1].Value := 200;
Sheet.Cells[1, 2].Formula := 'DISCOUNT(A1)';
Net := Book.Calculate('DISCOUNT(A1) + SUM(A1:A1)');

Pozornosť si zaslúžia tri detaily. Po prvé, nastavte Handled := True iba vtedy, keď ste názov skutočne rozpoznali. Ponechanie hodnoty False umožní enginu pokračovať v bežnom spracovaní neznámej funkcie, takže jeden obslužný program môže obslúžiť viacero zošitov bez toho, aby spracovával všetky prechádzajúce volania. Po druhé, porovnávajte názvy bez ohľadu na veľkosť písmen pomocou SameText, pretože autori vzorcov píšu discount( a DISCOUNT( zameniteľne. Po tretie, argumenty prichádzajú už vyhodnotené: volanie DISCOUNT(A1) vám odovzdá hodnotu bunky A1, nie odkaz na ňu, takže funkcia nedokáže určiť pôvod svojich vstupov. Tento posledný bod definuje obmedzenie, ktorým sa zaoberá nasledujúca časť.

K telu obslužného programu pristupujte rovnako defenzívne ako k akémukoľvek externému vstupnému bodu. Pole Args odráža to, čo autor vzorca napísal, preto pred indexovaním overte počet a typy argumentov a vopred sa rozhodnite, čo vráti neplatné volanie: chybovú hodnotu typu Variant alebo vyvolanú výnimku. Táto voľba je dôležitá, pretože výnimka vyhodená v obslužnom programe sa šíri von cez volanie Calculate, ktoré spustilo vyhodnotenie. To je prijateľné v prísne kontrolovanom generátore, ale nevhodné v službe spracovávającej zošity vytvorené používateľmi, kde by jeden nesprávny vzorec zhodil celú požiadavku. V takomto prostredí zachyťte výnimku vnútri obslužného programu a vráťte špečálnu hodnotu, ktorú okolitý kód dokáže rozpoznať a zapísať do logu.

Funkcie závislé od pozície vyžadujú variant Ex

Niektoré funkcie legitímne závisia od toho, kde sa vyhodnocujú. Sadzba, ktorá sa líši pre každý hárok, vyhľadávanie relatívne k riadku alebo násobiteľ pre konkrétny región aplikovaný iba na regionálnych hárkoch: nič z toho nie je možné vyriešiť iba na základe hodnôt argumentov. Bežná udalosť to nedokáže vyjadriť, preto engine ponúka OnUserFunctionEx, ktorá je identická, ale obsahuje jeden parameter navyše:

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
    // the same formula yields a different rate on each regional sheet
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

Kontext TXLSUserFunctionContext nesie vlastnosti SheetIndex, Row a Col vyhodnocovanej bunky. Ak výsledok funkcie čo i len minimálne závisí od jej umiestnenia, prepojte udalosť s variantom Ex hneď na začiatku. Dodatočné pridávanie kontextu do obslužného programu, ktorý už volá tridsať vzorcov, je oveľa zložitejšie ako výber správnej signatúry v prvý deň, pričom obe udalosti sú si inak natoľko podobné, že nie je dôvod začínať s tou užšou.

Vlastné funkcie sa neprenášajú do Excelu

Vlastná funkcia existuje výhradne vo vašom procese. Názov DISCOUNT má význam iba vtedy, keď beží váš kód v Delphi a jeho obslužný program udalosti. Ak uložený súbor otvoríte v Exceli, DISCOUNT bude len nerozpoznaný názov; bunka zobrazí chybu #NAME?, pokiaľ na počítači používateľa neexistuje zodpovedajúca funkcia VBA alebo doplnok. Toto je návrhový fakt, ktorý odlišuje demo od hotového produktu, a vyžaduje si rozhodnutie, ktoré musíte urobiť zámerne, a nečakať, kým ho objavíte neskôr.

Rozhodnite sa pre každú bunku, ktorý z dvoch prístupov zvolíte. Bunky, ktorých prepočet v Exceli má používateľ vidieť, musia byť postavené výhradne zo slovníka funkcií samotného Excelu. Bunky s internou logikou by mali byť vyhodnotené v procese pomocou metódy Calculate a uložené ako čisté hodnoty, takže vlastná funkcia sa správa ako interné výpočtové pravidlo a nie ako obsah súboru. Chybový stav, ktorý spoľahlivo generuje požiadavky na podporu, je stredná cesta: uloženie vzorca s vlastnou funkciou a očakávanie, že ho Excel spracuje.

Prístup ukladania iba hodnôt má jednu skrytú výhodu: chráni duševné vlastníctvo. Cenové pravidlo vyhodnotené vo vašom procese v Delphi a odoslané ako číslo nie je možné spätne analyzovať zo zošita tak, ako je to možné pri viditeľnom vzorci, a používateľ ho nemôže poškodiť úpravou medziľahlej bunky. Generátory faktúr, provízne výkazy a sadzobníky takmer vždy patria do tejto kategórie. Prípadom, ktorý skutočne vyžaduje živé vzorce, je interaktívny model typu "čo ak", kde sa od zákazníka očakáva, že bude meniť vstupy a sledovať zmeny celkových súčtov; tie musia byť postavené na základe vlastných funkcií Excelu a definovaných názvov.

Režimy výpočtu, iterácie a R1C1: nastavenia rozhrania XLS

Rozhranie XLS sprístupňuje nastavenia výpočtov na úrovni formátu BIFF, ktoré Excel číta zo súboru. Vlastnosť CalculationMode prijíma hodnoty xlCalcManual, xlCalcAutomatic (predvolené) alebo xlCalcAutomaticExceptTables a určuje správanie Excelu po otvorení súboru. Zložitý zošit s tisíckami vzorcov je často lepšie doručiť v manuálnom režime, aby sa príjemca sám rozhodol, kedy prebehne náročný prepočet. Vlastnosť EnableIteration (predvolene False) spolu s MaxIterations (predvolene 100) a MaxIterationChange (predvolene 0.001) sprístupňuje riadené cyklické odkazy pre metódy iteračnej konvergencie, ktoré sa vyskytujú v niektorých finančných modeloch. Vlastnosť ReferenceStyle prepína medzi zobrazením A1 a R1C1 a UseFullPrecision zrkadlí voľbu presnosti podľa zobrazenia v Exceli.

Tieto vlastnosti sa nachádzajú v rozhraní XLS, pretože sa mapujú na záznamy BIFF; pri generovaní formátu .xlsx plánujte vzorce tak, aby nezáviseli od nastavení iterácií, prípadne vypočítajte konvergované hodnoty v Delphi a zapíšte priamo výsledky.

Maticové vzorce: verejným vstupným bodom je XLSX

Tradičné maticové vzorce štýlu CSE sa vytvárajú pomocou metódy TXLSXRange.SetArrayFormula:

// one array formula spanning A2:A4
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

Ekvivalentná metóda existuje aj v hierarchii tried XLS, ale nachádza sa v privátnej sekcii, takže neexistuje podporovaný spôsob, ako vytvárať nové maticové vzorce v súboroch .xls. Existujúce maticové vzorce v otvorených súboroch sa pri načítaní a uložení zachovajú; nemôžete ich však vytvárať. Pravidlo, ktoré z toho vyplýva, je jednoduché: ak sú súčasťou požiadaviek maticové vzorce, zamerajte sa na formát .xlsx. Ak starší formát .xls skutočne vyžaduje maticové správanie, praktickou cestou je vypočítať maticový výsledok v Delphi a zapísať jednotlivé hodnoty priamo do buniek.

Dve súvisiace čítania na tomto webe: článok o definovaných názvoch a vzorcoch naprieč hárkami sa zaoberá vyhodnocovaním názvov, ktoré engine vykonáva, a článok o exporte do formátov CSV a TSV podrobne opisuje správanie pri exporte, ktoré si vyžaduje explicitný výpočet. Úplná referenčná príručka k enginu vrátane zoznamu podporovaných funkcií sa dodáva s komponentom HotXLS Component.