Retrieving Lists of All the Worksheets from Excel 2010 Workbooks by Using the Open XML SDK 2.0
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.
Last modified: September 12, 2012
Applies to: Excel 2007 | Excel 2010 | Office 2007 | Office 2010 | Open XML | PowerPoint 2010 | VBA | Visual Studio | Word 2010
Published: February 2012
Provided by: Ken Getz
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.
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:
Also, you should add the following using or Imports statements to the top of your code file.
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.0Sheets 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 DEMOFILE As String = "SampleWorkbook.xlsx" Dim results = XLGetAllSheets(DEMOFILE) ' Because Sheet inherits from OpenXmlElement, you can cast ' each item in the collection to be a Sheet instance: For Each item As Sheet In results Console.WriteLine(item.Name) Next
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).
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 document As SpreadsheetDocument = SpreadsheetDocument.Open(fileName, False) Dim wbPart As WorkbookPart = document.WorkbookPart ' Code removed here… End Using
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.
The sample includes the following code.
Public Function XLGetAllSheets(ByVal fileName As String) As Sheets Dim theSheets As Sheets Using document As SpreadsheetDocument = SpreadsheetDocument.Open(fileName, False) Dim wbPart As WorkbookPart = document.WorkbookPart theSheets = wbPart.Workbook.Sheets() End Using Return theSheets End Function
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.
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.
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.