Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
How to: Retrieve a List of the Hidden Rows or Columns in a Spreadsheet Document

How to: Retrieve a List of the Hidden Rows or Columns in a Spreadsheet Document

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 rows or columns in a Microsoft Office Excel 2007 or Microsoft Excel 2010 worksheet, without loading the document into Excel. It contains an example GetHiddenRowsOrCols method to illustrate this task.

Last modified: November 17, 2011

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

In this article
GetHiddenRowsOrCols Method
Calling the GetHiddenRowsOrCols Method
How the Code Works
Retrieving the List of Hidden Row or Column Index Values
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.IO;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

You can use the GetHiddenRowsOrCols method to retrieve a list of the hidden rows or columns in a worksheet. The GetHiddenRowsOrCols method accepts three parameters, indicating the following:

  • The name of the document to examine (string).

  • The name of the sheet to examine (string).

  • Whether to detect rows (true) or columns (false) (Boolean).

public static List<uint> GetHiddenRowsOrCols(
  string fileName, string sheetName, bool detectRows)

The method returns a list of unsigned integers that contain each index for the hidden rows or columns, if the specified worksheet contains any hidden rows or columns (rows and columns are numbered starting at 1, rather than 0.) To call the method, pass all the parameter values, as shown in the following example code.

const string fileName = @"C:\users\public\documents\RetrieveHiddenRowsCols.xlsx";
List<uint> items = GetHiddenRowsOrCols(fileName, "Sheet1", true);
var sw = new StringWriter();
foreach (var item in items)
    sw.WriteLine(item);
Console.WriteLine(sw.ToString());

The code starts by creating a variable, itemList, that will contain the return value.

List<uint> itemList = new List<uint>();

Next, the code opens the document, by using the SpreadsheetDocument.Open method and indicating that the document should be open for read-only access (the final false parameter value). Next the code retrieves a reference to the workbook part, by using the WorkbookPart property of the document.

using (SpreadsheetDocument document =
    SpreadsheetDocument.Open(fileName, false))
{
    WorkbookPart wbPart = document.WorkbookPart;
    // Code removed here...
}

To find the hidden rows or columns, the code must first retrieve a reference to the specified sheet, given its name. This is not as easy as you might think. The code must look through all the sheet-type descendants of the workbook part's Workbook property, examining the Name property of each sheet that it finds. Note that this search simply looks through the relations of the workbook, and does not actually find a worksheet part. It simply finds a reference to a Sheet object, which contains information such as the name and Id property of the sheet. The simplest way to accomplish this is to use a LINQ query.

Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().
    Where((s) => s.Name == sheetName).FirstOrDefault();
if (theSheet == null)
{
    throw new ArgumentException("sheetName");
}

The FirstOrDefault method returns either the first matching reference (a sheet, in this case) or a null reference if no match was found. The code checks for the null reference, and throws an exception if you passed in an invalid sheet name. Now that you have information about the sheet, the code must retrieve a reference to the corresponding worksheet part. The sheet information you already retrieved provides an Id property, and given that Id property, the code can retrieve a reference to the corresponding WorksheetPart property by calling the GetPartById method of the WorkbookPart object.

else
{
    // The sheet does exist.
    WorksheetPart wsPart =
        (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
    Worksheet ws = wsPart.Worksheet;
    // Code removed here...
}

The code uses the detectRows parameter that you specified when you called the method to determine whether to retrieve information about rows or columns.

if (detectRows)
{
    // Retrieve hidden rows.
    // Code removed here...
}
else
{
    // Retrieve hidden columns.
    // Code removed here...
}

The code that actually retrieves the list of hidden rows requires only a single line of code.

itemList = ws.Descendants<Row>().
    Where((r) => r.Hidden != null && r.Hidden.Value).
    Select(r => r.RowIndex.Value).ToList<uint>();

This single line accomplishes a lot, however. It starts by calling the Descendants method of the worksheet, retrieving a list of all the rows. The Where method limits the results to only those rows where the Hidden property of the item is not null and the value of the Hidden property is True. The Select method projects the return value for each row, returning the value of the RowIndex property. Finally, the ToList<TSource> method converts the resulting IEnumerable<T> interface into a List<T> object of unsigned integers. If there are no hidden rows, the returned list is empty.

Retrieving the list of hidden columns is a bit trickier, because Excel collapses groups of hidden columns into a single element, and provides Min and Max properties that describe the first and last columns in the group. Therefore, the code that retrieves the list of hidden columns starts the same as the code that retrieves hidden rows. However, it must iterate through the index values (looping through each item in the collection of hidden columns, adding each index from the Min to the Max value, inclusively).

var cols = ws.Descendants<Column>().
  Where((c) => c.Hidden != null && c.Hidden.Value);
foreach (Column item in cols)
{
    for (uint i = item.Min.Value; i <= item.Max.Value; i++)
    {
        itemList.Add(i);
    }
}

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

public static List<uint> GetHiddenRowsOrCols(
  string fileName, string sheetName, bool detectRows)
{
    // Given a workbook and a worksheet name, return 
    // either a list of hidden row numbers, or a list 
    // of hidden column numbers. If detectRows is true, return
    // hidden rows. If detectRows is false, return hidden columns. 
    // Rows and columns are numbered starting with 1.

    List<uint> itemList = new List<uint>();

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

        Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().
            Where((s) => s.Name == sheetName).FirstOrDefault();
        if (theSheet == null)
        {
            throw new ArgumentException("sheetName");
        }
        else
        {
            // The sheet does exist.
            WorksheetPart wsPart =
                (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
            Worksheet ws = wsPart.Worksheet;

            if (detectRows)
            {
                // Retrieve hidden rows.
                itemList = ws.Descendants<Row>().
                    Where((r) => r.Hidden != null && r.Hidden.Value).
                    Select(r => r.RowIndex.Value).ToList<uint>();
            }
            else
            {
                // Retrieve hidden columns.
                var cols = ws.Descendants<Column>().
                    Where((c) => c.Hidden != null && c.Hidden.Value);
                foreach (Column item in cols)
                {
                    for (uint i = item.Min.Value; i <= item.Max.Value; i++)
                    {
                        itemList.Add(i);
                    }
                }
            }
        }
    }
    return itemList;
}
Show:
© 2015 Microsoft