Pivot tables in Excel spreadsheets are used to summarize the data in an interactive way. Suppose, you have the data of a number of invoices in a worksheet. In that case, you can use a pivot table to total the invoices grouped by the customers or products. In this article, you will learn how to deal with pivot tables in Excel programmatically. Particularly, you will come to know how to create a pivot table in Excel using Java. Furthermore, we will also generate charts based on the pivot table.
- Java API to Work with Excel Pivot Tables
- Create a Pivot Table in Excel
- Generate Chart using a Pivot Table
- Get a Free API License
Java API to Create Pivot Table in Excel
In order to create Excel pivot table, we’ll use Aspose.Cells for Java. It is a powerful API that lets you generate, modify and convert Excel files from within your Java applications. You can either download the API or install it using the following Maven configurations.
<repository>
<id>AsposeJavaAPI</id>
<name>Aspose Java API</name>
<url>https://repository.aspose.com/repo/</url>
</repository><dependency>
<groupId>com.aspose</groupId>
<artifactId>aspose-cells</artifactId>
<version>21.4</version>
</dependency>Create a Pivot Table in Excel using Java
The following are the steps to create a pivot table in Excel using Java.
- Create a new or load an existing Excel file using the Workbook class.
- Populate the worksheet with data (optional).
- Get collection of pivot tables into a PivotTableCollection object using Worksheet.getPivotTables() method.
- Add a new pivot table using PivotTableCollection.add(string, string, string) method, and get its reference in a PivotTable object.
- Set options such as grand total, formatting, etc.
- Add fields to area using PivotTable.addFieldToArea(int, int) method.
- Save the workbook using Workbook.save(string) method.
The following code sample shows how to add a pivot table in Excel.
Excel Data
Pivot Table
Generate a Chart using Excel Pivot Tables in Java
The following are the steps to generate a chart using Excel pivot tables in Java.
- Create a new or load an existing Excel file using the Workbook class.
- Populate the worksheet (optional).
- Add a new worksheet of chart type using Workbook.getWorksheets().add(SheetType.CHART) and get its reference in a Worksheet object.
- Add a new chart using Worksheet.getCharts().add() method and get its reference in a Chart object.
- Set pivot table as the data source of chart using Chart.setPivotSource(string) method.
- Save the workbook using Workbook.save(string) method.
The following code sample shows how to generate a chart using an Excel pivot table.
Get a Free API License
You can get a free temporary license to use the API without evaluation limitations.
Conclusion
In this article, you have learned how to create a pivot table in Excel using Java. Furthermore, you have seen how to generate charts using the pivot tables in Excel. You can explore more about Java Excel API using documentation. In case you would have any questions, feel free to let us know via our forum.
