//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 two slicers for Category column. ISlicer slicer1 = cache.getSlicers().add(workbook.getWorksheets().get("Sheet1"), "cate1", "Category", 30, 550, 100, 200); ISlicer slicer2 = cache.getSlicers().add(workbook.getWorksheets().get("Sheet1"), "cate2", "Category", 30, 700, 100, 200); //save to an excel file workbook.save("AddSlicersForTable.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 two slicers for Category column. val slicer1 = cache.slicers.add(workbook.worksheets.get("Sheet1"), "cate1", "Category", 30.0, 550.0, 100.0, 200.0) val slicer2 = cache.slicers.add(workbook.worksheets.get("Sheet1"), "cate2", "Category", 30.0, 700.0, 100.0, 200.0) //save to an excel file workbook.save("AddSlicersForTable.xlsx")