PivotGrid refresh generates corrupt Excel content

Aspose.Cells Product Family
1

Hello,

We’ve encountered an issue related to Excel pivot tables refresh.

We have a test file “TestPivotTables.xlsx” with one pivot grid built on top of “element1” and “element2” fields, from the Sheet1!A1:C4 range.

In our code we have to modify the pivot grid’s base range by adding new values, possibly removing the “element1” and “element2” fields and adding new ones. E.g. our test code changes the fields to “element3” and “element4” values. After doing so and refreshing the pivot tables by using PivotTable.RefreshData() and pivotTable.CalculateData(), the document seems to be corrupted and cannot be opened in Excel anymore, also recovery does not work.

We’ve checked by using Excel and in this situation (when changing the names of the elements) the pivot grid becomes empty and should be re-created by the user.

We are using Aspose.Cells version 7.7.1.0 , .NET Framework 4.0.

I’ve attached a zip file containing a test console app executing what described above. The zip contains also the “TestPivotTables.xlsx” test file being used. The console app generates the ModifiedPivotSource.xlsx file, which seems to be corrupted and cannot be loaded by Excel.

Any feedback will be highly appreciated.

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

2

Hi Mihai,

Thanks for your posting and using Aspose.Cells.

We were able to replicate this issue using your sample project and source file. We have tested it with the latest version: Aspose.Cells
for .NET v7.7.1.4 but the issue still occurs. The output xlsx file is corrupt.

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-42351.

I have also attached the screenshot of the error displayed by MS-Excel when the output file is opened for a reference.

Screenshot:

3

Hello,

We’ve tested against Aspose.Cells 8.1.0.0 and the problem is reproducible.

Do you have an estimate, when this issue will be handled ?

Thank you,
Mihai Andrei

4

Hi Mihai,


We are sorry for the inconvenience.

The ticket attached to this thread hasn’t been marked resolved yet, nor we have received any updates in this regard so believe the issue persists in the recent releases of Aspose.Cells for .NET API. We have recorded a note for the concerned development team member to share the insight of this issue, and possibly an estimated timeline for the fix. As soon as some news comes in, we will post here for your kind reference.
5

Hi Mihai,


Thank you for your patience with us.

We have evaluated your presented scenario on our end, and have concluded that the behavior exhibited in attached console application is correct. This is a standard of MS Excel that when a PivotField name is changed in the PivotTable data source, MS Excel removes it on refresh. Therefore when you call PivotTable.CalculateData on a PivotTable with incomplete data source, the spreadsheet becomes corrupted/damaged.

In order to avoid this situation, you have to add the new/modified PivotField to some row, column, page or data area before calling the PivotTable.CalculateData method. Please check the following code snippet for demonstration purposes. Please note, the code will replace the statements in RefreshPivotTables method of your provided sample application.

C#

pivotTable.RefreshData();
pivotTable.AddFieldToArea(PivotFieldType.Data, 0);
pivotTable.AddFieldToArea(PivotFieldType.Data, 1);
if (pivotTable.DataField != null)

{
pivotTable.AddFieldToArea(PivotFieldType.Column, pivotTable.DataField);
}
pivotTable.CalculateData();


If you wish to confirm this behavior with MS Excel application, you can do that with the following two steps,

  1. Change the PivotField name of PivotTable source,
  2. Refresh PivotTable

You will notice that the PivotFields are cleaned up.
6

Hello,

I’ve opened the input test file in Excel and manually performed the updates as the console application does. Indeed, when refreshing the pivot table from within Excel, it will be cleared up and this is expected behavior.

Thanks for the C# code, it works fine, but our intention is not to automatically re-construct the Pivot Table, we will be happy just to produce a file with an empty Pivot Table, which can be opened by MS Excel. In our business use cases this is rather an unusual situation, but it does happen from once in a while, but the user can manually reconstruct the Pivot Table, if the file is not corrupt.

The problem is that Aspose does not clears up the Pivot Table as Excel does, but instead produces a corrupt Excel file which cannot be opened anymore by MS Excel.

Thank you,
Mihai Andrei

7

Hi Mihai,

Thanks for your posting and using Aspose.Cells.

Kindly explain your scenario with sample code and source and output files, so that we could investigate this issue at our end and add in our database for correction.