A pivot table is needed to quickly analyze data of a table with very little effort (and no formulas) and sometimes not everyone has time to look at the data in the table and see what’s going on and use it to build good-looking reports for large data sets in an Excel worksheet. Let’s discuss a step-by-step proper explanation to create a pivot table in an Excel file in Java using Free Spire.XLS for Java API.
Requirements to Create Pivot Table in Excel using Java
Firstly, we need to add necessary dependencies to the Maven project including the Free Spire.XLS project before starting to write the code.
How to Install Spire.XLS for Java
By adding the following code to your project’s pom.xml file, you can quickly import the JAR file into your program if you use Maven.
< repositories >
< repository >
< id >com.e-iceblue</ id >
< name >e-iceblue</ name >
</ repository >
</ repositories >
< dependencies >
< dependency >
< groupId >javax.xml.bind</ groupId >
< artifactId >jaxb-api</ artifactId >
< version >2.3.1</ version > </ dependency >
< dependency >
< groupId >e-iceblue</ groupId >
< artifactId >spire.xls.free</ artifactId >
< version >3.9.1</ version >
</ dependency >
</ dependencies >
|
How to Create a Pivot Table in Excel using Java
Here are the steps for using Java to make a pivot table in Excel.
Step 1: Create a workbook
Workbook workbook = new Workbook();
Step 2: Create a sheet
Worksheet sheet = workbook.getWorksheets().get(0);
Step 3: Add some data to the worksheet in table form.
sheet.getCellRange(“A1”).setValue(“Student Name”);
Step 4: Pivot Cache is something that automatically gets generated when you create a Pivot Table. Create an object of PivotCache and add a range.
PivotCache cache = workbook.getPivotCaches().add(dataRange);
Step 5: Create an object of PivotTable to get PivotTable and add table name, Cell Range from where a table is starting to arg1, PivotCache arg2
PivotTable pivotTable = sheet.getPivotTables().add(“Pivot Table”, sheet.getCellRange(“A16”), cache);
Step 6: Create a pivot field to configure the pivot table’s fields after that set layout of the pivot table by using the set Axis method and then select type.
PivotField pivotField1 = null;
if (pivotTable.getPivotFields().get(“Student Name”) instanceof PivotField) {
pivotField1 = (PivotField) pivotTable.getPivotFields().get(“Student Name”);
}
pivotField1.setAxis(AxisTypes.Row);
Step 7: Drag the field to the data area
pivotTable.getDataFields().add(pivotTable.getPivotFields().get(“Attendance”), “SUM of Attendance”, SubtotalTypes.Sum);
Step 8: Set PivotTable style
pivotTable.setBuiltInStyle(PivotBuiltInStyles.PivotStyleMedium12);
Step 9: Calculate the data
pivotTable.calculateData();
Step 10: Set column width with setColumnWidth method having two parameters, first is columnIndex in int datatype, width size in double datatype.
sheet.setColumnWidth(1, 14);
sheet.setColumnWidth(2, 14);
Step 11: Save workbook by using saveToFile(String fileName, ExcelVersion version)
workbook.saveToFile(workbookName, ExcelVersion.Version2013);
How to write a Java Program to create a pivot table in a spreadsheet
import com.spire.xls.AxisTypes;
import com.spire.xls.CellRange;
import com.spire.xls.ExcelVersion;
import com.spire.xls.PivotBuiltInStyles;
import com.spire.xls.PivotCache;
import com.spire.xls.PivotField;
import com.spire.xls.PivotTable;
import com.spire.xls.SubtotalTypes;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
class GFG {
public static void main(String[] args)
{
// Create a workbook
Workbook workbook = new Workbook();
// Create a sheet
Worksheet sheet = workbook.getWorksheets().get( 0 );
// Add data to the worksheet in table form
// Header
sheet.getCellRange( "A1" ).setValue( "Student Name" );
sheet.getCellRange( "B1" ).setValue( "Month" );
sheet.getCellRange( "C1" ).setValue( "Attendance" );
// Data
sheet.getCellRange( "A2" ).setValue( "Harry" );
sheet.getCellRange( "A3" ).setValue( "Harry" );
sheet.getCellRange( "A4" ).setValue( "Harry" );
sheet.getCellRange( "A5" ).setValue( "Nicole" );
sheet.getCellRange( "A6" ).setValue( "Nicole" );
sheet.getCellRange( "A7" ).setValue( "Nicole" );
sheet.getCellRange( "A8" ).setValue( "Peter" );
sheet.getCellRange( "A9" ).setValue( "Peter" );
sheet.getCellRange( "A10" ).setValue( "Peter" );
sheet.getCellRange( "A11" ).setValue( "Lisa" );
sheet.getCellRange( "A12" ).setValue( "Lisa" );
sheet.getCellRange( "A13" ).setValue( "Lisa" );
sheet.getCellRange( "B2" ).setValue( "January" );
sheet.getCellRange( "B3" ).setValue( "February" );
sheet.getCellRange( "B4" ).setValue( "March" );
sheet.getCellRange( "B5" ).setValue( "January" );
sheet.getCellRange( "B6" ).setValue( "February" );
sheet.getCellRange( "B7" ).setValue( "March" );
sheet.getCellRange( "B8" ).setValue( "January" );
sheet.getCellRange( "B9" ).setValue( "February" );
sheet.getCellRange( "B10" ).setValue( "March" );
sheet.getCellRange( "B11" ).setValue( "January" );
sheet.getCellRange( "B12" ).setValue( "February" );
sheet.getCellRange( "B13" ).setValue( "March" );
sheet.getCellRange( "C2" ).setValue( "25" );
sheet.getCellRange( "C3" ).setValue( "22" );
sheet.getCellRange( "C4" ).setValue( "24" );
sheet.getCellRange( "C5" ).setValue( "24" );
sheet.getCellRange( "C6" ).setValue( "23" );
sheet.getCellRange( "C7" ).setValue( "24" );
sheet.getCellRange( "C8" ).setValue( "22" );
sheet.getCellRange( "C9" ).setValue( "15" );
sheet.getCellRange( "C10" ).setValue( "23" );
sheet.getCellRange( "C11" ).setValue( "25" );
sheet.getCellRange( "C12" ).setValue( "20" );
sheet.getCellRange( "C13" ).setValue( "18" );
// Add a PivotTable to the worksheet
// Get Range of Table
CellRange dataRange = sheet.getCellRange( "A1:C13" );
PivotCache cache
= workbook.getPivotCaches().add(dataRange);
PivotTable pivotTable = sheet.getPivotTables().add(
"Pivot Table" , sheet.getCellRange( "A16" ),
cache);
// Drag the fields to the row area
PivotField pivotField1 = null ;
if (pivotTable.getPivotFields().get( "Student Name" )
instanceof PivotField) {
pivotField1
= (PivotField)pivotTable.getPivotFields()
.get( "Student Name" );
}
pivotField1.setAxis(AxisTypes.Row);
PivotField pivotField2 = null ;
if (pivotTable.getPivotFields().get( "Month" )
instanceof PivotField) {
pivotField2
= (PivotField)pivotTable.getPivotFields()
.get( "Month" );
}
pivotField2.setAxis(AxisTypes.Row);
// Drag the field to the data area
pivotTable.getDataFields().add(
pivotTable.getPivotFields().get( "Attendance" ),
"SUM of Attendance" , SubtotalTypes.Sum);
// Set PivotTable style
pivotTable.setBuiltInStyle(
PivotBuiltInStyles.PivotStyleMedium12);
// Calculate data
pivotTable.calculateData();
// Set column width
sheet.setColumnWidth( 1 , 14 );
sheet.setColumnWidth( 2 , 14 );
// Save the result file
String workbookName = "Geeks_For_Geeks.xlsx" ;
workbook.saveToFile(workbookName,
ExcelVersion.Version2013);
System.out.println(workbookName
+ " is written successfully" );
}
} |
Output
On the console window
When the program is successfully executed.
Geeks_For_Geeks.xlsx is written successfully
Output
Workbook (Excel file)
FAQs on How to Create Pivot Table in Excel using Java?
Q1: What is a Pivot Table in Excel?
Answer:
A pivot table is one of the most useful features in Excel. They are used to summarize or aggregate lots of data. The summarization of the data can be in the form of average, count, and other statistical methods. It converts a lot of data into a few rows and columns of summary.
Q2: How to define the data range for the Pivot Table?
Answer:
Specify the cell range containing the data you want to include in the Pivot Table. For example, if your data is in cells A1 to C50, you can define the range as “A1:C50” in Excel.
Q3: How to create a Pivot Table in Excel using Java?
Answer:
You can use the Apache POI library to create a Pivot table in Excel using Java. Apache POI library provides support for working with Excel Files. Follow the below steps:
Step 1: Set up your Java project and include the Apache POI library as a dependency.
Step 2: Load the Excel workbook that contains the data you want to use for the Pivot Table.
Step 3: Define the range of the cells you want to include in the Pivot Table.
Step 4: Create a new sheet in the workbook where you want the Pivot table to be located.
Step 5: Use the Apache POI API to create and configure the Pivot table based on the selected data range.
Step 6: Save the modified workbook.