We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Aspose Ranges that are determined dynamically with an offset function

In the attached workbook, you can see the refers to range is :
=OFFSET(Sheet1!D7,0,0,ROW(Sheet1!D23)-ROW(Sheet1!D7),COLUMN(Sheet1!K7)-COLUMN(Sheet1!D7))
xbrzRKC4PR.png (17.3 KB)

When loading with Aspose.cells, the range is not the same as Excel:

lJFOx6pUlt.png (12.3 KB)

The code I used:

        using (var workbook = new Aspose.Cells.Workbook("RefersTo.xlsx"))
        {
            var names = workbook.Worksheets.Names["REFERSTO"];

            System.Diagnostics.Debug.Print(names.RefersTo);
        }

PFA File:
RefersTo.zip (8.5 KB)

@Moonglum,

Please notice, after an initial test, I am able to reproduce the issue as you mentioned by using your template file. I found an issue with retrieving Ranges (RefersTo) that are determined dynamically with an offset function. I have logged a ticket with an id “CELLSNET-49192” for your issue. We will look into it soon.

Once we have an update on it, we will let you know.

We evaluated your issue in details. The references in the formula of the named range are relative, so the formula expression depends on the focused cell. For Name.RefersTo property, we always take the cell base as “A1”. In our upcoming fix/version, we will provide overloaded method to provide users the specific cell based expression for the defined Name:

Name.GetRefersTo(bool isR1C1, bool isLocal, int row, int column)

For your case, current focused cell is “M7”(row is 6 and column is 12), so you may get the expected output by:

System.Diagnostics.Debug.Print(names.GetRefersTo(false, false, 6, 12));

But I dont think that will fix the problem. I need to get the range object for a range name. I won’t know the name 's range object ahead of time. That is what I need to get.

In Excel I can do this:

Dim r As Range
Dim wb As Workbook
Dim n As Name

Set wb = ActiveWorkbook
Set n = wb.Names("REFERSTO")
Set r = n.RefersToRange
Debug.Print r.Address

against that workbook and I get range object for that name.

I need to be able to get the range object from a name. or at least a refersto that I can use to get the range.

@Moonglum,

Thanks for your feedback.

We will evaluate your concerns and get back to you soon.

@Moonglum,

Please note, there is an API for your requirement already:

Name.GetRange(int sheetIndex, int row, int column)

So, you may try:

....

Range r = n.GetRange(0, 6, 12);
...

Hope, this helps a bit.

But get range is not returning the correct range. I cant use getrange with parameters because I don’t won’t know what the range refers to ahead of time.

ZJoPlOygIf.png (6.9 KB)

@Moonglum,
We have noted your concern and will share our comments after analyzing it.

@Moonglum,
You may check in ms excel manually, then you can find the referenced ranges are different for different cells. You should determine which one cell is the base to get the referenced range. For your template file and requirement, maybe by default the base cell is the active cell. If so, please use Worksheet.ActiveCell to get the parameters.

The issues you have found earlier (filed as CELLSNET-49192) have been fixed in this update. This message was posted using Bugs notification tool by simon.zhao