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

Free Support Forum - aspose.com

Errors Calculating Formulas that look at a pivot table

Aspose.Cells Product Family
#1

Hi,


Id like to confirm a bug or possible user error.
I have a report - and can create any basic report where I provide my data source to the document and simply create a pivot table with it - any fields, doesnt matter - works fine.

I then crate a workbook that has a lookup for a field of the pivot table using the below:

=(INDEX(HeadcountData!A:A,ROW()))

So my pivot table is on worksheet HeadcountData and my table on this new worksheet has this formula on it to pull through the value held on the pivot table for the same row/column.

This works fine in excel, but if I run the document through Aspose and use:

myWorkbook.CalculateFormula();

myWorkbook is simple a Workbook.

I get:

Aspose.Cells.CellsException: Error in calculating cell [Testing!A3]:
NullReferenceException: Object reference not set to an instance of an object. at
Aspose.Cells.Workbook.CalculateFormula(Boolean ignoreError, ICustomFunction
customFunction) at Aspose.Cells.Workbook.CalculateFormula()


Can you confirm if this is a bug or if simply ref a field in a pivot table is something that wont work as the data is being refreshed/submitted/calculated

#2

Hi Aaron,

Thanks for your posting and using Aspose.Cells.

We were able to replicate this exception using the latest version: Aspose.Cells for .NET (Latest Version) . Please let us know how did you create Copy of TEST.xlsx, did you create it using Microsoft Excel or did you create it using Aspose.Cells?

#3

Hi,


The document itself was created using Aspose.
The addition that was done manually was to create both the pivot table and the worksheet that references the values in the pivot table.

Then this document is used like a template to generate the data source data.
#4

Hi Aaron,

Thanks for your feedback and using Aspose.Cells.

We were able to replicate this issue with the following code using the latest version. The calculate formula throws exception.

We have logged this issue in our database for investigation. We will look into it and fix 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-43178 - Error Calculating Formulas that look at a Pivot Table

C#

string filePath = @“F:\Shak-Data-RW\Downloads\Copy+of+TEST.xlsx”;


Workbook workbook = new Workbook(filePath);


//It throws exception

workbook.CalculateFormula();
Exception:
at Aspose.Cells.Workbook.CalculateFormula(Boolean ignoreError, ICustomFunction customFunction)
at Aspose.Cells.Workbook.CalculateFormula()
An unhandled exception of type 'Aspose.Cells.CellsException' occurred in AsposeProject.exe

Additional information: Error in calculating cell [Testing!A3]: NullReferenceException: Object reference not set to an instance of an object.
#5

Hi Aaron,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET (Latest Version) and let us know your feedback.

#6

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

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