Spoľahlivým spôsobom, ako vygenerovať naštýlovaný report v Exceli z Delphi, je začať so zošitom, ktorý už vytvoril dizajnér. Niekto z finančného oddelenia navrhne faktúru v Exceli: logo, hlavičky stĺpcov, orámovanie pásu s podrobnosťami, tučný riadok s celkovými súčtami a formáty meny. Váš kód tento súbor otvorí, vloží reálne dáta do buniek, ktoré pre ne dizajnér vyhradil, a výsledok uloží. Vzhľad patrí im, čísla patria vám. HotXLS, natívna knižnica pre Delphi a C++Builder, ktorá číta a zapisuje zošity XLS a XLSX bez nutnosti riadenia Excelu, vám poskytuje tri operácie potrebné pre tento prístup: vyhľadanie bunky podľa jej textu, kopírovanie rozsahu so zachovaním jeho štýlov a vzorcov a vkladanie riadkov tak, aby sa všetko pod nimi posunulo nadol spolu s dátami.
Jediným pravidlom, ktoré odlišuje generátor schopný prežiť úpravy šablóny od toho, ktorý zlyhá hneď pri prvej zmene, je nikdy neadresovať bunky pomocou pevných čísiel riadkov a stĺpcov. Šablóna je dokument, ktorý upravujú iní ľudia. Finančný tím pridá riadok s daňou, zvýši výšku riadku s logom, zmení poradie bloku s adresou a súborový formát vám nijako nepomôže: zápis formátu BIFF alebo OOXML prebehne úspešne bez ohľadu na to, či riadok 10 stále znamená to, čo znamenal minulý štvrťrok. Generátor, ktorý zapisuje prvý riadok s podrobnosťami na pevne zakódovaný riadok 10, pri prvom vložení bloku nad sekciu podrobností prepíše nesprávne bunky a sčíta celkový rozsah, ktorý už nepokrýva skutočné dáta. Nič nevyhodí výnimku, každé uloženie vráti úspech a jediným signálom chyby bude zákazník, ktorý si všimne nesprávnu faktúru.
Ukotvite každú súradnicu k zástupnému symbolu
Riešením je nechať šablónu niesť svoje vlastné súradnice. Dizajnér zapíše zástupné symboly ako {{CUSTOMER}}, {{DATE}} a {{DETAIL_START}} do buniek, ktorých sa musí generátor dotknúť, a generátor určí každú pozíciu za behu podľa toho, kde tieto symboly nájde. Úpravy rozloženia už nie sú problémom, pretože symbol sa posúva spolu s bunkou, v ktorej sa nachádza. Druhou stranou tejto dohody je pravidlo zlyhania: ak požadovaný symbol chýba, úloha sa zastaví skôr, ako sa dáta zákazníka zapíšu do súboru. Zmena šablóny by mala generovať chybový lístok a nie doručiť chybný dokument.
Vyhľadávanie symbolov: FindText a ReplaceText
Obe rodiny tried HotXLS poskytujú vyhľadávanie na úrovni hárka. Metóda FindText vracia riadok a stĺpec prvej bunky, ktorej text sa zhoduje, pričom obsahuje aj preťaženie s rozlišovaním veľkosti písmen. Metóda ReplaceText nahrádza každý výskyt a vracia počet zmien. Tieto dve metódy pokrývajú dva typy symbolov, ktoré zvyčajne používate. Jednu kotvu, napríklad meno zákazníka, vyhľadáte raz a píšete vedľa nej; symbol, ktorý sa má objaviť presne raz, napríklad dátum reportu, nahradíte a skontrolujete počet zmien. Na strane XLSX vyzerá napĺňanie s takýmto ukotvením nasledovne:
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 is 0-based
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');
// detail expansion and save follow below
finally
Book.Free;
end;
end;Dôležité sú dva detaily. Po prvé, metódy FindText a ReplaceText porovnávajú textovú hodnotu bunky; symbol vložený vnútri reťazca vzorca je pre ne neviditeľný, preto zástupné symboly patria do čistých buniek, nikdy nie do vzorcov. Po druhé, počet nahradení je vaším detektorom zmien. Šablóna, ktorá by mala obsahovať presne jeden symbol {{DATE}}, no nahlási nula nahradení, bola upravená a vyvolanie výnimky v tom momente je presne to, čo premení tichú zmenu rozloženia na viditeľné zlyhanie.
Klonovanie riadku s podrobnosťami bez straty štýlov alebo vzorcov
Sekcia s podrobnosťami na faktúre rastie spolu s dátami. Zápis hodnôt priamo do prázdnych riadkov pod vzorovým riadkom zahadzuje všetko, čo dizajnér pripravil: orámovania, číselné formáty, vzorce pre jednotlivé riadky. Vzorom, ktorý toto všetko zachováva, je ponechať v šablóne jeden kompletne naštýlovaný vzorový riadok a naklonovať ho pre každú položku. Metóda CopyRange duplikuje štýly a vzorce v jedom volaní, po ktorom generátor prepisuje už len hodnoty buniek.
const
DetailRow = 10; // the formatted sample row in the template
var
I: Integer;
begin
// Open space before the totals block first, so the SUM range
// below the detail band stretches together with the data.
if Length(Items) > 1 then
Sheet.InsertRows(DetailRow + 1, Length(Items) - 1);
for I := 0 to High(Items) do
begin
if I > 0 then // clone styles + formulas from the sample row
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]); // no '=' prefix
end;
end;Pozorne sledujte priradenie vzorca. Vlastnosť Formula na strane XLSX prijíma výraz bez úvodného znaku rovnosti, zatiaľ čo rozhranie XLS očakáva priradenie '=B10*C10' cez vlastnosť Value. Miešanie týchto dvoch konvencií je najčastejšou chybou pri prenose kódu medzi rodinami tried a prejde bez akejkoľvek chybovej hlášky: bunka len ukladá doslovný reťazec, ktorý Excel zobrazí ako text. Ak šablóna zdobí pás s podrobnosťami zlúčenými riadkami nadpisov, pamätajte, že iba ľavá horná bunka zlúčenej oblasti nesie hodnotu. Pravidlá rozloženia v súvisiacom článku o zlúčených bunkách v šablónach reportov vysvetľujú, prečo zlúčené oblasti patria úplne mimo dátového pásu.
Čo InsertRows presúva a čo ponecháva
Vkladanie riadkov pred blok celkových súčtov je to, čo udržiava rozsah SUM rozširujúci sa spolu s rastom sekcie podrobností. Na strane XLSX prenáša metóda InsertRows nadol s bunkami dlhý zoznam závislých štruktúr: zlúčené rozsahy, výšky riadkov, hyperodkazy, komentáre, ukotvené panely, rozsahy automatických filtrov, podmienené formáty, overenia údajov, tabuľky, definované názvy a kotvy obrázkov a grafov. V tomto zozname je jedna hranica, ktorú si treba zapamätať: prepisovanie vzorcov zasahuje iba odkazy v rámci rovnakého hárka. Vzorec na súhrnnom hárku, ktorý ukazuje do presunutej oblasti, si zachová svoje staré súradnice a ticho číta nesprávne bunky, preto sú súčty ťahané naprieč hárkami bezpečnejšie vyjadrené pomocou názvov na úrovni zošita. Tomuto vzoru sa venuje súvisiaci článok o definovaných názvoch a vzorcoch naprieč hárkami.
Starší formát XLS kladie hranicu na prísnejšom mieste. HotXLS uchováva kontingenčné tabuľky (pivot tables), tabuľky dopytov a externé dátové pripojenia v súboroch BIFF ako surové bloky bajtov. Prežívajú otvorenie a uloženie bez zmeny, no nie sú modelované, takže vloženie riadkov ich neovplyvní. Šablóna, ktorá umiestňuje kontingenčnú tabuľku pod rozširujúci sa blok detailov, sa uloží bez akéhokoľvek varovania, kým zdrojový obdĺžnik tabuľky sa posunie mimo skutočné dáta. Riešenie je štrukturálne, nie defenzívne: udržiavajte kontingenčné tabuľky a dopyty na hárkoch, do ktorých generátor nikdy nevkladá riadky, a k tomuto posunu nedôjde.
Prepočítajte pred doručením, alebo vedzte, prečo ste to vynechali
HotXLS nevyhodnocuje vzorce počas metódy SaveAs. Keď človek otvorí súbor, Excel prepočíta všetko (rozhranie XLS sprístupňuje vlastnosti CalculationMode a RecalcOnSave, ak to potrebujete riadiť), takže report smerujúci do doručenej pošty používateľa od vás nevyžaduje nič viac. Situácia sa však mení v momente, keď zošit slúži ako vstup pre iný program. Export do CSV zapisuje vzorce ako doslovný text a nikdy ich nepočíta, a akýkoľvek nadväzujúci parser dôverujúci uloženým hodnotám prečíta staré čísla alebo prázdne hodnoty. Pre tieto cesty vykonajte výpočet na serveri pomocou metódy Calculate, ktorá vyhodnotí ľubovoľný výraz voči načítanému zošitu a vráti výsledok:
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 celkového súčtu voči záznamu objednávky pred uložením je lacné poistenie s veľkou návratnosťou. Premení nesprávnu faktúru na zlyhanú úlohu. Operátor môže zlyhanú úlohu spustiť znova v priebehu sekúnd; nesprávna faktúra, ktorá už odišla do schránky zákazníka, stojí manažéra ospravedlnenie a opravu.
Dve rodiny tried, jeden algoritmus
Rovnaká logika sa prenáša medzi formátmi, no nie rovnaký kód. Trieda TXLSWorkbook pre starší formát .xls je založená na rozhraniach a využíva počítanie odkazov, s indexovaním hárkov od 1, a nikdy ju neuvoľňujete ručne. Trieda TXLSXWorkbook pre .xlsx je bežný objekt, ktorý musíte uvoľniť v konštrukcii try..finally, s indexovaním hárkov od 0 a konvenciou vzorcov ukázanou vyššie. Metódy FindText, ReplaceText, CopyRange a InsertRows existujú na oboch stranách, takže princíp vyhľadania, klonovania a prepočítania sa prenáša čisto. Praktickou radou je zaviazať sa k jednému formátu pre celý proces spracovania, alebo skryť oba životné cykly objektov za jednoduchý vlastný adaptér, namiesto zanášania týchto rozdielov do samotného generátora.
Veľkosť zriedka zohráva rolu pri type reportov, ktoré tento vzor produkuje. Klonovanie naštýlovaného riadku niekoľko tisíckrát je pre dnešný hardvér zanedbateľné. Cesta ukladania sa stáva úzkym hrdlom až vtedy, keď pás s podrobnosťami dosahuje šesťciferný počet riadkov, a v tom momente zapnutie vlastnosti StreamingWrite posiela XML kód hárka priamo do cieľového balíka namiesto jeho ukladania do vyrovnávacej pamäte; článok o streamovanom zápise pre dávkové úlohy na serveri sa zaoberá tým, kedy sa tento krok oplatí. Grafy sa správajú rovnako ako zvyšok rozloženia: na strane XLSX sa kotva grafu aj odkazy na jeho rady posunú, ak nad nimi prebehne InsertRows, takže graf pod riadkom súčtov zostane naviazaný na správne dáta, zatiaľ čo na strane XLS sa grafy nachádzajú na vlastných grafových hárkoch a (podobne ako kontingenčné tabuľky) sa nikdy neposúvajú. To je ďalší argument pre ponechanie prezentačných hárkov oddelených od hárka, ktorý generátor rozširuje.
Tento prístup vyhľadania, klonovania a prepočítania umožňuje dizajnérovi vlastniť vzhľad zošita, zatiaľ čo váš kód vlastní jeho obsah, čo zvyčajne robí generovaný výstup z Excelu udržiavateľným. Vyhľadávacie, kopírovacie a vkladacie volania ukázané v tomto článku spolu s výpočtovým enginom použitým na kontrolu celkového súčtu pred doručením sa dodávajú s komponentom HotXLS Component pre Delphi a C++Builder.