Retrieving Lists of Hidden Rows or Columns in Excel 2010 Workbooks by Using the Open XML SDK 2.0

Summary:  Use strongly typed classes in the Open XML SDK 2.0 to retrieve a list of hidden rows or columns in a Microsoft Excel 2007 or Excel 2010 worksheet, without loading the document into Excel.

Applies to: Excel 2010 | Office 2010 | Open XML | PowerPoint 2010 | VBA | Word 2010

Published:  August 2010

Provided by:  Ken Getz, MVP, MCW Technologies, LLC

Use Open XML file formats to retrieve information about hidden rows and columns in an Excel worksheet. The Open XML SDK 2.0 adds strongly typed classes that are designed to simplify access to the Open XML file formats. The SDK simplifies the tasks of retrieving information about the workbook, and finding the appropriate XML content. The code sample that is included with this Visual How To shows how to the use the SDK to do this. The code sample provided with this Visual How To includes the code that is required to retrieve a list of indexes for hidden rows or columns in a specified sheet in an Excel 2007 or Excel 2010 workbook. Setting Up References To use the code from the Open XML SDK 2.0, you must add several references to your project. The sample project includes these references, but in your own code, you must explicitly reference the following assemblies: WindowsBase─This reference may be set for you, depending on the kind of project that you create. DocumentFormat.OpenXml─Installed by the Open XML SDK 2.0. Also, add the following using/Imports statements to the top of your code file. Imports DocumentFormat.OpenXml.Packaging Imports DocumentFormat.OpenXml.Spreadsheet Imports System.Collections.Generic using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using System.Collections.Generic; Examining the Procedure The XLGetHiddenRowsOrCols procedure 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) Public Function XLGetHiddenRowsOrCols( ByVal fileName As String, ByVal sheetName As String, ByVal detectRows As Boolean) As List(Of UInteger) public static List<uint> XLGetHiddenRowsOrCols( string fileName, string sheetName, bool detectRows) The procedure returns a list of unsigned integers that contain each index for the hidden row or columns, if the specified worksheet contained any hidden rows or columns. To call the procedure, pass all the parameter values, as shown in the following code example. Note Rows and columns are numbered starting at 1 instead of 0. Const fileName As String = "C:\temp\HiddenRowsCols.xlsx" Dim items As List(Of UInteger) = XLGetHiddenRowsOrCols(fileName, "Sheet1", False) Dim sw As New StringWriter For Each item In items sw.WriteLine(item) Next Console.WriteLine(sw.ToString()) const string fileName = @"C:\temp\HiddenRowsCols.xlsx"; List<uint> items = XLGetHiddenRowsOrCols(fileName, "Sheet1", true); var sw = new StringWriter(); foreach (var item in items) sw.WriteLine(item); Console.WriteLine(sw.ToString()); Accessing the Sheet The following code example shows that the code starts by creating a variable that will contain the return value. Dim itemList As New List(Of UInteger) List<uint> itemList = new List<uint>(); Next, the code opens the document that uses the SpreadsheetDocument.Open method and indicates that the document should be open for read-only access (the final false parameter). The code then retrieves a reference to the workbook part by using the WorkbookPart property of the document as shown in the following code example. Using document As SpreadsheetDocument = SpreadsheetDocument.Open(fileName, False) Dim wbPart As WorkbookPart = document.WorkbookPart ' Code removed here… End Using 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. The code must look through all the sheet-type descendants of the workbook part Workbook property, examining the Name property of each sheet that it finds. Note This search only looks through the relations of the workbook, and does not actually find a worksheet part. It only finds a reference to a Sheet, which contains information such as the name and ID of the sheet. To do this, use a LINQ query. 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") End If Sheet theSheet = wbPart.Workbook.Descendants<Sheet>(). Where(s => s.Name == sheetName).FirstOrDefault(); if (theSheet == null) { throw new ArgumentException("sheetName"); } Note The FirstOrDefault method returns either the first matching reference (a sheet, in this case) or null if no match was found. The code verifies 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 that you already retrieved provides an Id property, and given that Id property, the code can retrieve a reference to the corresponding WorksheetPart by calling the WorkbookPart GetPartById property. 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 else { // The sheet does exist. WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id)); Worksheet ws = wsPart.Worksheet; // Code removed here… } Retrieving the List of Hidden Row or Column Index Values As shown in the following code example, the code uses the detectRows parameter that you specified when you called the procedure to determine whether to retrieve information about rows or columns. If detectRows Then ' Retrieve hidden rows. ' Code removed here… Else ' Retrieve hidden columns. ' Code removed here… End If if (detectRows) { // Retrieve hidden rows. // Code removed here… } else { // Retrieve hidden columns. // Code removed here… } The following code example shows that the code that retrieves the list of hidden rows requires only a single line of code. itemList = ws.Descendants(Of Row). Where(Function(r) r.Hidden IsNot Nothing AndAlso r.Hidden.Value). Select(Function(r) r.RowIndex.Value).ToList() itemList = ws.Descendants<Row>(). Where((r) => r.Hidden != null && r.Hidden.Value). Select(r => r.RowIndex.Value).ToList<uint>(); This single line accomplishes much. 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 method converts the resulting IEnumerable into a List of unsigned integers. If there are no hidden rows, the returned list is empty. Retrieving the list of hidden columns is more complex 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, but 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). 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 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); } } Sample Procedure The following code example is the complete sample procedure. Public Function XLGetHiddenRowsOrCols( 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, True) 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 public static List<uint> XLGetHiddenRowsOrCols( 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; } The sample code included with this Visual How To retrieves a list of hidden rows or columns in a specified sheet in an Excel 2007 or Excel 2010 workbook. To use the sample, you must install the Open XML SDK 2.0, available from the link listed in the Explore It section. The sample also uses code included as part of a set of code examples for the Open XML SDK 2.0. The Explore It section also includes a link to the full set of code examples, although you can use the sample without downloading and installing the code examples. The sample application retrieves a list of hidden rows in a document that you supply, calling the XLGetHiddenRowsOrCols method in the sample to do the work. This method returns a generic list of unsigned integers. The calling code must interpret and iterate through the returned list. The method call is shown in the following code example. const string fileName = @"C:\temp\HiddenRowsCols.xlsx"; List<uint> items = XLGetHiddenRowsOrCols(fileName, "Sheet1", false); var sw = new StringWriter(); foreach (var item in items) sw.WriteLine(item); Console.WriteLine(sw.ToString()); It is important to understand how Excel stores information about hidden rows and columns. The Open XML SDK 2.0 includes, in its tool directory, a useful application named OpenXmlSdkTool.exe, shown in Figure 1. This tool enables you to open a document and view its various parts and the hierarchy of parts. Figure 1 shows a test document. In the left pane, the document has been expanded to the worksheet node, and in the right panes, the tool displays both the XML for the part and the reflected C# code that you could use to generate the contents of the part. Figure 1 shows the Open XML SDK 2.0 Productivity Tool that enables you to view the Open XML content of a document. Figure 1. Open XML SDK 2.0 Productivity Tool If you examine the left pane (the hierarchy of parts) and the XML content in Figure 1, you will learn what you must know in to understand the code in this Visual How To: To retrieve the list of hidden rows/columns, first locate the particular sheet that contains the cell. In the highlighted XML content on the right, you can see that the markup contains a collection of column information, and ranges of hidden columns include information that marks those columns as hidden. The Open XML SDK 2.0 exposes this attribute as the Hidden property of the Column object. The property might be null (if the column has never been hidden) or it might be true or false. Each Column object exposes a Min and Max property that the sample code uses to determine the first and last column in a group of columns. Figure 2 shows information for a single row and you can recognize that the row is hidden based on the hidden attribute. The Open XML SDK 2.0 exposes this attribute as the Hidden property of a Row object. Figure 2 shows how individual rows include information about their hidden status. Figure 2. Hidden status information in individual rows.

Watch the video > [!VIDEO https://www.microsoft.com/en-us/videoplayer/embed/62d2f3c7-62db-43ec-aac4-ebf7108924af] Length: 00:08:40 Grab the Code Open XML SDK 2.0 for Microsoft Office Open XML Format SDK 2.0 Code Snippets for Visual Studio 2008 Blog: Brian Jones & Zeyad Rajabi Open XML Developer Center Office Development in Visual Studio About the Author Ken Getz is a senior consultant with MCW Technologies. He is coauthor of ASP.NET Developers Jumpstart (Addison-Wesley, 2002), Access Developer's Handbook (Sybex, 2001), and VBA Developer's Handbook, 2nd Edition (Sybex, 2001).