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
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,
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.
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