How to: Retrieve a List of the Hidden Worksheets in a Spreadsheet Document

Article
07/30/2014

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

Applies to: Excel 2010 | Office 2010 | PowerPoint 2010 | Word 2010

In this article
GetHiddenSheets Method
Calling the GetHiddenSheets Method
How the Code Works
Retrieving the Collection of Worksheets
Retrieving Hidden Sheets
Sample Code

To use the sample code in this topic, you must install the Open XML SDK 2.0. 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 System.Collections.Generic;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

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

GetHiddenSheets Method

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

public static List<Sheet> GetHiddenSheets(string fileName)

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

The method works with the workbook you specify, filling a List<T> instance with a reference to each hidden Sheet object.

Calling the GetHiddenSheets Method

The method returns a generic list that contains information about the individual hidden Sheet objects. To call the GetHiddenWorksheets method, pass the required parameter value, as shown in the following code.

// Revise this path to the location of a file that contains hidden worksheets.
const string DEMOPATH = 
    @"C:\Users\Public\Documents\HiddenSheets.xlsx";
List<Sheet> sheets = GetHiddenSheets(DEMOPATH);
foreach (var sheet in sheets)
{
    Console.WriteLine(sheet.Name);
}

' Revise this path to the location of a file that contains hidden worksheets.
Const DEMOPATH As String =
    "C:\Users\Public\Documents\HiddenSheets.xlsx"
Dim sheets As List(Of Sheet) = GetHiddenSheets(DEMOPATH)
For Each sheet In sheets
    Console.WriteLine(sheet.Name)
Next

How the Code Works

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

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

Dim returnVal As New List(Of Sheet)

Next, the following code opens the specified workbook by using the SpreadsheetDocument.Open method and indicating that the document should be open for read-only access (the final false parameter value). 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 (SpreadsheetDocument document = 
    SpreadsheetDocument.Open(fileName, false))
{
    WorkbookPart wbPart = document.WorkbookPart;
    // Code removed here… 
}
return returnVal;

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

Retrieving the Collection of Worksheets

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 following code uses the Descendants generic method of the Workbook object to retrieve a collection of Sheet objects that contain information about all the sheet child elements of the workbook's XML content.

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

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

Retrieving Hidden Sheets

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

Given the collection that contains information about all the sheets, the following 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 SheetStateValues.Hidden or SheetStateValues.VeryHidden.

var hiddenSheets = sheets.Where((item) => item.State != null && 
    item.State.HasValue && 
    (item.State.Value == SheetStateValues.Hidden || 
    item.State.Value == SheetStateValues.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))

Finally, the following code calls the ToList<TSource> method to execute 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 Code

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

public static List<Sheet> GetHiddenSheets(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;
}

Public Function GetHiddenSheets(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

How to: Retrieve a List of the Hidden Worksheets in a Spreadsheet Document

GetHiddenSheets Method

Calling the GetHiddenSheets Method

How the Code Works

Retrieving the Collection of Worksheets

Retrieving Hidden Sheets

Sample Code

See Also

Reference

Other Resources

Additional resources