Issue with formulas referencing data tables and saving as PDF

Aspose.Cells Product Family
1

Hello,


I have today found an issue with how Aspose.Cells calculates formulas, it behaves very differently from Excel when calculating formulas which reference columns in a data table (e.g. “=Data[Column1]”).

Attached is a test case demonstrating the issue.

The process :-

In the attached project, there is an excel document (Source.xlsx), which has two sheets.

The first sheet (Report) is the report we wish to save to PDF.

The second sheet (DataTable) is the table of data used for the source of the report (in the final template, this sheet would be hidden)

Data is added to the DataTable sheet, and the report is saved as a PDF document (Report.pdf) and a copy of the changed spreadsheet is saved (Report.xlsx).

The issue :-

When first loaded, the report (this is a test case, so it’s just three cells with formulas) will show the data as calculated from the template data source (OLD1, OLD2 and OLD3).

The test case overwrites the data with three new entries (New1, New2 and New3).

However, when the formulas are re-calculated, instead of showing New1, New2 and New3 as expected – and as how excel would calculate then – the report will show Column1, Column2 and Column3 (the headers).

But, if you open the saved copy of the changed spreadsheet, excel will auto refresh the formulas and show the correct data (New1, New2 and New3).

This would appear to be a bug in the way Aspose.Cells calculates these formulas, or I’m doing something wrong somewhere.

Please let me know.

/James.
2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

After running your code, I got the following console output. Please let us know if it is correct. In case, it is wrong, let us know your expected console output to investigate this issue further.

Console Output:
Formula in Report!$A$1 : =Data[Column1]
Value in Report!$A$1 : OLD1
Calculation of formula =Data[Column1] in Report!$A$1 : Column1

Formula in Report!$A$1 : =Data[Column1]
Value in Report!$A$1 : Column1
Calculation of formula =Data[Column1] in Report!$A$1 : Column1


3

Hi,

Thanks for your posting and using Aspose.Cells.

After further investigation, we have found this issue. I have attached the output pdf and output xlsx file for a reference.

We have logged this issue in our database. We will look into this issue and fix it. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-41451.

4

Hi,


This is indeed wrong.

I would expect the following console output if this was being calculated correctly :-

Console Output:
Formula in Report!$A$1 : =Data[Column1]
Value in Report!$A$1 : OLD1
Calculation of formula =Data[Column1] in Report!$A$1 : OLD1

Formula in Report!$A$1 : =Data[Column1]
Value in Report!$A$1 : New1
Calculation of formula =Data[Column1] in Report!$A$1 : New1

That being said, this assumes that I’m actually pulling data from the data table the correct way. But I would expect these formulas to work the same way they do in excel.

/James
5

Hi,

Thanks for providing us your expected output.

We have logged your comments inside the database against the issue. These will be helpful in diagnosing this issue.

Once, we will have some update or fix for you, we will let you know asap.

6

Hi,


Please try the attached fix/version: Aspose.Cells for .NET v7.4.1.2, we have fixed your issue now.

Thank you.

7

Hi Amjad,


That patch fixed one issue, however another still exists with formulas referencing data tables.

In this case, the following formula fails :-

=IF(Table1[Column1] & “” = “”, “Not Answered”, Table1[Column1])

The attached test case will demonstrate the issue, and generate a PDF and XLSX document.

The PDF will show #VALUE!, whereas the XLSX file will show the correct result (As excel will re-calculate when the file is opened)


8
Hi,

Thanks for the new project with template file.

After an initial test, using your newly attached project with your template file, I can notice the issue as you pointed out. The output XLSX file is fine but PDF still shows "#VALUE!" error for the value with new fix.

I have reopened your issue "CELLSNET-41451" again. We will look into it soon. Once we have any update on it, we will let you know here.

Thank you.
9

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We have fixed the issue.

Please download and try this fix: Aspose.Cells for .NET v7.4.1.4 and let us know your feedback.

10

Hi Shakeel,


The fix has indeed worked, and now Aspose.Cells produces the same results for the affected formulas that Excel does.

We still have some formatting issues with text/images, but I’ve opened a separate thread for that.

Thanks for the update!
11

Hi,


Good to know that your issue is resolved by the new fix v7.4.1.4. We have closed this ticket.

And for your other issue(s), please follow up your other thread(s), e.g https://forum.aspose.com/t/97806

Thank you.

12

The issues you have found earlier (filed as CELLSNET-41451) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.