The downstream ERP rejected the nightly feed with a parse error in the totals column. The CSV looked correct in a text editor until row 42, where the amount field read =SUM(D2:D41) — the formula itself, as text, not the number it computes. Nothing had failed: that is documented behaviour. CSV export in HotXLS serialises the cell model in its current state, and a formula cell whose value has never been calculated has only formula text to give. Any team that wires the library's export calls into an integration pipeline hits a variant of this in the first week, so it is the right place to begin.
UK teams should align this hotxls csv tsv html export delphi workflow with local governance, audit, and data quality requirements before production release
Why your CSV contains formulas instead of numbers
HotXLS keeps formula text and computed value as separate things, and SaveAsCSV deliberately does not invoke the calculation engine — an export must not mutate the workbook or stall on a pathological formula chain. Files saved by Excel carry cached results alongside the formulas, so exporting those works as expected; the trap arises with workbooks your own code generated, where formulas were written but never evaluated. The remedy is to make the values exist before exporting, using the same Calculate engine that handles cross-sheet references and custom functions:
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
R: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('invoice-run.xlsx');
Sheet := Book.Sheets[0];
// Materialize formula results so the CSV carries numbers, not '=...' text
for R := 2 to 41 do
if Sheet.Cells[R, 4].Formula <> '' then
Sheet.Cells[R, 4].Value := Book.Calculate(Sheet.Cells[R, 4].Formula);
Book.SaveAsCSV('feed.csv', 0, ','); // sheet 0, comma
Book.SaveAsCSV('feed.tsv', 0, #9); // same sheet as TSV
finally
Book.Free;
end;
end;Note what the loop does: it overwrites the formula cells with their computed values, which is exactly right for a throwaway export pass but wrong if you intend to save the workbook again as .xlsx afterwards. Export from a copy, or scope the write-back to the export run. The broader capabilities of the engine behind Calculate — including registering your own functions — are covered by the HotXLS formula engine and custom functions.
What the delimited writer guarantees
The CSV path produces UTF-8 with a byte order mark, CRLF line endings, and RFC 4180 quoting — fields containing the delimiter, quotes, or line breaks are wrapped and embedded quotes doubled. Dates render as yyyy-mm-dd hh:nn:ss regardless of the cell's display format, which is the sane choice for machine consumers but surprises people expecting the on-screen formatting. Rich text cells are flattened by concatenating their runs.
Those defaults resolve most importer arguments before they start, with two caveats worth writing into your interface contract. Firstly, the BOM: it is what makes Excel itself open the file with correct accents, but a handful of strict parsers treat those three bytes as data — if yours does, strip them in the handoff. Secondly, TSV is not a separate feature; it is the same writer with #9 as the delimiter parameter, so everything above applies identically. The sheet is selected by 0-based index in the multi-argument overload, whilst the single-argument SaveAsCSV(FileName) shorthand takes the active sheet.
HTML export is a snapshot, not an interchange format
Where CSV throws away everything but values, SaveAsHTML tries to preserve appearance: one <table> per sheet, merged regions expressed as colspan and rowspan, and basic cell styling inlined as CSS. Theme-relative colours are skipped instead of resolved, so a corporate template that leans on theme slots will come out plainer than it looks in Excel — set explicit RGB colours on anything that must survive. The options object controls the envelope:
var
Opts: TXLSXHtmlExportOptions;
begin
Opts := TXLSXHtmlExportOptions.Create;
try
Opts.Title := 'Weekly settlement';
Opts.TableClass := 'report-grid'; // hook for the host page stylesheet
Opts.WriteDocument := True; // full page, not a fragment
if Book.SaveAsHTML('settlement.html', 0, Opts) <> 0 then
raise Exception.Create('Sheet index out of range');
finally
Opts.Free;
end;
end;Two details in that snippet repay attention. WriteDocument := False flips the output to a bare table fragment, which is what you want when injecting a preview into an existing page — set TableClass and let the surrounding stylesheet do the theming. And the return convention is the opposite of most HotXLS calls: SaveAsHTML returns 0 on success and -1 for a bad sheet index, so a habit-driven = 1 success check will misreport every export. For emailing or embedding just a region instead of a sheet, TXLSXRange.SaveAsHTML exports any rectangular block with the same rendering rules.
RTF output and where it still earns its place
The fourth export target writes RTF 1.6 tables, one sheet per call via SaveAsRTF. Column widths are approximated at roughly 96 twips per character of column width, and — the structural limitation — merged cells do not span in the output: only the anchor cell carries content and the covered cells emit as blanks. That makes RTF unsuitable for layout-heavy templates, but it remains the path of least resistance for pasting tabular results into word-processor documents and legacy document management systems that predate HTML ingestion.
Round-tripping: importing CSV is destructive by design
The import direction has its own contract. OpenCSV clears the entire workbook and rebuilds it as a single sheet named Sheet1 — it is a constructor in spirit, not a merge, so never call it on a workbook holding unsaved content. Passing #0 as the separator triggers automatic delimiter detection, and the ADetectTypes flag controls type promotion: with it on, numeric strings become numbers, ISO-8601 strings become dates, and true/false become booleans. Turn it off when the feed contains identifiers with leading zeros or zip codes, which type promotion silently corrupts into numbers. Both workbook facades expose the same import; pair it with the export calls above and you have a format bridge that needs no Excel installation anywhere in the pipeline, the scenario explored further in database-to-Excel report generation with HotXLS.
Exporting straight into a stream
Every writer discussed here — CSV, HTML, RTF, and the workbook formats themselves — has stream overloads alongside the file-name versions, and in server code those are the ones to reach for. A web endpoint that produces a CSV download can write into a TMemoryStream and hand it to the response object directly: no temporary file, no cleanup job, no collision between two requests exporting under the same generated name. The same applies to dropping exports into blob storage or attaching them to outbound mail — the file system stops being part of the pipeline at all.
This pattern compounds with the library's core deployment property: both facades are native Object Pascal readers and writers, so there is no Excel installation, no COM automation, and no per-process single-threading bottleneck on the server. Each request can own its workbook object, run the calculation write-back from the first section, and stream the export, fully in parallel with its neighbors. The one resource to watch is memory: the workbook model is held in RAM during export, so a service that opens very large workbooks merely to re-emit them as CSV should cap concurrent jobs or queue the oversized ones instead of letting peak traffic decide the working set.
Set IncludeBOM on the HTML options when the fragment will be saved as a standalone file consumed by tools that sniff encoding, and leave the content-type charset declaration to the HTTP layer when serving directly.
FAQ: spreadsheet export from Delphi
How do I export one specific sheet instead of the active one? Use the overloads that take a sheet index — SaveAsCSV(FileName, SheetIndex, Delimiter), SaveAsHTML(FileName, SheetIndex, Options), SaveAsRTF(FileName, SheetIndex). The index is 0-based on the XLSX facade.
Excel shows broken accented characters when opening my exported CSV. Why? It almost certainly is not the file — the writer emits a UTF-8 BOM precisely so Excel decodes it correctly. Check whether an intermediate step (an FTP transfer in text mode, a stream copy that strips the first bytes) damaged the BOM.
Can I export only a cell range to CSV? On the XLS facade, yes — IXLSRange has its own SaveAsCSV, SaveAsHTML, and SaveAsRTF. On the XLSX facade, range-level export is available for HTML via TXLSXRange.SaveAsHTML.
Exports are where workbook semantics meet other systems' parsers, so the contract details above belong in your integration documentation, not just your code. The full method reference is on the HotXLS Component product page.