Grouping in PivotTable corrupts file

Note that I have tested this problem in Aspose.Cells for .NET 17.11, 18.2, and 18.2.8.

Replication steps:

  1. Create new XLSX file in Excel
  2. Add the following data in the first worksheet:

Des Date
A 01-Jan-18
B 02-Jan-18
C 03-Jan-18

  1. Select those 8 cells and create a Named Range called “Data”
  2. Insert a new pivot table using “Data” as the range.
  3. In the PivotTable designer, select both fields so that they are checked.
    NOTE: The included file “sample1.xlsx” shows the file up to this point.
  4. Insert a column between Des and Date, which will effectively introduce a new field to the named range.
  5. Add a new caption called “Count”, along with the number 1 in each of the three rows under it.
  6. Right-click the date field in the pivot table select “Group…”
  7. Select Months and Years
    Note: The included file “sample2.xlsx” shows the file up to this point.

Running the below code works fine with sample1.xlsx, but generates a corrupted file when run against sample2.xlsx.

_ Dim workbook As New Aspose.Cells.Workbook(“c:\temp\sample1.xlsx”) _
_ Dim sheet As Aspose.Cells.Worksheet = workbook.Worksheets(0)_
_ Dim pivot As Aspose.Cells.Pivot.PivotTable = sheet.PivotTables(0)_

_ pivot.RefreshData()_

_ workbook.Save(“c:\temp\result.xlsx”)_

sample files.zip (17.0 KB)

A couple of points to clarify about this.

  • It is the grouping that is causing the issue. If all the steps are the same except the date field is not grouped, then it will work.
  • Perhaps this will give a hint as to what is going on. If you open sample2.xlsx, right-click the pivot table and select to refresh, and then save the file and run the code, the results generated will not be corrupted.

Thanks for your time.

@chrism.peloton,

Thanks for the template files and details.

Could you provide us your output files by using both your input files.

Moreover, please note, Aspose.Cells does support to refresh or calculate PivotTables whose source is external files or have external references. Since your underlying PivotTable has external reference to (“zzz.xlsx!Data”) as its data source, so Aspose.Cells might not be able to refresh or calculate PivotTable’s data fine.

I’ve attached recreated examples and the results for each.

updated files.zip (34.8 KB)

The reason for the “zzz” part had to do with my original test file that I then renamed. The new files I have included were created from scratch using the steps I included in the original message.

@chrism.peloton

Thanks for using Aspose APIs.

We were able to observe this issue and logged it in our database for investigation and for a fix. Once, the issue is resolved or we have some other news for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-45988 - Refreshing Pivot Table in Sample2.xlsx generates corrupt Excel file

C#

Workbook wb = new Workbook("sample2.xlsx");

Worksheet ws = wb.Worksheets[0];
PivotTable pt = ws.PivotTables[0];

pt.RefreshData();
pt.CalculateData();

wb.Save("out.xlsx");

@chrism.peloton

Thanks for using Aspose APIs.

Please download and try the following fix for your issue CELLSNET-45988 and let us know your feedback.

This does appear to resolve the issue. Thank you very much!

@chrism.peloton

Thanks for your feedback and using Aspose.Cells.

It is good to know that your issue is resolved with the provided 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-45988) have been fixed in Aspose.Cells for .NET 18.4. Please also check the document/article for your reference: Install Aspose Cells through NuGet|Documentation