Sustainability of Digital Formats: Planning for Library of Congress Collections

Introduction | Sustainability Factors | Content Categories | Format Descriptions | Contact
Format Description Categories >> Browse Alphabetical List

OpenDocument Spreadsheet Document Format (ODS), Version 1.2, ISO 26300:2015

>> Back
Table of Contents
Format Description Properties Explanation of format description terms

Identification and description Explanation of format description terms

Full name OpenDocument Spreadsheet Document Format (ODS), Version 1.2. Part of OASIS Open Document Format for Office Applications, Version 1.2 and the equivalent ISO 26300:2015.
Description

The OpenDocument Spreadsheet Document Format (ODS), Version 1.2 (given the short name ODF_spreadsheet_1_2 here) is an XML-based format for editable spreadsheet documents. It is one of several subtypes in the ODF family for particular content categories. The format, an international standard, is specified in the ODF 1.2 specification as published by OASIS. The ISO/IEC 26300:2015 specification is equivalent. The key difference between ODF_spreadsheet_1_1 and ODF_spreadsheet_1_2 is that the later version incorporates the OpenFormula specification for formulas and functions, whereas ODF_spreadsheet_1_1 allowed any syntax for strings representing formulas. This shortcoming led to interoperability problems resolved in version 1.2.

The primary ODF markup used for spreadsheets is specified in the table: namespace, with the <table:table> element as a container for each worksheet. The same namespace and markup is used for tables in textual documents.

The ODF specification covers two physical forms for ODF documents, a flat form as a single XML file and a package form based on the ZIP_6_2_0 format. See Notes in ODF Family for more information about the flat XML-only variant of ODF files. For a flat ODF spreadsheet file, usually given a file extension of .fods, the root <office:document> element has an office:mimetype attribute with one of the values listed below as File signifiers. This description focuses on the more commonly used ZIP-based package format for ODF spreadsheet files, typically given the .ods file extension. Files using the same markup specification and package but with an extension of .ots are for use as spreadsheet document templates.

An ODF package can be recognized as a spreadsheet document in several ways. Externally, there are file extensions for two ways in which ODF spreadsheet documents may be used, as noted above. The primary internal indication is that the mandatory file named mimetype will contain one of the corresponding strings listed as File signifiers below. An additional way to recognize an ODF spreadsheet document is that the <office:body> element, a child of the root <office:document-content> element in content.xml has the child element <office:spreadsheet>.

The typical content.xml file for a minimal spreadsheet file with a single worksheet has the basic form:

  • <office:document-content>
  • <office:automatic-styles> -- captures formatting styles created automatically in response to a direct user choice, such as a fill color for a cell, with references to fuller style specifications in the styles.xml file -- </office:automatic-styles>
  • <office:body>
  • <office:spreadsheet>
  • <table:table> -- contains a series of <table:table-column> elements to describe each column in the table. The column specifications are followed by <table:table-row> elements consisting of <table:table-cell> entries. A cell that contains a string will contain a child <text:p> element. A cell containing a number will also contain a <text:p> that shows the display form of the value. The actual value is stored in the <table:table-cell> element using two attributes: office:value-type and office:value. Numeric value types for cells are float, percentage, and currency. Separate types are used to indicate date and time values. For discussion of cells containing formulas, see Notes below -- </table:table>
  • </office:spreadsheet>
  • </office:body>
  • </office:document-content>

For details of the ZIP-based package for ODF_spreadsheet_1_2, see ODF_package_1_2. The typical files for a minimal spreadsheet document include: mimetype (one-line file containing only the string "application/vnd.oasis.opendocument.spreadsheet"; ./META-INF/manifest.xml (package manifest); content.xml (spreadsheet content); styles.xml (spreadsheet formatting). The package specification, in Part 3 of the ODF 1.2 specification defines the form for the package manifest, and options for digital signatures, encryption, etc.

Production phase Can be used in any production phase. Particularly used for creating documents (initial state) and for editing and review (middle-state). Used in final-state contexts where recalculation based on current data is required.
Relationship to other formats
    Subtype of ODF_Family, OpenDocument Format (ODF) Family, OASIS and ISO/IEC 26300
    May contain ODF_chart_1_2, OpenDocument Chart Document Format (ODC), Version 1.2, ISO 26300-1:2015
    May contain Other ODF content objects, such as drawings.
    Subtype of ODF_package_1_2, OpenDocument Package Format, ODF 1.2, part 3. ISO/IEC 26300-3:2015
    Contains META-INF/manifest.xml file, not described separately on this resource at this time. This manifest file is mandatory in all ODF packages.
    Has earlier version ODF_spreadsheet_1_1, OpenDocument Spreadsheet Document Format (ODS), Version 1.1, ISO/IEC 26300:2015
    Defined via XML_1_0, XML (Extensible Markup Language) 1.0. A normative RELAX NG schema is part of the specification for ODF 1.1, which includes the specification for spreadsheet documents.

Local use Explanation of format description terms

LC experience or existing holdings See ODF_family.
LC preference The Library of Congress Recommended Formats Statement (RFS) does not explicitly list ODF (ISO/IEC 26300) as an acceptable format for datasets. As described here, spreadsheets in the ODF 1.2 format, which incorporates the OpenFormula specification, have fewer interoperability problems than spreadsheets in earlier ODF versions.

Sustainability factors Explanation of format description terms

Disclosure International open standard. Developed and maintained by OASIS Open Document Format for Office Applications (OpenDocument) TC as part of the OpenDocument Format (ODF) 1.2 specification published by OASIS in 2011. Also approved as part of the equivalent ISO/IEC 26300:2015 by ISO/IEC JTC1/SC34.
    Documentation

Specifications from OASIS: Open Document Format for Office Applications (OpenDocument) Version 1.2. Specification for ODF 1.2 spreadsheet documents are found primarily in chapter 9 of Part 1 of the specification, with the specification for formulas in Part 2. The technical markup specification is in a normative RNG schema for primary component files for ODF 1.2 documents.. The ZIP-based package is defined in Part 3.

The identical specification is published as parts of ISO/IEC 26300:2015:

Adoption

Versions 3.0 and onward of OpenOffice.org defaulted to ODF 1.2. The version of OpenOffice.org at the time of the 2010 split between OpenOffice and LibreOffice was 3.3.

By 2015, the major applications supporting ODF could read and write spreadsheet documents as defined in ODF 1.2, although the default format might be termed "ODF 1.2 Extended."

  • In late 2015, LibreOffice recommended ODF 1.2 Extended as the default option for saving new files, but also allows users to default to ODF 1.2 or ODF 1.1. Setting Up LibreOffice shows how to change the default ODF version. See Notes for brief discussion of LibreOffice Extensions to standard versions of ODF.
  • Apache OpenOffice also supports ODF 1.2 (and has done so since version 3.0), with the product installing with ODF 1.2 Extended as the default ODF version. The compilers of this resource have not determined how extensions supported by Apache OpenOffice compare with those for LibreOffice. Comments welcome.
  • Microsoft first introduced support for ODF_spreadsheet_1_2 with Excel 2013. Microsoft lists ODF 1.2 as a "persistence format" for Excel. This indicates that use of ODF in Office does not involve conversion to and from OOXML. See Notes and Useful references below for information relating to how Microsoft supports ODF_spreadsheet _1_1, including the syntax for formulas, given that the ODF 1.1 specification does not mandate a particular syntax for formulas.

Gnumeric, an open source spreadsheet application for Unix, reads version 1.0, 1.1 and 1.2 of ODF spreadsheet files and writes version 1.2 files with or without foreign elements. Products descended from early adopters of ODF include: IBM Connections Docs, Calligra Sheets, and NeoOffice (which started as a Mac-oriented fork from OpenOffice.org). It is likely that these products can read and write ODF 1.2 spreadsheets, although the compilers of this resource have not confirmed this. Comments welcome.

See ODF_family for information on the widespread adoption of the OpenDocument Format by governments for the exchange of editable documents for collaboration between government agencies and the public and other entities.

    Licensing and patents No concerns. See ODF_Family.
Transparency

The structure and text of an ODS file are all represented in XML and hence viewable without special tools, although XML-aware tools that can show the element hierarchy make viewing and interpretation more convenient. The most commonly used parts, elements, and attributes have recognizable names. Simple documents can be interpreted with very basic tools. However, interpreting the semantics of some elements and the correspondence of some elements and attributes to spreadsheet terminology or functionality will require not only understanding of the schema and the specification text, but familiarity with the associated terminology and functionality.

Self-documentation

As for other members of the ODF 1.2 family, ODF_spreadsheet_1_2 added support for metadata based on RDF (W3C's Resource Description Framework). As well as using RDF for metadata for the document package as a whole, RDF can be attached to elements within the document's content. The use of "custom" metadata as specified in ODF 1.1 is deprecated in ODF 1.2.

Pre-defined metadata elements for the document as a whole, stored in an <office:meta> element include:

  • From the Dublin core namespace, using the dc: prefix: Title, Creator (of most recent modification), Description, Subject, Date (last modified), Language
  • From the ODF specification, using the meta: prefix: Generator (creating software application), Keywords, Initial Creator, Creation Date and Time, Modification Date and Time, Print Date and Time, Document Template, Document Statistics (word count, page count, etc.),

The pre-defined elements are all optional and repeatable. However, applications are not required to update multiple occurrences in a specific way to reflect modifications to a document.

Also supported in both ODF 1.1 and ODF 1.2 is an XML structure for user-defined metadata, based on triplets of name, data type, and value.

External dependencies

Depends on features used. Spreadsheets may include links to externally stored data. Macros can be included.

Technical protection considerations Encryption is supported for files within an ODF 1.2 package. In addition, an ODF package file may be encrypted during interchange or as part of DRM controlling distribution.

Quality and functionality factors Explanation of format description terms

Text
Normal rendering

No specific set of factors for assessing quality and functionality of a spreadsheet format has been developed. This format description uses selected factors for assessing formats for text and datasets.

Some spreadsheets have a printable or viewable report as a primary function. Textual content in cells in ODF_spreadsheet_1_2 worksheets is conveniently extractable for quotation and for indexing. Full support for Unicode.

Integrity of document structure The semantic structure of formulas and their relationship to cells with values is fully represented. Rectangular areas within a worksheet can be identified as tables, with labels for rows and columns.
Integrity of layout and display Excellent support for layout choices. Represents entire layout and formatting as intended by an author who used an application for which ODF_spreadsheet_1_2 is a native format. Differences in detail can occur on display if the original fonts used are not available in the system used for viewing. Charts based on data in the spreadsheet are considered separate objects, which can be positioned on a sheet.
Support for mathematics, formulae, etc.

Equations or formulas can be represented visually in ODF-spreadsheet_1_2 using MathML. The MathML object is a separate object, which can be positioned on a page like a chart.

Functionality beyond normal rendering As a format designed for creating and editing a spreadsheet, ODF_spreadsheet_1_2 stores information associated with the process of creation and review of spreadsheets, such as comments. Also supported are embedded media objects in binary formats, and links to external media objects, such as images, audio, or video.
Dataset
Normal functionality

ODF_spreadsheet_1_2 does not support the range of stored types as typical in database applications and programming languages, e.g., to distinguish integers from floating point numbers. It uses a limited set of data types for values in cells. The only pure numeric type is float. The OpenFormula specification (ODF 1.2: Part 2) does not prescribe a level of numeric precision, commenting that,"Formulas define the correct result, and not the algorithm for calculation. Since computing systems have limited precision and range of numbers, some functions cannot or should not be naively implemented as their formulas suggest. This specification defines the mathematically correct answer, and allows implementors to choose the best algorithm that will meet that definition." Most spreadsheet software has a practical limit of 15 decimal digits. This is insufficient for some forms of statistical analysis (see references below).

Cells with numbers that are intended as percentages or currency values have distinct types. Types and syntax for dates and times are based on XML Schema types. There is a single string type for text. There is also a boolean type with permitted values true and false.

Support for software interfaces (APIs, etc.)

There is no API specifically aimed at the use of ODF_spreadsheet_1_2 to hold a dataset. See Notes on Relationship to CSV for discussion of widely used support for extracting data from a spreadsheet for use in statistical software.

Data documentation (quality, provenance, etc.) ODF 1.2 packages have no specific support for rich discipline-specific metadata or codebooks. See Self-documentation in Sustainability Factors above.
Beyond normal functionality An ODF spreadsheet document can hold not only raw data, but also formulas that perform calculations on that data and present results as numbers or in graphical form. It can also include: macros; visual mathematical representation of equations; and charts based on the spreadsheet data.

File type signifiers and format identifiers Explanation of format description terms

Tag Value Note
Filename extension ods
.ods is the extension used for a spreadsheet file.
Internet Media Type application/vnd.oasis.opendocument.spreadsheet
 
Magic numbers See note.  Magic numbers that apply to ODF document category subtypes incorporate the magic number for ZIP_PK, the string mimetype at position 30, and the MIME subtype string value at position 38.
Pronom PUID fmt/295
For OpenDocument Spreadsheet 1.2. See https://www.nationalarchives.gov.uk/pronom/fmt/295.
Wikidata Title ID Q27203789
For OpenDocument Spreadsheet, version 1.2. See https://www.wikidata.org/wiki/Q27203789.
Tag Value Note
Filename extension ots
The extension .ots is used for a spreadsheet document used as a template.
Internet Media Type application/vnd.oasis.opendocument.spreadsheet-template
 

Notes Explanation of format description terms

General

Formulas in ODF 1.2: A cell containing a formula has a table:formula attribute. The value of the attribute is a string representing the formula in some syntax, preceded by a namespace prefix that identifies the specification with which the formula complies. If no namespace prefix is supplied, the namespace identified by "urn:oasis:names:tc:opendocument:xmlns:of:1.2" is assumed. This identifies the specification in Part 2 of the ODF 1.2 standard, often termed OpenFormula, and given the namespace prefix of:. Other prefixes that might be found include: msoxl:, used by Microsoft for its support of ODF spreadsheets; and oooc:. the namespace prefix used by OpenOffice.org before the development of OpenFormula, and adopted by various spreadsheet applications that built on the OpenOffice.org code. However, conformance with the ODF 1.2 specification requires that 'All namespace prefixes used in the values of table:formula attributes values shall be bound to the "urn:oasis:names:tc:opendocument:xmlns:of:1.2" namespace.' This means that strict compliance with ODS 1.2 requires the OpenFormula syntax and function set. The ability to use alternative namespaces permits extensions to OpenFormula to be deployed and tested in applications.

The OpenFormula specification in Part 2 of the ODF 1.2 specification was developed by the OASIS: OpenDocument - Formula Sub-Committee, which included active participation by both developers of OpenOffice and staff from Microsoft. The specification includes a formula grammar (syntax), a processing model for formula evaluation, and three sets of functions, allowing alternative levels of support for software libraries for evaluation of formulas. The three levels/profiles are known as the "small group", the "medium group", and the "large group." The compilers of this resource have not found that applications declare which group they support, but suspect that the major applications aim to fulfil the requirements of a large group evaluator. Comments welcome.

Typically, an ODF formula follows the namespace prefix with an equals ('=') sign. This may be followed by a second '=' as a “forced recalculate” marker, followed by an expression. If the second '=' is present, then the expression should be recalculated whenever a cell or other predecessor it depends on changes. Expressions can include the following basic components: constant numbers, constant text, operators, function calls, cell references including named ranges, in addition to advanced features such as quoted labels (defining rows or columns), named expressions, and inline arrays. Cell references in OpenFormula expressions start with '[' and end with ']'.

Software for conversion between formulas as specified for OOXML spreadsheets and OpenFormula formulas was developed collaboratively as part of a more general translator between OOXML and ODF. See How the OpenXML/ODF Translator Deals with Formulas.

Relationship to CSV: The CSV format is a simple textual format for rectangular datasets. Rows represent observations and columns represent the variables measured. The first row may hold labels for the variables. Conversion from CSV to ODS is straightforward and offered as an import feature by most spreadsheet applications. One caveat with import is that CSV imposes no limits to the precision of numeric value that can be represented. If numbers in a CSV file have more than 15 digits, the values will likely be rounded on conversion to ODS. If an ODS worksheet consists simply of data values where rows represent observations and columns represent variables, export/conversion to CSV is straightforward and widely offered by spreadsheet applications. If the top row gives variable names it will typically be exported appropriately. However, the exported file does not distinguish between raw data and calculated values. This loss of semantics may be significant in some contexts.

LibreOffice extensions to ODF standard versions: LibreOffice explains its intermittent use of ODF 1.x Extended as the recommended version for saving ODF files at LibreOffice ODF Extensions. Extensions are considered for moving into the next update to the ODF standards. LibreOffice lists extensions at LibreOffice: Development/ODF Implementer Notes/List of LibreOffice ODF Extensions and LibreOffice: Development/ODF Implementer Notes/List of LibreOffice OpenFormula Extensions. As OpenFormula was developed, a large number of OpenFormula extensions were in the 'COM.MICROSOFT' namespace and specifically supported interoperability between LibreOffice and Microsoft Excel.

Microsoft Excel support for ODF 1.2 files: According to [MS-OFFDI] Microsoft Office File Format Documentation Introduction, support for ODF 1.2 was introduced in Office 2013. Also, starting with Excel 2013, ODS 1.1 files opened and modified will be saved as ODS 1.2 files.

Excel 2013 and 2016 handling in ODF 1.2 files of formulas in different namespaces is described in [MS-OODF3]: 2.1.820 Part 1 Section 19.642, table:formula in discussion of the attribute table:formula, when contained within the element <table:table-cell>, itself contained within a parent <office:spreadsheet \ table:table-row>.

  • When saving the table:formula attribute, Excel precedes the formula string with the 'of' namespace and saves a formula string that follows the OpenFormula specification (ODF 1.2 Part 2).
  • Formulas using the 'of' namespace and those without a namespace are expected to follow the OpenFormula specification. Formulas using the 'msoxl' namespace follow [ISO/IEC-29500-1] section 18.17, except that workbook-names are written as literal values.
  • When loading the attribute table:formula, Excel first looks at the namespace. If the namespace is 'of', 'msoxl', or not specified, Excel will attempt to load the value of table:formula as a formula. When loading the table:formula attribute, if the namespace is unknown, the table:formula attribute is not loaded, and the value “office:value” is used instead. Hence the value displayed corresponds to the most recent value calculated, but the cell contains a static value rather than a formula that can be re-calculated. If the result of the formula is an error, Excel loads the <text:p> element and maps it to an Error data type. Error data types that Excel does not support are mapped to #VALUE!.
History

ODF 1.2 was approved by ISO/IEC JTC1/SC34/WG6 in September 2014 and published as an ISO/IEC standard in June 2015. ODF 1.2 introduced several substantive extensions, for digital signatures, for RDF-based metadata, and OpenFormula for spreadsheet formulas.

The most substantial difference between ODF_spreadsheet_1_1 (ODS 1.1) and ODF_spreadsheet_1_2 (ODS 1.2) is the addition of the OpenFormula specification, which defines a syntax for cell formulas and rules for evaluation. As a result, ODS 1.2 resolves many of the interoperability problems associated with ODS 1.1.

See ODF_package_1_2 for discussion of changes to ODF packages between versions 1.1 and 1.2.

ODF 1.3 was approved as an OASIS Committee Specification in December 2020, according to a December 4, 2020 announcement. This followed several periods of public review in 2019 and 2020. The next stage in the multi-step OASIS process is to gather three "statements of use", written statements that a party has successfully used or implemented the specification. See Approval of an OASIS Standard.

The specification for ODF 1.3 has been re-organized into four Parts. Part 1 is a brief introduction; Part 2 is the Packages specification; Part 3 defines the OpenDocument Schema, which includes specifications for the ODF content subtypes, including charts; and Part 4 defines the Recalculated Formula (OpenFormula) Format. The specification for spreadsheet content is in subclause 3.7 and clause 9 of ODF 1.3, Part 3, with the specification for formulas in ODF 1.3, Part 4. Since spreadsheet documents can include tables, charts, and database reports, clauses 9, 11, and 12 of Part 3 are also relevant. Clause 16 covers styles. Judging from the change logs in Appendix G of ODF 1.3, Part 3, most of the changes that might relate to spreadsheets are corrections and clarifications aimed at improving interoperability across implementations. The change log in Appendix A of ODF 1.3 Part 4 also consists mainly of corrections and clarifications to ensure consistent interpretations of functions in LibreOffice, Excel, and Gnumeric.

See ODF_family for more on the history of ODF in general.


Format specifications Explanation of format description terms


Useful references

URLs


Last Updated: 12/21/2020