LlosLab Software Development BlogInženjerske beleške za programere PDF-a, Delphi-ja i softvera za dokumente

Tehnički članak

Izvoz Delphi datasetova u Excel izveštaje pomoću HotXLS-a

HotXLS Component

Pretvaranje rezultata upita u Excel izveštaj sastoji se od triju problema u jednom. Svaki tip polja u Delphiju mora da sleti u ćeliju kao ispravan tip u Excelu, red zaglavlja mora da izgleda kao izveštaj umesto kao ispis sheme, a brojevi, datumi i novčani iznosi moraju da nose formate koji će preživeti putovanje. Preskočite bilo koji od njih i datoteka će se i dalje otvarati, i dalje izgledati verovatno, a ipak će zakazati onog trenutka kada korisnik iz finansija odabere kolonu i pričeka zbir koji se nikada ne pojavi. Vrednosti su zapisane kao tekst, Excel ih tretira kao oznake i nikakva izuzetak nije prijavljen da bi vas upozorio

HotXLS je izvorna biblioteka proračunskih tabela za Object Pascal koja direktno zapisuje XLS i XLSX datoteke iz Delphija i C++Buildera, bez ikakve Excel automatizacije. Nudi dva puta od TDataset do radne sveske: ugrađenu komponentu TDataToXLS i ručno napisanu petlju prema API-ju radne sveske. Oni se ne mogu međusobno zameniti. Komponenta je deo VCL familije izgrađen na XLS fasadi, pa ispravan odabir zavisi od toga gde se kod izvršava i koji format datoteke primalac očekuje. Ono što sledi su oba puta, granica gde komponenta prestaje biti pravi alat i kako očuvati tipove polja netaknutima bez obzira na to koji put odabrali

Tipovi polja su stvarni izvozni ugovor

Pre bilo kog poziva API-ja odlučite kako će koji tip polja iz Delphija sleteti u ćeliju. Ćelija koja primi znakovni niz iz Delphija ostaje znakovni niz. HotXLS ne nagađa da je '1,234.50' trebao biti broj, niti bi to trebao činiti, jer je raščlanjivanje zavisno od lokaliteta tačno onaj način na koji se nemački decimalni zarez pretvara u separator hiljada na engleskom serveru. Pouzdan obrazac je dodela putem tipisanih pristupnika (accessors): AsFloat ili AsCurrency za numerička polja, AsDateTime za datume kako bi ćelija sadržala stvarni Excelov serijski broj datuma umjesto oblikovanog niza znakova, te AsString samo za polja koja su doista tekst

Rukovanje NULL vrednostima zaslužuje izričitu odluku, a ne zadano ponašanje. Pretvaranje vrednosti polja pomoću VarToStr pretvara SQL NULL u prazan niz znakova, što je tekstualna ćelija, dok preskakanje dodele ostavlja ćeliju doista praznom, što je ono što potrošači funkcija AVERAGE, COUNT i zaokretnih tabela (pivot-tables) očekuju. Za kolone sa novčanim iznosima odlučite pre pisanja petlje znači li NULL nulu ili nepoznatu vrednost. Njih dvoje se prikazuju identično nakon što neko oblikuje kolonu, a ta razlika menja svaki agregat koji se izračunava u kasnijem toku

Put komponente: TDataToXLS u VCL aplikacijama

Za klasičnu VCL aplikaciju sa upitom koji je već spojen u podatkovni modul (data module), TDataToXLS je put sa jednim pozivom. On prolazi kroz bilo kog potomka klase TDataset, bilo FireDAC, ADO, IBX ili bilo šta drugo što implementira apstraktno sučelje skupa podataka, i proizvodi stilizovani radni list sa zaglavljima, fontovima, obrubima, opcionalnim međuzbirovima grupa (subtotals) i automatskim deljenjem listova za velike skupove rezultata

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;

Dva svojstva ovde nose većinu produkcijske težine. HeaderSource := hsDisplayLabel zapisuje DisplayLabel svakog polja umesto sirovog SQL naziva kolone, pa radna sveska prikazuje 'Customer Name' umesto CUST_NM. Svojstvo RowsPerSheet postoji jer komponenta piše BIFF8 format, čija se mreža zaustavlja na 65.536 redova i 256 kolona; njegovo postavljanje na 50.000 deli veliki skup rezultata na više listova pre nego što ga gornja granica formata skrati. Izgledom upravljaju svojstva HeaderFont, DetailFont, GroupColor i stilovi obruba, a skup DisableFormat isključuje cele kategorije oblikovanja kada primalac želi jednostavne ćelije. Za bilo šta prilagođeno, događaji AfterCell i AfterRow predaju vam upravo zapisani raspon za naknadnu obradu

Gde komponenta prestaje biti opcija

Tri su ograničenja ugrađena u TDataToXLS, a njihovo poznavanje unapred sprečava neprijatan redizajn dva sprinta kasnije

  • To je VCL komponenta u punom smislu. Njena jedinica povlači Forms, Controls i Dialogs, pa povezivanje iste u konzolni posao ili Windows servis uvlači VCL u binarnu datoteku. Jezgrene jedinice radne sveske nemaju takvu zavisnost. Potrebni su im samo Windows, Classes, SysUtils i Variants, zbog čega bi poslužiteljski kod trebao da koristi petlju prikazanu u nastavku
  • Izgrađena je na XLS fasadi. Komponenta popunjava sučelje IXLSWorkbook i zapisuje .xls (BIFF8). Ne postoji svojstvo koje je prebacuje na OOXML izlaz
  • Njene funkcije/događaji govore XLS dijalektom. Parametar Cell: IXLSRange u AfterCell pripada XLS objektom modelu, pa je prilagođavanje po ćeliji napisano tamo kod u XLS stilu čak i ako se datoteka nakon toga pretvori u .xlsx

Stvaranje .xlsx formata iz izlaza komponente

Kada primalac insistira na .xlsx formatu, ali logika izvoza već živi u komponenti TDataToXLS, funkcija mosta u jedinici lxXlsxExport pretvara 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 taj most kao prenosnik tabelarnih podataka, a ne kao pretvarač pune vernosti. On kopira vrednosti, formule, formate brojeva, boje ispune, atribute fontova, širine kolona i postavke prikaza. Namerno ne kopira obrube, spojene raspone, komentare, grafikone niti uslovna oblikovanja. Za običnu mrežu zaglavlja i redova to je tačno dovoljno. Za stilizovani izveštaj nije, i ispravan način je direktno generisanje XLSX-a radije nego krpljenje pretvorene datoteke

Ručno napisana petlja za servise i serijske poslove

Poslužiteljski kod trebao bi direktno da cilja TXLSXWorkbook. Pripazite na razliku u životnom veku između ove dve fasade pre kopiranja bilo kog uzorka. TXLSWorkbook na XLS strani drži se kroz sučelje sa brojenjem referenci i ne sme se ručno oslobađati, dok je TXLSXWorkbook obična klasa koja zahteva try..finally Free. Mešanje ovih dviju konvencija je pouzdan način za stvaranje curenja memorije ili dvostrukog oslobađ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;

Linije koje su važne su tipisane dodele i provera IsNull. Datumi dolaze kao serijski brojevi datuma, iznosi dolaze kao dvostruki realni brojevi (doubles), a NULL datumi porudžbina ostaju doista prazni umesto da postanu prazni znakovni nizovi. Postavka StreamingWrite := True menja samo putanju spremanja: XML radnog lista struji direktno u zip kontejner umesto da se najpre sastavlja kao jedan veliki znakovni niz, što izravnava vršno opterećenje memorije u trenutku poziva SaveAs za šesterocifreni broj redova. Svaka metoda spremanja ima i preopterećenje TStream, pa radna sveska može ići direktno u HTTP odgovor bez doticaja sa diskom. Članak strujno pisanje i serijski poslovi objašnjava taj obrazac implementacije, a članak performanse velikih radnih sveski pokriva što učiniti kada broj redova dodatno poraste

Ova petlja je ujedno i put koji se dobro skalira kroz niti (threads). Oba pogona su izvorni Object Pascal pisci, tokovi zapisa BIFF8 na jednoj strani i OOXML zip plus XML na drugoj, pa nijedan deo izvoza ne dotiče COM automatizaciju niti treba Excel licencu na poslužitelju. To vam donosi paralelnost bez uskog grla jedne instance, pod uslovom da svaka nit gradi sopstvenu radnu svesku. Objekti radnih sveski nisu bezbedni za rad sa više niti (thread-safe) kod zajedničke upotrebe, pa je pravilo jedna instanca po izvozu, nikada zajednička zaštićena lokotom

Jednu granicu vredi znati pre nego što dizajnirate oko nje. XLSX mreža se zaustavlja na 1.048.576 redova i 16.384 kolona, pa podela listova kojom upravlja RowsPerSheet na XLS strani ovde retko zatreba. Radna sveska od milion redova ionako je retko ono što ljudski primalac želi. Kada je skup rezultata doista tako velik, datoteka sa graničnicima obično je bolji ugovor, a članak izvoz u CSV i TSV pokriva graničnike, ponašanje BOM-a i upozorenje o proceni formula koje se tamo primenjuje

Odabir početne točke

Ako izvoz živi u stonom VCL alatu i .xls izlaz je prihvatljiv, počnite sa komponentom TDataToXLS i njenom podrškom za grupisanje. To zahteva najmanje koda, a most kroz SaveXLSWorkbookAsXLSX je tu kada neko kasnije zatraži .xlsx, sve dok prihvatate već opisane granice vernosti. Ako kod radi bez nadzora, ili primalac od samog početka zahteva .xlsx format, napišite petlju. Oba puta dolaze sa funkcionalnim demo projektima i deo su paketa HotXLS Component