Delphi-tietokantatulosten vienti Excel-raportteihin HotXLS-Delphi-komponentilla

Kyselytuloksen muuttaminen Excel-raportiksi on kolme ongelmaa saman takin alla. Jokaisen Delphi-kenttätyypin on päädyttävä soluun oikeana Excel-tyyppinä, otsikkorivin on luettava kuin raportti eikä kuin rakennemäärityksen tuloste, ja lukujen, päivämäärien sekä rahamäärien on kannettava mukanaan muotoilut, jotka säilyvät matkassa. Jätä yksikin näistä väliin, niin tiedosto avautuu yhä, näyttää uskottavalta ja epäonnistuu silti heti, kun talouskäyttäjä valitsee sarakkeen ja odottaa summaa, jota ei koskaan ilmesty. Arvot kirjoitettiin tekstinä, Excel kohtelee niitä tunnisteina, eikä mikään poikkeus varoittanut sinua

HotXLS on natiivi Object Pascal -laskentataulukkokirjasto, joka kirjoittaa XLS- ja XLSX-tiedostoja suoraan Delphistä ja C++Builderista ilman Excel-automaatiota. Se tarjoaa kaksi reittiä TDataset-objektista työkirjaan: käyttövalmiin TDataToXLS-komponentin ja käsin kirjoitetun silmukan työkirjan API:a vasten. Ne eivät ole keskenään vaihdettavia. Komponentti on XLS-rajapinnan päälle rakennettu VCL-kansalainen, joten oikea valinta riippuu siitä, missä koodi suoritetaan ja mitä tiedostomuotoa vastaanottaja odottaa. Seuraavassa käsitellään molemmat reitit, kohta jossa komponentti lakkaa olemasta oikea työkalu ja miten kenttätyypit pidetään eheinä kummallakin reitillä

Kenttätyypit ovat todellinen vientisopimus

Päätä ennen yhtäkään API-kutsua, miten kukin Delphi-kenttätyyppi päätyy soluun. Delphi-merkkijonon vastaanottava solu pysyy merkkijonona. HotXLS ei arvaa, että '1,234.50' oli tarkoitettu luvuksi, eikä sen pidäkään, sillä maa-asetuksista riippuva uudelleenjäsennys muuttaa juuri siten saksalaisen desimaalipilkun tuhaterottimeksi englanninkielisellä palvelimella. Luotettava malli on sijoittaa tyypitettyjen käytettävien kautta: AsFloat tai AsCurrency numerokentille, AsDateTime päivämäärille, jotta solussa on aito Excelin päivämääräsarja muotoillun merkkijonon sijaan, sekä AsString vain kentille, jotka ovat todella tekstiä

Null-arvojen käsittely vaatii nimenomaisen päätöksen oletuksen sijaan. Kenttäarvon muuntaminen VarToStr-kutsulla muuttaa SQL NULL -arvon tyhjäksi merkkijonoksi eli tekstisoluksi, kun taas sijoituksen ohittaminen jättää solun aidosti tyhjäksi, mitä AVERAGE-, COUNT- ja pivot-taulukkokäyttäjät odottavat. Päätä rahasarakkeille ennen silmukan kirjoittamista, tarkoittaako NULL nollaa vai tuntematonta. Kun joku muotoilee sarakkeen, ne näyttävät samalta, mutta ero muuttaa jokaista myöhemmin laskettua koostetta

Komponenttireitti: TDataToXLS VCL-sovelluksissa

Perinteisessä VCL-sovelluksessa, jossa kysely on jo kytketty datamoduuliin, TDataToXLS on yhden kutsun reitti. Se käy läpi minkä tahansa TDataset-jälkeläisen, olipa kyse FireDACista, ADO:sta, IBX:stä tai mistä tahansa abstraktin tietojoukkorajapinnan toteuttavasta tekniikasta, ja tuottaa muotoillun laskentataulukon, jossa on otsikkotekstit, fontit, reunat, valinnaiset ryhmän välisummat ja suurten tulosjoukkojen automaattinen jako arkeille

var
  Exporter: TDataToXLS;
begin
  Exporter := TDataToXLS.Create(nil);
  try
    Exporter.Dataset := OrdersQuery;          // mikä tahansa TDataset-perillinen
    Exporter.WorksheetName := 'Orders';
    Exporter.HeaderSource := hsDisplayLabel;  // kuvatekstit, ei raakaa sarakenimiä
    Exporter.GroupFields.Add('CustomerID');   // välisummablokki asiakasta kohden
    Exporter.RowsPerSheet := 50000;           // pysy alle BIFF8-rivikaton
    Exporter.VisibleFieldsOnly := True;             // kunnioita Field.Visible-arvoa
    Exporter.SaveDatasetAs('orders.xls');
  finally
    Exporter.Free;
  end;
end;

Kaksi ominaisuutta kantaa tässä suurimman osan tuotantokuormasta. HeaderSource := hsDisplayLabel kirjoittaa kunkin kentän DisplayLabel-arvon raa'an SQL-sarakenimen sijaan, joten työkirjassa lukee "Customer Name" eikä CUST_NM. RowsPerSheet on olemassa, koska komponentti kirjoittaa BIFF8-muotoa, jonka ruudukko päättyy 65 536 riviin ja 256 sarakkeeseen; arvon 50 000 asettaminen jakaa suuren tulosjoukon arkeille ennen kuin muotoraja katkaisee sen. Ulkoasua hallitsevat HeaderFont-, DetailFont-, GroupColor- ja reunustyylien ominaisuudet, ja DisableFormat-joukko poistaa kokonaisia muotoiluluokkia käytöstä, kun vastaanottaja haluaa pelkkiä soluja. Kaikkea erityistä varten AfterCell- ja AfterRow-tapahtumat antavat juuri kirjoitetun alueen jälkikäsiteltäväksi

Missä komponentti päättyy

Kolme rajoitetta on rakennettu TDataToXLS-komponenttiin, ja niiden tunteminen etukäteen välttää kiusallisen uudelleensuunnittelun kahden sprintin päästä

• Se on VCL-komponentti sanan täydessä merkityksessä. Sen yksikkö vetää mukaan Forms-, Controls- ja Dialogs-yksiköt, joten sen linkittäminen konsolityöhön tai Windows-palveluun tuo VCL:n binääriin. Työkirjan ydinyksiköillä ei ole tällaista riippuvuutta. Ne tarvitsevat vain Windows-, Classes-, SysUtils- ja Variants-yksiköt, minkä vuoksi palvelinpuolen koodin kannattaa käyttää alla esitettyä silmukkaa
• Se on rakennettu XLS-rajapinnan päälle. Komponentti täyttää IXLSWorkbook-objektin ja kirjoittaa .xls-muotoa (BIFF8). Ominaisuutta, joka vaihtaisi sen OOXML-tulosteeseen, ei ole
• Sen tapahtumat puhuvat XLS-murretta. Cell: IXLSRange -parametri AfterCell-tapahtumassa kuuluu XLS-objektimalliin, joten siellä kirjoitettu solukohtainen mukautus on XLS-tyylistä koodia, vaikka tiedosto muunnettaisiin myöhemmin .xlsx-muotoon

.xlsx-tiedoston tuottaminen komponentin tulosteesta

Kun vastaanottaja vaatii .xlsx-muotoa, mutta vientilogiikka on jo TDataToXLS-komponentissa, lxXlsxExport-yksikön siltatoiminto muuntaa täytetyn työkirjan yhdellä kutsulla:

uses lxXlsxExport;

Exporter.SaveDatasetAs('orders.xls');
// komponentti paljastaa täyttämänsä IXLSWorkbook-työkirjan
SaveXLSWorkbookAsXLSX(Exporter.Workbook, 'orders.xlsx');

Käsittele siltaa taulukkomuotoisen datan välittäjänä, ei täydellisen tarkkuuden muuntimena. Se kopioi arvot, kaavat, lukumuotoilut, täyttövärit, fonttiattribuutit, sarakeleveydet ja näkymäasetukset. Se ei tarkoituksella kopioi reunoja, yhdistettyjä alueita, kommentteja, kaavioita eikä ehdollisia muotoiluja. Tasaiselle otsikon ja rivien ruudukolle se riittää täsmälleen. Muotoillulle raportille se ei riitä, ja rehellinen korjaus on luoda XLSX suoraan sen sijaan, että paikattaisiin muunnettua tiedostoa

Käsin kirjoitettu silmukka palveluille ja eräajoille

Palvelinpuolen koodin tulee kohdistaa TXLSXWorkbook-luokkaan suoraan. Huomaa kahden rajapinnan elinkaariero ennen kuin kopioit yhtäkään esimerkkiä. XLS-puolen TXLSWorkbook pidetään viitelasketun rajapinnan kautta eikä sitä saa vapauttaa käsin, kun taas TXLSXWorkbook on tavallinen luokka, joka vaatii try..finally Free -rakenteen. Näiden käytäntöjen sekoittaminen on luotettava tapa tuottaa joko muistivuoto tai kaksoisvapautus

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;  // suora arkin XML:n virrallistus zipiin
    Book.SaveAs(FileName);
  finally
    Book.Free;
  end;
end;

Tärkeät rivit ovat tyypitetyt sijoitukset ja IsNull-suojaus. Päivämäärät saapuvat päivämääräsarjoina, summat kaksoistarkkuuslukuina ja NULL-tilauspäivämäärät jäävät aidosti tyhjiksi sen sijaan, että niistä tulisi tyhjiä merkkijonoja. StreamingWrite := True muuttaa vain tallennusreittiä: laskentataulukon XML suoratoistetaan suoraan zip-säiliöön sen sijaan, että se koottaisiin ensin yhdeksi suureksi merkkijonoksi. Tämä tasoittaa SaveAs-vaiheen muistipiikin kuusinumeroisilla rivimäärillä. Jokaisella tallennusmenetelmällä on myös TStream-ylikuormitus, joten työkirja voidaan lähettää suoraan HTTP-vastaukseen koskematta levyyn. Suoratoistettua kirjoitusta ja eräajoja käsittelevä artikkeli käy tämän käyttöönottomallin läpi, ja suurten työkirjojen suorituskykyä käsittelevä artikkeli selittää, mitä tehdä rivimäärien kasvaessa edelleen

Tämä silmukka on myös reitti, joka skaalautuu säikeiden välillä. Molemmat moottorit ovat natiiveja Object Pascal -kirjoittajia, toisella puolella BIFF8-tietuevirrat ja toisella OOXML zip plus XML, joten mikään viennin osa ei koske COM-automaatiota eikä tarvitse Excel-lisenssiä palvelimella. Saat rinnakkaisuuden ilman yksittäisen instanssin pullonkaulaa, kunhan jokainen säie rakentaa oman työkirjansa. Työkirjaobjektit eivät ole säieturvallisia jaettuun käyttöön, joten sääntö on yksi instanssi vientiä kohden, ei koskaan lukolla suojattu yhteinen instanssi

Yksi raja kannattaa tietää ennen sen ympärille suunnittelua. XLSX-ruudukko päättyy 1 048 576 riviin ja 16 384 sarakkeeseen, joten XLS-puolella RowsPerSheet-ominaisuuden hoitamaa arkkijakoa tarvitaan täällä harvoin. Miljoonan rivin työkirja on harvoin myöskään ihmiskäyttäjän toive. Kun tulosjoukko on aidosti niin suuri, erotinmerkeillä eroteltu tiedosto on yleensä parempi sopimus, ja CSV- ja TSV-vientiä käsittelevä artikkeli kattaa erottimet, BOM-käyttäytymisen ja siellä sovellettavan kaavojen arvioinnin huomautuksen

Lähtökohdan valitseminen

Jos vienti on VCL-työpöytätyökalussa ja .xls-tuloste kelpaa, aloita TDataToXLS-komponentista ja sen ryhmittelytuesta. Se vaatii vähiten koodia, ja silta SaveXLSWorkbookAsXLSX-kutsun kautta on käytettävissä, kun joku myöhemmin pyytää .xlsx-muotoa, kunhan hyväksyt jo kuvatut tarkkuusrajoitukset. Jos koodi suoritetaan ilman valvontaa tai vastaanottaja vaatii .xlsx-muotoa alusta asti, kirjoita silmukka. Molemmat reitit toimitetaan toimivien demoprojektien kanssa ja ne kuuluvat HotXLS Delphi Component -pakettiin