Eksportere Delphi-databaseresultater til Excel-rapporter med HotXLS

Å gjøre om et spørringsresultat til en Excel-rapport er tre problemer i én frakk. Hver Delphi-felttype må lande i en celle som riktig Excel-type, overskriftsraden må lese som en rapport i stedet for en skjemadump, og tall, datoer og penger må bære formater som overlever turen. Hopp over ett eneste av dem, og filen åpnes fortsatt, ser fortsatt plausibel ut, og feiler fortsatt i det øyeblikket en finansbruker velger en kolonne og venter på en sum som aldri dukker opp. Verdiene ble skrevet som tekst, Excel behandler dem som etiketter, og aldri ble noe unntak utløst for å advare deg

HotXLS er et nativt Object Pascal-regnearkbibliotek som skriver XLS- og XLSX-filer direkte fra Delphi og C++Builder, uten Excel-automatisering involvert. Det tilbyr to veier fra et TDataset til en arbeidsbok: den ferdigpakkede TDataToXLS-komponenten, og en håndskrevet løkke mot arbeidsbok-API-et. De er ikke utskiftbare. Komponenten er en VCL-borger bygget på XLS-fasaden, så det riktige valget avhenger av hvor koden kjører og hvilket filformat konsumenten forventer. Det som følger, er begge veiene, grensen der komponenten slutter å være riktig verktøy, og hvordan du holder felttypene intakte uansett hvilken du velger

Felttyper er den egentlige eksportkontrakten

Før noe API-kall, bestem hvordan hver Delphi-felttype lander i en celle. En celle som mottar en Delphi-streng, forblir en streng. HotXLS gjetter ikke at '1,234.50' var ment å være et tall, og det bør den ikke, fordi lokalitetsavhengig gjentolkning er nøyaktig slik et tysk desimalkomma blir et tusenskilletegn på en engelsk server. Det pålitelige mønsteret er å tildele gjennom de typede tilgangsmetodene: AsFloat eller AsCurrency for numeriske felt, AsDateTime for datoer slik at cellen holder et ekte Excel-datoserienummer i stedet for en formatert streng, og AsString bare for felt som faktisk er tekst

Nullhåndtering fortjener en eksplisitt beslutning i stedet for en standard. Å konvertere en feltverdi med VarToStr gjør SQL NULL om til en tom streng, som er en tekstcelle, mens det å hoppe over tildelingen lar cellen bli virkelig tom, som er det AVERAGE, COUNT og pivottabellkonsumenter forventer. For pengekolonner, bestem før løkken er skrevet om NULL betyr null eller ukjent. De to gjengis identisk så snart noen formaterer kolonnen, og forskjellen endrer hvert eneste aggregat beregnet nedstrøms

Komponentveien: TDataToXLS i VCL-applikasjoner

For en klassisk VCL-applikasjon med en spørring allerede koblet inn i en datamodul, er TDataToXLS ett-kalls-veien. Den går gjennom enhver TDataset-etterkommer, enten FireDAC, ADO, IBX, eller noe annet som implementerer det abstrakte datasettgrensesnittet, og produserer et stilert regneark med overskriftstekster, fonter, kanter, valgfrie gruppesummer og automatisk arkdeling for store resultatsett

var
  Exporter: TDataToXLS;
begin
  Exporter := TDataToXLS.Create(nil);
  try
    Exporter.Dataset := OrdersQuery;          // enhver TDataset-etterkommer
    Exporter.WorksheetName := 'Orders';
    Exporter.HeaderSource := hsDisplayLabel;  // tekster, ikke rå kolonnenavn
    Exporter.GroupFields.Add('CustomerID');   // delsumblokk per kunde
    Exporter.RowsPerSheet := 50000;           // hold deg under BIFF8-radtaket
    Exporter.VisibleFieldsOnly := True;             // respekter Field.Visible
    Exporter.SaveDatasetAs('orders.xls');
  finally
    Exporter.Free;
  end;
end;

To egenskaper bærer mesteparten av produksjonsvekten her. HeaderSource := hsDisplayLabel skriver hvert felts DisplayLabel i stedet for det rå SQL-kolonnenavnet, så arbeidsboken sier «Customer Name» fremfor CUST_NM. RowsPerSheet finnes fordi komponenten skriver BIFF8, hvis rutenett stopper ved 65 536 rader ganger 256 kolonner; å sette den til 50 000 deler et stort resultatsett over ark før formattaket kutter det. Utseendet håndteres av egenskapene HeaderFont, DetailFont, GroupColor og kantstil, og DisableFormat-settet slår av hele formateringskategorier når konsumenten vil ha vanlige celler. For alt skreddersydd gir AfterCell- og AfterRow-hendelsene deg det nettopp skrevne området for etterbehandling

Der komponenten stopper

Tre begrensninger er designet inn i TDataToXLS, og å kjenne dem på forhånd unngår en klosset omdesign to sprinter senere

• Det er en VCL-komponent i full forstand. Enheten dens drar inn Forms, Controls og Dialogs, så å lenke den inn i en konsolljobb eller en Windows-tjeneste drar VCL-en inn i binærfilen. Kjerneenhetene for arbeidsboken har ingen slik avhengighet. De trenger bare Windows, Classes, SysUtils og Variants, som er hvorfor serverkode heller bør bruke løkken vist nedenfor
• Den er bygget på XLS-fasaden. Komponenten fyller ut et IXLSWorkbook og skriver .xls (BIFF8). Det finnes ingen egenskap som bytter den til OOXML-utdata
• Hendelsene dens snakker XLS-dialekten. Cell: IXLSRange-parameteren i AfterCell tilhører XLS-objektmodellen, så per-celle-tilpasning skrevet der er XLS-stilkode selv om filen konverteres til .xlsx etterpå

Å produsere .xlsx fra komponentens utdata

Når konsumenten insisterer på .xlsx, men eksportlogikken allerede bor i TDataToXLS, konverterer brofunksjonen i lxXlsxExport-enheten den utfylte arbeidsboken i ett kall:

uses lxXlsxExport;

Exporter.SaveDatasetAs('orders.xls');
// komponenten eksponerer IXLSWorkbook-en den fylte ut
SaveXLSWorkbookAsXLSX(Exporter.Workbook, 'orders.xlsx');

Behandle broen som en bærer av tabelldata, ikke en konverterer med full trofasthet. Den kopierer verdier, formler, tallformater, fyllfarger, fontattributter, kolonnebredder og visningsinnstillinger. Den kopierer bevisst ikke kanter, sammenslåtte områder, kommentarer, diagrammer eller betinget formatering. For et flatt rutenett av overskrift pluss rader er det akkurat nok. For en stilert rapport er det ikke det, og den ærlige løsningen er å generere XLSX-en direkte i stedet for å lappe den konverterte filen

Den håndskrevne løkken for tjenester og batchjobber

Serverkode bør sikte mot TXLSXWorkbook direkte. Legg merke til levetidsforskjellen mellom de to fasadene før du kopierer noe eksempel. XLS-sidens TXLSWorkbook holdes gjennom et referansetalt grensesnitt og må ikke frigjøres manuelt, mens TXLSXWorkbook er en vanlig klasse som krever try..finally Free. Å blande de to konvensjonene er en pålitelig måte å skape enten en lekkasje eller en dobbelfrigjøring 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;  // strømmer arkets XML rett inn i zip-filen
    Book.SaveAs(FileName);
  finally
    Book.Free;
  end;
end;

Linjene som betyr noe, er de typede tildelingene og IsNull-vakten. Datoer ankommer som datoserienumre, beløp ankommer som doubler, og NULL-ordredatoer forblir virkelig tomme i stedet for å bli tomme strenger. StreamingWrite := True endrer bare lagringsstien: regnearkets XML strømmes rett inn i zip-beholderen i stedet for å settes sammen som én stor streng først, noe som flater ut minnetoppen ved SaveAs-tidspunktet for radantall på seks sifre. Hver lagringsmetode har også en TStream-overbelastning, så arbeidsboken kan gå rett inn i et HTTP-svar uten å røre disken. Artikkelen om strømmeskriving og batchjobber går gjennom det utrullingsmønsteret, og artikkelen om ytelse for store arbeidsbøker dekker hva du skal gjøre når radantallet stiger videre

Denne løkken er også veien som skalerer på tvers av tråder. Begge motorene er native Object Pascal-skrivere, BIFF8-poststrømmer på den ene siden og OOXML-zip pluss XML på den andre, så ingen del av en eksport berører COM-automatisering eller trenger en Excel-lisens på serveren. Det det kjøper deg, er parallellitet uten en enkeltinstans-flaskehals, forutsatt at hver tråd bygger sin egen arbeidsbok. Arbeidsbokobjektene er ikke trådsikre for delt bruk, så regelen er én instans per eksport, aldri en delt en voktet av en lås

Én grense er verdt å kjenne før du designer rundt den. XLSX-rutenettet stopper ved 1 048 576 rader ganger 16 384 kolonner, så arkdelingen som RowsPerSheet håndterer på XLS-siden, trengs sjelden her. En arbeidsbok på en million rader er sjelden det en menneskelig konsument vil ha heller. Når resultatsettet virkelig er så stort, er en avgrenset fil vanligvis den bedre kontrakten, og artikkelen om CSV- og TSV-eksport dekker skilletegn, BOM-oppførsel og formelevalueringsforbeholdet som gjelder der

Å velge et startpunkt

Hvis eksporten bor i et VCL-skrivebordsverktøy og .xls-utdata er akseptabelt, start med TDataToXLS og gruppestøtten dens. Det er minst kode, og broen gjennom SaveXLSWorkbookAsXLSX er der når noen senere ber om .xlsx, så lenge du aksepterer trofasthetsgrensene allerede beskrevet. Hvis koden kjører uten tilsyn, eller konsumenten krever .xlsx fra starten av, skriv løkken. Begge veiene leveres med fungerende demoprosjekter og er en del av HotXLS Delphi Component-pakken