//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); var ws = workbook.Worksheets[0]; ws.Name = "SORT"; ws.Range["$B$1"].Value = "Use the SORT function to sort a range or array in ascending or descending order. Syntax: SORT(array,[sort_index],[sort_order],[by_col])"; ws.Range["$E$2:$E$3"].Value = new object[,] { { "Sort data in descending order: SORT(C4:C13,1,-1)"}, { "Units"} }; ws.Range["$C$3:$C$13"].Value = new object[,] { { "Units"}, { 622d}, { 961d}, { 691d}, { 445d}, { 378d}, { 483d}, { 650d}, { 783d}, { 142d}, { 404d} }; ws.Range["$H$15:$H$16"].Value = new object[,] { { "Sort range C17:F32 by Units: SORT(C17:F32,4,1,FALSE)"}, { "Region"} }; ws.Range["$C$16:$F$32"].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} }; // Apply table style. ITable table = ws.Tables.Add(ws.Range["C3:C13"], true); ITable table1 = ws.Tables.Add(ws.Range["E3:E13"], true); ITable table2 = ws.Tables.Add(ws.Range["C16:F32"], true); ITable table3 = ws.Tables.Add(ws.Range["H16:K32"], true); table.ConvertToRange(); table1.ConvertToRange(); table2.ConvertToRange(); table3.ConvertToRange(); ws.Range["$I$16:$K$16"].Value = new object[,] { { "Sales Rep", "Product", "Units"} }; ws.Range["$E$4"].Formula2 = "=SORT(C4:C13,1,-1)"; ws.Range["$H$17"].Formula2 = "=SORT(C17:F32,4,1,FALSE)"; ws.Range["$F$17:$F$32,$K$17:$K$32"].NumberFormat = "#,##0"; // Save to an excel file workbook.Save("SortFunction.xlsx");
' Create a new Workbook Dim workbook As New Workbook Dim ws = workbook.Worksheets(0) ws.Name = "SORT" ws.Range!B1.Value = "Use the SORT function to sort a range or array in ascending or descending order. Syntax: SORT(array,[sort_index],[sort_order],[by_col])" ws.Range("$E$2:$E$3").Value = New Object(,) { {"Sort data in descending order: SORT(C4:C13,1,-1)"}, {"Units"} } ws.Range("$C$3:$C$13").Value = New Object(,) { {"Units"}, {622.0R}, {961.0R}, {691.0R}, {445.0R}, {378.0R}, {483.0R}, {650.0R}, {783.0R}, {142.0R}, {404.0R} } ws.Range("$H$15:$H$16").Value = New Object(,) { {"Sort range C17:F32 by Units: SORT(C17:F32,4,1,FALSE)"}, {"Region"} } ws.Range("$C$16:$F$32").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("$I$16:$K$16").Value = New Object(,) { {"Sales Rep", "Product", "Units"} } ' Apply table style. Dim table As ITable = ws.Tables.Add(ws.Range("C3:C13"), True) Dim table1 As ITable = ws.Tables.Add(ws.Range("E3:E13"), True) Dim table2 As ITable = ws.Tables.Add(ws.Range("C16:F32"), True) Dim table3 As ITable = ws.Tables.Add(ws.Range("H16:K32"), True) table.ConvertToRange() table1.ConvertToRange() table2.ConvertToRange() table3.ConvertToRange() ws.Range!E4.Formula2 = "=SORT(C4:C13,1,-1)" ws.Range!H17.Formula2 = "=SORT(C17:F32,4,1,FALSE)" ws.Range("$F$17:$F$32,$K$17:$K$32").NumberFormat = "#,##0" ' save to an excel file workbook.Save("SortFunction.xlsx")