Retrieve a list of the hidden 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 hidden worksheets in a Microsoft Excel 2010 or Microsoft Excel 2010 workbook, without loading the document into Excel. It contains an example GetHiddenSheets method to illustrate this task.

GetHiddenSheets method

You can use the GetHiddenSheets method, 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. The method works with the workbook you specify, filling a List<T> instance with a reference to each hidden Sheet object.

Retrieve 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 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.

C#

WorkbookPart? wbPart = document.WorkbookPart;

if (wbPart is not null)
{
    var sheets = wbPart.Workbook.Descendants<Sheet>();

Visual Basic

Dim wbPart As WorkbookPart = document.WorkbookPart
Dim sheets = wbPart.Workbook.Descendants(Of Sheet)()

Retrieve hidden sheets

It's important to be aware that Excel supports two levels of 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 as a value, and where the value is either SheetStateValues.Hidden or SheetStateValues.VeryHidden.

C#

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

Visual Basic

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))

Sample code

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

C#

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

static List<Sheet> GetHiddenSheets(string fileName)
{
    List<Sheet> returnVal = new List<Sheet>();

    using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
    {
        WorkbookPart? wbPart = document.WorkbookPart;

        if (wbPart is not null)
        {
            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 is not null &&
                item.State.HasValue &&
                (item.State.Value == SheetStateValues.Hidden ||
                item.State.Value == SheetStateValues.VeryHidden));

            returnVal = hiddenSheets.ToList();
        }
    }

    return returnVal;
}

Visual Basic

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

Module Program
    Sub Main(args As String())
        Dim fileName As String = args(0)
        Dim hiddenSheets As List(Of Sheet) = GetHiddenSheets(fileName)

        For Each sheet As Sheet In hiddenSheets
            Console.WriteLine("Sheet ID: {0}  Name: {1}", sheet.Id, sheet.Name)
        Next
    End Sub



    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
End Module

See also

• Open XML SDK class library reference