Create Pivot Tables in Excel Java

create-chart-from-pivot-table.java

 // Instantiate an Workbook object
 Workbook workbook = new Workbook("pivotTable.xls");
 
 // Add a new sheet
 int sheetIndex = workbook.getWorksheets().add(SheetType.CHART);
 Worksheet sheet3 = workbook.getWorksheets().get(sheetIndex);
 
 // Name the sheet
 sheet3.setName("PivotChart");
 
 // Add a column chart
 int chartIndex = sheet3.getCharts().add(ChartType.COLUMN, 0, 5, 28, 16);
 Chart chart = sheet3.getCharts().get(chartIndex);
 
 // Set the pivot chart data source
 chart.setPivotSource("PivotTable!PivotTable1");
 chart.setHidePivotFieldButtons(false);
 
 // Save the Excel file
 workbook.save("pivotChart_test.xls");

create-pivot-table.java

// Instantiate an Workbook object
Workbook workbook = new Workbook("worksheet.xlsx");

// Access the sheet
Worksheet sheet2 = workbook.getWorksheets().get(1);

// Get the pivottables collection in the sheet
PivotTableCollection pivotTables = sheet2.getPivotTables();

// Add a PivotTable to the worksheet
int index = pivotTables.add("=Data!A1:F30", "B3", "PivotTable1");

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

// Show the grand totals
pivotTable.setRowGrand(true);
pivotTable.setColumnGrand(true);

// Set the PivotTable report is automatically formatted
pivotTable.setAutoFormat(true);

// Set the PivotTable autoformat type.
pivotTable.setAutoFormatType(PivotTableAutoFormatType.REPORT_6);

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

// Drag the third field to the row area.
pivotTable.addFieldToArea(PivotFieldType.ROW, 2);

// Drag the second field to the row area.
pivotTable.addFieldToArea(PivotFieldType.ROW, 1);

// Drag the fourth field to the column area.
pivotTable.addFieldToArea(PivotFieldType.COLUMN, 3);

// Drag the fifth field to the data area.
pivotTable.addFieldToArea(PivotFieldType.DATA, 5);

// Set the number format of the first data field
pivotTable.getDataFields().get(0).setNumber(7);

// Save the Excel file
workbook.save("pivotTable.xls");