Note that I have tested this problem in Aspose.Cells for .NET 17.11, 18.2, and 18.2.8.
- Create new XLSX file in Excel
- Add the following data in the first worksheet:
- Select those 8 cells and create a Named Range called “Data”
- Insert a new pivot table using “Data” as the range.
- 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.
- Insert a column between Des and Date, which will effectively introduce a new field to the named range.
- Add a new caption called “Count”, along with the number 1 in each of the three rows under it.
- Right-click the date field in the pivot table select “Group…”
- 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)_
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.