// Create a new workbook Workbook workbook = new Workbook(); 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"}, }; IWorksheet worksheet = workbook.getWorksheets().get(0); worksheet.getRange("G1:L16").setValue(sourceData); worksheet.getRange("G:L").setColumnWidth(15); 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"); //config pivot table's fields IPivotField field_Product = pivottable.getPivotFields().get(1); field_Product.setOrientation(PivotFieldOrientation.RowField); IPivotField field_Amount = pivottable.getPivotFields().get(3); field_Amount.setOrientation(PivotFieldOrientation.DataField); field_Amount.setNumberFormat("$#,##0.00"); IPivotField field_Country = pivottable.getPivotFields().get(5); field_Country.setOrientation(PivotFieldOrientation.PageField); //row field filter. field_Product.getPivotItems().get("Bose 785593-0050").setVisible(false); field_Product.getPivotItems().get("Haier 394L 4Star").setVisible(false); field_Product.getPivotItems().get("Iphone XR").setVisible(false); //page filter. field_Country.getPivotItems().get("United States").setVisible(false); field_Country.getPivotItems().get("Canada").setVisible(false); worksheet.getRange("A:B").getEntireColumn().autoFit(); // Save to an excel file workbook.save("FilterItemsInPivotTable.xlsx");
// Create a new workbook var workbook = Workbook() val sourceData: Any = arrayOf( arrayOf("Order ID", "Product", "Category", "Amount", "Date", "Country"), arrayOf(1, "Bose 785593-0050", "Consumer Electronics", 4270, GregorianCalendar(2018, 0, 6), "United States"), arrayOf(2, "Canon EOS 1500D", "Consumer Electronics", 8239, GregorianCalendar(2018, 0, 7), "United Kingdom"), arrayOf(3, "Haier 394L 4Star", "Consumer Electronics", 617, GregorianCalendar(2018, 0, 8), "United States"), arrayOf(4, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 8384, GregorianCalendar(2018, 0, 10), "Canada"), arrayOf(5, "Mi LED 40inch", "Consumer Electronics", 2626, GregorianCalendar(2018, 0, 10), "Germany"), arrayOf(6, "Sennheiser HD 4.40-BT", "Consumer Electronics", 3610, GregorianCalendar(2018, 0, 11), "United States"), arrayOf(7, "Iphone XR", "Mobile", 9062, GregorianCalendar(2018, 0, 11), "Australia"), arrayOf(8, "OnePlus 7Pro", "Mobile", 6906, GregorianCalendar(2018, 0, 16), "New Zealand"), arrayOf(9, "Redmi 7", "Mobile", 2417, GregorianCalendar(2018, 0, 16), "France"), arrayOf(10, "Samsung S9", "Mobile", 7431, GregorianCalendar(2018, 0, 16), "Canada"), arrayOf(11, "OnePlus 7Pro", "Mobile", 8250, GregorianCalendar(2018, 0, 16), "Germany"), arrayOf(12, "Redmi 7", "Mobile", 7012, GregorianCalendar(2018, 0, 18), "United States"), arrayOf(13, "Bose 785593-0050", "Consumer Electronics", 1903, GregorianCalendar(2018, 0, 20), "Germany"), arrayOf(14, "Canon EOS 1500D", "Consumer Electronics", 2824, GregorianCalendar(2018, 0, 22), "Canada"), arrayOf(15, "Haier 394L 4Star", "Consumer Electronics", 6946, GregorianCalendar(2018, 0, 24), "France")) val worksheet = workbook.worksheets[0] worksheet.getRange("G1:L16").value = sourceData worksheet.getRange("G:L").columnWidth = 15.0 val pivotcache = workbook.pivotCaches.create(worksheet.getRange("G1:L16")) val pivottable = worksheet.pivotTables.add(pivotcache, worksheet.getRange("A1"), "pivottable1") worksheet.getRange("J1:J16").numberFormat = "$#,##0.00" //config pivot table's fields val field_Product = pivottable.pivotFields[1] field_Product.orientation = PivotFieldOrientation.RowField val field_Amount = pivottable.pivotFields[3] field_Amount.orientation = PivotFieldOrientation.DataField field_Amount.numberFormat = "$#,##0.00" val field_Country = pivottable.pivotFields[5] field_Country.orientation = PivotFieldOrientation.PageField //row field filter. field_Product.pivotItems["Bose 785593-0050"].visible = false field_Product.pivotItems["Haier 394L 4Star"].visible = false field_Product.pivotItems["Iphone XR"].visible = false //page filter. field_Country.pivotItems["United States"].visible = false field_Country.pivotItems["Canada"].visible = false worksheet.getRange("A:B").entireColumn.autoFit() // Save to an excel file workbook.save("FilterItemsInPivotTable.xlsx")