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 for Office to programmatically retrieve a list of hidden rows or columns in a Microsoft Excel worksheet. It contains an example GetHiddenRowsOrCols method to illustrate this task.
GetHiddenRowsOrCols Method
You can use the GetHiddenRowsOrCols method to retrieve a list of the hidden rows or columns in a worksheet. 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). The GetHiddenRowsOrCols method accepts three parameters:
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).
How the Code Works
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.
List<uint> itemList = new List<uint>();
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
{
if (document is not null)
{
WorkbookPart wbPart = document.WorkbookPart ?? document.AddWorkbookPart();
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>().FirstOrDefault((s) => s.Name == sheetName);
if (theSheet is null || theSheet.Id is null)
{
throw new ArgumentException("sheetName");
}
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.
// The sheet does exist.
WorksheetPart? wsPart = wbPart.GetPartById(theSheet.Id!) as WorksheetPart;
Worksheet? ws = wsPart?.Worksheet;
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.The code that actually retrieves the list of hidden rows requires only a single line of code.
// Retrieve hidden rows.
itemList = ws.Descendants<Row>()
.Where((r) => r?.Hidden is not null && r.Hidden.Value)
.Select(r => r.RowIndex?.Value)
.Cast<uint>()
.ToList();
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 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 is not null && c.Hidden.Value);
foreach (Column item in cols)
{
if (item.Min is not null && item.Max is not null)
{
for (uint i = item.Min.Value; i <= item.Max.Value; i++)
{
itemList.Add(i);
}
}
}
Sample Code
The following is the complete GetHiddenRowsOrCols code sample in C# and Visual Basic.
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Linq;
static List<uint> GetHiddenRowsOrCols(string fileName, string sheetName, string detectRows = "false")
{
// 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))
{
if (document is not null)
{
WorkbookPart wbPart = document.WorkbookPart ?? document.AddWorkbookPart();
Sheet? theSheet = wbPart.Workbook.Descendants<Sheet>().FirstOrDefault((s) => s.Name == sheetName);
if (theSheet is null || theSheet.Id is null)
{
throw new ArgumentException("sheetName");
}
else
{
// The sheet does exist.
WorksheetPart? wsPart = wbPart.GetPartById(theSheet.Id!) as WorksheetPart;
Worksheet? ws = wsPart?.Worksheet;
if (ws is not null)
{
if (detectRows.ToLower() == "true")
{
// Retrieve hidden rows.
itemList = ws.Descendants<Row>()
.Where((r) => r?.Hidden is not null && r.Hidden.Value)
.Select(r => r.RowIndex?.Value)
.Cast<uint>()
.ToList();
}
else
{
// Retrieve hidden columns.
var cols = ws.Descendants<Column>().Where((c) => c?.Hidden is not null && c.Hidden.Value);
foreach (Column item in cols)
{
if (item.Min is not null && item.Max is not null)
{
for (uint i = item.Min.Value; i <= item.Max.Value; i++)
{
itemList.Add(i);
}
}
}
}
}
}
}
}
return itemList;
}