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

Free Support Forum - aspose.com

RefreshData on pivot table corrupts workbook

When I execute the following code on the attached workbook:

Workbook oWorkbook = new Workbook(@"c:\temp\book5.xlsx");
oWorkbook.Worksheets["Sheet1"].PivotTables["PivotTable1"].RefreshData();
oWorkbook.Save(@"c:\temp\book6.xlsx");

I receive the following error when opening it in excel:

"We found a problem with some content in 'book6.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes."

And upon clicking yes, I receive the message "Removed feature: PivotTable report from /xl/pivotTables/pivotTable2.xml part (PivotTable view)"

I using aspose 8.7.

Hi Sarah,


Thank you for contacting Aspose support.

We have evaluated the presented scenario while using the latest revision of Aspose.Cells for .NET 8.7.0.1, and we are able to replicate the said issue. In order to further investigate the matter, we have logged this incident in our bug tracking system as CELLSNET-44206. Please spare us little time to properly analyze the case, and get back with updates in this regard. In the meanwhile, we will keep you posted with updates in this regard.

Any updates on this?


If you can’t provide a fix in the near future, do you have any more information about what is triggering the bug (in case I might be able to work around it by changing something in the workbook)

Thanks

Hi,


I am afraid, your issue is not resolved yet. I have asked the relevant developer from product team to share an update, workaround (to cope with your issue for the time being) or ETA for your issue.

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

Thank you.

This appears to be something to do with the pivot cache.


Setting “Number of items to retain per field” to None, and refreshing the pivot tables appears to resolve the issue.

However, I do need to have this set to automatic and further this isn’t even fixing the problem in my real world spreadsheet so I would appreciate it if you could continue to investigate.

Hi Sarah,


Thank you for sharing your valuable feedback. We have logged your comments to the aforementioned ticket and have requested the concerned member of the product team to consider this information during the investigation. As soon as we get any news in this regard, we will share here for your kind reference.

Hi again,


This is to update you that we have resolved the problem logged earlier as CELLSNET-44206. We will shortly share the fix here for your testing.
Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v16.11.2 for your issue CELLSNET-44206 and let us know your feedback.

All the PivotTables which use the same data source need to be refreshed and calculated at the same time. Please see the following sample code for your reference.

C#
Workbook oWorkbook = new Workbook(filePath + @"book5.xlsx");

PivotTable pt1 = oWorkbook.Worksheets[“Sheet1”].PivotTables[“PivotTable1”];
pt1.RefreshDataFlag = true;
pt1.RefreshData();
pt1.CalculateData();
pt1.RefreshDataFlag = false;

PivotTable pt2 = oWorkbook.Worksheets[“Sheet2”].PivotTables[“PivotTable3”];
pt2.RefreshDataFlag = true;
pt2.RefreshData();
pt2.CalculateData();
pt2.RefreshDataFlag = false;

oWorkbook.Save(filePath + “out.xlsx”);

seems to be fixed now, thanks

Hi,

Thanks for your feedback and using Aspose.Cells.

It is good to know that your issue is resolved with the latest fix. Let us know if you encounter any other issue, we will be glad to look into it and help you further.

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


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

Hi,

I am still running into the same error and I am using aspose - version 19.4.

Below is my sample code:

Worksheet ws = wb.Worksheets[0];
PivotTable pvt= ws.PivotTables[0];
pvt.RefreshDataFlag = true;
pvt.RefreshData();
pvt.CalculateData();
pvt.RefreshDataFlag = false;
string fileName = Path.GetFileNameWithoutExtension(importFileName);
string exportFileName = exportLocation + @"" + fileName + “-out.xlsx”;
wb.Save(exportFileName);

Is there anyone who can help out with this?

regards,

@nitishsujeebun,

Thanks for the sample code segment.

Please provide your template file or complete runnable sample code (if you are not using any template file and you use code to input data into worksheet and create PivotTable based on it dynamically), we will check it soon.

PS. Please try using our latest version/fix: Aspose.Cells for .NET v19.4.x (if you are not already using it).