# Creating Pivot Tables in Excel Using Java

Learn how to programmatically create pivot tables in Excel in Java applications. Get started and see more from Document Solutions today.

## Content

## Creating Pivot Tables in Excel Using Java (DsExcel Java)

This tutorial shows how to generate **Excel pivot tables programmatically** using **Document Solutions for Excel, Java Edition (DsExcel Java)**. You’ll place your raw dataset on a **dedicated source worksheet**, then generate a pivot table on a **separate report worksheet**.
Pivot tables are one of the most useful Excel features for summarizing large datasets, such as sales reports, inventory lists, logs, or financial exports. With DsExcel Java, you can automate pivot table creation directly in your Java applications.

## How Pivot Tables Work in DsExcel Java

DsExcel creates pivot tables in two steps:

1. **Create a pivot cache** using `IPivotCaches.create(...)` (this defines the pivot data source).
2. **Insert a pivot table** using `IPivotTables.add(...)` (this places the pivot table into a worksheet using the cache).

You can build the pivot cache from either:

* A **range** (range-based data source), or
* A **table** (table-based data source)

***

## Prerequisites

* **JDK 8+**
* **DsExcel Java** (JARs added to your classpath)

```auto
import com.grapecity.documents.excel.*;
```

***

## 1) Add Pivot Source Data to a Separate Worksheet

Start by creating a workbook and a worksheet dedicated to pivot source data.

```auto
Workbook workbook = new Workbook();

IWorksheet sourceSheet = workbook.getWorksheets().get(0);
sourceSheet.setName("Pivot Source");
```

Add a dataset (header row + records) into a range. Pivot tables typically work best when your dataset is a clean rectangle with column headers.

```auto
Object[][] sourceData = new Object[][]{
    {"Order ID", "Product", "Category", "Amount", "Date", "Country"},
    {1, "Bose 785593-0050", "Consumer Electronics", 4270, new GregorianCalendar(2018, 0, 6), "United States"},
    {2, "Canon EOS 1500D", "Consumer Electronics", 8239, new GregorianCalendar(2018, 0, 7), "United Kingdom"},
    {3, "Haier 394L 4Star", "Consumer Electronics", 617, new GregorianCalendar(2018, 0, 8), "United States"},
    {4, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 8384, new GregorianCalendar(2018, 0, 10), "Canada"},
    {5, "Mi LED 40inch", "Consumer Electronics", 2626, new GregorianCalendar(2018, 0, 10), "Germany"},
    {6, "Sennheiser HD 4.40-BT", "Consumer Electronics", 3610, new GregorianCalendar(2018, 0, 11), "United States"},
    {7, "Iphone XR", "Mobile", 9062, new GregorianCalendar(2018, 0, 11), "Australia"},
    {8, "OnePlus 7Pro", "Mobile", 6906, new GregorianCalendar(2018, 0, 16), "New Zealand"},
    {9, "Redmi 7", "Mobile", 2417, new GregorianCalendar(2018, 0, 16), "France"},
    {10, "Samsung S9", "Mobile", 7431, new GregorianCalendar(2018, 0, 16), "Canada"},
    {11, "OnePlus 7Pro", "Mobile", 8250, new GregorianCalendar(2018, 0, 16), "Germany"},
    {12, "Redmi 7", "Mobile", 7012, new GregorianCalendar(2018, 0, 18), "United States"},
    {13, "Bose 785593-0050", "Consumer Electronics", 1903, new GregorianCalendar(2018, 0, 20), "Germany"},
    {14, "Canon EOS 1500D", "Consumer Electronics", 2824, new GregorianCalendar(2018, 0, 22), "Canada"},
    {15, "Haier 394L 4Star", "Consumer Electronics", 6946, new GregorianCalendar(2018, 0, 24), "France"},
};

sourceSheet.getRange("A1:F16").setValue(sourceData);
sourceSheet.getRange("A:F").setColumnWidth(18);
sourceSheet.getRange("D2:D16").setNumberFormat("$#,##0.00"); // Amount column
```

The Pivot Source sheet data will look as follows:
![image](https://cdn.mescius.io/document-site-files/images/7466bdc8-02f8-494c-b60a-49ddbe7c10d4/image-20260218.8d3910.png)

***

## 2) Create a Separate Worksheet for the Pivot Table Output

Create a second worksheet that will contain the pivot table.

```auto
IWorksheet pivotSheet = workbook.getWorksheets().add();
pivotSheet.setName("Pivot Report");
```

***

## 3) Create the Pivot Cache (IPivotCache)

Build the pivot cache using the source range on the source worksheet.

```auto
IPivotCache pivotCache = workbook.getPivotCaches()
    .create(sourceSheet.getRange("A1:F16"));
```

***

## 4) Add the Pivot Table (IPivotTable)

Insert the pivot table onto the pivot report sheet, starting at a chosen cell (for example, A1).

```auto
IPivotTable pivotTable = pivotSheet.getPivotTables()
    .add(pivotCache, pivotSheet.getRange("A1"), "pivotTable1");
```

***

## 5) Configure Pivot Fields (Orientation Examples)

Assign fields to their pivot roles:

* **RowField**: items listed vertically
* **ColumnField**: grouped across the top
* **DataField**: summarized values (Sum, Count, etc.)
* **PageField**: filter dropdown

This setup creates a pivot where:

* **Product** = rows
* **Category** = columns
* **Amount** = summarized totals
* **Country** = filter

```auto
IPivotField fieldCategory = pivotTable.getPivotFields().get("Category");
fieldCategory.setOrientation(PivotFieldOrientation.ColumnField);

IPivotField fieldProduct = pivotTable.getPivotFields().get("Product");
fieldProduct.setOrientation(PivotFieldOrientation.RowField);

IPivotField fieldAmount = pivotTable.getPivotFields().get("Amount");
fieldAmount.setOrientation(PivotFieldOrientation.DataField);
fieldAmount.setNumberFormat("$#,##0.00");

IPivotField fieldCountry = pivotTable.getPivotFields().get("Country");
fieldCountry.setOrientation(PivotFieldOrientation.PageField);

// Optional: make the report sheet readable
pivotSheet.getRange("A:D").getEntireColumn().autoFit();
```

***

## Final Pivot Table Output

When you open the generated workbook in Excel, you’ll see:

* **Pivot Source** sheet: your raw dataset (A1:F16)
* **Pivot Report** sheet: a pivot table beginning at **A1** that:
    * Lists **Products** down the left (rows)
    * Shows **Categories** across the top (columns)
    * Displays **Sum of Amount** in the values area
    * Includes a **Country** filter dropdown (page field)

![image](https://cdn.mescius.io/document-site-files/images/7466bdc8-02f8-494c-b60a-49ddbe7c10d4/image-20260218.57d6c2.png)

***

## Save the Workbook

```auto
workbook.save("CreatePivotTable.xlsx");
```

***

## Next Steps

* Explore the [full documentation for pivot tables](https://developer.mescius.com/document-solutions/java-excel-api/docs/online/Features/UsePivotTable) here
* View our [full-featured demos](https://developer.mescius.com/document-solutions/java-excel-api/demos/pivottable) on pivot tables