Worksheets.GetRangeByName() returns incorrect address when name returns to relative cell address

Aspose.Cells Product Family
,
1

Hello,

It appears that Worksheets.GetRangeByName() and Workbooks.Names[] return incorrect cell address when a named range refers to relative cell address. We are using Aspose Cells version 20.12.0.

In the attached workbook are two named ranges: NAME_HELLO refers to an absolute cell address of $F$10; and NAME_FOO refers to a relative address of F11. When Worksheets.GetRangeByName() is called with NAME_FOO the methods returns a range object whose address is A1048576 instead of F11.

We observe a similar behaviour when Worksheets.Names[] is called with NAME_FOO. Name.RefersTo returns a value of =Model!A1048576 instead of =Model!F11.

When a name refers to an absolute address, both Worksheets.GetRangeByName() and Name.RefersTo returns the expected values.

The sample test code below demonstrates the behaviour of Worksheets.GetRangeByName() and Worksheets.Names[] when a named range refers to an absolute address and relative address.

Thanks,

Stanley

Sample workbook: bare-workbook.zip (6.3 KB)

using Aspose.Cells;
using NUnit.Framework;

namespace AsposeTests
{
    [TestFixture]
    public class AsposeTests
    {
        [TestCase("NAME_HELLO", "F10", "=Model!$F$10")]
        [TestCase("NAME_FOO", "F11", "=Model!F11")]
        public void BareMinimumCase(string name, string expectedAddress, string expectedRefersTo)
        {
            string fullFilePath = $"c:\\temp\\bare-workbook.xlsx";

            var asposeWorkbook = new Workbook(fullFilePath);

            var asposeNameObj = asposeWorkbook.Worksheets.Names[name];
            Assert.AreEqual(expectedRefersTo, asposeNameObj.RefersTo);

            var asposeRangeObj = asposeWorkbook.Worksheets.GetRangeByName(name);
            Assert.AreEqual(expectedAddress, asposeRangeObj.Address);
        }
    }
}
2

@skwok,
We have observed this issue and logged it in our database for further investigation. You will be notified here once any update is ready for sharing.

This issue is logged as:
CELLSNET-47859 - GetRangeByName returns incorrect address when a named range refers to relative cell address

3

@skwok,

For relative references of defined Name, the reference varies according to the cell with “focus”. Without specified base cell, we take it as A1 to get the referenced address. When you select A1 and then view the Name in “Name Manager” of MS Excel, you will find the “Refers to” is also A1048576.

To get the range as F11, please specify the base cell as F12 by the method: Name.GetRange(int sheetIndex, int row, int column).

For property Name.RefersTo, currently we only provide the value based on A1. If you need we may consider to provide new method to get RefersTo base on user specified active cell.