Java Pivot Table Formatting

Aspose.Cells Product Family
1

I am trying to make a pivot table in a blank sheet that pulls its data from another sheet. I am new to Aspose.cells and pivot tables in Excel. I have looked at the documentation to figure out how to make a pivot table, but I do not know how to make it look like I want it to using Aspose.cells. I’ve attached both the pivot table and the table that is generated when the fields are summarized as a sum. Any help would be greatly appreciated. expandedTable.PNG (4.9 KB)
PivotTable.PNG (9.5 KB)

2

@DanielWeber,
Thank you for your query. You may give a try to the following sample code and share the feedback.

// Instantiating a Workbook object
Workbook workbook = new Workbook("Book2.xlsx");

// Obtaining the reference of the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);

Cells cells = sheet.getCells();

PivotTableCollection pivotTables = sheet.getPivotTables();

// Adding a PivotTable to the worksheet
int index = pivotTables.add("=A1:C51", "E3", "PivotTable2");

// Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables.get(index);

// Showing grand totals for columns.
pivotTable.setColumnGrand(true);

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

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

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

pivotTable.addFieldToArea(PivotFieldType.COLUMN, pivotTable.getDataField());
// Saving the Excel file
workbook.save("Java_pivotTable_test_out.xls");

Sample template file and output file is attached here for your reference.
Book2.zip (7.1 KB)
Java_pivotTable_test_out.zip (5.4 KB)

3

Thank you, that was very helpful and created the table that I wanted. However, I need to pull the data from one worksheet and put the pivot table into a new worksheet. How can I do that?
I also need to get the data from non-consecutive columns. For example in Book2 that I’ve attached, I need to make the pivot table from columns A, C, and D. When I try to select this range using Excel’s syntax (A1:A51,C1:D51), I get an IndexOutOfBounds exception. Do you have any advice for these two issues? Thanks.Book2.zip (9.2 KB)

4

@DanielWeber,
Thank you for the feedback. I have also tried this scenario using Excel but it raises exception Data Source is invalid. As this is not possible using Excel, therefore no option is available to achieve it using Aspose.Cells which mimics the behaviour of Excel and does not implement any feature which is not valid in Excel.

You may please try to achieve your requirement in Excel, and if get success, please share the steps with us along with the expected output Excel file containing the desired pivot table. We will try to provide the same feature using Aspose.Cells.

1 Like