HotXLS: database export to spreadsheet reports in Delphi

Een queryresultaat omzetten in een Excel-rapport is drie problemen in één jas. Elk Delphi-veldtype moet in een cel terechtkomen als het juiste Excel-type, de kopregel moet lezen als een rapport in plaats van een schema-dump, en getallen, datums en bedragen moeten opmaak dragen die de tocht overleeft. Sla er ook maar één over en het bestand gaat nog steeds open, ziet er nog steeds plausibel uit, en faalt nog steeds op het moment dat een financieel gebruiker een kolom selecteert en wacht op een som die nooit verschijnt. De waarden waren als tekst geschreven, Excel behandelt ze als labels, en er werd nooit een uitzondering opgeworpen om je te waarschuwen

HotXLS is een native Object Pascal-spreadsheetbibliotheek die XLS- en XLSX-bestanden rechtstreeks vanuit Delphi en C++Builder schrijft, zonder Excel-automatisering. Het biedt twee routes van een TDataset naar een werkmap: de kant-en-klare TDataToXLS-component, en een handgeschreven lus tegen de workbook-API. Ze zijn niet uitwisselbaar. De component is een volwaardig VCL-onderdeel gebouwd op de XLS-facade, dus de juiste keuze hangt af van waar de code draait en welk bestandsformaat de afnemer verwacht. Hierna volgen beide routes, de grens waar de component ophoudt het juiste gereedschap te zijn, en hoe je veldtypen intact houdt welke je ook kiest

Veldtypen zijn het echte exportcontract

Bepaal vóór elke API-aanroep hoe elk Delphi-veldtype in een cel terechtkomt. Een cel die een Delphi-string ontvangt, blijft een string. HotXLS raadt niet dat '1,234.50' een getal moest zijn, en dat moet het ook niet doen, want locale-afhankelijk herinterpreteren is precies hoe een Duitse decimale komma op een Engelse server verandert in een duizendtalscheidingsteken. Het betrouwbare patroon is toewijzen via de getypeerde accessors: AsFloat of AsCurrency voor numerieke velden, AsDateTime voor datums zodat de cel een echt Excel-datumserienummer bevat in plaats van een opgemaakte string, en AsString alleen voor velden die daadwerkelijk tekst zijn

Null-afhandeling verdient een expliciete beslissing in plaats van een standaardkeuze. Het omzetten van een veldwaarde met VarToStr maakt van SQL NULL een lege string, wat een tekstcel is, terwijl het overslaan van de toewijzing de cel écht leeg laat, wat is wat AVERAGE, COUNT en draaitabel-consumenten verwachten. Bepaal voor geldkolommen, voordat de lus wordt geschreven, of NULL nul of onbekend betekent. Beide zien er identiek uit zodra iemand de kolom opmaakt, en het verschil verandert elk aggregaat dat verderop wordt berekend

De componentroute: TDataToXLS in VCL-toepassingen

Voor een klassieke VCL-toepassing met een query die al is aangesloten op een datamodule, is TDataToXLS de route met één aanroep. Het doorloopt elke TDataset-afstammeling, of het nu FireDAC, ADO, IBX of iets anders is dat de abstracte dataset-interface implementeert, en produceert een opgemaakt werkblad met kopbijschriften, lettertypen, randen, optionele groepssubtotalen en automatische bladsplitsing voor grote resultaatsets

var
  Exporter: TDataToXLS;
begin
  Exporter := TDataToXLS.Create(nil);
  try
    Exporter.Dataset := OrdersQuery;          // elke TDataset-afstammeling
    Exporter.WorksheetName := 'Orders';
    Exporter.HeaderSource := hsDisplayLabel;  // bijschriften, geen ruwe kolomnamen
    Exporter.GroupFields.Add('CustomerID');   // subtotaalblok per klant
    Exporter.RowsPerSheet := 50000;           // blijf onder de BIFF8-rijlimiet
    Exporter.VisibleFieldsOnly := True;             // respecteer Field.Visible
    Exporter.SaveDatasetAs('orders.xls');
  finally
    Exporter.Free;
  end;
end;

Twee eigenschappen dragen hier het meeste productiegewicht. HeaderSource := hsDisplayLabel schrijft het DisplayLabel van elk veld in plaats van de ruwe SQL-kolomnaam, zodat de werkmap "Customer Name" zegt in plaats van CUST_NM. RowsPerSheet bestaat omdat de component BIFF8 schrijft, waarvan het raster stopt bij 65.536 rijen bij 256 kolommen; door het in te stellen op 50.000 wordt een grote resultaatset over bladen verdeeld voordat de formaatlimiet deze afkapt. Het uiterlijk wordt geregeld door de eigenschappen HeaderFont, DetailFont, GroupColor en randstijl, en de DisableFormat-set schakelt hele opmaakcategorieën uit wanneer de afnemer platte cellen wil. Voor alles op maat geven de events AfterCell en AfterRow je het zojuist geschreven bereik voor nabewerking

Waar de component ophoudt

Drie beperkingen zitten ingebouwd in TDataToXLS, en als je ze vooraf kent, voorkom je een ongemakkelijk herontwerp twee sprints later

• Het is een volwaardige VCL-component. De unit trekt Forms, Controls en Dialogs binnen, dus het koppelen ervan aan een consoletaak of Windows-service sleept de VCL mee in de binary. De kern-workbookunits hebben die afhankelijkheid niet. Ze hebben alleen Windows, Classes, SysUtils en Variants nodig, en daarom moet server-side code in plaats daarvan de lus hieronder gebruiken
• Hij is gebouwd op de XLS-facade. De component vult een IXLSWorkbook en schrijft .xls (BIFF8). Er is geen eigenschap die hem omschakelt naar OOXML-uitvoer
• De events spreken het XLS-dialect. De parameter Cell: IXLSRange in AfterCell hoort bij het XLS-objectmodel, dus per-cel-aanpassingen die daar worden geschreven zijn XLS-stijlcode, zelfs als het bestand daarna wordt geconverteerd naar .xlsx

.xlsx produceren uit de uitvoer van de component

Wanneer de afnemer op .xlsx staat, maar de exportlogica al in TDataToXLS zit, converteert de bridge-functie in de unit lxXlsxExport de gevulde werkmap in één aanroep:

uses lxXlsxExport;

Exporter.SaveDatasetAs('orders.xls');
// de component geeft de IXLSWorkbook vrij die hij heeft gevuld
SaveXLSWorkbookAsXLSX(Exporter.Workbook, 'orders.xlsx');

Behandel de bridge als een drager van tabelgegevens, niet als een converter met volledige getrouwheid. Hij kopieert waarden, formules, getalnotaties, vulkleuren, lettertypekenmerken, kolombreedtes en weergave-instellingen. Hij kopieert bewust geen randen, samengevoegde bereiken, opmerkingen, grafieken of voorwaardelijke opmaak. Voor een plat raster van kop plus rijen is dat precies genoeg. Voor een opgemaakt rapport is dat niet zo, en de eerlijke oplossing is de XLSX rechtstreeks te genereren in plaats van het geconverteerde bestand te patchen

De handgeschreven lus voor services en batchtaken

Server-side code moet rechtstreeks op TXLSXWorkbook mikken. Let op het verschil in levensduur tussen de twee facades voordat je een voorbeeld overneemt. De TXLSWorkbook aan de XLS-kant wordt vastgehouden via een interface met referentietelling en mag niet handmatig worden vrijgegeven, terwijl TXLSXWorkbook een gewone class is die try..finally Free vereist. Beide conventies door elkaar gebruiken is een betrouwbare manier om óf een lek óf een dubbele vrijgave te veroorzaken

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 werkblad-XML rechtstreeks naar de zip
    Book.SaveAs(FileName);
  finally
    Book.Free;
  end;
end;

De regels die ertoe doen zijn de getypeerde toewijzingen en de IsNull-bewaker. Datums komen aan als datumserienummers, bedragen komen aan als doubles, en NULL-besteldatums blijven écht leeg in plaats van lege strings te worden. StreamingWrite := True verandert alleen het opslagpad: werkblad-XML streamt rechtstreeks naar de zip-container in plaats van eerst als één grote string te worden opgebouwd, wat de geheugenpiek tijdens SaveAs afvlakt bij rijaantallen met zes cijfers. Elke opslagmethode heeft ook een TStream-overload, zodat de werkmap rechtstreeks in een HTTP-respons kan worden geplaatst zonder de schijf aan te raken. Het artikel over streaming write en batchtaken behandelt dat implementatiepatroon, en het artikel over prestaties van grote werkmappen behandelt wat te doen wanneer rijaantallen verder oplopen

Deze lus is ook de route die over threads heen schaalt. Beide engines zijn native Object Pascal-schrijvers, BIFF8-recordstromen aan de ene kant en OOXML-zip plus XML aan de andere kant, dus geen enkel onderdeel van een export raakt COM-automatisering aan of heeft een Excel-licentie op de server nodig. Wat je daarmee krijgt is parallellisme zonder een single-instance-bottleneck, mits elke thread zijn eigen werkmap opbouwt. De workbook-objecten zijn niet thread-safe voor gedeeld gebruik, dus de regel is: één instantie per export, nooit een gedeelde instantie afgeschermd door een lock

Eén limiet is de moeite waard om te kennen voordat je eromheen ontwerpt. Het XLSX-raster stopt bij 1.048.576 rijen bij 16.384 kolommen, dus de bladsplitsing die RowsPerSheet aan de XLS-kant regelt, is hier zelden nodig. Een werkmap met een miljoen rijen is ook zelden wat een menselijke afnemer wil. Wanneer de resultaatset écht zo groot is, is een bestand met scheidingstekens meestal het betere contract, en het artikel over CSV- en TSV-export behandelt scheidingstekens, BOM-gedrag en de kanttekening over formule-evaluatie die daar geldt

Een startpunt kiezen

Als de export zich in een VCL-desktoptool bevindt en .xls-uitvoer acceptabel is, begin dan met TDataToXLS en de bijbehorende groeperingsondersteuning. Dat is de minste code, en de bridge via SaveXLSWorkbookAsXLSX is er wanneer iemand later om .xlsx vraagt, zolang je de al beschreven getrouwheidslimieten accepteert. Als de code onbeheerd draait, of de afnemer vanaf het begin .xlsx vereist, schrijf dan de lus. Beide routes worden geleverd met werkende demoprojecten en maken deel uit van het HotXLS Delphi Component-pakket