We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Data sorting in Pivot tables gives exception in Java

hi - i’m trying to sort a pivot table with the following code but i’m getting a null pointer…

// Create a pivot

PivotTables pivotTables = analysis.getPivotTables();

int pti = pivotTables.add(workbook.getWorksheets().getRangeByName(“RawDataRange”).getRefersTo(), 3, 1,

“RevenueAnalysis”);



PivotTable pt = analysis.getPivotTables().get(pti);

pt.setManualUpdate(false);

pt.setPreserveFormatting(true);

int clientFieldIndex = pt.addFieldToArea(PivotFieldType.ROW, 0);

PivotField clientField = pt.getRowFields().get(clientFieldIndex);

clientField.setAutoSort(true);

clientField.setAutoSortField(0); // NULL POINTER THROWN HERE

clientField.setAscendSort(false);



int revenueFieldIndex = pt.addFieldToArea(PivotFieldType.DATA, 18);

PivotField revenueField = pt.getDataFields().get(revenueFieldIndex);

revenueField.setNumber(2);



pt.setAutoFormat(true);

pt.setAutoFormatType(PivotTableAutoFormatType.CLASSIC);

pt.setRowGrand(true);

pt.setColumnGrand(true);

pt.setPageFieldOrder(OrderType.DOWN_THEN_OVER);



//if i change the duff line to this:



clientField.setAutoSortField(-1);


it works, but it sorts by the row field which is not what i want. i want to sort by the revenue field.



I’m using the very latest java version of aspose cells.

Hi,

Thank you for considering Aspose.

The NullPointer Exception is coming because you are trying to apply the sorting on the pivot field before adding it to the pivot table.

Please change you code as under to get your desired results,

// Create a pivot

PivotTables pivotTables = analysis.getPivotTables();

int pti = pivotTables.add(workbook.getWorksheets().getRangeByName("RawDataRange").getRefersTo(), 3, 1, "RevenueAnalysis");

PivotTable pt = analysis.getPivotTables().get(pti);

pt.setManualUpdate(false);

pt.setPreserveFormatting(true);

int clientFieldIndex = pt.addFieldToArea(PivotFieldType.ROW, 0);

PivotField clientField = pt.getRowFields().get(clientFieldIndex);

int revenueFieldIndex = pt.addFieldToArea(PivotFieldType.DATA, 1);

PivotField revenueField = pt.getDataFields().get(revenueFieldIndex);

revenueField.setNumber(2);

clientField.setAutoSort(true);

clientField.setAutoSortField(0);

clientField.setAscendSort(false);

pt.setAutoFormat(true);

pt.setAutoFormatType(PivotTableAutoFormatType.CLASSIC);

pt.setRowGrand(true);

pt.setColumnGrand(true);

pt.setPageFieldOrder(OrderType.DOWN_THEN_OVER);

Thank You & Best Regards,