Tables

A table helps organize and provide structure to lists of information in a worksheet. Tables have clearly labeled columns, rows, and data regions. Tables make it easier for users to sort, analyze, format, manage, add, and delete information.

If a region of data is designated as a Table, then special behaviors can be applied which help the user perform useful actions. For example, if the user types additional data in the row adjacent to the bottom of the table, the table can expand and automatically add that data to the data region of the table. Similarly, adding a column is as easy as typing a new column heading to the right or left of the current column headings. Filter and sort abilities can automatically be surfaced to the user via the drop down arrows. Special calculated columns can be created which summarize or calculate data in the table. These columns have the ability to expand and shrink according to size of the table, and maintain proper formula referencing.

Tables can be created from data already present in the worksheet, from an external data query, or from mapping a collection of repeating XML elements to a worksheet range.

The sheet XML stores the numeric and textual data. The table XML records the various attributes for the particular table object.

[Example:

<table xmlns=http://schemas.openxmlformats.org/spreadsheetml/2006/5/main
id="1" name="MarginTable" displayName="MarginTable" ref="D3:G6"
totalsRowShown="0">

<autoFilter ref="D3:G6"/>
<tableColumns count="4">
<tableColumn id="1" name="Product"/>
<tableColumn id="2" name="Wholesale"/>
<tableColumn id="3" name="Retail"/>
<tableColumn id="4" name="Margin" dataDxfId="0">
<calculatedColumnFormula d="1">[Retail]-
[Wholesale]</calculatedColumnFormula>
</tableColumn>
</tableColumns>

<tableStyleInfo name="TableStyleMedium9" showFirstColumn="0"
showLastColumn="0" showRowStripes="1" showColumnStripes="0"/>
</table>

end example]

The above xml example shows a table that spans cells D3 through G6, and has four columns: Product, Wholesale, Retail, and Margin. Margin is a column where each cell has its values calculated based on the formula (Retail - Wholesale), where those values are taken from the cells in the table columns on the corresponding row. The table has a style applied, "TableStyleMedium9", but the styles formatting isn't applied to the first column and the column striping isn't shown. Note that all the data and text values are stored in the sheet xml; the table xml just stores the properties that are specific to this table, and it is referenced by the sheet.