I’m using Aspose Cells for Java to build a worksheet with pivot table. The problem I have is with grouping data by year in that table. It’s pretty easy task to do when using excel, I can just select date column, hit group, then select grouping by year and then I can even put it into filters and display only the years I want.
I’m not able to achieve the same with Aspose.
I’ve tried to use autogroup by setAutoGroupField method. It works and groups data, but does it by month, not by year. I don’t know if I can set grouping type for autogroup, I haven’t found that option.
When I use manual filter though, then I have completely different problems.
If I try to group only by year, then file builds without any exception on Aspose/Java side, but Excel is not able to open the file correctly, instead it displays error and pivot table is broken.
If I try to group by more than one type, e.g. by year and by quarter, then it works. But I don’t want quarter grouping, just years.
Does anyone have any idea what I’m doing wrong? I’m putting code snippet below
//I need to add group field as row or it will throw an exception when grouping saying that index is invalid pivotTable.addFieldToArea(PivotFieldType.ROW, 2); //this line groups by month pivotTable.setAutoGroupField(2); //code below produces broken file until there is more group types, then it works, but it's not what I need ArrayList groupTypes = new ArrayList(); groupTypes.add(PivotGroupByType.YEARS); var startDate = new DateTime(2000, 1, 1); var endDate = new DateTime(2017, 12, 31); pivotTable.setManualGroupField(2, startDate, endDate, groupTypes, 1);