// 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");