Export (0) Print
Expand All
Expand Minimize

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

Office 2010

Office Visual How To

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.

Last modified: September 12, 2012

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

Published:  August 2010

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

Overview

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.

Code It

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.

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

NoteNote

Rows and columns are numbered starting at 1 instead of 0.

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.

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

NoteNote

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.

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

NoteNote

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.
  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)
{
  // 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<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).

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 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;
}
Read It

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

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.

Hidden status information in individual rows
See It

Watch the video

Watch video

Length: 00:08:40

Click to grab code

Grab the Code

Explore It

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

Show:
© 2014 Microsoft