Excel PivotTable has powerful data processing function, which can make the data in the table more intuitive. With Excel PivotTable, it is convenient for users to sort and filter all kinds of data quickly, and at the same time, it can meet the needs of users for different data summary. This article describes how to create and format an excel PivotTable in a Java application.

Use tools: Free Spire. XLS for Java (free version)

Jar file import method

Method 1:

Download and extract the latest free fire. XLS for java package, and then import the fire. XLS. Jar package into your Java application from the lib folder. (see the figure below after importing successfully)

Method 2:

Import through Maven warehouse installation. Please refer to the link for detailed operation steps:

https://www.e-iceblue.cn/licensing/install-spirepdf-for-java-from-maven-repository.html

Java code example

import com.spire.xls.*;
public class CreatePivotTable {
    public static void main(String[] args)  {

        //Load sample document
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Sample.xlsx");

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

        //Create a cache for data that needs to be aggregated and analyzed
        CellRange dataRange = sheet.getCellRange("B1:D11");
        PivotCache cache = workbook.getPivotCaches().add(dataRange);

        //Create a PivotTable using the cache and specify the name of the PivotTable and its location in the worksheet
        PivotTable pt = sheet.getPivotTables().add("Pivot Table", sheet.getCellRange("F4"), cache);

        //Add row field
        PivotField pf=null;
        if (pt.getPivotFields().get("region") instanceof PivotField){
          pf= (PivotField) pt.getPivotFields().get("region");
        }
        pf.setAxis(AxisTypes.Row);
        PivotField pf2 =null;
        if (pt.getPivotFields().get("commodity") instanceof PivotField){
          pf2= (PivotField) pt.getPivotFields().get("commodity");
        }
        pf2.setAxis(AxisTypes.Row);

        //Add value field
        pt.getDataFields().add(pt.getPivotFields().get("Amount of money"), "Sum: amount", SubtotalTypes.Sum);

        //Style pivot table
        pt.setBuiltInStyle(PivotBuiltInStyles.PivotStyleMedium12);

        //Save document
        workbook.saveToFile("Pivot table.xlsx", ExcelVersion.Version2013);
    }
}

This code is to summarize the sales amount of various commodities in each region. The generated pivot table is as follows: