Problem with grouping in pivot table

Aspose.Cells Product Family
, ,
1

Hi guys,

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);
2

@mztow,

Thanks for your query.

Please share your sample files before and after grouping data created by MS Excel. Also send us your complete runnable code snippet for our testing. We will reproduce the problem and provide our feedback after analysis.

3

@ahsaniqbalsidiqui

Thanks for reply

I’m reading data from elasticsearch and files contain client data, so I’m not able to provide it quickly, I would need to build something similar.

Anyway, can you confirm, that

  • for setAutoGroupField method there is no option to set grouping type
  • it should be possible to group by only year with setManualGroupField

Thanks

4

@mztow,

We have logged the issue CELLSJAVA-42708 in our database for detailed investigation. Once, we will have some detailed findings, we will update you in this topic.

6

@mztow,

Please note:

  1. setManualGroupField method has a parameter named “groupByList”, so, you can add a value “PivotGroupByType.Years” to implement group by only year.

  2. You cannot call the method “setAutoGroupField” and “setManualGroupField” in the same PivotField.