Read or retrieve value from spreadsheet using existing rangenames

Question

I have an existing excel file and it has already defined cell name or cell range names.

I am able to get all cell range names using Openxml sdk. My sample code is given below:

 var path = @"D:\test.xlsx";
        using (var document = SpreadsheetDocument.Open(path, true))
        {
            var workbookPart = document.WorkbookPart;
              var wb = workbookPart.Workbook;
            var definedNames = wb.DefinedNames;
            if (definedNames != null)
            {
                System.Console.WriteLine("Name\tText\tName.Value");
                foreach (DefinedName dn in definedNames)
                {
                    System.Console.WriteLine(dn.Name + "\t" + dn.Text + "\t" + dn.Name.Value);

                }
            }

        }

Is there any way to retrieve or read cell value from defined name using this OpenXml or any other SDK in c#?

Answer 1

Accessing Named Ranges

If you have one or more Named Ranges you can access them in different ways:

A specific range/cell in the named range

// worksheet scope
var range = worksheet.Range("NameOfTheRange");
var cell = worksheet.Cell("NameOfTheRange");

// workbook scope
var range = workbook.Range("NameOfTheRange");
var cell = workbook.Cell("NameOfTheRange");

All ranges/cells specified in the named range (yes a named range can point to many ranges/cells)

// worksheet scope
var ranges = worksheet.Ranges("NameOfTheRange");
var cells = worksheet.Cells("NameOfTheRange");

// workbook scope
var ranges = workbook.Ranges("NameOfTheRange");
var cells = workbook.Cells("NameOfTheRange");

Worksheet scope from the workbook
One handy way to access named ranges is to access worksheet's range from the workbook.
For example:

var range = workbook.Range("Sheet1!Result");
var cell = workbook.Cell("Sheet1!Result");

Scope:
If you ask for a named range in a worksheet then ClosedXML will look on the worksheet and then the workbook if it can't find it.

For example, after creating a named range with workbook scope you can access it from either the workbook or worksheet (as long as there isn't one on the worksheet already.

// Create a range with workbook scope (the default)
worksheet.RangeUsed().AddToNamed("Result");

// Access it from the workbook:
var range = workbook.Range("Result");

// Access it from the worksheet:
// What happens here is that it will try to get the named range
// on the worksheet, when it fails it then gets the named range
// on the workbook
var range = worksheet.Range("Result");

Can't find it?
A null is returned if the named range doesn't exist.

Reference: Accessing Named Ranges