ODS Format: The Complete Guide to OpenDocument Spreadsheets

What Is ODS?

ODS stands for OpenDocument Spreadsheet, the spreadsheet file format defined by the OASIS Open Document Format for Office Applications (OpenDocument) standard, formally ISO/IEC 26300. It is the native format of LibreOffice Calc, Apache OpenOffice Calc, and several other open-source office suites, and an optional export format in Google Sheets, Apple Numbers, and even Microsoft Excel 2007 and later.

Like XLSX, an ODS file is a ZIP archive containing XML files. Unlike XLSX, which was designed primarily by Microsoft and later standardised, ODS was designed from the start by an industry consortium with a mandate for vendor neutrality, making it the preferred format for government institutions and organisations committed to open standards.

ZIP Container Structure

Unzipping an ODS file reveals:

mimetype                      — Must be first; uncompressed; declares application/vnd.oasis.opendocument.spreadsheet
META-INF/
  manifest.xml                — Lists all files in the archive with MIME types
content.xml                   — All cell data, formulas, styles, sheets (main document)
styles.xml                    — Page styles, headers/footers, master pages
meta.xml                      — Author, creation date, revision count
settings.xml                  — View settings (zoom, active sheet, freeze rows)
Thumbnails/
  thumbnail.png               — Preview image (128×128 px)
Pictures/                     — Embedded images (if any)

The mimetype file has a unique requirement: it must be the very first entry in the ZIP archive, stored without compression (STORE method). This allows file type detection utilities to identify the ODS type by reading the first bytes of the archive without decompressing anything.

content.xml: The Core Document

The entire spreadsheet lives in content.xml. It uses a hierarchical namespace structure:

<office:spreadsheet>
  <table:table table:name="Sheet1">
    <table:table-row>
      <table:table-cell office:value-type="string">
        <text:p>Hello, World!</text:p>
      </table:table-cell>
      <table:table-cell office:value-type="float" office:value="42.75">
        <text:p>42.75</text:p>
      </table:table-cell>
      <table:table-cell table:formula="of:=SUM([.A1:.B1])" office:value-type="float" office:value="487">
        <text:p>487</text:p>
      </table:table-cell>
    </table:table-row>
  </table:table>
</office:spreadsheet>

Key observations:

• office:value stores the raw numeric value while <text:p> stores the display text
• Formulas use the OpenFormula syntax (of: prefix) — slightly different from Excel's formula language
• Repeated empty cells are collapsed with table:number-columns-repeated to avoid verbose XML

OpenFormula: ODS Formula Language

ODS formulas are defined by the ODF Formula (OpenFormula) specification, a companion standard to ODS itself. Most function names are identical to Excel's (SUM, IF, VLOOKUP, INDEX, MATCH), but there are syntactic differences:

In practice, LibreOffice handles translation automatically when importing XLSX or exporting to XLSX. The divergence becomes visible only when inspecting the raw XML.

Styles and Formatting

ODS separates content and presentation more cleanly than XLSX. Styles are defined in styles.xml (for page-level styles) and in a style section within content.xml (for cell styles). Cell styles cascade similarly to CSS:

<style:style style:name="Currency" style:family="table-cell"
             style:parent-style-name="Default">
  <style:text-properties fo:color="#000000" fo:font-size="11pt"/>
  <style:table-cell-properties fo:border="0.05pt solid #000000"
                                fo:background-color="#FFFACD"/>
  <number:currency-style style:name="N104">
    <number:currency-symbol>$</number:currency-symbol>
    <number:number number:decimal-places="2"/>
  </number:currency-style>
</style:style>

Conditional formatting rules, data bars, colour scales, and icon sets are all supported in ODS, though not all are rendered identically by every application.

ODS vs. XLSX: Key Differences

Macro Support in ODS

ODS supports macros written in multiple languages through LibreOffice's scripting framework:

• LibreOffice Basic — closest analogue to Excel's VBA
• Python (via BASIC bridge) — available in LibreOffice 6.4+
• JavaScript — experimental
• BeanShell — Java-like scripting

Macros are stored in a separate directory within the ZIP archive:

Basic/
  Standard/
    Module1.xml   — Macro source code
  script-lc.xml  — Library catalogue

Unlike XLSM, there is no separate "ODS with macros" extension — macros simply coexist within the ODS file. This means any ODS file may potentially contain macros, a difference from XLSX's clean macro-free guarantee.

Interoperability with Excel

When converting between ODS and XLSX, expect these potential losses:

• Pivot tables — ODS has limited pivot table support; complex Excel pivots may not survive
• Slicers and timelines — not supported in ODS
• Power Query / Power Pivot — not supported
• Advanced conditional formatting — icon sets and data bars generally survive; formula-based rules may not
• Comments/notes — generally preserved
• Charts — basic chart types survive; some Excel-specific chart subtypes may simplify
• Named ranges — generally preserved
• Tables (structured references) — converted to plain ranges or simple database ranges

The reverse (XLSX → ODS) sees similar losses, particularly around pivot tables and VBA macros.

Google Sheets and ODS

Google Sheets can both import and export ODS files. The import handles most spreadsheet data correctly. Export from Google Sheets to ODS preserves:

• Cell values and formulas
• Basic number formatting
• Charts (exported as images)
• Conditional formatting (partially)

Google Sheets–specific features like ARRAYFORMULA, QUERY, IMPORTRANGE, and GOOGLETRANSLATE functions do not survive export to ODS since those functions don't exist in the OpenFormula specification.

File Size Comparison

ODS files are typically similar in size to XLSX for the same content because both use ZIP compression. ODS may be slightly larger for formula-heavy workbooks due to the verbose namespace declarations in XML, or slightly smaller for text-heavy workbooks due to slightly different string encoding strategies.

When to Use ODS

Use ODS when:

• Working primarily in LibreOffice or OpenOffice
• Submitting to government agencies or institutions that require open standards
• Collaborating with users who prefer open-source software
• Archiving data in a format that does not depend on any commercial vendor

Use XLSX when:

• Collaborating with Excel users (the majority of corporate environments)
• Using advanced Excel features: Power Query, Power Pivot, VBA macros, sparklines, slicers
• Maximum interoperability with non-LibreOffice applications is required

Converting ODS Files

ODS → XLSX: Open in LibreOffice Calc and Save As → Excel 2007-365 (.xlsx). Most features survive; pivot tables and macros do not. Google Drive also handles this conversion.

ODS → CSV: File → Save A Copy → Text CSV. Only the active sheet is exported; all formatting is lost.

ODS → PDF: File → Export as PDF. Provides fine-grained control over page ranges, compression, and accessibility tags.

XLSX → ODS: File → Save As → ODS in LibreOffice. VBA macros, pivot tables, and Power Query are not preserved.

CSV → ODS: Import using the LibreOffice Calc CSV import dialog, which allows specifying delimiter, encoding, and column data types.

Summary

ODS is a mature, ISO-standardised spreadsheet format built for vendor-neutrality and long-term archivability. While it lacks some of Excel's advanced analytical features (pivot slicers, Power Query, VBA), it excels in interoperability with open-source toolchains and compliance with open standards mandates. For organisations committed to software freedom or government-mandated open formats, ODS is the natural choice; for maximum compatibility with the Microsoft ecosystem, XLSX remains the practical standard.

Related conversions

Frequent conversions across the catalogue:

• JPG to PNG
• MP4 to MP3
• PDF to DOCX
• PNG to JPG
• MP3 to WAV
• DOCX to PDF