Jádro vzorců HotXLS a vlastní funkce v Delphi

Knihovna pro tabulkové procesory, která pouze ukládá vzorce jako textové řetězce, a knihovna s funkčním výpočetním jádrem jsou dva různé produkty. Vypadají sice identicky, ale jen do chvíle, než po jednom z nich chcete skutečné číslo. Většina kódu v Delphi si tohoto rozdílu nikdy nevšimne, protože Excel jej překrývá: zapíšete SUM(B2:B501) do buňky, uložíte a Excel přepočítá součet v okamžiku, koukoliv uživatel soubor otevře. Pokud však člověka z tohoto procesu vynecháte a sešit zpracujete na serveru, který exportuje přímo do formátu CSV, rozdíl přestane být akademický. Výsledný soubor CSV pak bude obsahovat doslovný text =SUM(B2:B501) namísto čísla, protože v žádném okamžiku nedošlo ke skutečnému vyhodnocení vzorce

A to je přesně ta hranice, na jejíž správné straně HotXLS stojí. K vzorcům přistupuje tak, jak to dělají samotné souborové formáty: jako k uloženému textu s volitelným mezipaměťovým výsledkem. Samotný export do CSV tak bez vyhodnocení reprodukuje spíše recept než hotové jídlo. HotXLS však obsahuje výpočetní jádro, které můžete volat přímo (stejné pro rozhraní XLS i XLSX), a navíc umožňuje napojení pro vyhodnocování názvů funkcí, které jádro samo nezná. HotXLS je nativní knihovna v Object Pascalu, která čte a zapisuje formáty XLS a XLSX z Delphi a C++Builderu bez nutnosti automatizace Excelu, a její výpočetní část je právě tím, co převádí uložené vzorce zpět na hodnoty podle potřeby

Vzorce se ukládají, nevyhodnocují se ihned

Zápis vzorce do buňky nic nepočítá. Při ukládání zaznamená sešit text vzorce. Na straně XLS navíc zaznamenává příznaky řízené vlastností RecalcOnSave, která je standardně nastavená na True a říká Excelu, že má sešit po otevření přepočítat. Tento model je správný pro soubory určené k otevření v Excelu, ale nevhodný pro procesy, které hodnoty buněk konzumují přímo — ať už jde o export do CSV, export do HTML nebo váš vlastní kód čtoucí buňky zpět. V těchto případech vzorce vyhodnocujte explicitně pomocí metody Calculate. Ta je k dispozici na čtyřech přístupových bodech: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook a TXLSXWorksheet shodně vystavují 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 předaný metodě Calculate je běžným textem vzorce Excelu. Odkazy napříč listy, definované názvy i vnořené funkce se vyhodnocují vůči aktuálnímu sešitu v paměti, díky čemuž je toto volání užitečné daleko za hranicemi pouhého exportu do CSV. Považujte jej za mechanismus ověřování (assertion). Generátor, který právě zapsal pět set podrobných řádků, se může sešitu zeptat na jeho vlastní celkový součet a porovnat jej s údajem, který vypočítal nezávisle v Pascalu, čímž zachytí chybu v rozsahu o jeden řádek dříve než auditor zákazníka

To také určuje správnou strategii testování pro výstupy plné složitých vzorců. Excel zůstává referenční implementací jazyka vzorců, takže pro tu hrstku vzorců s obchodním dopadem doporučujeme udržovat schválený testovací soubor (fixture), jehož očekávané hodnoty vygeneroval samotný Excel. Sestavovací proces pak může vyhodnocovat vzorce generovaného sešitu pomocí Calculate vůči těmto vzorům. Rozdíl se pak projeví jako neúspěšné testy v Delphi, nikoli jako nesrovnalosti zjištěné zákazníkem při porovnávání dvou reportů

Přidávání obchodních funkcí pomocí OnUserFunction

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)');

Pozornost si zaslouží tři detaily. Za prvé, nastavit Handled := True pouze v případě, že jste název funkce skutečně rozpoznali. Ponechání hodnoty False umožňuje jádru pokračovat v běžném zpracování neznámé funkce, takže jedna obsluha (handler) může sloužit několika sešitům, aniž by blokovala vše, co přes ni prochází. Za druhé, porovnávejte názvy bez ohledu na velikost písmen pomocí SameText, protože autoři vzorců píší discount( a DISCOUNT( zaměnitelně. Za třetí, argumenty přicházejí již vyhodnocené: volání DISCOUNT(A1) vám předá hodnotu buňky A1, nikoli odkaz na ni, takže funkce sama nemůže zjistit, odkud její vstupy pocházejí. Tento poslední bod definuje omezení, kterým se zabývá následující část

K tělu obsluhy přistupujte se stejnou opatrností jako k jakémukoli externímu vstupu. Pole Args odráží to, co autor vzorce zapsal, takže před jeho indexováním ověřte počet a typy argumentů a předem se rozhodněte, co neplatné volání vrátí: zda chybovou hodnotu typu Variant, nebo vyvolanou výjimku. Na této volbě záleží, protože výjimka vyhozená uvnitř obsluhy se šíří ven přes volání Calculate, které vyhodnocení spustilo. To je přijatelné v přísně kontrolovaném generátoru, ale nevhodné ve službě vyhodnocující sešity vytvořené uživateli, kde by jeden chybný vzorec shodil celý požadavek. V takovém prostředí chybu zachyťte uvnitř obsluhy a vraťte speciální hodnotu (sentinel), kterou okolní proces dokáže rozpoznat a zaznamenat

Funkce závislé na pozici vyžadují variantu Ex

Některé funkce oprávněně závisejí na tom, kde se vyhodnocují. Sazba lišící se podle listu, vyhledávání relativní k řádku nebo regionální násobitel platný pouze na konkrétních listech: nic z toho nelze vyřešit pouze na základě hodnot argumentů. Jednoduchá událost to vyjádřit nedokáže, a proto jádro nabízí událost OnUserFunctionEx, která se liší pouze jedním parametrem navíc:

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 nese SheetIndex, Row a Col vyhodnocované buňky. Pokud výsledek funkce závisí na její pozici byť jen minimálně, zapojte událost Ex hned od začátku. Dodatečné doplňování kontextu do obsluhy, kterou již volá třicet vzorců, je mnohem složitější než zvolit správnou signaturu hned první den. Obě události jsou si navíc natolik podobné, že není důvod začínat s tou užší

Vlastní funkce se nepřenášejí do Excelu

Vlastní funkce žije výhradně uvnitř vašeho procesu. Název DISCOUNT má význam pouze tehdy, když běží váš kód v Delphi a jeho obsluha události. Otevřete uložený soubor v Excelu a název DISCOUNT bude pouze neznámým slovem; buňka zobrazí chybu #NAME?, pokud na počítači uživatele náhodou neexistuje odpovídající funkce VBA nebo doplněk. To je návrhová realita, která odlišuje ukázku od finálního produktu, a nutí vás učinit rozhodnutí záměrně a nikoli až po zjištění chyb za provozu

Rozhodněte u každé buňky, který ze dvou modelů dodáváte. Buňky, u kterých má uživatel vidět přepočet uvnitř Excelu, musí být sestaveny výhradně ze slovníku vlastních funkcí Excelu. Buňky s chráněnou logikou by měly být vyhodnoceny v rámci procesu pomocí Calculate a uloženy jako prosté hodnoty, takže vlastní funkce funguje jako interní výpočetní pravidlo a nikoli jako součást obsahu souboru. Nejčastějším chybovým stavem, který spolehlivě generuje požadavky na podporu, je kompromisní řešení: uložit vzorec s vlastní funkcí a očekávat, že jej Excel zpracuje

Ukládání pouze hodnot má však i jednu skrytou výhodu: chrání duševní vlastnictví. Cenové pravidlo vyhodnocené ve vašem procesu v Delphi a odeslané jako číslo nelze ze sešitu zpětně analyzovat tak snadno jako viditelný vzorec, a uživatel jej nemůže narušit úpravou mezilehlé buňky. Generátory faktur, provizní výpisy a ceníky téměř vždy patří do této skupiny. Případem, který skutečně vyžaduje živé vzorce, je interaktivní model typu „co-když“ (what-if), kde se od zákazníka očekává změna vstupů a sledování posunů v součtech; ty pak musí být sestaveny z vlastního slovníku Excelu a definovaných názvů

Režimy výpočtů, iterace a R1C1: přepínače rozhraní XLS

Rozhraní XLS zpřístupňuje nastavení výpočtů na úrovni BIFF, která Excel čte ze souboru. Vlastnost CalculationMode přijímá hodnoty xlCalcManual, xlCalcAutomatic (výchozí) nebo xlCalcAutomaticExceptTables a určuje, jak se Excel chová po otevření souboru. A modelový sešit s tisíci vzorci je často vhodnější doručit v manuálním režimu, takže příjemce sám rozhodne, kdy ke spuštění výpočtů dojde. Vlastnosti EnableIteration (výchozí False) spolu s MaxIterations (výchozí 100) a MaxIterationChange (výchozí 0,001) odemykají záměrné cyklické odkazy typu iterační konvergence, které se objevují v některých finančních modelech. ReferenceStyle přepíná mezi zobrazením A1 a R1C1 a UseFullPrecision zrcadlí možnost Excelu pro výpočet s přesností podle zobrazení

Tyto vlastnosti žijí v rozhraní XLS, protože se mapují na záznamy BIFF; při generování souborů .xlsx navrhujte vzorce tak, aby nezávisely na iteračním nastavení, případně konvergované hodnoty vypočítejte v Delphi a zapište hotové výsledky

Maticové vzorce: veřejným vstupním bodem je XLSX

Starší maticové vzorce ve stylu CSE se vytvářejí pomocí TXLSXRange.SetArrayFormula:

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

Ekvivalentní metoda existuje i v hierarchii tříd XLS, nachází se však v privátní sekci, takže neexistuje podporovaný způsob, jak zapisovat nové maticové vzorce do souborů .xls. Již existující vzorce v otevřených souborech se přenášejí beze změny; co však udělat nemůžete, je vytvářet nové. Pravidlo, které z toho plyne, je prosté: pokud jsou součástí požadavků matice, cílejte na formát .xlsx. Pokud starší soubor .xls skutečně vyžaduje maticové chování, pragmatickou cestou je spočítat maticový výsledek v Delphi a zapsat jednotlivé hodnoty přímo do buněk

Dva související články na tomto webu: definované názvy a vzorce napříč listy se věnuje vyhodnocování názvů jádrem, a článek o exportu do CSV a TSV podrobně popisuje chování při exportu, kvůli kterému je explicitní výpočet nutný. Kompletní reference jádra, včetně podporované sady funkcí, se dodává s produktem HotXLS Component