# Create Pivot Table

Learn how to create pivot tables using DsExcel in a spreadsheet. Learn more in DsExcel docs.

## Content

DsExcel provides functionality to create and add pivot tables in a spreadsheet using either a specified range or a table. [create](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/IPivotCaches.html#create) method of [IPivotCaches](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/IPivotCaches.html) interface initializes the pivot cache for the pivot table, while [add](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/IPivotTables.html#add) method of [IPivotTables](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/IPivotTables.html) interface uses this cache to insert the new pivot table into the spreadsheet.

## Create Pivot Table with Range-Based Data Source

Refer to the following example code to create a pivot table in a worksheet with a range:

```Java
// Create a new workbook.
Workbook workbook = new Workbook();

// Source data for PivotCache.
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"},
};

// Fetch the first workSheet.
IWorksheet worksheet = workbook.getWorksheets().get(0);
        
// Assigning data to the range.
worksheet.getRange("G1:L16").setValue(sourceData);
worksheet.getRange("G:L").setColumnWidth(15);
        
// Create pivot table.
IPivotCache pivotcache = workbook.getPivotCaches().create(worksheet.getRange("G1:L16"));
IPivotTable pivottable = worksheet.getPivotTables().add(pivotcache, worksheet.getRange("A1"), "pivottable1");
worksheet.getRange("J1:J16").setNumberFormat("$#,##0.00");

// Configure pivot table fields.
IPivotField field_Category = pivottable.getPivotFields().get("Category");
field_Category.setOrientation(PivotFieldOrientation.ColumnField);

IPivotField field_Product = pivottable.getPivotFields().get("Product");
field_Product.setOrientation(PivotFieldOrientation.RowField);

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

IPivotField field_Country = pivottable.getPivotFields().get("Country");
field_Country.setOrientation(PivotFieldOrientation.PageField);

worksheet.getRange("A:D").getEntireColumn().autoFit();
    
// Save to an excel file.
workbook.save("CreatePivotTable.xlsx");
```

![](https://cdn.mescius.io/document-site-files/images/dd59ea42-cd61-4fa6-a018-6231c2a9c598/images/pivot-table-range.png)

## Create Pivot Table with Table-Based Data Source

Refer to the following example code to create a pivot table in a worksheet with a table:

```Java
// Create a new workbook.
var workbook = new Workbook();

// Create source for the table.
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"},
};

// Assigning data to the range.
IWorksheet worksheet = workbook.getWorksheets().get(0);
worksheet.getRange("G1:L16").setValue(sourceData);
worksheet.getRange("G:L").setColumnWidth(15);

// Create table with range G1:L16.
ITable table = worksheet.getTables().add(worksheet.getRange("G1:L16"), true);

// Create pivot table using table as data source.
IPivotCache pivotcache = workbook.getPivotCaches().create(table);
IPivotTable pivottable = worksheet.getPivotTables().add(pivotcache, worksheet.getRange("A1"), "pivottable1");
worksheet.getRange("J1:J16").setNumberFormat("$#,##0.00");

// Configure pivot table's fields.
IPivotField field_Category = pivottable.getPivotFields().get("Category");
field_Category.setOrientation(PivotFieldOrientation.ColumnField);

IPivotField field_Product = pivottable.getPivotFields().get("Product");
field_Product.setOrientation(PivotFieldOrientation.RowField);

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

IPivotField field_Country = pivottable.getPivotFields().get("Country");
field_Country.setOrientation(PivotFieldOrientation.PageField);

worksheet.getRange("A:D").getEntireColumn().autoFit();

// Save the workbook.
workbook.save("CreatePivotTableUsingTable.xlsx");
```

![](https://cdn.mescius.io/document-site-files/images/dd59ea42-cd61-4fa6-a018-6231c2a9c598/images/pivot-table-table.png)

## Use Picture in Cell as Pivot Table Data Source

Pivot tables support using ranges that contain in-cell pictures as the data source. When sorting or filtering pivot fields that are based on in-cell pictures, the picture's alt text is used for comparison and filtering.
Refer to the following example code to create a pivot table with in-cell pictures as a source.

```java
// Create a new workbook.
Workbook workbook = new Workbook();
IWorksheet sheet = workbook.getActiveSheet();

try {
    // Set data.    
    Object[][] data = new Object[][]{
            {"Item", "Category", "Quantity", "Image"},
            {"Dress", "Clothing", 10, null},
            {"Hat", "Accessories", 5, null},
            {"Boots", "Shoes", 8, null},
            {"Glasses", "Accessories", 3, null}
    };
    sheet.getRange("G1:J5").setValue(data);

    // Set pictures in cells with alt text.    
    String[] imageFiles = {"Hat.png", "Boots.png", "Dress.png", "Glasses.png"};
    for (int i = 0; i < imageFiles.length; i++) {
        byte[] imageData = Files.readAllBytes(Paths.get(imageFiles[i]));
        String fileNameWithoutExtension = Paths.get(imageFiles[i]).getFileName().toString().replace(".png", "");
        sheet.getRange("J" + (i + 2)).setCellPicture(new CellPicture(imageData, fileNameWithoutExtension));
    }

    // Configure pivot table's fields.    
    IPivotCache pivotcache = workbook.getPivotCaches().create(sheet.getRange("G1:J5"));
    IPivotTable pivottable = sheet.getPivotTables().add(pivotcache, sheet.getRange("A1"), "pivottable");
    pivottable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.ColumnField);
    pivottable.getPivotFields().get("Image").setOrientation(PivotFieldOrientation.RowField);
    pivottable.getPivotFields().get("Quantity").setOrientation(PivotFieldOrientation.DataField);

    // Save to an Excel file.    
    workbook.save("PivotTablewithPicture.xlsx");
} catch (IOException e) {
    throw new RuntimeException("Error loading image resource", e);
}
```

The output is shown in the figure below:
![image](https://cdn.mescius.io/document-site-files/images/6c089d65-4816-4591-8c0d-bb0bfb8c43df/image-20260414.eb2da1.png)
**Limitation**
DsExcel does not support referencing partial table area, such as 'Table1[[Column1]:[Column3]]'.