Exportul rezultatelor bazelor de date Delphi în rapoarte Excel cu HotXLS

Transformarea rezultatului unei interogări într-un raport Excel înseamnă trei probleme îmbrăcate în aceeași haină. Fiecare tip de câmp Delphi trebuie să aterizeze într-o celulă cu tipul Excel potrivit, rândul de antet trebuie să se citească a raport, nu a listare de schemă, iar numerele, datele calendaristice și sumele de bani trebuie să poarte formate care supraviețuiesc drumului. Sari peste oricare dintre ele și fișierul tot se deschide, tot arată plauzibil și tot cade în clipa în care un utilizator din finanțe selectează o coloană și așteaptă o sumă care nu apare niciodată. Valorile au fost scrise ca text, Excel le tratează drept etichete și nu s-a ridicat niciodată vreo excepție care să te avertizeze

HotXLS este o bibliotecă nativă de foi de calcul scrisă în Object Pascal, care produce fișiere XLS și XLSX direct din Delphi și C++Builder, fără nicio automatizare Excel la mijloc. Oferă două drumuri de la un TDataset la un registru de lucru: componenta TDataToXLS, gata de folosit, și o buclă scrisă de mână peste API-ul registrului de lucru. Nu sunt interschimbabile. Componenta este un cetățean VCL construit pe fațada XLS, așa că alegerea potrivită depinde de locul în care rulează codul și de formatul de fișier pe care îl așteaptă consumatorul. Ce urmează sunt ambele drumuri, linia de la care componenta încetează să mai fie unealta potrivită și felul în care păstrezi tipurile de câmp intacte, indiferent ce alegi

Tipurile de câmp sunt adevăratul contract de export

Înainte de orice apel de API, hotărăște cum aterizează într-o celulă fiecare tip de câmp Delphi. O celulă care primește un șir Delphi rămâne șir. HotXLS nu ghicește că '1,234.50' era menit să fie un număr, și nici nu ar trebui să ghicească, pentru că reanalizarea dependentă de locale este exact felul în care o virgulă zecimală germană se preface în separator de mii pe un server englezesc. Tiparul de încredere este atribuirea prin accesorii tipizați: AsFloat sau AsCurrency pentru câmpurile numerice, AsDateTime pentru date, ca celula să țină un serial de dată Excel autentic, nu un șir formatat, și AsString doar pentru câmpurile care chiar sunt text

Tratarea valorilor null merită o decizie explicită, nu una implicită. Convertirea unei valori de câmp cu VarToStr preface NULL din SQL într-un șir gol, adică într-o celulă de text, în timp ce sărirea peste atribuire lasă celula cu adevărat goală, adică exact ce așteaptă AVERAGE, COUNT și consumatorii de tabele pivot. Pentru coloanele de bani, hotărăște înainte să fie scrisă bucla dacă NULL înseamnă zero sau necunoscut. Cele două se redau identic din clipa în care cineva formatează coloana, iar diferența schimbă orice agregat calculat în aval

Drumul cu componentă: TDataToXLS în aplicații VCL

Pentru o aplicație VCL clasică, cu o interogare deja cablată într-un modul de date, TDataToXLS este drumul dintr-un singur apel. Parcurge orice descendent de TDataset, fie el FireDAC, ADO, IBX sau orice altceva care implementează interfața abstractă de set de date, și produce o foaie de calcul stilizată, cu titluri de antet, fonturi, borduri, subtotaluri opționale pe grupuri și împărțire automată pe foi pentru seturile mari de rezultate

var
  Exporter: TDataToXLS;
begin
  Exporter := TDataToXLS.Create(nil);
  try
    Exporter.Dataset := OrdersQuery;          // orice descendent de TDataset
    Exporter.WorksheetName := 'Orders';
    Exporter.HeaderSource := hsDisplayLabel;  // titluri, nu nume brute de coloană
    Exporter.GroupFields.Add('CustomerID');   // bloc de subtotal pentru fiecare client
    Exporter.RowsPerSheet := 50000;           // rămâi sub plafonul de rânduri BIFF8
    Exporter.VisibleFieldsOnly := True;             // respectă Field.Visible
    Exporter.SaveDatasetAs('orders.xls');
  finally
    Exporter.Free;
  end;
end;

Două proprietăți duc aici cea mai mare parte din greutatea de producție. HeaderSource := hsDisplayLabel scrie DisplayLabel al fiecărui câmp în loc de numele brut al coloanei SQL, așa că registrul de lucru spune „Customer Name”, nu CUST_NM. RowsPerSheet există pentru că această componentă scrie BIFF8, a cărui grilă se oprește la 65.536 de rânduri pe 256 de coloane; setarea ei la 50.000 împarte un set mare de rezultate pe mai multe foi înainte ca plafonul formatului să îl reteze. Aspectul este tratat de proprietățile HeaderFont, DetailFont, GroupColor și de cele de stil al bordurii, iar mulțimea DisableFormat stinge categorii întregi de formatare atunci când consumatorul vrea celule simple. Pentru orice este croit pe comandă, evenimentele AfterCell și AfterRow îți predau intervalul tocmai scris, pentru prelucrare ulterioară

Unde se oprește componenta

Trei constrângeri sunt proiectate în TDataToXLS, iar cunoașterea lor din capul locului scutește de o reproiectare stânjenitoare peste două sprinturi

• Este o componentă VCL în sensul deplin. Unitatea ei trage după sine Forms, Controls și Dialogs, așa că legarea ei într-un job de consolă sau într-un serviciu Windows târăște VCL în binar. Unitățile de bază ale registrului de lucru nu au o asemenea dependență. Le trebuie doar Windows, Classes, SysUtils și Variants, motiv pentru care codul de pe server ar trebui să folosească bucla arătată mai jos
• Este construită pe fațada XLS. Componenta populează un IXLSWorkbook și scrie .xls (BIFF8). Nu există nicio proprietate care să o comute pe ieșire OOXML
• Evenimentele ei vorbesc dialectul XLS. Parametrul Cell: IXLSRange din AfterCell aparține modelului de obiecte XLS, așa că personalizarea per celulă scrisă acolo este cod în stil XLS, chiar dacă fișierul este convertit după aceea în .xlsx

Producerea de .xlsx din rezultatul componentei

Când consumatorul insistă pe .xlsx, dar logica de export trăiește deja în TDataToXLS, funcția-punte din unitatea lxXlsxExport convertește registrul de lucru populat dintr-un singur apel:

uses lxXlsxExport;

Exporter.SaveDatasetAs('orders.xls');
// componenta expune IXLSWorkbook-ul pe care l-a populat
SaveXLSWorkbookAsXLSX(Exporter.Workbook, 'orders.xlsx');

Tratează puntea ca pe un transportor de date tabelare, nu ca pe un convertor de fidelitate deplină. Copiază valori, formule, formate de număr, culori de umplere, atribute de font, lățimi de coloană și setări de vizualizare. Nu copiază, în mod deliberat, borduri, intervale îmbinate, comentarii, grafice sau formate condiționate. Pentru o grilă plată, de antet plus rânduri, atât este exact suficient. Pentru un raport stilizat nu este, iar remediul cinstit este să generezi XLSX-ul direct, nu să peticești fișierul convertit

Bucla scrisă de mână pentru servicii și joburi în lot

Codul de pe server ar trebui să țintească direct TXLSXWorkbook. Ia aminte la diferența de durată de viață dintre cele două fațade înainte să copiezi vreun exemplu. TXLSWorkbook din partea XLS este ținut printr-o interfață numărată prin referință și nu trebuie eliberat manual, în timp ce TXLSXWorkbook este o clasă obișnuită, care cere try..finally Free. Amestecarea celor două convenții este o rețetă sigură pentru a fabrica fie o scurgere de memorie, fie o dublă eliberare

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;  // trimite XML-ul foii direct în arhiva zip
    Book.SaveAs(FileName);
  finally
    Book.Free;
  end;
end;

Liniile care contează sunt atribuirile tipizate și garda IsNull. Datele calendaristice sosesc ca seriale de dată, sumele sosesc ca valori double, iar datele de comandă NULL rămân cu adevărat goale, în loc să devină șiruri goale. StreamingWrite := True schimbă doar calea de salvare: XML-ul foii de calcul curge direct în containerul zip, în loc să fie asamblat mai întâi ca un singur șir mare, ceea ce aplatizează vârful de memorie din momentul SaveAs la numere de rânduri de ordinul sutelor de mii. Fiecare metodă de salvare are și o supraîncărcare pe TStream, așa că registrul de lucru poate merge direct într-un răspuns HTTP fără să atingă discul. Articolul despre scrierea în flux și joburile în lot parcurge tiparul acela de instalare, iar articolul despre performanța registrelor de lucru mari tratează ce ai de făcut când numărul de rânduri urcă și mai sus

Bucla aceasta este și drumul care scalează pe mai multe fire de execuție. Ambele motoare sunt scriitoare native Object Pascal, fluxuri de înregistrări BIFF8 pe o parte, zip plus XML OOXML pe cealaltă, așa că nicio parte a unui export nu atinge automatizarea COM și nu are nevoie de o licență Excel pe server. Ce câștigi din asta este paralelism fără un gât de sticlă cu instanță unică, cu condiția ca fiecare fir să își construiască propriul registru de lucru. Obiectele registru de lucru nu sunt sigure pentru folosire partajată între fire, așa că regula este câte o instanță pentru fiecare export, niciodată una partajată și păzită de un lacăt

O limită merită știută înainte să proiectezi în jurul ei. Grila XLSX se oprește la 1.048.576 de rânduri pe 16.384 de coloane, așa că împărțirea pe foi de care se ocupă RowsPerSheet în partea XLS este rareori necesară aici. Un registru de lucru de un milion de rânduri este oricum rareori ce vrea un consumator uman. Când setul de rezultate chiar este atât de mare, un fișier delimitat este de obicei contractul mai bun, iar articolul despre exportul CSV și TSV tratează delimitatorii, comportamentul BOM și avertismentul privind evaluarea formulelor care se aplică acolo

Cum alegi punctul de plecare

Dacă exportul trăiește într-o unealtă VCL de desktop și ieșirea .xls este acceptabilă, pornește de la TDataToXLS și de la suportul lui pentru grupare. Este cel mai puțin cod, iar puntea prin SaveXLSWorkbookAsXLSX stă la dispoziție când cineva cere mai târziu .xlsx, atâta vreme cât accepți limitele de fidelitate deja descrise. Dacă rulează cod nesupravegheat sau dacă consumatorul cere .xlsx de la bun început, scrie bucla. Ambele drumuri vin cu proiecte demonstrative funcționale și fac parte din pachetul HotXLS Delphi Component