Array formula in Excel spreadsheet not calculated by Workbook.CalculateFormula() method

Aspose.Cells Product Family
1

Hi,

We found that when we call the Workbook.CalculatFormla() method on a Workbook before saving it as pdf file, the generated pdf file does not have its Array formulas calculated (only the regular formulas are calculated).

I have attached a small project to demonstrate the issue. I have also attached an input file “input file with formulas.xlsx” to help demonstrate the issue.

The input file contains two tables in two different worksheets; the second worksheet “Locations” contains the table with both an array formula and a regular formula.

The project code aims to open an excel file, calculate its data using Workbook.CalculateFormula() before re-saving the file as a pdf and xlsx file called “generated file”.

A fix for this would be useful, as we would like to be able to produce both types of files from the Workbook.

2

Hi,

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

We were able to observe this issue. The generated pdf’s array formulas are not calculated. We have logged this issue in our database. We will look into it and resolve this issue. 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-41835.

3

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.5.1.1 and let us know your feedback.

4

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

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

Hi,

I am using the latest version of Aspose.Cells, and the array formulas are not being calculated. I do workbook.CalculateFormula(), but the array formulas return #N/A. When I open the workbook in Excel they work fine. Shouldn’t I be able to get the array formulas to calculate in the code with CalculateFormula()?
Thanks
Daniel
6

By the way, my array formulas are in a data table. Shouldn’t this still be recalculated properly by CalculateFormula?

7

Hi,

Thanks for your posting and using Aspose.Cells.

I have tested this issue with the latest version: Aspose.Cells
for .NET v8.4.1.2 using the following code and it give correct results. I have attached the output pdf generate by the code and screenshot showing the MS-Excel and Aspose.Cells results which are same.

C#

string filePath = @“F:\Shak-Data-RW\Downloads\input+file+with+formulas.xlsx”;

Workbook workbook = new Workbook(filePath);

workbook.CalculateFormula();

workbook.Save(filePath + “.out.pdf”);