Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

How to: Retrieve a list of the worksheets in a spreadsheet document (Open XML SDK)

Office 2013

This topic shows how to use the classes in the Open XML SDK 2.5 for Office to programmatically retrieve a list of the worksheets in a Microsoft Excel 2010 or Microsoft Excel 2013 workbook, without loading the document into Excel. It contains an example GetAllWorksheets method to illustrate this task.

Last modified: March 25, 2013

Applies to: Office 2013 | Open XML

In this article
GetAllWorksheets Method
Calling the GetAllWorksheets Method
How the Code Works
Sample Code

To use the sample code in this topic, you must install the Open XML SDK 2.5. You must explicitly reference the following assemblies in your project:

  • WindowsBase

  • DocumentFormat.OpenXml (installed by the Open XML SDK)

You must also use the following using directives or Imports statements to compile the code in this topic.

using System;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

You can use the GetAllWorksheets method, which is shown in the following code, to retrieve a list of the worksheets in a workbook. The GetAllWorksheets method accepts a single parameter, a string that indicates the path of the file that you want to examine.

public static Sheets GetAllWorksheets(string fileName)

The method works with the workbook you specify, returning an instance of the Sheets object, from which you can retrieve a reference to each Sheet object.

To call the GetAllWorksheets method, pass the required value, as shown in the following code.

const string DEMOFILE = @"C:\Users\Public\Documents\SampleWorkbook.xlsx";

static void Main(string[] args)
{
    var results = GetAllWorksheets(DEMOFILE);
    foreach (Sheet item in results)
    {
        Console.WriteLine(item.Name);
    }
}

The sample method, GetAllWorksheets, creates a variable that will contain a reference to the Sheets collection of the workbook. At the end of its work, the method returns the variable, which contains either a reference to the Sheets collection, or null/Nothing if there were no sheets (this cannot occur in a well-formed workbook).

Sheets theSheets = null;
// Code removed here…
return theSheets;

The code then continues by opening the document in read-only mode, and retrieving a reference to the WorkbookPart.

using (SpreadsheetDocument document = 
    SpreadsheetDocument.Open(fileName, false))
{
    WorkbookPart wbPart = document.WorkbookPart;
    // Code removed here.
}

To get access to the Workbook object, the code retrieves the value of the Workbook property from the WorkbookPart, and then retrieves a reference to the Sheets object from the Sheets property of the Workbook. The Sheets object contains the collection of Sheet objects that provide the method's return value.

theSheets = wbPart.Workbook.Sheets;

The following is the complete GetAllWorksheets code sample in C# and Visual Basic.

using System;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace GetAllWorkheets
{
    class Program
    {
        const string DEMOFILE = 
            @"C:\Users\Public\Documents\SampleWorkbook.xlsx";

        static void Main(string[] args)
        {
            var results = GetAllWorksheets(DEMOFILE);
            foreach (Sheet item in results)
            {
                Console.WriteLine(item.Name);
            }
        }

        // Retrieve a List of all the sheets in a workbook.
        // The Sheets class contains a collection of 
        // OpenXmlElement objects, each representing one of 
        // the sheets.
        public static Sheets GetAllWorksheets(string fileName)
        {
            Sheets theSheets = null;

            using (SpreadsheetDocument document = 
                SpreadsheetDocument.Open(fileName, false))
            {
                WorkbookPart wbPart = document.WorkbookPart;
                theSheets = wbPart.Workbook.Sheets;
            }
            return theSheets;
        }
    }
}

Contribute to this article

Want to edit or suggest changes to this content? You can edit and submit changes to this article using GitHub.

Show:
© 2015 Microsoft