// Create a new workbook Workbook workbook = new Workbook(); IWorksheet ws = workbook.getWorksheets().get(0); ws.setName("RANDARRAY"); ws.getRange("$C$1").setValue( "The RANDARRAY function allows you to generate a list of random numbers from 0 to 1 in an array. Syntax: RANDARRAY([rows],[columns])"); ws.getRange("$C$3").setValue("SEQUENCE(1, 5)"); ws.getRange("$H$6:$H$7").setValue(new Object[][] { { " <--- DATE(YEAR(TODAY()), C4:G4 , 1) with mmmm" }, { " <--- RANDARRAY(4,5) with 0.000000" } }); ws.getRange("$H$13").setValue(" <--- INT(RANDARRAY(4,5)*100)"); ws.getRange("$D$18").setValue("Random order: SORTBY(C20:C29,RANDARRAY(COUNTA(C20:C29)))"); ws.getRange("$J$18:$J$19") .setValue(new Object[][] { { "Sort range by birthday column: SORTBY(G20:H27,H20:H27)" }, { "Name" } }); ws.getRange("$C$19").setValue("Units"); ws.getRange("$E$19").setValue("SortBy"); ws.getRange("$G$19:$G$27").setValue(new Object[][] { { "Name" }, { "Fritz" }, { "Xi" }, { "Amy" }, { "Sravan" }, { "Tom" }, { "Fred" }, { "Hector" }, { "Sal" } }); ws.getRange("$H$19:$I$19").setValue(new Object[][] { { "Birthday", "" } }); // Apply table style. ITable table = ws.getTables().add(ws.getRange("C6:G10"),true); ITable table1 = ws.getTables().add(ws.getRange("C12:G16"),true); table1.setShowHeaders(false); ITable table2 = ws.getTables().add(ws.getRange("C19:C29"),true); ITable table3 = ws.getTables().add(ws.getRange("E19:E29"),true); ITable table4 = ws.getTables().add(ws.getRange("G19:H27"),true); ITable table5 = ws.getTables().add(ws.getRange("J19:K27"),true); table.convertToRange(); table1.convertToRange(); table2.convertToRange(); table3.convertToRange(); table4.convertToRange(); table5.convertToRange(); ws.getRange("C6:G6").clearContents(); ws.getRange("$K$19").setValue("Birthday"); ws.getRange("$C$4").setFormula2("=SEQUENCE(1,5)"); ws.getRange("$C$6").setFormula2("=DATE(YEAR(TODAY()),C4:G4,1)"); ws.getRange("$C$7").setFormula2("=RANDARRAY(4,5)"); ws.getRange("$C$13").setFormula2("=INT(RANDARRAY(4,5)*100)"); ws.getRange("$C$20").setFormula2("=SEQUENCE(10)"); ws.getRange("$E$20").setFormula2("=SORTBY(C20:C29,RANDARRAY(COUNTA(C20:C29)))"); ws.getRange("$H$20").setFormula2("=RANDARRAY(COUNTA(C20:C27))*40000"); ws.getRange("$J$20").setFormula2("=SORTBY(G20:H27,H20:H27)"); ws.getRange("$C$6:$G$6").setNumberFormat("mmmm"); ws.getRange("$C$7:$G$10").setNumberFormat("0.000000"); ws.getRange("$H$20:$H$27,$K$20:$K$27").setNumberFormat("mm/dd/yyyy"); ws.getRange("C:G").getEntireColumn() .setColumnWidth(ws.getRange("C:G").getEntireColumn().getColumnWidth() * 1.5); ws.getRange("H:H").getEntireColumn() .setColumnWidth(ws.getRange("H:H").getEntireColumn().getColumnWidth() * 1.5); ws.getRange("K:K").getEntireColumn() .setColumnWidth(ws.getRange("K:K").getEntireColumn().getColumnWidth() * 1.5); // Save to an excel file workbook.save("RandArrayFunction.xlsx");
// Create a new workbook var workbook = Workbook() val ws: IWorksheet = workbook.getWorksheets().get(0) ws.setName("RANDARRAY") ws.getRange("\$C$1").setValue( "The RANDARRAY function allows you to generate a list of random numbers from 0 to 1 in an array. Syntax: RANDARRAY([rows],[columns])") ws.getRange("\$C$3").setValue("SEQUENCE(1, 5)") ws.getRange("\$H$6:\$H$7").setValue(arrayOf(arrayOf(" <--- DATE(YEAR(TODAY()), C4:G4 , 1) with mmmm"), arrayOf(" <--- RANDARRAY(4,5) with 0.000000"))) ws.getRange("\$H$13").setValue(" <--- INT(RANDARRAY(4,5)*100)") ws.getRange("\$D$18").setValue("Random order: SORTBY(C20:C29,RANDARRAY(COUNTA(C20:C29)))") ws.getRange("\$J$18:\$J$19") .setValue(arrayOf(arrayOf("Sort range by birthday column: SORTBY(G20:H27,H20:H27)"), arrayOf("Name"))) ws.getRange("\$C$19").setValue("Units") ws.getRange("\$E$19").setValue("SortBy") ws.getRange("\$G$19:\$G$27").setValue(arrayOf(arrayOf("Name"), arrayOf("Fritz"), arrayOf("Xi"), arrayOf("Amy"), arrayOf("Sravan"), arrayOf("Tom"), arrayOf("Fred"), arrayOf("Hector"), arrayOf("Sal"))) ws.getRange("\$H$19:\$I$19").setValue(arrayOf(arrayOf("Birthday", ""))) // Apply table style. val table: ITable = ws.getTables().add(ws.getRange("C6:G10"), true) val table1: ITable = ws.getTables().add(ws.getRange("C12:G16"), true) table1.setShowHeaders(false) val table2: ITable = ws.getTables().add(ws.getRange("C19:C29"), true) val table3: ITable = ws.getTables().add(ws.getRange("E19:E29"), true) val table4: ITable = ws.getTables().add(ws.getRange("G19:H27"), true) val table5: ITable = ws.getTables().add(ws.getRange("J19:K27"), true) table.convertToRange() table1.convertToRange() table2.convertToRange() table3.convertToRange() table4.convertToRange() table5.convertToRange() ws.getRange("C6:G6").clearContents() ws.getRange("\$K$19").setValue("Birthday") ws.getRange("\$C$4").setFormula2("=SEQUENCE(1,5)") ws.getRange("\$C$6").setFormula2("=DATE(YEAR(TODAY()),C4:G4,1)") ws.getRange("\$C$7").setFormula2("=RANDARRAY(4,5)") ws.getRange("\$C$13").setFormula2("=INT(RANDARRAY(4,5)*100)") ws.getRange("\$C$20").setFormula2("=SEQUENCE(10)") ws.getRange("\$E$20").setFormula2("=SORTBY(C20:C29,RANDARRAY(COUNTA(C20:C29)))") ws.getRange("\$H$20").setFormula2("=RANDARRAY(COUNTA(C20:C27))*40000") ws.getRange("\$J$20").setFormula2("=SORTBY(G20:H27,H20:H27)") ws.getRange("\$C$6:\$G$6").setNumberFormat("mmmm") ws.getRange("\$C$7:\$G$10").setNumberFormat("0.000000") ws.getRange("\$H$20:\$H$27,\$K$20:\$K$27").setNumberFormat("mm/dd/yyyy") ws.getRange("C:G").getEntireColumn() .setColumnWidth(ws.getRange("C:G").getEntireColumn().getColumnWidth() * 1.5) ws.getRange("H:H").getEntireColumn() .setColumnWidth(ws.getRange("H:H").getEntireColumn().getColumnWidth() * 1.5) ws.getRange("K:K").getEntireColumn() .setColumnWidth(ws.getRange("K:K").getEntireColumn().getColumnWidth() * 1.5) // Save to an excel file workbook.save("RandArrayFunction.xlsx")