Document Solutions for Excel, Java Edition | Document Solutions
Features / Pivot Table / Create Pivot Table
In This Topic
    Create Pivot Table
    In This Topic

    DsExcel provides functionality to create and add pivot tables in a spreadsheet using either a specified range or a table. create method of IPivotCaches interface initializes the pivot cache for the pivot table, while add method of IPivotTables 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
    Copy Code 
    // 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");

    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
    Copy Code 
    // 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");


    Limitation

    DsExcel does not support referencing partial table area, such as 'Table1[[Column1]:[Column3]]'.