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. The import path brings formulas back as live formulas next to their cached results, and it also reads styles, number formats, merged ranges, comments, images, charts and conditional formats. OpenFormula and Excel syntax are different dialects, though, and a few package-level details sit outside spreadsheet content, so a team that assumes a perfect mirror will 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. Know what the bridge carries before you build on it. It copies values, formulas, number formats, fonts, fills, borders, merged ranges, column widths, and row heights. Comments, charts, and conditional formatting stay on the .xls side, so re-create them on the XLSX model after the bridge runs when the ODS output needs them
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;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 labeling 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 back
Read this part carefully before you promise anyone round-trip fidelity. OpenODS reads 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. Its scope is spreadsheet content, so plan separately for ODS protection keys, comment geometry and advanced comment formatting, and package features outside the spreadsheet, and treat formulas as the dialect conversion described next
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. OpenODS imports the expression as well (available since HotXLS 2.88.229): 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. The import also converts inline array rows ({1;2|3;4} becomes {1,2;3,4}) and the ~ reference union operator, and such a union reads back without a second pair of parentheses. Translating OpenFormula into Excel syntax is 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 follows from that. Because formulas come back live, the 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 the round trip
Export renders from the full in-memory workbook model: values, formulas, styles, and, if you ask for them, charts and images. Formula export keeps inline arrays, reference unions, and absolute references meaningful in OpenFormula, declares the namespaces of its formula prefixes so LibreOffice evaluates the formulas, and writes whole-column and whole-row references in the OpenFormula form [.A:.A] so Excel reads them as well. Conditional formats are written twice: formula-based rules as formula-is(...) with their base cell, which LibreOffice recognizes, and value and formula rules also 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 therefore keeps values, formulas, styles, and conditional formats; the items outside spreadsheet content noted in the previous section are the ones to plan for separately
Book := TXLSXWorkbook.Create;
try
Book.Open('vendor-revision.ods'); // format auto-detected
if Book.SourceFormat = xlsxOpenDocumentSpreadsheet then
begin
// 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