Access Named Ranges in Sheet Scope in Excel in .NET

Aspose.Cells Product Family
1

Dear Team,

Require help regarding .NET Aspose.Cells
I am having a worksheet with different named ranges with sheet scope & Workbook scopes
I need to access them using their names at runtime and assign some value can we do this using Aspose
I tried workbook.Worksheets.Names and this return name objects collection. How can we assign values to a specific range using the name object.

Thanks
Anish

2

Hi,

Thanks for your posting and using Aspose.Cells.

In order to assign values to the named range, you will have to iterate individual cells inside the named range and insert values using Cell.PutValue().

If you want to change the named range address, then you can do it using Name.RefersTo property.

C#

string filePath = @“F:\Shak-Data-RW\Downloads\source.xlsx”;


Workbook workbook = new Workbook(filePath);

Worksheet worksheet = workbook.Worksheets[0];


Name name = workbook.Worksheets.Names[0];


Range range = workbook.Worksheets.GetRangeByName(name.Text);


//You can update range using this property

name.RefersTo = “=” + worksheet.Name + “!” + “A1:D10”;


workbook.Save(filePath + “.out.xlsx”, SaveFormat.Xlsx);
3

Hi,


Moreover, we would recommend you to see the document for your complete references on Named Ranges:
http://www.aspose.com/docs/display/cellsnet/Named+Ranges

As my colleague told you that you have to iterate through the individual cells in a range to update any value for your needs. If you need to put a single value to all the cells in a range, you may use Range.Value attribute. If you need to put different values into the cells of a range, then you have to iterate through the cells and put your desired values accordingly, see a code snippet below for reference:

//…

Range[] aRange = workbook.Worksheets.Names[stringName].GetRanges();

foreach (Range aArea in aRange)

{

for (int r = aArea.FirstRow, x = 0; x < aArea.RowCount; x++, r++)

for (int c = aArea.FirstColumn, y = 0; y < aArea.ColumnCount; y++, c++)

workbook.Worksheets[SheetName].Cells[r, c].PutValue(YourValue);

}

//…


Thank you.

4

Hi,

Thanks for the reply. I understood how we can access the ranges collection in a workbook. My doubt is in Aspose can we access a named range with sheet scope using the following?

Dim range As Range = workbook.Worksheets.GetRangeByName("TestRange")
range.Value = "Test"

Question:- How can I directly access a range with sheet scope with name (eg: TestRange) . If I use the above syntax will it be available

Thanks
Anish

5

Hi,


Well, if a range is worksheet scoped, then you need to add worksheet’s name before range’s name to access it when using GetRangeByName method, see the sample code for reference.

e.g

Dim worksheet As Worksheet = workbook.Worksheets(1)
Dim range As Range = workbook.Worksheets.GetRangeByName(worksheet.Name & “!” & “MyRange”)

Thank you.
6

Thanks a lot

Anish

7

Hi,


You are welcome,

Feel free to contact us any time, if you need further help or have more queries. We will be happy to assist you.

Thank you.