Aspose.Cells fail to fetch GetRangeByName when range is a formula

Aspose.Cells Product Family
1

Hi team,

In newer versions of Aspose if a Name.RefersTo has something like =INDIRECT(ADDRESS(1,1,INDIRECT(“B1”))) then, Worksheets.GetRangeByName throws Exception.

I’m using Aspose 25.3.0,
This wasn’t happening in previous version like 24.11. 0

I’ve also tested this with latest version ( 25.10.0 )

Here is the sample code :

var asposeName = workbook.Worksheets.Names.FirstOrDefault(n => n.FullText.Contains(name));
if (asposeName != null)
{
var range = workbook.Worksheets.GetRangeByName(asposeName.FullText);
}

Here is a sample workbook with a single Name in it:

test_name.zip (6.7 KB)

Cheers,

2

@yashar.heydari

It appears you are experiencing an issue with the GetRangeByName method in Aspose.Cells when the named range refers to a formula that includes INDIRECT. This issue seems to have emerged in newer versions of Aspose.Cells, particularly starting from version 25.3.0, while it functioned correctly in earlier versions like 24.11.0.

To troubleshoot this issue, consider the following steps:

  1. Check for Updates: Verify that you are using the latest version of Aspose.Cells, as the issue may have been resolved in subsequent releases after 25.3.0.

  2. Simplify the Formula: If feasible, simplify the formula used in the named range to determine if a less complex formula operates without errors.

  3. Use Alternative Methods: Instead of relying on GetRangeByName, you can directly access the cell or range using its coordinates if you know them. For example:

    var cell = workbook.Worksheets[0].Cells[0, 0]; // Accessing cell A1 directly

  4. Debugging: Implement error handling around the GetRangeByName call to capture and log the specific exception message, which may provide further insight into the issue.

If the problem persists, consider reaching out to Aspose support or checking their forums for similar issues reported by other users.

Feel free to share any specific error messages you encounter, and I can assist you further!

3

As I mentioned I’ve already

  1. Tested with latest version (Checked the updates)
  2. Cannot simplify, as this is a client workbook.
  3. GetRangeByName is part of the process and not just for accessing a cell.
  4. Exception looks like this:

Unhandled exception. System.NullReferenceException: Object reference not set to an instance of an object.
atnt32 )
at
t ispose()
at .Dispose()
at Aspose.Cells.Name.(Boolean , )
at Aspose.Cells.WorksheetCollection.GetRangeByName(String rangeName)
at AsposeTest.Processor.ReadName(String name) in

4

@yashar.heydari,

Please try the following sample code to retrieve the named ranges in the workbook. I have tested using your template Excel file and it works fine and as expected.

Workbook workbook = new Workbook("e:\\test2\\test_name.xlsx");
var names = workbook.Worksheets.Names;
for (int i = 0; i < names.Count; i++)
{

   Console.WriteLine("Name:" + names[i].Text + ", Referes To: " + names[i].RefersTo + ", Visible:  " + names[i].IsVisible);

}

console output:

Name:my_sheet, Referes To: =INDIRECT(ADDRESS(1,1,INDIRECT(“B1”))), Visible: True

Hope, this helps a bit.

5

Hi @amjad.sahi

I could use this approach to fetch the Refers To, but isn’t this what GetRangeByName does? Also I’m using this method in different places on the code and now I have to change them all? Plus in workbooks with lots of names this isn’t a very efficient way of reading it.

Just curious if you will be fixing this issue in the next releases?

thank you

6

@yashar.heydari
We can reproduce the issue by testing on the latest version v25.10 using the following sample code and sample file. NullReferenceException occurs when calling WorksheetCollection.GetRangeByName method.

Workbook workbook = new Workbook(filePath + "test_name.xlsx");
string name = "my_sheet";
var asposeName = workbook.Worksheets.Names.FirstOrDefault(n => n.FullText.Contains(name));
if (asposeName != null)
{
    var range = workbook.Worksheets.GetRangeByName(asposeName.FullText);
}

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-59252

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.