Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
How to: Get Worksheet Information from an Office Open XML Package by Using the Open XML API
Collapse the table of content
Expand the table of content

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.

NoteNote

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.

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 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.

Show:
© 2015 Microsoft