GetPivotData function computes erronated value

Aspose.Cells Product Family
1

Hello,

We’ve encountered an issue with GETPIVOTDATA function during workbook calculation.

In the attached “TestFile.xlsx”, the G2 cell displays the value 167035.4 when calculating in Excel.

After performing Workbook.CalculateFormula(true) with Aspose, the value shows 22.

We are using Aspose.Cells version 7.7.1.0.

Any feedback will be highly appreciated.

Thank you in advance,
Mihai Andrei
Senior Software Engineer
IBM Romania

2

Hi Mihai,

Thank you for reporting this problem to us.

We are able to observe the problem of incorrect value calculated while using the latest version of Aspose.Cells for .NET 7.7.2. A ticket (CELLSNET-42386) has been logged in our bug tracking system to look further into this matter. Please spare us little time to properly analyze the problem cause, and to provide a fix at earliest. In the meanwhile, we will keep you posted with updates in this regard.

Please accept our apologies for your inconvenience.

3

Any updates on this case? This is a critical issue for one of our customers.

4

Hi,

Thanks for your posting and using Aspose.Cells.

We are afraid, there is no update for you at this moment. However, we have logged your comments in our database. Please spare us some time. Once, there is some fix or update for you, we will let you know asap.

5

Hi,

Thanks for using Aspose.Cells.

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

6

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

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

Hello,

The initial issue seems to be solved, however changing a little bit the test file by placing the GETPIVOTDATA function on a separate sheet on Sheet1!A1, the problem reappears.

I’ve attached a new console application describing the issue, using the modified test file. After calculating the workbook with Aspose, the Sheet1!A1 GETPIVOTDATA function evaluates to no value…

We are using Aspose.Cells version 8.0.2.0.

Any feedback will be highly appreciated.

Thank you in advance,
Mihai Andrei
Senior Software Engineer
IBM Romania

8

Hi Mihai,

Thanks for your posting and using Aspose.Cells.

We found error in your code. Which is highlighted in red. You are reading the value from cell G2. It should be A1 and after setting it to A1, the value is read correctly and no issue is found.

private static void TestFile()
{
Workbook workbook = new Workbook(".\TestFiles\TestFile.xlsx");

var worksheet = workbook.Worksheets[“Sheet1”];

string a1Value = worksheet.Cells[“A1”].StringValue;
Console.WriteLine(“A1 Cell Value before CalculateFormula: ‘{0}’”, a1Value);

workbook.CalculateFormula(true);

a1Value = worksheet.Cells[“G2”].StringValue;
Console.WriteLine(“A1 Cell Value after CalculateFormula: ‘{0}’”, a1Value);
}

9

Hello,

Indeed, there was a mistake in my previous code.

I’ve created another test input document, much closer to the one which our client is experiencing issues with. After applying CalculateFormula on it, the A1 cell containing the GETPIVOTDATA formula shows #REF!.

Any feedback will be highly appreciated.

Thank you,
Mihai Andrei

10

Hi,


Thanks for the sample file.

After an initial test, I reproduced the issue using your newly attached file with the following sample code:
e.g
Sample code:

Workbook workbook = new Workbook(“e:\test2\testfile.xlsx”);

var worksheet = workbook.Worksheets[“Sheet1”];

string a1Value = worksheet.Cells[“A1”].StringValue;
Console.WriteLine(“A1 Cell Value before CalculateFormula: ‘{0}’”, a1Value);

workbook.CalculateFormula();

a1Value = worksheet.Cells[“A1”].StringValue;
Console.WriteLine(“A1 Cell Value after CalculateFormula: ‘{0}’”, a1Value);

I got the following console output:
A1 Cell Value before CalculateFormula: '-77504319.8’
A1 Cell Value after CalculateFormula: '#REF!'

I have reopened your issue now. We will look into it to figure it out soon.

Thank you.
11

Hi,

Thanks for your using Aspose.Cells.

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

12

Hi,

I tried Aspose.Cells for .Net v8.1.2.0 and the issue isn’t fixed. I am looking for your feedback.

Thank you,
Roxana Ionicioiu
IBM Romania

13

Hi,

I have tested your scenario/ case using the code segment (shared by one of my previous post in this thread) with your template file, it works fine. I am using latest version/fix: Aspose.Cells for .NET (Latest Version) .

If you still find the issue with latest version/fix, kindly provide your template file and paste your sample code here to reproduce the issue on our end, we will check it soon.

Thank you.

14

Hi,

I tested using Aspose.Cells 8.2.1.0 and the attached file and the issue is still present.
The values in MS Excel are: 1,6,8.
The values in Aspose.Cells are: 15,6,8.

I used the following code:

var workbook = new Workbook(“TestFiles\GetPivotData.xlsx”);

Console.WriteLine(“Before CalculateFormula:”);
Console.WriteLine("D21 = " + workbook.Worksheets[1].Cells[“D21”].StringValue);
Console.WriteLine("D22 = " + workbook.Worksheets[1].Cells[“D22”].StringValue);
Console.WriteLine("D23 = " + workbook.Worksheets[1].Cells[“D23”].StringValue);

workbook.CalculateFormula();

Console.WriteLine(“After CalculateFormula:”);
Console.WriteLine("D21 = " + workbook.Worksheets[1].Cells[“D21”].StringValue);
Console.WriteLine("D22 = " + workbook.Worksheets[1].Cells[“D22”].StringValue);
Console.WriteLine("D23 = " + workbook.Worksheets[1].Cells[“D23”].StringValue);

Console.ReadKey();

Regards,
Aurelian Iordache
IBM Romania

15

Hi Aurelian,


Thank you for providing the test spreadsheet.

We have evaluated your presented scenario while using the latest revision of Aspose.Cells for .NET 8.2.1.3, and we are able to observe the difference in calculated value for D21; sheet4. Please note, Aspose.Cells API returns this value as 15, whereas MS Excel calculates it 1. We have logged the problem in our bug tracking system under the ticket CELLSNET-43061 for further investigation. Please spare us little time to properly analyze the case, and to provide a fix. In the meanwhile, we will keep you posted with updates in this regard.
16

Hi Aurelian,


Thanks for using Aspose.Cells.

We have fixed this issue now. We will provide you a fix in couple of
days after incorporating other enhancements and fixes and conducting
some extensive testing. Once, it is available for you, we will let you
know asap by posting in this thread.
17

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

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