How to: Retrieve a dictionary of all named ranges in a spreadsheet document (Open XML SDK)

The GetDefinedNames procedure accepts a single parameter that indicates the name of the document from which to retrieve the defined names. The procedure returns an Dictionary instance that contains information about the defined names within the specified workbook, which may be empty if there are no defined names.

The method examines the workbook that you specify, looking for the part that contains defined names. If it exists, the code iterates through all the contents of the part, adding the name and value for each defined name to the returned dictionary.

To call the sample method, pass a string that contains the name of the file from which to retrieve the defined names. The following code example passes a string that contains the name of the file from which to retrieve the defined names and iterates through the returned dictionary, and displays the key and value from each item.

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

The code continues by opening the spreadsheet document, using the Open method and indicating that the document 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.

Given the workbook part, the next step is simple. The code uses the Workbook property of the workbook part to retrieve a reference to the content of the workbook, and then retrieves the DefinedNames collection provided by the Open XML SDK 2.5. This property returns a collection of all of the defined names that are 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.

The following is the complete GetDefinedNames code sample in C# and Visual Basic.

Other resources