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.
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;
Imports System.IO
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet
GetHiddenRowsOrCols Method
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)
Public Function GetHiddenRowsOrCols(
ByVal fileName As String, ByVal sheetName As String,
ByVal detectRows As Boolean) As List(Of UInteger)
Calling the GetHiddenRowsOrCols Method
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());
Const fileName As String = "C:\Users\Public\Documents\RetrieveHiddenRowsCols.xlsx"
Dim items As List(Of UInteger) =
GetHiddenRowsOrCols(fileName, "Sheet1", True)
Dim sw As New StringWriter
For Each item In items
sw.WriteLine(item)
Next
Console.WriteLine(sw.ToString())
How the Code Works
The code starts by creating a variable, itemList, that will contain the return value.
List<uint> itemList = new List<uint>();
Dim itemList As New List(Of UInteger)
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...
}
Using document As SpreadsheetDocument =
SpreadsheetDocument.Open(fileName, False)
Dim wbPart As WorkbookPart = document.WorkbookPart
' Code removed here...
End Using
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");
}
Dim theSheet As Sheet = wbPart.Workbook.Descendants(Of Sheet)().
Where(Function(s) s.Name = sheetName).FirstOrDefault()
If theSheet Is Nothing Then
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...
}
Else
' The sheet does exist.
Dim wsPart As WorksheetPart =
CType(wbPart.GetPartById(theSheet.Id), WorksheetPart)
Dim ws As Worksheet = wsPart.Worksheet
' Code removed here...
End If
Retrieving the List of Hidden Row or Column Index Values
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...
}
If detectRows Then
' Retrieve hidden rows.
' Code removed here...
Else
' Retrieve hidden columns.
' Code removed here...
End If
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>();
itemList = ws.Descendants(Of Row).
Where(Function(r) r.Hidden IsNot Nothing AndAlso
r.Hidden.Value).
Select(Function(r) r.RowIndex.Value).ToList()
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);
}
}
Dim cols = ws.Descendants(Of Column).
Where(Function(c) c.Hidden IsNot Nothing AndAlso
c.Hidden.Value)
For Each item As Column In cols
For i As UInteger = item.Min.Value To item.Max.Value
itemList.Add(i)
Next
Next
Sample Code
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;
}
Public Function GetHiddenRowsOrCols(
ByVal fileName As String, ByVal sheetName As String,
ByVal detectRows As Boolean) As List(Of UInteger)
' 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.
Dim itemList As New List(Of UInteger)
Using document As SpreadsheetDocument =
SpreadsheetDocument.Open(fileName, False)
Dim wbPart As WorkbookPart = document.WorkbookPart
Dim theSheet As Sheet = wbPart.Workbook.Descendants(Of Sheet)().
Where(Function(s) s.Name = sheetName).FirstOrDefault()
If theSheet Is Nothing Then
Throw New ArgumentException("sheetName")
Else
' The sheet does exist.
Dim wsPart As WorksheetPart =
CType(wbPart.GetPartById(theSheet.Id), WorksheetPart)
Dim ws As Worksheet = wsPart.Worksheet
If detectRows Then
' Retrieve hidden rows.
itemList = ws.Descendants(Of Row).
Where(Function(r) r.Hidden IsNot Nothing AndAlso
r.Hidden.Value).
Select(Function(r) r.RowIndex.Value).ToList()
Else
' Retrieve hidden columns.
Dim cols = ws.Descendants(Of Column).
Where(Function(c) c.Hidden IsNot Nothing AndAlso
c.Hidden.Value)
For Each item As Column In cols
For i As UInteger = item.Min.Value To item.Max.Value
itemList.Add(i)
Next
Next
End If
End If
End Using
Return itemList
End Function
See Also
Reference
Other Resources
Retrieving Lists of Hidden Rows or Columns in Excel 2010 Workbooks by Using the Open XML SDK 2.0