A Delphi reporting backend that has emitted .xlsx for years picks up a new requirement: a public-sector customer's procurement rules mandate OpenDocument Spreadsheet output, and the analysts on that account send their edits back as .ods files saved from LibreOffice. So now the same code has to write ODS and read it. HotXLS, losLab's native Object Pascal spreadsheet library for Delphi and C++Builder, handles both directions with no Excel or LibreOffice installed anywhere. The two directions are close but not identical. Since HotXLS 2.88.229 the import path brings formulas back as live formulas next to their cached results, and current releases also read styles, number formats, merged ranges, comments, images, charts and conditional formats. What still does not survive is full OpenFormula semantics and a handful of package-level details, so a team that assumes a perfect mirror will still meet edge cases somewhere between the customer's revision and the next report, usually with no error to point at
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
That placement has a practical edge: a legacy .xls workbook cannot become .ods in one call. You bridge the BIFF content into the XLSX model first, with SaveXLSWorkbookAsXLSX from unit lxXlsxExport, reopen the result through TXLSXWorkbook, then export from there. The bridge is not lossless, and it is worth knowing the gaps before you build on it. It copies values, formulas, number formats, fonts, fills, borders, merged ranges, column widths, and row heights. It drops comments, charts, and conditional formatting. A .xls source with heavy formatting will reach ODS looking plainer than it left, and that is a property of the bridge, not of the ODS writer
Detection on the import side is automatic. The plain Open method recognises 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;The options object is owned by the caller. HotXLS will not free it, which is why the inner try..finally is there and not optional. The two properties that change the output, rather than just labelling it, are worth a closer look. Setting IncludeCharts := False does more than hide charts: it strips the chart sub-documents and their manifest entries out of the package, which is exactly what you want when the consumer is a data pipeline that would trip over them. Generator overrides the ODF meta:generator string, which otherwise reads HotXLS/<version>; override it when downstream tooling fingerprints file producers to route support. If none of that applies, skip the options object entirely. Calling SaveAs(FileName, xlsxOpenDocumentSpreadsheet) is the same as SaveAsODS with defaults, and stream overloads on both let you write the package straight into an HTTP response with no temp file
What the import path reads - and what it still skips
Read this part carefully before you promise anyone round-trip fidelity. ODS import in HotXLS started as a lightweight path that kept only scalar values and cached formula results, and it has grown well past that. Current releases read content.xml together with styles.xml and settings.xml: scalar values and cached formula results, repeated rows and columns expanded into the grid, merged ranges, rich text, hyperlinks, comments, embedded images and charts, inherited and default cell styles, number and date formats, row heights and column widths, named ranges, freeze panes, AutoFilter ranges, data validations, and conditional formats. What OpenODS still does not convert is full OpenFormula semantics, ODS protection keys, comment geometry, advanced comment formatting, and package features outside spreadsheet content
Formulas are the part most likely to bite. An ODF cell stores two things side by side: 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. Early releases read only the cached value. Since HotXLS 2.88.229, OpenODS also imports the expression: a cell written with the of:= or oooc:= prefix keeps its cached value and gets a normalized Excel formula, with bracketed references such as [.A1:.B1] and semicolon argument separators converted to Excel syntax. Since 2.384.32 the import also converts inline array rows ({1;2|3;4} becomes {1,2;3,4}) and the ~ reference union operator, and since 2.384.55 such a union reads back without a second pair of parentheses. Translating OpenFormula into Excel syntax is still a dialect-conversion problem with real edge cases around function vocabularies, reference syntax, and error models, which is why the cached value comes along: the numbers you import are exactly the numbers the sender last saw
The failure mode to design around has narrowed accordingly. Before 2.88.229, a spreadsheet whose totals were correct when LibreOffice last saved it imported with correct numbers that were now constants: edit an input cell, recalculate, and nothing moved. On current releases the formulas come back live, so the remaining risk is a construct the translator does not model, which can leave a formula that recalculates differently from the producing application while the cached value still shows the sender's result. Compare recalculated results against the imported cached values for the formulas your pipeline depends on, and re-establish any formula that disagrees from your own business rules through Cell.Formula, which on the XLSX facade takes the expression without a leading equals sign
Designing around the remaining round-trip gaps
Export renders from the full in-memory workbook model: values, formulas, styles, and, if you ask for them, charts and images. Formula export has been tightened release by release. Since 2.384.55 inline arrays, reference unions, and absolute references keep their meaning in OpenFormula; since 2.384.56 HotXLS declares the namespaces of its formula prefixes, so LibreOffice evaluates the formulas instead of showing #VALUE! in every formula cell; and since 2.384.65 whole-column and whole-row references are written in the OpenFormula form [.A:.A], so Excel no longer opens them as #NAME?. Conditional formats followed: since 2.384.66 formula-based rules are written as formula-is(...) with their base cell, which LibreOffice recognizes, and since 2.384.69 value and formula rules are also written as standard style:map conditions, the only form Excel reads, while the import side reads both forms. A full .xlsx to .ods to .xlsx cycle on a current release therefore keeps values, formulas, styles, and conditional formats; what it can still lose sits in the gaps listed in the previous section
Book := TXLSXWorkbook.Create;
try
Book.Open('vendor-revision.ods'); // format auto-detected
if Book.SourceFormat = xlsxOpenDocumentSpreadsheet then
begin
// Since HotXLS 2.88.229 formulas come back live next to their
// cached results; re-establish any formula the downstream
// pipeline cannot trust as translated 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
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 Delphi Component product page