// 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 calculatedFieldSheet = workbook.getWorksheets().add(); calculatedFieldSheet.setName("CalculatedField"); // Add pivot table. IPivotCache pivotCache = workbook.getPivotCaches().create(worksheet.getRange("A1:F71")); IPivotTable calculatedFieldTable = calculatedFieldSheet.getPivotTables().add(pivotCache, calculatedFieldSheet.getRange("A1")); calculatedFieldTable.getPivotFields().get("Product").setOrientation(PivotFieldOrientation.RowField); calculatedFieldTable.getPivotFields().get("Amount").setOrientation(PivotFieldOrientation.DataField); // Add calculated field. calculatedFieldTable.getCalculatedFields().add("Tax", "=IF(Amount > 1000, 3% * Amount, 0)"); // Set calculated field as data field. calculatedFieldTable.getPivotFields().get("Tax").setOrientation(PivotFieldOrientation.DataField); calculatedFieldTable.getDataFields().get(0).setNumberFormat("$#,##0_);($#,##0)"); calculatedFieldTable.getDataFields().get(1).setNumberFormat("$#,##0_);($#,##0)"); calculatedFieldSheet.getRange("A:I").autoFit(); calculatedFieldSheet.activate(); // Save to an excel file workbook.save("CalculatedField.xlsx");
// Create a new workbook var workbook = Workbook() val data = arrayOf(arrayOf("Order ID", "Product", "Category", "Amount", "Date", "Country"), arrayOf(1, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 2300, "1/1/2020", "United Kingdom"), arrayOf(2, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 4700, "1/1/2020", "Australia"), arrayOf(3, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 2000, "1/1/2020", "NewZealand"), arrayOf(4, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 3700, "1/1/2020", "United States"), arrayOf(5, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 4000, "1/1/2020", "Germany"), arrayOf(6, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 4200, "1/1/2020", "Canada"), arrayOf(7, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 3700, "1/1/2020", "France"), arrayOf(8, "Mi LED 40inch", "Consumer Electronics", 1500, "1/1/2020", "United Kingdom"), arrayOf(9, "Mi LED 40inch", "Consumer Electronics", 2100, "1/1/2020", "Australia"), arrayOf(10, "Mi LED 40inch", "Consumer Electronics", 3100, "1/1/2020", "NewZealand"), arrayOf(11, "Mi LED 40inch", "Consumer Electronics", 3200, "1/1/2020", "United States"), arrayOf(12, "Mi LED 40inch", "Consumer Electronics", 4800, "1/1/2020", "Germany"), arrayOf(13, "Mi LED 40inch", "Consumer Electronics", 200, "1/1/2020", "Canada"), arrayOf(14, "Mi LED 40inch", "Consumer Electronics", 700, "1/1/2020", "France"), arrayOf(15, "Sennheiser HD 4.40-BT", "Consumer Electronics", 900, "1/1/2020", "United Kingdom"), arrayOf(16, "Sennheiser HD 4.40-BT", "Consumer Electronics", 3600, "1/1/2020", "Australia"), arrayOf(17, "Sennheiser HD 4.40-BT", "Consumer Electronics", 4300, "1/1/2020", "NewZealand"), arrayOf(18, "Sennheiser HD 4.40-BT", "Consumer Electronics", 4500, "1/1/2020", "United States"), arrayOf(19, "Sennheiser HD 4.40-BT", "Consumer Electronics", 3800, "1/1/2020", "Germany"), arrayOf(20, "Sennheiser HD 4.40-BT", "Consumer Electronics", 2700, "1/1/2020", "Canada"), arrayOf(21, "Sennheiser HD 4.40-BT", "Consumer Electronics", 1200, "1/1/2020", "France"), arrayOf(22, "Iphone XR", "Mobile", 4600, "1/1/2020", "United Kingdom"), arrayOf(23, "Iphone XR", "Mobile", 3900, "1/1/2020", "Australia"), arrayOf(24, "Iphone XR", "Mobile", 2100, "1/1/2020", "NewZealand"), arrayOf(25, "Iphone XR", "Mobile", 2000, "1/1/2020", "United States"), arrayOf(26, "Iphone XR", "Mobile", 4000, "1/1/2020", "Germany"), arrayOf(27, "Iphone XR", "Mobile", 2900, "1/1/2020", "Canada"), arrayOf(28, "Iphone XR", "Mobile", 2200, "1/1/2020", "France"), arrayOf(29, "Samsung S9", "Mobile", 1200, "1/1/2020", "United Kingdom"), arrayOf(30, "Samsung S9", "Mobile", 2800, "1/1/2020", "Australia"), arrayOf(31, "Samsung S9", "Mobile", 1700, "1/1/2020", "NewZealand"), arrayOf(32, "Samsung S9", "Mobile", 500, "1/1/2020", "United States"), arrayOf(33, "Samsung S9", "Mobile", 200, "1/1/2020", "Germany"), arrayOf(34, "Samsung S9", "Mobile", 3900, "1/1/2020", "Canada"), arrayOf(35, "Samsung S9", "Mobile", 4800, "1/1/2020", "France"), arrayOf(36, "OnePlus 7Pro", "Mobile", 3600, "1/1/2020", "United Kingdom"), arrayOf(37, "OnePlus 7Pro", "Mobile", 2700, "1/1/2020", "Australia"), arrayOf(38, "OnePlus 7Pro", "Mobile", 1800, "1/1/2020", "NewZealand"), arrayOf(39, "OnePlus 7Pro", "Mobile", 2200, "1/1/2020", "United States"), arrayOf(40, "OnePlus 7Pro", "Mobile", 900, "1/1/2020", "Germany"), arrayOf(41, "OnePlus 7Pro", "Mobile", 600, "1/1/2020", "Canada"), arrayOf(42, "OnePlus 7Pro", "Mobile", 2500, "1/1/2020", "France"), arrayOf(43, "Redmi 7", "Mobile", 4200, "1/1/2020", "United Kingdom"), arrayOf(44, "Redmi 7", "Mobile", 4200, "1/1/2020", "Australia"), arrayOf(45, "Redmi 7", "Mobile", 100, "1/1/2020", "NewZealand"), arrayOf(46, "Redmi 7", "Mobile", 3300, "1/1/2020", "United States"), arrayOf(47, "Redmi 7", "Mobile", 4200, "1/1/2020", "Germany"), arrayOf(48, "Redmi 7", "Mobile", 3900, "1/1/2020", "Canada"), arrayOf(49, "Redmi 7", "Mobile", 1700, "1/1/2020", "France"), arrayOf(50, "Bose 785593-0050", "Consumer Electronics", 2800, "1/1/2020", "United Kingdom"), arrayOf(51, "Bose 785593-0050", "Consumer Electronics", 200, "1/1/2020", "Australia"), arrayOf(52, "Bose 785593-0050", "Consumer Electronics", 400, "1/1/2020", "NewZealand"), arrayOf(53, "Bose 785593-0050", "Consumer Electronics", 4500, "1/1/2020", "United States"), arrayOf(54, "Bose 785593-0050", "Consumer Electronics", 3400, "1/1/2020", "Germany"), arrayOf(55, "Bose 785593-0050", "Consumer Electronics", 200, "1/1/2020", "Canada"), arrayOf(56, "Bose 785593-0050", "Consumer Electronics", 2700, "1/1/2020", "France"), arrayOf(57, "Canon EOS 1500D", "Consumer Electronics", 800, "1/1/2020", "United Kingdom"), arrayOf(58, "Canon EOS 1500D", "Consumer Electronics", 4800, "1/1/2020", "Australia"), arrayOf(59, "Canon EOS 1500D", "Consumer Electronics", 1700, "1/1/2020", "NewZealand"), arrayOf(60, "Canon EOS 1500D", "Consumer Electronics", 2500, "1/1/2020", "United States"), arrayOf(61, "Canon EOS 1500D", "Consumer Electronics", 1300, "1/1/2020", "Germany"), arrayOf(62, "Canon EOS 1500D", "Consumer Electronics", 1500, "1/1/2020", "Canada"), arrayOf(63, "Canon EOS 1500D", "Consumer Electronics", 200, "1/1/2020", "France"), arrayOf(64, "Haier 394L 4Star", "Consumer Electronics", 4300, "1/1/2020", "United Kingdom"), arrayOf(65, "Haier 394L 4Star", "Consumer Electronics", 1300, "1/1/2020", "Australia"), arrayOf(66, "Haier 394L 4Star", "Consumer Electronics", 2600, "1/1/2020", "NewZealand"), arrayOf(67, "Haier 394L 4Star", "Consumer Electronics", 4600, "1/1/2020", "United States"), arrayOf(68, "Haier 394L 4Star", "Consumer Electronics", 1200, "1/1/2020", "Germany"), arrayOf(69, "Haier 394L 4Star", "Consumer Electronics", 800, "1/1/2020", "Canada"), arrayOf(70, "Haier 394L 4Star", "Consumer Electronics", 2200, "1/1/2020", "France")) val worksheet = workbook.worksheets[0] worksheet.name = "Source" // Add data source of pivot table. worksheet.getRange("A1:F71").value = data worksheet.getRange("E2:E71").numberFormat = "m/d/yyyy;@" worksheet.getRange("D2:D71").numberFormat = "$#,##0_);($#,##0)" val calculatedFieldSheet = workbook.worksheets.add() calculatedFieldSheet.name = "CalculatedField" // Add pivot table. val pivotCache = workbook.pivotCaches.create(worksheet.getRange("A1:F71")) val calculatedFieldTable = calculatedFieldSheet.pivotTables.add(pivotCache, calculatedFieldSheet.getRange("A1")) calculatedFieldTable.pivotFields["Product"].orientation = PivotFieldOrientation.RowField calculatedFieldTable.pivotFields["Amount"].orientation = PivotFieldOrientation.DataField // Add calculated field. calculatedFieldTable.getCalculatedFields().add("Tax", "=IF(Amount > 1000, 3% * Amount, 0)") // Set calculated field as data field. calculatedFieldTable.pivotFields["Tax"].orientation = PivotFieldOrientation.DataField calculatedFieldTable.getDataFields().get(0).setNumberFormat("$#,##0_);($#,##0)") calculatedFieldTable.getDataFields().get(1).setNumberFormat("$#,##0_);($#,##0)") calculatedFieldSheet.getRange("A:I").autoFit() calculatedFieldSheet.activate() // Save to an excel file workbook.save("CalculatedField.xlsx")