Picture a nightly job that builds an invoice workbook in code and writes it out as CSV for a downstream system to import. The numbers look right in Excel. The CSV opens cleanly in a text editor. Then the importer chokes on the totals column, because the amount field for row 42 reads =SUM(D2:D41), the formula as literal text, not the figure it should compute to. Nothing is broken. This is documented behavior, and it is the first thing to understand about exporting from HotXLS: the writer serializes the cell model exactly as it stands, and a formula cell whose value was never computed has only its formula text to hand over
Why your CSV contains formulas instead of numbers
HotXLS stores formula text and computed value as two separate things. SaveAsCSV does not run the calculation engine on the way out, by design: an export should not mutate the workbook, and it should not risk stalling on a pathological formula chain. Files that Excel itself saved carry cached results next to the formulas, so re-exporting those behaves the way you expect. The trap is specific to workbooks your own code generated, where formulas were written but never evaluated. The fix is to make the values exist before you export, using the same Calculate engine that resolves 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;Watch what the loop actually does: it overwrites the formula cells with their computed values. That is exactly right for a throwaway export pass and wrong if you mean to save the workbook again as .xlsx afterwards, because you have just replaced live formulas with frozen numbers. Export from a copy, or scope the write-back so it touches only the export run. The engine behind Calculate goes further than this, including registering your own functions, which is the subject of 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. Any field containing the delimiter, a quote, or a line break gets wrapped, and embedded quotes are doubled. Dates render as yyyy-mm-dd hh:nn:ss regardless of the cell's display format. That is the right call for a machine consumer, though it surprises anyone who expected the on-screen formatting to carry over. Rich text cells are flattened by concatenating their runs
Those defaults settle most arguments with an importer before they start, but two of them belong in your interface contract anyway. The first is the BOM. It is what lets Excel open the file with accented characters intact, yet a handful of strict parsers treat those three bytes as data; if yours is one of them, strip them in the handoff. The second is TSV. It is not a separate feature at all, just the same writer called with #9 as the delimiter, so everything above applies to it unchanged. The sheet to export is chosen by 0-based index in the multi-argument overload, while 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 keep the appearance: one <table> per sheet, merged regions expressed as colspan and rowspan, basic cell styling inlined as CSS. Theme-relative colors are skipped rather than resolved, so a template that leans on theme slots comes out plainer than it looks in Excel. Set explicit RGB colors on anything that has to survive the trip. 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. Flip WriteDocument to False and the output becomes a bare table fragment instead of a full page, which is what you want when injecting a preview into an existing layout: set TableClass and let the host stylesheet do the theming. The return convention is also the reverse of most HotXLS calls. SaveAsHTML returns 0 on success and -1 for a bad sheet index, so a habit-driven check for = 1 will report every successful export as a failure. When you need a region rather than a whole sheet, perhaps to email or embed a single block, TXLSXRange.SaveAsHTML exports any rectangular range under the same rendering rules
RTF output and where it still earns its place
The fourth target writes RTF 1.6 tables, one sheet per call through SaveAsRTF. Column widths are approximated at roughly 96 twips per character of column width. The structural limitation to know about is that merged cells do not span in the output: only the anchor cell carries its content, and the covered cells emit as blanks. That rules RTF out for layout-heavy templates. It still earns its place as the path of least resistance for dropping tabular results into a word processor or into a legacy document-management system that predates HTML ingestion
Round-tripping: importing CSV is destructive by design
Reading CSV back in 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 that still holds unsaved content. Passing #0 as the separator triggers automatic delimiter detection. 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 carries identifiers with leading zeros, zip codes, or product codes, all of which promotion silently mangles into numbers (a leading zero is simply gone the moment 00123 becomes 123). Both facades expose the same import. Pair it with the export calls above and you have a format bridge that needs no Excel installed anywhere in the pipeline, the scenario covered in database-to-Excel report generation with HotXLS
Exporting straight into a stream
Every writer here has a stream overload sitting next to the file-name version: CSV, HTML, RTF, and the workbook formats themselves. In server code those overloads are the ones to reach for. A web endpoint that serves a CSV download can write into a TMemoryStream and hand that straight to the response object, with no temporary file, no cleanup job, and no collision between two requests that happened to pick the same generated name. The same holds for pushing exports into blob storage or attaching them to outbound mail. The file system drops out of the picture entirely
That pattern compounds with how the library deploys. Both facades are native Object Pascal readers and writers, so there is no Excel installation, no COM automation, and no per-process bottleneck serializing requests on the server. Each request can own its workbook object, run the calculation write-back from the first section, and stream its export in parallel with its neighbors. Memory is the one resource to keep an eye on. The workbook model lives in RAM for the duration of the export, so a service that opens very large files just to re-emit them as CSV should cap concurrent jobs, or queue the oversized ones, rather than letting a traffic spike decide the working set
One smaller knob: set IncludeBOM on the HTML options when the fragment will be saved as a standalone file that some tool downstream sniffs for encoding. When you serve HTML directly over HTTP, leave the charset declaration to the response headers instead
When the bytes still come out wrong
The most common support question about CSV export is the opening problem in a different costume: Excel shows mojibake instead of accented characters. The instinct is to blame the writer, but it emits a UTF-8 BOM for exactly this reason, and the file is almost always correct when it leaves your code. Something between there and Excel ate the BOM. An FTP transfer in text mode, a stream copy that skips the first three bytes, a proxy that re-encodes on the way through: any of those will strip the marker and leave Excel to guess at the encoding, which it does badly. Diagnose this at the boundary, not in the export call. Open the delivered file in a hex viewer and confirm EF BB BF is still the first thing in it
That is the through-line for all four formats. The export call is the easy part, and HotXLS makes a defensible choice at each decision the writer faces. The failures live at the seams, where formula text meets a parser that wanted a number, where a BOM meets a transport that does not preserve it, where a merged cell meets RTF's flat table model. Each of those is a fact to write into the contract between your exporter and whatever consumes it, because the consumer cannot read your intentions out of the bytes. For the complete method list across both workbook facades, the HotXLS Component product page carries the full reference