Formula 'DAYS' in Excel tables gets replaced with a NamedRange

Aspose.Cells Product Family
1

Hi,

I found out a very strange behaviour when copying a worksheet containing a Table with slightly complex formula on its columns.
Basically I need to copy a worksheet several times in order to be filled with different data sets and remove the original sheet after copy.
In my case the original sheet contains an Excel table with formulae in its columns (see the attached file).
Moreover my Excel file contains also some NamedRanges (in the example there is a named range called ‘Another’).
By running the code below a strange thing happens:
the formula contained in column D2
=IF(IF(ISBLANK([@[Current Projected End Date]]);0;DAYS([@[Current Projected End Date]];[@[Original Projected End Date]]))<0;0;IF(ISBLANK([@[Current Projected End Date]]);0;DAYS([@[Current Projected End Date]];[@[Original Projected End Date]]))))
becomes:
=IF(IF(ISBLANK([@[Current Projected End Date]]);0;Another([@[Current Projected End Date]];[@[Original Projected End Date]]))<0;0;IF(ISBLANK([@[Current Projected End Date]]);0;Another([@[Current Projected End Date]];[@[Original Projected End Date]])))

Basically the same formula except for the fact that DAYS has been replaced with the name of the first named range ‘Another’, thus resulting in a Excel formula error.

The code:
Workbook wb = new Workbook(“Test.xlsx”);
var sourceWorksheet = wb.Worksheets[0];
Worksheet newWorksheet = wb.Worksheets.Add(“NewCopySheet”);
newWorksheet.MoveTo(sourceWorksheet.Index + 1);
newWorksheet.Copy(sourceWorksheet);
wb.Worksheets.RemoveAt(sourceWorksheet.Name);
wb.Save(“output.xlsx”);



2

Hi,


Thanks for the template file, sample code and details.

After an initial test, I am able to reproduce the issue as you mentioned by using your sample code with your template file. I found that the formula/function ‘DAYS’ in Excel tables gets replaced with a NamedRange. By running the following sample code, the formula contained in the cell D2, i.e.,
"=IF(IF(ISBLANK([@[Current Projected End Date]]);0;DAYS([@[Current Projected End Date]];[@[Original Projected End Date]]))<0;0;IF(ISBLANK([@[Current Projected End Date]]);0;DAYS([@[Current Projected End Date]];[@[Original Projected End Date]]))))"
becomes:
“=IF(IF(ISBLANK([@[Current Projected End Date]]);0;Another([@[Current Projected End Date]];[@[Original Projected End Date]]))<0;0;IF(ISBLANK([@[Current Projected End Date]]);0;Another([@[Current Projected End Date]];[@[Original Projected End Date]])))”
e.g
Sample code:

Workbook wb = new Workbook(“Test.xlsx”);
var sourceWorksheet = wb.Worksheets[0];

Worksheet newWorksheet = wb.Worksheets.Add(“NewCopySheet”);
newWorksheet.MoveTo(sourceWorksheet.Index + 1);
newWorksheet.Copy(sourceWorksheet);
wb.Worksheets.RemoveAt(sourceWorksheet.Name);
wb.Save(“output.xlsx”);

I have logged a ticket with an id “CELLSNET-45261” for your issue. We will look into it soon.

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

Thank you.
3
Hi,

Thanks for using Aspose.Cells.

This is to inform you that we have fixed your issue CELLSNET-45261 now. We will soon provide the fix after performing QA and including other enhancements and fixes.
4
Hi,

Thanks for using Aspose.Cells.

Please download and try the following latest fix

Aspose.Cells for .NET v17.3.6 (.NET 2.0) compiled in .NET Framework 2.0.
Aspose.Cells for .NET v17.3.6 (.NET 4.0) compiled in .NET Framework 4.0.

and let us know your feedback.
5

Thanks,

the fix works fine for both the test case and the real production scenario.
I’ll be waiting for the next offical release.
6

Hi,


Good to know that your issue is sorted out by the fix/version. Hopefully, our next official release: Aspose.Cells v17.4.0 would be released around the mid of April, 2017, the date is not finalized yet.

Thank you.
7

The issues you have found earlier (filed as CELLSNET-45261) have been fixed in Aspose.Cells for .NET 17.4.0.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.