//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); var ws = workbook.Worksheets[0]; ws.Name = "UNIQUE"; ws.Range["$B$1"].Value = "Use the UNIQUE function to return a unique list from a range or array of items. Syntax: UNIQUE(array,[by_col],[occurs_once])"; ws.Range["$G$2:$G$3"].Value = new object[,] { { "Unique values:"}, { "Region"} }; ws.Range["$K$2:$K$3"].Value = new object[,] { { "Unique values in order by SORT"}, { "Region"} }; ws.Range["$B$3:$E$19"].Value = 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.Range["$H$3:$J$3"].Value = new object[,] { { "Sales Rep", "Product", ""} }; ws.Range["$L$3:$N$3"].Value = new object[,] { { "Sales Rep", "Product", "Units"} }; // Apply table style ITable table = ws.Tables.Add(ws.Range["B3:E19"], true); ITable table1 = ws.Tables.Add(ws.Range["G3:I12"], true); ITable table2 = ws.Tables.Add(ws.Range["K3:N19"], true); table.ConvertToRange(); table1.ConvertToRange(); table2.ConvertToRange(); ws.Range["$G$4:$I$4"].Formula2 = "=UNIQUE(B4:B19)"; ws.Range["$K$4:$N$4"].Formula2 = "=SORT(UNIQUE(B4:B19))"; ws.Range["$E$4:$E$19,$N$4:$N$19"].NumberFormat = "#,##0"; // Save to an excel file workbook.Save("UniqueFunction.xlsx");
' Create a new Workbook Dim workbook As New Workbook Dim ws = workbook.Worksheets(0) ws.Name = "UNIQUE" ws.Range!B1.Value = "Use the UNIQUE function to return a unique list from a range or array of items. Syntax: UNIQUE(array,[by_col],[occurs_once])" ws.Range("$G$2:$G$3").Value = New Object(,) { {"Unique values:"}, {"Region"} } ws.Range("$K$2:$K$3").Value = New Object(,) { {"Unique values in order by SORT"}, {"Region"} } ws.Range("$B$3:$E$19").Value = New Object(,) { {"Region", "Sales Rep", "Product", "Units"}, {"East", "Tom", "Apple", 6380.0R}, {"West", "Fred", "Grape", 5619.0R}, {"North ", "Amy", "Pear", 4565.0R}, {"South", "Sal", "Banana", 5323.0R}, {"East", "Fritz", "Apple", 4394.0R}, {"West", "Sravan", "Grape", 7195.0R}, {"North ", "Xi", "Pear", 5231.0R}, {"South", "Hector", "Banana", 2427.0R}, {"East", "Tom", "Banana", 4213.0R}, {"West", "Fred", "Pear", 3239.0R}, {"North ", "Amy", "Grape", 6420.0R}, {"South", "Sal", "Apple", 1310.0R}, {"East", "Fritz", "Banana", 6274.0R}, {"West", "Sravan", "Pear", 4894.0R}, {"North ", "Xi", "Grape", 7580.0R}, {"South", "Hector", "Apple", 9814.0R} } ws.Range("$H$3:$J$3").Value = New Object(,) { {"Sales Rep", "Product", ""} } ws.Range("$L$3:$N$3").Value = New Object(,) { {"Sales Rep", "Product", "Units"} } ' Apply table style Dim table As ITable = ws.Tables.Add(ws.Range("B3:E19"), True) Dim table1 As ITable = ws.Tables.Add(ws.Range("G3:I12"), True) Dim table2 As ITable = ws.Tables.Add(ws.Range("K3:N19"), True) table.ConvertToRange() table1.ConvertToRange() table2.ConvertToRange() ws.Range("$G$4:$I$4").Formula2 = "=UNIQUE(B4:B19)" ws.Range("$K$4:$N$4").Formula2 = "=SORT(UNIQUE(B4:B19))" ws.Range("$E$4:$E$19,$N$4:$N$19").NumberFormat = "#,##0" ' save to an excel file workbook.Save("UniqueFunction.xlsx")