// Create a new workbook Workbook workbook = new Workbook(); // VSTACK Function Example IWorksheet sheetOfVSTACK = workbook.getActiveSheet(); sheetOfVSTACK.setName("VSTACK"); sheetOfVSTACK.getRange("A1:G3").setValue(new Object[][] { {"Data", null, null, null, null, null, null}, {"A", "B", "C", null, "AA", "BB", "CC"}, {"D", "E", "F", null, "DD", "EE", "FF"} }); sheetOfVSTACK.getRange("A1").getFont().setBold(true); sheetOfVSTACK.getRange("A4").setValue("Formula"); sheetOfVSTACK.getRange("A4").getFont().setBold(true); sheetOfVSTACK.getRange("A5").setFormula("=FORMULATEXT(A7)"); sheetOfVSTACK.getRange("A6").setValue("Result"); sheetOfVSTACK.getRange("A6").getFont().setBold(true); sheetOfVSTACK.getRange("A7").setFormula2("=VSTACK(A2:C3,E2:G3)"); // HSTACK Function Example IWorksheet sheetOfHSTACK = workbook.getWorksheets().add(); sheetOfHSTACK.setName("HSTACK"); sheetOfHSTACK.getRange("A1:G3").setValue(new Object[][] { {"Data", null, null, null, null, null, null}, {"A", "B", "C", null, "AA", "BB", "CC"}, {"D", "E", "F", null, "DD", "EE", "FF"} }); sheetOfHSTACK.getRange("A1").getFont().setBold(true); sheetOfHSTACK.getRange("A4").setValue("Formula"); sheetOfHSTACK.getRange("A4").getFont().setBold(true); sheetOfHSTACK.getRange("A5").setFormula("=FORMULATEXT(A7)"); sheetOfHSTACK.getRange("A6").setValue("Result"); sheetOfHSTACK.getRange("A6").getFont().setBold(true); sheetOfHSTACK.getRange("A7").setFormula2("=HSTACK(A2:C3,E2:G3)"); // TOROW Function Example IWorksheet sheetOfTOROW = workbook.getWorksheets().add(); sheetOfTOROW.setName("TOROW"); sheetOfTOROW.getRange("A1:D4").setValue(new Object[][] { {"Data", null, null, null}, {"Ben", "Peter", "Mary", "Sam"}, {"John", "Hillary", "Jenny", "James"}, {"Agnes", "Harry", "Felicity", "Joe"} }); sheetOfTOROW.getRange("A1").getFont().setBold(true); sheetOfTOROW.getRange("A5").setValue("Formula"); sheetOfTOROW.getRange("A5").getFont().setBold(true); sheetOfTOROW.getRange("A6").setFormula("=FORMULATEXT(A8)"); sheetOfTOROW.getRange("A7").setValue("Result"); sheetOfTOROW.getRange("A7").getFont().setBold(true); sheetOfTOROW.getRange("A8").setFormula2("=TOROW(A2:D4)"); // TOCOL Function Exmaple IWorksheet sheetOfTOCOL = workbook.getWorksheets().add(); sheetOfTOCOL.setName("TOCOL"); sheetOfTOCOL.getRange("A1:D4").setValue(new Object[][] { {"Data", null, null, null}, {"Ben", "Peter", "Mary", "Sam"}, {"John", "Hillary", "Jenny", "James"}, {"Agnes", "Harry", "Felicity", "Joe"} }); sheetOfTOCOL.getRange("A1").getFont().setBold(true); sheetOfTOCOL.getRange("A5").setValue("Formula"); sheetOfTOCOL.getRange("A5").getFont().setBold(true); sheetOfTOCOL.getRange("A6").setFormula("=FORMULATEXT(A8)"); sheetOfTOCOL.getRange("A7").setValue("Result"); sheetOfTOCOL.getRange("A7").getFont().setBold(true); sheetOfTOCOL.getRange("A8").setFormula2("=TOCOL(A2:D4)"); // WRAPROWS Function Example IWorksheet sheetOfWRAPROWS = workbook.getWorksheets().add(); sheetOfWRAPROWS.setName("WRAPROWS"); sheetOfWRAPROWS.getRange("A1:G2").setValue(new Object[][] { {"Data", null, null, null, null, null, null}, {"A", "B", "C", "D", "E", "F", "G"} }); sheetOfWRAPROWS.getRange("A1").getFont().setBold(true); sheetOfWRAPROWS.getRange("A4").setValue("Formula"); sheetOfWRAPROWS.getRange("A4").getFont().setBold(true); sheetOfWRAPROWS.getRange("B4").setFormula("=FORMULATEXT(B5)"); sheetOfWRAPROWS.getRange("A5").setValue("Result"); sheetOfWRAPROWS.getRange("A5").getFont().setBold(true); sheetOfWRAPROWS.getRange("B5").setFormula2("=WRAPROWS(A2:G2,3)"); sheetOfWRAPROWS.getRange("A9").setValue("Formula"); sheetOfWRAPROWS.getRange("A9").getFont().setBold(true); sheetOfWRAPROWS.getRange("B9").setFormula("=FORMULATEXT(B10)"); sheetOfWRAPROWS.getRange("A10").setValue("Result"); sheetOfWRAPROWS.getRange("A10").getFont().setBold(true); sheetOfWRAPROWS.getRange("B10").setFormula2("=WRAPROWS(A2:G2,3,\"x\")"); // WRAPCOLS IWorksheet sheetOfWRAPCOLS = workbook.getWorksheets().add(); sheetOfWRAPCOLS.setName("WRAPCOLS"); sheetOfWRAPCOLS.getRange("A1:G2").setValue(new Object[][] { {"Data", null, null, null, null, null, null}, {"A", "B", "C", "D", "E", "F", "G"} }); sheetOfWRAPCOLS.getRange("A1").getFont().setBold(true); sheetOfWRAPCOLS.getRange("A4").setValue("Formula"); sheetOfWRAPCOLS.getRange("A4").getFont().setBold(true); sheetOfWRAPCOLS.getRange("B4").setFormula("=FORMULATEXT(B5)"); sheetOfWRAPCOLS.getRange("A5").setValue("Result"); sheetOfWRAPCOLS.getRange("A5").getFont().setBold(true); sheetOfWRAPCOLS.getRange("B5").setFormula2("=WRAPCOLS(A2:G2,3)"); sheetOfWRAPCOLS.getRange("A9").setValue("Formula"); sheetOfWRAPCOLS.getRange("A9").getFont().setBold(true); sheetOfWRAPCOLS.getRange("B9").setFormula("=FORMULATEXT(B10)"); sheetOfWRAPCOLS.getRange("A10").setValue("Result"); sheetOfWRAPCOLS.getRange("A10").getFont().setBold(true); sheetOfWRAPCOLS.getRange("B10").setFormula2("=WRAPCOLS(A2:G2,3,\"x\")"); // TAKE Function Example IWorksheet sheetOfTAKE = workbook.getWorksheets().add(); sheetOfTAKE.setName("TAKE"); sheetOfTAKE.getRange("A1:C4").setValue(new Object[][] { {"Data", null, null}, {1, 2, 3}, {4, 5, 6}, {7, 8, 9} }); sheetOfTAKE.getRange("A1").getFont().setBold(true); sheetOfTAKE.getRange("A6").setValue("Formula"); sheetOfTAKE.getRange("A6").getFont().setBold(true); sheetOfTAKE.getRange("B6").setFormula("=FORMULATEXT(B7)"); sheetOfTAKE.getRange("A7").setValue("Result"); sheetOfTAKE.getRange("A7").getFont().setBold(true); sheetOfTAKE.getRange("B7").setFormula2("=TAKE(A2:C4,2)"); sheetOfTAKE.getRange("A10").setValue("Formula"); sheetOfTAKE.getRange("A10").getFont().setBold(true); sheetOfTAKE.getRange("B10").setFormula("=FORMULATEXT(B11)"); sheetOfTAKE.getRange("A11").setValue("Result"); sheetOfTAKE.getRange("A11").getFont().setBold(true); sheetOfTAKE.getRange("B11").setFormula2("=TAKE(A2:C4,,2)"); sheetOfTAKE.getRange("A15").setValue("Formula"); sheetOfTAKE.getRange("A15").getFont().setBold(true); sheetOfTAKE.getRange("B15").setFormula("=FORMULATEXT(B16)"); sheetOfTAKE.getRange("A16").setValue("Result"); sheetOfTAKE.getRange("A16").getFont().setBold(true); sheetOfTAKE.getRange("B16").setFormula2("=TAKE(A2:C4,-2)"); sheetOfTAKE.getRange("A19").setValue("Formula"); sheetOfTAKE.getRange("A19").getFont().setBold(true); sheetOfTAKE.getRange("B19").setFormula("=FORMULATEXT(B20)"); sheetOfTAKE.getRange("A20").setValue("Result"); sheetOfTAKE.getRange("A20").getFont().setBold(true); sheetOfTAKE.getRange("B20").setFormula2("=TAKE(A2:C4,2,2)"); // DROP Function Example IWorksheet sheetOfDROP = workbook.getWorksheets().add(); sheetOfDROP.setName("DROP"); sheetOfDROP.getRange("A1:C4").setValue(new Object[][] { {"Data", null, null}, {1, 2, 3}, {4, 5, 6}, {7, 8, 9} }); sheetOfDROP.getRange("A1").getFont().setBold(true); sheetOfDROP.getRange("A6").setValue("Formula"); sheetOfDROP.getRange("A6").getFont().setBold(true); sheetOfDROP.getRange("B6").setFormula("=FORMULATEXT(B7)"); sheetOfDROP.getRange("A7").setValue("Result"); sheetOfDROP.getRange("A7").getFont().setBold(true); sheetOfDROP.getRange("B7").setFormula2("=DROP(A2:C4,2)"); sheetOfDROP.getRange("A9").setValue("Formula"); sheetOfDROP.getRange("A9").getFont().setBold(true); sheetOfDROP.getRange("B9").setFormula("=FORMULATEXT(B10)"); sheetOfDROP.getRange("A10").setValue("Result"); sheetOfDROP.getRange("A10").getFont().setBold(true); sheetOfDROP.getRange("B10").setFormula2("=DROP(A2:C4,,2)"); sheetOfDROP.getRange("A14").setValue("Formula"); sheetOfDROP.getRange("A14").getFont().setBold(true); sheetOfDROP.getRange("B14").setFormula("=FORMULATEXT(B15)"); sheetOfDROP.getRange("A15").setValue("Result"); sheetOfDROP.getRange("A15").getFont().setBold(true); sheetOfDROP.getRange("B15").setFormula2("=DROP(A2:C4,-2)"); sheetOfDROP.getRange("A17").setValue("Formula"); sheetOfDROP.getRange("A17").getFont().setBold(true); sheetOfDROP.getRange("B17").setFormula("=FORMULATEXT(B18)"); sheetOfDROP.getRange("A18").setValue("Result"); sheetOfDROP.getRange("A18").getFont().setBold(true); sheetOfDROP.getRange("B18").setFormula2("=DROP(A2:C4,2,2)"); // CHOOSEROWS Function Example IWorksheet sheetOfCHOOSEROWS = workbook.getWorksheets().add(); sheetOfCHOOSEROWS.setName("CHOOSEROWS"); sheetOfCHOOSEROWS.getRange("A1:B7").setValue(new Object[][] { {"Data", null}, {1, 2}, {3, 4}, {5, 6}, {7, 8}, {9, 10}, {11, 12} }); sheetOfCHOOSEROWS.getRange("A1").getFont().setBold(true); sheetOfCHOOSEROWS.getRange("A9").setValue("Formula"); sheetOfCHOOSEROWS.getRange("A9").getFont().setBold(true); sheetOfCHOOSEROWS.getRange("B9").setFormula("=FORMULATEXT(B10)"); sheetOfCHOOSEROWS.getRange("A10").setValue("Result"); sheetOfCHOOSEROWS.getRange("A10").getFont().setBold(true); sheetOfCHOOSEROWS.getRange("B10").setFormula2("=CHOOSEROWS(A2:B7,1,3,5,1)"); sheetOfCHOOSEROWS.getRange("A15").setValue("Formula"); sheetOfCHOOSEROWS.getRange("A15").getFont().setBold(true); sheetOfCHOOSEROWS.getRange("B15").setFormula("=FORMULATEXT(B16)"); sheetOfCHOOSEROWS.getRange("A16").setValue("Result"); sheetOfCHOOSEROWS.getRange("A16").getFont().setBold(true); sheetOfCHOOSEROWS.getRange("B16").setFormula2("=CHOOSEROWS(A2:B7,-1,-2)"); // CHOOSECOLS Function Example IWorksheet sheetOfCHOOSECOLS = workbook.getWorksheets().add(); sheetOfCHOOSECOLS.setName("CHOOSECOLS"); sheetOfCHOOSECOLS.getRange("A1:E6").setValue(new Object[][] { {"Data", null, null, null, null}, {1, 2, 3, 4, 5}, {6, 7, 8, 9, 10}, {11, 12, 13, 14, 15}, {16, 17, 18, 19, 20}, {21, 22, 23, 24, 25} }); sheetOfCHOOSECOLS.getRange("A1").getFont().setBold(true); sheetOfCHOOSECOLS.getRange("A8").setValue("Formula"); sheetOfCHOOSECOLS.getRange("A8").getFont().setBold(true); sheetOfCHOOSECOLS.getRange("B8").setFormula("=FORMULATEXT(B9)"); sheetOfCHOOSECOLS.getRange("A9").setValue("Result"); sheetOfCHOOSECOLS.getRange("A9").getFont().setBold(true); sheetOfCHOOSECOLS.getRange("B9").setFormula2("=CHOOSECOLS(A2:E6,1,3,5,1)"); sheetOfCHOOSECOLS.getRange("A15").setValue("Formula"); sheetOfCHOOSECOLS.getRange("A15").getFont().setBold(true); sheetOfCHOOSECOLS.getRange("B15").setFormula("=FORMULATEXT(B16)"); sheetOfCHOOSECOLS.getRange("A16").setValue("Result"); sheetOfCHOOSECOLS.getRange("A16").getFont().setBold(true); sheetOfCHOOSECOLS.getRange("B16").setFormula2("=CHOOSECOLS(A2:E6,-1,-2)"); // EXPAND Function Exmaple IWorksheet sheetOfEXPAND = workbook.getWorksheets().add(); sheetOfEXPAND.setName("EXPAND"); sheetOfEXPAND.getRange("A1:C3").setValue(new Object[][] { {"Data", null}, {1, 2}, {3, 4} }); sheetOfEXPAND.getRange("A1").getFont().setBold(true); sheetOfEXPAND.getRange("A5").setValue("Formula"); sheetOfEXPAND.getRange("A5").getFont().setBold(true); sheetOfEXPAND.getRange("B5").setFormula("=FORMULATEXT(B6)"); sheetOfEXPAND.getRange("A6").setValue("Result"); sheetOfEXPAND.getRange("A6").getFont().setBold(true); sheetOfEXPAND.getRange("B6").setFormula2("=EXPAND(A2:B3,3,3)"); sheetOfEXPAND.getRange("A10").setValue("Formula"); sheetOfEXPAND.getRange("A10").getFont().setBold(true); sheetOfEXPAND.getRange("B10").setFormula("=FORMULATEXT(B11)"); sheetOfEXPAND.getRange("A11").setValue("Result"); sheetOfEXPAND.getRange("A11").getFont().setBold(true); sheetOfEXPAND.getRange("B11").setFormula2("=EXPAND(A2:B3,3,3,\"-\")"); // Save to an excel file workbook.save("NewArrayManipulationFunctions.xlsx");
// Create a new workbook var workbook = Workbook() // VSTACK Function Example val sheetOfVSTACK = workbook.activeSheet sheetOfVSTACK.name = "VSTACK" sheetOfVSTACK.getRange("A1:G3").value = arrayOf( arrayOf("Data", null, null, null, null, null, null), arrayOf("A", "B", "C", null, "AA", "BB", "CC"), arrayOf("D", "E", "F", null, "DD", "EE", "FF") ) sheetOfVSTACK.getRange("A1").font.bold = true sheetOfVSTACK.getRange("A4").value = "Formula" sheetOfVSTACK.getRange("A4").font.bold = true sheetOfVSTACK.getRange("A5").formula = "=FORMULATEXT(A7)" sheetOfVSTACK.getRange("A6").value = "Result" sheetOfVSTACK.getRange("A6").font.bold = true sheetOfVSTACK.getRange("A7").formula2 = "=VSTACK(A2:C3,E2:G3)" // HSTACK Function Example val sheetOfHSTACK = workbook.worksheets.add() sheetOfHSTACK.name = "HSTACK" sheetOfHSTACK.getRange("A1:G3").value = arrayOf( arrayOf("Data", null, null, null, null, null, null), arrayOf("A", "B", "C", null, "AA", "BB", "CC"), arrayOf("D", "E", "F", null, "DD", "EE", "FF") ) sheetOfHSTACK.getRange("A1").font.bold = true sheetOfHSTACK.getRange("A4").value = "Formula" sheetOfHSTACK.getRange("A4").font.bold = true sheetOfHSTACK.getRange("A5").formula = "=FORMULATEXT(A7)" sheetOfHSTACK.getRange("A6").value = "Result" sheetOfHSTACK.getRange("A6").font.bold = true sheetOfHSTACK.getRange("A7").formula2 = "=HSTACK(A2:C3,E2:G3)" // TOROW Function Example val sheetOfTOROW = workbook.worksheets.add() sheetOfTOROW.name = "TOROW" sheetOfTOROW.getRange("A1:D4").value = arrayOf( arrayOf("Data", null, null, null), arrayOf("Ben", "Peter", "Mary", "Sam"), arrayOf("John", "Hillary", "Jenny", "James"), arrayOf("Agnes", "Harry", "Felicity", "Joe") ) sheetOfTOROW.getRange("A1").font.bold = true sheetOfTOROW.getRange("A5").value = "Formula" sheetOfTOROW.getRange("A5").font.bold = true sheetOfTOROW.getRange("A6").formula = "=FORMULATEXT(A8)" sheetOfTOROW.getRange("A7").value = "Result" sheetOfTOROW.getRange("A7").font.bold = true sheetOfTOROW.getRange("A8").formula2 = "=TOROW(A2:D4)" // TOCOL Function Exmaple val sheetOfTOCOL = workbook.worksheets.add() sheetOfTOCOL.name = "TOCOL" sheetOfTOCOL.getRange("A1:D4").value = arrayOf( arrayOf("Data", null, null, null), arrayOf("Ben", "Peter", "Mary", "Sam"), arrayOf("John", "Hillary", "Jenny", "James"), arrayOf("Agnes", "Harry", "Felicity", "Joe") ) sheetOfTOCOL.getRange("A1").font.bold = true sheetOfTOCOL.getRange("A5").value = "Formula" sheetOfTOCOL.getRange("A5").font.bold = true sheetOfTOCOL.getRange("A6").formula = "=FORMULATEXT(A8)" sheetOfTOCOL.getRange("A7").value = "Result" sheetOfTOCOL.getRange("A7").font.bold = true sheetOfTOCOL.getRange("A8").formula2 = "=TOCOL(A2:D4)" // WRAPROWS Function Example val sheetOfWRAPROWS = workbook.worksheets.add() sheetOfWRAPROWS.name = "WRAPROWS" sheetOfWRAPROWS.getRange("A1:G2").value = arrayOf( arrayOf("Data", null, null, null, null, null, null), arrayOf("A", "B", "C", "D", "E", "F", "G") ) sheetOfWRAPROWS.getRange("A1").font.bold = true sheetOfWRAPROWS.getRange("A4").value = "Formula" sheetOfWRAPROWS.getRange("A4").font.bold = true sheetOfWRAPROWS.getRange("B4").formula = "=FORMULATEXT(B5)" sheetOfWRAPROWS.getRange("A5").value = "Result" sheetOfWRAPROWS.getRange("A5").font.bold = true sheetOfWRAPROWS.getRange("B5").formula2 = "=WRAPROWS(A2:G2,3)" sheetOfWRAPROWS.getRange("A9").value = "Formula" sheetOfWRAPROWS.getRange("A9").font.bold = true sheetOfWRAPROWS.getRange("B9").formula = "=FORMULATEXT(B10)" sheetOfWRAPROWS.getRange("A10").value = "Result" sheetOfWRAPROWS.getRange("A10").font.bold = true sheetOfWRAPROWS.getRange("B10").formula2 = "=WRAPROWS(A2:G2,3,\"x\")" // WRAPCOLS val sheetOfWRAPCOLS = workbook.worksheets.add() sheetOfWRAPCOLS.name = "WRAPCOLS" sheetOfWRAPCOLS.getRange("A1:G2").value = arrayOf( arrayOf("Data", null, null, null, null, null, null), arrayOf("A", "B", "C", "D", "E", "F", "G") ) sheetOfWRAPCOLS.getRange("A1").font.bold = true sheetOfWRAPCOLS.getRange("A4").value = "Formula" sheetOfWRAPCOLS.getRange("A4").font.bold = true sheetOfWRAPCOLS.getRange("B4").formula = "=FORMULATEXT(B5)" sheetOfWRAPCOLS.getRange("A5").value = "Result" sheetOfWRAPCOLS.getRange("A5").font.bold = true sheetOfWRAPCOLS.getRange("B5").formula2 = "=WRAPCOLS(A2:G2,3)" sheetOfWRAPCOLS.getRange("A9").value = "Formula" sheetOfWRAPCOLS.getRange("A9").font.bold = true sheetOfWRAPCOLS.getRange("B9").formula = "=FORMULATEXT(B10)" sheetOfWRAPCOLS.getRange("A10").value = "Result" sheetOfWRAPCOLS.getRange("A10").font.bold = true sheetOfWRAPCOLS.getRange("B10").formula2 = "=WRAPCOLS(A2:G2,3,\"x\")" // TAKE Function Example val sheetOfTAKE = workbook.worksheets.add() sheetOfTAKE.name = "TAKE" sheetOfTAKE.getRange("A1:C4").value = arrayOf( arrayOf("Data", null, null), arrayOf(1, 2, 3), arrayOf(4, 5, 6), arrayOf(7, 8, 9) ) sheetOfTAKE.getRange("A1").font.bold = true sheetOfTAKE.getRange("A6").value = "Formula" sheetOfTAKE.getRange("A6").font.bold = true sheetOfTAKE.getRange("B6").formula = "=FORMULATEXT(B7)" sheetOfTAKE.getRange("A7").value = "Result" sheetOfTAKE.getRange("A7").font.bold = true sheetOfTAKE.getRange("B7").formula2 = "=TAKE(A2:C4,2)" sheetOfTAKE.getRange("A10").value = "Formula" sheetOfTAKE.getRange("A10").font.bold = true sheetOfTAKE.getRange("B10").formula = "=FORMULATEXT(B11)" sheetOfTAKE.getRange("A11").value = "Result" sheetOfTAKE.getRange("A11").font.bold = true sheetOfTAKE.getRange("B11").formula2 = "=TAKE(A2:C4,,2)" sheetOfTAKE.getRange("A15").value = "Formula" sheetOfTAKE.getRange("A15").font.bold = true sheetOfTAKE.getRange("B15").formula = "=FORMULATEXT(B16)" sheetOfTAKE.getRange("A16").value = "Result" sheetOfTAKE.getRange("A16").font.bold = true sheetOfTAKE.getRange("B16").formula2 = "=TAKE(A2:C4,-2)" sheetOfTAKE.getRange("A19").value = "Formula" sheetOfTAKE.getRange("A19").font.bold = true sheetOfTAKE.getRange("B19").formula = "=FORMULATEXT(B20)" sheetOfTAKE.getRange("A20").value = "Result" sheetOfTAKE.getRange("A20").font.bold = true sheetOfTAKE.getRange("B20").formula2 = "=TAKE(A2:C4,2,2)" // DROP Function Example val sheetOfDROP = workbook.worksheets.add() sheetOfDROP.name = "DROP" sheetOfDROP.getRange("A1:C4").value = arrayOf( arrayOf("Data", null, null), arrayOf(1, 2, 3), arrayOf(4, 5, 6), arrayOf(7, 8, 9) ) sheetOfDROP.getRange("A1").font.bold = true sheetOfDROP.getRange("A6").value = "Formula" sheetOfDROP.getRange("A6").font.bold = true sheetOfDROP.getRange("B6").formula = "=FORMULATEXT(B7)" sheetOfDROP.getRange("A7").value = "Result" sheetOfDROP.getRange("A7").font.bold = true sheetOfDROP.getRange("B7").formula2 = "=DROP(A2:C4,2)" sheetOfDROP.getRange("A9").value = "Formula" sheetOfDROP.getRange("A9").font.bold = true sheetOfDROP.getRange("B9").formula = "=FORMULATEXT(B10)" sheetOfDROP.getRange("A10").value = "Result" sheetOfDROP.getRange("A10").font.bold = true sheetOfDROP.getRange("B10").formula2 = "=DROP(A2:C4,,2)" sheetOfDROP.getRange("A14").value = "Formula" sheetOfDROP.getRange("A14").font.bold = true sheetOfDROP.getRange("B14").formula = "=FORMULATEXT(B15)" sheetOfDROP.getRange("A15").value = "Result" sheetOfDROP.getRange("A15").font.bold = true sheetOfDROP.getRange("B15").formula2 = "=DROP(A2:C4,-2)" sheetOfDROP.getRange("A17").value = "Formula" sheetOfDROP.getRange("A17").font.bold = true sheetOfDROP.getRange("B17").formula = "=FORMULATEXT(B18)" sheetOfDROP.getRange("A18").value = "Result" sheetOfDROP.getRange("A18").font.bold = true sheetOfDROP.getRange("B18").formula2 = "=DROP(A2:C4,2,2)" // CHOOSEROWS Function Example val sheetOfCHOOSEROWS = workbook.worksheets.add() sheetOfCHOOSEROWS.name = "CHOOSEROWS" sheetOfCHOOSEROWS.getRange("A1:B7").value = arrayOf( arrayOf("Data", null), arrayOf(1, 2), arrayOf(3, 4), arrayOf(5, 6), arrayOf(7, 8), arrayOf(9, 10), arrayOf(11, 12) ) sheetOfCHOOSEROWS.getRange("A1").font.bold = true; sheetOfCHOOSEROWS.getRange("A9").value = "Formula" sheetOfCHOOSEROWS.getRange("A9").font.bold = true sheetOfCHOOSEROWS.getRange("B9").formula = "=FORMULATEXT(B10)" sheetOfCHOOSEROWS.getRange("A10").value = "Result" sheetOfCHOOSEROWS.getRange("A10").font.bold = true sheetOfCHOOSEROWS.getRange("B10").formula2 = "=CHOOSEROWS(A2:B7,1,3,5,1)" sheetOfCHOOSEROWS.getRange("A15").value = "Formula" sheetOfCHOOSEROWS.getRange("A15").font.bold = true sheetOfCHOOSEROWS.getRange("B15").formula = "=FORMULATEXT(B16)" sheetOfCHOOSEROWS.getRange("A16").value = "Result" sheetOfCHOOSEROWS.getRange("A16").font.bold = true sheetOfCHOOSEROWS.getRange("B16").formula2 = "=CHOOSEROWS(A2:B7,-1,-2)" // CHOOSECOLS Function Example val sheetOfCHOOSECOLS = workbook.worksheets.add() sheetOfCHOOSECOLS.name = "CHOOSECOLS" sheetOfCHOOSECOLS.getRange("A1:E6").value = arrayOf( arrayOf("Data", null, null, null, null), arrayOf(1, 2, 3, 4, 5), arrayOf(6, 7, 8, 9, 10), arrayOf(11, 12, 13, 14, 15), arrayOf(16, 17, 18, 19, 20), arrayOf(21, 22, 23, 24, 25) ) sheetOfCHOOSECOLS.getRange("A1").font.bold = true sheetOfCHOOSECOLS.getRange("A8").value = "Formula" sheetOfCHOOSECOLS.getRange("A8").font.bold = true sheetOfCHOOSECOLS.getRange("B8").formula = "=FORMULATEXT(B9)" sheetOfCHOOSECOLS.getRange("A9").value = "Result" sheetOfCHOOSECOLS.getRange("A9").font.bold = true sheetOfCHOOSECOLS.getRange("B9").formula2 = "=CHOOSECOLS(A2:E6,1,3,5,1)" sheetOfCHOOSECOLS.getRange("A15").value = "Formula" sheetOfCHOOSECOLS.getRange("A15").font.bold = true sheetOfCHOOSECOLS.getRange("B15").formula = "=FORMULATEXT(B16)" sheetOfCHOOSECOLS.getRange("A16").value = "Result" sheetOfCHOOSECOLS.getRange("A16").font.bold = true sheetOfCHOOSECOLS.getRange("B16").formula2 = "=CHOOSECOLS(A2:E6,-1,-2)" // EXPAND Function Exmaple val sheetOfEXPAND = workbook.worksheets.add() sheetOfEXPAND.name = "EXPAND" sheetOfEXPAND.getRange("A1:C3").value = arrayOf(arrayOf("Data", null), arrayOf(1, 2), arrayOf(3, 4)) sheetOfEXPAND.getRange("A1").font.bold = true; sheetOfEXPAND.getRange("A5").value = "Formula" sheetOfEXPAND.getRange("A5").font.bold = true sheetOfEXPAND.getRange("B5").formula = "=FORMULATEXT(B6)" sheetOfEXPAND.getRange("A6").value = "Result" sheetOfEXPAND.getRange("A6").font.bold = true sheetOfEXPAND.getRange("B6").formula2 = "=EXPAND(A2:B3,3,3)" sheetOfEXPAND.getRange("A10").value = "Formula" sheetOfEXPAND.getRange("A10").font.bold = true sheetOfEXPAND.getRange("B10").formula = "=FORMULATEXT(B11)" sheetOfEXPAND.getRange("A11").value = "Result" sheetOfEXPAND.getRange("A11").font.bold = true sheetOfEXPAND.getRange("B11").formula2 = "=EXPAND(A2:B3,3,3,\"-\")" // Save to an excel file workbook.save("NewArrayManipulationFunctions.xlsx")