Create Pivot Table and Pivot Chart in Excel in Java

When you need to quickly analyze data in an Excel worksheet and see the comparisons, patterns, and trends in the data, your first instinct may be to create a pivot table. But sometimes not everyone has time to look at the data in the table and see what’s going on. Pivot chart makes it easier to understand the data by representing the data in a graphical way. In this article, I will introduce how to create pivot table and pivot chart in an Excel file in Java using Free Spire.XLS for Java API.

Add Dependencies

First of all, you need to add needed dependencies for including Free Spire.XLS for Java into your Java project. There are two ways to do that.

If you use maven, you need to add the following code to your project’s pom.xml file.

<repositories>  
        <repository>  
            <id>com.e-iceblue</id>  
            <name>e-iceblue</name>  
            <url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>  
        </repository>  
</repositories>  
<dependencies>  
    <dependency>  
        <groupId>e-iceblue</groupId>  
        <artifactId>spire.xls.free</artifactId>  
        <version>3.9.1</version>  
    </dependency>  
</dependencies>

For non-maven projects, download Free Spire.XLS for Java from this website, unzip the package and add Spire.Xls.jar in the lib folder into your project as a dependency.

Create Pivot Table

Free Spire.XLS for Java API provides an XlsPivotTablesCollection.add method that is used to add pivot table to an Excel worksheet. The following example shows how to create an Excel file, add data to Excel worksheet, and then create pivot table to analyze the data in the sheet.

import com.spire.xls.*;

public class CreatePivotTable {
    public static void main(String[] args) {
        //Create a workbook
        Workbook workbook = new Workbook();
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Add some data to the worksheet
        sheet.getCellRange("A1").setValue("Product");
        sheet.getCellRange("B1").setValue("Month");
        sheet.getCellRange("C1").setValue("Sale");

        sheet.getCellRange("A2").setValue("Coffee");
        sheet.getCellRange("A3").setValue("Coffee");
        sheet.getCellRange("A4").setValue("Chocolate");
        sheet.getCellRange("A5").setValue("Chocolate");
        sheet.getCellRange("A6").setValue("Milk");
        sheet.getCellRange("A7").setValue("Milk");

        sheet.getCellRange("B2").setValue("January");
        sheet.getCellRange("B3").setValue("February");
        sheet.getCellRange("B4").setValue("January");
        sheet.getCellRange("B5").setValue("February");
        sheet.getCellRange("B6").setValue("January");
        sheet.getCellRange("B7").setValue("February");

        sheet.getCellRange("C2").setValue("1000");
        sheet.getCellRange("C3").setValue("1500");
        sheet.getCellRange("C4").setValue("900");
        sheet.getCellRange("C5").setValue("700");
        sheet.getCellRange("C6").setValue("800");
        sheet.getCellRange("C7").setValue("1000");

        //Add a PivotTable to the worksheet
        CellRange dataRange = sheet.getCellRange("A1:C7");
        PivotCache cache = workbook.getPivotCaches().add(dataRange);
        PivotTable pt = sheet.getPivotTables().add("Pivot Table", sheet.getCellRange("A10"), cache);

        //Drag the fields to the row area
        PivotField pf =null;
        if (pt.getPivotFields().get("Product") instanceof PivotField){
            pf= (PivotField) pt.getPivotFields().get("Product");
        }
        pf.setAxis(AxisTypes.Row);

        PivotField pf2 =null;
        if (pt.getPivotFields().get("Month") instanceof PivotField){
            pf2= (PivotField) pt.getPivotFields().get("Month");
        }
        pf2.setAxis(AxisTypes.Row);

        //Drag the field to the data area
        pt.getDataFields().add(pt.getPivotFields().get("Sale"), "SUM of Sale", SubtotalTypes.Sum);

        //Set PivotTable style
        pt.setBuiltInStyle(PivotBuiltInStyles.PivotStyleMedium12);

        //Calculate data 
        pt.calculateData();
        //Set column width
        sheet.setColumnWidth(1, 14);
        sheet.setColumnWidth(2, 14);

        //Save the result file
        workbook.saveToFile("CreatePivotTable.xlsx", ExcelVersion.Version2013);
    }
}

Output:

Create Pivot Chart

To add a pivot chart to an Excel worksheet, you need to use the WorksheetChartsCollection.add method. In the following example, you will see how to add a pivot chart to an Excel worksheet.

import com.spire.xls.*;
import com.spire.xls.core.IPivotTable;

public class CreatePivotChart {
    public static void main(String[] args) {
        //Load the Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("CreatePivotTable.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);
        //get the first pivot table in the worksheet
        IPivotTable pivotTable = sheet.getPivotTables().get(0);

        //Add a clustered column chart based on the pivot table to the second worksheet
        Chart chart = workbook.getWorksheets().get(1).getCharts().add(ExcelChartType.ColumnClustered, pivotTable);
        //Set chart position
        chart.setTopRow(2);
        chart.setBottomRow(15);
        //Set chart title
        chart.setChartTitle("Total");

        //Save the result file
        workbook.saveToFile("CreatPivotChart.xlsx", ExcelVersion.Version2013);
    }
}

Output: