Izvoz podatkovnih nizov Delphi v poročila Excel s HotXLS

Pretvorba rezultatov poizvedbe v poročilo Excel predstavlja tri težave v enem paketu. Vsaka vrsta polja Delphi mora v celico priti kot ustrezna vrsta za Excel, vrstica z glavo mora izgledati kot poročilo in ne kot izpis podatkovne sheme, številke, datumi ter denarne vrednosti pa morajo imeti formate, ki preživijo prenos. Če izpustite le enega od teh korakov, se bo datoteka še vedno odprla in bo videti pravilna, vendar ne bo delovala v trenutku, ko bo uporabnik iz finančnega oddelka izbral stolpec in čakal na vsoto, ki se nikoli ne pojavi. Vrednosti so bile namreč zapisane kot besedilo, Excel jih obravnava kot oznake in noben izjemni dogodek vas ni opozoril na napako.

HotXLS je izvorna knjižnica za preglednice v jeziku Object Pascal, ki piše datoteke XLS in XLSX neposredno iz Delphija in C++Builderja, brez uporabe avtomatizacije Excel. Ponuja dve poti od TDataset do delovnega zvezka: vgradno komponento TDataToXLS in ročno napisano zanko prek API-ja delovnega zvezka. Ti poti nista medsebojno zamenljivi. Komponenta je del okolja VCL in temelji na fasadi XLS, zato je prava izbira odvisna od tega, kje se koda izvaja in kateri format datoteke pričakuje prejemnik. V nadaljevanju sta predstavljeni obe poti, meja, pri kateri komponenta ni več pravo orodje, in način, kako ohraniti vrste polj nepoškodovane, ne glede na to, katero možnost izberete.

Vrste polj so prava izvozna pogodba

Pred vsakim klicem API-ja se odločite, kako bo posamezna vrsta polja Delphi zapisana v celico. Celica, ki prejme niz Delphi, ostane niz. HotXLS ne ugiba, ali naj bi bil niz '1,234.50' v resnici številka, in tega tudi ne bi smel početi, saj je ponovno razčlenjevanje, odvisno od lokalnih nastavitev, natanko tisti razlog, zakaj se nemška decimalna veja na angleškem strežniku spremeni v ločilo tisočic. Zanesljiv vzorec je dodeljevanje prek tipiziranih dostopov: AsFloat or AsCurrency za numerična polja, AsDateTime za datume, tako da celica vsebuje dejanski Excelov serijski datum namesto formatiranega niza, in AsString samo za polja, ki so dejansko besedilo.

Obravnava vrednosti Null si zasluži eksplicitno odločitev namesto privzetega obnašanja. Pretvorba vrednosti polja s funkcijo VarToStr spremeni vrednost SQL NULL v prazen niz, kar ustvari celico z besedilom, medtem ko izpustitev dodelitve pusti celico resnično prazno, kar pa pričakujejo funkcije AVERAGE, COUNT in poročila z vrtilnimi tabelami. Za stolpce z denarnimi vrednostmi se pred pisanjem zanke odločite, ali NULL pomeni nič ali neznano vrednost. Obe možnosti sta po oblikovanju stolpca videti enako, vendar razlika vpliva na vsak agregat, izračunan v nadaljnjem procesu.

Pot s komponento: TDataToXLS v aplikacijah VCL

Za klasično aplikacijo VCL s poizvedbo, ki je že povezana v podatkovnem modulu, je TDataToXLS pot z enim samim klicem. Pregleda kateri koli odvod razreda TDataset, naj bo to FireDAC, ADO, IBX or karkoli drugega, kar implementira abstraktni vmesnik podatkovnega niza, ter ustvari oblikovan delovni list z naslovi glav, pisavami, obrobami, neobveznimi delnimi vsotami skupin in samodejno razdelitvijo listov za velike nize rezultatov.

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;

Dve lastnosti imata tukaj največjo vlogo pri delovanju. Lastnost HeaderSource := hsDisplayLabel zapiše vrednost DisplayLabel posameznega polja namesto surovega imena stolpca SQL, tako da delovni zvezek prikaže "Customer Name" namesto CUST_NM. Lastnost RowsPerSheet obstaja zato, ker komponenta piše v formatu BIFF8, katerega mreža je omejena na 65.536 vrstic in 256 stolpcev; nastavitev na 50.000 razdeli velik nabor rezultatov na več listov, preden bi ga omejitev formata skrajšala. Videz določajo lastnosti HeaderFont, DetailFont, GroupColor in slogi obrob, z množico DisableFormat pa lahko izklopite celotne kategorije oblikovanja, če prejemnik želi le navadne celice. Za kakršne koli prilagoditve po meri vam dogodka AfterCell in AfterRow ponudita pravkar zapisano območje za nadaljnjo obdelavo.

Kje se možnosti komponente končajo

V komponento TDataToXLS so vgrajene tri omejitve in njihovo poznavanje vnaprej preprečuje neprijetno preoblikovanje kode kasneje.

Gre za komponento VCL v polnem pomenu besede. Njena enota vključi Forms, Controls in Dialogs, zato povezovanje te komponente v konzolno opravilo ali storitev Windows vključi knjižnico VCL v binarno datoteko. Jedrne enote delovnega zvezka nimajo take odvisnosti. Potrebujejo le enote Windows, Classes, SysUtils in Variants, zato bi morala koda na strežniški strani namesto tega uporabljati spodaj prikazano zanko.
Zgrajena je na fasadi XLS. Komponenta napolni vmesnik IXLSWorkbook in piše datoteko .xls (BIFF8). Ni lastnosti, ki bi jo preklopila na izhod OOXML.
Njeni dogodki govorijo v narečju XLS. Parameter Cell: IXLSRange v dogodku AfterCell pripada objektnemu modelu XLS, zato je tam napisana prilagoditev posameznih celic koda v slogu XLS, tudi če se datoteka kasneje pretvori v .xlsx.

Ustvarjanje .xlsx iz izhoda komponente

Če prejemnik vztraja pri formatu .xlsx, vendar izvozna logika že obstaja v komponenti TDataToXLS, povezovalna funkcija v enoti lxXlsxExport pretvori napolnjen delovni zvezek v enem klicu:

uses lxXlsxExport;

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

Povezovalno funkcijo obravnavajte kot prenašalca tabelaričnih podatkov in ne kot popoln pretvornik z visoko natančnostjo. Kopira vrednosti, formule, formate števil, barve polnil, atribute pisav, širine stolpcev in nastavitve pogleda. Namerno pa ne kopira obrob, združenih območij, komentarjev, grafikonov ali pogojnega oblikovanja. Za preprosto mrežo glave in vrstic je to povsem dovolj. Za stilsko oblikovano poročilo pa to ni primerno, zato je prava rešitev neposredno ustvarjanje datoteke XLSX namesto popravljanja pretvorjene datoteke.

Ročno napisana zanka za storitve in serijska opravila

Koda na strežniški strani bi morala neposredno ciljati na TXLSXWorkbook. Pred kopiranjem katerega koli primera bodite pozorni na razliko v življenjski dobi med obema fasadama. Objekt TXLSWorkbook na strani XLS se upravlja prek vmesnika s števcem referenc in ga ne smete ročno sprostiti, medtem ko je TXLSXWorkbook navaden razred, ki zahteva uporabo bloka try..finally Free. Mešanje teh dveh konvencij je zanesljiv način za povzročitev uhajanja pomnilnika ali dvojnega sproščanja.

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;

Ključne vrstice so tipizirane dodelitve in zaščita IsNull. Datumi se prenesejo kot serijske številke datumov, zneski kot dvojna realna števila (double), prazni datumi naročil (NULL) pa ostanejo zares prazni, namesto da bi postali prazni nizi. Nastavitev StreamingWrite := True spremeni le pot shranjevanja: XML delovnega lista se pretaka neposredno v arhiv zip namesto začasnega sestavljanja v en velik niz, kar drastično zmanjša porabo pomnilnika pri klicu SaveAs pri stotisočih vrsticah. Vsaka metoda shranjevanja ima tudi preobremenitev za TStream, tako da lahko delovni zvezek pošljete neposredno v odgovor HTTP brez pisanja na disk. Članek o pretočnem pisanju in serijskih opravilih podrobneje opisuje ta vzorec uvajanja, članek o zmogljivosti velikih delovnih zvezkov pa pokriva možnosti ob nadaljnjem naraščanju števila vrstic.

Ta zanka je tudi način za prilagajanje delovanja med nitmi. Oba pogona sta izvorna zapisovalnika v jeziku Object Pascal (tokovi zapisov BIFF8 na eni strani ter OOXML zip in XML na drugi), zato noben del izvoza ne uporablja avtomatizacije COM ali zahteva licence za Excel na strežniku. To vam omogoča vzporedno delovanje brez ozkega grla ene same instance, pod pogojem, da vsaka nit ustvari svoj delovni zvezek. Objekti delovnega zvezka niso varni za sočasno uporabo v več nitih, zato velja pravilo ene instance na izvoz in nikoli skupne instance, zaščitene z zaklepanjem.

Pred načrtovanjem je dobro poznati še eno omejitev. Mreža XLSX se zaključi pri 1.048.576 vrsticah in 16.384 stolpcih, zato razdeljevanje listov, ki ga na strani XLS upravlja lastnost RowsPerSheet, tu le redko potrebujemo. Delovni zvezek z milijonom vrstic je tudi redkokdaj tisto, kar želi končni uporabnik. Ko je nabor podatkov res tako velik, je običajno boljša izbira datoteka z ločili; članek o izvozu v CSV in TSV podrobneje obravnava ločila, obnašanje oznake BOM in opozorilo glede ocenjevanja formul, ki velja v tem primeru.

Izbira začetne točke

Če se izvoz izvaja v namiznem orodju VCL in je izhod .xls sprejemljiv, začnite s komponento TDataToXLS in njeno podporo za razvrščanje. To zahteva najmanj kode, povezava prek SaveXLSWorkbookAsXLSX pa je na voljo, če se kasneje pojavi potreba po formatu .xlsx (če le sprejmete opisane omejitve zanesljivosti). Če se koda izvaja samodejno brez nadzora ali če prejemnik že od začetka zahteva .xlsx, napišite zanko. Obe poti vključujeta delujoče demonstracijske projekte in sta del paketa HotXLS Component.