Document Interop Initiative

Table of Contents

1. Excel 1

1.1. Additional Parts. 1

1.1.1. Excel Attached Toolbars Part 1

1.1.2. Worksheet Sort Map Part 3

1.2. Microsoft Office Excel Sortmap. 3

1.2.1. Sortmap Elements. 3

1.2.2. Simple Types. 8

1.3. Additional SpreadsheetML Schema. 9

1.3.1. Formulas. 9

1. Excel

This section contains additional information used to more fully describe how Excel implements ECMA-376 Office Open XML File Formats[ECMA-376].

The subclauses subordinate to this one describe in detail each of the part types specific to Excel in addition to those listed as part of ECMA-376 Office Open XML File Formats ("[ECMA-376] Part 1 §12; SpreadsheetML").

1.1.1. Excel Attached Toolbars Part

Content Type:	application/vnd.ms-excel.attachedToolbars
Root Namespace:	Not applicable
Source Relationship:	http://schemas.microsoft.com/office/2006/relationships/attachedToolbars

This binary part supports the storage of custom toolbars created by the user.

A package shall contain at most one Attached Toolbars part, and that part shall be the target of an implicit relationship from the Workbook part ("[ECMA-376] Part 1 §12.3.23; Workbook Part").

[Example: The following Workbook part-relationship item contains a relationship to the Attached Toolbars part, which is stored in the ZIP item attachedToolbars.bin:

end example]

An Attached Toolbars part shall be located within the package containing the source relationship (expressed syntactically, the TargetMode attribute of the Relationship element shall be Internal).

An Attached Toolbars part shall not have implicit or explicit relationships to any part defined by ECMA-376 Office Open XML File Formats[ECMA-376].

The internal structure of the ZIP item attachedToolbars.bin is specified in the XCB section of the Excel 97-2003 Binary File Format (.xls) Structure Specification[MS-XLS] book.

1.1.2. Worksheet Sort Map Part

Content Type:	application/vnd.ms-excel.wsSortMap+xml
Root Namespace:	http://schemas.microsoft.com/office/excel/2006/main
Source Relationship:	http://schemas.microsoft.com/office/2006/relationships/wsSortMap

An instance of this part contains information about a series of before and after row or column mapping to resolve different sort operations performed on the same range by different users when the workbook is shared.

A package is permitted to contain zero or one Worksheet Sort Map part for each worksheet, and that part shall be the target of an implicit relationship from the Worksheet part ("[ECMA-376] Part 1 §12.3.24; Worksheet Part").

[Example: The following Worksheet part-relationship item contains a relationship to the Worksheet Sort Map part, which is stored in the ZIP item wsSortMap1.xml:

end example]

The root element for a part of this content type shall be worksheetSortMap.

[Example: This Worksheet Sort Map part results from a row sort on a range containing 3 rows:

end example]

A Worksheet Sort Map part shall be located within the package containing the source relationship (expressed syntactically, the TargetMode attribute of the Relationship element shall be Internal).

A Worksheet Sort Map part shall not have implicit or explicit relationships to any part defined by ECMA-376 Office Open XML File Formats [ECMA-376].

1.2. Microsoft Office Excel Sortmap

This part specifies a Sort Map used by Excel to recalculate a shared workbook. This part is present in files created by Excel 2007 SP1 and later.

1.2.1. Sortmap Elements

This subclause specifies the elements and simple types that comprise the Sort Map.

A sort map is used in the context of a shared workbook to record the sort order of a range of cells. This information is used to correctly update cells and formulas that depend on the sort order when revision information is applied. There is one sort map per worksheet.

The sort map consists of two components: The row sort map and the column sort map. The row sort map contains a list of row indices that need to be adjusted when revision information is applied. The row sort map is created when a range of cells is sorted top down or bottom up. The column sort map contains a list of column indices that need to be adjusted when revision information is applied. The column sort map is created when a range of cells is sorted left to right or right to left.

[Example: Consider the following shared workbook:

The worksheet contains four rows of data. Once the data is sorted in descending order, the worksheet looks as follows:

When this worksheet is saved, the following sort map should be created and saved to the file:

In the context of this example, the sort map implies that the contents of the second row should be replaced with the contents of the fifth row; contents of the third row should be replaced with the contents of the fourth row; and so on.

Now consider the following worksheet:

This worksheet contains four columns of data. Once the data is sorted left to right in descending order, the worksheet looks as follows:

When this worksheet is saved, the following sort map should be created and saved to the file:

In the context of this example, the sort map implies that the contents of the first column should be replaced with the contents of the fourth column; contents of the second column should be replaced with the contents of the third column; and so on.

End Example]

1.2.1.1. col (Column)

This element represents a single column mapping in the sort map.

Parent Elements

colSortMap (§1.2.1.2)

Attributes

Description

newVal (New Value)

Specifies the zero-based index of the column or row that contains data after a sort operation.

In the context of a row element, the value of this attribute shall be less than 1048576.

In the context of a col element, the value of this attribute shall be less than 16384.

The possible values for this attribute are defined by the XML Schema unsignedInt datatype.

oldVal (Old Value)

Specifies the zero-based index of the column or row that contains data before a sort operation.

In the context of a row element, the value of this attribute shall be less than 1048576.

In the context of a col element, the value of this attribute shall be less than 16384.

The possible values for this attribute are defined by the XML Schema unsignedInt datatype.

The following XML Schema fragment defines the contents of this element:

</complexType>

1.2.1.2. colSortMap (Column Sort Map)

This element represents the list of column mappings of the sort map.

Parent Elements

worksheetSortMap (§1.2.1.5)

Child Elements	Subclause
col (Column)	§1.2.1.1

Attributes

Description

count (Count)

Specifies the number of col elements. The value of this attribute shall match the number of col elements.

The possible values for this attribute are defined by the ST_SortMapCount simple type (§1.2.2.1).

ref (Reference)

Specifies the row and column bounds of the sort map.

The possible values for this attribute are defined by the ST_Ref simple type ("[ECMA-376] Part 4 §3.18.64").

The following XML Schema fragment defines the contents of this element:

</sequence>

</complexType>

1.2.1.3. row (Row)

This element represents a single row mapping in the sort map.

Parent Elements

rowSortMap (§1.2.1.4)

Attributes

Description

newVal (New Value)

Specifies the zero-based index of the column or row that contains data after a sort operation.

In the context of a row element, the value of this attribute shall be less than 1048576.

In the context of a col element, the value of this attribute shall be less than 16384.

The possible values for this attribute are defined by the XML Schema unsignedInt datatype.

oldVal (Old Value)

Specifies the zero-based index of the column or row that contains data before a sort operation.

In the context of a row element, the value of this attribute shall be less than 1048576.

In the context of a col element, the value of this attribute shall be less than 16384.

The possible values for this attribute are defined by the XML Schema unsignedInt datatype.

The following XML Schema fragment defines the contents of this element:

</complexType>

1.2.1.4. rowSortMap (Row Sort Map)

This element represents a list of row mappings of the sort map.

Parent Elements

worksheetSortMap (§1.2.1.5)

Child Elements	Subclause
row (Row)	§1.2.1.3

Attributes

Description

count (Count)

Specifies the number of row elements. The value of this attribute shall match the number of row elements.

The possible values for this attribute are defined by the ST_SortMapCount simple type (§1.2.2.1).

ref (Reference)

Specifies the row and column bounds of the sort map.

The possible values for this attribute are defined by the ST_Ref simple type ("[ECMA-376] Part 4 §3.18.64").

The following XML Schema fragment defines the contents of this element:

</sequence>

</complexType>

1.2.1.5. worksheetSortMap (Worksheet Sort Map)

This element represents the row and column sort mappings associated with a worksheet.

Child Elements	Subclause
colSortMap (Column Sort Map)	§1.2.1.2
rowSortMap (Row Sort Map)	§1.2.1.4

The following XML Schema fragment defines the contents of this element:

</sequence>

</complexType>

1.2.2. Simple Types

This is the complete list of simple types in the http://schemas.microsoft.com/office/excel/2006/main namespace.

1.2.2.1. ST_SortMapCount (Sort Map Count)

This simple type represents the count of row or column sort map entries.

This simple type's contents are a restriction of the XML Schema unsignedInt datatype.

This simple type also specifies the following restrictions:

· This simple type has a maximum value of less than or equal to 536870910.

Referenced By

colSortMap@count (§1.2.1.2); rowSortMap@count (§1.2.1.4)

The following XML Schema fragment defines the contents of this simple type:

</restriction>

</simpleType>

1.3. Additional SpreadsheetML Schema

The subordinate clauses define additional information in the SpreadsheetML schema used by Excel.

1.3.1. Formulas

1.3.1.1. Structure References

A structure reference has the following form:

structure-reference=

[table-identifier], intra-table-reference ;

table-identifier=

[book-prefix], table-name ;

table-name=

name ;

intra-table-reference=

spaced-lbracket, inner-reference, spaced-rbracket |

keyword |

'[', [simple-column-name], ']' ;

inner-reference=

keyword-list |

[keyword-list, spaced-comma], column-range ;

keyword=

"[#All]" | "[#Data]" | "[#Headers]" | "[#Totals]" | "[#This Row]" ;

keyword-list=

keyword |

"[#Headers]", spaced-comma, "[#Data]" |

"[#Data]", spaced-comma, "[#Totals]" ;

column-range=

column, [":", column] ;

column=

simple-column-name |

spaced-lbracket, {space}, simple-column-name, {space}, spaced-rbracket ;

simple-column-name=

[any-nospace-column-character, {any-column-character }], any-nospace-column-character ;

escape-column-character=

"'" | "[" | "]" | "#" ;

any-column-character=

character - escape-column-character |

"'", escape-column-character ;

any-nospace-column-character=

any-column-character – space ;

spaced-comma=

[space], ",", [space] ;

spaced-lbracket=

"[", [space] ;

spaced-rbracket=

[space], "]" ;

Structure references define rectangular sections of tables using special syntax, table column names, and reserved keywords instead of relative or absolute references. Structure references do not need adjustment if tables they refer to have been modified. They also provide a mechanism to define areas using meaningful table column names instead of less helpful cell references.

table-name is the name of the table the structure reference refers to. If it is missing, then the formula containing the structure reference must be entered into a cell that belongs to a table, and that table’s name is used as the table-name. table-name must be a name of a table. It must not be any other user-defined name.

table-name[] refers to all cells in table-name except Header Row and Total Row.

table-name[#Data] refers to all table-name’s cells except Header Row and Total Row. It is equivalent to the form table-name[].

table-name[#Headers] refers to all cells in table-name’s Header Row.

table-name[#Total Row] refers to all cells in the table-name’s Total Row

table-name[#All] refers to the entire table area. table-name[#All] is the union of table-name[#Headers], table-name[#Data], and table-name[#Total Row]

table-name[column-name] refers to all cells in the column named column-name except the cells from Header Row and Total Row.

table-name[[column-name]] refers to all cells in the column named column-name except the cells from Header Row and Total Row.

table-name[[column-name1]:[column-name2]] refers to all cells from column named column-name1 through column named column-name2 except the cells from Header Row and Total Row.

table-name[[keyword],[column-name]], where keyword is one of #Headers, #Total Row, #Data, #All, refers to the intersection of the area defined by table-name[keyword] and all cells from the column named column-name.

table-name[[#Data],[column-name]] is equivalent to table-name[column-name]

table-name[[keyword],[column-name1]:[column-name2]], where keyword is one of #Headers, #Total Row, #Data, #All, refers to the intersection of the area defined by table-name[keyword] and all cells from the table from column named column-name1 through column named coumn-name2.

table-name[[#Headers],[#Data],[column-name]] is the union of table-name[[#Headers],[column-name]] and table-name[[#Data],[column-name]]

table-name[[#Data],[#Total Row],[column-name]] is the union of table-name[[#Data],[column-name]] and table-name[[#Total Row],[column-name]]

table-name[[#Headers],[#Data],[column-name1]:[column-name2]] is the union of table-name[[#Headers], [column-name1]:[column-name2]] and table-name[[#Data], [column-name1]:[column-name2]]

table-name[[#Data],[#Total Row], [column-name1]:[column-name2]] is the union of table-name[[#Data], [column-name1]:[column-name2]] and table-name[[#Total Row], [column-name1]:[column-name2]]

table-name[[#This Row],[column-name]] refers to the cell in the intersection of table-name[column-name] and the current row; for example, the row of the cell that contains the formula with the structure reference. table-name[[#This Row],[column-name1]:[column-name2]]refers to the cells in the intersection of table-name[[column-name]:[column-name2]] and the current row; for example, the row of the cell that contains the formula with such structure reference. These two forms allow formulas to perform implicit intersection using structure references.

#This Row must not be combined with any of #Headers, #Total Row, #Data, #All.

1.3.1.2. Conditional Formatting Formulas

Conditional formatting formulas are part of conditional formatting rules.

A conditional formatting formula shall not use array constants.

A conditional formatting formula shall not use structure references.

A conditional formatting formula shall not use union and intersection binary operators.

A conditional formatting formula shall not use 3-D references.

CF-formula=

CF-expression ;

CF-expression= {space}, CF-nospace-expression, {space} ;

CF-nospace-expression=

"(", CF-expression, ")" |

CF-constant |

prefix-operator, CF-expression |

CF-expression, CF-infix-operator, CF-expression |

CF-expression, postfix-operator |

A1-reference |

CF-function-call |

name ;

CF-constant=

constant – array-constant ;

CF-infix-operator=

":" | "^" | "*" | "/" | "+" | "-" | "&"|

"=" | "<>" | "<" | "<=" | ">" | ">=" ;

CF-function-call=

CF-function-name, "(", CF-function-argument-list, ")" ;

CF-function-name=

predefined-function-name |

name ;

CF-function-argument-list =

[CF-expression], { comma, CF-function-argument-list } ;

1.3.1.3. Data Validation Formulas

Data validation formulas are part of Data Validation rules. They shall not use array constants, structure references, or union or intersection binary operators.

DV-formula=

DV-expression ;

DV-expression= {space}, DV-nospace-expression, {space} ;

DV-nospace-expression=

"(", DV-expression, ")" |

DV-constant |

prefix-operator, DV-expression |

DV-expression, DV-infix-operator, DV-expression |

DV-expression, postfix-operator |

A1-reference |

function-call |

name ;

DV-constant=

constant – array-constant ;
DV-infix-operator=

":" | "^" | "*" | "/" | "+" | "-" | "&"|

"=" | "<>" | "<" | "<=" | ">" | ">=" ;

DV-function-call=

DV-function-name, "(", DV-function-argument-list, ")" ;

DV-function-name=

predefined-function-name |

name ;

DV-function-argument-list=

[DV-expression], { comma, DV-function-argument-list } ;

1.3.1.4. Name Formulas

If a name is defined as an alias for a formula then that formula shall contain 3-D references only.

name-formula=

name-expression ;

name-expression= {space}, name-nospace-expression, {space} ;

name-nospace-expression=

"(", name-expression, ")" |

constant |

prefix-operator, name-expression |

name-expression, infix-operator, name-expression |

name-expression, postfix-operator |

external-cell-reference |

"!", A1-reference |

name-function-call |

name-reference |

"!", name |

structure-reference ;

name-function-call=

function-name, "(", name-function-argument-list, ")" ;

name-function-argument-list=

[name-expression], { comma, name-function-argument-list } ;

1.3.1.5. External Name Formulas

External name formulas are restricted to cell references within the same external book.

external-name-formula=

["="], external-name-expression ;

external-name-expression=

same-book-prefix, A1-reference |

external-name-error-constant ;

same-book-prefix= same-book-single-sheet, "!" |

same-book-sheet-range, "!" ;

same-book-single-sheet= sheet-name |

apostrophe, sheet-name-special, apostrophe ;

same-book-sheet-range= sheet-name, ":", sheet-name |

apostrophe, sheet-name-special, ":", sheet-name-special, apostrophe ;

external-name-error-constant=

"#REF!" ;

1.3.1.6. Chart formulas

Chart formulas define data ranges and values used by charts, such as chart ranges, series ranges, series titles, and axis titles.

A chart formula shall be either a 3-D reference or a constant.

chart-formula=

chart_ref |

"(", chart-ref, ",", chart-ref, {",", chart-ref}, ")"

chart_ref =

external-cell-reference |

external-name ;

1.3.1.7. R1C1 formulas

In R1C1 formulas, R1C1-style references shall be used instead of A1-style references.

An R1C1 formula has the following form:

R1C1-formula=

R1C1-expression ;

R1C1-expression=

{space}, nospace-R1C1-expression, {space} ;

nospace-R1C1-expression=

"(", R1C1-expression, ")" |

constant |

prefix-operator, R1C1-expression |

R1C1-expression, infix-operator, R1C1-expression |

R1C1-expression, postfix-operator |

R1C1-cell-reference |

R1C1-function-call |

name-reference |

structure-reference ;

R1C1-function-call=
function-name, "(", R1C1-argument-list, ")" ;

R1C1-argument-list=

R1C1-argument, { comma, R1C1-argument } ;

R1C1-argument=

[R1C1-expression] ;

R1C1-cell-reference=

[sheets-prefix] R1C1-reference ;

1.3.1.8. Pivot Formulas

Pivot formulas are used to define calculated items or calculated fields for PivotTables.

Pivot formulas shall not use user-defined names.

A pivot formula shall not use array constants.

A pivot formula shall not use cell references.

A pivot formula shall not use structure references.

A pivot formula shall not use calls to the following worksheet functions: GETPIVOTDATA, DCOUNT, DCOUNTA, DSUM, DAVERAGE, DMIN, DMAX, DPRODUCT, DSTDEV, DSTDEVP, DVAR, DVARP, INDEX, RAND, NOW, AREAS, ROWS, COLUMNS, OFFSET, CELL, INDIRECT, TODAY, INFO, and RANDBETWEEN.

A pivot formula shall not use calls to user-defined functions.

1.3.1.8.1. Calculated Field Formulas

A calculated field formula defines calculations using the contents of fields in a PivotTable.

The only names a calculated field formula shall use are names of fields defined within the PivotTable the formula is used for.

A calculated field formula has the following form:

pivot-field-formula=

pivot-field-expression ;

pivot-field-expression=

{space}, pivot-nospace-field-expression, {space} ;

pivot-nospace-field-expression=

"(", pivot-field-expression, ")" |

pivot-constant |

prefix-operator, pivot-field-expression |

pivot-field-expression, pivot-infix-operator, pivot-field-expression |

pivot-field-expression, postfix-operator |

pivot-field-function-call |

pivot-field-name ;

pivot-constant=

constant – array-constant ;
pivot-infix-operator=

"^" | "*" | "/" | "+" | "-" | "&"|

"=" | "<>" | "<" | "<=" | ">" | ">=" ;

pivot-field-function-call=

pivot-function-name, "(", pivot-field-function-argument-list, ")" ;

pivot-field-function-argument-list =

[pivot-field-expression], { comma, pivot-field-function-argument-list} ;

pivot-function-name=

predefined-function-name - pivot-function-invalid-name;

pivot-function-invalid-name=

pivot-field-name=

name – pivot-invalid-name |

"’" [pivot-field-string-chars] "’" ;

pivot-invalid-name=

"All" | "Blank" ;

pivot-field-string-chars=

pivot-field-string-char, { pivot-field-string-chars} ;

pivot-field-string-char=

"’’" |

character - "’" ;

1.3.1.8.2. Calculated Item Formulas

A calculated item formula defines calculations using the contents of items in a PivotTable.

The only names calculated item formula shall use are names of fields and items defined within the PivotTable the formula is used for.

A calculated item formula has the following form:

pivot-item-formula=

pivot-item-expression ;

pivot-item-expression= {space}, pivot-nospace-item-expression, {space} ;

pivot-nospace-item-expression=

"(", pivot-item-expression, ")" |

pivot-constant |

prefix-operator, pivot-item-expression |

pivot-item-expression, pivot-infix-operator, pivot-item-expression |

pivot-item-expression, postfix-operator |

pivot-item-function-call |

pivot-items ;

pivot-item-function-call=

pivot-function-name, "(", pivot-item-function-argument-list, ")" ;

pivot-item-function-argument-list=

[pivot-item-expression], { comma, pivot-item-function-argument-list} ;

pivot-items=

pivot_item, [ space, {space}, pivot-items] ;
pivot-item=

pivot-item-name |

pivot-field-name, "[", pivot-item-value, "]" ;

pivot-item-name=

pivot-field-name
pivot-item-value=

pivot-item-name |

[sign], whole-number-part

pivot-item refers to an item either by its name or by position.

pivot-item-name refers to an item by its name.

pivot-field-name [pivot-item-name] refers to an item by its field name and its own name. This notation shall be used to avoid #NAME? errors when two items in two different fields have the same name.

pivot-field-name[whole-number-part] refers to an item by its position in the PivotTable as currently sorted and displayed. The item referred to in this way can change whenever the positions of items change or different items are displayed or hidden. Hidden items are not counted in this index.

pivot-field-name [sign whole-number-part] refers to an item using relative positions. The positions are determined relative to the calculated item that contains the formula.

1.3.1.9. OLE Link Formulas

An OLE Link formula is a name-reference to an oleItem ("[ECMA-376] Part 4 §3.14.9; oleItem"). The name attribute of the oleItem element shall be "'" (apostrophe, 0x0027).

book-prefix, apostrophe, apostrophe, apostrophe, apostrophe ;

book-prefix refers to an externalLink ("[ECMA-376] Part 4 §3.14.8; externalLink") part containing the oleItem element.