With Excel Pivot tables, users can calculate, group, and summarize large amounts of data in a concise, tabular format, which makes reporting and analysis easier. As one of the most powerful tools in Excel, it provides users with the ability to view static data from multiple perspectives. This article introduces how to create a pivot table in Excel in Java, how to sort pivot table data, and how to perform other settings in pivot table, by using Spire.XLS for Java.
- Create a Pivot Table in Excel in Java
- Sort Pivot Table by Column Values in Java
- Expand or Collapse Rows in Pivot Table in Java
- Preform Other Settings in Pivot Table
Add Spire.Xls jar as dependency
If you are working on a maven project, you can include the dependency in pom.xml file using this:
<repositories>
<repository>
<id>com.e-iceblue</id>
<name>e-iceblue</name>
<url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.xls</artifactId>
<version>12.8.4</version>
</dependency>
</dependencies>
If you are not using maven, then you can find the required jar files from the zip file available in this location. Include all the jar files into the application lib folder to run the sample code given in this tutorial.
Create a Pivot Table in Excel in Java
Below are the steps to create a pivot table based on the data in an existing Excel file using Spire.XLS for Java.
- Create a Workbook object.
- Load a sample Excel document using Workbook.loadFromFile() method.
- Get a specified worksheet using Workbook.getWorksheets().get() method.
- Select the data source range using Worksheet.getRange().get() method, and add the range to the PivotCachesCollection using Workbook.PivotCaches.add() method and return an object of PivotCache.
- Add a pivot table to the worksheet and set the location and cache of it using Worksheet.getPivotTables().add() method.
- Drag "Region" and "Product" fields to rows area.
- Add"Quantity" and "Amount" fields to values area.
- Save the result document using Workbook.saveToFile() method.
import com.spire.xls.*;
public class CreatePivotTable {
public static void main(String[] args) {
//Create a Workbook object
Workbook workbook = new Workbook();
//Load a sample Excel document
workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\data.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
//Select the data source range
CellRange dataRange = sheet.getRange().get("C1:F11");
PivotCache cache = workbook.getPivotCaches().add(dataRange);
//Add a PivotTable to the worksheet and set the location and cache of it
PivotTable pt = sheet.getPivotTables().add("Pivot Table", sheet.getRange().get("H3"), cache);
//Drag "Region" and "Product" fields to rows area
PivotField regionField =(PivotField)pt.getPivotFields().get("Region");
regionField.setAxis(AxisTypes.Row);
pt.getOptions().setRowHeaderCaption("Region");
PivotField productField = (PivotField)pt.getPivotFields().get("Product");
productField.setAxis(AxisTypes.Row);
//Add "Quantity" and "Amount" fields to values area
pt.getDataFields().add(pt.getPivotFields().get("Quantity"), "SUM of Quantity", SubtotalTypes.Sum);
pt.getDataFields().add(pt.getPivotFields().get("Amount"), "SUM of Amount", SubtotalTypes.Sum);
//Apply a built-in style to the pivot table
pt.setBuiltInStyle(PivotBuiltInStyles.PivotStyleMedium11);
//Set column width
sheet.setColumnWidth(8,16);
sheet.setColumnWidth(9,16);
sheet.setColumnWidth(10,16);
//Save the document
workbook.saveToFile("output/CreatePivotTable.xlsx", ExcelVersion.Version2016);
}
}
Sort Pivot Table by Column Values in Java
A specific field can be accessed by PivotTable.getPivotFields().get() method, and then you can set its sort type using PivotField. setSortType() method. The following code example shows you how to sort pivot table by column values of “Region” field.
import com.spire.xls.*;
import com.spire.xls.core.IPivotDataField;
public class SortPivotTableByRow {
public static void main(String[] args) {
//Create a Workbook object
Workbook workbook = new Workbook();
//Load a sample Excel document
workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\data.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
//Select the data source range
CellRange dataRange = sheet.getRange().get("A1:F11");
PivotCache cache = workbook.getPivotCaches().add(dataRange);
//Add a PivotTable to the worksheet and set the location and cache of it
PivotTable pt = sheet.getPivotTables().add("Pivot Table", sheet.getRange().get("H3"), cache);
//Drag "Region" , "Order ID" and "Product" fields to rows area
PivotField regionField =(PivotField)pt.getPivotFields().get("Region");
regionField.setAxis(AxisTypes.Row);
pt.getOptions().setRowHeaderCaption("Region");
PivotField idField = (PivotField)pt.getPivotFields().get("Order ID");
idField.setAxis(AxisTypes.Row);
PivotField productField = (PivotField)pt.getPivotFields().get("Product");
productField.setAxis(AxisTypes.Row);
//Add "Quantity" and "Amount" fields to values area
pt.getDataFields().add(pt.getPivotFields().get("Quantity"), "SUM of Quantity", SubtotalTypes.Sum);
pt.getDataFields().add(pt.getPivotFields().get("Amount"), "SUM of Amount", SubtotalTypes.Sum);
//Apply a built-in style to the pivot table
pt.setBuiltInStyle(PivotBuiltInStyles.PivotStyleMedium11);
//Calculate data
pt.calculateData();
//Sort data in the column of "Order ID" field
idField.setSortType(PivotFieldSortType.Descending);
//Set column width
sheet.setColumnWidth(8,16);
sheet.setColumnWidth(9,16);
sheet.setColumnWidth(10,16);
//Save the document
workbook.saveToFile("output/SortDataByRow.xlsx", ExcelVersion.Version2016);
}
}
Expand or Collapse Rows in Pivot Table in Java
To collapse the details under a certain pivot field, use PivotField.hideItemDetail(String, Boolean) method and set the second parameter to true; to show the details, use the same method and set the second parameter to false.
import com.spire.xls.*;
public class CollapseRows {
public static void main(String[] args) {
//Create a Workbook object
Workbook workbook = new Workbook();
//Load a sample Excel document
workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\data.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
//Select the data source range
CellRange dataRange = sheet.getRange().get("C1:F11");
PivotCache cache = workbook.getPivotCaches().add(dataRange);
//Add a PivotTable to the worksheet and set the location and cache of it
PivotTable pt = sheet.getPivotTables().add("Pivot Table", sheet.getRange().get("H3"), cache);
//Drag "Region" and "Product" fields to rows area
PivotField regionField =(PivotField)pt.getPivotFields().get("Region");
regionField.setAxis(AxisTypes.Row);
pt.getOptions().setRowHeaderCaption("Region");
PivotField productField = (PivotField)pt.getPivotFields().get("Product");
productField.setAxis(AxisTypes.Row);
//Hide item details of the region field
regionField.hideItemDetail("West",true);
regionField.hideItemDetail("East",true);
//Add "Quantity" and "Amount" fields to values area
pt.getDataFields().add(pt.getPivotFields().get("Quantity"), "SUM of Quantity", SubtotalTypes.Sum);
pt.getDataFields().add(pt.getPivotFields().get("Amount"), "SUM of Amount", SubtotalTypes.Sum);
//Apply a built-in style to the pivot table
pt.setBuiltInStyle(PivotBuiltInStyles.PivotStyleMedium11);
//Calculate data
pt.calculateData();
//Set column width
sheet.setColumnWidth(8,16);
sheet.setColumnWidth(9,16);
sheet.setColumnWidth(10,16);
//Save the document
workbook.saveToFile("output/CollapseRows.xlsx", ExcelVersion.Version2016);
}
}
Other Settings
Refresh Pivot Tale/Update data source:
PivotTable.getCache().isRefreshOnLoad(true);
Display or show subtotals:
PivotTable.isShowSubtotals(true);
Change data source:
PivotTable.changeDataSource();
Add a filter:
PivotReportFilter filter = new PivotReportFilter(String fieldName);
PivotTable.getReportFilters().add(filter);
Top comments (0)