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

Blog TOC 

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.0encoding=UTF-8standalone=yes?>
<tablexmlns=http://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.0encoding=UTF-8standalone=yes?>
<worksheetxmlns=http://schemas.openxmlformats.org/spreadsheetml/2006/main
           xmlns:r=http://schemas.openxmlformats.org/officeDocument/2006/relationships
           xmlns:mc=http://schemas.openxmlformats.org/markup-compatibility/2006
           mc:Ignorable=x14ac
           xmlns:x14ac=http://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.0encoding=UTF-8standalone=yes?>
<sstxmlns=http://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 (2)

  1. Ali says:

    Hi, i´m working on markup tables with xml, do you know how could markup a combined cell?

  2. Roman says:

    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

Skip to main content