// Create a new workbook Workbook workbook = new Workbook(); Object[][] data = { {1, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 2300, "1/1/2020", "United Kingdom"}, {2, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 4700, "1/1/2020", "Australia"}, {3, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 2000, "1/1/2020", "NewZealand"}, {4, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 3700, "1/1/2020", "United States"}, {5, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 4000, "1/1/2020", "Germany"}, {6, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 4200, "1/1/2020", "Canada"}, {7, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 3700, "1/1/2020", "France"}, {8, "Mi LED 40inch", "Consumer Electronics", 1500, "1/1/2020", "United Kingdom"}, {9, "Mi LED 40inch", "Consumer Electronics", 2100, "1/1/2020", "Australia"}, {10, "Mi LED 40inch", "Consumer Electronics", 3100, "1/1/2020", "NewZealand"}, {11, "Mi LED 40inch", "Consumer Electronics", 3200, "1/1/2020", "United States"}, {12, "Mi LED 40inch", "Consumer Electronics", 4800, "1/1/2020", "Germany"}, {13, "Mi LED 40inch", "Consumer Electronics", 200, "1/1/2020", "Canada"}, {14, "Mi LED 40inch", "Consumer Electronics", 700, "1/1/2020", "France"}, {15, "Sennheiser HD 4.40-BT", "Consumer Electronics", 900, "1/1/2020", "United Kingdom"}, {16, "Sennheiser HD 4.40-BT", "Consumer Electronics", 3600, "1/1/2020", "Australia"}, {17, "Sennheiser HD 4.40-BT", "Consumer Electronics", 4300, "1/1/2020", "NewZealand"}, {18, "Sennheiser HD 4.40-BT", "Consumer Electronics", 4500, "1/1/2020", "United States"}, {19, "Sennheiser HD 4.40-BT", "Consumer Electronics", 3800, "1/1/2020", "Germany"}, {20, "Sennheiser HD 4.40-BT", "Consumer Electronics", 2700, "1/1/2020", "Canada"}, {21, "Sennheiser HD 4.40-BT", "Consumer Electronics", 1200, "1/1/2020", "France"}, {22, "Iphone XR", "Mobile", 4600, "1/1/2020", "United Kingdom"}, {23, "Iphone XR", "Mobile", 3900, "1/1/2020", "Australia"}, {24, "Iphone XR", "Mobile", 2100, "1/1/2020", "NewZealand"}, {25, "Iphone XR", "Mobile", 2000, "1/1/2020", "United States"}, {26, "Iphone XR", "Mobile", 4000, "1/1/2020", "Germany"}, {27, "Iphone XR", "Mobile", 2900, "1/1/2020", "Canada"}, {28, "Iphone XR", "Mobile", 2200, "1/1/2020", "France"}, {29, "Samsung S9", "Mobile", 1200, "1/1/2020", "United Kingdom"}, {30, "Samsung S9", "Mobile", 2800, "1/1/2020", "Australia"}, {31, "Samsung S9", "Mobile", 1700, "1/1/2020", "NewZealand"}, {32, "Samsung S9", "Mobile", 500, "1/1/2020", "United States"}, {33, "Samsung S9", "Mobile", 200, "1/1/2020", "Germany"}, {34, "Samsung S9", "Mobile", 3900, "1/1/2020", "Canada"}, {35, "Samsung S9", "Mobile", 4800, "1/1/2020", "France"}, {36, "OnePlus 7Pro", "Mobile", 3600, "1/1/2020", "United Kingdom"}, {37, "OnePlus 7Pro", "Mobile", 2700, "1/1/2020", "Australia"}, {38, "OnePlus 7Pro", "Mobile", 1800, "1/1/2020", "NewZealand"}, {39, "OnePlus 7Pro", "Mobile", 2200, "1/1/2020", "United States"}, {40, "OnePlus 7Pro", "Mobile", 900, "1/1/2020", "Germany"}, {41, "OnePlus 7Pro", "Mobile", 600, "1/1/2020", "Canada"}, {42, "OnePlus 7Pro", "Mobile", 2500, "1/1/2020", "France"}, {43, "Redmi 7", "Mobile", 4200, "1/1/2020", "United Kingdom"}, {44, "Redmi 7", "Mobile", 4200, "1/1/2020", "Australia"}, {45, "Redmi 7", "Mobile", 100, "1/1/2020", "NewZealand"}, {46, "Redmi 7", "Mobile", 3300, "1/1/2020", "United States"}, {47, "Redmi 7", "Mobile", 4200, "1/1/2020", "Germany"}, {48, "Redmi 7", "Mobile", 3900, "1/1/2020", "Canada"}, {49, "Redmi 7", "Mobile", 1700, "1/1/2020", "France"}, {50, "Bose 785593-0050", "Consumer Electronics", 2800, "1/1/2020", "United Kingdom"}, {51, "Bose 785593-0050", "Consumer Electronics", 200, "1/1/2020", "Australia"}, {52, "Bose 785593-0050", "Consumer Electronics", 400, "1/1/2020", "NewZealand"}, {53, "Bose 785593-0050", "Consumer Electronics", 4500, "1/1/2020", "United States"}, {54, "Bose 785593-0050", "Consumer Electronics", 3400, "1/1/2020", "Germany"}, {55, "Bose 785593-0050", "Consumer Electronics", 200, "1/1/2020", "Canada"}, {56, "Bose 785593-0050", "Consumer Electronics", 2700, "1/1/2020", "France"}, {57, "Canon EOS 1500D", "Consumer Electronics", 800, "1/1/2020", "United Kingdom"}, {58, "Canon EOS 1500D", "Consumer Electronics", 4800, "1/1/2020", "Australia"}, {59, "Canon EOS 1500D", "Consumer Electronics", 1700, "1/1/2020", "NewZealand"}, {60, "Canon EOS 1500D", "Consumer Electronics", 2500, "1/1/2020", "United States"}, {61, "Canon EOS 1500D", "Consumer Electronics", 1300, "1/1/2020", "Germany"}, {62, "Canon EOS 1500D", "Consumer Electronics", 1500, "1/1/2020", "Canada"}, {63, "Canon EOS 1500D", "Consumer Electronics", 200, "1/1/2020", "France"}, {64, "Haier 394L 4Star", "Consumer Electronics", 4300, "1/1/2020", "United Kingdom"}, {65, "Haier 394L 4Star", "Consumer Electronics", 1300, "1/1/2020", "Australia"}, {66, "Haier 394L 4Star", "Consumer Electronics", 2600, "1/1/2020", "NewZealand"}, {67, "Haier 394L 4Star", "Consumer Electronics", 4600, "1/1/2020", "United States"}, {68, "Haier 394L 4Star", "Consumer Electronics", 1200, "1/1/2020", "Germany"}, {69, "Haier 394L 4Star", "Consumer Electronics", 800, "1/1/2020", "Canada"}, {70, "Haier 394L 4Star", "Consumer Electronics", 2200, "1/1/2020", "France"} }; // add data source IWorksheet worksheet = workbook.getWorksheets().get(0); worksheet.setName("Source"); worksheet.getRange("A1:F1").setValue(new String[]{"Order ID", "Product", "Category", "Amount", "Date", "Country"}); IRange dataRange = worksheet.getRange("A2:F71"); dataRange.setValue(data); worksheet.getRange("E2:E71").setNumberFormat("m/d/yyyy;@"); // add worksheets for pivot table. IWorksheet general = workbook.getWorksheets().add(); general.setName("General"); IWorksheet grandTotalSheet = workbook.getWorksheets().add(); grandTotalSheet.setName("Percent Of Grand Total"); IWorksheet differentSheet = workbook.getWorksheets().add(); differentSheet.setName("Different From"); IWorksheet percentOfSheet = workbook.getWorksheets().add(); percentOfSheet.setName("Percent Of"); IWorksheet percentOfParentColumnSheet = workbook.getWorksheets().add(); percentOfParentColumnSheet.setName("Percent Of Parent Column"); IWorksheet rankSheet = workbook.getWorksheets().add(); rankSheet.setName("Rank Smallest To Largest"); // add pivot cache. IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:F71")); // normal pivot table. IPivotTable normalTable = general.getPivotTables().add(pivotCache, general.getRange("A1")); normalTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField); normalTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField); normalTable.getPivotFields().get("Country").setOrientation(PivotFieldOrientation.ColumnField); normalTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField); general.getRange("A:I").autoFit(); // percent of grand total. IPivotTable grandTotalTable = grandTotalSheet.getPivotTables().add(pivotCache, grandTotalSheet.getRange("A1")); grandTotalTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField); grandTotalTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField); grandTotalTable.getPivotFields().get("Country").setOrientation(PivotFieldOrientation.ColumnField); grandTotalTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField); // set show value as. IPivotField dataField = grandTotalTable.getDataFields().get(0); dataField.setCalculation(PivotFieldCalculation.PercentOfTotal); grandTotalSheet.getRange("A:I").autoFit(); // different from australia. IPivotTable differentTable = differentSheet.getPivotTables().add(pivotCache, differentSheet.getRange("A1")); differentTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField); differentTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField); differentTable.getPivotFields().get("Country").setOrientation(PivotFieldOrientation.ColumnField); differentTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField); // set show value as, base field, base item. IPivotField differentField = differentTable.getDataFields().get(0); differentField.setCalculation(PivotFieldCalculation.DifferenceFrom); differentField.setBaseField("Country"); differentField.setBaseItem("Australia"); percentOfSheet.getRange("A:I").autoFit(); // percent of australia. IPivotTable percentOfTable = percentOfSheet.getPivotTables().add(pivotCache, percentOfSheet.getRange("A1")); percentOfTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField); percentOfTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField); percentOfTable.getPivotFields().get("Country").setOrientation(PivotFieldOrientation.ColumnField); percentOfTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField); // set show value as, base field, base item. IPivotField percentOfTableDataField = percentOfTable.getDataFields().get(0); percentOfTableDataField.setCalculation(PivotFieldCalculation.PercentOf); percentOfTableDataField.setBaseField("Country"); percentOfTableDataField.setBaseItem("Australia"); percentOfSheet.getRange("A:I").autoFit(); // percent of parent column. IPivotTable percentOfParentColumnTable = percentOfParentColumnSheet.getPivotTables().add(pivotCache, percentOfParentColumnSheet.getRange("A1")); percentOfParentColumnTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField); percentOfParentColumnTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField); percentOfParentColumnTable.getPivotFields().get("Country").setOrientation(PivotFieldOrientation.ColumnField); percentOfParentColumnTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField); // set show value as. IPivotField parentField = percentOfParentColumnTable.getDataFields().get(0); parentField.setCalculation(PivotFieldCalculation.PercentOfParentColumn); percentOfParentColumnSheet.getRange("A:I").autoFit(); // rank smallest to largest. IPivotTable rankTable = rankSheet.getPivotTables().add(pivotCache, rankSheet.getRange("A1")); rankTable.getPivotFields().get("Category").setOrientation(PivotFieldOrientation.RowField); rankTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField); rankTable.getPivotFields().get("Country").setOrientation(PivotFieldOrientation.ColumnField); rankTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField); IPivotField rankField = rankTable.getDataFields().get(0); rankField.setCalculation(PivotFieldCalculation.RankAscending); rankField.setBaseField("Country"); rankSheet.getRange("A:I").autoFit(); general.activate(); // Save to an excel file workbook.save("ShowValueAs.xlsx");