Metadata

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:

<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. ]

Note: