//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); var ws = workbook.Worksheets[0]; ws.Name = "RANDARRAY"; ws.Range["$C$1"].Value = "The RANDARRAY function allows you to generate a list of random numbers from 0 to 1 in an array. Syntax: RANDARRAY([rows],[columns])"; ws.Range["$C$3"].Value = "SEQUENCE(1, 5)"; ws.Range["$H$6:$H$7"].Value = new object[,] { { " <--- DATE(YEAR(TODAY()), C4:G4 , 1) with mmmm"}, { " <--- RANDARRAY(4,5) with 0.000000"} }; ws.Range["$H$13"].Value = " <--- INT(RANDARRAY(4,5)*100)"; ws.Range["$D$18"].Value = "Random order: SORTBY(C20:C29,RANDARRAY(COUNTA(C20:C29)))"; ws.Range["$J$18:$J$19"].Value = new object[,] { { "Sort range by birthday column: SORTBY(G20:H27,H20:H27)"}, { "Name"} }; ws.Range["$C$19"].Value = "Units"; ws.Range["$E$19"].Value = "SortBy"; ws.Range["$G$19:$G$27"].Value = new object[,] { { "Name"}, { "Fritz"}, { "Xi"}, { "Amy"}, { "Sravan"}, { "Tom"}, { "Fred"}, { "Hector"}, { "Sal"} }; ws.Range["$H$19:$I$19"].Value = new object[,] { { "Birthday", ""} }; // Apply table style ITable table = ws.Tables.Add(ws.Range["C6:G10"], true); ITable table1 = ws.Tables.Add(ws.Range["C12:G16"], true); table1.ShowHeaders = false; ITable table2 = ws.Tables.Add(ws.Range["C19:C29"], true); ITable table3 = ws.Tables.Add(ws.Range["E19:E29"], true); ITable table4 = ws.Tables.Add(ws.Range["G19:H27"], true); ITable table5 = ws.Tables.Add(ws.Range["J19:K27"], true); table.ConvertToRange(); table1.ConvertToRange(); table2.ConvertToRange(); table3.ConvertToRange(); table4.ConvertToRange(); table5.ConvertToRange(); ws.Range["C6:G6"].ClearContents(); ws.Range["$K$19"].Value = "Birthday"; ws.Range["$C$4"].Formula2 = "=SEQUENCE(1,5)"; ws.Range["$C$6"].Formula2 = "=DATE(YEAR(TODAY()),C4:G4,1)"; ws.Range["$C$7"].Formula2 = "=RANDARRAY(4,5)"; ws.Range["$C$13"].Formula2 = "=INT(RANDARRAY(4,5)*100)"; ws.Range["$C$20"].Formula2 = "=SEQUENCE(10)"; ws.Range["$E$20"].Formula2 = "=SORTBY(C20:C29,RANDARRAY(COUNTA(C20:C29)))"; ws.Range["$H$20"].Formula2 = "=RANDARRAY(COUNTA(C20:C27))*40000"; ws.Range["$J$20"].Formula2 = "=SORTBY(G20:H27,H20:H27)"; ws.Range["$C$6:$G$6"].NumberFormat = "mmmm"; ws.Range["$C$7:$G$10"].NumberFormat = "0.000000"; ws.Range["$H$20:$H$27,$K$20:$K$27"].NumberFormat = "mm/dd/yyyy"; ws.Range["C:G"].EntireColumn.ColumnWidth *= 1.5; ws.Range["H:H"].EntireColumn.ColumnWidth *= 1.5; ws.Range["K:K"].EntireColumn.ColumnWidth *= 1.5; // Save to an excel file workbook.Save("RandArrayFunction.xlsx");
' Create a new Workbook Dim workbook As New Workbook Dim ws = workbook.Worksheets(0) ws.Name = "RANDARRAY" With ws.Range !C1.Value = "The RANDARRAY function allows you to generate a list of random numbers from 0 to 1 in an array. Syntax: RANDARRAY([rows], [columns])" !C3.Value = "SEQUENCE(1, 5)" .Item("$H$6:$H$7").Value = New Object(,) { {" <--- DATE(YEAR(TODAY()), C4:G4 , 1) with mmmm"}, {" <--- RANDARRAY(4,5) with 0.000000"} } !H13.Value = " <--- INT(RANDARRAY(4,5)*100)" !D18.Value = "Random order: SORTBY(C20:C29,RANDARRAY(COUNTA(C20:C29)))" .Item("$J$18:$J$19").Value = New Object(,) { {"Sort range by birthday column: SORTBY(G20:H27,H20:H27)"}, {"Name"} } !C19.Value = "Units" !E19.Value = "SortBy" .Item("$G$19:$G$27").Value = New Object(,) { {"Name"}, {"Fritz"}, {"Xi"}, {"Amy"}, {"Sravan"}, {"Tom"}, {"Fred"}, {"Hector"}, {"Sal"} } .Item("$H$19:$I$19").Value = New Object(,) { {"Birthday", ""} } ' Apply table style Dim table As ITable = ws.Tables.Add(ws.Range("C6:G10"), True) Dim table1 As ITable = ws.Tables.Add(ws.Range("C12:G16"), True) table1.ShowHeaders = False Dim table2 As ITable = ws.Tables.Add(ws.Range("C19:C29"), True) Dim table3 As ITable = ws.Tables.Add(ws.Range("E19:E29"), True) Dim table4 As ITable = ws.Tables.Add(ws.Range("G19:H27"), True) Dim table5 As ITable = ws.Tables.Add(ws.Range("J19:K27"), True) table.ConvertToRange() table1.ConvertToRange() table2.ConvertToRange() table3.ConvertToRange() table4.ConvertToRange() table5.ConvertToRange() ws.Range("C6:G6").ClearContents() !K19.Value = "Birthday" !C4.Formula2 = "=SEQUENCE(1,5)" !C6.Formula2 = "=DATE(YEAR(TODAY()),C4:G4,1)" !C7.Formula2 = "=RANDARRAY(4,5)" !C13.Formula2 = "=INT(RANDARRAY(4,5)*100)" !C20.Formula2 = "=SEQUENCE(10)" !E20.Formula2 = "=SORTBY(C20:C29,RANDARRAY(COUNTA(C20:C29)))" !H20.Formula2 = "=RANDARRAY(COUNTA(C20:C27))*40000" !J20.Formula2 = "=SORTBY(G20:H27,H20:H27)" .Item("$C$6:$G$6").NumberFormat = "mmmm" .Item("$C$7:$G$10").NumberFormat = "0.000000" .Item("$H$20:$H$27,$K$20:$K$27").NumberFormat = "mm/dd/yyyy" .Item("C:G").EntireColumn.ColumnWidth *= 1.5 .Item("H:H").EntireColumn.ColumnWidth *= 1.5 .Item("K:K").EntireColumn.ColumnWidth *= 1.5 End With ' save to an excel file workbook.Save("RandArrayFunction.xlsx")