Izvoz Delphi baza podataka u Excel izveštaje pomoću HotXLS

Pretvaranje rezultata upita u Excel izveštaj predstavlja tri problema u jednom. Svaki tip polja iz Delphi-ja mora završiti u ćeliji kao odgovarajući Excel tip, red zaglavlja mora izgledati kao izveštaj umesto kao ispis šeme baze podataka, a brojevi, datumi i novčane vrednosti moraju nositi formate koji preživljavaju prenos. Preskočite bilo koji od ovih koraka i fajl će se i dalje otvarati, i dalje će izgledati uverljivo, ali će zakazati onog trenutka kada finansijski korisnik izabere kolonu i pokuša da je sabere, a zbir se ne pojavi. Vrednosti su upisane kao tekst, Excel ih tretira kao oznake (labels), a nikakav izuzetak nije podignut da vas na to upozori.

HotXLS je izvorna Object Pascal biblioteka za radne sveske koja piše XLS i XLSX fajlove direktno iz Delphi i C++Builder programa, bez ikakve potrebe za automatizacijom Excel-a. Ona nudi dva puta od TDataset-a do radne sveske: gotovu komponentu TDataToXLS i ručno napisanu petlju nad API-jem radne sveske. One nisu međusobno zamenljive. Komponenta je deo VCL ekosistema izgrađena na XLS interfejsu, pa ispravan izbor zavisi od toga gde se kod izvršava i koji format fajla primalac dodgy. U nastavku su predstavljena oba puta, granica gde komponenta prestaje da bude pravi alat, kao i način da sačuvate tipove polja netaknutim, bez obzira na to koji put odaberete.

Tipovi polja su stvarni ugovor o izvozu

Pre bilo kog API poziva, odlučite kako svaki Delphi tip polja završava u ćeliji. Ćelija koja primi Delphi string ostaje string. HotXLS ne nagađa da je '1,234.50' trebalo da bude broj, i to ne bi ni trebalo da radi, jer je ponovno parsiranje zavisno od lokaliteta upravo način na koji se nemački decimalni zarez pretvara u separator hiljada na engleskom serveru. Pouzdan obrazac je dodeljivanje preko tipiziranih funkcija pristupa: AsFloat ili AsCurrency za numerička polja, AsDateTime za datume kako bi ćelija sadržala pravi Excel-ov serijski broj datuma umesto formatiranog stringa, i AsString samo za polja koja su zaista tekst.

Rukovanje null vrednostima zaslužuje eksplicitnu odluku umesto podrazumevanog ponašanja. Pretvaranje vrednosti polja pomoću VarToStr pretvara SQL NULL u prazan string, što predstavlja tekstualnu ćeliju, dok preskakanje dodele ostavlja ćeliju zaista praznom, što je upravo ono što potrošači funkcija AVERAGE, COUNT i izvedenih tabela (pivot tables) očekuju. Za kolone sa novčanim vrednostima, pre pisanja petlje odlučite da li NULL označava nulu ili nepoznatu vrednost. Te dve opcije se prikazuju identično kada neko formatira kolonu, a razlika menja svaki zbirni proračun u kasnijoj fazi.

Putanja komponente: TDataToXLS u VCL aplikacijama

Za klasičnu VCL aplikaciju sa upitom koji je već povezan u modulu podataka, TDataToXLS je putanja od jednog poziva. Ona prolazi kroz bilo koji izvedeni TDataset, bilo da je to FireDAC, ADO, IBX ili bilo šta drugo što implementira apstraktni interfejs dataset-a, i proizvodi stilizovani radni list sa zaglavljima, fontovima, ivicama, opcionim podzbirovima grupa i automatskim deljenjem listova za velike skupove rezultata.

var
  Exporter: TDataToXLS;
begin
  Book := TXLSWorkbook.Create;
  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;

Dva svojstva ovde nose najveću težinu u produkciji. HeaderSource := hsDisplayLabel upisuje DisplayLabel svakog polja umesto sirovog SQL naziva kolone, pa u radnoj svesci piše "Customer Name" umesto CUST_NM. Svojstvo RowsPerSheet postoji jer komponenta piše u BIFF8 formatu, čija se mreža završava na 65.536 redova i 256 kolona; postavljanje ove vrednosti na 50.000 deli veliki skup rezultata na više listova pre nego što ga gornja granica formata skrati. Izgledom se upravlja preko svojstava HeaderFont, DetailFont, GroupColor i stilova ivica, dok skup DisableFormat isključuje čitave kategorije formatiranja kada potrošač zahteva obične ćelije. Za bilo kakva prilagođena podešavanja, događaji AfterCell i AfterRow vam prosleđuju upravo upisani opseg za naknadnu obradu.

Gde komponenta prestaje

Tri ograničenja su dizajnirana u sklopu komponente TDataToXLS, a njihovo poznavanje unapred sprečava nezgodan redizajn koda kasnije:

Ona je VCL komponenta u punom smislu reči. Njena jedinica (unit) povlači Forms, Controls i Dialogs, pa povezivanje iste u konzolni posao ili Windows servis uvlači VCL u binarni fajl. Osnovne jedinice radne sveske nemaju takvu zavisnost. Potrebni su im samo Windows, Classes, SysUtils i Variants, zbog čega kod na strani servera treba da koristi petlju prikazanu u nastavku.
Izgrađena je na XLS interfejsu. Komponenta popunjava IXLSWorkbook i upisuje .xls (BIFF8). Ne postoji svojstvo koje je prebacuje na OOXML izlaz.
Njeni događaji govore XLS dijalektom. Parametar Cell: IXLSRange u događaju AfterCell pripada XLS objektnom modelu, pa je svako prilagođavanje ćelije napisano tamo u stilu XLS koda, čak i ako se fajl kasnije konvertuje u .xlsx.

Dobijanje .xlsx fajla iz izlaza komponente

Kada primalac insistira na .xlsx formatu, a logika izvoza već živi u komponenti TDataToXLS, prelazna funkcija u jedinici lxXlsxExport konvertuje popunjenu radnu svesku u jednom pozivu:

uses lxXlsxExport;

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

Tretirajte prelaznu funkciju kao prenosioca tabelarnih podataka, a ne kao konverter potpunog kvaliteta. Ona kopira vrednosti, formule, formate brojeva, boje ispune, atribute fontova, širine kolona i podešavanja prikaza. Namerno ne kopira ivice, spojene opsege, komentare, grafikone ili uslovno formatiranje. Za običnu tabelu sa zaglavljem i redovima to je sasvim dovoljno. Za stilizovani izveštaj nije, i ispravno rešenje je generisanje XLSX-a direktno, umesto krpljenja konvertovanog fajla.

Ručno napisana petlja za servise i serijske poslove

Kod na strani servera treba direktno da cilja TXLSXWorkbook. Obratite pažnju na razliku u životnom veku između ova dva interfejsa pre kopiranja bilo kog primera. TXLSWorkbook na XLS strani se koristi preko interfejsa sa brojenjem referenci i ne sme se ručno oslobađati, dok je TXLSXWorkbook obična klasa koja zahteva try..finally Free blok. Mešanje ove dve konvencije je siguran put do curenja memorije ili rušenja programa.

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;

Linije koje su najvažnije jesu tipizirane dodele i IsNull zaštita. Datumi stižu kao serijski brojevi datuma, iznosi kao realni brojevi (doubles), a NULL datumi porudžbina ostaju zaista prazni umesto da postanu prazni stringovi. Postavljanje StreamingWrite := True menja samo putanju čuvanja: XML radnog lista se strimuje direktno u zip kontejner umesto da se najpre sastavlja kao jedan veliki string, što smanjuje memorijski skok u trenutku pozivanja SaveAs za tabele sa šetocifrenim brojem redova. Svaka metoda čuvanja takođe ima preopterećenje za TStream, tako da radna sveska može ići direktno u HTTP odgovor bez upisivanja na disk. Članak o strimovanju i serijskim poslovima opisuje taj šablon implementacije, a članak o performansama velikih radnih svezaka pokriva šta raditi kada broj redova raste još više.

Ova petlja je takođe putanja koja se dobro skalira kroz više niti (threads). Oba mehanizma su izvorni Object Pascal pisci, sa tokovima zapisa BIFF8 sa jedne strane i OOXML zip-om i XML-om sa druge, pa nijedan deo izvoza ne koristi COM automatizaciju niti zahteva licencu za Excel na serveru. To vam omogućava paralelni rad bez uskih grla sa jednom instancom, pod uslovom da svaka nit gradi sopstvenu radnu svesku. Objekti radne sveske nisu bezbedni za deljenje među nitima (not thread-safe), tako da je pravilo: jedna instanca po izvozu, nikada deljena instanca zaštićena lokotom.

Jedno ograničenje vredi znati pre nego što dizajnirate sistem oko njega. XLSX mreža se zaustavlja na 1.048.576 redova i 16.384 kolone, pa deljenje listova koje RowsPerSheet obavlja na XLS strani ovde retko zatreba. Radna sveska od milion redova je takođe retko ono što ljudski korisnik želi. Kada je skup rezultata zaista tako veliki, razgraničeni tekstualni fajl je obično bolji izbor, a članak o izvozu u CSV i TSV formate pokriva graničnike, ponašanje BOM-a i napomenu o proračunu formula koja se tamo primenjuje.

Izbor početne tačke

Ako izvoz živi u VCL stonom (desktop) alatu i .xls izlaz je prihvatljiv, počnite sa TDataToXLS i njegovom podrškom za grupisanje. To zahteva najmanje koda, a most kroz SaveXLSWorkbookAsXLSX je tu kada neko kasnije zatraži .xlsx, sve dok prihvatate pomenuta ograničenja vernosti konverzije. Ako se kod izvršava automatski bez nadzora, ili primalac zahteva .xlsx od samog početka, napišite petlju. Obe putanje dolaze sa funkcionalnim demo projektima i deo su paketa HotXLS Component.