// 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");
// Create a new workbook var workbook = Workbook() //#region BYROW val sheet1 = workbook.worksheets.add() sheet1.name = "BYROW" val sheet1rngB2B3 = arrayOf(arrayOf("Get row totals"), arrayOf("Formula: =BYROW(C6:E10,LAMBDA(row,SUM(row)))")) sheet1.getRange("\$B$2:\$B$3").value = sheet1rngB2B3 val sheet1rngB5E10 = arrayOf( arrayOf("Student", "Math", "English", "Physics"), arrayOf("Tom", 19.0, 23.0, 19.0), arrayOf("Jerry", 21.0, 15.0, 18.0), arrayOf("Mario", 19.0, 22.0, 22.0), arrayOf("Luigi", 17.0, 15.0, 23.0), arrayOf("Galen", 16.0, 22.0, 24.0) ) sheet1.getRange("\$B$5:\$E$10").value = sheet1rngB5E10 val sheet1rngG5 = "Total" sheet1.getRange("\$G$5").value = sheet1rngG5 sheet1.getRange("\$G$6").formula2 = "=BYROW(C6:E10,LAMBDA(row,SUM(row)))" sheet1.getRange("\$B$2").font.bold = true val sheet1Font1 = sheet1.getRange("\$B$3").font sheet1Font1.themeColor = ThemeColor.Accent1 sheet1Font1.italic = true val sheet1Font2 = sheet1.getRange("\$B$5:\$E$5,\$G$5").font sheet1Font2.themeColor = ThemeColor.Light1 sheet1Font2.bold = true val sheet1Interior1 = sheet1.getRange("\$B$5:\$E$5,\$G$5").interior sheet1Interior1.themeColor = ThemeColor.Accent1 sheet1Interior1.pattern = Pattern.Solid sheet1Interior1.patternThemeColor = ThemeColor.Accent1 val sheet1Interior2 = sheet1.getRange("\$B$6:\$E$6,\$G$6,\$B$8:\$E$8,\$G$8,\$B$10:\$E$10,\$G$10").interior sheet1Interior2.themeColor = ThemeColor.Accent1 sheet1Interior2.pattern = Pattern.Solid sheet1Interior2.tintAndShade = 0.8 val sheet1Range1 = sheet1.getRange("B5:E10,G5:G10") sheet1Range1.borders[BordersIndex.EdgeTop].lineStyle = BorderLineStyle.DashDot sheet1Range1.borders[BordersIndex.EdgeLeft].lineStyle = BorderLineStyle.DashDot sheet1Range1.borders[BordersIndex.EdgeRight].lineStyle = BorderLineStyle.DashDot sheet1Range1.borders[BordersIndex.EdgeBottom].lineStyle = BorderLineStyle.DashDot sheet1Range1.borders[BordersIndex.InsideHorizontal].lineStyle = BorderLineStyle.DashDot sheet1Range1.borders[BordersIndex.InsideVertical].lineStyle = BorderLineStyle.DashDot // #endregion // #region val sheet2 = workbook.worksheets.add() sheet2.name = "BYCOL" val sheet2rngB2B3 = arrayOf(arrayOf("Get column totals"), arrayOf("Formula: =BYCOL(C6:E10,LAMBDA(col,SUM(col)))")) sheet2.getRange("\$B$2:\$B$3").value = sheet2rngB2B3 val sheet2rngB5E10 = arrayOf( arrayOf("Student", "Math", "English", "Physics"), arrayOf("Tom", 19.0, 23.0, 19.0), arrayOf("Jerry", 21.0, 15.0, 18.0), arrayOf("Mario", 19.0, 22.0, 22.0), arrayOf("Luigi", 17.0, 15.0, 23.0), arrayOf("Galen", 16.0, 22.0, 24.0) ) sheet2.getRange("\$B$5:\$E$10").value = sheet2rngB5E10 val sheet2rngB12 = "Column Total" sheet2.getRange("\$B$12").value = sheet2rngB12 sheet2.getRange("\$C$12").formula2 = "=BYCOL(C6:E10,LAMBDA(col,SUM(col)))" sheet2.getRange("\$B$2").font.bold = true val sheet2Font1 = sheet2.getRange("\$B$3").font sheet2Font1.themeColor = ThemeColor.Accent1 sheet2Font1.italic = true val sheet2Font2 = sheet2.getRange("\$B$5:\$E$5").font sheet2Font2.themeColor = ThemeColor.Light1 sheet2Font2.bold = true val sheet2Interior1 = sheet2.getRange("\$B$5:\$E$5").interior sheet2Interior1.themeColor = ThemeColor.Accent1 sheet2Interior1.pattern = Pattern.Solid sheet2Interior1.patternThemeColor = ThemeColor.Accent1 val sheet2Interior2 = sheet2.getRange("\$B$6:\$E$6,\$B$8:\$E$8,\$B$10:\$E$10,\$B$12:\$E$12").interior sheet2Interior2.themeColor = ThemeColor.Accent1 sheet2Interior2.pattern = Pattern.Solid sheet2Interior2.tintAndShade = 0.8 sheet2.getRange("\$B:\$B").columnWidthInPixel = 103.0 sheet2.getRange("\$E:\$E").columnWidthInPixel = 74.0 val sheet2Range1 = sheet2.getRange("B5:E10,B12:E12") sheet2Range1.borders[BordersIndex.EdgeTop].lineStyle = BorderLineStyle.DashDot sheet2Range1.borders[BordersIndex.EdgeLeft].lineStyle = BorderLineStyle.DashDot sheet2Range1.borders[BordersIndex.EdgeRight].lineStyle = BorderLineStyle.DashDot sheet2Range1.borders[BordersIndex.EdgeBottom].lineStyle = BorderLineStyle.DashDot sheet2Range1.borders[BordersIndex.InsideHorizontal].lineStyle = BorderLineStyle.DashDot sheet2Range1.borders[BordersIndex.InsideVertical].lineStyle = BorderLineStyle.DashDot // #endregion // #region SCAN val sheet3 = workbook.worksheets.add() sheet3.name = "SCAN" val sheet3rngB2B3 = arrayOf( arrayOf("Create a list of factorials"), arrayOf("Formula: =SCAN(1,B6:D7,LAMBDA(a,b,a*b))") ) sheet3.getRange("\$B$2:\$B$3").value = sheet3rngB2B3 val sheet3rngB5D7 = arrayOf(arrayOf("Data", null, null), arrayOf(1.0, 2.0, 3.0), arrayOf(4.0, 5.0, 6.0)) sheet3.getRange("\$B$5:\$D$7").value = sheet3rngB5D7 val sheet3rngF5H5 = arrayOf(arrayOf("Result:", null, null)) sheet3.getRange("\$F$5:\$H$5").value = sheet3rngF5H5 sheet3.getRange("\$F$6").formula2 = "=SCAN(1,B6:D7,LAMBDA(a,b,a*b))" sheet3.getRange("\$B$2").font.bold = true val sheet3Font1 = sheet3.getRange("\$B$3").font sheet3Font1.themeColor = ThemeColor.Accent1 sheet3Font1.italic = true val sheet3Font2 = sheet3.getRange("\$B$5:\$D$5,\$F$5:\$H$5").font sheet3Font2.themeColor = ThemeColor.Light1 sheet3Font2.bold = true val sheet3Font3 = sheet3.getRange("\$B$5:\$D$5,\$F$5:\$H$5").interior sheet3Font3.themeColor = ThemeColor.Accent1 sheet3Font3.pattern = Pattern.Solid sheet3Font3.patternThemeColor = ThemeColor.Accent1 val sheet3Font4 = sheet3.getRange("\$B$6:\$D$6,\$F$6:\$H$6").interior sheet3Font4.themeColor = ThemeColor.Accent1 sheet3Font4.pattern = Pattern.Solid sheet3Font4.tintAndShade = 0.8 sheet3.getRange("\$B$5:\$D$5").merge() sheet3.getRange("\$F$5:\$H$5").merge() sheet3.getRange("\$C:\$D").columnWidthInPixel = 84.0 val sheet3Range1 = sheet3.getRange("B5:D7,F5:H7") sheet3Range1.borders[BordersIndex.EdgeTop].lineStyle = BorderLineStyle.DashDot sheet3Range1.borders[BordersIndex.EdgeLeft].lineStyle = BorderLineStyle.DashDot sheet3Range1.borders[BordersIndex.EdgeRight].lineStyle = BorderLineStyle.DashDot sheet3Range1.borders[BordersIndex.EdgeBottom].lineStyle = BorderLineStyle.DashDot sheet3Range1.borders[BordersIndex.InsideHorizontal].lineStyle = BorderLineStyle.DashDot sheet3Range1.borders[BordersIndex.InsideVertical].lineStyle = BorderLineStyle.DashDot // #endregion // #region REDUCE val sheet4 = workbook.worksheets.add() sheet4.name = "REDUCE" val sheet4rngB2B3 = arrayOf(arrayOf("Get cumulative results"), arrayOf("Formula: =REDUCE(1,B6:D7,LAMBDA(a,b,a*b))")) sheet4.getRange("\$B$2:\$B$3").value = sheet4rngB2B3 val sheet4rngB5D7 = arrayOf(arrayOf("Data", null, null), arrayOf(1.0, 2.0, 3.0), arrayOf(4.0, 5.0, 6.0)) sheet4.getRange("\$B$5:\$D$7").value = sheet4rngB5D7 val rngF5 = "Result:" sheet4.getRange("\$F$5").value = rngF5 sheet4.getRange("\$G$5").formula2 = "=REDUCE(1,B6:D7,LAMBDA(a,b,a*b))" sheet4.getRange("\$B$2").font.bold = true val sheet4Font1 = sheet4.getRange("\$B$3").font sheet4Font1.themeColor = ThemeColor.Accent1 sheet4Font1.italic = true val sheet4Font2 = sheet4.getRange("\$B$5:\$D$5").font sheet4Font2.themeColor = ThemeColor.Light1 sheet4Font2.bold = true val sheet4Interior1 = sheet4.getRange("\$B$5:\$D$5").interior sheet4Interior1.themeColor = ThemeColor.Accent1 sheet4Interior1.pattern = Pattern.Solid sheet4Interior1.patternThemeColor = ThemeColor.Accent1 val sheet4Interior2 = sheet4.getRange("\$B$6:\$D$6").interior sheet4Interior2.themeColor = ThemeColor.Accent1 sheet4Interior2.pattern = Pattern.Solid sheet4Interior2.tintAndShade = 0.8 sheet4.getRange("\$B$5:\$D$5").merge() val sheet4Range1 = sheet4.getRange("B5:D7") sheet4Range1.borders[BordersIndex.EdgeTop].lineStyle = BorderLineStyle.DashDot sheet4Range1.borders[BordersIndex.EdgeLeft].lineStyle = BorderLineStyle.DashDot sheet4Range1.borders[BordersIndex.EdgeRight].lineStyle = BorderLineStyle.DashDot sheet4Range1.borders[BordersIndex.EdgeBottom].lineStyle = BorderLineStyle.DashDot sheet4Range1.borders[BordersIndex.InsideHorizontal].lineStyle = BorderLineStyle.DashDot sheet4Range1.borders[BordersIndex.InsideVertical].lineStyle = BorderLineStyle.DashDot // #endregion // #region MAP val sheet5 = workbook.worksheets.add() sheet5.name = "MAP" val sheet5rngB2B3 = arrayOf( arrayOf("Square numbers above a vertain value"), arrayOf("Formula: =MAP(B6:D7,LAMBDA(a,IF(a>4,a*a,a*1)))") ) sheet5.getRange("\$B$2:\$B$3").value = sheet5rngB2B3 val sheet5rngB5D7 = arrayOf(arrayOf("Data", null, null), arrayOf(1.0, 2.0, 3.0), arrayOf(4.0, 5.0, 6.0)) sheet5.getRange("\$B$5:\$D$7").value = sheet5rngB5D7 val rngF5H5 = arrayOf(arrayOf("Result:", null, null)) sheet5.getRange("\$F$5:\$H$5").value = rngF5H5 sheet5.getRange("\$F$6").formula2 = "=MAP(B6:D7,LAMBDA(a,IF(a>4,a*a,a*1)))" sheet5.getRange("\$B$2").font.bold = true val sheet5Font1 = sheet5.getRange("\$B$3").font sheet5Font1.themeColor = ThemeColor.Accent1 sheet5Font1.italic = true val sheet5Font2 = sheet5.getRange("\$B$5:\$D$5,\$F$5:\$H$5").font sheet5Font2.themeColor = ThemeColor.Light1 sheet5Font2.bold = true val sheet5Interior1 = sheet5.getRange("\$B$5:\$D$5,\$F$5:\$H$5").interior sheet5Interior1.themeColor = ThemeColor.Accent1 sheet5Interior1.pattern = Pattern.Solid sheet5Interior1.patternThemeColor = ThemeColor.Accent1 val sheet5Interior2 = sheet5.getRange("\$B$6:\$D$6,\$F$6:\$H$6").interior sheet5Interior2.themeColor = ThemeColor.Accent1 sheet5Interior2.pattern = Pattern.Solid sheet5Interior2.tintAndShade = 0.8 sheet5.getRange("\$B$5:\$D$5").merge() sheet5.getRange("\$F$5:\$H$5").merge() val sheet5Range1 = sheet5.getRange("B5:D7,F5:H7") sheet5Range1.borders[BordersIndex.EdgeTop].lineStyle = BorderLineStyle.DashDot sheet5Range1.borders[BordersIndex.EdgeLeft].lineStyle = BorderLineStyle.DashDot sheet5Range1.borders[BordersIndex.EdgeRight].lineStyle = BorderLineStyle.DashDot sheet5Range1.borders[BordersIndex.EdgeBottom].lineStyle = BorderLineStyle.DashDot sheet5Range1.borders[BordersIndex.InsideHorizontal].lineStyle = BorderLineStyle.DashDot sheet5Range1.borders[BordersIndex.InsideVertical].lineStyle = BorderLineStyle.DashDot // #endregion // #region MAKEARRAY val sheet6 = workbook.worksheets.add() sheet6.name = "MAKEARRAY" val sheet6rngB2B3 = arrayOf( arrayOf("Multiplication table"), arrayOf("Formula: =MAKEARRAY(9,9,LAMBDA(row,col,row&\"*\"&col&\" = \"&row*col))") ) sheet6.getRange("\$B$2:\$B$3").value = sheet6rngB2B3 sheet6.getRange("\$B$5").formula2 = "=MAKEARRAY(9,9,LAMBDA(row,col,row&\"*\"&col&\" = \"&row*col))" sheet6.getRange("\$B$2").font.bold = true val sheet6Font1 = sheet6.getRange("\$B$3").font sheet6Font1.themeColor = ThemeColor.Accent1 sheet6Font1.italic = true // #endregion // #region ISOMITTED val sheet7 = workbook.worksheets.add() sheet7.name = "ISOMITTED" val sheet7rngB2B3 = arrayOf( arrayOf("Check for a missing parameter and return a friendly string"), arrayOf("Formula: =LAMBDA(x,y,IF(ISOMITTED(y),\"Missing second argument\",x+y))(1,)") ) sheet7.getRange("\$B$2:\$B$3").value = sheet7rngB2B3 val rngB5 = "Result:" sheet7.getRange("\$B$5").value = rngB5 sheet7.getRange("\$C$5").formula2 = "=LAMBDA(x,y,IF(ISOMITTED(y),\"Missing second argument\",x+y))(1,)" sheet7.getRange("\$B$2").font.bold = true val sheet7Font1 = sheet7.getRange("\$B$3").font sheet7Font1.themeColor = ThemeColor.Accent1 sheet7Font1.italic = true // #endregion workbook.worksheets["Sheet1"].delete() // Save to an excel file workbook.save("LambdaAdditionalFunction.xlsx")