Examples

Here are some examples of LAMBDA functions

Here are some examples of LAMBDA functions The first sheet gives some examples of recursive Lambda functions. The second sheet and the third sheet give two more complicated examples.
var data = { Recursive: { values: { 1: { 1: "Examples" }, 3: { 1: "Example 1: Compute a factorial" }, 4: { 1: "Define the following in Name Manager:" }, 5: { 1: "Name:", 2: "myFact" }, 6: { 1: "Scope:", 2: "Workbook" }, 7: { 1: "Comment:", 2: "Computes the factorial of a number" }, 8: { 1: "Refers To:", 2: "=LAMBDA(num,\r\n IF(num<2,\r\n 1,\r\n num * myFact(num - 1)\r\n )\r\n)", }, 14: { 1: "Data", 2: "Formula", 3: "Result" }, 15: { 1: 4 }, 16: { 1: 16 }, 17: { 1: 52 }, 19: { 1: "Example 2: Replace characters in a string" }, 20: { 1: "Define the following in Name Manager:" }, 21: { 1: "Name:", 2: "ReplaceChars" }, 22: { 1: "Scope:", 2: "Workbook" }, 23: { 1: "Comment:", 2: "Replaces the specified characters in a string" }, 24: { 1: "Refers To:", 2: '=LAMBDA(str, chars, sub\r\n IF(chars="",\r\n str,\r\n ReplaceChars(\r\n SUBSTITUTE(str, LEFT(chars), sub),\r\n MID(chars,2,LEN(chars) - 1),\r\n sub\r\n )\r\n )\r\n)', }, 34: { 1: "Data", 2: "Formula", 3: "Result" }, 35: { 1: "WARNING!! <script>" }, 36: { 1: "#4 & #7 + $803*" }, 37: { 1: "Generally (#25) free" }, 39: { 1: "Example 3: Reverse a string value" }, 40: { 1: "3a. Define the following in Name Manager:" }, 41: { 1: "Name:", 2: "HEAD" }, 42: { 1: "Scope:", 2: "Workbook" }, 43: { 1: "Comment:", 2: "Returns the first character of a string" }, 44: { 1: "Refers To:", 2: '=LAMBDA(str,\r\n IF(str="",\r\n "",\r\n LEFT(str, 1)\r\n )\r\n)', }, 50: { 1: "3b. Define the following in Name Manager:" }, 51: { 1: "Name:", 2: "TAIL" }, 52: { 1: "Scope:", 2: "Workbook" }, 53: { 1: "Comment:", 2: "Returns the string minus the 1st char" }, 54: { 1: "Refers To:", 2: '=LAMBDA(str,\r\n IF(str="",\r\n "",\r\n RIGHT(str, LEN(str) - 1)\r\n )\r\n)', }, 60: { 1: "3c. Define the following in Name Manager:" }, 61: { 1: "Name:", 2: "REVERSE" }, 62: { 1: "Scope:", 2: "Workbook" }, 63: { 1: "Comment:", 2: "Returns the string in reverse order" }, 64: { 1: "Refers To:", 2: "=LAMBDA(str,\r\n IF(LEN(str)<2,\r\n str,\r\n REVERSE(TAIL(str)) & HEAD(str)\r\n )\r\n)", }, 70: { 1: "Data", 2: "Formula", 3: "Result" }, 71: { 1: "palindrome" }, 72: { 1: "backwards" }, 73: { 1: "forwards" }, 75: { 1: "Example 4: Check whether a string is a palindrome" }, 76: { 1: "Define the following in Name Manager:" }, 77: { 1: "Name:", 2: "IsPalindrome" }, 78: { 1: "Scope:", 2: "Workbook" }, 79: { 1: "Comment:", 2: "Returns TRUE if the string is a palindrome" }, 80: { 1: "Refers To:", 2: '=LAMBDA(str, \r\n LET(\r\n replaceStr, ReplaceChars(str, "!@#$%^&*()[]<>-?.,\'"" ", ""),\r\n lowStr, LOWER(replaceStr),\r\n lowStr = REVERSE(lowStr)\r\n )\r\n)', }, 87: { 1: "Data", 2: "Formula", 3: "Result" }, 88: { 1: "I, man, am Regal, a German am I" }, 89: { 1: "Never odd or even" }, 90: { 1: "If I had a Hi-Fi" }, 91: { 1: "Madam, I'm Adam" }, 92: { 1: "Too hot to hoot" }, 93: { 1: "No lemons, no melon" }, 94: { 1: "Too bad I hid a boot" }, 95: { 1: "Lisa Bonet ate no basil" }, 96: { 1: "Warsaw was raw" }, 97: { 1: "Was it a car or a cat I saw?" }, }, formulas: { 15: { 2: "FORMULATEXT(D16)", 3: "MYFACT(B16)" }, 16: { 2: "FORMULATEXT(D17)", 3: "MYFACT(B17)" }, 17: { 2: "FORMULATEXT(D18)", 3: "MYFACT(B18)" }, 35: { 2: "FORMULATEXT(D36)", 3: 'REPLACECHARS(B36,"!@#$%^&*()[]<>-?.,","")' }, 36: { 2: "FORMULATEXT(D37)", 3: 'REPLACECHARS(B37,"!@#$%^&*()[]<>-?.,","")' }, 37: { 2: "FORMULATEXT(D38)", 3: 'REPLACECHARS(B38,"!@#$%^&*()[]<>-?.,","")' }, 71: { 2: "FORMULATEXT(D72)", 3: "REVERSE(B72)" }, 72: { 2: "FORMULATEXT(D73)", 3: "REVERSE(B73)" }, 73: { 2: "FORMULATEXT(D74)", 3: "REVERSE(B74)" }, 88: { 2: "FORMULATEXT(D89)", 3: "ISPALINDROME(B89)" }, 89: { 2: "FORMULATEXT(D90)", 3: "ISPALINDROME(B90)" }, 90: { 2: "FORMULATEXT(D91)", 3: "ISPALINDROME(B91)" }, 91: { 2: "FORMULATEXT(D92)", 3: "ISPALINDROME(B92)" }, 92: { 2: "FORMULATEXT(D93)", 3: "ISPALINDROME(B93)" }, 93: { 2: "FORMULATEXT(D94)", 3: "ISPALINDROME(B94)" }, 94: { 2: "FORMULATEXT(D95)", 3: "ISPALINDROME(B95)" }, 95: { 2: "FORMULATEXT(D96)", 3: "ISPALINDROME(B96)" }, 96: { 2: "FORMULATEXT(D97)", 3: "ISPALINDROME(B97)" }, 97: { 2: "FORMULATEXT(D98)", 3: "ISPALINDROME(B98)" }, }, cellStyles: { B2: 0, "B4:D4": 1, B5: 2, "B6:B8": 3, "C6:C8": 4, "D6:D8": 5, B9: 6, C9: 7, D9: 8, "B10:B13": 9, "C10:C13": 10, "D10:D13": 11, B14: 12, C14: 13, D14: 14, B15: 15, C15: 16, D15: 15, "B16:B18": 17, "C16:D18": 18, "B20:D20": 1, B21: 2, "B22:B24": 3, "C22:C24": 4, "D22:D24": 5, B25: 6, C25: 7, D25: 8, "B26:B33": 9, "C26:C33": 10, "D26:D33": 11, B34: 12, C34: 13, D34: 14, "B35:D35": 15, "B36:B38": 17, "C36:D38": 18, "B40:D40": 1, B41: 2, "B42:B44": 3, "C42:C44": 4, "D42:D44": 5, B45: 6, C45: 7, D45: 8, "B46:B49": 9, "C46:C49": 10, "D46:D49": 11, B50: 12, C50: 13, D50: 14, B51: 2, "B52:B54": 3, "C52:C54": 4, "D52:D54": 5, B55: 6, C55: 7, D55: 8, "B56:B59": 9, "C56:C59": 10, "D56:D59": 11, B60: 12, C60: 13, D60: 14, B61: 2, "B62:B64": 19, "C62:C64": 20, "D62:D64": 5, B65: 21, "C65:C70": 22, "D65:D70": 23, "B66:B69": 24, B70: 25, "B71:D71": 15, "B72:B74": 17, "C72:D74": 18, "B76:D76": 1, B77: 2, "B78:B80": 19, "C78:C80": 20, "D78:D80": 5, B81: 21, "C81:C87": 22, "D81:D87": 23, "B82:B86": 24, B87: 25, "B88:D88": 15, "B89:B98": 17, "C89:C98": 26, "D89:D98": 18, A99: 27, }, styles: { records: [ { foreColor: 0, font: 0, border: [null, null, 0] }, { foreColor: 0, font: 1, border: [null, null, 1] }, { foreColor: 0, font: 1 }, { backColor: 2, foreColor: 1, font: 1, border: [2, 2, 2, 2] }, { backColor: 3, font: 1, border: [2, null, 2, 2] }, { backColor: 3, font: 1, border: [2, 2, 2] }, { backColor: 2, foreColor: 1, font: 1, border: [2, 2, null, 2] }, { backColor: 3, font: 1, wordWrap: true, border: [2, null, null, 2] }, { backColor: 3, font: 1, wordWrap: true, border: [2, 2] }, { backColor: 2, foreColor: 1, font: 1, border: [null, 2, null, 2] }, { backColor: 3, font: 1, wordWrap: true, border: [null, null, null, 2] }, { backColor: 3, font: 1, wordWrap: true, border: [null, 2] }, { backColor: 2, foreColor: 1, font: 1, border: [null, 2, 2, 2] }, { backColor: 3, font: 1, wordWrap: true, border: [null, null, 2, 2] }, { backColor: 3, font: 1, wordWrap: true, border: [null, 2, 2] }, { backColor: 4, foreColor: 1, font: 1 }, { backColor: 4, foreColor: 1, font: 1, border: [2] }, { backColor: 6, foreColor: 5, border: [3, 3, 3, 3] }, { backColor: 8, foreColor: 7, font: 1, border: [3, 3, 3, 3] }, { backColor: 2, foreColor: 1, font: 1, border: [2, null, 2, 2] }, { backColor: 3, font: 1, border: [2, null, 2] }, { backColor: 2, foreColor: 1, font: 1, border: [2, null, null, 2] }, { backColor: 3, font: 1, wordWrap: true, border: [2, null, 2] }, { backColor: 3, font: 1, wordWrap: true, border: [2, 2, 2] }, { backColor: 2, foreColor: 1, font: 1, border: [null, null, null, 2] }, { backColor: 2, foreColor: 1, font: 1, border: [null, null, 2, 2] }, { backColor: 8, foreColor: 7, font: 1, border: [3, null, 3, 3] }, { foreColor: 9 }, ], borders: [ { color: "#accdea", style: 5 }, { color: "#9bc3e6", style: 2 }, { color: "#000000", style: 1 }, { color: "#7f7f7f", style: 1 }, ], colors: ["#44546a", "#ffffff", "#4472c4", "#d9e3f2", "#5b9bd5", "#3f3f76", "#ffcc99", "#fa7d00", "#f2f2f2", "#0563c1"], fonts: ["700 17.3px Calibri", "700 14.7px Calibri"], }, others: { columnWidth: { 0: 36, 1: 172, 2: 300, 3: 141 }, rowHeight: { 1: 24, 2: 21, 3: 21, 19: 21, 39: 21, 75: 21 }, spans: ["B81:B87", "B65:B70", "C45:D50", "C55:D60", "C65:D70", "C81:D87", "C9:D14", "C25:D34"], }, }, Calendar: { values: { 0: { 1: "Calendar Function" }, 2: { 1: "This example LAMBDA function generates a calendar in a range of cells from a serial date." }, 5: { 1: "Calendar Syntax:" }, 6: { 1: "=CALENDAR(serial, mark)" }, 7: { 1: "serial: The serial date value around which the calendar is generated." }, 8: { 1: 'mark: True to mark the serial date value with "X" and false otherwise.' }, 10: { 1: "This example LAMBDA function is complicated and requires some predefined names:" }, 12: { 1: "Name:", 2: "months" }, 13: { 1: "Refers To:", 2: '={"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"}', }, 15: { 1: "Name:", 2: "days" }, 16: { 1: "Refers To:", 2: '={"Mon";"Tue";"Wed";"Thu";"Fri";"Sat";"Sun"}' }, 18: { 1: "Name:", 2: "getDay" }, 19: { 1: "Scope:", 2: "Workbook" }, 20: { 1: "Comment:", 2: "Gets the day of the week as text for a serial date." }, 21: { 1: "Refers To:", 2: "=LAMBDA(serial, INDEX(days, WEEKDAY(serial, 2),1))" }, 23: { 1: "Name:", 2: "vcat" }, 24: { 1: "Scope:", 2: "Workbook" }, 25: { 1: "Comment:", 2: "Concatenates two arrays together into one array by stacking them one on top of the other.", }, 27: { 1: "Refers To:", 2: "=LAMBDA(top,bot,\r\n LET(width, MIN(COLUMNS(top), COLUMNS(bot)),\r\n topH, ROWS(top),\r\n arrayMAKE(ROWS(bot)+topH, width,\r\n LAMBDA(i,j,\r\n IF(i <= topH,\r\n INDEX(top, i, j),\r\n INDEX(bot, i-topH, j)\r\n )\r\n )\r\n )\r\n )\r\n)", }, 41: { 1: "LAMBDA Implementation" }, 42: { 1: "Define the following in Name Manager:" }, 43: { 1: "Name:", 2: "Calendar" }, 44: { 1: "Scope:", 2: "Workbook" }, 45: { 1: "Comment:", 2: "Generates a calendar in a range of cells from a serial date." }, 46: { 1: "Refers To:", 2: '=LAMBDA(serial, mark,\r\n LET(daysInMonth, EOMONTH(serial, 0)-EOMONTH(serial, -1),\r\n foMonth, DATE(YEAR(serial), MONTH(serial), 1),\r\n dayPadding, WEEKDAY(foMonth, 2)-1,\r\n calendarRows, ROUNDUP((dayPadding+daysInMonth)/7, 0),\r\n body, arrayMAKE(calendarRows, 7,\r\n LAMBDA(i, j,\r\n LET(seqNum, ((i-1)*7+j)-dayPadding,\r\n IFS(seqNum<=0, "",\r\n seqNum=DAY(serial), IF(mark, "X", seqNum),\r\n seqNum<=daysInMonth, seqNum,\r\n TRUE, ""\r\n )\r\n )\r\n )\r\n ),\r\n vcat(\r\n arrayMAKE(1, 7,\r\n LAMBDA(i, j,\r\n IF(j=1,\r\n INDEX(months, MONTH(serial)),\r\n IF(j=2,\r\n YEAR(serial), \r\n ""\r\n )\r\n )\r\n )\r\n ),\r\n arrayMAKE(calendarRows+1, 7,\r\n LAMBDA(i, j,\r\n IF(i=1,\r\n INDEX(days, j, 1), \r\n INDEX(body, i-1, j)\r\n )\r\n )\r\n )\r\n)))', }, 84: { 1: "Example" }, 85: { 1: "Date:", 2: "2021-12-31T16:00:00.000Z", 4: "Formula:" }, 86: { 1: "Mark:", 2: false, 4: "Result:" } }, formulas: { 85: { 5: "FORMULATEXT(F87)" }, 86: { 5: "CALENDAR(C86,C87)" } }, cellStyles: { B1: 0, "B3:H4": 1, "B6:C6": 2, "B7:C7": 3, "B8:H9": 4, "B11:H12": 1, "B13:B14": 5, "C13:C14": 6, "D13:G14": 7, "H13:H14": 8, "B16:B17": 5, "C16:C17": 6, "D16:G17": 7, "H16:H17": 8, "B19:B22": 5, "C19:C22": 6, "D19:G22": 7, "H19:H22": 8, "B24:B25": 5, "C24:C25": 6, "D24:G25": 7, "H24:H25": 8, B26: 9, C26: 10, "D26:G26": 11, H26: 12, B27: 13, C27: 14, "D27:G27": 15, H27: 16, B28: 9, C28: 10, "D28:G28": 11, H28: 12, "B29:B39": 17, "C29:C39": 18, "D29:G39": 19, "H29:H39": 20, B40: 13, C40: 14, "D40:G40": 15, H40: 16, "B42:D42": 21, B43: 2, "B44:B83": 5, "C44:H45": 22, "C46:H83": 23, B85: 21, "B86:B87": 24, "C86:C87": 25, "E86:E87": 24, "F86:L94": 3, }, styles: { records: [ { font: 0 }, { foreColor: 0, font: 1, wordWrap: true }, { foreColor: 0, font: 1 }, { backColor: 2, foreColor: 1, font: 1, border: [0, 0, 0, 0] }, { backColor: 3, border: [1, 1, 1, 1] }, { backColor: 5, foreColor: 4, font: 1, border: [2, 2, 2, 2] }, { backColor: 6, font: 1, border: [2, null, 2, 2] }, { backColor: 6, font: 1, border: [2, null, 2] }, { backColor: 6, font: 1, border: [2, 2, 2] }, { backColor: 5, foreColor: 4, font: 1, border: [2, 2, null, 2] }, { backColor: 6, font: 1, wordWrap: true, border: [2, null, null, 2] }, { backColor: 6, font: 1, wordWrap: true, border: [2] }, { backColor: 6, font: 1, wordWrap: true, border: [2, 2] }, { backColor: 5, foreColor: 4, font: 1, border: [null, 2, 2, 2] }, { backColor: 6, font: 1, wordWrap: true, border: [null, null, 2, 2] }, { backColor: 6, font: 1, wordWrap: true, border: [null, null, 2] }, { backColor: 6, font: 1, wordWrap: true, border: [null, 2, 2] }, { backColor: 5, foreColor: 4, font: 1, border: [null, 2, null, 2] }, { backColor: 6, font: 1, wordWrap: true, border: [null, null, null, 2] }, { backColor: 6, font: 1, wordWrap: true }, { backColor: 6, font: 1, wordWrap: true, border: [null, 2] }, { foreColor: 0, font: 2, border: [null, null, 3] }, { backColor: 6, font: 1, border: [2, 2, 2, 2] }, { backColor: 6, font: 1, wordWrap: true, border: [2, 2, 2, 2] }, { backColor: 7, foreColor: 4, border: [2, 2, 2, 2] }, { backColor: 9, foreColor: 8, border: [0, 0, 0, 0] }, ], borders: [ { color: "#7f7f7f", style: 1 }, { color: "#b2b2b2", style: 1 }, { color: "#000000", style: 1 }, { color: "#accdea", style: 5 }, ], colors: ["#44546a", "#fa7d00", "#f2f2f2", "#ffffcc", "#ffffff", "#4472c4", "#d9e3f2", "#5b9bd5", "#3f3f76", "#ffcc99"], fonts: ["700 24px Calibri", "700 14.7px Calibri", "700 17.3px Calibri"], }, others: { columnWidth: { 0: 36, 1: 78, 2: 83 }, rowHeight: { 0: 31, 41: 24, 42: 21, 84: 24, 85: 21 }, spans: [ "C16:H16", "C17:H17", "C47:H83", "C44:H44", "C45:H45", "C13:H13", "C14:H14", "C46:H46", "B47:B83", "C24:H24", "C25:H25", "B26:B27", "C26:H27", "B28:B40", "C28:H40", "B3:H4", "B11:H12", "C19:H19", "C20:H20", "C21:H21", "C22:H22", ], }, }, NiceAxis: { values: { 0: { 1: "NiceAxis Function" }, 2: { 1: "This example LAMBDA function computes nice axis bounds and increment values from given min and max of values to be charted.", }, 5: { 1: "NiceAxis Syntax:" }, 6: { 1: "=NiceAxis(min, max)" }, 7: { 1: "min: The minimum value of the data to be charted." }, 8: { 1: "max: The maximum value of the data to be charted." }, 10: { 1: "LAMBDA Implementation" }, 11: { 1: "Define the following in Name Manager:" }, 12: { 1: "Name:", 2: "Calendar" }, 13: { 1: "Scope:", 2: "Workbook" }, 14: { 1: "Comment:", 2: "Computes nice axis bounds and increment values for a chart" }, 15: { 1: "Refers To:", 2: "=LAMBDA(min_0,max_0,\r\n LET(\r\n min_1,MIN(min_0,max_0),\r\n max_1,MAX(min_0,max_0),\r\n delta,IF(min_1=max_1,9,max_1-min_1),\r\n min_2,\r\n IF(min_1=0,\r\n 0,\r\n IF(min_1>0,\r\n MAX(0,min_1-delta/100),\r\n min_1-delta/100\r\n )\r\n ),\r\n max_2,\r\n IF(max_1=0,\r\n IF(min_1=0,1,0),\r\n IF(max_1<0,\r\n MIN(0,max_1+delta/100),\r\n max_1+delta/100\r\n )\r\n ),\r\n power,LOG10(max_2-min_2),\r\n factor,10^(power-INT(power)),\r\n major_3,\r\n XLOOKUP(\r\n factor,\r\n {0,2.1,5,10},\r\n {0.2,0.5,1,2},,\r\n -1\r\n )*10^INT(power),\r\n min_3,major_3*INT(min_2/major_3),\r\n max_3,\r\n major_3*\r\n IF(max_2/major_3=INT(max_2/major_3),\r\n max_2/major_3,\r\n INT(max_2/major_3)+1\r\n ),\r\n CHOOSE({1;2;3},min_3,max_3,major_3)\r\n )\r\n)", }, 56: { 1: "Example" }, 57: { 1: "Data", 3: "Formula:" }, 58: { 1: 12.5, 3: "Nice Min" }, 59: { 1: 23.6, 3: "Nice Max" }, 60: { 1: 22.8, 3: "Nice Inc" }, 61: { 1: 38.3 }, 62: { 1: 92.6 }, 63: { 1: 37.8 }, 64: { 1: 42.9 }, 65: { 1: 83.7 }, 66: { 1: 16.9 }, 67: { 1: 44.8 }, }, formulas: { 57: { 4: "FORMULATEXT(E59)" }, 58: { 4: "NICEAXIS(MIN(B59:B68),MAX(B59:B68))" } }, cellStyles: { B1: 0, "B3:H4": 1, "B6:C6": 2, "B7:C7": 3, "B8:F9": 4, B10: 2, "B11:D11": 5, B12: 2, "B13:B55": 6, "C13:G14": 7, "C15:G55": 8, B57: 5, B58: 9, "D58:D61": 10, "E58:E61": 3, "F58:G58": 3, "B59:B68": 11, }, styles: { records: [ { font: 0 }, { foreColor: 0, font: 1, wordWrap: true }, { foreColor: 0, font: 1 }, { backColor: 2, foreColor: 1, font: 1, border: [0, 0, 0, 0] }, { backColor: 3, border: [1, 1, 1, 1] }, { foreColor: 0, font: 2, border: [null, null, 2] }, { backColor: 5, foreColor: 4, font: 1, border: [3, 3, 3, 3] }, { backColor: 6, font: 1, border: [3, 3, 3, 3] }, { backColor: 6, font: 1, wordWrap: true, border: [3, 3, 3, 3] }, { backColor: 7, foreColor: 4, font: 1 }, { backColor: 7, foreColor: 4, font: 1, border: [3, 3, 3, 3] }, { backColor: 9, foreColor: 8, border: [0, 0, 0, 0] }, ], borders: [ { color: "#7f7f7f", style: 1 }, { color: "#b2b2b2", style: 1 }, { color: "#accdea", style: 5 }, { color: "#000000", style: 1 }, ], colors: ["#44546a", "#fa7d00", "#f2f2f2", "#ffffcc", "#ffffff", "#4472c4", "#d9e3f2", "#5b9bd5", "#3f3f76", "#ffcc99"], fonts: ["700 24px Calibri", "700 14.7px Calibri", "700 17.3px Calibri"], }, others: { columnWidth: { 0: 36, 1: 86, 6: 130 }, rowHeight: { 0: 31, 10: 24, 11: 21, 56: 24, 57: 21 }, spans: ["B3:H4", "B16:B55", "C16:G55", "C13:G13", "C14:G14", "C15:G15"], }, }, customNames: { arrayMAKE: "LAMBDA(r,c,f, LET(seq, SEQUENCE(r,c), IF(seq, LET(i, ROUNDDOWN((seq-1)/c, 0)+1, j, MOD(seq-1,c)+1, f(i,j)))))", Calendar: 'LAMBDA(serial,mark, LET(daysInMonth, EOMONTH(serial, 0)-EOMONTH(serial, -1), foMonth, DATE(YEAR(serial), MONTH(serial), 1), dayPadding, WEEKDAY(foMonth, 2)-1, calendarRows, ROUNDUP((dayPadding+daysInMonth)/7, 0), body, arrayMAKE(calendarRows, 7, LAMBDA(i,j, LET(seqNum, ((i-1)*7+j)-dayPadding, IFS(seqNum<=0, "", seqNum=DAY(serial), IF(mark, "X", seqNum), seqNum<=daysInMonth, seqNum, TRUE, "" ) ) ) ), vcat( arrayMAKE(1, 7, LAMBDA(i,j, IF(j=1, INDEX(months, MONTH(serial)), IF(j=2, YEAR(serial), "" ) ) ) ), arrayMAKE(calendarRows+1, 7, LAMBDA(i,j, IF(i=1, INDEX(days, j, 1), INDEX(body, i-1, j) ) ) ) )))', days: '{"Mon";"Tue";"Wed";"Thu";"Fri";"Sat";"Sun"}', getDay: "LAMBDA(serial, INDEX(days, WEEKDAY(serial, 2), 1))", HEAD: 'LAMBDA(str, IF(str="", "", LEFT(str,1)))', IsPalindrome: 'LAMBDA(str, LET( replaceStr, ReplaceChars(str, "!@#$%^&*()[]<>-?.,\'"" ", ""), lowStr, LOWER(replaceStr), lowStr = REVERSE(lowStr) ) )', months: '{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"}', myFact: "LAMBDA(num, IF(num<2, 1, num * myFact(num - 1) ) )", NiceAxis: "LAMBDA(min_0,max_0, LET( min_1,MIN(min_0,max_0), max_1,MAX(min_0,max_0), delta,IF(min_1=max_1,9,max_1-min_1), min_2, IF(min_1=0, 0, IF(min_1>0, MAX(0,min_1-delta/100), min_1-delta/100 ) ), max_2, IF(max_1=0, IF(min_1=0,1,0), IF(max_1<0, MIN(0,max_1+delta/100), max_1+delta/100 ) ), power,LOG10(max_2-min_2), factor,10^(power-INT(power)), major_3, XLOOKUP( factor, {0,2.1,5,10}, {0.2,0.5,1,2},, -1 )*10^INT(power), min_3,major_3*INT(min_2/major_3), max_3, major_3* IF(max_2/major_3=INT(max_2/major_3), max_2/major_3, INT(max_2/major_3)+1 ), CHOOSE({1;2;3},min_3,max_3,major_3) ) )", ReplaceChars: 'LAMBDA(str,chars,sub, IF(chars="", str, ReplaceChars( SUBSTITUTE(str, LEFT(chars), sub), MID(chars,2,LEN(chars) - 1), sub ) ) )', REVERSE: "LAMBDA(str, IF(LEN(str)<2, str, REVERSE(TAIL(str)) & HEAD(str) ) )", TAIL: 'LAMBDA(str, IF(str="", "", RIGHT(str, LEN(str) - 1)))', vcat: "LAMBDA(top,bot, LET(width, MIN(COLUMNS(top), COLUMNS(bot)), topH, ROWS(top), arrayMAKE(ROWS(bot)+topH, width, LAMBDA(i,j, IF(i <= topH, INDEX(top, i, j), INDEX(bot, i-topH, j) ) ) ) ) )", }, }; window.onload = function () { var workbook = new GC.Spread.Sheets.Workbook(document.getElementById("ss")); workbook.options.allowDynamicArray = true; workbook.suspendPaint(); // add custom name foreachObj(data.customNames, function (name, formula) { workbook.addCustomName(name, formula); }); workbook.setSheetCount(3); workbook.sheets[0].name("Recursive"); workbook.sheets[1].name("Calendar"); workbook.sheets[2].name("NiceAxis"); initSheet(workbook.getSheet(0), data.Recursive); initSheet(workbook.getSheet(1), data.Calendar); initSheet(workbook.getSheet(2), data.NiceAxis); workbook.getSheet(1).setValue(85, 2, new Date(2022, 0, 1)); workbook.resumePaint(); }; function initSheet(sheet, data) { setSheetPr(sheet, data); setCells(sheet, data); } function setCells(sheet, data) { foreachObj(data.values, function (r, row) { foreachObj(row, function (c, v) { setValue(sheet, Number(r), Number(c), v); }); }); foreachObj(data.formulas, function (r, row) { foreachObj(row, function (c, v) { setFormula(sheet, Number(r), Number(c), v); }); }); foreachObj(data.cellStyles, function (ref, id) { setStyle(sheet, ref, data.styles.records[id], data.styles); }); } function setValue(sheet, r, c, v) { if (v === undefined || v === null) return; sheet.setValue(r, c, v); } function setFormula(sheet, r, c, v) { if (v === undefined || v === null) return; sheet.setFormula(r, c, v); } function setStyle(sheet, ref, v, styles) { if (v === undefined || v === null) return; var range = sheet.getRange(ref); var foreColor = styles.colors[v.foreColor]; var backColor = styles.colors[v.backColor]; var font = styles.fonts[v.font]; var wordWrap = v.wordWrap; if (foreColor) { range.foreColor(foreColor); } if (backColor) { range.backColor(backColor); } if (font) { range.font(font); } if (wordWrap) { range.wordWrap(wordWrap); } var border = v.border || []; var borderTop = styles.borders[border[0]]; var borderRight = styles.borders[border[1]]; var borderBottom = styles.borders[border[2]]; var borderLeft = styles.borders[border[3]]; if (borderTop) { range.borderTop(createLineStyle(borderTop)); } if (borderBottom) { range.borderBottom(createLineStyle(borderBottom)); } if (borderLeft) { range.borderLeft(createLineStyle(borderLeft)); } if (borderRight) { range.borderRight(createLineStyle(borderRight)); } } function setSheetPr(sheet, data) { // set column width foreachObj(data.others.columnWidth, function (index, v) { sheet.setColumnWidth(Number(index), v); }); // set row height foreachObj(data.others.rowHeight, function (index, v) { sheet.setRowHeight(Number(index), v); }); // set spans var spans = data.others.spans || []; for (var i = 0; i < spans.length; i++) { var range = sheet.getRange(spans[i]); sheet.addSpan(range.row, range.col, range.rowCount, range.colCount); } // set tables var tables = data.others.tables || []; for (var i = 0; i < tables.length; i++) { var table = tables[i]; var range = sheet.getRange(table.ref); sheet.tables.add(table.name, range.row, range.col, range.rowCount, range.colCount); } } function createLineStyle(v) { return new GC.Spread.Sheets.LineBorder(v.color, v.style); } function foreachObj(obj, func) { if (!obj) return; var keys = Object.keys(obj); for (var i = 0; i < keys.length; i++) { var key = keys[i]; var v = obj[key]; func(key, v); } }
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script> <script src="app.js" type="text/javascript"></script> <link rel="stylesheet" type="text/css" href="styles.css"> </head> <body> <div class="sample-tutorial"> <div id="ss" class="sample-spreadsheets"></div> </div> </body> </html>
input[type="text"] { width: 200px; margin-right: 20px; } label { display: inline-block; width: 110px; } .sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: 100%; height: 100%; overflow: hidden; float: left; } label { display: block; margin-bottom: 6px; } input { padding: 4px 6px; } input[type=button] { margin-top: 6px; display: block; width:216px; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } code { border: 1px solid #000; }