// Create a new workbook Workbook workbook = new Workbook(); //#region BYROW IWorksheet sheet1 = workbook.getWorksheets().add(); sheet1.setName("BYROW"); Object[][] sheet1rngB2B3 = new Object[][] { {"Get row totals"}, {"Formula: =BYROW(C6:E10,LAMBDA(row,SUM(row)))"} }; sheet1.getRange("$B$2:$B$3").setValue(sheet1rngB2B3); Object[][] sheet1rngB5E10 = new Object[][] { {"Student", "Math", "English", "physics"}, {"Tom", 19d, 23d, 19d}, {"Jerry", 21d, 15d, 18d}, {"Mario", 19d, 22d, 22d}, {"Luigi", 17d, 15d, 23d}, {"Galen", 16d, 22d, 24d} }; sheet1.getRange("$B$5:$E$10").setValue(sheet1rngB5E10); String sheet1rngG5 = "Total"; sheet1.getRange("$G$5").setValue(sheet1rngG5); sheet1.getRange("$G$6").setFormula2("=BYROW(C6:E10,LAMBDA(row,SUM(row)))"); sheet1.getRange("$B$2").getFont().setBold(true); IFont sheet1Font1 = sheet1.getRange("$B$3").getFont(); sheet1Font1.setThemeColor(ThemeColor.Accent1); sheet1Font1.setItalic(true); IFont sheet1Font2 = sheet1.getRange("$B$5:$E$5,$G$5").getFont(); sheet1Font2.setThemeColor(ThemeColor.Light1); sheet1Font2.setBold(true); IInterior sheet1Interior1 = sheet1.getRange("$B$5:$E$5,$G$5").getInterior(); sheet1Interior1.setThemeColor(ThemeColor.Accent1); sheet1Interior1.setPattern(Pattern.Solid); sheet1Interior1.setPatternThemeColor(ThemeColor.Accent1); IInterior sheet1Interior2 = sheet1.getRange("$B$6:$E$6,$G$6,$B$8:$E$8,$G$8,$B$10:$E$10,$G$10").getInterior(); sheet1Interior2.setThemeColor(ThemeColor.Accent1); sheet1Interior2.setPattern(Pattern.Solid); sheet1Interior2.setTintAndShade(0.8); IRange sheet1Range1 = sheet1.getRange("B5:E10,G5:G10"); sheet1Range1.getBorders().get(BordersIndex.EdgeTop).setLineStyle(BorderLineStyle.DashDot); sheet1Range1.getBorders().get(BordersIndex.EdgeLeft).setLineStyle(BorderLineStyle.DashDot); sheet1Range1.getBorders().get(BordersIndex.EdgeRight).setLineStyle(BorderLineStyle.DashDot); sheet1Range1.getBorders().get(BordersIndex.EdgeBottom).setLineStyle(BorderLineStyle.DashDot); sheet1Range1.getBorders().get(BordersIndex.InsideHorizontal).setLineStyle(BorderLineStyle.DashDot); sheet1Range1.getBorders().get(BordersIndex.InsideVertical).setLineStyle(BorderLineStyle.DashDot); // #endregion // #region IWorksheet sheet2 = workbook.getWorksheets().add(); sheet2.setName("BYCOL"); Object[][] sheet2rngB2B3 = new Object[][] { {"Get column totals"}, {"Formula: =BYCOL(C6:E10,LAMBDA(col,SUM(col)))"} }; sheet2.getRange("$B$2:$B$3").setValue(sheet2rngB2B3); Object[][] sheet2rngB5E10 = new Object[][] { {"Student", "Math", "English", "physics"}, {"Tom", 19d, 23d, 19d}, {"Jerry", 21d, 15d, 18d}, {"Mario", 19d, 22d, 22d}, {"Luigi", 17d, 15d, 23d}, {"Galen", 16d, 22d, 24d} }; sheet2.getRange("$B$5:$E$10").setValue(sheet2rngB5E10); String sheet2rngB12 = "Column Total"; sheet2.getRange("$B$12").setValue(sheet2rngB12); sheet2.getRange("$C$12").setFormula2("=BYCOL(C6:E10,LAMBDA(col,SUM(col)))"); sheet2.getRange("$B$2").getFont().setBold(true); IFont sheet2Font1 = sheet2.getRange("$B$3").getFont(); sheet2Font1.setThemeColor(ThemeColor.Accent1); sheet2Font1.setItalic(true); IFont sheet2Font2 = sheet2.getRange("$B$5:$E$5").getFont(); sheet2Font2.setThemeColor(ThemeColor.Light1); sheet2Font2.setBold(true); IInterior sheet2Interior1 = sheet2.getRange("$B$5:$E$5").getInterior(); sheet2Interior1.setThemeColor(ThemeColor.Accent1); sheet2Interior1.setPattern(Pattern.Solid); sheet2Interior1.setPatternThemeColor(ThemeColor.Accent1); IInterior sheet2Interior2 = sheet2.getRange("$B$6:$E$6,$B$8:$E$8,$B$10:$E$10,$B$12:$E$12").getInterior(); sheet2Interior2.setThemeColor(ThemeColor.Accent1); sheet2Interior2.setPattern(Pattern.Solid); sheet2Interior2.setTintAndShade(0.8); sheet2.getRange("$B:$B").setColumnWidthInPixel(103d); sheet2.getRange("$E:$E").setColumnWidthInPixel(74d); IRange sheet2Range1 = sheet2.getRange("B5:E10,B12:E12"); sheet2Range1.getBorders().get(BordersIndex.EdgeTop).setLineStyle(BorderLineStyle.DashDot); sheet2Range1.getBorders().get(BordersIndex.EdgeLeft).setLineStyle(BorderLineStyle.DashDot); sheet2Range1.getBorders().get(BordersIndex.EdgeRight).setLineStyle(BorderLineStyle.DashDot); sheet2Range1.getBorders().get(BordersIndex.EdgeBottom).setLineStyle(BorderLineStyle.DashDot); sheet2Range1.getBorders().get(BordersIndex.InsideHorizontal).setLineStyle(BorderLineStyle.DashDot); sheet2Range1.getBorders().get(BordersIndex.InsideVertical).setLineStyle(BorderLineStyle.DashDot); // #endregion // #region SCAN IWorksheet sheet3 = workbook.getWorksheets().add(); sheet3.setName("SCAN"); Object[][] sheet3rngB2B3 = new Object[][] { {"Create a list of factorials"}, {"Formula: =SCAN(1,B6:D7,LAMBDA(a,b,a*b))"} }; sheet3.getRange("$B$2:$B$3").setValue(sheet3rngB2B3); Object[][] sheet3rngB5D7 = new Object[][] { {"Data", null, null}, {1d, 2d, 3d}, {4d, 5d, 6d} }; sheet3.getRange("$B$5:$D$7").setValue(sheet3rngB5D7); Object[][] sheet3rngF5H5 = new Object[][] { {"Result:", null, null} }; sheet3.getRange("$F$5:$H$5").setValue(sheet3rngF5H5); sheet3.getRange("$F$6").setFormula2("=SCAN(1,B6:D7,LAMBDA(a,b,a*b))"); sheet3.getRange("$B$2").getFont().setBold(true); IFont sheet3Font1 = sheet3.getRange("$B$3").getFont(); sheet3Font1.setThemeColor(ThemeColor.Accent1); sheet3Font1.setItalic(true); IFont sheet3Font2 = sheet3.getRange("$B$5:$D$5,$F$5:$H$5").getFont(); sheet3Font2.setThemeColor(ThemeColor.Light1); sheet3Font2.setBold(true); IInterior sheet3Font3 = sheet3.getRange("$B$5:$D$5,$F$5:$H$5").getInterior(); sheet3Font3.setThemeColor(ThemeColor.Accent1); sheet3Font3.setPattern(Pattern.Solid); sheet3Font3.setPatternThemeColor(ThemeColor.Accent1); IInterior sheet3Font4 = sheet3.getRange("$B$6:$D$6,$F$6:$H$6").getInterior(); sheet3Font4.setThemeColor(ThemeColor.Accent1); sheet3Font4.setPattern(Pattern.Solid); sheet3Font4.setTintAndShade(0.8); sheet3.getRange("$B$5:$D$5").merge(); sheet3.getRange("$F$5:$H$5").merge(); sheet3.getRange("$C:$D").setColumnWidthInPixel(84d); IRange sheet3Range1 = sheet3.getRange("B5:D7,F5:H7"); sheet3Range1.getBorders().get(BordersIndex.EdgeTop).setLineStyle(BorderLineStyle.DashDot); sheet3Range1.getBorders().get(BordersIndex.EdgeLeft).setLineStyle(BorderLineStyle.DashDot); sheet3Range1.getBorders().get(BordersIndex.EdgeRight).setLineStyle(BorderLineStyle.DashDot); sheet3Range1.getBorders().get(BordersIndex.EdgeBottom).setLineStyle(BorderLineStyle.DashDot); sheet3Range1.getBorders().get(BordersIndex.InsideHorizontal).setLineStyle(BorderLineStyle.DashDot); sheet3Range1.getBorders().get(BordersIndex.InsideVertical).setLineStyle(BorderLineStyle.DashDot); // #endregion // #region REDUCE IWorksheet sheet4 = workbook.getWorksheets().add(); sheet4.setName("REDUCE"); Object[][] sheet4rngB2B3 = new Object[][] { {"Get cumulative results"}, {"Formula: =REDUCE(1,B6:D7,LAMBDA(a,b,a*b))"} }; sheet4.getRange("$B$2:$B$3").setValue(sheet4rngB2B3); Object[][] sheet4rngB5D7 = new Object[][] { {"Data", null, null}, {1d, 2d, 3d}, {4d, 5d, 6d} }; sheet4.getRange("$B$5:$D$7").setValue(sheet4rngB5D7); String rngF5 = "Result:"; sheet4.getRange("$F$5").setValue(rngF5); sheet4.getRange("$G$5").setFormula2("=REDUCE(1,B6:D7,LAMBDA(a,b,a*b))"); sheet4.getRange("$B$2").getFont().setBold(true); IFont sheet4Font1 = sheet4.getRange("$B$3").getFont(); sheet4Font1.setThemeColor(ThemeColor.Accent1); sheet4Font1.setItalic(true); IFont sheet4Font2 = sheet4.getRange("$B$5:$D$5").getFont(); sheet4Font2.setThemeColor(ThemeColor.Light1); sheet4Font2.setBold(true); IInterior sheet4Interior1 = sheet4.getRange("$B$5:$D$5").getInterior(); sheet4Interior1.setThemeColor(ThemeColor.Accent1); sheet4Interior1.setPattern(Pattern.Solid); sheet4Interior1.setPatternThemeColor(ThemeColor.Accent1); IInterior sheet4Interior2 = sheet4.getRange("$B$6:$D$6").getInterior(); sheet4Interior2.setThemeColor(ThemeColor.Accent1); sheet4Interior2.setPattern(Pattern.Solid); sheet4Interior2.setTintAndShade(0.8); sheet4.getRange("$B$5:$D$5").merge(); IRange sheet4Range1 = sheet4.getRange("B5:D7"); sheet4Range1.getBorders().get(BordersIndex.EdgeTop).setLineStyle(BorderLineStyle.DashDot); sheet4Range1.getBorders().get(BordersIndex.EdgeLeft).setLineStyle(BorderLineStyle.DashDot); sheet4Range1.getBorders().get(BordersIndex.EdgeRight).setLineStyle(BorderLineStyle.DashDot); sheet4Range1.getBorders().get(BordersIndex.EdgeBottom).setLineStyle(BorderLineStyle.DashDot); sheet4Range1.getBorders().get(BordersIndex.InsideHorizontal).setLineStyle(BorderLineStyle.DashDot); sheet4Range1.getBorders().get(BordersIndex.InsideVertical).setLineStyle(BorderLineStyle.DashDot); // #endregion // #region MAP IWorksheet sheet5 = workbook.getWorksheets().add(); sheet5.setName("MAP"); Object[][] sheet5rngB2B3 = new Object[][] { {"Square numbers above a vertain value"}, {"Formula: =MAP(B6:D7,LAMBDA(a,IF(a>4,a*a,a*1)))"} }; sheet5.getRange("$B$2:$B$3").setValue(sheet5rngB2B3); Object[][] sheet5rngB5D7 = new Object[][] { {"Data", null, null}, {1d, 2d, 3d}, {4d, 5d, 6d} }; sheet5.getRange("$B$5:$D$7").setValue(sheet5rngB5D7); Object[][] rngF5H5 = new Object[][] { {"Result:", null, null} }; sheet5.getRange("$F$5:$H$5").setValue(rngF5H5); sheet5.getRange("$F$6").setFormula2("=MAP(B6:D7,LAMBDA(a,IF(a>4,a*a,a*1)))"); sheet5.getRange("$B$2").getFont().setBold(true); IFont sheet5Font1 = sheet5.getRange("$B$3").getFont(); sheet5Font1.setThemeColor(ThemeColor.Accent1); sheet5Font1.setItalic(true); IFont sheet5Font2 = sheet5.getRange("$B$5:$D$5,$F$5:$H$5").getFont(); sheet5Font2.setThemeColor(ThemeColor.Light1); sheet5Font2.setBold(true); IInterior sheet5Interior1 = sheet5.getRange("$B$5:$D$5,$F$5:$H$5").getInterior(); sheet5Interior1.setThemeColor(ThemeColor.Accent1); sheet5Interior1.setPattern(Pattern.Solid); sheet5Interior1.setPatternThemeColor(ThemeColor.Accent1); IInterior sheet5Interior2 = sheet5.getRange("$B$6:$D$6,$F$6:$H$6").getInterior(); sheet5Interior2.setThemeColor(ThemeColor.Accent1); sheet5Interior2.setPattern(Pattern.Solid); sheet5Interior2.setTintAndShade(0.8); sheet5.getRange("$B$5:$D$5").merge(); sheet5.getRange("$F$5:$H$5").merge(); IRange sheet5Range1 = sheet5.getRange("B5:D7,F5:H7"); sheet5Range1.getBorders().get(BordersIndex.EdgeTop).setLineStyle(BorderLineStyle.DashDot); sheet5Range1.getBorders().get(BordersIndex.EdgeLeft).setLineStyle(BorderLineStyle.DashDot); sheet5Range1.getBorders().get(BordersIndex.EdgeRight).setLineStyle(BorderLineStyle.DashDot); sheet5Range1.getBorders().get(BordersIndex.EdgeBottom).setLineStyle(BorderLineStyle.DashDot); sheet5Range1.getBorders().get(BordersIndex.InsideHorizontal).setLineStyle(BorderLineStyle.DashDot); sheet5Range1.getBorders().get(BordersIndex.InsideVertical).setLineStyle(BorderLineStyle.DashDot); // #endregion // #region MAKEARRAY IWorksheet sheet6 = workbook.getWorksheets().add(); sheet6.setName("MAKEARRAY"); Object[][] sheet6rngB2B3 = new Object[][] { {"Multiplication table"}, {"Formula: =MAKEARRAY(9,9,LAMBDA(row,col,row&\"*\"&col&\" = \"&row*col))"} }; sheet6.getRange("$B$2:$B$3").setValue(sheet6rngB2B3); sheet6.getRange("$B$5").setFormula2("=MAKEARRAY(9,9,LAMBDA(row,col,row&\"*\"&col&\" = \"&row*col))"); sheet6.getRange("$B$2").getFont().setBold(true); IFont sheet6Font1 = sheet6.getRange("$B$3").getFont(); sheet6Font1.setThemeColor(ThemeColor.Accent1); sheet6Font1.setItalic(true); // #endregion // #region ISOMITTED IWorksheet sheet7 = workbook.getWorksheets().add(); sheet7.setName("ISOMITTED"); Object[][] sheet7rngB2B3 = new Object[][] { {"Check for a missing parameter and return a friendly string"}, {"Formula: =LAMBDA(x,y,IF(ISOMITTED(y),\"Missing second argument\",x+y))(1,)"} }; sheet7.getRange("$B$2:$B$3").setValue(sheet7rngB2B3); String rngB5 = "Result:"; sheet7.getRange("$B$5").setValue(rngB5); sheet7.getRange("$C$5").setFormula2("=LAMBDA(x,y,IF(ISOMITTED(y),\"Missing second argument\",x+y))(1,)"); sheet7.getRange("$B$2").getFont().setBold(true); IFont sheet7Font1 = sheet7.getRange("$B$3").getFont(); sheet7Font1.setThemeColor(ThemeColor.Accent1); sheet7Font1.setItalic(true); // #endregion workbook.getWorksheets().get("Sheet1").delete(); // Save to an excel file workbook.save("LambdaAdditionalFunction.xlsx");