Create and Refresh a Pivot Table in Excel using Java

A Pivot Table is usually used to sort, count and total the data that includes the total figures, average, maximum, minimum and so on. By using it, users can group and summarize large amounts of data in a concise, tabular format for easier reporting and analysis. This article will demonstrate how to create a pivot table in an Excel worksheet using Java codes and refresh it after changing the data source.

Dependency

A free API used in this tutorial is called Free Spire.XLS for Java. Before typing codes, we need to download it from the website and then insert Spire.Xls,jar into IDEA or directly reference it using the following Maven configurations:

<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>

Create a Pivot Table
Free Spire.XLS for Java supports creating a pivot table according to the data source in an Excel 
worksheet. Here are some steps to do it.
Step 1. Load an Excel document as a sample and get its first worksheet.
Step 2. Select the data source range.
Step 3. Create a pivot table and set its title and location.
Step 4. Separately add data to the row field, column field and value field.
Step 5. Set the PivotTable style and save the resulting file.
import com.spire.xls.*;
public class CreatePivotTable {
    public static void main(String[] args) {
        //Load an Excel sample
        Workbook workbook = new Workbook();
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\Sample.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Select the data source range
        CellRange dataRange = sheet.getCellRange("A1:C17");
        PivotCache cache = workbook.getPivotCaches().add(dataRange);

        //Add a PivotTable to the worksheet and set the title and location of it
        PivotTable pt = sheet.getPivotTables().add("Pivot Table", sheet.getCellRange("D4"), cache);

        //Add data to the row field
        PivotField pf1 = null;
        if (pt.getPivotFields().get("Quarter") instanceof PivotField){
            pf1 = (PivotField) pt.getPivotFields().get("Quarter");
        }
        pf1.setAxis(AxisTypes.Row);
        //Set the title of the row field
        pt.getOptions().setRowHeaderCaption("Quarter");

        //Add data to the column field
        PivotField pf2 = null;
        if (pt.getPivotFields().get("Product") instanceof PivotField){
            pf2 = (PivotField) pt.getPivotFields().get("Product");
        }
        pf2.setAxis(AxisTypes.Column);
        //Set the title of the column field
        pt.getOptions().setColumnHeaderCaption("Product");

        //Add data to the value field
        pt.getDataFields().add(pt.getPivotFields().get("Sales"),"SUM of sales",SubtotalTypes.Sum);

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

        //Save the document
        workbook.saveToFile("output/CreatePivotTable.xlsx", ExcelVersion.Version2013);
    }
}
Output



Refresh a Pivot Table
In addition to creating a pivot table, Free Spire.XLS for Java supports refresh the pivot table after 
changing the data source. The following codes demonstrate how to refresh the original pivot table after 
changing the products’ names. 
import com.spire.xls.*;
public class RefreshPivotTable {
    public static void main(String[] args) {
        //Load an Excel sample
        Workbook wb = new Workbook();
        wb.loadFromFile("C:\\Users\\Test1\\Desktop\\CreatePivotTable.xlsx");

        //Get the first worksheet
        Worksheet sheet = wb.getWorksheets().get(0);

        //Change the data source of the Pivot Table
        sheet.getCellRange("B2:B5").setText("Refrigerator");
        sheet.getCellRange("B6:B9").setText("Air Conditioner");
        sheet.getCellRange("B10:B13").setText("Washing Machine");
        sheet.getCellRange("B14:B17").setText("Kitchen Ventilator");

        //Get the Pivot Table and refresh the data
        PivotTable pivotTable = (PivotTable) sheet.getPivotTables().get(0);
        pivotTable.getCache().isRefreshOnLoad();

        //Saving the resulting file
        wb.saveToFile("output/RefreshPivotTable.xlsx",FileFormat.Version2013);
    }
}
Output