CODEWITHSUNDEEP
c#.netexcelopenxml-sdkclosedxml
Ravi Anand
Ravi Anand

Reputation: 5544

Read or retrieve value from spreadsheet using existing rangenames

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#?

Upvotes: 3

Views: 2277

Answers (2)

Francois Botha
Francois Botha

Reputation: 4849

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

Upvotes: 2

Florian Piper
Florian Piper

Reputation: 1

I think you are searching for this

using System.Xml;

XmlNodeList elemList = doc.GetElementsByTagName(@"XML node");

                for (int i = 0; i < elemList.Count; i++)
                {
                    //dosomthing with   
                    elemList[i].Attributes["AttributeName"].Value;
                }

Greetings

Upvotes: 0

Related Questions