Generation of Pivot table using Aspose Cells for Java

Aspose.Cells Product Family
1

Hi,

I am trying to generate the pivot table as in the attached Excel using Aspose. I tried the following code but it generates the table in the default manner. Please let me know how to generate the Pivot in Aspose Cells (columns are shown side by side instead of row wise manner).

Workbook workbook = new Workbook();
workbook.open(“c:\temp\pivot_practice.xls”);
Worksheet sheet = workbook.getWorksheets().getActiveSheet();

PivotTables pivotTables = sheet.getPivotTables();

//Adding a PivotTable to the worksheet

int index = pivotTables.add("=A1:L27", “D36”, “PivotTable2”);

//Accessing the instance of the newly added PivotTable

PivotTable pivotTable = pivotTables.get(index);

//Unshowing grand totals for rows.

pivotTable.setRowGrand(false);

//Dragging the first field to the row area.
pivotTable.addFieldToArea(PivotFieldType.ROW, 4);

//Draging the second field to the column area.
///pivotTable.addFieldToArea(PivotFieldType.COLUMN, 1);

//Draging the third field to the data area.
pivotTable.addFieldToArea(PivotFieldType.DATA, 6);
pivotTable.addFieldToArea(PivotFieldType.DATA, 8);

//Saving the Excel file

workbook.save(“C:\temp\pivotmodified.xls”, FileFormatType.DEFAULT);

Thanks,
Shashi

2

Hi Shashi,

Thanks for providing us the template excel file containing your desired pivot table.

May the following code help you for your need, kindly consult it:

Workbook workbook = new Workbook();
workbook.open("c:\\temp\\pivot_practice.xls");
Worksheet sheet = workbook.getWorksheets().getActiveSheet();

PivotTables pivotTables = sheet.getPivotTables();

//Adding a PivotTable to the worksheet

int index = pivotTables.add("=A1:L27", "D36", "PivotTable2");

//Accessing the instance of the newly added PivotTable

PivotTable pivotTable = pivotTables.get(index);

//Unshowing grand totals for rows.

pivotTable.setRowGrand(false);

//Dragging a field to the row area.
pivotTable.addFieldToArea(PivotFieldType.ROW, 4);

//Draging the fourth and second source fields to the page area.
pivotTable.addFieldToArea(PivotFieldType.PAGE, 3);
pivotTable.addFieldToArea(PivotFieldType.PAGE, 1);

//Draging some fields to the data area.
pivotTable.addFieldToArea(PivotFieldType.DATA, 8);
pivotTable.addFieldToArea(PivotFieldType.DATA, 6);
pivotTable.addFieldToArea(PivotFieldType.DATA, 11);

pivotTable.addFieldToArea(PivotFieldType.COLUMN, pivotTable.getDataField());

//Saving the Excel file

workbook.save("C:\\temp\\pivotmodified.xls", FileFormatType.DEFAULT);

Thank you.

3

Hi Amjad,

Thanks for the solution. It solves my problem. I have one more item related to Pivot table. When I try to update the datasource used to generate the pivot table through Aspose Cells, it does not refresh the pivot area with the updated data. Is there any way to do this? Please note that I want to do it through aspose cells only and not through Excel.

Thanks,
Shashi

4

Hi Shashi,

Well, Currently, Aspose.Cells for Java does not support to manipulate pivot tables rather it only keeps them and preserve them with the file. Actually it forces MS Excel to create / refresh the pivot table report on opening the output file into it. We will look into the feature after completing some other important tasks on hand.

Thank you.

5

Amjad,

Do you have any timelines in mind?

Thanks,
Shashi

6

Hi Shashi,

I am afraid we can't give a definitive timeline for this feature currently, thank you for your patience.