Exportovanie Delphi datasetov do Excel reportov cez HotXLS

Premena výsledku dopytu na Excel report predstavuje tri problémy v jednom. Každý typ poľa Delphi musí byť zapísaný do bunky ako správny excelovský typ, riadok hlavičky musí pôsobiť ako report a nie ako výpis schémy, a čísla, dátumy či finančné hodnoty musia niesť formátovanie, ktoré tento prechod prežije. Vynechajte ktorýkoľvek z týchto krokov a súbor sa síce otvorí, bude vyzerať dôveryhodne, no zlyhá v momente, keď používateľ z finančného oddelenia označí stĺpec a čaká na súčet, ktorý sa nikdy neobjaví. Hodnoty boli totiž zapísané ako text, Excel s nimi zaobchádza ako s menovkami a na chybu vás neupozornila žiadna výnimka.

HotXLS je natívna knižnica tabuľkového procesora v Object Pascale, ktorá zapisuje súbory XLS a XLSX priamo z prostredia Delphi a C++Builder, a to bez akejkoľvek automatizácie Excelu. Ponúka dve cesty od TDataset k zošitu: zabudovaný komponent TDataToXLS a ručne napísaný cyklus s využitím API zošita. Tieto prístupy nie sú vzájomne zameniteľné. Komponent je súčasťou VCL a je postavený na rozhraní XLS, takže správna voľba závisí od toho, kde kód beží a aký formát súboru príjemca očakáva. Nasledujúci text popisuje obe cesty, hranicu, kedy komponent prestáva byť vhodným nástrojom, a spôsob, ako udržať typy polí neporušené pri oboch riešeniach.

Typy polí sú skutočným integračným pravidlom

Pred akýmkoľvek volaním API sa rozhodnite, ako sa každý typ poľa Delphi zapíše do bunky. Bunka, ktorá prijme delphiovský reťazec (string), zostane reťazcom. HotXLS neodhadne, že hodnota '1,234.50' mala byť číslom, a ani by to robiť nemal. Opätovné spracovanie závislé od lokálnych nastavení (locale) je totiž presne spôsob, akým sa nemecká desatinná čiarka zmení na oddeľovač tisícov na anglickom serveri. Spoľahlivým vzorom je priraďovanie hodnôt cez typované metódy: AsFloat alebo AsCurrency pre číselné polia, AsDateTime pre dátumy, aby bunka obsahovala skutočné sériové číslo dátumu Excelu a nie iba naformátovaný reťazec, a AsString iba pre polia, ktoré sú skutočne textom.

Spracovanie prázdnych hodnôt (NULL) si vyžaduje jasné rozhodnutie namiesto predvoleného správania. Konverzia hodnoty poľa pomocou VarToStr zmení SQL NULL na prázdny reťazec, čo vytvorí textovú bunku, zatiaľ čo vynechanie priradenia ponechá bunku skutočne prázdnu. Práve to očakávajú funkcie ako AVERAGE, COUNT či používatelia kontingenčných tabuliek. Pri peňažných stĺpcoch sa ešte pred vytvorením cyklu rozhodnite, či NULL znamená nulu alebo neznámu hodnotu. Obe možnosti sa po naformátovaní stĺpca zobrazia rovnako, no rozdiel zmení každú agregovanú hodnotu počítanú nadväzujúcim systémom.

Cesta komponentu: TDataToXLS vo VCL aplikáciách

Pre klasickú VCL aplikáciu s dopytom už zapojeným do dátového modulu je TDataToXLS riešením na jedno volanie. Prechádza akéhokoľvek potomka triedy TDataset, či už ide o FireDAC, ADO, IBX alebo čokoľvek iné, čo implementuje abstraktné rozhranie datasetu, a vytvára naštýlovaný hárok s nadpismi hlavičiek, písmom, ohraničeniami, voliteľnými medzisúčtami skupín a automatickým rozdeľovaním hárkov pre veľké dátové sady.

var
  Exporter: TDataToXLS;
begin
  Exporter := TDataToXLS.Create(nil);
  try
    Exporter.Dataset := OrdersQuery;          // any TDataset descendant
    Exporter.WorksheetName := 'Orders';
    Exporter.HeaderSource := hsDisplayLabel;  // captions, not raw column names
    Exporter.GroupFields.Add('CustomerID');   // subtotal block per customer
    Exporter.RowsPerSheet := 50000;           // stay below the BIFF8 row ceiling
    Exporter.OnlyVisible := True;             // respect Field.Visible
    Exporter.SaveDatasetAs('orders.xls');
  finally
    Exporter.Free;
  end;
end;

Najväčší vplyv na výsledok tu majú dve vlastnosti. Nastavenie HeaderSource := hsDisplayLabel zapisuje hodnotu DisplayLabel každého poľa namiesto surového názvu stĺpca SQL, takže v zošite sa zobrazí napríklad „Meno zákazníka“ a nie CUST_NM. RowsPerSheet existuje preto, že komponent zapisuje formát BIFF8, ktorého mriežka končí na limite 65 536 riadkov a 256 stĺpcov; jej nastavenie na 50 000 rozdelí veľkú dátovú sadu na viacero hárkov skôr, než ju limit formátu oreže. Vzhľad sa spravuje cez vlastnosti HeaderFont, DetailFont, GroupColor a štýly ohraničení, pričom sada DisableFormat vypína celé kategórie formátovania, ak príjemca požaduje čisté bunky. Pre akékoľvek vlastné úpravy vám udalosti AfterCell a AfterRow odovzdajú práve zapísaný rozsah na dodatočné spracovanie.

Kde možnosti komponentu končia

Do komponentu TDataToXLS sú zabudované tri obmedzenia. Ich znalosť vopred vám ušetrí nepríjemné prerábanie kódu v neskorších fázach vývoja.

Ide o VCL komponent v plnom zmysle slova. Jeho unit zahŕňa Forms, Controls a Dialogs, takže jeho prepojenie s konzolovou úlohou alebo službou systému Windows zatiahne knižnicu VCL do binárneho súboru. Jadro knižnice zošita takéto závislosti nemá. Vyžaduje iba Windows, Classes, SysUtils a Variants, preto by serverový kód mal radšej použiť cyklus popísaný nižšie.
Je postavený na rozhraní XLS. Komponent napĺňa IXLSWorkbook a zapisuje formát .xls (BIFF8). Neexistuje vlastnosť, ktorá by ho prepla na výstup OOXML.
Jeho udalosti hovoria dialektom XLS. Parameter Cell: IXLSRange v udalosti AfterCell patrí do objektového modelu XLS, takže vlastne úpravy buniek tam zapísané sú kódom v štýle XLS, a to aj vtedy, ak sa súbor neskôr skonvertuje do .xlsx.

Vytváranie .xlsx z výstupu komponentu

Keď príjemca vyžaduje formát .xlsx, no exportná logika už existuje v komponente TDataToXLS, premosťovacia funkcia v unite lxXlsxExport skonvertuje naplnený zošit jedným volaním:

uses lxXlsxExport;

Exporter.SaveDatasetAs('orders.xls');
// the component exposes the IXLSWorkbook it populated
SaveXLSWorkbookAsXLSX(Exporter.Workbook, 'orders.xlsx');

Považujte toto premostenie za nosič tabuľkových dát, nie za plnohodnotný konvertor. Kopíruje hodnoty, vzorce, číselné formáty, farby výplne, atribúty písma, šírky stĺpcov a nastavenia zobrazenia. Zámerne nekopíruje ohraničenia, zlúčené rozsahy, komentáre, grafy ani podmienené formáty. Pre jednoduchú mriežku s hlavičkou a riadkami to úplne stačí. Pre naštýlovaný report to však nestačí a správnym riešením je generovať XLSX priamo namiesto upravovania skonvertovaného súboru.

Ručne napísaný cyklus pre služby a dávkové úlohy

Serverový kód by mal pracovať priamo s TXLSXWorkbook. Pred kopírovaním akéhokoľvek príkladu si všimnite rozdiel v životnom cykle medzi oboma rozhraniami. Trieda TXLSWorkbook na strane XLS je spravovaná cez rozhranie s počítaním referencií a nesmie sa uvoľňovať ručne, zatiaľ čo TXLSXWorkbook je bežná trieda, ktorá vyžaduje blok try..finally Free. Miešanie týchto dvoch konvencií je spoľahlivou cestou k úniku pamäte alebo k chybe double-free.

procedure ExportOrders(Q: TDataSet; const FileName: string);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Orders');
    Sheet.Cells[1, 1].Value := 'Order No';
    Sheet.Cells[1, 2].Value := 'Customer';
    Sheet.Cells[1, 3].Value := 'Ordered';
    Sheet.Cells[1, 4].Value := 'Amount';

    Row := 2;
    Q.First;
    while not Q.Eof do
    begin
      Sheet.Cells[Row, 1].Value := Q.FieldByName('OrderNo').AsInteger;
      Sheet.Cells[Row, 2].Value := Q.FieldByName('Customer').AsString;
      if not Q.FieldByName('Ordered').IsNull then
        Sheet.Cells[Row, 3].Value := Q.FieldByName('Ordered').AsDateTime;
      Sheet.Cells[Row, 4].Value := Q.FieldByName('Amount').AsFloat;
      Inc(Row);
      Q.Next;
    end;

    Book.StreamingWrite := True;  // stream sheet XML straight into the zip
    Book.SaveAs(FileName);
  finally
    Book.Free;
  end;
end;

Dôležité sú typované priradenia a ochrana IsNull. Dátumy prichádzajú ako sériové čísla dátumu Excelu, sumy ako desatinné čísla (doubles) a prázdne dátumy objednávok (NULL) zostávajú skutočne prázdne namiesto premeny na prázdne reťazce. Nastavenie StreamingWrite := True mení iba spôsob ukladania: XML hárka sa streamuje priamo do archívu zip namiesto toho, aby sa najprv zostavovalo ako jeden veľký reťazec v pamäti. To výrazne znižuje pamäťovú špičku pri volaní SaveAs pre šesťciferné počty riadkov. Každá metóda ukladania má aj preťaženie s TStream, takže zošit môže ísť priamo do HTTP odpovede bez zápisu na disk. Článok o streamovanom zápise a dávkových úlohách popisuje tento vzor nasadenia a článok o výkone pri veľkých zošitoch popisuje kroky, keď počty riadkov rastú ešte viac.

Tento cyklus je zároveň cestou, ktorá škáluje naprieč vláknami. Oba enginy sú natívnymi zapisovačmi v Object Pascale (streamy záznamov BIFF8 na jednej strane a OOXML archív zip s XML na druhej), takže žiadna časť exportu nevyužíva COM automatizáciu a nevyžaduje licenciu na Excel na serveri. To vám prináša paralelný beh bez úzkeho hrdla jedinej inštancie, pokiaľ si každé vlákno vytvára vlastný zošit. Objekty zošitov nie sú bezpečné pre súbežné zdieľanie medzi vláknami, takže platí pravidlo jedna inštancia na jeden export a nikdy nie zdieľaná inštancia chránená zámkom.

Pred návrhom systému je dôležité poznať jeden limit. Mriežka XLSX končí na 1 048 576 riadkoch a 16 384 stĺpcoch, takže rozdeľovanie hárkov, ktoré na strane XLS rieši vlastnosť RowsPerSheet, je tu potrebné len zriedka. Zošit s miliónom riadkov je navyše málokedy to, čo ľudský príjemca reálne potrebuje. Keď je výsledná dátová sada skutočne taká veľká, lepšou voľbou je súbor s oddelenými hodnotami, pričom článok o exporte do CSV a TSV popisuje oddeľovače, správanie BOM a dôležité upozornenia ohľadom vyhodnocovania vzorcov.

Výber počiatočného bodu

Ak export prebieha vo VCL desktopovej aplikácii a výstup .xls je prijateľný, začnite s komponentom TDataToXLS a jeho podporou zoskupovania. Vyžaduje to najmenej kódu a premostenie cez SaveXLSWorkbookAsXLSX je k dispozícii, ak by niekto neskôr vyžadoval formát .xlsx (pokiaľ akceptujete už popísané obmedzenia vernosti prevodu). Ak kód beží bez dozoru na serveri alebo príjemca vyžaduje .xlsx hneď od začiatku, napíšte cyklus. Obe cesty sa dodávajú s funkčnými ukážkovými projektmi a sú súčasťou balíka HotXLS Component.