Refreshing a pivot table with calculated field corrupts Excel file

Aspose.Cells Product Family
1

If a pivot table has a calculated field and I import new data and refresh the pivot table, the references in the formula for the calculated field is changed and hence becomes invalid. If I remove the calculated field all is good.

Before/after screenshot
2019-03-27_12-13-38.png (201.7 KB)

Template
I can send the template Excel file with the calculated field (can’t upload it here).

Code
Data import:
dataSheet.Cells.ImportData(data, 0, 0, new ImportTableOptions {DateFormat = "dd-MMM-yyyy HH:mm"});

Pivot table refresh:
foreach (var worksheet in workbook.Worksheets)
{
if (!worksheet.PivotTables.Any()) continue;
foreach (var pt in worksheet.PivotTables)
{
pt.RefreshData();
pt.CalculateData();
}
}

I also tried, without luck, to add the calculated field in code:
var pt = workbook.Worksheets[“Reports by manager”].PivotTables[“ptByManager”];
pt?.AddCalculatedField(“Status”, “= SUM(‘Report’) / (SUM(‘Report’) + SUM(‘NoReport’)) * 100”, true);

Two questions:
How can Aspose.Cells handle pivot tables with calculated fields?
What should the formula in the AddCalculatedField look like?

Thanks in advance.
Regards
Morten

2

@mortenma,

Thanks for the screenshot and code segment.

Could you perform the task manually in MS Excel (i.e., insert data manually in the source data range and refresh/calculate Pivot table, etc.) to check if it works or not. If MS Excel gives different results, please provide us a sample console application (runnable), zip the project and post us here to show the issue, we will check it soon. Please also zip your Excel file(s) prior attaching. Moreover, please create dynamic datatable/dataset to be imported into the worksheet cells in code to remove any inter-dependencies of external data sources, so we could execute your sample project seamlessly. This will help us evaluate your issue precisely to consequently figure it out soon.

3

Hi Amjad

thanks for your reply. My mistake. The structure in the new data I imported was not 100% identical to the data on which the pivot table was based. It works now.

/Morten

4

@mortenma,

Good to know that your issue is sorted. 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.