Definované názvy a vzorce napříč listy v Delphi s HotXLS

Definovaný název je popisek zastupující konstantu, rozsah buněk nebo výraz vzorce. V sešitu se ukládá pouze jednou a odkazuje se na něj symbolicky všude, kde je to potřeba. Pokud ve vzorci napíšete TaxRate, jádro jej vyhodnotí na cokoli, co definice názvu obsahuje — ať už je to hodnota 0.08 nebo rozsah Data!$A$2:$D$100. Odkaz napříč listy je související koncept: zápis Data!D2 přistupuje k buňce na jiném listu tím, že adresu kvalifikuje názvem listu. Spojíte-li tyto dvě možnosti dohromady, souhrnný list může sčítat podrobný list pomocí názvu, který se o konkrétní adrese vůbec nezmiňuje, což je přesně to, co požadujete v sešitu, který generátor sestavuje a účetní později kontroluje

HotXLS, nativní knihovna v Delphi od losLab pro soubory XLS a XLSX, zpřístupňuje tabulku názvů obou formátů s možností vytváření, vyhledávání a mazání, a navíc nabízí výpočetní jádro vzorců vyhodnocující názvy a odkazy napříč listy v rámci procesu. Oba formáty si udržují samostatné hierarchie tříd a rozdíly mezi jejich rozhraními API pro názvy jsou tím, co může zkomplikovat přenos kódu z jednoho formátu do druhého

Dvě úložiště názvů bez společného rozhraní

Na straně XLS vrací metoda TXLSWorkbook.GetNames kolekci IXLSNames, jejíž přetížení Add(Name, RefersTo, Visible) zapisuje název do tabulky názvů BIFF. Jednotlivé záznamy se vracejí jako objekty IXLSName nesoucí Name, RefersTo, vyhodnocený RefersToRange a metodu Delete. Na straně XLSX představuje vlastnost TXLSXWorkbook.DefinedNames kolekci TXLSXDefinedNames s metodami Add, FindByName a DeleteByName

Konvence vyhledávání se liší způsobem, který se projeví spíše při převodu kódu za běhu než při kompilaci. Výchozí vlastnost Item kolekce XLS přijímá typ Variant, takže se vůči ní vyhodnocují jak Names[0], tak Names['TaxRate']. Kolekce XLSX žádnou takovou výchozí vlastnost nemá; voláte metodu FindByName('TaxRate'), která v případě absence názvu vrátí hodnotu nil. Kód napsaný pro jedno rozhraní se vůči druhému zkompiluje pouze náhodou a chyba se pak projeví spíše jako přístup k neexistujícímu objektu (nil access) za běhu než jako červené podtržení v IDE

Rozsah platnosti je prvním rozhodnutím, nikoli příznakem přidávaným později

Definovaný název má platnost buď pro celý sešit (workbook-scoped), kdy je viditelný pro vzorce na všech listech, nebo pro konkrétní list (sheet-scoped), kdy je viditelný pouze na svém domovském listu. V rozhraní API XLSX představuje tento rozdíl jediný volitelný parametr. Volání DefinedNames.Add(AName, AFormula) vytvoří název na úrovni sešitu, zatímco Add(AName, AFormula, ASheetIndex) jej naváže na konkrétní list. Při zpětném čtení vrací vlastnost TXLSXDefinedName.SheetIndex hodnotu -1 pro platnost v celém sešitu, v opačném případě vrací index listu od nuly

Rozsah platnosti zároveň slouží jako vaše strategie předcházení kolizím, což je důvod, proč si jej ujasnit ještě před zápisem prvního názvu. Excel povoluje lokální název Total na každém listu a zároveň globální název Total pro celý sešit. Vzorec na daném listu pak vyhodnocuje lokální název prioritně. Generované sešity by toho měly záměrně využívat. Globální parametry využívané více listy, jako jsou daňové sazby, měnové kurzy a sledované období, patří do rozsahu sešitu. Pomocné rozsahy, na které odkazují pouze vzorce jednoho listu, je bezpečnější definovat s platností pouze pro daný list, kde nemohou nic zastínit a nic nezastíní je

var
  Book: TXLSXWorkbook;
  Data, Summary: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Summary := Book.Sheets.Add('Summary');
    // ... fill Data!A2:D100 with detail rows ...

    Book.DefinedNames.Add('TaxRate', '0.08');                // workbook scope, a constant
    Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100');  // workbook scope, a range
    Book.DefinedNames.Add('LocalNote', 'Summary!$B$1', 1);   // scoped to sheet index 1 only

    // XLSX formulas take no leading '='
    Summary.Cells[2, 2].Formula := 'SUM(Data!D2:D100)*TaxRate';
    Book.SaveAs('model.xlsx');
  finally
    Book.Free;
  end;
end;

Definovaný název nemusí ukazovat pouze na rozsah buněk. Výše uvedený TaxRate odkazuje na pouhou konstantu 0.08, což je nejčistší způsob, jak definovat globální parametry. Zobrazí se jednou ve Správci názvů Excelu, každý vzorec na něj symbolicky odkazuje a změna sazby v příštím čtvrtletí představuje úpravu jednoho řádku v generátoru namísto vyhledávání ve čtrnácti sestavených řetězcích vzorců

Znaménko rovná se, které patří pouze na jednu stranu

Způsob zadávání vzorců je místem, kde se přenášený kód nejčastěji rozbíjí, protože obě rozhraní se neshodnou na používání znaménka rovná se. Buňky XLS přijímají vzorce přes vlastnost Value se začínajícím =. Buňky XLSX mají vyhrazenou vlastnost Formula, která přijímá výraz bez této předpony. Pokud zapíšete '=SUM(A1:A10)' do vlastnosti TXLSXCell.Formula, znaménko rovná se se stane součástí uloženého textu výrazu namísto označení vzorce a soubor se nebude chovat tak, jak se choval stejný řetězec na straně XLS

var
  Book: IXLSWorkbook;   // interface-counted: do not Free
  Names: IXLSNames;
begin
  Book := TXLSWorkbook.Create;
  // assume a sheet named 'Data' already holds the detail rows
  Names := Book.GetNames;
  Names.Add('TaxRate', '0.08');
  Names.Add('Helper', 'Data!$A$2:$A$100', False);  // False = hidden from the Name Manager

  // XLS formulas go through Value, with the '=' prefix
  Book.Sheets[1].Cells.Item[2, 2].Value := '=SUM(Data!A2:A100)*TaxRate';
  Book.SaveAs('model.xls');
end;

Tato ukázka odhaluje další dvě specifika strany XLS. Kolekce listů začíná indexem 1, takže Sheets[1] je první list, na rozdíl od indexace od nuly u XLSX Sheets[0]. A třetí parametr metody Add vytváří skrytý název: ten je v souboru přítomen a vzorce jej mohou používat, ale je neviditelný ve Správci názvů Excelu. Skryté názvy jsou správným nástrojem pro vnitřní logiku generátoru, kterou by koncoví uživatelé neměli omylem upravovat ani mazat

Odkazy napříč listy a co se stane při přesunu řádků

Obě výpočetní jádra vzorců přijímají standardní syntaxi napříč listy. Jednoduché názvy listů se kvalifikují přímo jako Data!A1; název s mezerami nebo interpunkcí vyžaduje jednoduché uvozovky, například 'Sheet With Space'!A1. V textu RefersTo u definovaného názvu sáhněte téměř vždy po absolutních odkazech, jako je Data!$A$2:$D$100. Relativní odkaz uvnitř definovaného názvu se vyhodnocuje relativně k buňce, která jej používá, což je záměrná vlastnost Excelu a spolehlivý zdroj zmatků, pokud se aktivuje omylem

Strukturální úpravy jsou chvílí, kdy se správa odkazů napříč listy vyplatí, a strana XLSX udržuje názvy v těchto situacích konzistentní. Metody InsertRows a DeleteRows posouvají definované rozsahy názvů společně s buňkami, sloučeními, hypertextovými odkazy i kotvami grafů. Název ukazující na Data!$A$2:$D$100 tak stále pokrývá datový blok i poté, co generátor otevře mezeru nad ním. U vzorců je třeba pamatovat na jedno zdokumentované upozornění: vložení řádku upravuje pouze ty odkazy, které cílí na upravovaný list. Vzorec na listu Summary odkazující na Data!D2:D100 se přepíše při vložení řádků na list Data, což je obvykle požadované chování. Raději si to však ověřte, než na to spoléhejte, protože jádro vám to sdělí velmi jednoduše:

// the calculation engine resolves names and cross-sheet references in-process
V := Book.Calculate('SUM(Data!D2:D100)*TaxRate');
if VarIsNumeric(V) then
  Log('net total checks out: ' + FloatToStr(V));

Metoda Calculate vyhodnocuje libovolný výraz vůči aktuálnímu stavu sešitu bez nutnosti cokoli ukládat, což z ní dělá ideální kontrolní prvek pro testy generátoru. Vypočítejte očekávaný souhrn ze zdrojových dat v Pascalu, vyhodnoťte vlastní vzorec sešitu a oba výsledky porovnejte. Článek o jádru vzorců popisuje, co a kdy jádro vyhodnocuje a jak jej rozšířit o vlastní funkce

Názvy _xlnm patřící vrstvě vlastností

Pokud otevřete tabulku názvů generovaného souboru v nízkoúrovňovém inspektoru, najdete záznamy, které jste nikdy nezapsali: _xlnm.Print_Area, _xlnm.Print_Titles a jim podobné. Tímto způsobem formát OOXML (ECMA-376 / ISO 29500) ukládá tiskové oblasti a opakované řádky záhlaví — jako definované názvy s vyhrazenými identifikátory. HotXLS je spravuje prostřednictvím vyhrazených vlastností listu, takže nastavení PrintArea nebo PrintTitleRows zapíše odpovídající položku _xlnm.* za vás

Úskalím je ruční zasahování do tohoto vyhrazeného jmenného prostoru. Pokud přidáte položku _xlnm.Print_Area přes DefinedNames.Add a zároveň nastavíte vlastnost PrintArea, sešit bude obsahovat dvě konfliktní definice pro jeden vyhrazený název. Excel tento stav řeší způsoby, na které by se žádný produkt neměl spoléhat. S každým identifikátorem začínajícím na _xlnm. zacházejte jako s prvkem patřícím vrstvě vlastností. Chcete-li zkontrolovat nastavení tisku, čtěte vlastnosti, nikoli tabulku názvů. Článek o zabezpečení a nastavení stránky se těmto vlastnostem tiskové oblasti věnuje v kontextu

Dva limity, které je dobré znát před dokončením návrhu

Definované názvy se nepřenášejí přes pohodlný převodní most z XLS do XLSX. Metoda SaveXLSWorkbookAsXLSX kopíruje obsah buněk a základní formátování, tabulka názvů však není na jejím dokumentovaném seznamu kopírování. Sešit, který na svých názvů závisel, je při převodu ztratí. Po převodu názvy znovu vytvořte pomocí DefinedNames.Add. Tento krok je snazší, než se zdá, protože vám dává prostor pro normalizaci jejich rozsahů platnosti namísto pouhého přenesení toho, co se v souboru XLS zrovna nacházelo

Druhým limitem je nesoulad mezi řetězci vzorců a názvy listů. Excel při interaktivním přejmenování přepisuje odkazy na listy uvnitř vzorců a názvů, takže soubory upravované uživatelem v Excelu zůstávají samy o sobě konzistentní. Riziko je na straně generátoru: když kód v Pascalu sestavuje řetězce vzorců z doslovného názvu listu, jeho přejmenování na jednom místě a opomenutí na druhém vytvoří odkaz na list, který již neexistuje. Udržujte název listu v jediné konstantě v Delphi a předávejte ji jak metodě Sheets.Add, tak sestavování vzorců — tyto dvě části se pak nikdy neshodnou. Je to stejný princip, který hovoří pro pojmenovávání výstupních buněk reportu namísto pevného kódování adres: šablona, jejíž buňka s celkovým součtem je pojmenovaná, funguje i poté, co návrhář vloží tři řádky nad ni, zatímco generátor zapisující na pevnou adresu B17 tiše zapíše číslo na špatné místo. Článek o generování reportů ze šablon staví přesně na tomto vzoru

Kompletní API definovaných názvů pro oba formáty, společně s referencí výpočetního jádra vzorců, se dodává s produktem HotXLS Component