Sustainability of Digital Formats: Planning for Library of Congress Collections |
|
![]() |
|
Introduction | Sustainability Factors | Content Categories | Format Descriptions | Contact |
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:
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. |
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. |
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).
|
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:
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. |
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. |
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 |
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>.
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>.
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. |
|