Export datových sad Delphi do reportů Excelu pomocí HotXLS

Převod výsledků dotazu do reportu v Excelu představuje tři problémy v jednom. Každý datový typ pole v Delphi musí v buňce skončit jako odpovídající typ Excelu, řádek záhlaví musí vypadat jako report a nikoli jako výpis schématu a čísla, data i měna musí mít formát, který převod přežije. Pokud cokoli z toho vynecháte, soubor se sice otevře, bude vypadat věrohodně, ale selže v okamžiku, kdy uživatel z finančního oddělení vybere sloupec a bude čekat na součet, který se nikdy neobjeví. Hodnoty totiž byly zapsány jako text, Excel s nimi zachází jako s popisy a kód nevyhodil žádnou výjimku, která by vás varovala

HotXLS je nativní tabulková knihovna v Object Pascalu, která zapisuje soubory XLS a XLSX přímo z Delphi a C++Builderu, bez nutnosti automatizace Excelu. Nabízí dvě cesty od datové sady TDataset k sešitu: hotovou komponentu TDataToXLS a ručně psaný cyklus nad API sešitu. Tyto cesty nejsou zaměnitelné. Komponenta je součástí rozhraní VCL postavenou na fasádě XLS, takže správná volba závisí na tom, kde kód běží a jaký souborový formát příjemce očekává. Následující text popisuje obě cesty, hranici, kde komponenta přestává být vhodným nástrojem, a jak zachovat datové typy polí bez ohledu na zvolený postup

Datové typy polí jsou skutečnou exportní dohodou

Před jakýmkoli voláním API se rozhodněte, jak má každý typ pole Delphi skončit v buňce. Buňka, která obdrží řetězec z Delphi, zůstane řetězcem. HotXLS neodhaduje, že řetězec '1,234.50' měl být číslem, a ani by to dělat nemělo. Zpětná analýza závislá na národním prostředí je přesně tím způsobem, jakým se německá desetinná čárka promění na oddělovač tisíců na anglickém serveru. Spolehlivým postupem je přiřazovat hodnoty přes typované přístupové metody: AsFloat nebo AsCurrency pro číselná pole, AsDateTime pro data (aby buňka obsahovala skutečné sériové číslo data Excelu a nikoli pouze formátovaný řetězec) a AsString pouze pro pole, která jsou skutečně textová

Zpracování prázdných hodnot (NULL) si zaslouží jasné rozhodnutí a nikoli spoléhání na výchozí stav. Převod hodnoty pole pomocí VarToStr změní SQL NULL na prázdný řetězec, což vytvoří textovou buňku, zatímco vynechání přiřazení ponechá buňku skutečně prázdnou, což je to, co očekávají funkce AVERAGE, COUNT i kontingenční tabulky. U peněžních sloupců se před napsáním cyklu rozhodněte, zda NULL znamená nulu nebo neznámou hodnotu. Obě se po naformátování sloupce vykreslí identicky, ale rozdíl změní každý následně počítaný souhrn

Cesta komponenty: TDataToXLS ve VCL aplikacích

Pro klasické aplikace VCL s dotazem již zapojeným v datovém modulu představuje TDataToXLS jednorázovou cestu. Prochází jakéhokoli potomka TDataset, ať už jde o FireDAC, ADO, IBX nebo cokoli jiného, co implementuje abstraktní rozhraní datové sady, a generuje stylovaný list s popisky záhlaví, písmy, ohraničením, volitelnými dílčími součty skupin a automatickým rozdělováním listů pro velké sady výsledků

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;

Většinu práce zde odvádějí dvě vlastnosti. Nastavení HeaderSource := hsDisplayLabel zapisuje popisek DisplayLabel každého pole namísto čistého názvu sloupce v SQL, takže sešit zobrazí „Jméno zákazníka“ namísto CUST_NM. Vlastnost RowsPerSheet existuje proto, že komponenta zapisuje formát BIFF8, jehož mřížka končí na 65 536 řádcích a 256 sloupcích; její nastavení na 50 000 rozdělí velkou sadu výsledků na více listů dříve, než ji limit formátu ořízne. Vzhled řídí vlastnosti HeaderFont, DetailFont, GroupColor a styl ohraničení, a sada DisableFormat vypíná celé kategorie formátování, pokud příjemce vyžaduje čisté buňky. Pro jakékoli specifické úpravy vám události AfterCell a AfterRow předávají právě zapsaný rozsah k dalšímu zpracování

Kde komponenta naráží na své limity

V komponentě TDataToXLS jsou navržena tři omezení a jejich znalost předem pomůže předejít složitému přepracování kódu později

Jedná se o VCL komponentu v plném smyslu. Její jednotka načítá moduly Forms, Controls a Dialogs, so linking it into a console job or a Windows service drags the VCL into the binary. The core workbook units have no such dependency. They need only Windows, Classes, SysUtils and Variants, which is why server-side code should use the loop shown below instead
Je postavena na rozhraní XLS. Komponenta plní rozhraní IXLSWorkbook a zapisuje formát .xls (BIFF8). Neexistuje žádná vlastnost, která by ji přepnula na výstup OOXML
Její události komunikují v dialektu XLS. Parametr Cell: IXLSRange v události AfterCell patří do objektového modelu XLS, so per-cell customization written there is XLS-style code even if the file is converted to .xlsx afterwards

Generování .xlsx z výstupu komponenty

Pokud odběratel trvá na formátu .xlsx, ale exportní logika již využívá TDataToXLS, převodní funkce v jednotce lxXlsxExport převede naplněný sešit v jediném volání:

uses lxXlsxExport;

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

Považujte tento převodní most za přenašeč tabulkových dat a nikoli za stoprocentní konvertor. Kopíruje hodnoty, vzorce, číselné formáty, barvy výplně, vlastnosti písem, šířky sloupců a nastavení zobrazení. Záměrně však nekopíruje ohraničení, sloučené rozsahy, komentáře, grafy ani podmíněné formáty. Pro plochou mřížku záhlaví a řádků to naprosto stačí. Pro stylovaný report nikoli a správným řešením je generovat XLSX přímo, spíše než upravovat převedený soubor

Ručně psaný cyklus pro služby a dávkové úlohy

Kód na straně serveru by měl cílit přímo na TXLSXWorkbook. Před kopírováním jakékoli ukázky si všimněte rozdílu v životním cyklu obou rozhraní. XLS verze TXLSWorkbook je držena přes rozhraní s počítáním odkazů a nesmí se uvolňovat ručně, zatímco TXLSXWorkbook je běžná třída vyžadující konstrukci try..finally Free. Smíchání těchto dvou konvencí je spolehlivým způsobem, jak způsobit buď únik paměti, nebo dvojí uvolnění stejného objektu

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;

Klíčovými řádky jsou typovaná přiřazení a kontrola IsNull. Data přicházejí jako sériová čísla, částky jako typ double a prázdná (NULL) data objednávek zůstávají skutečně prázdná, místo aby se měnila na prázdné řetězce. Nastavení StreamingWrite := True mění pouze cestu uložení: XML listu se streamuje přímo do archivu zip, místo aby se nejprve sestavovalo jako jeden velký řetězec v paměti, což odstraňuje paměťové špičky při volání SaveAs u šestimístných počtů řádků. Každá ukládací metoda má také přetížení pro TStream, takže sešit může jít přímo do odpovědi HTTP bez zápisu na disk. Článek o proudovém zápisu a dávkových úlohách se věnuje tomuto vzoru nasazení a článek o výkonu při práci s velkými sešity popisuje, co dělat, když počty řádků rostou ještě výše

Tento cyklus je také cestou, která dobře škáluje napříč vlákny. Obě jádra jsou nativními zapisovači v Object Pascalu (BIFF8 proudy záznamů na jedné straně a OOXML archiv zip plus XML na straně druhé), takže žádná část exportu nevyužívá automatizaci COM ani nevyžaduje licenci na Excel na serveru. To vám přináší paralelní zpracování bez úzkého hrdla jediné instance, pokud si každé vlákno staví vlastní sešit. Objekty sešitů nejsou bezpečné pro souběžné použití (thread-safe), takže pravidlem je jedna instance na export, nikdy ne sdílená instance chráněná zámkem

Před návrhem je dobré znát jeden limit. Mřížka XLSX končí na 1 048 576 řádcích a 16 384 sloupcích, takže rozdělování listů, které vlastnost RowsPerSheet řeší na straně XLS, je zde potřeba jen zřídka. Milionový sešit navíc málokdy odpovídá tomu, co lidský příjemce požaduje. Pokud je sada výsledků opravdu tak velká, bývá lepší volbou oddělovaný soubor a článek o exportu do CSV a TSV se zabývá oddělovači, chováním BOM i upozorněními ohledně vyhodnocování vzorců, která se tam uplatňují

Volba výchozího bodu

Pokud export běží v desktopovém nástroji VCL a výstup .xls je přijatelný, začněte s komponentou TDataToXLS a její podporou seskupování. Vyžaduje to nejméně kódu a převodní most přes SaveXLSWorkbookAsXLSX je k dispozici, pokud někdo později požádá o formát .xlsx (s ohledem na popsané limity věrnosti převodu). Pokud kód běží bez obsluhy na pozadí nebo příjemce vyžaduje formát .xlsx od samého začátku, napište ruční cyklus. Obě cesty se dodávají s funkčními ukázkovými projekty a jsou součástí balíku HotXLS Component