Examples

下面是一些LAMBDA函数的例子

这里有一些LAMBDA函数的例子。 第一个表单给出了一些递归Lambda函数的例子。 第二个和第三个表单给出了两个更复杂的例子。
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 name="spreadjs culture" content="zh-cn" /> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.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; }