// Create a new workbook Workbook workbook = new Workbook(); Object sourceData = new Object[][]{ {"Order ID", "Product", "Category", "Amount", "Date", "Country"}, {1, "Carrots", "Vegetables", 4270, new GregorianCalendar(2018, 0, 6), "United States"}, {2, "Broccoli", "Vegetables", 8239, new GregorianCalendar(2018, 0, 7), "United Kingdom"}, {3, "Banana", "Fruit", 617, new GregorianCalendar(2018, 0, 8), "United States"}, {4, "Banana", "Fruit", 8384, new GregorianCalendar(2018, 0, 10), "Canada"}, {5, "Beans", "Vegetables", 2626, new GregorianCalendar(2018, 0, 10), "Germany"}, {6, "Orange", "Fruit", 3610, new GregorianCalendar(2018, 0, 11), "United States"}, {7, "Broccoli", "Vegetables", 9062, new GregorianCalendar(2018, 0, 11), "Australia"}, {8, "Banana", "Fruit", 6906, new GregorianCalendar(2018, 0, 16), "New Zealand"}, {9, "Apple", "Fruit", 2417, new GregorianCalendar(2018, 0, 16), "France"}, {10, "Apple", "Fruit", 7431, new GregorianCalendar(2018, 0, 16), "Canada"}, {11, "Banana", "Fruit", 8250, new GregorianCalendar(2018, 0, 16), "Germany"}, {12, "Broccoli", "Vegetables", 7012, new GregorianCalendar(2018, 0, 18), "United States"}, {13, "Carrots", "Vegetables", 1903, new GregorianCalendar(2018, 0, 20), "Germany"}, {14, "Broccoli", "Vegetables", 2824, new GregorianCalendar(2018, 0, 22), "Canada"}, {15, "Apple", "Fruit", 6946, new GregorianCalendar(2018, 0, 24), "France"}, }; IWorksheet worksheet = workbook.getWorksheets().get(0); worksheet.getRange("A:F").setColumnWidth(15); worksheet.getRange("A1:F16").setValue(sourceData); ITable table = worksheet.getTables().add(worksheet.getRange("A1:F16"), true); table.getColumns().get(3).getDataBodyRange().setNumberFormat("$#,##0.00"); //Create slicer cache for table. ISlicerCache cache = workbook.getSlicerCaches().add(table, "Category", "categoryCache"); //Add slicer ISlicer slicer1 = cache.getSlicers().add(workbook.getWorksheets().get("Sheet1"), "cate1", "Category", 30, 550, 100, 200); //do filter operation. filter out vegetables. slicer1.getSlicerCache().getSlicerItems().get("Vegetables").setSelected(false); //clear slicer filter. slicer1.getSlicerCache().clearAllFilters(); // Save to an excel file workbook.save("UseSlicerToClearFilter.xlsx");
// Create a new workbook var workbook = Workbook() val sourceData = arrayOf(arrayOf("Order ID", "Product", "Category", "Amount", "Date", "Country"), arrayOf(1, "Carrots", "Vegetables", 4270, GregorianCalendar(2018, 0, 6), "United States"), arrayOf(2, "Broccoli", "Vegetables", 8239, GregorianCalendar(2018, 0, 7), "United Kingdom"), arrayOf(3, "Banana", "Fruit", 617, GregorianCalendar(2018, 0, 8), "United States"), arrayOf(4, "Banana", "Fruit", 8384, GregorianCalendar(2018, 0, 10), "Canada"), arrayOf(5, "Beans", "Vegetables", 2626, GregorianCalendar(2018, 0, 10), "Germany"), arrayOf(6, "Orange", "Fruit", 3610, GregorianCalendar(2018, 0, 11), "United States"), arrayOf(7, "Broccoli", "Vegetables", 9062, GregorianCalendar(2018, 0, 11), "Australia"), arrayOf(8, "Banana", "Fruit", 6906, GregorianCalendar(2018, 0, 16), "New Zealand"), arrayOf(9, "Apple", "Fruit", 2417, GregorianCalendar(2018, 0, 16), "France"), arrayOf(10, "Apple", "Fruit", 7431, GregorianCalendar(2018, 0, 16), "Canada"), arrayOf(11, "Banana", "Fruit", 8250, GregorianCalendar(2018, 0, 16), "Germany"), arrayOf(12, "Broccoli", "Vegetables", 7012, GregorianCalendar(2018, 0, 18), "United States"), arrayOf(13, "Carrots", "Vegetables", 1903, GregorianCalendar(2018, 0, 20), "Germany"), arrayOf(14, "Broccoli", "Vegetables", 2824, GregorianCalendar(2018, 0, 22), "Canada"), arrayOf(15, "Apple", "Fruit", 6946, GregorianCalendar(2018, 0, 24), "France")) val worksheet = workbook.worksheets.get(0) worksheet.getRange("A:F").columnWidth = 15.0 worksheet.getRange("A1:F16").value = sourceData val table = worksheet.tables.add(worksheet.getRange("A1:F16"), true) table.columns.get(3).dataBodyRange.numberFormat = "$#,##0.00" //Create slicer cache for table. val cache = workbook.slicerCaches.add(table, "Category", "categoryCache") //Add slicer val slicer1 = cache.slicers.add(workbook.worksheets.get("Sheet1"), "cate1", "Category", 30.0, 550.0, 100.0, 200.0) //do filter operation. filter out vegetables. slicer1.slicerCache.slicerItems.get("Vegetables").selected = false //clear slicer filter. slicer1.slicerCache.clearAllFilters() // Save to an excel file workbook.save("UseSlicerToClearFilter.xlsx")