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.1, ISO 26300:2006

>> 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.1. Part of OASIS Open Document Format for Office Applications, Version 1.1 and the equivalent ISO 26300:2006 (including 2012 Amendment 1).
Description

The OpenDocument Spreadsheet Document Format (ODS), Version 1.1 (given the short name ODF_spreadsheet_1_1 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.1 specification as published by OASIS. The ISO/IEC 26300:2006 specification is equivalent when modified by its 2012 amendment. 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 the later version.

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_PK 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_1, see ODF_package_1_1. 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 clause 17 of the ODF 1.1 specification defines the form for the package manifest, and support for 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 are required.
Relationship to other formats
    Subtype of ODF_Family, OpenDocument Format (ODF) Family, OASIS and ISO/IEC 26300
    Subtype of ODF_package_1_1, OpenDocument Package Format, ODF 1.1, ISO 26300:2006
    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 later version ODF_spreadsheet_1_2, OpenDocument Spreadsheet Document Format (ODS), Version 1.2, ISO 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.1. format have generated significant interoperability problems, considerably mitigated in ODF 1.2 with the incorporation of the OpenFormula specification.

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.1 specification published by OASIS in 2007. Also approved as part of the equivalent ISO/IEC 26300:2006 by ISO/IEC JTC1/SC34.
    Documentation

Specifications from OASIS: Open Document Format for Office Applications (OpenDocument) Version 1.1. Specification for ODF 1.1 spreadsheet documents are found primarily in chapter 8 of the specification. The technical specification is part of a normative RNG schema for primary component files for ODF documents.

An equivalent specification was adopted by ISO and published by means of ISO/IEC 26300:2006, Information technology -- Open Document Format for Office Applications (OpenDocument) v1.0 together with a 2012 amendment, ISO/IEC 26300:2006, Information technology -- Open Document Format for Office Applications (OpenDocument) v1.0 -- Amendment 1: Open Document Format for Office Applications (OpenDocument) v1.1. A number of technical corrigenda have been published.

Adoption

OpenOffice.org introduced support for ODF 1.0 in the beta version of OpenOffice 2.0 in 2005, according to Press Release for OpenOffice 2.0 beta. From Version 2.0 onward, OpenOffice.org used the standard ODF OpenDocument as its native format. Versions 2.0 through 2.3.0 defaulted to ODF 1.0; versions 2.3.1 through 2.4.3 defaulted to ODF 1.1; versions 3.0 onward defaulted to ODF 1.2. The version of OpenOffice.org at the time of the 2010 split between OpenOffice and LibreOffice was 3.3.

The adopters of ODF listed in the April 2009 registration of MIME type for ODF spreadsheet documents included: IBM (Lotus Symphony), KOffice (became Calligra), NeoOffice (which started as a Mac-oriented fork from OpenOffice.org), OpenOffice.org (predecessor to Apache OpenOffice and LibreOffice), StarOffice (predecessor of OpenOffice.org, code contributed to Apache OpenOffice), and TextMaker (now part of SoftMaker).

The major applications supporting ODF in 2020 can read and write spreadsheet documents as defined in ODF 1.1, although the default format is usually ODF 1.2 (or 1.2 Extended).

  • Apache OpenOffice offers users the ability to save documents as ODF 1.0/1.1.
  • LibreOffice also offers ODF 1.0/1.1 as a format for saving documents. Setting Up LibreOffice suggests choosing this option to share documents with people using editing software that supports only the older format.
  • Microsoft first introduced support for ODF_spreadsheet_1_1 with Service Pack 2 for Excel 2007. Support is also provided in Excel 2010, 2013, and 2016. Microsoft lists ODF (both ODF 1.1 and 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.
    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

ODF 1.1 support for metadata is through three mechanisms defined in clauses 2.2 (Document Metadata) and clause 3 (Metadata Elements) of the OASIS ODF 1.1 specification: predefined elements explicitly listed in the specification; user-defined metadata, using a specified XML structure for a triplet of name, data type, and value; and custom metadata, held in arbitrary elements within the <office:meta> element. The ODF 1.1 specification requires implementers to preserve custom metadata. Note that the use of arbitrary custom metadata was deprecated in ODF 1.2 and replaced by support for RDF-based metadata.

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.1 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_1 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_1 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_1 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_1 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_1 does not support the range of data 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 ODF 1.1 specification provides no information on numeric precision, but 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_1 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 packages have no specific support for rich discipline-specific metadata or codebooks in externally specified schemas. ODF 1.1 allows for "custom" metadata in arbitrary format, but this feature was deprecated in ODF 1.2 and is therefore not recommended. 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/294
For OpenDocument Spreadsheet 1.1. See https://www.nationalarchives.gov.uk/PRONOM/fmt/294.
Wikidata Title ID Q27203722
For OpenDocument Spreadsheet, version 1.1. See https://www.wikidata.org/wiki/Q27203722.
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.1: A cell containing a formula has a table:formula attribute. The value of the attribute is a string representing the formula in some syntax. ODF 1.1 does not prescribe the use of a particular syntax or provide a list of functions that can be included in formulas. The description of the attribute in the specification provides some constraints. A formula should begin with a namespace prefix, indicating the syntax and semantics used. Several spreadsheet implementations used the oooc: namespace prefix adopted by OpenOffice.org. However, some early implementations did not use a prefix, and Microsoft's first support for ODF (in Office 2007 Service Pack 2, released on April 28, 2009) introduced the prefix msoxl:. This variability led to some interoperability problems and many blog posts about this ODF shortcoming. See Useful references below. Meanwhile, an OASIS subcommittee, formed in February 2006, was developing the OpenFormula specification, intended for inclusion in version 1.2 of the ODF specification, eventually published by OASIS in September 2011. The emerging OpenFormula and ODF 1.2 specifications were tested in OpenOffice.org (starting with version 3.0 in Summer 2008) and other implementations. Use of OpenFormula within ODF 1.1 files is indicated by the of: namespace prefix. Software for conversion between OOXML 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.

Typically, an ODF formula follows the namespace prefix with an equals ('=') sign and can include the following components: numbers, text, named ranges, operators, function calls, or addresses of cells that contain numbers. Addresses in ODF formulas start with '[' and end with ']'. The ODF 1.1 specification provides details in subclause 8.3.1 as to how cells and ranges of cells should be addressed.

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.

Microsoft Excel support for ODF 1.1 files: According to [MS-OFFDI] Microsoft Office File Format Documentation Introduction, ODS 1.1 was introduced as a persistence format in Microsoft Office Excel 2007 Service Pack 2. Starting with Excel 2013, ODS 1.1 files opened and modified will be saved as ODS 1.2 files.

Excel 2007 SP2 handling in ODF 1.1 files of formulas in different namespaces is described in [MS-OODF]: 2.1.210 Section 8.1.3, Table Cell 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 2007 precedes the formula string with the "msoxl" namespace and saves a formula string that follows [ISO/IEC-29500-1] section 18.17, except that workbook-names are written as literal values.
  • When loading the attribute table:formula, Excel 2007 first looks at the namespace. If the namespace is “msoxl”, Excel 2007 will load the value of table:formula as a formula. If the namespace is missing or 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 recalculated. If the latest result of the formula was an error, Excel 2007 loads the <text:p> element and maps it to an Error data type. Error data types that Excel 2007 does not support are mapped to #VALUE!.

Excel 2010 and 2013 handling in ODF 1.1 files of formulas in different namespaces is described in [MS-OODF2]: 2.1.209 Section 8.1.3, Table Cell 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 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!

See also notes on conversion between XLSX and ODS in XLSX/OOXML_2012.

History

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 the ODF package between versions 1.1 and 1.2.

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