// Create a new workbook Workbook workbook = new Workbook(); workbook.getNames().add("user", "='Case 3'!$C$4"); // Case 1 IWorksheet ws = workbook.getActiveSheet(); ws.setName("Use case"); Object[][] rngB2E7 = new Object[][] { {"=LET(data,Table1[Salary],calc,IFS(C9=\"Min\", 1,C9=\"Max\",2,C9=\"Average\",3),aggregate,CHOOSE(calc,5,4,1),SUBTOTAL(aggregate,data))", null, null, null}, {"Support Engineer", "Start Date", "Time with Company", "Salary"}, {"Bob", 41784d, "5+ years", 2790d}, {"Jim", 43666d, "1-4 years", 2216d}, {"Kevin", 42795d, "1-4 years", 2498d}, {"Sarah", 44026d, "1-4 years", 1989d} }; ws.getRange("B1").setValue("Use Let to make the formula easy to understand"); ws.getRange("$B$2:$E$7").setValue(rngB2E7); Object[][] rngB9C9 = new Object[][] { {"Salary Calculations", "Max"} }; ws.getRange("$B$9:$C$9").setValue(rngB9C9); ITable table1 = ws.getTables().add(ws.getRange("$B$3:$E$7"), true); table1.setTableStyle(ws.getWorkbook().getTableStyles().get("TableStyleLight2")); ws.getRange("$D$9").setFormula2("=LET(data,Table1[Salary],calc,IFS(C9=\"Min\", 1,C9=\"Max\",2,C9=\"Average\",3),aggregate,CHOOSE(calc,5,4,1),SUBTOTAL(aggregate,data))"); ws.getRange("$B$1").getFont().setBold(true); IFont range1 = ws.getRange("$B$9").getFont(); range1.setBold(true); range1.setColor(Color.GetWhite()); IInterior range2 = ws.getRange("$B$9").getInterior(); range2.setThemeColor(ThemeColor.Accent1); IInterior range3 = ws.getRange("$C$9:$D$9").getInterior(); range3.setThemeColor(ThemeColor.Accent1); range3.setTintAndShade(0.6); IFont sheet2B2 = ws.getRange("$B$2").getFont(); sheet2B2.setBold(true); sheet2B2.setColor(Color.FromArgb((int) 0xFFC00000)); sheet2B2.setName("Consolas"); sheet2B2.setSize(9d); ws.getRange("B:E").setColumnWidthInPixel(100d); ws.getRange("C9").getValidation().add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Between, "Min,Max,Average", null); // Case 2 IWorksheet sheet2 = workbook.getWorksheets().add(); sheet2.setName("Case 2"); String rngB2 = "LET basic usage"; sheet2.getRange("$B$2").setValue(rngB2); Object[][] rngB4B6 = new Object[][] { {"Sample pair varible"}, {"=LET(x,2,x+3)"}, {"Result"} }; sheet2.getRange("$B$4:$B$6").setValue(rngB4B6); Object[][] rngB8B10 = new Object[][] { {"Multiple pair varibles"}, {"=LET(x,1,y,2,z,3,x+y+z=x*y*z)"}, {"Result"} }; sheet2.getRange("$B$8:$B$10").setValue(rngB8B10); Object[][] rngB12B14 = new Object[][] { {"Invoked formula"}, {"=LET(x,1,y,2,SUM(x,y))"}, {"Result"} }; sheet2.getRange("$B$12:$B$14").setValue(rngB12B14); Object[][] rngB16B19 = new Object[][] { {"Dynamic Array"}, {"=LET(rows,0,cols,0,OFFSET(C18:E18,rows,cols))"}, {"Range"}, {"Result"} }; sheet2.getRange("$B$16:$B$19").setValue(rngB16B19); double rngC18 = 1d; sheet2.getRange("$C$18").setValue(rngC18); Object[][] rngD18E18 = new Object[][] { {2d, 3d} }; sheet2.getRange("$D$18:$E$18").setValue(rngD18E18); sheet2.getRange("$C$6").setFormula2("=LET(x,2,x+3)"); sheet2.getRange("$C$10").setFormula2("=LET(x,1,y,2,z,3,x+y+z=x*y*z)"); sheet2.getRange("$C$14").setFormula2("=LET(x,1,y,2,SUM(x,y))"); sheet2.getRange("$C$19").setFormula2("=LET(rows,0,cols,0,OFFSET(C18:E18,rows,cols))"); IFont range4 = sheet2.getRange("$B$2").getFont(); range4.setBold(true); range4.setColor(Color.FromArgb((int) 0xFF172B4D)); range4.setSize(12d); IFont range5 = sheet2.getRange("$B$4,$B$8,$B$12,$B$16").getFont(); range5.setBold(true); range5.setSize(9d); IFont range6 = sheet2.getRange("$B$5,$B$9,$B$13,$B$17").getFont(); range6.setThemeFont(ThemeFont.None); range6.setBold(true); range6.setColor(Color.FromArgb((int) 0xFFC00000)); range6.setName("Consolas"); range6.setSize(9d); IInterior range7 = sheet2.getRange("$B$6,$B$10,$B$14,$B$19").getInterior(); range7.setThemeColor(ThemeColor.Accent1); IFont range7Font = sheet2.getRange("$B$6,$B$10,$B$14,$B$19").getFont(); range7Font.setColor(Color.GetWhite()); range7Font.setBold(true); IInterior range8 = sheet2.getRange("$B$18").getInterior(); range8.setThemeColor(ThemeColor.Accent1); range8.setTintAndShade(0.6); // Case 3 IWorksheet sheet3 = workbook.getWorksheets().add(); sheet3.setName("Case 3"); Object[][] rngB2B4 = new Object[][] { {"LET local varible & custom name"}, {"This sheet includes a custom name \"user\" which be assigned \"White\" value"}, {"user"} }; sheet3.getRange("$B$2:$B$4").setValue(rngB2B4); String rngC4 = "White"; sheet3.getRange("$C$4").setValue(rngC4); Object[][] rngB6B8 = new Object[][] { {"Always use let local varible first"}, {"=LET(user,\"Andy\",\"The actual user is: \"&user)"}, {"Result"} }; sheet3.getRange("$B$6:$B$8").setValue(rngB6B8); Object[][] rngB10B12 = new Object[][] { {"Use custom name if not avalible local varible"}, {"=LET(user,user,\"The actual user is: \"&user)"}, {"Result"} }; sheet3.getRange("$B$10:$B$12").setValue(rngB10B12); sheet3.getRange("$C$8").setFormula2("=LET(user,\"Andy\",\"The actual user is: \"&user)"); sheet3.getRange("$C$12").setFormula2("=LET(user,user,\"The actual user is: \"&user)"); IFont range10 = sheet3.getRange("$B$2").getFont(); range10.setBold(true); range10.setColor(Color.FromArgb((int) 0xFF172B4D)); range10.setSize(12d); IFont range11 = sheet3.getRange("$B$3").getFont(); range11.setBold(true); range11.setColor(Color.GetGray()); range11.setName("Segoe UI"); range11.setSize(9d); IFont range12 = sheet3.getRange("$B$6,$B$10").getFont(); range12.setThemeFont(ThemeFont.None); range12.setBold(true); range12.setColorIndex(-4105); range12.setName("Segoe UI"); range12.setSize(9d); IFont range13 = sheet3.getRange("$B$7,$B$11").getFont(); range13.setThemeFont(ThemeFont.None); range13.setBold(true); range13.setColor(Color.FromArgb((int) 0xFFC00000)); range13.setName("Consolas"); range13.setSize(9d); IInterior range14 = sheet3.getRange("$B$4").getInterior(); range14.setThemeColor(ThemeColor.Accent1); range14.setTintAndShade(0.6); IInterior range15 = sheet3.getRange("$B$8,$B$12").getInterior(); range15.setThemeColor(ThemeColor.Accent1); IFont range15Font = sheet3.getRange("$B$8,$B$12").getFont(); range15Font.setColor(Color.GetWhite()); range15Font.setBold(true); // Case 4 IWorksheet sheet4 = workbook.getWorksheets().add(); sheet4.setName("Case 4"); String sheet4B2 = "LET nested"; sheet4.getRange("$B$2").setValue(sheet4B2); Object[][] sheet4B4B6 = new Object[][] { {"Always use the current scope varible"}, {"=LET(IFont,\"First scope\",LET(IFont,\"Second scope\",IFont))"}, {"Result"} }; sheet4.getRange("$B$4:$B$6").setValue(sheet4B4B6); Object[][] sheet4B8B10 = new Object[][] { {"Use the top scope varible if not found the avalible varible in current scope"}, {"=LET(IFont,\"First scope\",LET(IFont,IFont,IFont&\" [from the second scope]\"))"}, {"Result"} }; sheet4.getRange("$B$8:$B$10").setValue(sheet4B8B10); sheet4.getRange("$C$6").setFormula2("=LET(IFont,\"First scope\",LET(IFont,\"Second scope\",IFont))"); sheet4.getRange("$C$10").setFormula2("=LET(IFont,\"First scope\",LET(IFont,IFont,IFont&\" [from the second scope]\"))"); IFont range41 = sheet4.getRange("$B$2").getFont(); range41.setBold(true); range41.setColor(Color.FromArgb((int) 0xFF172B4D)); range41.setSize(12d); IFont range42 = sheet4.getRange("$B$4,$B$8").getFont(); range42.setBold(true); range42.setSize(9d); IFont range43 = sheet4.getRange("$B$5,$B$9").getFont(); range43.setBold(true); range43.setColor(Color.FromArgb((int) 0xFFC00000)); range43.setName("Consolas"); range43.setSize(9d); IInterior range44 = sheet4.getRange("$B$6,$B$10").getInterior(); range44.setThemeColor(ThemeColor.Accent1); IFont range44Font = sheet4.getRange("$B$6,$B$10").getFont(); range44Font.setColor(Color.GetWhite()); range44Font.setBold(true); // Case 5 IWorksheet sheet5 = workbook.getWorksheets().add(); sheet5.setName("Case 5"); String sheet5B2 = "LET simplify the complex formula"; sheet5.getRange("$B$2").setValue(sheet5B2); Object[][] sheet5B4B13 = new Object[][] { {"Filter the data to show one person"}, {"=LET(filterCriteria,H7,filteredRange,FILTER(B7:E13,B7:B13=filterCriteria),IF(ISBLANK(filteredRange),\"-\",filteredRange))"}, {"Rep"}, {"Amy"}, {"Fred"}, {"Amy"}, {"Fred"}, {"Fred"}, {"Amy"}, {"Fred"} }; sheet5.getRange("$B$4:$B$13").setValue(sheet5B4B13); Object[][] sheet5C6C13 = new Object[][] { {"Region"}, {"East"}, {"South"}, {"West"}, {"North"}, {"West"}, {"East"}, {"North"} }; sheet5.getRange("$C$6:$C$13").setValue(sheet5C6C13); Object[][] rngD6D9 = new Object[][] { {"Product"}, {"Apple"}, {"Banana"}, {"Mango"} }; sheet5.getRange("$D$6:$D$9").setValue(rngD6D9); Object[][] rngE6E13 = new Object[][] { {"Profit"}, {1.33d}, {0.09d}, {1.85d}, {0.82d}, {1.25d}, {0.72d}, {0.54d} }; sheet5.getRange("$E$6:$E$13").setValue(rngE6E13); Object[][] rngG7G8 = new Object[][] { {"Rep"}, {"Result"} }; sheet5.getRange("$G$7:$G$8").setValue(rngG7G8); String rngH7 = "Fred"; sheet5.getRange("$H$7").setValue(rngH7); Object[][] rngD11D13 = new Object[][] { {"Banana"}, {"Apple"}, {"Mango"} }; sheet5.getRange("$D$11:$D$13").setValue(rngD11D13); Object[][] sheet5B16B19 = new Object[][] { {"Generate all dates between May 1, 2020 and May 15, 2020"}, {"=LET(dates,SEQUENCE(C19-C18+1,1,C18,1),FILTER(dates,WEEKDAY(dates,2)<6))"}, {"Start"}, {"End"} }; sheet5.getRange("$B$16:$B$19").setValue(sheet5B16B19); Object[][] rngC18C19 = new Object[][] { {new GregorianCalendar(2020, 5, 1)}, {new GregorianCalendar(2020, 5, 15)} }; sheet5.getRange("$C$18:$C$19").setValue(rngC18C19); String rngG18 = "Result"; sheet5.getRange("$G$18").setValue(rngG18); sheet5.getRange("$H$8").setFormula2("=LET(filterCriteria,H7,filteredRange,FILTER(B7:E13,B7:B13=filterCriteria),IF(ISBLANK(filteredRange),\"-\",filteredRange))"); sheet5.getRange("$H$18").setFormula2("=LET(dates,SEQUENCE(C19-C18+1,1,C18,1),FILTER(dates,WEEKDAY(dates,2)<6))"); sheet5.getRange("$C$18:$C$19,$H$18:$H$28").setNumberFormat("yyyy/m/d"); IFont sheet5Range2 = sheet5.getRange("$B$2").getFont(); sheet5Range2.setBold(true); sheet5Range2.setColor(Color.FromArgb((int) 0xFF172B4D)); sheet5Range2.setSize(12d); IFont range51 = sheet5.getRange("$B$4,$B$16").getFont(); range51.setBold(true); range51.setName("Segoe UI"); range51.setSize(9d); IFont sheet5Range3 = sheet5.getRange("$B$5,$B$17").getFont(); sheet5Range3.setBold(true); sheet5Range3.setColor(Color.FromArgb((int) 0xFFC00000)); sheet5Range3.setName("Consolas"); sheet5Range3.setSize(9d); IFont sheet5Range4 = sheet5.getRange("$B$6:$E$6,$B$18:$B$19").getFont(); sheet5Range4.setBold(true); IInterior range52 = sheet5.getRange("$B$6:$E$6,$B$18:$B$19").getInterior(); range52.setColor(Color.FromArgb((int) 0xFFD9E1F2)); range52.setPattern(Pattern.Solid); IInterior sheet5Range5 = sheet5.getRange("$G$7").getInterior(); sheet5Range5.setThemeColor(ThemeColor.Accent1); IFont sheet5Range5Font = sheet5.getRange("$G$7").getFont(); sheet5Range5Font.setColor(Color.GetWhite()); sheet5Range5Font.setBold(true); IInterior sheet5Range6 = sheet5.getRange("$G$8,$G$18").getInterior(); sheet5Range6.setThemeColor(ThemeColor.Accent1); sheet5Range6.setTintAndShade(0.6); sheet5.getRange("$C:$C").autoFit(); sheet5.getRange("$H:$H").setColumnWidth(10); // Save to an excel file workbook.save("LetFunction.xlsx");