Generování reportů do Excelu ze šablon v Delphi s HotXLS

Spolehlivý způsob, jak z Delphi vytvořit stylovaný Excel report, je vyjít z workbooku, který už sestavil designér. Někdo z finančního oddělení připraví fakturu v Excelu: logo, záhlaví sloupců, ohraničení detailního pásma, tučný řádek se součty, formáty měny. Váš kód tento soubor otevře, vloží živá data do buněk, které pro ně designér vyhradil, a výsledek uloží. Vzhled patří jemu, čísla patří vám. HotXLS, nativní knihovna pro Delphi a C++Builder, která čte a zapisuje workbooky XLS a XLSX bez řízení Excelu, vám dává tři operace, které tento přístup potřebuje: vyhledat buňku podle jejího textu, zkopírovat rozsah se zachovanými styly a vzorci a vložit řádky tak, aby se vše pod nimi posunulo spolu s daty

Jediné pravidlo, které odlišuje generátor přežívající úpravy šablony od generátoru, který se rozbije při první z nich, zní: nikdy neadresovat buňky pomocí doslovných čísel řádků a sloupců. Šablona je dokument, který upravují jiní lidé. Finanční tým přidá řádek s daní, zvýší výšku řádku s logem, přeuspořádá adresní blok, a formát souboru vám v tom vůbec nepomůže: uložení v BIFF nebo OOXML uspěje bez ohledu na to, jestli řádek 10 stále znamená to, co znamenal minulé čtvrtletí. Generátor, který zapisuje první detailní řádek na pevně zakódovaný řádek 10, při první vložené sekci nad detailní oblastí otiskne položky do špatných buněk a sečte rozsah součtů, který už data nepokrývá. Nic nevyvolá výjimku, každé uložení vrátí úspěch a jediným signálem je zákazník, který si všimne chybné faktury

Ukotvěte každou souřadnici k zástupnému tokenu

Řešením je, aby si šablona nesla vlastní souřadnice sama. Designér zapíše do buněk, kterých se generátor musí dotknout, tokeny jako {{CUSTOMER}}, {{DATE}} a {{DETAIL_START}}, a generátor si za běhu odvodí každou pozici z toho, kde tyto tokeny najde. Na úpravách rozvržení už nezáleží, protože token se pohybuje spolu s buňkou, ve které sedí. Druhou polovinou smlouvy je pravidlo pro selhání: pokud požadovaný token chybí, úloha se zastaví dřív, než se k souboru dostanou jakákoli zákaznická data. Šablona, která se rozjela od zdroje, by měla vyprodukovat neúspěšný job tiket, ne doručený dokument

Hledání tokenů: FindText a ReplaceText

Obě rodiny tříd HotXLS nabízejí vyhledávání na úrovni listu. FindText vrací řádek a sloupec první buňky, jejíž text odpovídá, s přetížením, které přidává rozlišování velkých a malých písmen. ReplaceText nahradí každý výskyt a vrátí počet změn. Tyto dvě metody pokrývají dva druhy tokenů, se kterými se obvykle setkáte. Jediný kotevní bod, jako je jméno zákazníka, najdete jednou a zapíšete vedle něj; token, který by se měl objevit přesně jednou, jako je datum reportu, nahradíte a zkontrolujete počet. Na straně XLSX vypadá naplnění, které se takto ukotvuje, takto:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  R, C: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open('invoice-template.xlsx') <> 1 then
      raise Exception.Create('Cannot open invoice template');
    Sheet := Book.Sheets[0];               // TXLSXSheets.Items je indexováno od 0

    if not Sheet.FindText('{{CUSTOMER}}', R, C) then
      raise Exception.Create('Template drift: {{CUSTOMER}} anchor missing');
    Sheet.Cells[R, C].Value := 'ACME Corp';

    if Sheet.ReplaceText('{{DATE}}',
         FormatDateTime('yyyy-mm-dd', Date)) = 0 then
      raise Exception.Create('Template drift: {{DATE}} token missing');
    // rozšíření detailů a uložení následují níže
  finally
    Book.Free;
  end;
end;

Záleží na dvou detailech. Za prvé, FindText i ReplaceText porovnávají textovou hodnotu buňky; token vložený uvnitř řetězce vzorce je pro ně neviditelný, takže zástupné tokeny patří do obyčejných buněk, nikdy do vzorců. Za druhé, počet nahrazení je váš detektor rozjetí od šablony. Šablona, která by měla obsahovat přesně jeden token {{DATE}}, ale hlásí nula nahrazení, byla upravena, a vyvolání výjimky v tu chvíli je přesně to, co mění tiché rozjetí rozvržení na viditelné selhání

Klonování detailního řádku beze ztráty stylů či vzorců

Detailní sekce faktury roste s daty. Zápis hodnot přímo do prázdných řádků pod vzorovým řádkem zahodí vše, co designér připravil: ohraničení, číselné formáty, vzorce vázané na jednotlivé řádky. Vzor, který toto vše zachová, spočívá v tom, že v šabloně necháte jeden plně naformátovaný vzorový řádek a pro každou položku ho naklonujete. CopyRange naráz duplikuje styly i vzorce, a generátor pak přepíše jen buňky s hodnotami

const
  DetailRow = 10;            // formátovaný vzorový řádek v šabloně
var
  I: Integer;
begin
  // Nejprve uvolněte místo před blokem součtů, aby se rozsah SUM
  // pod detailním pásmem roztahoval společně s daty.
  if Length(Items) > 1 then
    Sheet.InsertRows(DetailRow + 1, Length(Items) - 1);

  for I := 0 to High(Items) do
  begin
    if I > 0 then              // klonuje styly a vzorce ze vzorového řádku
      Sheet.CopyRange(DetailRow, 1, DetailRow, 5, DetailRow + I, 1);
    Sheet.Cells[DetailRow + I, 1].Value := Items[I].Name;
    Sheet.Cells[DetailRow + I, 2].Value := Items[I].Qty;
    Sheet.Cells[DetailRow + I, 3].Value := Items[I].UnitPrice;
    Sheet.Cells[DetailRow + I, 4].Formula :=
      Format('B%d*C%d', [DetailRow + I, DetailRow + I]);  // bez prefixu '='
  end;
end;

Přiřazení vzorce sledujte pozorně. Vlastnost XLSX Formula přijímá výraz bez úvodního znaménka rovná se, zatímco fasáda XLS očekává '=B10*C10' přiřazené přes Value. Zaměnění těchto dvou konvencí je nejčastější chyba při přenosu mezi rodinami tříd a selže beze slova: buňka pak jen drží doslovný řetězec, který Excel zobrazí jako text. Pokud šablona zdobí detailní pásmo sloučenými titulními řádky, pamatujte, že hodnotu nese jen levá horní buňka sloučené oblasti. Pravidla rozvržení v článku o sloučených buňkách v šablonách reportů řízených rozvržením vysvětlují, proč sloučené oblasti patří zcela mimo datové pásmo

Co InsertRows přesouvá a co ponechává na místě

Vkládání řádků před blok součtů je to, co udržuje rozsah SUM roztahující se s růstem detailní sekce. Na straně XLSX InsertRows stahuje s buňkami dolů dlouhý seznam závislých struktur: sloučené rozsahy, výšky řádků, hypertextové odkazy, komentáře, ukotvená okna, rozsahy automatického filtru, podmíněné formátování, ověřování dat, tabulky, definované názvy a kotvy obrázků a grafů. V tomto seznamu je jedna hranice, kterou stojí za to si zapamatovat. Přepisování vzorců zasahuje jen odkazy v rámci téhož listu. Vzorec na souhrnném listu, který ukazuje do přesunuté oblasti, si podrží staré souřadnice a potichu čte ze špatných buněk, a proto je bezpečnější vyjadřovat součty přenášené mezi listy přes názvy na úrovni workbooku. Postup rozebírá navazující článek o definovaných názvech a vzorcích napříč listy

Starší formát XLS vede hranici na tvrdším místě. HotXLS uchovává kontingenční tabulky, dotazové tabulky a externí datová připojení v souborech BIFF jako syrové bloky bajtů. Otevření i uložení přežijí beze změny, ale nejsou modelované, takže se jich vkládání řádků nikdy nedotkne. Šablona, která zaparkuje kontingenční tabulku pod rozšiřujícím se detailním blokem, se uloží zcela bez varování, zatímco zdrojový obdélník kontingenční tabulky se od dat postupně vzdaluje. Řešením je strukturální přístup, ne obranný: obsah kontingenčních a dotazových tabulek držte na listech, do kterých generátor nikdy nevkládá, a k zastarání pak nemůže dojít

Přepočítejte před doručením, nebo vězte, proč jste to vynechali

HotXLS během SaveAs vzorce nevyhodnocuje. Když soubor otevře člověk, Excel vše přepočítá sám (fasáda XLS pro řízení tohoto chování nabízí CalculationMode a RecalcOnSave), takže report mířící do lidské schránky od vás nic dalšího nepotřebuje. Obrázek se změní ve chvíli, kdy workbook krmí jiný program. Export do CSV zapisuje vzorce jako jejich doslovný text a nikdy je nevyhodnocuje, a jakýkoli navazující parser, který důvěřuje uloženým hodnotám, přečte zastaralá čísla nebo prázdná pole. Pro tyto cesty počítejte na serveru pomocí Calculate, které vyhodnotí libovolný výraz proti načtenému workbooku a vrátí výsledek:

var
  Total: Variant;
  LastDetail: Integer;
begin
  LastDetail := DetailRow + Length(Items) - 1;
  Total := Book.Calculate(Format('SUM(Invoice!D%d:D%d)',
    [DetailRow, LastDetail]));
  if (not VarIsNumeric(Total)) or
     (Abs(Total - ExpectedTotal) > 0.005) then
    raise Exception.Create('Invoice total does not match the order record');

  if Book.SaveAs('invoice-2026-0611.xlsx') <> 1 then
    raise Exception.Create('Save failed: check output path and permissions');
end;

Kontrola vypočítaného součtu proti objednávkovému záznamu před uložením je levné pojištění s dobrou návratností. Mění chybnou fakturu na neúspěšnou úlohu. Operátor může neúspěšnou úlohu zopakovat během vteřin; chybná faktura už v poštovní schránce zákazníka stojí account manažera omluvu a opravu

Dvě rodiny tříd, jeden algoritmus

Stejná logika se přenáší mezi formáty, ale ne stejný kód. TXLSWorkbook pro starší .xls je založený na rozhraních a počítání referencí, s indexováním listů od 1, a nikdy ho neuvolňujete ručně. TXLSXWorkbook pro .xlsx je obyčejný objekt, který musíte uvolnit v bloku try..finally, s indexováním listů od 0 a konvencí pro vzorce popsanou výše. FindText, ReplaceText, CopyRange a InsertRows existují na obou stranách, takže se vzor kotva-klon-přepočet přenáší čistě. Praktická rada zní: držet se v rámci jednoho pipeline jednoho formátu, nebo skrýt oba životní cykly objektů za vlastní tenký adaptér, místo aby se ten rozdíl rozprostíral po celém generátoru

Velikost u tohoto typu reportu obvykle nehraje roli. Naklonovat stylovaný řádek několik tisíckrát nepředstavuje pro současný hardware žádný problém. Ukládání se stane úzkým hrdlem až ve chvíli, kdy detailní pásmo naroste do šesticiferných počtů řádků, a v tu chvíli nastavení StreamingWrite posílá XML listu přímo do výstupního balíčku místo bufferování; kdy se tato výměna vyplatí, popisuje článek o streamovaném zápisu pro dávkové úlohy na serveru. Grafy se chovají stejně jako zbytek rozvržení: na straně XLSX se při běhu InsertRows nad nimi posunou jak kotva grafu, tak odkazy na jeho řady, takže graf pod řádkem se součty zůstane svázán se správnými daty, zatímco na straně XLS sedí grafy na vlastních listech grafů a stejně jako kontingenční tabulky se nikdy neposunou. To je další argument pro to, aby prezentační listy zůstaly mimo list, který generátor rozšiřuje

Tento přístup kotva-klon-přepočet nechává designéra vlastnit, jak workbook vypadá, zatímco váš kód vlastní to, co říká, a to je obvykle to, díky čemu se vygenerovaný výstup v Excelu vyplatí udržovat. Volání pro vyhledávání, kopírování a vkládání ukázaná zde, spolu s enginem pro vzorce použitým při kontrole součtu před doručením, jsou součástí HotXLS Delphi Component pro Delphi a C++Builder