A Delphi reporting backend that has produced .xlsx files for years gets a new requirement: a public-sector customer's procurement rules mandate OpenDocument Spreadsheet output, and that customer's analysts send revisions back as .ods files saved from LibreOffice. HotXLS, losLab's native Object Pascal spreadsheet library for Delphi and C++Builder, covers both directions of that exchange without Excel or LibreOffice being installed anywhere. The two directions are not symmetric, though, and pretending they are is how data quietly disappears between systems. This article maps exactly what the ODS code path reads, what it writes, and how to design a round trip whose losses are known in advance instead of discovered in a support ticket.
ODS support lives on the XLSX facade, not the XLS one
HotXLS ships two independent class hierarchies in a single package: TXLSWorkbook in unit lxHandle for binary BIFF8 .xls files, and TXLSXWorkbook in unit lxHandleX for OOXML .xlsx packages. Every OpenDocument entry point - OpenODS, SaveAsODS, GetODSSheetNames - hangs off TXLSXWorkbook. The placement is not arbitrary. An ODS package, as specified in OASIS ODF 1.3, is a zip archive carrying a mimetype member, a manifest, and a content.xml body, which makes it a structural cousin of the OOXML zip; BIFF8 is a 1990s binary record stream with nothing in common.
The practical consequence: a legacy .xls workbook cannot become .ods in one call. The supported route is bridging the BIFF content into the XLSX model first with SaveXLSWorkbookAsXLSX from unit lxXlsxExport, reopening the result through TXLSXWorkbook, and exporting from there. Budget for the bridge's fidelity limits while planning - it copies values, formulas, number formats, fonts, fills, and column widths, but not borders, merged ranges, comments, charts, or conditional formatting.
Detection on the import side is automatic. The plain Open method recognizes an ODS package by its mimetype member, falling back to a top-level content.xml check when that member is absent, so a generic "open whatever the user uploaded" code path needs no extension sniffing of its own. After opening, the SourceFormat property reports which branch fired.
Exporting to ODS with TODSExportOptions
The export call itself is one line; the options object around it carries the decisions a reviewer will ask about later:
var
Book: TXLSXWorkbook;
Opts: TODSExportOptions;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('quarterly-report.xlsx');
Opts := TODSExportOptions.Create; // caller owns and frees this
try
Opts.Generator := 'ReportService 4.2'; // meta:generator override
Opts.IncludeCharts := True;
Opts.IncludeImages := True;
Book.SaveAsODS('quarterly-report.ods', Opts);
finally
Opts.Free;
end;
finally
Book.Free;
end;
end;Three details earn their place in that listing. First, the options object is owned by the caller - HotXLS does not free it, so the inner try..finally matters. Second, IncludeCharts := False does more than hide charts: it removes the chart sub-documents and their manifest entries from the package entirely, which is the right shape when the consumer is a data pipeline that would choke on them. Third, Generator overrides the ODF meta:generator string (the default is HotXLS/<version>); set it when downstream tooling fingerprints file producers for support routing. Calling SaveAs(FileName, xlsxOpenDocumentSpreadsheet) is equivalent to SaveAsODS with default options, and stream overloads exist for delivering the package straight to an HTTP response without a temp file.
What the import path reads - and what it deliberately skips
This is the section to read twice before promising round-trip fidelity to anyone. ODS import in HotXLS is a lightweight path: it preserves scalar cell values, the cached results of formulas, and expands repeated rows and columns into the grid. It does not import styles, it does not import ODS formula expressions, and it does not import drawings.
The formula decision deserves explanation rather than apology. An ODF cell stores both the formula expression - written in the OpenFormula dialect defined in ODF 1.3 Part 4 - and the last value the producing application computed for it. Translating OpenFormula into Excel formula syntax is a dialect-conversion problem with genuine edge cases: different function vocabularies, different reference syntax, different error models. By reading the cached value instead, HotXLS sidesteps the entire class of silent mistranslation bugs and guarantees that the numbers you import are the numbers the sender last saw.
The failure mode to design around follows directly: a spreadsheet whose totals were correct when LibreOffice last saved it imports with correct numbers, but those numbers are now constants. Edit an input cell, recalculate, and nothing moves - the formula is gone, only its final result remains. If the workflow needs live formulas after import, re-establish them programmatically from your own business rules through Cell.Formula, which on the XLSX facade takes the expression without a leading equals sign.
Designing around the asymmetric round trip
Export renders from the full in-memory workbook model - values, styles, and optionally charts and images - while import is values-only. Stated as a matrix: .xlsx to .ods is high fidelity; .ods to .xlsx lands values and cached results but arrives without styling or live formulas; and a full .xlsx to .ods to .xlsx cycle therefore loses styles and formulas on the second leg even though the first leg wrote them faithfully.
Book := TXLSXWorkbook.Create;
try
Book.Open('vendor-revision.ods'); // format auto-detected
if Book.SourceFormat = xlsxOpenDocumentSpreadsheet then
begin
// Values and cached formula results are present after an ODS
// import; styles and live formulas are not. Rebuild whatever
// the downstream pipeline depends on before saving.
Book.Sheets[0].Cells[2, 5].Formula := 'SUM(B2:D2)';
Book.SaveAs('vendor-revision.xlsx');
end;
finally
Book.Free;
end;The architectural pattern that falls out of this: treat inbound .ods files as data feeds, not as documents to edit in place. Keep the canonical workbook in .xlsx, read values out of customer revisions, and emit fresh ODS on demand from the canonical copy. Verification belongs in both camps - open exported files in LibreOffice Calc, the reference ODF consumer, and in Excel, which has read ODS for years but disagrees with LibreOffice at the edges of chart and style support. Sheet count, a handful of key cells, and chart presence make a sufficient smoke check per export profile.
Triaging an ODS file before committing to an import
When an endpoint accepts uploads, listing sheet names is far cheaper than a full parse and catches structural surprises early:
Names := TStringList.Create;
Book := TXLSXWorkbook.Create;
try
if Book.GetODSSheetNames('incoming.ods', Names) <= 0 then
raise Exception.Create('not a readable ODS package');
if Names.IndexOf('Data') < 0 then
raise Exception.Create('revision is missing the Data sheet');
finally
Book.Free;
Names.Free;
end;The return convention trips people up: HotXLS calls generally return a positive count or 1 on success and -1 on failure, clearing the list as they fail, so test <= 0 rather than comparing against one specific positive value. GetODSSheetNames neither resets nor populates the workbook instance, so a single probe object can vet a whole directory of incoming files. Structural checks like this catch the most common real-world failure - an analyst renaming or deleting a sheet before sending the revision back - at the gate, where the error message can still name the file and the missing sheet instead of surfacing as a nil reference three layers deeper.
Frequently asked questions
Can HotXLS convert .xls directly to .ods?
Not in one call. Bridge the .xls into an .xlsx with SaveXLSWorkbookAsXLSX, reopen it with TXLSXWorkbook, then call SaveAsODS - and account for the bridge dropping borders, merges, comments, and charts along the way.
Do formulas survive an ODS round trip?
Writing ODS preserves what the workbook model contains. Reading ODS keeps each formula's cached result as a constant and drops the expression, so live formulas do not survive the return leg.
Will Excel open the ODS files HotXLS produces?
Yes - Excel reads OpenDocument spreadsheets - but validate output in both Excel and LibreOffice, because the two applications support different subsets of ODF styling and chart features.
If you are building a broader conversion pipeline around this, the workbook audit and conversion workbench pattern shows how to inventory a file's features before choosing a target format, and the large-workbook performance guide keeps batch exports inside sane memory bounds.
HotXLS is a native Delphi and C++Builder spreadsheet library with full source code; the complete feature list and licensing details are on the HotXLS Component product page.