Eksport af Delphi-databaseresultater til Excel-rapporter med HotXLS

At omdanne et forespørgselsresultat til en Excel-rapport er tre problemer i én frakke. Hver Delphi-felttype skal lande i en celle som den rigtige Excel-type, overskriftsrækken skal læses som en rapport i stedet for et skemadump, og tal, datoer og penge skal bære formater, der overlever turen. Springer man et eneste af dem over, åbner filen stadig, ser stadig plausibel ud og fejler stadig, i det øjeblik en finansbruger markerer en kolonne og venter på en sum, der aldrig dukker op. Værdierne blev skrevet som tekst, Excel behandler dem som etiketter, og der blev aldrig kastet nogen undtagelse for at advare dig

HotXLS er et nativt Object Pascal-regnearksbibliotek, der skriver XLS- og XLSX-filer direkte fra Delphi og C++Builder, uden nogen Excel-automatisering involveret. Det tilbyder to veje fra et TDataset til en projektmappe: den indstiksfærdige TDataToXLS-komponent og en håndskrevet løkke mod projektmappe-API'et. De er ikke ombyttelige. Komponenten er en VCL-borger bygget på XLS-facaden, så det rigtige valg afhænger af, hvor koden kører, og hvilket filformat forbrugeren forventer. I det følgende gennemgås begge veje, grænsen hvor komponenten ophører med at være det rigtige værktøj, og hvordan man holder felttyperne intakte, uanset hvilken man vælger

Felttyper er den egentlige eksportkontrakt

Før noget API-kald skal man beslutte, hvordan hver Delphi-felttype lander i en celle. En celle, der modtager en Delphi-streng, forbliver en streng. HotXLS gætter ikke på, at '1,234.50' var ment som et tal, og det bør den heller ikke, fordi locale-afhængig genfortolkning er præcis, hvordan et tysk decimalkomma bliver til en tusindtalsseparator på en engelsk server. Det pålidelige mønster er at tildele gennem de typede accessorer: AsFloat eller AsCurrency til numeriske felter, AsDateTime til datoer, så cellen indeholder et ægte Excel-dato-serienummer frem for en formateret streng, og AsString kun til felter, der faktisk er tekst

Håndtering af null fortjener en eksplicit beslutning frem for en standard. At konvertere en feltværdi med VarToStr gør SQL NULL til en tom streng, hvilket er en tekstcelle, mens man ved at springe tildelingen over lader cellen være reelt tom, hvilket er, hvad AVERAGE, COUNT og pivottabel-forbrugere forventer. For pengekolonner skal man beslutte, før løkken skrives, om NULL betyder nul eller ukendt. De to gengives identisk, så snart nogen formaterer kolonnen, og forskellen ændrer hvert eneste aggregat, der beregnes længere nede i kæden

Komponentvejen: TDataToXLS i VCL-applikationer

For en klassisk VCL-applikation med en forespørgsel allerede tilkoblet et datamodul er TDataToXLS ét-kalds-vejen. Den gennemløber enhver TDataset-efterkommer, hvad enten det er FireDAC, ADO, IBX eller noget andet, der implementerer den abstrakte dataset-grænseflade, og producerer et stylet regneark med overskriftstekster, skrifttyper, kanter, valgfrie gruppe-subtotaler og automatisk arkopdeling til store resultatsæt

var
  Exporter: TDataToXLS;
begin
  Exporter := TDataToXLS.Create(nil);
  try
    Exporter.Dataset := OrdersQuery;          // enhver TDataset-efterkommer
    Exporter.WorksheetName := 'Orders';
    Exporter.HeaderSource := hsDisplayLabel;  // overskriftstekster, ikke rå kolonnenavne
    Exporter.GroupFields.Add('CustomerID');   // subtotal-blok pr. kunde
    Exporter.RowsPerSheet := 50000;           // hold dig under BIFF8's rækkeloft
    Exporter.VisibleFieldsOnly := True;             // respekter Field.Visible
    Exporter.SaveDatasetAs('orders.xls');
  finally
    Exporter.Free;
  end;
end;

To egenskaber bærer det meste af den produktionsmæssige vægt her. HeaderSource := hsDisplayLabel skriver hvert felts DisplayLabel i stedet for det rå SQL-kolonnenavn, så projektmappen siger "Customer Name" frem for CUST_NM. RowsPerSheet findes, fordi komponenten skriver BIFF8, hvis gitter stopper ved 65.536 rækker gange 256 kolonner; at sætte den til 50.000 opdeler et stort resultatsæt på tværs af ark, før formatloftet afskærer det. Udseendet håndteres af egenskaberne HeaderFont, DetailFont, GroupColor og kantstil, og DisableFormat-sættet slår hele formateringskategorier fra, når forbrugeren vil have almindelige celler. Til alt skræddersyet giver AfterCell- og AfterRow-hændelserne dig det netop skrevne område til efterbehandling

Hvor komponenten stopper

Tre begrænsninger er designet ind i TDataToXLS, og at kende dem på forhånd undgår et akavet redesign to sprints senere

• Den er en VCL-komponent i fuld forstand. Dens unit trækker Forms, Controls og Dialogs med sig, så det at linke den ind i et konsoljob eller en Windows-tjeneste trækker VCL med ind i den binære fil. Kerne-projektmappe-unittene har ingen sådan afhængighed. De behøver kun Windows, Classes, SysUtils og Variants, hvilket er grunden til, at server-side-kode i stedet bør bruge løkken vist nedenfor
• Den er bygget på XLS-facaden. Komponenten udfylder et IXLSWorkbook og skriver .xls (BIFF8). Der findes ingen egenskab, der skifter den til OOXML-output
• Dens hændelser taler XLS-dialekten. Parameteren Cell: IXLSRange i AfterCell hører til XLS-objektmodellen, så per-celle-tilpasning skrevet dér er XLS-stil kode, selv hvis filen konverteres til .xlsx bagefter

At producere .xlsx fra komponentens output

Når forbrugeren insisterer på .xlsx, men eksportlogikken allerede bor i TDataToXLS, konverterer bro-funktionen i lxXlsxExport-unitten den udfyldte projektmappe med ét kald:

uses lxXlsxExport;

Exporter.SaveDatasetAs('orders.xls');
// komponenten eksponerer det IXLSWorkbook, den udfyldte
SaveXLSWorkbookAsXLSX(Exporter.Workbook, 'orders.xlsx');

Behandl broen som en transportør af tabeldata, ikke en fuld-troskabs-konverter. Den kopierer værdier, formler, taltformater, udfyldningsfarver, skriftattributter, kolonnebredder og visningsindstillinger. Den kopierer bevidst ikke kanter, sammenlagte områder, kommentarer, diagrammer eller betinget formatering. Til et fladt gitter af overskrift plus rækker er det præcis nok. Til en stylet rapport er det ikke, og den ærlige løsning er at generere XLSX'en direkte i stedet for at lappe den konverterede fil

Den håndskrevne løkke til tjenester og batch-job

Server-side-kode bør målrette TXLSXWorkbook direkte. Bemærk levetidsforskellen mellem de to facader, før du kopierer noget eksempel. XLS-sidens TXLSWorkbook holdes gennem en referencetalt grænseflade og må ikke frigøres manuelt, mens TXLSXWorkbook er en almindelig klasse, der kræver try..finally Free. At blande de to konventioner er en pålidelig måde at fremstille enten en lækage eller en double-free på

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 ark-XML direkte ind i zip'en
    Book.SaveAs(FileName);
  finally
    Book.Free;
  end;
end;

De linjer, der betyder noget, er de typede tildelinger og IsNull-vagten. Datoer ankommer som dato-serienumre, beløb ankommer som doubles, og NULL-ordredatoer forbliver reelt tomme i stedet for at blive til tomme strenge. StreamingWrite := True ændrer kun gemmestien: ark-XML strømmer direkte ind i zip-beholderen i stedet for først at blive samlet som én stor streng, hvilket flader hukommelsesspidsen ved SaveAs-tidspunktet for rækketal med seks cifre. Hver gemmemetode har også et TStream-overload, så projektmappen kan gå direkte ind i et HTTP-svar uden at røre disken. Artiklen om streaming-skrivning og batch-job gennemgår det udrulningsmønster, og artiklen om ydeevne for store projektmapper dækker, hvad man skal gøre, når rækketallene stiger yderligere

Denne løkke er også den vej, der skalerer på tværs af tråde. Begge motorer er native Object Pascal-skrivere, BIFF8-poststrømme på den ene side og OOXML-zip plus XML på den anden, så ingen del af en eksport rører COM-automatisering eller kræver en Excel-licens på serveren. Det, det køber dig, er parallelisme uden en enkelt-instans-flaskehals, forudsat at hver tråd bygger sin egen projektmappe. Projektmappeobjekterne er ikke trådsikre til delt brug, så reglen er én instans pr. eksport, aldrig en delt én bevogtet af en lås

Én grænse er værd at kende, før du designer omkring den. XLSX-gitteret stopper ved 1.048.576 rækker gange 16.384 kolonner, så den arkopdeling, som RowsPerSheet håndterer på XLS-siden, er sjældent nødvendig her. En projektmappe med en million rækker er også sjældent, hvad en menneskelig forbruger ønsker. Når resultatsættet virkelig er så stort, er en afgrænset fil normalt den bedre kontrakt, og artiklen om CSV- og TSV-eksport dækker afgrænsere, BOM-adfærd og det forbehold om formelevaluering, der gælder dér

At vælge et udgangspunkt

Hvis eksporten bor i et VCL-desktopværktøj, og .xls-output er acceptabelt, så start med TDataToXLS og dens grupperingsstøtte. Det er den mindste kode, og broen via SaveXLSWorkbookAsXLSX er der, når nogen senere beder om .xlsx, så længe du accepterer de troskabsgrænser, der allerede er beskrevet. Hvis koden kører uovervåget, eller forbrugeren kræver .xlsx fra start, så skriv løkken. Begge veje leveres med fungerende demoprojekter og er en del af pakken HotXLS-Delphi-komponenten