A finance user opens last night's order export, selects the Amount column, and Excel's status bar shows a count but no sum. Every value in the column is text. The export code called AsString on every field because that was the quickest way to get data out of a TDataset, and the workbook faithfully stored forty thousand left-aligned strings that merely resemble currency. Nothing raised an exception, the file opens cleanly, and the report is still useless for analysis. Most database-to-Excel defects look exactly like this: silent, type-related, and invisible until a customer tries to do arithmetic on the result.
UK teams should align this hotxls database export delphi reports workflow with local governance, audit, and data quality requirements before production release
For UK environments, align the hotxls database export delphi reports implementation with local quality gates that include governance approval, versioned fixture baselines, and evidence retention for every publication candidate. Keep an explicit review log for accessibility, redaction policy, and data residency checks so your deployment audit is repeatable without changing output binaries
UK technical governance addendum
HotXLS is a native Object Pascal spreadsheet library that writes XLS and XLSX files directly from Delphi and C++Builder, with no Excel automation involved. It offers two distinct routes from a TDataset to a workbook: the drop-in TDataToXLS component, and a hand-written loop against the workbook API. The two routes are not interchangeable — the component is a VCL citizen built on the XLS facade — so the right choice depends on where the code runs and which file format the consumer expects. This guide covers both, where the component route stops, and how to keep field types intact either way.
Field types are the real export contract
Before any API call, decide how each Delphi field type lands in a cell. A cell that receives a Delphi string stays a string; HotXLS does not guess that '1,234.50' was meant to be a number, and it should not, because locale-dependent reparsing is exactly how a German decimal comma turns into a thousands separator on an English server. The reliable pattern is to assign through the typed accessors: AsFloat or AsCurrency for numeric fields, AsDateTime for dates so the cell holds a genuine Excel date serial instead of a formatted string, and AsString only for fields that are actually text.
Null handling deserves an explicit decision instead of a default. Converting a field value with VarToStr turns SQL NULL into an empty string — which is a text cell — whilst skipping the assignment leaves the cell truly empty, which is what AVERAGE, COUNT and pivot-table consumers expect. For money columns, decide before the loop is written whether NULL means zero or unknown, because the two render identically once someone formats the column, and the difference changes every aggregate computed downstream.
The component route: TDataToXLS in VCL applications
For a classic VCL application with a query already wired into a data module, TDataToXLS is the one-call route. It walks any TDataset descendant — FireDAC, ADO, IBX, anything that implements the abstract dataset interface — and produces a styled worksheet with header captions, fonts, borders, optional group subtotals, and automatic sheet splitting for large result sets.
var
Exporter: TDataToXLS;
begin
Exporter := TDataToXLS.Create(nil);
try
Exporter.Dataset := OrdersQuery; // any TDataset descendant
Exporter.WorksheetName := 'Orders';
Exporter.HeaderSource := hsDisplayLabel; // captions, not raw column names
Exporter.GroupFields.Add('CustomerID'); // subtotal block per customer
Exporter.RowsPerSheet := 50000; // stay below the BIFF8 row ceiling
Exporter.OnlyVisible := True; // respect Field.Visible
Exporter.SaveDatasetAs('orders.xls');
finally
Exporter.Free;
end;
end;Two properties carry most of the production weight here. HeaderSource := hsDisplayLabel writes each field's DisplayLabel instead of the raw SQL column name, so the workbook says 'Customer Name' instead of CUST_NM. RowsPerSheet exists because the component writes BIFF8, whose grid stops at 65,536 rows by 256 columns; setting it to 50,000 splits a large result set across sheets before the format ceiling truncates it. Appearance is handled by the HeaderFont, DetailFont, GroupColor and border-style properties, and the DisableFormat set switches off whole formatting categories when the consumer wants plain cells. For anything bespoke, the AfterCell and AfterRow events hand you the just-written range for post-processing.
Where the component stops
Three constraints are designed into TDataToXLS, and knowing them up front avoids an awkward redesign two sprints later.
- It is a VCL component in the full sense. Its unit pulls in
Forms,ControlsandDialogs, so linking it into a console job or a Windows service drags the VCL into the binary. The core workbook units have no such dependency — they need onlyWindows,Classes,SysUtilsandVariants— which explains why server-side code should use the loop shown below instead. - It is built on the XLS facade. The component populates an
IXLSWorkbookand writes .xls (BIFF8). There is no property that switches it to OOXML output. - Its events speak the XLS dialect. The
Cell: IXLSRangeparameter inAfterCellbelongs to the XLS object model, so per-cell customisation written there is XLS-style code even if the file is converted to .xlsx afterwards.
Producing .xlsx from the component's output
When the consumer insists on .xlsx but the export logic already lives in TDataToXLS, the bridge function in the lxXlsxExport unit converts the populated workbook in one call:
uses lxXlsxExport;
Exporter.SaveDatasetAs('orders.xls');
// the component exposes the IXLSWorkbook it populated
SaveXLSWorkbookAsXLSX(Exporter.Workbook, 'orders.xlsx');Treat the bridge as a tabular-data carrier, not a full-fidelity converter. It copies values, formulas, number formats, fill colours, font attributes, column widths and view settings — and it deliberately does not copy borders, merged ranges, comments, charts or conditional formats. For a flat grid of header plus rows that is exactly enough; for a styled report it is not, and the honest fix is to generate the XLSX directly instead of to patch the converted file.
The hand-written loop for services and batch jobs
Server-side code should target TXLSXWorkbook directly. Note the lifetime difference between the two facades before copying any sample: the XLS-side TXLSWorkbook is held through a reference-counted interface and must not be freed manually, whilst TXLSXWorkbook is a plain class that requires try..finally Free. Mixing the two conventions is a reliable way to manufacture either a leak or a double-free.
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 sheet XML straight into the zip
Book.SaveAs(FileName);
finally
Book.Free;
end;
end;The lines that matter are the typed assignments and the IsNull guard: dates arrive as date serials, amounts arrive as doubles, and NULL order dates stay genuinely empty instead of becoming empty strings. StreamingWrite := True changes only the save path — worksheet XML streams straight into the zip container instead of being assembled as one large string first — which flattens the memory spike at SaveAs time for six-figure row counts. Every save method also has a TStream overload, so the workbook can go straight into an HTTP response without touching disk; the streaming write and batch job article walks through that deployment pattern, and the large workbook performance article covers what to do when row counts climb further.
One more practical note: the XLSX grid limit is 1,048,576 rows by 16,384 columns, so the sheet-splitting logic that RowsPerSheet provides on the XLS side is rarely needed here — but a million-row workbook is rarely what a human consumer wants either. When the result set is that large, a delimited file is typically the better contract; the CSV and TSV export article covers delimiters, BOM behaviour, and the formula-evaluation caveat that applies there.
Common questions about dataset export
Can TDataToXLS write .xlsx directly?
No. The component is built on the XLS facade and writes BIFF8 files. Either convert its workbook with SaveXLSWorkbookAsXLSX and accept the fidelity limits described above, or write the XLSX loop by hand — the demo projects ship both patterns side by side.
Does the server need Microsoft Excel installed?
No. Both engines are native Object Pascal writers — BIFF8 record streams on one side, OOXML zip plus XML on the other — so there is no COM automation, no Excel licence on the server, and no single-instance bottleneck when several exports run concurrently. Each thread should simply use its own workbook instance, because the workbook objects are not thread-safe for shared use.
Why do my exported numbers not sum in Excel?
Almost always because they were written through AsString or a Variant that arrived as text. Assign numeric fields with AsFloat or AsCurrency and let number formats handle presentation; the cell then carries a real IEEE double that every Excel aggregate understands.
Picking a starting point
If the export lives in a VCL desktop tool and .xls output is acceptable, begin with TDataToXLS and its grouping support — it is the least code. If the code runs unattended, or the consumer requires .xlsx, write the loop. Both routes, with working demo projects for each, are part of the HotXLS Component package.