„Delphi“ duomenų rinkinių eksportavimas į „Excel“ ataskaitas su „HotXLS“

Užklausos rezultato pavertimas „Excel“ ataskaita yra trys problemos po vienu apsiaustu. Kiekvienas „Delphi“ lauko tipas turi patekti į langelį kaip tinkamas „Excel“ tipas, antraštės eilutė turi atrodyti kaip ataskaita, o ne schemos sąvartynas, o skaičiai, datos ir piniginės reikšmės turi turėti formatus, kurie išlieka po eksportavimo. Praleiskite bet kurį iš šių žingsnių, ir failas vis tiek atsidarys, atrodys patikimai, tačiau sugrius tą akimirką, kai finansų skyriaus vartotojas pasirinks stulpelį ir lauks sumos, kuri niekada nepasirodys. Reikšmės buvo įrašytos kaip tekstas, „Excel“ jas traktuoja kaip etiketes, ir nebuvo iškelta jokia išimtis, kuri jus apie tai įspėtų.

„HotXLS“ yra vietinė „Object Pascal“ skaičiuoklių biblioteka, kuri tiesiogiai rašo XLS ir XLSX failus iš „Delphi“ ir „C++Builder“, nenaudodama „Excel“ automatizavimo. Ji siūlo du kelius nuo TDataset iki darbo knygos: paruoštą naudoti TDataToXLS komponentą ir rankiniu būdu parašytą ciklą, naudojantį darbo knygos API. Jie nėra keičiami vietomis. Komponentas yra VCL dalis, sukurta remiantis XLS fasadu, todėl teisingas pasirinkimas priklauso nuo to, kur veikia kodas ir kokio failo formato tikisi vartotojas. Toliau aprašomi abu keliai, riba, kurioje komponentas nustoja būti tinkamu įrankiu, ir kaip išlaikyti laukų tipus nepažeistus, kad ir kurį būdą pasirinktumėte.

Laukų tipai yra tikroji eksporto sutartis

Prieš atlikdami bet kokį API iškvietimą, nuspręskite, kaip kiekvienas „Delphi“ lauko tipas pateks į langelį. Langelis, kuris gauna „Delphi“ eilutę (angl. string), išlieka eilute. „HotXLS“ nespėlioja, kad '1,234.50' turėjo būti skaičius, ir ji neturėtų to daryti, nes nuo regiono priklausomas perrašymas yra būtent tai, kaip vokiškas kablelis, skiriantis dešimtainę dalį, angliškame serveryje virsta tūkstančių skirtuku. Patikimas būdas yra priskirti reikšmes per tipizuotas kreiptis: AsFloat arba AsCurrency skaitiniams laukams, AsDateTime datoms, kad langelyje būtų tikroji „Excel“ datos serija, o ne suformatuota eilutė, ir AsString tik tiems laukams, kurie iš tikrųjų yra tekstas.

NULL reikšmių tvarkymui reikalingas aiškus sprendimas, o ne numatytoji elgsena. Lauko reikšmės konvertavimas naudojant VarToStr paverčia SQL NULL į tuščią eilutę, kas yra tekstinis langelis, o praleidus priskyrimą, langelis lieka visiškai tuščias, ko ir tikisi AVERAGE, COUNT bei suvestinių lentelių vartotojai. Finansiniuose stulpeliuose prieš rašydami ciklą nuspręskite, ar NULL reiškia nulį, ar nežinomą reikšmę. Vizualiai jie atrodo vienodai, kai stulpelis suformatuojamas, tačiau šis skirtumas pakeičia kiekvieną toliau skaičiuojamą agreguotą reikšmę.

Komponento kelias: TDataToXLS VCL programose

Klasikinėje VCL programoje, kurioje užklausa jau yra susieta su duomenų moduliu, TDataToXLS yra vieno iškvietimo kelias. Jis pereina bet kurį TDataset įpėdinį, nesvarbu, ar tai būtų „FireDAC“, ADO, IBX, ar bet kas kitas, kas realizuoja abstrakčią duomenų rinkinio sąsają, ir sugeneruoja stilingą darbalapį su antraščių pavadinimais, šriftais, rėmeliais, pasirinktiniais tarpiniais grupių rezultatais ir automatiniu lapų skaidymu dideliems rezultatų rinkiniams.

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;

Dvi savybės čia atlieka didžiąją dalį darbo. Nustatymas HeaderSource := hsDisplayLabel įrašo kiekvieno lauko DisplayLabel, o ne neapdorotą SQL stulpelio pavadinimą, todėl darbo knygoje bus rodoma „Customer Name“, o ne CUST_NM. Savybė RowsPerSheet reikalinga todėl, kad komponentas rašo BIFF8 formatu, kurio tinklelis ribojamas iki 65 536 eilučių ir 256 stulpelių. Nustačius ją ties 50 000, didelis rezultatų rinkinys yra padalijamas per kelis lapus, kol formato apribojimai jo neapkarpė. Išvaizda valdoma per HeaderFont, DetailFont, GroupColor bei rėmelių stiliaus savybes, o DisableFormat rinkinys išjungia ištisas formatavimo kategorijas, kai vartotojui reikia paprastų langelių. Bet kokiems nestandartiniams poreikiams AfterCell ir AfterRow įvykiai pateikia ką tik įrašytą rėžį papildomam apdorojimui.

Kur baigiasi komponento galimybės

Komponente TDataToXLS yra numatyti trys apribojimai, kuriuos žinant iš anksto galima išvengti nepatogaus kodo perrašymo vėliau.

Tai yra VCL komponentas pilna prasme. Jo modulis įtraukia Forms, Controls ir Dialogs, todėl susiejus jį su konsolės užduotimi ar „Windows“ paslauga, VCL bus įtrauktas į dvejetainį failą. Pagrindiniai darbo knygos moduliai tokios priklausomybės neturi. Jiems reikia tik Windows, Classes, SysUtils ir Variants, todėl serverio kodo pusėje turėtų būti naudojamas žemiau pateiktas ciklas.
Jis sukurtas naudojant XLS fasadą. Komponentas užpildo IXLSWorkbook ir rašo .xls (BIFF8) formatu. Nėra jokios savybės, kuri leistų jį perjungti į OOXML išvestį.
Jo įvykiai naudoja XLS dialektą. Įvykio AfterCell parametras Cell: IXLSRange priklauso XLS objektų modeliui, todėl ten parašytas kiekvieno langelio pritaikymo kodas yra XLS stiliaus, net jei failas vėliau konvertuojamas į .xlsx.

„.xlsx“ generavimas iš komponento išvesties

Kai vartotojas reikalauja .xlsx formato, tačiau eksporto logika jau yra realizuota su TDataToXLS, tiltinė funkcija modulyje lxXlsxExport konvertuoja užpildytą darbo knygą vienu iškvietimu:

uses lxXlsxExport;

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

Vertinkite šį tiltą kaip lentelės duomenų nešėją, o ne visiškai tikslų konverterį. Jis nukopijuoja reikšmes, formules, skaičių formatus, užpildymo spalvas, šrifto atributus, stulpelių pločius ir rodinio nustatymus. Jis sąmoningai nekopijuoja rėmelių, sujungtų rėžių, komentarų, diagramų ar sąlyginio formatavimo. Paprastam tinkliui su antrašte ir eilutėmis to visiškai pakanka. Tačiau to neužtenka stilingai ataskaitai, o teisingas sprendimas yra generuoti XLSX tiesiogiai, o ne bandyti taisyti konvertuotą failą.

Rankiniu būdu parašytas ciklas paslaugoms ir paketinėms užduotims

Serverio kodas turėtų būti nukreiptas tiesiai į TXLSXWorkbook. Prieš kopijuodami bet kokį pavyzdį, atkreipkite dėmesį į gyvavimo ciklo skirtumus tarp šių dviejų fasadų. XLS pusėje TXLSWorkbook yra valdomas per nuorodų skaičiavimo sąsają ir jo negalima atlaisvinti rankiniu būdu, tuo tarpu TXLSXWorkbook yra paprasta klasė, reikalaujanti naudoti try..finally Free bloką. Sumaišius šias dvi konvencijas, lengva sukurti atminties nutekėjimą arba dvigubo atlaisvinimo klaidą.

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;

Svarbiausios eilutės yra tipizuoti priskyrimai ir IsNull apsauga. Datos atkeliauja kaip datos serijos numeriai, sumos – kaip dvigubo tikslumo slankiojo kablelio skaičiai, o NULL užsakymo datos lieka visiškai tuščios, užuot virtusios tuščiomis eilutėmis. Nustatymas StreamingWrite := True pakeičia tik išsaugojimo būdą: darbalapio XML siunčiamas tiesiai į zip konteinerį, o ne surenkamas kaip viena didelė eilutė atmintyje, kas padeta išvengti atminties šuolių atliekant SaveAs dideliems eilučių kiekiams. Kiekvienas išsaugojimo metodas taip pat turi TStream perkrovą, todėl darbo knyga gali keliauti tiesiai į HTTP atsakymą neįrašant jos į diską. Straipsnyje srautinis rašymas serverio ir paketinėse užduotyse aprašomas šis diegimo modelis, o straipsnis apie didelių darbo knygų našumą paaiškina, ką daryti, kai eilučių skaičius dar labiau išauga.

Šis ciklas taip pat leidžia efektyviai paskirstyti užduotis gijose. Abu varikliai yra sukurti naudojant vietinį „Object Pascal“ kodą (BIFF8 įrašų srautai vienoje pusėje ir OOXML zip bei XML kitoje), todėl jokia eksporto dalis nenaudoja COM automatizavimo ir serveriui nereikia „Excel“ licencijos. Tai suteikia lygiagretumą be vieno procesu strigčių, jei tik kiekviena gija kuria savo darbo knygą. Darbo knygos objektai nėra saugūs naudoti keliose gijose vienu metu, todėl galioja taisyklė: viena instancija vienam eksportui, ir jokio bendro naudojimo, apsaugoto užraktais.

Vieną apribojimą verta žinoti prieš pradedant kurti architektūrą. XLSX tinklelis baigiasi ties 1 048 576 eilutėmis ir 16 384 stulpeliais, todėl lapų skaidymo, kurį XLS pusėje atlieka RowsPerSheet, čia beveik niekada neprireiks. Be to, milijono eilučių darbo knyga retai kada yra tai, ko nori vartotojas. Kai rezultatų rinkinys yra tikrai toks didelis, paprastai geriau naudoti skyrikliais atskirtų reikšmių failą, o straipsnyje apie CSV ir TSV eksportavimą aprašomi skyrikliai, BOM elgsena bei ten taikomas formulių vertinimo įspėjimas.

Pradinio taško pasirinkimas

Jei eksportas vykdomas VCL darbalaukio programoje ir .xls išvestis yra priimtina, pradėkite nuo TDataToXLS ir jo grupavimo palaikymo. Tam reikės mažiausiai kodo, o tiltas per SaveXLSWorkbookAsXLSX padės, jei vėliau prireiks .xlsx formato, atsižvelgiant į jau minėtus tikslumo apribojimus. Jei kodas veikia be vartotojo priežiūros arba gavėjui iškart reikia .xlsx formato, parašykite ciklą. Abu būdai platinami su veikiančiais demonstraciniais projektais ir yra įtraukti į HotXLS Component paketą.