Resolve non sequenced named ranges by name

Aspose.Cells Product Family
1

Hi there

I see that we can create non sequenced (i.e. non-continuous) named ranges from code. Can such ranges already present in a sheet be accessed in any way? The most recent answer I found for it (This message was posted using Page2Forum from Implementing Non-Sequenced Ranges - Aspose.Cells for .NET

2

Hi Lukas,

Thank you for considering Aspose.

Well, you can use Name.GetRanges() API to get all the ranges in a non-sequenced named range. Please check the following sample code and see if it fits your requirement.

Sample Code:

Workbook workbook = new Workbook();

int index = workbook.Worksheets.Names.Add("NonSequencedRange");

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

name.RefersTo = "=Sheet1!$A$1:$B$3,Sheet1!$D$5:$E$6";

name = workbook.Worksheets.Names["NonSequencedRange"];

Range [] ranges = name.GetRanges();

if (ranges != null)

{

for (int i = 0; i < ranges.Length; i++)

{

MessageBox.Show(ranges[i].FirstRow + ":" + ranges[i].FirstColumn);

}

}

Thank You & Best Regards,

3

Hi,

Moreover, if you want to get complete boundaries for the ranges, you may see the code below. I used the template file “Book1.xls” (in the other thread you referenced) and obtained the ranges, it works fine.

Sample code:

Workbook workbook = new Workbook();
workbook.Open(“f:\test\nons\Book1.xls”);
Worksheets sheets = workbook.Worksheets;
Range[] rngs = sheets.Names[“Teds”].GetRanges();
MessageBox.Show(rngs.Length.ToString());
if (rngs != null)
{

for (int i = 0; i < rngs.Length; i++)
{
MessageBox.Show(CellsHelper.CellIndexToName(rngs[i].FirstRow, rngs[i].FirstColumn) + “:” + CellsHelper.CellIndexToName(rngs[i].FirstRow + rngs[i].RowCount - 1, rngs[i].FirstColumn + rngs[i].ColumnCount - 1));
}
}


Thank you.

4

Mea culpa

The answer is already in the mentioned post, halfway down. I didn't notice.

Use workbook.Worksheet.Names["..."] instead of workbook.Worksheet.GetRangeByName("...") to resolve non sequenced named ranges.

Cheers

Lukas