|Introduction | Sustainability Factors | Content Categories | Format Descriptions | Contact|
|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.|
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:
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.|
|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.|
|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.|
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:
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."
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.|
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.
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:
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.
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.|
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.|
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.|
||.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.|
||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.|
||The extension .ots is used for a spreadsheet document used as a template.|
|Internet Media Type||application/vnd.oasis.opendocument.spreadsheet-template
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>.
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.