Edit

Share via

Retrieve a list of the worksheets in a spreadsheet document

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

GetAllWorksheets Method

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.

Sheets? sheets = GetAllWorksheets(args[0]);

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.

Calling the GetAllWorksheets Method

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

Sheets? sheets = GetAllWorksheets(args[0]);

if (sheets is not null)
{
    foreach (Sheet sheet in sheets)
    {
        Console.WriteLine(sheet.Name);
    }
}

How the Code Works

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;

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))
{
    theSheets = document?.WorkbookPart?.Workbook.Sheets;

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 = document?.WorkbookPart?.Workbook.Sheets;

Sample Code

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

static Sheets? GetAllWorksheets(string fileName)
{
    Sheets? theSheets = null;

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

    return theSheets;
}

See also