Create Pivot Table

The following example shows you how to create a pivot table.
import com.independentsoft.office.spreadsheet.Cell;
import com.independentsoft.office.spreadsheet.Workbook;
import com.independentsoft.office.spreadsheet.Worksheet;
import com.independentsoft.office.spreadsheet.pivotTables.DataField;
import com.independentsoft.office.spreadsheet.pivotTables.Field;
import com.independentsoft.office.spreadsheet.pivotTables.Location;
import com.independentsoft.office.spreadsheet.pivotTables.NumericValue;
import com.independentsoft.office.spreadsheet.pivotTables.PivotCache;
import com.independentsoft.office.spreadsheet.pivotTables.PivotCacheField;
import com.independentsoft.office.spreadsheet.pivotTables.PivotCacheRecord;
import com.independentsoft.office.spreadsheet.pivotTables.PivotCacheSource;
import com.independentsoft.office.spreadsheet.pivotTables.PivotField;
import com.independentsoft.office.spreadsheet.pivotTables.PivotFieldItem;
import com.independentsoft.office.spreadsheet.pivotTables.PivotItemType;
import com.independentsoft.office.spreadsheet.pivotTables.PivotTable;
import com.independentsoft.office.spreadsheet.pivotTables.PivotTableAxis;
import com.independentsoft.office.spreadsheet.pivotTables.SharedItems;
import com.independentsoft.office.spreadsheet.pivotTables.SourceType;
import com.independentsoft.office.spreadsheet.pivotTables.StringValue;
import com.independentsoft.office.spreadsheet.pivotTables.WorksheetSource;

public class Example {

    public static void main(String[] args)
    {
        try
        {
            String product1 = "Product1";
            String product2 = "Product2";
            String product3 = "Product3";
            String product4 = "Product4";
            String product5 = "Product5";

            String quarter1 = "Qtr.1";
            String quarter2 = "Qtr.2";
            String quarter3 = "Qtr.3";
            String quarter4 = "Qtr.4";

            int salesP1Q1 = 5000;
            int salesP1Q3 = 4500;
            int salesP1Q4 = 3900;
            int salesP2Q1 = 2300;
            int salesP2Q2 = 3100;
            int salesP2Q3 = 5000;
            int salesP2Q4 = 9000;
            int salesP3Q1 = 8500;
            int salesP3Q3 = 7800;
            int salesP3Q4 = 6600;
            int salesP4Q3 = 2230;
            int salesP4Q4 = 1190;
            int salesP5Q1 = 5200;
            int salesP5Q2 = 4200;
            int salesP5Q3 = 3680;
            int salesP5Q4 = 5230;

            Worksheet sheet1 = new Worksheet("TestSheet");

            sheet1.set("A1", new Cell("Product"));
            sheet1.set("A2", new Cell(product1));
            sheet1.set("A3", new Cell(product1));
            sheet1.set("A4", new Cell(product1));
            sheet1.set("A5", new Cell(product2));
            sheet1.set("A6", new Cell(product2));
            sheet1.set("A7", new Cell(product2));
            sheet1.set("A8", new Cell(product2));
            sheet1.set("A9", new Cell(product3));
            sheet1.set("A10", new Cell(product3));
            sheet1.set("A11", new Cell(product3));
            sheet1.set("A12", new Cell(product4));
            sheet1.set("A13", new Cell(product4));
            sheet1.set("A14", new Cell(product5));
            sheet1.set("A15", new Cell(product5));
            sheet1.set("A16", new Cell(product5));
            sheet1.set("A17", new Cell(product5));

            sheet1.set("B1", new Cell("Quarter"));
            sheet1.set("B2", new Cell(quarter1));
            sheet1.set("B3", new Cell(quarter3));
            sheet1.set("B4", new Cell(quarter4));
            sheet1.set("B5", new Cell(quarter1));
            sheet1.set("B6", new Cell(quarter2));
            sheet1.set("B7", new Cell(quarter3));
            sheet1.set("B8", new Cell(quarter4));
            sheet1.set("B9", new Cell(quarter1));
            sheet1.set("B10", new Cell(quarter3));
            sheet1.set("B11", new Cell(quarter4));
            sheet1.set("B12", new Cell(quarter3));
            sheet1.set("B13", new Cell(quarter4));
            sheet1.set("B14", new Cell(quarter1));
            sheet1.set("B15", new Cell(quarter2));
            sheet1.set("B16", new Cell(quarter3));
            sheet1.set("B17", new Cell(quarter4));

            sheet1.set("C1", new Cell("Sales"));
            sheet1.set("C2", new Cell(salesP1Q1));
            sheet1.set("C3", new Cell(salesP1Q3));
            sheet1.set("C4", new Cell(salesP1Q4));
            sheet1.set("C5", new Cell(salesP2Q1));
            sheet1.set("C6", new Cell(salesP2Q2));
            sheet1.set("C7", new Cell(salesP2Q3));
            sheet1.set("C8", new Cell(salesP2Q4));
            sheet1.set("C9", new Cell(salesP3Q1));
            sheet1.set("C10", new Cell(salesP3Q3));
            sheet1.set("C11", new Cell(salesP3Q4));
            sheet1.set("C12", new Cell(salesP4Q3));
            sheet1.set("C13", new Cell(salesP4Q4));
            sheet1.set("C14", new Cell(salesP5Q1));
            sheet1.set("C15", new Cell(salesP5Q2));
            sheet1.set("C16", new Cell(salesP5Q3));
            sheet1.set("C17", new Cell(salesP5Q4));

            PivotTable pivotTable1 = new PivotTable(1, "PivotTable1", "Sum of Sales");
            pivotTable1.setLocation(new Location("G1:L8", 1, 2, 1));

            PivotField productPivotField = new PivotField();
            productPivotField.setAxis(PivotTableAxis.ROW);

            PivotFieldItem productPivotFieldItem1 = new PivotFieldItem(0);
            PivotFieldItem productPivotFieldItem2 = new PivotFieldItem(1);
            PivotFieldItem productPivotFieldItem3 = new PivotFieldItem(2);
            PivotFieldItem productPivotFieldItem4 = new PivotFieldItem(3);
            PivotFieldItem productPivotFieldItem5 = new PivotFieldItem(4);
            PivotFieldItem productPivotFieldItem6 = new PivotFieldItem(PivotItemType.DEFAULT);

            productPivotField.getItems().add(productPivotFieldItem1);
            productPivotField.getItems().add(productPivotFieldItem2);
            productPivotField.getItems().add(productPivotFieldItem3);
            productPivotField.getItems().add(productPivotFieldItem4);
            productPivotField.getItems().add(productPivotFieldItem5);
            productPivotField.getItems().add(productPivotFieldItem6);

            PivotField quarterPivotField = new PivotField();
            quarterPivotField.setAxis(PivotTableAxis.COLUMN);

            PivotFieldItem quarterPivotFieldItem1 = new PivotFieldItem(0);
            PivotFieldItem quarterPivotFieldItem2 = new PivotFieldItem(1);
            PivotFieldItem quarterPivotFieldItem3 = new PivotFieldItem(2);
            PivotFieldItem quarterPivotFieldItem4 = new PivotFieldItem(3);
            PivotFieldItem quarterPivotFieldItem5 = new PivotFieldItem(PivotItemType.DEFAULT);

            quarterPivotField.getItems().add(quarterPivotFieldItem1);
            quarterPivotField.getItems().add(quarterPivotFieldItem2);
            quarterPivotField.getItems().add(quarterPivotFieldItem3);
            quarterPivotField.getItems().add(quarterPivotFieldItem4);
            quarterPivotField.getItems().add(quarterPivotFieldItem5);

            PivotField salesPivotField = new PivotField();
            salesPivotField.setDataField(true);

            pivotTable1.getPivotFields().add(productPivotField);
            pivotTable1.getPivotFields().add(quarterPivotField);
            pivotTable1.getPivotFields().add(salesPivotField);

            Field productField = new Field(0);
            pivotTable1.getRowFields().add(productField);

            Field quarterField = new Field(1);
            pivotTable1.getColumnFields().add(quarterField);

            DataField salesField = new DataField(2);
            pivotTable1.getDataFields().add(salesField);
            
            PivotCache pivotCache1 = new PivotCache();
            pivotCache1.setRefreshOnLoad(true);

            PivotCacheSource cacheSource = new PivotCacheSource();
            cacheSource.setWorksheetSource(new WorksheetSource("TestSheet", "A1:C17"));
            cacheSource.setType(SourceType.WORKSHEET);

            pivotCache1.setSource(cacheSource);

            SharedItems sharedItems1 = new SharedItems();
            sharedItems1.getValues().add(new StringValue(product1));
            sharedItems1.getValues().add(new StringValue(product2));
            sharedItems1.getValues().add(new StringValue(product3));
            sharedItems1.getValues().add(new StringValue(product4));
            sharedItems1.getValues().add(new StringValue(product5));
            
            SharedItems sharedItems2 = new SharedItems();
            sharedItems2.getValues().add(new StringValue(quarter1));
            sharedItems2.getValues().add(new StringValue(quarter2));
            sharedItems2.getValues().add(new StringValue(quarter3));
            sharedItems2.getValues().add(new StringValue(quarter4));

            PivotCacheField productCacheField = new PivotCacheField("Product");
            productCacheField.setSharedItems(sharedItems1);

            PivotCacheField quarterCacheField = new PivotCacheField("Quarter");
            quarterCacheField.setSharedItems(sharedItems2);

            PivotCacheField salesCacheField = new PivotCacheField("Sales");

            pivotCache1.getFields().add(productCacheField);
            pivotCache1.getFields().add(quarterCacheField);
            pivotCache1.getFields().add(salesCacheField);

            PivotCacheRecord record1 = new PivotCacheRecord();
            record1.getValues().add(new StringValue(product1));
            record1.getValues().add(new StringValue(quarter1));
            record1.getValues().add(new NumericValue(salesP1Q1));

            PivotCacheRecord record2 = new PivotCacheRecord();
            record2.getValues().add(new StringValue(product1));
            record2.getValues().add(new StringValue(quarter3));
            record2.getValues().add(new NumericValue(salesP1Q3));

            PivotCacheRecord record3 = new PivotCacheRecord();
            record3.getValues().add(new StringValue(product1));
            record3.getValues().add(new StringValue(quarter4));
            record3.getValues().add(new NumericValue(salesP1Q4));

            PivotCacheRecord record4 = new PivotCacheRecord();
            record4.getValues().add(new StringValue(product2));
            record4.getValues().add(new StringValue(quarter1));
            record4.getValues().add(new NumericValue(salesP2Q1));

            PivotCacheRecord record5 = new PivotCacheRecord();
            record5.getValues().add(new StringValue(product2));
            record5.getValues().add(new StringValue(quarter2));
            record5.getValues().add(new NumericValue(salesP2Q2));

            PivotCacheRecord record6 = new PivotCacheRecord();
            record6.getValues().add(new StringValue(product2));
            record6.getValues().add(new StringValue(quarter3));
            record6.getValues().add(new NumericValue(salesP2Q3));

            PivotCacheRecord record7 = new PivotCacheRecord();
            record7.getValues().add(new StringValue(product2));
            record7.getValues().add(new StringValue(quarter4));
            record7.getValues().add(new NumericValue(salesP2Q4));

            PivotCacheRecord record8 = new PivotCacheRecord();
            record8.getValues().add(new StringValue(product3));
            record8.getValues().add(new StringValue(quarter1));
            record8.getValues().add(new NumericValue(salesP3Q1));

            PivotCacheRecord record9 = new PivotCacheRecord();
            record9.getValues().add(new StringValue(product3));
            record9.getValues().add(new StringValue(quarter3));
            record9.getValues().add(new NumericValue(salesP3Q3));

            PivotCacheRecord record10 = new PivotCacheRecord();
            record10.getValues().add(new StringValue(product3));
            record10.getValues().add(new StringValue(quarter4));
            record10.getValues().add(new NumericValue(salesP3Q4));

            PivotCacheRecord record11 = new PivotCacheRecord();
            record11.getValues().add(new StringValue(product4));
            record11.getValues().add(new StringValue(quarter3));
            record11.getValues().add(new NumericValue(salesP4Q3));

            PivotCacheRecord record12 = new PivotCacheRecord();
            record12.getValues().add(new StringValue(product4));
            record12.getValues().add(new StringValue(quarter4));
            record12.getValues().add(new NumericValue(salesP4Q4));

            PivotCacheRecord record13 = new PivotCacheRecord();
            record13.getValues().add(new StringValue(product5));
            record13.getValues().add(new StringValue(quarter1));
            record13.getValues().add(new NumericValue(salesP5Q1));

            PivotCacheRecord record14 = new PivotCacheRecord();
            record14.getValues().add(new StringValue(product5));
            record14.getValues().add(new StringValue(quarter2));
            record14.getValues().add(new NumericValue(salesP5Q2));

            PivotCacheRecord record15 = new PivotCacheRecord();
            record15.getValues().add(new StringValue(product5));
            record15.getValues().add(new StringValue(quarter3));
            record15.getValues().add(new NumericValue(salesP5Q3));

            PivotCacheRecord record16 = new PivotCacheRecord();
            record16.getValues().add(new StringValue(product5));
            record16.getValues().add(new StringValue(quarter4));
            record16.getValues().add(new NumericValue(salesP5Q4));

            pivotCache1.getRecords().add(record1);
            pivotCache1.getRecords().add(record2);
            pivotCache1.getRecords().add(record3);
            pivotCache1.getRecords().add(record4);
            pivotCache1.getRecords().add(record5);
            pivotCache1.getRecords().add(record6);
            pivotCache1.getRecords().add(record7);
            pivotCache1.getRecords().add(record8);
            pivotCache1.getRecords().add(record9);
            pivotCache1.getRecords().add(record10);
            pivotCache1.getRecords().add(record11);
            pivotCache1.getRecords().add(record12);
            pivotCache1.getRecords().add(record13);
            pivotCache1.getRecords().add(record14);
            pivotCache1.getRecords().add(record15);
            pivotCache1.getRecords().add(record16);

            pivotTable1.setPivotCache(pivotCache1);

            sheet1.getPivotTables().add(pivotTable1);

            Workbook book = new Workbook();
            book.getSheets().add(sheet1);

            book.save("c:\\test\\output.xlsx", true);
        }
        catch (Exception e)
        {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
    }
}
Independentsoft - any library, any programming language

JSpreadsheet > Tutorial > Create Pivot Table

Need help? Ask our developers:
