Formulas with GETPIVOTDATA are not being recalculated even though the pivot itself is

Aspose.Cells Product Family
1

Hi,

We use Aspose like a mail-merge engine:

1. Users provide an Excel XLSX file that serves as a template

2. Users provide data in the form of CSV files
3. Our application populates certain sheets of the XLSX template with the data

4. Our application re-calculates the resulting XLSX

5. Our application stores the resulting XLS, and allows users to download it

See attached files:
* the template = OS Report Clean.xlsx
* the data = PRIMEPOS COMBINED.csv

For the merge application, see post 708524

For the cell with the error, see sheet “Output”, cells F9 and F10.

Once the data is loaded on sheet “PRIMEPOS COMBINED”, our application recalculates the pivot and the spreadsheet.
The pivot table on sheet “OS_OutPut_Pivot_Current” is correctly recalculated, but the formulas on sheet “Output”, cells F9 and F10 are not. They keep their old values.


2

Hi,


Thanks for the sample files.

Please try our latest version/fix, i.e., Aspose.Cells for .NET v16.11.0 if it makes any difference:

We also appreciate if you could create a simple console application (runnable) with v16.11.0 to demonstrate your exact steps, zip the project (with all the resource files) and post us here to reproduce the issue on our end. We will check it and may log a ticket for the issue (if found).

PS. Please also attach some screenshots to compare older and newer/updated values for those cells to highlight the issue precisely (if possible).

Thank you.

3

I received a forwarded email with your reply 946907 but that reply seems not to be available / visible, hence I reproduce it here:

View the complete topic at: Thanks for your posting and using Aspose.Cells.

We have looked into this issue and found that the latest version: Aspose.Cells for .NET 16.11.0 is working fine.

Please check the sample.xlsx file, if you open it and check the cells F9, you will see #REF! and then execute the following code after commenting the two mentioned lines and then without commenting those lines. And the check the output pdfs i.e. output-normal.pdf and output-aftercommenting.pdf.

When you will open the normal pdf, you will see GETPIVOTDATA() has been calculated successfully.

C#

Workbook workbook = new Workbook("sample.xlsx");

PivotTable pt = workbook.Worksheets["OS_OutPut_Pivot_Current"].PivotTables[0];

//Commenting these two lines will not calculate GETPIVOTDATA() formula

pt.RefreshData();

pt.CalculateData();

workbook.Worksheets["PRIMEPOS COMBINED"].IsVisible = false;

workbook.CalculateFormula();

workbook.Save("output-normal.pdf");

4

Shakeel,

thank you for your suggestion: By first recalculating the pivots and then recalculating the spreadsheet the correct numbers were shown (and also upgrading to 16.11.0)

regards
Jacques

5

Hi Jacques,


Good to know that your issue is sorted out now by first re-calculating/refreshing the PivotTable and then calculating the workbook as suggested by Shakeel Faiz. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.
6

Hi,

Even though re-calculating a few GETPIVOTDATA cells does work now, the re-calculation does not work for the attached spreadsheet.

When using Calculationchain OFF, the formulas return "#REF!"

When using Calculationchain ON, the formulas are not recalculated, the answer is the same as before recalculating.

Excel recalculates these cells without issue.

Attached is:
* Executable = our code to recalculate
* Standalone generator code = source code of above
* Input.xlsx = file to use with executable; cells do not get recalculated or return #REF!
* ExampleOutput.xlsx = output after recalculation, with #REF!

We’re using 16.11.0.0

thank you
Jacques

7

Hi,


Thanks for the sample project with template files.

I could not find “#REF!” errors in your provided file “ExampleOutput.xlsx” when opening into MS Excel (even after manually calculating or recalculating worksheet(s)). I did try your scenario/ case using your sample project a bit. But I could not evaluate your issue properly. I tried to run your Standalone generator code project and provide LegendEntity.xlsx file using the interface for the application, it blocked me to provide “Exposure Consolidated” to Drop feeds field. Could you give us steps details on how to reproduce the issue on our end. Also give us some screenshots to highlight the problematic areas, so we could evaluate your issue precisely and may log it into our database to consequently figure it out soon.

PS. we also recommend you to kindly try our latest version/fix: Aspose.Cells for .NET if it makes any difference:

Thank you.
8

Hi,

The file called
“ExampleOutput.xlsx” has #REF! errors when I open it. First set recalculation to Manual before opening it, and do NOT recalculate it, because Excel is able to calculate these formulas. See screenshot ExampleOutput.png

When running the stand-alone generator, use the file “input.xlsx”.

* screenshot of XLSX before running through generator = “input_before.png”
* screenshot of running the generator = “aspose_generator.png”
* screenshot of XLSX after running through generator = "input_after.png"

hope this helps,

thank you
Jacques

9

Hi,


Thanks for the screenshots with details.

I am able to observe the issue as you mentioned by using your sample project with your template file. I found that the formulas with GETPIVOTDATA are not being recalculated properly in the output file as per the screenshot(s) you attached. The formulas do return “#REF!” error instead of valid value for the underlying cells in the “Output” worksheet. I have logged a ticket with an id “CELLSNET-45009” for your issue. We will look into it soon.

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

Thank you.
10
Hi,

Thanks for using Aspose.Cells.

Please download and try the following latest fix

Aspose.Cells for .NET v16.12.5 (.NET 2.0) compiled in .NET Framework 2.0.
Aspose.Cells for .NET v16.12.5 (.NET 4.0) compiled in .NET Framework 4.0.

and let us know your feedback.
11

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

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

Thank you - we will test this.