Delphi adatkészletek exportja Excelbe a HotXLS-szel

Egy lekérdezési eredmény Excel jelentéssé alakítása három probléma egyetlen kabátban. Minden Delphi mezőtípusnak a megfelelő Excel típusként kell cellába érkeznie, a fejlécsornak jelentésként kell olvasódnia, nem sémakiíratásként, a számoknak, dátumoknak és pénzösszegeknek pedig olyan formátumokat kell hordozniuk, amelyek túlélik az utat. Hagyja ki bármelyiket, és a fájl attól még megnyílik, hihetőnek is látszik, és abban a pillanatban mond csődöt, amikor egy pénzügyes kijelöl egy oszlopot, és olyan összegre vár, amely soha nem jelenik meg. Az értékek szövegként íródtak ki, az Excel címkeként kezeli őket, és soha nem keletkezett kivétel, amely figyelmeztette volna

A HotXLS natív Object Pascal táblázatkezelő könyvtár, amely XLS és XLSX fájlokat ír közvetlenül Delphiből és C++Builderből, mindenféle Excel automatizálás nélkül. Két utat kínál egy TDataset objektumtól a munkafüzetig: a beejthető TDataToXLS komponenst és a munkafüzet API-ja ellen kézzel írt ciklust. Ezek nem cserélhetők fel. A komponens VCL polgár, amely az XLS homlokzatra épül, így a helyes választás attól függ, hol fut a kód, és melyik fájlformátumot várja a fogyasztó. Az alábbiakban mindkét út következik, az a határ, ahol a komponens megszűnik a helyes eszköz lenni, és az, hogyan tartsa épen a mezőtípusokat bármelyiket választja is

A mezőtípusok az igazi exportszerződés

Bármely API hívás előtt döntse el, hogyan érkezzen cellába az egyes Delphi mezőtípusok. Az a cella, amely Delphi karakterláncot kap, karakterlánc marad. A HotXLS nem találgatja ki, hogy az '1,234.50' számnak készült, és nem is szabad, mert a területi beállítástól függő újraelemzés pontosan az, ahogyan egy német tizedes vessző ezreselválasztóvá válik egy angol kiszolgálón. A megbízható minta a típusos hozzáférőkön át való értékadás: AsFloat vagy AsCurrency a numerikus mezőkhöz, AsDateTime a dátumokhoz, hogy a cella valódi Excel dátumsorszámot tartson formázott karakterlánc helyett, és AsString csak azoknál a mezőknél, amelyek valóban szövegesek

A NULL kezelése kifejezett döntést érdemel, nem alapértelmezést. Egy mezőérték VarToStr hívással való átalakítása üres karakterlánccá teszi az SQL NULL értéket, ami szöveges cella, míg az értékadás kihagyása igazán üresen hagyja a cellát, és ezt várja az AVERAGE, a COUNT és a kimutatásokat fogyasztó fél. Pénzoszlopoknál még a ciklus megírása előtt döntse el, hogy a NULL nullát vagy ismeretlent jelent-e. A kettő azonosan jelenik meg, amint valaki megformázza az oszlopot, a különbség pedig minden alsóbb szinten számolt összesítést megváltoztat

A komponens útja: TDataToXLS a VCL alkalmazásokban

Egy klasszikus VCL alkalmazásnál, ahol a lekérdezés már be van kötve egy adatmodulba, a TDataToXLS az egyhívásos út. Bejár bármely TDataset leszármazottat, legyen az FireDAC, ADO, IBX vagy bármi más, ami megvalósítja az absztrakt adatkészlet-felületet, és stílusos munkalapot állít elő fejlécfeliratokkal, betűtípusokkal, szegélyekkel, választható csoportos részösszegekkel és nagy eredményhalmazoknál automatikus lapfelosztással

var
  Exporter: TDataToXLS;
begin
  Exporter := TDataToXLS.Create(nil);
  try
    Exporter.Dataset := OrdersQuery;          // bármely TDataset leszármazott
    Exporter.WorksheetName := 'Orders';
    Exporter.HeaderSource := hsDisplayLabel;  // feliratok, nem nyers oszlopnevek
    Exporter.GroupFields.Add('CustomerID');   // részösszeg-blokk ügyfelenként
    Exporter.RowsPerSheet := 50000;           // maradjon a BIFF8 sorhatár alatt
    Exporter.VisibleFieldsOnly := True;             // tartsa tiszteletben a Field.Visible értékét
    Exporter.SaveDatasetAs('orders.xls');
  finally
    Exporter.Free;
  end;
end;

Két tulajdonság viszi itt az éles üzem terhének nagy részét. A HeaderSource := hsDisplayLabel az egyes mezők DisplayLabel értékét írja ki a nyers SQL oszlopnév helyett, így a munkafüzetben „Customer Name” áll a CUST_NM helyett. A RowsPerSheet azért létezik, mert a komponens BIFF8 formátumot ír, amelynek rácsa 65 536 sornál és 256 oszlopnál megáll; 50 000 értékre állítva több lapra osztja a nagy eredményhalmazt, mielőtt a formátumhatár levágná. A megjelenésről a HeaderFont, a DetailFont, a GroupColor és a szegélystílus-tulajdonságok gondoskodnak, a DisableFormat halmaz pedig egész formázási kategóriákat kapcsol ki, amikor a fogyasztó csupasz cellákat kér. Bármi egyedihez az AfterCell és az AfterRow események adják kezébe az imént kiírt tartományt utófeldolgozásra

Ahol a komponens megáll

Három korlát bele van tervezve a TDataToXLS komponensbe, és ezek előzetes ismerete megkímél egy kínos újratervezéstől két sprinttel később

• A szó teljes értelmében VCL komponens. A unitja behúzza a Forms, Controls és Dialogs egységeket, így egy konzolos feladatba vagy Windows szolgáltatásba szerkesztve a VCL is bekerül a binárisba. A mag munkafüzet-unitoknak nincs ilyen függőségük. Csak a Windows, Classes, SysUtils és Variants kell nekik, ezért a kiszolgálóoldali kódnak inkább az alább bemutatott ciklust érdemes használnia
• Az XLS homlokzatra épül. A komponens egy IXLSWorkbook objektumot tölt fel, és .xls (BIFF8) fájlt ír. Nincs olyan tulajdonság, amely OOXML kimenetre váltaná
• Az eseményei az XLS nyelvjárását beszélik. Az AfterCell eseményben szereplő Cell: IXLSRange paraméter az XLS objektummodellhez tartozik, így az ott írt cellánkénti testreszabás XLS stílusú kód marad akkor is, ha a fájl utólag .xlsx formátumra alakul

.xlsx előállítása a komponens kimenetéből

Amikor a fogyasztó ragaszkodik az .xlsx formátumhoz, de az exportlogika már a TDataToXLS komponensben él, az lxXlsxExport unit hídfüggvénye egyetlen hívással alakítja át a feltöltött munkafüzetet:

uses lxXlsxExport;

Exporter.SaveDatasetAs('orders.xls');
// a komponens elérhetővé teszi az általa feltöltött IXLSWorkbook objektumot
SaveXLSWorkbookAsXLSX(Exporter.Workbook, 'orders.xlsx');

A hidat táblázatos adatokat szállító eszköznek tekintse, ne teljes hűségű átalakítónak. Átmásolja az értékeket, képleteket, számformátumokat, kitöltőszíneket, betűtípus-jellemzőket, oszlopszélességeket és nézetbeállításokat. Szándékosan nem másolja a szegélyeket, egyesített tartományokat, megjegyzéseket, diagramokat és feltételes formátumokat. Fejlécből és sorokból álló lapos rács esetén ez pontosan elég. Stílusos jelentésnél nem, és az őszinte megoldás az XLSX közvetlen előállítása az átalakított fájl foltozgatása helyett

A kézzel írt ciklus szolgáltatásokhoz és kötegelt feladatokhoz

A kiszolgálóoldali kódnak közvetlenül a TXLSXWorkbook objektumot kell céloznia. Bármely minta másolása előtt vegye észre az élettartambeli különbséget a két homlokzat között. Az XLS oldali TXLSWorkbook hivatkozásszámlált felületen át él, és nem szabad kézzel felszabadítani, míg a TXLSXWorkbook sima osztály, amely try..finally Free szerkezetet igényel. A két egyezmény keverése megbízható módja annak, hogy szivárgást vagy dupla felszabadítást gyártson

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;  // a lap XML-je egyenesen a zipbe folyik
    Book.SaveAs(FileName);
  finally
    Book.Free;
  end;
end;

A lényeges sorok a típusos értékadások és az IsNull őr. A dátumok dátumsorszámként érkeznek, az összegek doubleként, a NULL rendelési dátumok pedig valóban üresen maradnak ahelyett, hogy üres karakterlánccá válnának. A StreamingWrite := True csak a mentési útvonalat változtatja meg: a munkalap XML-je egyenesen a zip tárolóba folyik, ahelyett hogy előbb egyetlen nagy karakterlánccá állna össze, ami hatjegyű sorszámoknál ellaposítja a SaveAs pillanatában jelentkező memóriacsúcsot. Minden mentési metódusnak van TStream túlterhelése is, így a munkafüzet a lemez érintése nélkül kerülhet egyenesen egy HTTP válaszba. A folyamatos írásról és a kötegelt feladatokról szóló cikk végigveszi ezt a telepítési mintát, a nagy munkafüzetek teljesítményéről szóló cikk pedig azt tárgyalja, mi a teendő, ha a sorszám még feljebb kúszik

Ez a ciklus egyben az az út, amely szálak között skálázódik. Mindkét motor natív Object Pascal író, az egyik oldalon BIFF8 rekordfolyamokkal, a másikon OOXML zippel és XML-lel, így az export egyetlen része sem érint COM automatizálást, és nem igényel Excel licencet a kiszolgálón. Amit ez ad, az az egypéldányos szűk keresztmetszet nélküli párhuzamosság, feltéve hogy minden szál a saját munkafüzetét építi. A munkafüzet-objektumok megosztott használatra nem szálbiztosak, ezért a szabály az, hogy exportonként egy példány, soha nem egy zárral őrzött közös

Egy korlátot érdemes ismerni, mielőtt köré tervez. Az XLSX rács 1 048 576 sornál és 16 384 oszlopnál áll meg, így az a lapfelosztás, amelyet a RowsPerSheet intéz az XLS oldalon, itt ritkán kell. Egy egymillió soros munkafüzet amúgy is ritkán az, amit egy emberi fogyasztó akar. Amikor az eredményhalmaz valóban ekkora, rendszerint a tagolt fájl a jobb szerződés, a CSV és TSV exportról szóló cikk pedig lefedi az elválasztókat, a BOM viselkedését és az ott érvényes képletkiértékelési kikötést

Kiindulópont választása

Ha az export egy VCL asztali eszközben él, és az .xls kimenet elfogadható, kezdje a TDataToXLS komponenssel és annak csoportosítási támogatásával. Ez a legkevesebb kód, és a SaveXLSWorkbookAsXLSX hídja ott van arra az esetre, ha valaki később .xlsx fájlt kér, amennyiben elfogadja a már leírt hűségbeli korlátokat. Ha a kód felügyelet nélkül fut, vagy a fogyasztó kezdettől .xlsx formátumot követel, írja meg a ciklust. Mindkét úthoz működő bemutatóprojektek járnak, és mindkettő a HotXLS Delphi Component csomag része