Table Markup in Open XML SpreadsheetML
Tables are a feature in Open XML spreadsheets that allow you to interact with data more like a database. You can select a region and convert it to a table. Most Excel aficionados are familiar with this feature. It is very useful.
This blog is inactive.
New blog: EricWhite.com/blog
In a previous post, Using LINQ to Query Excel Tables, I presented some code that allows you to write LINQ queries over Excel tables. The code that retrieves rows from the table is somewhat involved. To be blunt, in that code, I got it wrong. The code reported incorrect values for cells if you reordered, deleted, or inserted columns in the table. I’ve now corrected the code and replaced the attachment to that post, which is a zip file that contains the code, as well as sample spreadsheets.
This post presents a summary of the Open XML SpreadsheetML markup that you use to define or query a table. I’ll show the markup for the very simple table in the above screen-clipping.
We find the definitions for the table in a table part (following is a screen-clippings of the Visual Studio OPC Package Editor, which is an invaluable tool for exploring and editing Open XML documents):
The following listing shows the markup in that part. You can see that there are three columns. The tableColumn elements are in the same order as they occur in the table. The ref attribute of the table element contains the range that the table occupies.
<?xmlversion="1.0"encoding="UTF-8"standalone="yes"?>
<tablexmlns="https://schemas.openxmlformats.org/spreadsheetml/2006/main"
id="1"
name="Table1"
displayName="Table1"
ref="A1:C3"
totalsRowShown="0">
<autoFilterref="A1:C3"/>
<tableColumnscount="3">
<tableColumnid="1"
name="Name"/>
<tableColumnid="4"
name="Gender"/>
<tableColumnid="2"
name="Age"/>
</tableColumns>
<tableStyleInfoname="TableStyleMedium2"
showFirstColumn="0"
showLastColumn="0"
showRowStripes="1"
showColumnStripes="0"/>
</table>
The worksheet, sheet1, contains a relationship to the table part. This is how we know that the table is in sheet1.
The following listing contains the worksheet sheet1. The markup for the worksheet is basically the same as it would be for a sheet that didn’t contain a table.
<?xmlversion="1.0"encoding="UTF-8"standalone="yes"?>
<worksheetxmlns="https://schemas.openxmlformats.org/spreadsheetml/2006/main"
xmlns:r="https://schemas.openxmlformats.org/officeDocument/2006/relationships"
xmlns:mc="https://schemas.openxmlformats.org/markup-compatibility/2006"
mc:Ignorable="x14ac"
xmlns:x14ac="https://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
<dimensionref="A1:C3"/>
<sheetViews>
<sheetViewtabSelected="1"
workbookViewId="0">
<selectionactiveCell="E12"
sqref="E12"/>
</sheetView>
</sheetViews>
<sheetFormatPrdefaultRowHeight="15"
x14ac:dyDescent="0.25"/>
<cols>
<colmin="1"
max="1"
width="11"
customWidth="1"/>
<colmin="2"
max="2"
width="13"
customWidth="1"/>
<colmin="4"
max="4"
width="9.85546875"
customWidth="1"/>
</cols>
<sheetData>
<rowr="1"
spans="1:3"
x14ac:dyDescent="0.25">
<cr="A1"
t="s"> <!-- s indicates that the value, 0 in this case, is an index into the shared string table -->
<v>0</v>
</c>
<cr="B1"
t="s">
<v>2</v>
</c>
<cr="C1"
t="s">
<v>1</v>
</c>
</row>
<rowr="2"
spans="1:3"
x14ac:dyDescent="0.25">
<cr="A2"
t="s"> <!-- also an index into the shared string table -->
<v>3</v>
</c>
<cr="B2"
t="s">
<v>4</v>
</c>
<cr="C2">
<v>50</v> <!-- contains the actual value of the cell -->
</c>
</row>
<rowr="3"
spans="1:3"
x14ac:dyDescent="0.25">
<cr="A3"
t="s">
<v>5</v>
</c>
<cr="B3"
t="s">
<v>6</v>
</c>
<cr="C3">
<v>45</v>
</c>
</row>
</sheetData>
<pageMarginsleft="0.7"
right="0.7"
top="0.75"
bottom="0.75"
header="0.3"
footer="0.3"/>
<tablePartscount="1">
<tablePartr:id="rId1"/>
</tableParts>
</worksheet>
The following listing shows the shared string table. The values for cells that have type = string (the attribute t == “s”) are indexes into this string table.
<?xmlversion="1.0"encoding="UTF-8"standalone="yes"?>
<sstxmlns="https://schemas.openxmlformats.org/spreadsheetml/2006/main"
count="7"
uniqueCount="7">
<si>
<t>Name</t>
</si>
<si>
<t>Age</t>
</si>
<si>
<t>Gender</t>
</si>
<si>
<t>Eric</t>
</si>
<si>
<t>M</t>
</si>
<si>
<t>Cheryl</t>
</si>
<si>
<t>F</t>
</si>
</sst>
Comments
Anonymous
February 04, 2011
Hi, i´m working on markup tables with xml, do you know how could markup a combined cell?Anonymous
April 16, 2012
Hi! How to turn the table that would display the columns in the rows like Name Illan Alice Ivan Gender M W M Role Owner Consultant Director