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
