// Create a new workbook Workbook workbook = new Workbook(); IWorksheet ws = workbook.getWorksheets().get(0); ws.setName("UNIQUE"); ws.getRange("$B$1").setValue( "Use the UNIQUE function to return a unique list from a range or array of items. Syntax: UNIQUE(array,[by_col],[occurs_once])"); ws.getRange("$G$2:$G$3").setValue(new Object[][] { { "Unique values:" }, { "Region" } }); ws.getRange("$K$2:$K$3").setValue(new Object[][] { { "Unique values in order by SORT" }, { "Region" } }); ws.getRange("$B$3:$E$19") .setValue(new Object[][] { { "Region", "Sales Rep", "Product", "Units" }, { "East", "Tom", "Apple", 6380d }, { "West", "Fred", "Grape", 5619d }, { "North ", "Amy", "Pear", 4565d }, { "South", "Sal", "Banana", 5323d }, { "East", "Fritz", "Apple", 4394d }, { "West", "Sravan", "Grape", 7195d }, { "North ", "Xi", "Pear", 5231d }, { "South", "Hector", "Banana", 2427d }, { "East", "Tom", "Banana", 4213d }, { "West", "Fred", "Pear", 3239d }, { "North ", "Amy", "Grape", 6420d }, { "South", "Sal", "Apple", 1310d }, { "East", "Fritz", "Banana", 6274d }, { "West", "Sravan", "Pear", 4894d }, { "North ", "Xi", "Grape", 7580d }, { "South", "Hector", "Apple", 9814d } }); ws.getRange("$H$3:$J$3").setValue(new Object[][] { { "Sales Rep", "Product", "" } }); ws.getRange("$L$3:$N$3").setValue(new Object[][] { { "Sales Rep", "Product", "Units" } }); // Apply table style. ITable table = ws.getTables().add(ws.getRange("B3:E19"),true); ITable table1 = ws.getTables().add(ws.getRange("G3:I12"),true); ITable table2 = ws.getTables().add(ws.getRange("K3:N19"),true); table.convertToRange(); table1.convertToRange(); table2.convertToRange(); ws.getRange("$G$4:$I$4").setFormula2("=UNIQUE(B4:B19)"); ws.getRange("$K$4:$N$4").setFormula2("=SORT(UNIQUE(B4:B19))"); ws.getRange("$E$4:$E$19,$N$4:$N$19").setNumberFormat("#,##0"); // Save to an excel file workbook.save("UniqueFunction.xlsx");