DocumentID: ECMA-376/Part4/3.9 Title: ECMA-376, Part4: 3.9 Metadata Extracted-From: ECMA-376 Office Open XML File Formats, 1st Edition / December 2006 Warning: Coverted to HTML format by a script known to have bugs
A cell in a spreadsheet application can have metadata associated with it. Metadata is just a set of additional properties about the particular cell, and this metadata is stored in the metadata xml part.
There are two types of metadata: cell metadata and value metadata. Cell metadata contains information about the cell itself, and this metadata can be carried along with the cell as it moves (insert, shift, copy/paste, merge, unmerge, etc). Value metadata is information about the value of a particular cell. Value metadata properties can be propagated along with the value as it is referenced in formulas.
The file format is architected such that it supports both value and cell metadata, as well as even allowing for future extensions. Formulas, such as CUBEMEMBER() or CUBE*, shall make use of value metadata as part of the SpreadsheetML standard. So, only value metadata must be implemented as it is used by MDX cube functions for retrieving data from OLAP data sources. The other parts are allowed for future extensibility.
See the informative material for background information on OLAP and the various CUBE* functions.
[Example: The CUBEMEMBER() function is used to return a specific member from an OLAP cube. The metadata will express the connection name (used as a friendly identifier for the external data connection to the OLAP server and cube), the mdx statement retrieving that member, and a set of operational attributes of the metadata that sepcify how it behaves in the spreadsheet application (i.e., whether it propagates through formula assignment, is able to be copy/pasted, etc).
<metadata xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<metadataTypes count="1">
<metadataType name="XLMDX" minSupportedVersion="120000" copy="1"
pasteAll="1" pasteValues="1" merge="1" splitFirst="1" rowColShift="1"
clearFormats="1" clearComments="1" assign="1" coerce="1"/>
</metadataTypes>
<metadataStrings count="2">
<s v="My Connection"/>
<s v="[Measures].[Internet Sales Amount]"/>
</metadataStrings>
<mdxMetadata count="1">
<mdx n="0" f="m">
<t c="1">
<n x="1"/>
</t>
</mdx>
</mdxMetadata>
<valueMetadata count="1">
<bk>
<rc t="1" v="0"/>
</bk>
</valueMetadata>
</metadata>
As seen above, the metadata string table contains two entries: the name of the connection (My Connection), and the expression that returns the Internet Sales Amount member from the cube. The metadataType specifies that the metadata persists with assignment, cell merging, copy/pasting, shifting rows/columns, when the formatting or comments are deleted from the cell, and is assigned to the upper left most cell if a merged cell is split. In the valueMetadata collection, the metadata block specifies that the first metadataType is used, and indexes the first (0th) entry in the mdxMetadata collection. This mdx element in the mdxMetadata collection in turn specifies the cube function type (m= cube member) and an index into the string table that specifies the connection name. It also contains a tuple (t) element which specifies, via index into the string table, which tuple is returned. end example]
[Note: When copying a cell with metadata, and the cell contains an array formula, each pasted cell shall contain the value from the corresponding position in the array and should contain the metadata corresponding to that cell. end note]