Identify Non-Contiguous Named Ranges

Aspose.Cells Product Family
1

Dear Team,

We are using Aspose.cells in one of our application and we are having issues to get the non-contiguous ranges. Please let us know how we can get these ranges using Aspose.

Attaching the sample excel in which we have 3 named ranges. Range name ‘one’ is identified by the Aspose but not other two. Please help

Thanks
Anish

2

Hi Anish,

Thanks for your posting and using Aspose.Cells for .NET.

Please see the following code to access the named ranges that includes non-contiguous ranges too. Please see the output of the code below.

C#


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


Workbook workbook = new Workbook(filePath);


foreach (Name namedRange in workbook.Worksheets.Names)

{

Debug.WriteLine(namedRange.Text + ": " + namedRange.RefersTo);

}

Output:
one: =Sheet1!$D$3:$I$7
three: =Sheet1!$N$13:$U$17,Sheet1!$N$5:$U$10
Two: =Sheet1!$D$13:$I$16,Sheet1!$D$20:$I$24
3

Thanks for the reply

Will try this and get back. But meantime we have question "why the ranges are not returned using the methods GetNameRanges & GetRangeByName?"

we basically need to get the range object for further processing. Please provide your valuable comments on this

Thanks
Anish

4

Hi,

Well, Please see the sample code below for your reference, you may create Range object based on the Named range(s).

Sample code:

Workbook workbook = new Workbook(“e:\test2\Book1.xlsx”);
foreach (Name namedRange in workbook.Worksheets.Names)
{

Range[] rngs = namedRange.GetRanges();
if (rngs != null)
{

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

}


Hope, this helps a bit.

Thank you.

5

Hi, The above post regarding non continuous range we posted in 2013 and we have implemented successfully as you suggested but very recently we are using the same code but with latest version of aspose version=18.5.1, it stopped working. Any idea what are we doing wrong here? oXRngs count gives zero. Let me know if you need any other details.
foreach (Name namedRange in oXWb.Worksheets.Names)
{
if (namedRange.Text.ToUpper() == oDSWorksheet.SheetRange.ToUpper())
{
Range[] oXRngs = namedRange.GetRanges();
if (oXRngs != null)
{
//throw new ArgumentNullException("Worksheet is missing Contiguous Ranges - " + oDSWorksheet.Name);
}
}
}

6

@Cnu,

Thanks for your query.

Please share your sample file and runnable code snippet with us for our testing. We will reproduce the problem and provide our feedback after analysis.

7

Asposetest.zip (4.3 MB)

8

Same code worked with the template with the previous version aspose. whats changed why it is not working?

9

@Cnu,

I have tried your sample project with versions 18.4, 18.5, 18.5.1, 18.6 and 19.1. I am afraid that no difference is observed in the output of all these versions. Could you please have a look at the output of all these versions and identify the issue? Provide details along with the images for our analysis.

output.zip (375 Bytes)

10

Hi
As I mentioned it could find the range that I am looking for but its not able to find we ask for GetRanges which gives the count zero, same template worked with old versions and used to return all the range values. I have attached the screen shots for your referenceUntitled.png (74.2 KB)
Untitled1.png (92.8 KB)
.

11

I am not able to download/open the Zip that you have attached.

12

@Cnu,
You may please download the output file from the following link:

Also please provide us the detailed steps to reproduce this issue as no difference is observed using your sample project with different versions mentioned above. We will analyze your information and provide feedback accordingly.

13

Please see my previous post i have attached the screenshots.

14

@Cnu,

I am afraid that these images are not clarifying the exact issue. As mentioned above, I have executed your code with different versions, but could not observe any difference among the outputs created by them. Please provide us comparison of old versions and new versions using images and detailed description. Also mention the exact versions which are used for those comparisons. We will analyze information in detail and provide our feedback.

15

@Cnu,

We have investigated your scenario a bit, for the “namedRange” object which gives zero length of ranges, the range references are all external. For external data it is not possible to create Range object. Maybe it is a bug for old versions which can create ranges for this defined Name and we think the generated Range object may reference to an incorrect sheet. So, for the external references, Name.GetRanges() is not the proper way to get the “range”. If you do need to get the similar “range” information for it, we may make further investigation to provide a solution.

Let us know your feedback.

16

Hi
Thank you for doing further investigation on this. Problem here is the previous version is ignoring the external range references, considering only the range referenced within the same excel and process was working as expected. Newer version of the aspose with the same template is considering the external range and returns zero length. So in our production system its failing do the required business functionality. is this something bug in older or newer I am not sure. Let me know if you need any more information.

17

@Cnu,

For a detailed investigation, please share the exact version which was working as per your expectation. It will help us to observe the issue and provide assistance.

18

Version 7.3.5.0

19

@Cnu,

We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-46598 - Name.GetRanges() behavior difference in old and new versions
20

Thank you. keep us posted.