How to: Get Worksheet Information from an Office Open XML Package by Using the Open XML API

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

The Office Open XML Package specification defines a set of XML files that contain the content and define the relationships for all of the parts stored in a single package. These packages combine the parts that make up the document files for Microsoft®® Office Excel® 2007, Microsoft Office PowerPoint® 2007, and Microsoft Office Word 2007. The Open XML Application Programming Interface (API) allows you to create packages and manipulate the files that make up the packages. This topic walks through the code and steps to retrieve information from a worksheet in an Office Open XML package in Office Excel 2007, although the steps are the same for each of the three 2007 Microsoft Office system programs that support the Office Open XML Format.

Note
The code samples in this topic are in Microsoft Visual Basic® .NET and Microsoft Visual C#®. You can use them in an add-in created in Microsoft Visual Studio® 2008. For more information about how to create an add-in in Visual Studio 2008, see Getting Started with the Open XML Format SDK 1.0.

Get Worksheet Information from an Office Open XML Package

In the following code, you retrieve a list containing the name and type of all sheets in a specified workbook contained in a SpreadsheetDocument document package:

' How to get worksheet information.
Public Function XLGetSheetInfo(ByVal fileName As String) As List(Of String)
    '  Fill this collection with a list of all the sheets.
    Dim sheets As List(Of String) = New List(Of String)
    Dim xlPackage As SpreadsheetDocument = SpreadsheetDocument.Open(fileName, false)
    Dim workbook As WorkbookPart = xlPackage.WorkbookPart
    Dim workbookstr As Stream = workbook.GetStream
    Dim doc As XmlDocument = New XmlDocument
    doc.Load(workbookstr)
    Dim nsManager As XmlNamespaceManager = New XmlNamespaceManager(doc.NameTable)
    nsManager.AddNamespace("default", doc.DocumentElement.NamespaceURI)
    Dim nodelist As XmlNodeList = doc.SelectNodes("//default:sheets/default:sheet", nsManager)
    For Each node As XmlNode In nodelist
        Dim sheetName As String = string.Empty
        sheetName = node.Attributes("name").Value
        sheets.Add(sheetName)
    Next
    Return sheets
End Function

// How to get worksheet information.
public List<string> XLGetSheetInfo(string fileName)
{
    //  Fill this collection with a list of all the sheets.
    List<string> sheets = new List<string>();

    using (SpreadsheetDocument xlPackage = SpreadsheetDocument.Open(fileName, false))
    {
        WorkbookPart workbook = xlPackage.WorkbookPart;
        Stream workbookstr = workbook.GetStream();
        XmlDocument doc = new XmlDocument();
        doc.Load(workbookstr);

        XmlNamespaceManager nsManager = new XmlNamespaceManager(doc.NameTable);
        nsManager.AddNamespace("default", doc.DocumentElement.NamespaceURI);
        XmlNodeList nodelist = doc.SelectNodes("//default:sheets/default:sheet", nsManager);

        foreach (XmlNode node in nodelist)
        {
            string sheetName = string.Empty;
            sheetName = node.Attributes["name"].Value;
            sheets.Add(sheetName);
        }
    }
    return sheets;
}

To retrieve a list containing the name and type of all sheets in a specified workbook contained in a SpreadsheetDocument document package

1. First, pass in parameters representing the path to and the name of the source Excel 2007 file.

2. Next, create a List object named sheets where you store the worksheet information.

3. Then, load the WorkbookPart part of the SpreadsheetDocument package to a new XmlDocument document named doc.

4. Next, you use an XPath expression and an XmlNodeList object to retrieve all the //default:sheets/default:sheet nodes of the XmlDocument document.

5. Then, you iterate the XmlNodeList object and add the name of the sheet to the list of sheets.

6. Finally, you return the list of sheets that contains the names of all the worksheets stored in a SpreadsheetDocument package.