References (named ranges) issue

Aspose.Cells Product Family
1

Some of our financial reports have references to each other (by using Excel named ranges). Aspose fails to combine them in one correctly. It's regression comparing to Excel. Please, fix.

Aspose keeps any unknown reference as a named range linking to nothing (can be checked in the list .Worksheets.Names).

E.g. If a sheet contains "=NamedRangeA" and "NamedRangeA" is not defined, NamedRangeA will be in the list of named ranges linking to null. It's different to Excel, that will keep NamedRangeA just as a text.

The problem occurs when another sheet with defined named range "NamedRangeA" is copied to the same workbook. Names collide and "good" "NamedRangeA" is stored as only sheet-wide named range. "NamedRangeA" on the first sheet will remain an empty named range.

In the attached example, on the generated workbook, you can see that references on Sheet1 in cells D9, D10, D12, D13 were not resolved. The same logic on Sheet2, cell D17, was correct.



2

Hi,


Thanks for providing us sample project with template files.

I have evaluated your scenario/ case using your sample project a bit. I observed the issue as you mentioned. To evaluate your issue properly and to log it as an issue into our database (after confirmation), please attach your output Test.xlsx file and your desired Excel file that you manually create in MS Excel performing the steps as per your sample code for copying worksheets (in the two workbooks) to a new workbook accordingly. I have to compare your provided output files with my files to clarify the things accurately so I could log the ticket properly.

Thank you.
3

Hi Amjad,
I’ve attached 2 input Excel files/sheets, the result generated by Aspose and the result I receive after manual copy of 2 input sheets to a new one.
Choosing Formulas | Name Manager in Excel you can see that all references in Excel result are still workbook-wide, and Aspose result has many sheet-wide references (problematic ones).
Regards,
Alex

4

Hi,


Thanks for providing us sample files.

It helps us to evaluate your issue properly. I have logged a ticket with an id “CELLSNET-42406” for your issue now. We will look into it to figure it out soon.

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

Thank you.
5

Hi,

Thanks for using Aspose.Cells.

We have checked your issue further. Both the File1 and the File2
contain the Global named range “RANGE1”. The “RANGE1” of the File1 should be
copied to the new workbook as some formulas refer to it.

When copying the
File2 to the new workbook, the Global named range “RANGE1” has been existed, so
the “Range1” of the File2 can only belong to the Sheet.

And the existed Global
could not be simply replaced because we could not know whether those old
formulas (which refer to it) should refer to the new named range.

There
is a temporary solution for yours need:

C#

Workbook wSource1 = new
Workbook(@“D:\FileTemp\Aspose+Demo++(Named+ranges)\Aspose Demo (Named
ranges)\bin\Debug\Files\File1.xlsX”);
Workbook wSource2 = new
Workbook(@“D:\FileTemp\Aspose+Demo++(Named+ranges)\Aspose Demo (Named
ranges)\bin\Debug\Files\File2.xlsX”);
Workbook wReport = new
Workbook();
wReport.Worksheets[0].Name =
wSource1.Worksheets[0].Name;

wReport.Worksheets.Add(wSource2.Worksheets[0].Name);
CopyOptions
options = new CopyOptions();
options.CopyNames =
false;
NameCollection names =
wReport.Worksheets.Names;
foreach (Name name in
wSource1.Worksheets.Names)
{
if (name.RefersTo
== null || name.RefersTo == “#REF!”)

continue;
int index =
names.Add(name.Text);
names[index].RefersTo =
name.RefersTo;
}
foreach (Name name in
wSource2.Worksheets.Names)
{
if (name.RefersTo
== null || name.RefersTo == “#REF!”)

continue;
int index =
names.Add(name.Text);
names[index].RefersTo =
name.RefersTo;
}

wReport.Worksheets[0].Copy(wSource1.Worksheets[0], options);
//
wReport.Worksheets.Add();

wReport.Worksheets[1].Copy(wSource2.Worksheets[0], options);


wReport.Save(@“D:\filetemp\dest.xlsx”);

There are some limitations here
:

a) The copied name of the sheet should be same as the source sheet.

b) The
named range could not refer to other worksheet.

6

Thank you for your reply and for workaround.
Your explanation is how named ranges are treated by Aspose. That’s exactly the issue, due to Excel does it a bit differently.

>>> Both the File1 and the File2
contain the Global named range "RANGE1"

Excel logic:
File1 doesn’t define Range1, it only reference/uses it in a formula. Excel Name Manager won’t show Range1 in its list. Also, you won’t find any definition of Range1 if you unzip and check File1 internals. The same is true if you copy File1 sheet to a new workbook. Only after you copy File2 sheet with global definition of Range1, it will be defined in a new workbook.

Regards,
Alex

7

Hi Alex,

Thanks for your posting and using Aspose.Cells.

We are afraid, we cannot fix this issue. We cannot replace the existing global name. Kindly use the temporary workaround as we suggested you earlier.