Retrieving a List of Hidden Worksheets from Excel 2010 Workbooks by Using the Open XML SDK 2.0

Article
07/24/2014

Office Visual How To

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

Published: August 2011

Provided by: Ken Getz, MCW Technologies, LLC

The Office Open XML file formats make it possible to retrieve information about Excel workbooks. The Open XML SDK 2.0 for Microsoft Office adds strongly typed classes that simplify access to the Office Open XML file formats. The SDK simplifies the tasks of working with, for example, information about hidden worksheets in a workbook. The code sample included with this Visual How To describes how to use the SDK to retrieve a generic list that contains information about all the hidden worksheets in a workbook, without requiring you to open the document in Microsoft Excel.

The code sample provided with this Visual How To includes the code used to retrieve a list of hidden worksheets in an Excel 2007 or Excel 2010 workbook. The following sections show you the code, in detail.

Setting Up References

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

WindowsBase (this reference may already be set for you, depending on the kind of project that you create)
DocumentFormat.OpenXml (installed by the Open XML SDK 2.0)

Also, ensure that your code includes at least the following using/Imports statements at the top of the code file.

Imports DocumentFormat.OpenXml.Spreadsheet
Imports DocumentFormat.OpenXml.Packaging

using System;
using System.Collections.Generic;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

The XLGetHiddenSheets procedure accepts a single parameter, a string that indicates the path of the file that you want to examine, and it returns a generic List that contains information about the individual hidden Sheet.

Public Function XLGetHiddenSheets(
    ByVal fileName As String) As List(Of Sheet)

public static List<Sheet> XLGetHiddenSheets(string filename)

The procedure works with the workbook you specify, filling a List instance with a reference to each hidden Sheet. To call the procedure, pass the Required parameter value, as shown in the code example. To demonstrate the procedure, create a workbook with one or more hidden sheets, and pass the path for the sample file to the XLGetHiddenSheets procedure.

Accessing the Workbook

The code starts by creating a generic list that will contain information about the hidden sheets.

Dim returnVal As New List(Of Sheet)

List<Sheet> returnVal = new List<Sheet>();

Next, the code opens the specified workbook by using the Open method and indicating that the document should be open for read-only access (the final false parameter). Given the open workbook, the code uses the WorkbookPart property to navigate to the main workbook part, storing the reference in a variable named wbPart.

Using document As SpreadsheetDocument = SpreadsheetDocument.Open(filename, False)
    Dim wbPart As WorkbookPart = document.WorkbookPart
     ' Code removed here…
End Using
Return returnVal

using (SpreadsheetDocument document = SpreadsheetDocument.Open(filename, false))
{
    WorkbookPart wbPart = document.WorkbookPart;
    // Code removed here…
}
return returnVal;

Retrieving a Collection of Sheets

The WorkbookPart class provides a Workbook property, which in turn contains the XML content of the workbook. Although the Open XML SDK 2.0 provides the Sheets property, which returns a collection of the Sheet parts, all the information that you need is provided by the Sheet elements within the Workbook XML content. The sample code uses the Descendants generic property of the Workbook to retrieve a collection of Sheet objects that contains information about all the sheet child elements of the workbook XML content (see Figure 1).

Dim sheets = wbPart.Workbook.Descendants(Of Sheet)()

var sheets = wbPart.Workbook.Descendants<Sheet>();

Retrieving Hidden Sheets

It is important to be aware that Excel supports two levels of hiding worksheets. You can hide a worksheet using the Excel user interface by right-clicking the worksheets tab and opting to hide the worksheet. For these worksheets, the Sheet object State property contains a value of Hidden. You can also make a worksheet "very hidden" by writing code (either in VBA or in some other language) that sets the sheet Visible property to the enumerated value, xlSheetVeryHidden. For worksheets hidden in this manner, the Sheet object State property contains the value VeryHidden.

Given the collection that contains information about all the sheets, the code uses the Where function to filter the collection so that it contains only the sheets in which the State property is not null. If the State property is not null, the code looks for the Sheet objects in which the State property has a value, and where the value is either Hidden or VeryHidden.

Dim hiddenSheets = sheets.Where(Function(item) item.State IsNot Nothing _
    AndAlso item.State.HasValue _
    AndAlso (item.State.Value = SheetStateValues.Hidden Or _
    item.State.Value = SheetStateValues.VeryHidden))

var hiddenSheets = sheets.Where((item) => item.State != null &&
    (item.State.Value == SheetStateValues.Hidden ||
    item.State.Value == SheetStateValues.VeryHidden));

Finally, the code calls the ToList method of the hiddenSheets variable, and executes the LINQ query that retrieves the list of hidden sheets, placing the result into the return value for the function.

returnVal = hiddenSheets.ToList()

returnVal = hiddenSheets.ToList();

Sample Procedure

The following code shows the full sample procedure.

Public Function XLGetHiddenSheets(ByVal fileName As String) As List(Of Sheet)
    Dim returnVal As New List(Of Sheet)
 
    Using document As SpreadsheetDocument = SpreadsheetDocument.Open(fileName, False)
        Dim wbPart As WorkbookPart = document.WorkbookPart
 
        Dim sheets = wbPart.Workbook.Descendants(Of Sheet)()
 
        ' Look for sheets where there is a State attribute defined, 
        ' where the State has a value, and where the value is
        ' either Hidden or VeryHidden.
        Dim hiddenSheets = sheets.Where(Function(item) item.State IsNot Nothing _
            AndAlso item.State.HasValue _
            AndAlso (item.State.Value = SheetStateValues.Hidden Or _
                item.State.Value = SheetStateValues.VeryHidden))
 
        returnVal = hiddenSheets.ToList()
    End Using
    Return returnVal
End Function

public static List<Sheet> XLGetHiddenSheets(string fileName)
{
    List<Sheet> returnVal = new List<Sheet>();
 
    using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
    {
        WorkbookPart wbPart = document.WorkbookPart;
 
        var sheets = wbPart.Workbook.Descendants<Sheet>();
 
        // Look for sheets where there is a State attribute defined,
        // where the State has a value, and where the value is
        // either Hidden or VeryHidden.
        var hiddenSheets = sheets.Where((item) => item.State != null && 
            item.State.HasValue && 
            (item.State.Value == SheetStateValues.Hidden || 
            item.State.Value == SheetStateValues.VeryHidden));
 
        returnVal = hiddenSheets.ToList();
    }
    return returnVal;
}

The code sample that is included with this Visual How To contains code that retrieves a generic list of references to hidden worksheets in an Excel workbook. To use the sample, you must install the Open XML SDK 2.0, available from the link listed in the Explore It section. The sample also uses a modified version of code 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 sample without downloading and installing the code examples.

The sample application demonstrates only a handful of the available properties and methods provided by the Open XML SDK 2.0 that you could interact with when you are retrieving information about a workbook structure. For more information, see the documentation included with the Open XML SDK 2.0 Productivity Tool: Click the Open XML SDK Documentation tab in the lower-left corner of the application window, and search for the class that you need to study. Although the documentation does not currently include code examples, given the sample shown here and the documentation, you should be able to successfully modify the sample application.

To understand what the sample code is doing, it is 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, opened in the tool. The sample code retrieves a reference to the Workbook part, and given that part, retrieves a collection of all the child worksheets. Code in the sample uses the state attribute (exposed as the State property of the corresponding Sheet object) to determine whether the sheet is hidden.

Figure 1. Sample workbook in the Open XML SDK 2.0 Productivity Tool

Sample workbook in the Productivity Tool

Watch the video

> [!VIDEO https://www.microsoft.com/en-us/videoplayer/embed/284f984b-7682-4df7-bbf5-d18f425b05b7]

Length: 00:7:41

Grab the Code

About the Author

Ken Getz, is a senior consultant with MCW Technologies. He is coauthor of ASP.NET Developers Jumpstart (Addison-Wesley, 2002), Access Developer's Handbook (Sybex, 2001), and VBA Developer's Handbook, 2nd Edition (Sybex, 2001).

Share via

Retrieving a List of Hidden Worksheets from Excel 2010 Workbooks by Using the Open XML SDK 2.0

Additional resources