Generovanie súborov Excel v Delphi bez automatizácie Office

Ak je jedinou úlohou servera generovať súbory Excel, nemá dôvod spúšťať samotný program Excel. Inštalácia balíka Office na zostavovací server alebo reportovaciu službu s cieľom riadiť ho prostredníctvom COM automatizácie je nesprávnym návrhom – a je ním už od samotného vzniku tejto praxe. Samotný Microsoft to uvádza v usmerneniach, ktoré za posledných dvadsať rokov nezmäkli: Office nie je navrhnutý ani licencovaný na to, aby bol automatizovaný zo serverového procesu bežiaceho bez prítomnosti používateľa. Správnou odpoveďou je priamy zápis bajtov formátov BIFF a OOXML, a to úplne bez prítomnosti Excelu. To je hlavná myšlienka knižnice HotXLS, natívnej knižnice pre Object Pascal, ktorá sama číta a zapisuje tabuľkové formáty, takže neexistuje žiadna desktopová aplikácia, ktorá by mohla zamrznúť, spotrebovávať pamäť alebo vyžadovať platby za licencie na používateľa

Prečo riadenie EXCEL.EXE zo služby zlyháva

COM automatizácia na diaľku ovláda desktopový program, a ten predpokladá tri veci, ktoré mu služba systému Windows nedokáže poskytnúť: načítaný používateľský profil, interaktívnu reláciu s oknami a človeka sledujúceho obrazovku. Odstráňte tieto podmienky a zlyhania sa prejavia v podobe, ktorú žiadny vývojársky počítač nedokáže reprodukovať. Výzva na obnovu súboru, chyba doplnku alebo dialógové okno aktivácie licencie sa otvoria v relácii na ploche, ktorú nikto nevidí, a volanie automatizácie, ktoré to vyvolalo, nikdy neskončí. Volajúci kód nakoniec vyprší (timeout) a zlyhá; inštancia Excelu však často beží ďalej ako osirotený proces, ktorý drží zámky súborov a ohrozuje ďalšie spustenie. Každý, kto zažil hromadenie osirotených procesov EXCEL.EXE pod servisným účtom, vie, o čom je reč

Škálovanie nie je o nič lepšie, ani keď k žiadnemu pádu nedôjde. Inštancia Excelu predstavuje proces spracovania jedného zošita, každý prístup k vlastnosti platí daň za medziprocesový prenos COM a stroj spúšťajúci kód nesie licenciu Office, ktorej podmienky presne toto použitie vylučujú. Väčšina tímov naráža na tieto limity postupne pri výpadkoch, čo je zvyčajne moment, kedy sa úloha „nahradiť vrstvu COM“ dostane do plánu prác

Pred začatím prepisu vyriešte jednu otázku rozsahu prác, pretože tá rozhodne o tom, koľko práce bude reálnej. Kód využívajúci COM automaticky neodovzdáva len hodnoty buniek. Volá Workbook.SaveAs s konštantami formátu, vynucuje prepočet, nastavuje tlač a niekedy siaha po schránke (clipboard). Prejdite starý kód a spíšte si, ktoré z týchto správaní reálne ovplyvňujú výstup, pretože každé z nich spadá do inej časti natívnej knižnice a niektoré (ako spolupráca so schránkou) nemajú na strane servera zmysel a mali by sa odstrániť, nie portovať

Dva natívne enginy, dva modely vlastníctva

HotXLS nahrádza proces Excelu dvoma priamymi implementáciami formátov. Engine pre prúd záznamov BIFF8 (trieda TXLSWorkbook, jednotka lxHandle) spracováva formát .xls. Natívny zapisovač balíkov OOXML (trieda TXLSXWorkbook, jednotka lxHandleX) vytvára súbory .xlsx v súlade so štandardom ECMA-376 / ISO/IEC 29500. Na serveri netreba nič registrovať ani inštalovať a môžete mať otvorených toľko zošitov súčasne, koľko pamäť dovolí

Čo vývojárov na začiatku často pletie, je skutočnosť, že obe rozhrania spravujú pamäť odlišne a rozdiel sa prejaví až pri páde programu:

var
  Book: IXLSWorkbook;          // interface reference: released automatically
  Sheet: IXLSWorksheet;
  BookX: TXLSXWorkbook;        // plain object: you free it
  SheetX: TXLSXWorksheet;
begin
  // BIFF8 .xls output - no Free; the interface refcount owns it
  Book := TXLSWorkbook.Create;
  Sheet := Book.Sheets.Add;
  Sheet.Name := 'Report';
  Sheet.Cells.Item[1, 1].Value := 'Generated without Excel';
  Book.SaveAs('report.xls');

  // OOXML .xlsx output - explicit lifetime
  BookX := TXLSXWorkbook.Create;
  try
    SheetX := BookX.Sheets.Add('Report');
    SheetX.Cells[1, 1].Value := 'Generated without Excel';
    BookX.SaveAs('report.xlsx');
  finally
    BookX.Free;
  end;
end;

Rozhranie XLS využíva počítanie odkazov (reference counting) prostredníctvom rozhrania IXLSWorkbook. Deklarujte premennú ako typ rozhrania a nikdy na nej nevolajte Free. Ak by ste rovnaký objekt držali v premennej typu bežného objektu a uvoľnili ho sami, mechanizmus počítania odkazov ho uvoľní druhýkrát, čo spôsobí pád. Rozhranie XLSX predstavuje bežný objekt, ktorý vyžaduje klasickú konštrukciu try..finally. Adresácia buniek začína od 1 na oboch stranách, čo je jediné miesto, kde sa zhodujú. Kolekcie hárkov sa však líšia: indexácia Entries na strane XLS začína od 1, zatiaľ čo indexátor Items pre XLSX začína od 0, pričom tento rozdiel sa úspešne skompiluje pri oboch variantoch a prejaví sa až za behu programu

Zápis zošita priamo do HTTP odpovede

Export na strane servera zvyčajne nemá dôvod zapisovať dáta na disk. Dočasné súbory vyžadujú politiku čistenia, kolidujú pri paralelných požiadavkách a ponechávajú dáta zákazníkov na úložiskách, ktoré nikto nekontroluje. Obe rozhrania prijímajú objekt TStream vo svojich preťaženiach metódy SaveAs, takže zošit môže ísť priamo do sieťovej odpovede:

Mem := TMemoryStream.Create;
Book := TXLSXWorkbook.Create;
try
  Sheet := Book.Sheets.Add('Data');
  Sheet.Cells[1, 1].Value := 'Generated ' + DateTimeToStr(Now);
  Book.SaveAs(Mem);          // writes from the CURRENT stream position
  Mem.Position := 0;         // rewind before handing the stream over
  Response.ContentType :=
    'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
  Response.ContentStream := Mem;   // the framework now owns Mem
finally
  Book.Free;
end;

Posun na začiatok (rewind) je riadok, ktorý si zaslúži svoj komentár. Metóda SaveAs(Stream) zapisuje od aktuálnej pozície v prúde a po zápise sa už neposúva na začiatok. Vynechajte riadok Mem.Position := 0 a klient stiahne súbor s nulovou veľkosťou, prípadne Excel nahlási poškodený súbor. Toto je najčastejšia a najzákernejšia chyba v kóde pre tabuľky bežiacom na webe, pretože prejde akýmkoľvek jednotkovým testom, ktorý overuje iba nenulovú dĺžku prúdu

Jeden kód na zostavenie zošita obslúži každý iný doručovací formát bez potreby zmeny štruktúry. Metóda SaveAsCSV rieši požiadavku na čisté dáta, SaveAsHTML spracováva vloženie do portálovej stránky, SaveAsRTF plní textové editory a SaveAsODS pokrýva legislatívne požiadavky na formát OpenDocument – to všetko s preťaženiami pre súbory aj prúdy. Jediná exportná rutina s parametrom formátu tak nahrádza niekdajšie štyri samostatné COM makrá. Vlastnosť TXLSXHtmlExportOptions pre HTML export nesie nadpis, CSS triedu a prepínač medzi fragmentom a celým dokumentom, čo odbremeňuje webovú časť od dodatočných úprav značiek pomocou regulárnych výrazov

Hodnoty vzorcov bez výpočtového procesu Excelu

V rámci COM automatizácie Excel prepočítaval všetko automaticky, čo sa pri odchode od COM stráca. Metóda SaveAs ukladá vzorce ako text bez ich vyhodnotenia; čísla sa objavia až vtedy, keď používateľ otvorí súbor v Exceli a prebehne prepočet – toto správanie umožňuje rozhranie XLS ladiť cez vlastnosti RecalcOnSave a CalculationMode. Pre súbor doručovaný človeku je to úplne v poriadku. Je to však nesprávne pre službu, ktorá musí hodnotu overiť pred odoslaním, a nesprávne pre export do CSV, ktorý by zapísal text vzorca namiesto výsledku. V týchto prípadoch musíte vzorce vyhodnotiť na serveri pomocou vstavaného enginu:

SheetX.Cells[1, 1].Value := 1200;
SheetX.Cells[2, 1].Value := 950;
SheetX.Cells[3, 1].Formula := 'SUM(A1:A2)';   // XLSX facade: no '=' prefix
Total := BookX.Calculate('SUM(A1:A2)');       // evaluate on the server, now
if Total <> 2150 then
  raise Exception.Create('reconciliation failed before delivery');

Tu sa opäť prejavuje rozdiel v konvenciách rozhraní. Strana XLSX priraďuje výrazy cez Cell.Formula bez znaku rovnosti; strana XLS ich zapisuje cez Cell.Value s úvodným znakom '='. Preneste kód z jedného rozhrania do druhého bez zmeny a nesprávna konvencia uloží len textový reťazec, ktorý vzorec iba pripomína, no bez akejkoľvek chybovej hlášky. Keď vzorce v zošite potrebujú komunikovať s vašou vlastnou obchodnou logikou, spätné volanie OnUserFunction umožní enginu odovzdať neznáme názvy funkcií kódu v Delphi počas vyhodnocovania. To je natívna náhrada za používateľské funkcie (UDF), ktoré sa zvyčajne nachádzajú v zošitoch pôvodne spracovávaných cez COM automatizáciu

Nasadenie a špecifiká serverového prostredia

O tom, či bude nasadenie bezproblémové, rozhoduje niekoľko detailov, pričom prvým je graf závislostí jednotiek (units). Vizuálny komponent TDataToXLS pre hromadný export datasetov vyžaduje knižnice VCL Forms, Controls a Dialogs. Vo vizuálnej aplikácii je to neškodné; v konzolovej službe to však zatiahne celú knižnicu VCL. Jadro knižnice v podobe jednotiek lxHandle a lxHandleX vyžaduje iba Windows, Classes, SysUtils a Variants, takže pre čistú službu je lepšie napísať si vlastný cyklus prechodu datasetu cez natívne API než importovať celý vizuálny komponent pre pohodlie

Ďalej je tu práca s vláknami. Inštancie zošitov nie sú vláknovo bezpečné, no nezdieľajú žiadny globálny stav, takže najlepšie škáluje ten najjednoduchší vzor: jeden objekt zošita na jednu úlohu alebo na jedno vlákno. To prináša paralelné generovanie reportov, čo s jednou zdieľanou inštanciou Excelu nikdy nedosiahnete. Obslužný program požiadavky, ktorý vytvorí, naplní, uloží a uvoľní svoj vlastný zošit, nepotrebuje žiadne zámky a dosah zlyhania sa zmenší z „zdieľaná inštancia Excelu zamrzla pre všetkých“ na „táto jedna požiadavka vyhodila výnimku“, s čím si váš existujúci systém na odchytávanie chýb ľahko poradí

Posledným bodom je zacielenie formátu. Metóda TXLSWorkbook.SaveAs predvolene zapisuje formát BIFF (xlExcel97) a prenos obsahu XLS do formátu .xlsx prebieha cez konverzný mostík SaveXLSWorkbookAsXLSX so zníženou vernosťou. Vyberte si rozhranie podľa cieľového formátu, ktorý chcete doručiť, a to už vo fáze návrhu, namiesto toho, aby ste vyvíjali v jednom a konvertovali na konci procesu

Pre časť načítania dát v typickom projekte migrácie pokrýva článok o exporte z databáz ako vizuálny komponent, tak aj ručne písaný cyklus. Ak počty riadkov dosiahnu šesťciferné hodnoty, techniky optimalizácie pre veľké zošity rozhodnú o tom, či bude proces trvať minúty alebo sekundy. Tvorba reportov zo šablón udržiavaných dizajnérmi je popísaná v sprievodcovi generovaním reportov zo šablón

HotXLS sa dodáva ako zdrojový kód v Object Pascale pre Delphi a C++Builder; edície, licencovanie a kompletná referenčná príručka k rozhraniu API sú k dispozícii na produktovej stránke komponentu HotXLS