Pivottables in Excel spreadsheets are used to interactively summarize data. Suppose you have data for many invoices in your worksheet. In this case, pivottables can be used to aggregate invoices grouped by customer or product. In this article, you’ll learn how to programmatically handle Pivottables in Excel. In particular, you’ll learn how to create a PivotTable in Java and generate charts based on it.

  • Use Java to create pivottables in Excel
  • Generate charts using pivottables

To use Excel Pivottables, we’ll use Aspose.Cells for Java, a powerful API that lets you generate, modify, and transform Excel files from within your Java application. Click the button below to download the trial. Download the latest version

Use Java to create pivottables in Excel

Here are the steps to create a PivotTable in Excel using Java.

  • Use the workbook class to create a new or load an existing Excel file.
  • Populate the worksheet with data (optional).
  • Collect pivottables into a PivotTableCollection object using the worksheet.getPivottables () method.
  • Use the pivottablecollect.add (string, string, string) method to add a new PivotTable and obtain its reference in the PivotTable object.
  • Set options, such as totals, formatting, and so on.
  • Use the PivotTable. AddFieldToArea (int, int) method to add fields to the area.
  • Save the Workbook using the workbook.save (string) method.

The following code example demonstrates how to use Java to add pivottables to Excel.

// 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");Copy the code

Excel spreadsheet

PivotTable

Generate charts using Excel Pivottables in Java

Here are the steps to generate a chart using Excel Pivottables in Java.

  • Use the workbook class to create a new or load an existing Excel file.
  • Populate the worksheet (optional).
  • Use the workbook.getWorksheets ().add(sheettype.chart) method to add a worksheet of a new CHART type and get its reference in a worksheet object.
  • Use the worksheet.getCharts ().add() method to add a new Chart and get its reference in the Chart object.
  • Use the chart.setPivotSource (string) method to set the PivotTable as the data source for the diagram.
  • Save the Workbook using the workbook.save (string) method.

The following code example shows how to use Excel PivotTable to generate charts in 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");Copy the code

If you have any questions or requirements, please feel free to join the Aspose Technology Exchange Group (761297826), we are happy to provide you with inquiries and consultation.