// Create a new workbook Workbook workbook = new Workbook(); IWorksheet sheet = workbook.getWorksheets().get(0); sheet.setName("FILTER"); sheet.getRange("A1").setValue( "The FILTER function filters a range or array based on criteria you specify. Syntax: FILTER(array,include,[if_empty])"); sheet.getRange("B3:E19").setValue(new Object[][] { { "Region", "Sales Rep", "Product", "Units" }, { "East", "Tom", "Apple", 6380 }, { "West", "Fred", "Grape", 5619 }, { "North ", "Amy", "Pear", 4565 }, { "South", "Sal", "Banana", 5323 }, { "East", "Fritz", "Apple", 4394 }, { "West", "Sravan", "Grape", 7195 }, { "North ", "Xi", "Pear", 5231 }, { "South", "Hector", "Banana", 2427 }, { "East", "Tom", "Banana", 4213 }, { "West", "Fred", "Pear", 3239 }, { "North ", "Amy", "Grape", 6420 }, { "South", "Sal", "Apple", 1310 }, { "East", "Fritz", "Banana", 6274 }, { "West", "Sravan", "Pear", 4894 }, { "North ", "Xi", "Grape", 7580 }, { "South", "Hector", "Apple", 9814 } }); sheet.getRange("G3:L4").setValue(new Object[][] { { "Criterion", "", "Product", "Units", "", "Total:" }, { 5000, null, null, null, null, null } }); // Apply table style. ITable table = sheet.getTables().add(sheet.getRange("B3:E19"),true); ITable table1 = sheet.getTables().add(sheet.getRange("I3:J12"),true); table.convertToRange(); table1.convertToRange(); // Filter the data in the range B4:E19 where the Units(E4:E19) are greater than 5000(G4) sheet.getRange("I4").setFormula2("=FILTER(D4:E19,E4:E19>G4,\"\")"); sheet.getRange("L4").setFormula2("=SUM(IF(E4:E19>G4,1,0))"); sheet.getRange("E4:E19,G4,J4:J12").setNumberFormat("#,##0"); // Save to an excel file workbook.save("FilterFunction.xlsx");
// Create a new workbook var workbook = Workbook() val sheet: IWorksheet = workbook.getWorksheets().get(0) sheet.setName("FILTER") sheet.getRange("A1").setValue( "The FILTER function filters a range or array based on criteria you specify. Syntax: FILTER(array,include,[if_empty])") sheet.getRange("B3:E19").setValue(arrayOf(arrayOf("Region", "Sales Rep", "Product", "Units"), arrayOf("East", "Tom", "Apple", 6380), arrayOf("West", "Fred", "Grape", 5619), arrayOf("North ", "Amy", "Pear", 4565), arrayOf("South", "Sal", "Banana", 5323), arrayOf("East", "Fritz", "Apple", 4394), arrayOf("West", "Sravan", "Grape", 7195), arrayOf("North ", "Xi", "Pear", 5231), arrayOf("South", "Hector", "Banana", 2427), arrayOf("East", "Tom", "Banana", 4213), arrayOf("West", "Fred", "Pear", 3239), arrayOf("North ", "Amy", "Grape", 6420), arrayOf("South", "Sal", "Apple", 1310), arrayOf("East", "Fritz", "Banana", 6274), arrayOf("West", "Sravan", "Pear", 4894), arrayOf("North ", "Xi", "Grape", 7580), arrayOf("South", "Hector", "Apple", 9814))) sheet.getRange("G3:L4").setValue(arrayOf(arrayOf("Criterion", "", "Product", "Units", "", "Total:"), arrayOf(5000, null, null, null, null, null))) // Apply table style. val table: ITable = sheet.getTables().add(sheet.getRange("B3:E19"), true) val table1: ITable = sheet.getTables().add(sheet.getRange("I3:J12"), true) table.convertToRange() table1.convertToRange() // Filter the data in the range B4:E19 where the Units(E4:E19) are greater than 5000(G4) sheet.getRange("I4").setFormula2("=FILTER(D4:E19,E4:E19>G4,\"\")") sheet.getRange("L4").setFormula2("=SUM(IF(E4:E19>G4,1,0))") sheet.getRange("E4:E19,G4,J4:J12").setNumberFormat("#,##0") // Save to an excel file workbook.save("FilterFunction.xlsx")