Pretvaranje rezultata upita u Excel izvješće sastoji se od triju problema u jednom. Svaki tip polja u Delphiju mora sletjeti u ćeliju kao ispravan tip u Excelu, redak zaglavlja mora izgledati kao izvješće umjesto kao ispis sheme, a brojevi, datumi i novčani iznosi moraju nositi formate koji će preživjeti putovanje. Preskočite bilo koji od njih i datoteka će se i dalje otvarati, i dalje izgledati vjerojatno, a ipak će zakazati onog trenutka kada korisnik iz financija odabere stupac i pričeka zbroj koji se nikada ne pojavi. Vrijednosti su zapisane kao tekst, Excel ih tretira kao oznake i nikakva iznimka nije prijavljena da bi vas upozorila.
HotXLS je izvorna knjižnica proračunskih tablica za Object Pascal koja izravno zapisuje XLS i XLSX datoteke iz Delphija i C++Buildera, bez ikakve Excel automatizacije. Nudi dva puta od TDataset do radne knjige: ugrađenu komponentu TDataToXLS i ručno napisanu petlju prema API-ju radne knjige. Oni se ne mogu međusobno zamijeniti. Komponenta je dio VCL obitelji izgrađen na XLS fasadi, pa ispravan odabir ovisi o tome gdje se kôd izvodi i koji format datoteke primatelj očekuje. Ono što slijedi su oba puta, granica gdje 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
Prije bilo kojeg poziva API-ja odlučite kako će koji tip polja iz Delphija sletjeti 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 ovisno o lokalitetu točno onaj način na koji se njemački decimalni zarez pretvara u separator tisućica na engleskom poslužitelju. Pouzdan obrazac je dodjela putem tipiziranih pristupnika (accessors): AsFloat ili AsCurrency za numerička polja, AsDateTime za datume kako bi ćelija sadržavala stvarni Excelov serijski broj datuma umjesto oblikovanog niza znakova, te AsString samo za polja koja su doista tekst.
Rukovanje NULL vrijednostima zaslužuje izričitu odluku, a ne zadano ponašanje. Pretvaranje vrijednosti polja pomoću VarToStr pretvara SQL NULL u prazan niz znakova, što je tekstualna ćelija, dok preskakanje dodjele ostavlja ćeliju doista praznom, što je ono što potrošači funkcija AVERAGE, COUNT i zaokretnih tablica (pivot-tables) očekuju. Za stupce s novčanim iznosima odlučite prije pisanja petlje znači li NULL nulu ili nepoznatu vrijednost. Njih dvoje se prikazuju identično nakon što netko oblikuje stupac, a ta razlika mijenja svaki agregat koji se izračunava u kasnijem toku.
Put komponente: TDataToXLS u VCL aplikacijama
Za klasičnu VCL aplikaciju s upitom koji je već spojen u podatkovni modul (data module), TDataToXLS je put s jednim pozivom. On prolazi kroz bilo kojeg potomka klase TDataset, bilo FireDAC, ADO, IBX ili bilo što drugo što implementira apstraktno sučelje skupa podataka, i proizvodi stilizirani radni list sa zaglavljima, fontovima, obrubima, opcionalnim međuzbrojevima grupa (subtotals) i automatskim dijeljenjem 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 ovdje nose većinu produkcijske težine. HeaderSource := hsDisplayLabel zapisuje DisplayLabel svakog polja umjesto sirovog SQL naziva stupca, pa radna knjiga prikazuje 'Customer Name' umjesto CUST_NM. Svojstvo RowsPerSheet postoji jer komponenta piše BIFF8 format, čija se mreža zaustavlja na 65.536 redaka i 256 stupaca; njegovo postavljanje na 50.000 dijeli veliki skup rezultata na više listova prije nego što ga gornja granica formata skrati. Izgledom upravljaju svojstva HeaderFont, DetailFont, GroupColor i stilovi obruba, a skup DisableFormat isključuje cijele kategorije oblikovanja kada primatelj želi jednostavne ćelije. Za bilo što prilagođeno, događaji AfterCell i AfterRow predaju vam upravo zapisani raspon za naknadnu obradu.
Gdje komponenta prestaje biti opcija
Tri su ograničenja ugrađena u TDataToXLS, a njihovo poznavanje unaprijed sprječava neugodan redizajn dva sprinta kasnije.
- To je VCL komponenta u punom smislu. Njezina jedinica povlači
Forms,ControlsiDialogs, pa povezivanje iste u konzolni posao ili Windows servis uvlači VCL u binarnu datoteku. Jezgrene jedinice radne knjige nemaju takvu ovisnost. Potrebni su im samoWindows,Classes,SysUtilsiVariants, zbog čega bi poslužiteljski kôd trebao koristiti petlju prikazanu u nastavku. - Izgrađena je na XLS fasadi. Komponenta popunjava sučelje
IXLSWorkbooki zapisuje .xls (BIFF8). Ne postoji svojstvo koje je prebacuje na OOXML izlaz. - Njezini događaji govore XLS dijalektom. Parametar
Cell: IXLSRangeuAfterCellpripada XLS objektom modelu, pa je prilagodba po ćeliji napisana tamo kôd u XLS stilu čak i ako se datoteka nakon toga pretvori u .xlsx.
Stvaranje .xlsx formata iz izlaza komponente
Kada primatelj inzistira na .xlsx formatu, ali logika izvoza već živi u komponenti TDataToXLS, funkcija mosta u jedinici lxXlsxExport pretvara popunjenu radnu knjigu 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 prijenosnik tabličnih podataka, a ne kao pretvarač pune vjernosti. On kopira vrijednosti, formule, formate brojeva, boje ispune, atribute fontova, širine stupaca i postavke prikaza. Namjerno ne kopira obrube, spojene raspone, komentare, grafikone niti uvjetna oblikovanja. Za običnu mrežu zaglavlja i redaka to je točno dovoljno. Za stilizirano izvješće nije, i ispravan način je izravno generiranje XLSX-a radije nego krpanje pretvorene datoteke.
Ručno napisana petlja za servise i serijske poslove
Poslužiteljski kôd trebao bi izravno ciljati TXLSXWorkbook. Pripazite na razliku u životnom vijeku između ove dvije fasade prije kopiranja bilo kojeg uzorka. TXLSWorkbook na XLS strani drži se kroz sučelje s brojenjem referenci i ne smije se ručno oslobađati, dok je TXLSXWorkbook obična klasa koja zahtijeva try..finally Free. Miješ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 tipizirane dodjele i provjera IsNull. Datumi dolaze kao serijski brojevi datuma, iznosi dolaze kao dvostruki realni brojevi (doubles), a NULL datumi narudžbi ostaju doista prazni umjesto da postanu prazni znakovni nizovi. Postavka StreamingWrite := True mijenja samo putanju spremanja: XML radnog lista struji izravno u zip spremnik umjesto da se najprije sastavlja kao jedan veliki znakovni niz, što izravnava vršno opterećenje memorije u trenutku poziva SaveAs za šesteroznamenkasti broj redaka. Svaka metoda spremanja ima i preopterećenje TStream, pa radna knjiga može ići izravno u HTTP odgovor bez doticaja s diskom. Članak strujno pisanje i serijski poslovi objašnjava taj obrazac implementacije, a članak performanse velikih radnih knjiga pokriva što učiniti kada broj redaka dodatno poraste.
Ova petlja je ujedno i put koji se dobro skalira kroz dretve (threads). Oba pogona su izvorni Object Pascal pisci, tokovi zapisa BIFF8 na jednoj strani i OOXML zip plus XML na drugoj, pa nijedan dio izvoza ne dotiče COM automatizaciju niti treba Excel licencu na poslužitelju. To vam donosi paralelnost bez uskog grla jedne instance, pod uvjetom da svaka dretva gradi vlastitu radnu knjigu. Objekti radnih knjiga nisu sigurni za rad s više dretvi (thread-safe) kod zajedničke upotrebe, pa je pravilo jedna instanca po izvozu, nikada zajednička zaštićena lokotom.
Jednu granicu vrijedi znati prije nego što dizajnirate oko nje. XLSX mreža se zaustavlja na 1.048.576 redaka i 16.384 stupaca, pa podjela listova kojom upravlja RowsPerSheet na XLS strani ovdje rijetko zatreba. Radna knjiga od milijun redaka ionako je rijetko ono što ljudski primatelj želi. Kada je skup rezultata doista tako velik, datoteka s graničnicima obično je bolji ugovor, a članak izvoz u CSV i TSV pokriva graničnike, ponašanje BOM-a i upozorenje o procjeni formula koje se tamo primjenjuje.
Odabir početne točke
Ako izvoz živi u stolnom VCL alatu i .xls izlaz je prihvatljiv, počnite s komponentom TDataToXLS i njezinom podrškom za grupiranje. To zahtijeva najmanje koda, a most kroz SaveXLSWorkbookAsXLSX je tu kada netko kasnije zatraži .xlsx, sve dok prihvaćate već opisane granice vjernosti. Ako kôd radi bez nadzora, ili primatelj od samog početka zahtijeva .xlsx format, napišite petlju. Oba puta dolaze s funkcionalnim demo projektima i dio su paketa HotXLS Component.