// Create a new workbook Workbook workbook = new Workbook(); Object[][] data = { {"Order ID", "Product", "Category", "Amount", "Date", "Country"}, {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"} }; IWorksheet worksheet = workbook.getWorksheets().get(0); worksheet.setName("Source"); //Add data source of pivot table. worksheet.getRange("A1:F71").setValue(data); worksheet.getRange("E2:E71").setNumberFormat("m/d/yyyy;@"); worksheet.getRange("D2:D71").setNumberFormat("$#,##0_);($#,##0)"); IWorksheet calculatedItemSheet = workbook.getWorksheets().add(); calculatedItemSheet.setName("CalculatedItem"); // Add pivot table. IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:F71")); IPivotTable calculatedItemTable = calculatedItemSheet.getPivotTables().add(pivotCache, calculatedItemSheet.getRange("A1")); calculatedItemTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField); calculatedItemTable.getPivotFields().get("Country").setOrientation(PivotFieldOrientation.ColumnField); calculatedItemTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField); calculatedItemTable.getDataFields().get(0).setNumberFormat("$#,##0_);($#,##0)"); ICalculatedItems countryCalcItems = calculatedItemTable.getPivotFields().get("Country").getCalculatedItems(); ICalculatedItems productCalcItems = calculatedItemTable.getPivotFields().get("Product").getCalculatedItems(); // add some calculated items countryCalcItems.add("Oceania", "=Australia+NewZealand"); countryCalcItems.add("Europe", "=France+Germany"); countryCalcItems.add("America", "=Canada"); productCalcItems.add("IPhone 13", "=2500"); // hide the duplicate normal item. IPivotItems countrys = calculatedItemTable.getPivotFields().get("Country").getPivotItems(); countrys.get("United Kingdom").setVisible(false); countrys.get("United States").setVisible(false); IPivotItems products = calculatedItemTable.getPivotFields().get("Product").getPivotItems(); products.get("IPhone 13").setVisible(false); calculatedItemSheet.getRange("A:I").autoFit(); calculatedItemSheet.activate(); // Save to an excel file workbook.save("CalculatedItem.xlsx");