Retrieving Dictionaries of All Named Ranges in Excel 2010 Workbooks by Using the Open XML SDK 2.0

The Open XML file formats make it possible to retrieve information about defined names in an Excel workbook, but doing so requires some effort. The Open XML SDK 2.0 adds strongly typed classes that simplify access to the Open XML file formats: The SDK simplifies the tasks of retrieving, in particular, the list of defined names that each workbook maintains. The code sample included with this Visual How To shows how to use the SDK to accomplish this goal.

The sample provided with this Visual How To includes the code necessary to retrieve a dictionary containing information about each defined name in an Excel 2007 (or later) document. Within the dictionary, each item's key contains the name for the range, and the value contains a string representation of the range itself. For example, Figure 1 shows the results of looping through the contents of the resulting dictionary for a sample workbook. The following sections walk you through the code.

Setting Up References

To use the code from the Open XML SDK 2.0, you must add a few references to your project. The sample project already includes these references, but in your own code, you would need to explicitly reference the following assemblies:

• WindowsBase─This reference may be set for you, depending on the type of project 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

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

Examining the Procedure

The XLGetDefinedNames procedure accepts a single parameter, indicating the name of the document from which you want to retrieve the table of contents (string). The procedure returns a Dictionary instance containing information about the defined names within the specified workbook, which may be empty if there are no defined names.

Public Function XLGetDefinedNames(
  ByVal fileName As String) As Dictionary(Of String, String)

public static Dictionary<String, String> 
  XLGetDefinedNames(String fileName)

The procedure examines the workbook that you specify, looking for the part that contains defined names. If it exists, the procedure iterates through all the contents of the part, adding the name and value for each defined name to the returned dictionary. The calling code in the sample iterates through the dictionary, displaying the key and value from each item.

Dim result = XLGetDefinedNames("C:\temp\definednames.xlsx")
For Each dn In result
  Console.WriteLine("{0}: {1}", dn.Key, dn.Value)
Next

var result = XLGetDefinedNames(@"C:\temp\definednames.xlsx");
foreach (var dn in result)
  Console.WriteLine("{0} {1}", dn.Key, dn.Value);

Accessing the Document

The code starts by creating a variable named returnValue that the procedure will return before it exits.

Dim returnValue As New Dictionary(Of String, String)
' Code removed here…
Return returnValue

var returnValue = new Dictionary<String, String>();
// Code removed here…
return returnValue;

The code continues by opening the workbook, using the SpreadsheetDocument.Open method and indicating that the workbook should be open for read-only access (the final false parameter). Given the open workbook, the code uses the WorkbookPart property to navigate to the main workbook part. The code stores this reference in a variable named wbPart.

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))
{
  var wbPart = document.WorkbookPart;
  // Code removed here.
}

Retrieving Defined Names

Given the workbook part, the next step is simple─the code uses the Workbook property of the wbPart variable, and then retrieves the DefinedNames collection provided by the Open XML SDK 2.0. This property returns a collection of all of the defined names contained within the workbook. If the property returns a non-null value, the code then iterates through the collection, retrieving information about each named part and adding the key (name) and value (range description) to the dictionary for each defined name.

Dim definedNames = wbPart.Workbook.DefinedNames
If definedNames IsNot Nothing Then
  For Each dn As DefinedName In definedNames
    returnValue.Add(dn.Name.Value, dn.Text)
  Next
End If

DefinedNames definedNames = wbPart.Workbook.DefinedNames;
if (definedNames != null)
{
  foreach (DefinedName dn in definedNames)
    returnValue.Add(dn.Name.Value, dn.Text);
}

Sample Procedure

The following code example contains the complete sample procedure.

Public Function XLGetDefinedNames(
  ByVal fileName As String) As Dictionary(Of String, String)

  ' Given a workbook name, return a dictionary of defined names.
  ' The pairs include the range name and a string 
  ' representing the range.

  Dim returnValue As New Dictionary(Of String, String)
  Using document As SpreadsheetDocument =
    SpreadsheetDocument.Open(fileName, False)
    Dim wbPart As WorkbookPart = document.WorkbookPart

    Dim definedNames = wbPart.Workbook.DefinedNames
    If definedNames IsNot Nothing Then
      For Each dn As DefinedName In definedNames
        returnValue.Add(dn.Name.Value, dn.Text)
      Next
    End If
  End Using
  Return returnValue
End Function

public static Dictionary<String, String> 
  XLGetDefinedNames(String fileName)
{
  // Given a workbook name, return a dictionary of defined names.
  // The pairs include the range name and a string 
  // representing the range.

  var returnValue = new Dictionary<String, String>();
    using (SpreadsheetDocument document = 
    SpreadsheetDocument.Open(fileName, false))
  {
    var wbPart = document.WorkbookPart;
    DefinedNames definedNames = wbPart.Workbook.DefinedNames;
    if (definedNames != null)
    {
      foreach (DefinedName dn in definedNames)
        returnValue.Add(dn.Name.Value, dn.Text);
    }
  }
  return returnValue;
}

The sample included with this Visual How To demonstrates code that retrieves a dictionary containing information about defined names in an Excel workbook. To make use of 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 a modified version of code included as part of a set of code snippets for the Open XML SDK 2.0. The Explore It section also includes a link to the full set of snippets, although you can use the sample without downloading and installing the snippets.

To understand what the sample code is doing, it is useful to examine the contents of the workbook using the Open XML SDK 2.0 Productivity Tool for Microsoft Office, which is included as part of the Open XML SDK 2.0. Figure 2 shows a sample workbook that contains several defined names, opened in the tool. The sample code retrieves a reference to the Workbook part, and within that part, locates the definedNames element, which contains one child element for each defined name in the workbook.

The Open XML SDK 2.0 returns the collection of defined names using the DefinedNames property of the workbook. You could certainly retrieve the same information without using the SDK, but it is easier to do so using the strongly typed members of the SDK.

The sample application demonstrates only a handful of the available properties and methods provided by the Open XML SDK 2.0 that you can interact with when retrieving information about workbooks. For more information, investigate the documentation that comes with the Open XML SDK 2.0 Productivity Tool: Click the Open XML SDK Documentation tab in the lower-left corner of the application window, and search for the class you need to study. Given the sample shown here and the documentation, you should be able to successfully modify the sample application.

Length: 00:06:04

Grab the Code

• Open XML SDK 2.0 for Microsoft Office

• Open XML Format SDK 2.0 Code Snippets for Visual Studio 2008

• Office Developer Center

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