Expand Minimize

Retrieving Lists of All the Worksheets from Excel 2010 Workbooks by Using the Open XML SDK 2.0

Office 2010

Office Visual How To

Summary:  Use strongly typed classes in the Open XML SDK 2.0 for Microsoft Office to retrieve a list of all the worksheets in a Microsoft Excel 2010 or Microsoft Office Excel 2007 workbook, without loading the workbook into Microsoft Excel.

Applies to:  Microsoft Office 2010 | 2007 Microsoft Office system | Microsoft Excel 2010 | Microsoft Office Excel 2007 | Open XML SDK 2.0 for Microsoft Office | Microsoft Visual Studio 2010

Published:  February 2012

Provided by: Ken Getz

Overview

The Open XML file formats make it possible to retrieve a list that contains references to all the worksheets in a Microsoft Excel 2010 or Microsoft Office Excel 2007 document, but doing this requires some effort. The Open XML SDK 2.0 adds strongly typed classes that simplify access to the Open XML file formats. The SDK also simplifies the retrieval of a list that contains all worksheets in a workbook, and the code sample that is included with this article describes how to use the SDK to retrieve a list of all the worksheets in an Excel 2010 or Excel 2007 document.

To use the code sample, install the Open XML SDK 2.0 by using the link listed in the Explore It section. The code sample is modified from code that is included as part of a set of code examples for the Open XML SDK 2.0. The Explore It section also includes a link to the full set of code examples, although you can use the code sample without downloading and installing the code examples. The sample application displays a list of the worksheet names, using a workbook that you supply.

Code It

The code sample that accompanies this article includes code that shows how to retrieve a list of all the worksheets that are in an Excel 2010 or Excel 2007 workbook.

Setting Up references

To use the code from the Open XML SDK 2.0, you must add several references to your project. The sample project includes these references, but in your own code, you would must explicitly reference the following assemblies:

  • WindowsBase. This reference may be set for you, depending on the kind of project that you create.

  • DocumentFormat.OpenXml. Installed by the Open XML SDK 2.0.

Also, you should add the following using or Imports statements to the top of your code file.

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

Calling the procedure

The sample application includes the procedure XLGetAllSheets, which accepts the name of the workbook to examine as a string, and returns an instance of the Open XML SDK 2.0 Sheets class. This instance contains a reference to zero or more Sheet objects, each of which contains information about one sheet in the workbook. To call the procedure, use the code in the following example.

const string DEMOFILE = "SampleWorkbook.xlsx";

var results = XLGetAllSheets(DEMOFILE);
foreach (Sheet item in results)
{
  Console.WriteLine(item.Name);
}

Running this code retrieves a collection that contains information about all of the sheets in the workbook that you specified.

Examining the sample procedure

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

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

The code continues by opening the workbook in read-only mode and retrieving a reference to the WorkbookPart part, as shown in the following code.

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

The code retrieves the Sheets property of the Workbook property of the WorkbookPart instance, which returns a reference to a collection of Sheet objects that provide the return value for the procedure.

theSheets = wbPart.Workbook.Sheets;

Sample procedure

The sample includes the following code.

public static Sheets XLGetAllSheets(string fileName)
{
  Sheets theSheets = null;
 
  using (SpreadsheetDocument document = 
   SpreadsheetDocument.Open(fileName, false))
  {
    WorkbookPart wbPart = document.WorkbookPart;
    theSheets = wbPart.Workbook.Sheets;
  }
  return theSheets;
}
Read It

To understand what the code sample is doing, it's useful to examine the contents of the workbook by using the Open XML SDK 2.0 Productivity Tool for Microsoft Office, which is included as part of the Open XML SDK 2.0. Figure 1 shows a sample workbook that contains several worksheets that are opened in the tool. The code sample retrieves a reference to the Workbook part, and then given that part, retrieves a collection of all the child worksheets. Figure 1 shows the XML content that the Sheets collection returns.

Figure 1. Sheets in the Open XML SDK 2.0 Productivity Tool

Sheets in the Open XML SDK 2.0 Productivity Tool

Each Sheet object in the Sheets collection is an OpenXmlElement object with a few additional properties, as defined in the Open XML schema for a sheet. You can find properties such as Id, Name, and State, each corresponding to an XML attribute, but not much else. If you need more information about the contents of the sheet, you have to interact with the individual worksheet part by using the GetPartById method.

The code examples in this article include several of the issues that you encounter when you work with the Open XML SDK 2.0. Each example is slightly different. However, the basic concepts are the same. Unless you understand the structure of the part that you are trying to work with, even the Open XML SDK 2.0 does not make it possible to interact with the part. Take the time to investigate the objects that you are working with before you start to write code. You will save time.

See It

 

Watch the video

Watch video

Length: 00:7:50

Click to grab code

Grab the Code

Explore It

About the Author

Ken Getz is a developer, writer, and trainer, working as a senior consultant with MCW Technologies, LLC, a Microsoft Solution Provider. He has co-authored several technical books for developers, including the best-selling ASP.NET Developer's Jumpstart, the Access Developer's Handbook series, and the VBA Developer's Handbook series. Ken is a lead courseware author for AppDev, and has authored many of their most popular titles. Ken has spoken for many years at technical conferences, including Microsoft TechEd.

Community Additions

ADD
Show:
© 2014 Microsoft