How to determine which sheet contains a table

Here is a blog post that describes table markup in detail.  You can understand exactly how table markup works by reading the post:

blogs.msdn.com/.../table-markup-in-open-xml-spreadsheetml.aspx

From the post:

>> The worksheet, sheet1, contains a relationship to the table part.  This is how we know that the table is in sheet1.

So what you need to do is to iterate through all sheets, looking for a relationship to the table in question.  When you find the relationship, you then know the sheet.

-Eric

Finding the actual name of a worksheet containing a table is actually way more difficult than it should be. Iterating through the worksheets is easy enough and finding each of those worksheets' tables is also straight-forward, but the problem arises that once a table is found, getting the name of the sheet or digging out any correlation between sheet and table is really counter-intuitive.

The way I've done it is a bit of a hack (and I'd love to be shown a better approach), but here is one way to get the sheet name for each table:

            using (SpreadsheetDocument xl = SpreadsheetDocument.Open(FILENAME, true))
            {
                var wbPart = xl.WorkbookPart;
 
                foreach (WorksheetPart sheet in wbPart.WorksheetParts)
                {
                    foreach (TableDefinitionPart t in sheet.TableDefinitionParts)
                    {
                        Console.WriteLine(t.Table.DisplayName);
                        Console.WriteLine(sheet.Uri.OriginalString.Split(new char[] { '/' }).Last().Replace(".xml", ""));
                    }
                }
            }

Is this the relationship part?

 <tableParts count="1">

   <tablePart r:id="rId1"/>

 </tableParts>

I still don't see how the worksheet indicates which tables it contains, sorry.

After reading Eric White's blog post and looking at Phil's reply and doing some trial and error I discovered that by using a table's Uri I could match it in a worksheetpart and then use that worksheet's Uri to get the id of the sheet containing the table.  Anyway, it may not be pretty but it works::

       public static Sheet GetTableSheet(string filename, string tableName)

       {

           Sheet sheet = null;

           using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(filename, true))

           {

               var workbookPart = spreadsheet.WorkbookPart;

               // Get the table

               Table table =

                   (from worksheetPart in workbookPart.WorksheetParts

                    from t in worksheetPart.TableDefinitionParts

                    where t.Table.Name == tableName

                    select t.Table)

                   .FirstOrDefault();

               // Get id of the sheet containing the table

               string targetId =

                   // Find the table's Uri in a worksheet part to get the worksheet's Uri

                   (from worksheetPart in workbookPart.WorksheetParts

                       from wsPart in worksheetPart.Parts

                       where (wsPart.OpenXmlPart is TableDefinitionPart &&

                           wsPart.OpenXmlPart.Uri.OriginalString.Equals(table.TableDefinitionPart.Uri.OriginalString))

                       // Find the worksheet's Uri in a workbook part to get the id of the Sheet

                       from wbPart in workbookPart.Parts

                       where (wbPart.OpenXmlPart is WorksheetPart &&

                           wbPart.OpenXmlPart.Uri.OriginalString.Equals(worksheetPart.Uri.OriginalString))

                       select wbPart.RelationshipId)

                       .FirstOrDefault();

               // Get the sheet containing the table

               sheet =

                   workbookPart.Workbook.Descendants<Sheet>()

                   .Where(s => s.Id == targetId)

                   .FirstOrDefault();

           }

           return sheet;

       }