LAMBDA函数

LAMBDA 函数使您能够使用 Excel 自己的公式语言定义自定义函数。 以下演示展示了如何在 SpreadJS 中使用 LAMBDA 函数的几个不同示例。

语法 参数 - (可选) 你想传递给函数的一个值,如一个单元格引用、字符串或数字。你最多可以输入253个参数。 计算 - (必填) 你想执行的公式,并作为函数的结果返回。它必须是最后一个参数,并且必须返回一个结果。 备注 使用了动态数组公式,你需要用以下代码启用它 基本用例 在单元格中直接使用LAMBDA函数 将Lambda添加到名称管理器中,然后在一个单元格中使用。 示例 将华氏温度转换为摄氏温度 求斜边 计算字数 计算球体的体积
var data = { values: { "1": { "1": "示例" }, "3": { "1": "示例 1: 华氏温度转摄氏温度", "8": "摄氏温度转华氏温度" }, "4": { "1": "在名称管理器中定义以下内容:", "8": "在名称管理器中定义以下内容:" }, "5": { "1": "名称:", "3": "ToCelsius", "8": "名称:", "10": "ToFarenheit" }, "6": { "1": "作用域:", "3": "工作簿", "8": "作用域:", "10": "工作簿" }, "7": { "1": "注释:", "3": "将华氏温度转换为摄氏温度", "8": "注释:", "10": "将摄氏温度转换为华氏温度", }, "8": { "1": "引用位置:", "3": "=LAMBDA(temp,(5/9)*(temp-32))", "8": "引用位置:", "10": "=LAMBDA(temp,(9/5)*temp+32)", }, "9": { "1": "数据", "3": "公式", "4": "结果", "8": "数据", "10": "公式", "11": "结果" }, "10": { "1": 104, "8": 40 }, "11": { "1": 86, "8": 30 }, "12": { "1": 68, "8": 20 }, "13": { "1": 50, "8": 10 }, "14": { "1": 32, "8": 0 }, "16": { "1": "示例 2: 计算直角三角形斜边长度", "8": "使用区域计算直角三角形斜边长度" }, "17": { "1": "在名称管理器中定义以下内容:", "8": "在名称管理器中定义以下内容:" }, "18": { "1": "名称:", "3": "Hypotenuse", "8": "名称:", "10": "Hypotenuse2" }, "19": { "1": "作用域:", "3": "工作簿", "8": "作用域:", "10": "工作簿" }, "20": { "1": "注释:", "3": "返回直角三角形斜边的长度", "8": "注释:", "10": "返回直角三角形斜边的长度", }, "21": { "1": "引用位置:", "3": "=LAMBDA(a,b,SQRT(a^2+b^2))", "8": "引用位置:", "10": "=LAMBDA(a,b,IF(AND(ISOMITTED(b),COUNT(a)=2),SQRT(SUM(a^2)),SQRT(a^2+b^2)))", }, "22": { "1": "数据", "3": "公式", "4": "结果", "8": "数据", "10": "公式", "11": "结果", "12": "结果2", "15": "相同数据倒置,按列引用:", }, "23": { "1": 3, "2": 4, "8": 3, "9": 4, "14": "数据", "15": 3, "16": 5, "17": 7, "18": 9 }, "24": { "1": 5, "2": 12, "8": 5, "9": 12, "15": 4, "16": 12, "17": 24, "18": 40 }, "25": { "1": 7, "2": 24, "8": 7, "9": 24, "14": "结果" }, "26": { "1": 9, "2": 40, "8": 9, "9": 40, "14": "结果2" }, "27": { "10": "此版本使第二个参数可选,并使用两个单元格的区域计算结果。", }, "29": { "1": "示例 3: 统计单词数量", "8": "统计区域中的单词数量" }, "30": { "1": "在名称管理器中定义以下内容:", "8": "在名称管理器中定义以下内容:" }, "31": { "1": "名称:", "3": "CountWords", "8": "名称:", "10": "CountWordsRange" }, "32": { "1": "作用域:", "3": "工作簿", "8": "作用域:", "10": "工作簿" }, "33": { "1": "注释:", "3": "返回文本字符串中的单词数量", "8": "注释:", "10": "返回单元格区域中的单词数量", }, "34": { "1": "引用位置:", "3": '=LAMBDA(text,IF(LEN(TRIM(text))=0,0,LEN(TRIM(text))-LEN(SUBSTITUTE(TRIM(text)," ",""))+1))', "8": "引用位置:", "10": "=LAMBDA(range,SUM(CountWords(range)))", }, "36": { "1": "数据", "3": "公式", "4": "结果", "8": "数据", "10": "公式:", "11": "结果:" }, "37": { "1": "Something wicked this way comes.", "8": "Something wicked this way comes." }, "38": { "1": "I came, I saw, I conquered.", "8": "I came, I saw, I conquered." }, "39": { "1": "A quick brown fox jumped over the lazy dog.", "8": "A quick brown fox jumped over the lazy dog.", }, "40": { "1": "Use the Force, Luke!", "8": "Use the Force, Luke!" }, "43": { "1": "示例 4: 查找感恩节日期", "8": "查找复活节日期" }, "44": { "1": "在名称管理器中定义以下内容:", "8": "在名称管理器中定义以下内容:" }, "45": { "1": "名称:", "3": "ThanksgivingDate", "8": "名称:", "10": "EasterDate" }, "46": { "1": "作用域:", "3": "工作簿", "8": "作用域:", "10": "工作簿" }, "47": { "1": "注释:", "3": "返回给定年份美国感恩节的日期", "8": "注释:", "10": "返回给定年份美国复活节的日期", }, "48": { "1": "引用位置:", "3": '=LAMBDA(year, TEXT(DATE(year, 11, CHOOSE(WEEKDAY(DATE(year, 11, 1)), 26, 25, 24, 23, 22, 28, 27)), "mm/dd/yyyy"))', "8": "引用位置:", "10": '=LAMBDA(year,FLOOR("5/"&DAY(MINUTE(year/38)/2+56)&"/"&year,7)-34)', }, "49": { "8": "日期", "10": "公式", "11": "结果" }, "50": { "1": "数据", "3": "公式", "4": "结果" }, "57": { "1": "示例 5: 获取区域或数组中的重复值", "8": "生成随机 GUID" }, "58": { "1": "在名称管理器中定义以下内容:", "8": "在名称管理器中定义以下内容:" }, "59": { "1": "名称:", "3": "GetDuplicates", "8": "名称:", "10": "Guid" }, "60": { "1": "作用域:", "3": "工作簿", "8": "作用域:", "10": "工作簿" }, "61": { "1": "注释:", "3": "获取区域或数组中的重复值", "8": "注释:", "10": "生成随机 GUID", }, "62": { "1": "引用位置:", "3": "=LAMBDA(values, UNIQUE(FILTER(values, COUNTIFS(values,values)>1)))", "8": "引用位置:", "10": '=LAMBDA(CONCATENATE(DEC2HEX(RANDBETWEEN(0,4294967295),8),"-",DEC2HEX(RANDBETWEEN(0,65535),4),"-",DEC2HEX(RANDBETWEEN(16384,20479),4),"-",DEC2HEX(RANDBETWEEN(32768,49151),4),"-",DEC2HEX(RANDBETWEEN(0,65535),4),DEC2HEX(RANDBETWEEN(0,4294967295),8)))', }, "64": { "1": "数据", "3": "公式", "4": "结果" }, "65": { "1": "jack" }, "66": { "1": "jill" }, "67": { "1": "jason" }, "68": { "1": "jack", "8": "公式" }, "69": { "1": "alex", "8": "结果" }, "70": { "1": "allen" }, "71": { "1": "alex" }, "73": { "1": "示例 6: 计算球体体积" }, "74": { "1": "在名称管理器中定义以下内容:" }, "75": { "1": "名称:", "3": "SphereVolume" }, "76": { "1": "作用域:", "3": "工作簿" }, "77": { "1": "注释:", "3": "计算球体的体积" }, "78": { "1": "引用位置:", "3": "=LAMBDA(r, 4/3*PI()*r^3)" }, "79": { "1": "数据", "3": "公式", "4": "结果" }, }, formulas: { "10": { "3": "FORMULATEXT(E11)", "4": "TOCELSIUS(B11)", "10": "FORMULATEXT(L11)", "11": "TOFARENHEIT(I11)", }, "11": { "3": "FORMULATEXT(E12)", "4": "TOCELSIUS(B12)", "10": "FORMULATEXT(L12)", "11": "TOFARENHEIT(I12)", }, "12": { "3": "FORMULATEXT(E13)", "4": "TOCELSIUS(B13)", "10": "FORMULATEXT(L13)", "11": "TOFARENHEIT(I13)", }, "13": { "3": "FORMULATEXT(E14)", "4": "TOCELSIUS(B14)", "10": "FORMULATEXT(L14)", "11": "TOFARENHEIT(I14)", }, "14": { "3": "FORMULATEXT(E15)", "4": "TOCELSIUS(B15)", "10": "FORMULATEXT(L15)", "11": "TOFARENHEIT(I15)", }, "23": { "3": "FORMULATEXT(E24)", "4": "HYPOTENUSE(B24,C24)", "10": "FORMULATEXT(L24)", "11": "HYPOTENUSE2(I24:J24,)", "12": "HYPOTENUSE2(I24,J24)", }, "24": { "3": "FORMULATEXT(E25)", "4": "HYPOTENUSE(B25,C25)", "10": "FORMULATEXT(L25)", "11": "HYPOTENUSE2(I25:J25,)", "12": "HYPOTENUSE2(I25,J25)", }, "25": { "3": "FORMULATEXT(E26)", "4": "HYPOTENUSE(B26,C26)", "10": "FORMULATEXT(L26)", "11": "HYPOTENUSE2(I26:J26,)", "12": "HYPOTENUSE2(I26,J26)", "15": "HYPOTENUSE2(P24:P25,)", "16": "HYPOTENUSE2(Q24:Q25,)", "17": "HYPOTENUSE2(R24:R25,)", "18": "HYPOTENUSE2(S24:S25,)", }, "26": { "3": "FORMULATEXT(E27)", "4": "HYPOTENUSE(B27,C27)", "10": "FORMULATEXT(L27)", "11": "HYPOTENUSE2(I27:J27,)", "12": "HYPOTENUSE2(I27,J27)", "15": "HYPOTENUSE2(P24,P25)", "16": "HYPOTENUSE2(Q24,Q25)", "17": "HYPOTENUSE2(R24,R25)", "18": "HYPOTENUSE2(S24,S25)", }, "37": { "3": "FORMULATEXT(E38)", "4": "COUNTWORDS(B38)", "10": "FORMULATEXT(L38)", "11": "COUNTWORDSRANGE(I38:J41)", }, "38": { "3": "FORMULATEXT(E39)", "4": "COUNTWORDS(B39)" }, "39": { "3": "FORMULATEXT(E40)", "4": "COUNTWORDS(B40)" }, "40": { "3": "FORMULATEXT(E41)", "4": "COUNTWORDS(B41)" }, "50": { "8": "YEAR(NOW())", "10": "FORMULATEXT(L51)", "11": "EASTERDATE(I51)" }, "51": { "1": "YEAR(NOW())", "3": "FORMULATEXT(E52)", "4": "THANKSGIVINGDATE(B52)", "8": "I51+1", "10": "FORMULATEXT(L52)", "11": "EASTERDATE(I52)", }, "52": { "1": "B52+1", "3": "FORMULATEXT(E53)", "4": "THANKSGIVINGDATE(B53)", "8": "I52+1", "10": "FORMULATEXT(L53)", "11": "EASTERDATE(I53)", }, "53": { "1": "B53+1", "3": "FORMULATEXT(E54)", "4": "THANKSGIVINGDATE(B54)", "8": "I53+1", "10": "FORMULATEXT(L54)", "11": "EASTERDATE(I54)", }, "54": { "1": "B54+1", "3": "FORMULATEXT(E55)", "4": "THANKSGIVINGDATE(B55)", "8": "I54+1", "10": "FORMULATEXT(L55)", "11": "EASTERDATE(I55)", }, "55": { "1": "B55+1", "3": "FORMULATEXT(E56)", "4": "THANKSGIVINGDATE(B56)", "8": "I55+1", "10": "FORMULATEXT(L56)", "11": "EASTERDATE(I56)", }, "65": { "3": "FORMULATEXT(E66)", "4": "GETDUPLICATES(B66:B72)" }, "68": { "10": "FORMULATEXT(K70)" }, "69": { "10": "GUID()" }, "80": { "2": "SEQUENCE(11)", "3": "FORMULATEXT(E81)", "4": "SPHEREVOLUME(C81#)" }, }, cellStyles: { "B2:C2": 0, "B4:D4": 1, "I4:K4": 1, L4: 2, "B5:E5": 3, "I5:L5": 3, "B6:B9": 4, "C6:C9": 5, "D6:D9": 6, "E6:E9": 7, "I6:I9": 4, "J6:J9": 5, "K6:K9": 6, "L6:L9": 7, B10: 8, C10: 9, "D10:E10": 10, I10: 8, J10: 9, "K10:L10": 10, B11: 11, C11: 12, D11: 13, E11: 14, I11: 11, J11: 12, "K11:L11": 13, "B12:B14": 15, "C12:C14": 16, "D12:D14": 17, "E12:E14": 18, "I12:I14": 15, "J12:J14": 16, "K12:L15": 17, B15: 19, C15: 20, D15: 21, E15: 22, I15: 19, J15: 20, "B17:D17": 1, "E17:E18": 2, "I17:K17": 1, L17: 2, "B18:E18": 2, "I18:L18": 3, "B19:B22": 4, "C19:C22": 5, "D19:D22": 6, "E19:E22": 7, "I19:I22": 4, "J19:J22": 5, "K19:K22": 6, "L19:L22": 23, "M19:M22": 7, B23: 24, C23: 25, D23: 26, E23: 27, I23: 28, "J23:L23": 29, M23: 30, "P23:S23": 31, "B24:C27": 32, "D24:E27": 17, "I24:J27": 32, "K24:M27": 17, O24: 26, "P24:S25": 32, O25: 25, "O26:O27": 26, "P26:S27": 17, "K28:M28": 33, "B30:D30": 1, "E30:E31": 2, "I30:K30": 1, "B31:E31": 2, "I31:J31": 2, "B32:B34": 4, "C32:C34": 5, "D32:D34": 6, "E32:E34": 7, "I32:I34": 4, "J32:J34": 5, "K32:K34": 6, "L32:L34": 7, B35: 34, C35: 35, D35: 36, E35: 37, I35: 34, J35: 35, K35: 38, L35: 39, B36: 40, C36: 41, D36: 42, E36: 43, I36: 40, J36: 41, K36: 44, L36: 45, B37: 46, C37: 31, D37: 25, E37: 47, I37: 24, J37: 25, "K37:L37": 48, "B38:B41": 49, "C38:C41": 16, "D38:E41": 17, "I38:I41": 49, "J38:J41": 16, "K38:L38": 17, "B44:D44": 1, "I44:K44": 1, "L44:L45": 2, B45: 2, "I45:L45": 2, "B46:B48": 4, "C46:C48": 5, "D46:D48": 6, "E46:E48": 7, "I46:I49": 4, "J46:J49": 5, "K46:K48": 6, "L46:L48": 7, B49: 34, C49: 35, D49: 36, E49: 37, K49: 50, L49: 51, B50: 40, C50: 41, D50: 42, E50: 43, "I50:J50": 52, K50: 48, L50: 53, B51: 54, C51: 52, D51: 26, E51: 27, "I51:I56": 49, "J51:J56": 16, "K51:L56": 17, "B52:B56": 49, "C52:C56": 16, "D52:E56": 17, "B58:D58": 1, "I58:K58": 1, "L58:L59": 2, B59: 2, "I59:L59": 2, "B60:B62": 4, "C60:C62": 5, "D60:D62": 6, "E60:E62": 7, "I60:I62": 4, "J60:J62": 5, "K60:K62": 6, "L60:L62": 7, B63: 34, C63: 35, D63: 36, E63: 37, I63: 34, J63: 35, K63: 36, L63: 37, B64: 40, C64: 41, D64: 42, E64: 43, "I64:I67": 55, "J64:J67": 56, "K64:K67": 57, "L64:L67": 58, B65: 59, C65: 60, "D65:E65": 10, "B66:B72": 49, C66: 16, "D66:E67": 17, "C67:C72": 61, I68: 40, J68: 41, K68: 42, L68: 43, I69: 62, J69: 63, "K69:K70": 17, I70: 8, J70: 9, "B74:D74": 1, B75: 2, "B76:B79": 4, "C76:C79": 5, "D76:D78": 6, "E76:E78": 7, D79: 50, E79: 51, B80: 59, C80: 60, "D80:E80": 64, "B81:B91": 49, "C81:C91": 16, "D81:E91": 17, }, styles: { records: [ { foreColor: 0, font: 0, border: [null, null, 0] }, { foreColor: 0, font: 1, border: [null, null, 1] }, { foreColor: 0, font: 1 }, { foreColor: 0, font: 1, border: [null, null, 2] }, { backColor: 2, foreColor: 1, font: 1, border: [2, null, 2, 2] }, { backColor: 2, foreColor: 1, font: 1, border: [2, 2, 2] }, { backColor: 3, font: 1, border: [2, null, 2, 2] }, { backColor: 3, font: 1, border: [2, 2, 2] }, { backColor: 4, foreColor: 1, font: 1, border: [2, null, 2, 2] }, { backColor: 4, foreColor: 1, font: 1, border: [2, 2, 2] }, { backColor: 4, foreColor: 1, font: 1, border: [2, 2, 2, 2] }, { backColor: 6, foreColor: 5, border: [null, null, 3, 2] }, { backColor: 6, foreColor: 5, border: [null, 3, 3] }, { backColor: 8, foreColor: 7, font: 1, border: [null, 3, 3, 3] }, { backColor: 8, foreColor: 7, font: 1, border: [null, 2, 3, 3] }, { backColor: 6, foreColor: 5, border: [3, null, 3, 2] }, { backColor: 6, foreColor: 5, border: [3, 3, 3] }, { backColor: 8, foreColor: 7, font: 1, border: [3, 3, 3, 3] }, { backColor: 8, foreColor: 7, font: 1, border: [3, 2, 3, 3] }, { backColor: 6, foreColor: 5, border: [3, null, 2, 2] }, { backColor: 6, foreColor: 5, border: [3, 3, 2] }, { backColor: 8, foreColor: 7, font: 1, border: [3, 3, 2, 3] }, { backColor: 8, foreColor: 7, font: 1, border: [3, 2, 2, 3] }, { backColor: 3, font: 1, border: [2, null, 2] }, { backColor: 4, foreColor: 1, font: 1, border: [null, null, 4, 4] }, { backColor: 4, foreColor: 1, font: 1, border: [null, null, 4] }, { backColor: 4, foreColor: 1, font: 1, border: [4] }, { backColor: 4, foreColor: 1, font: 1, border: [4, 4] }, { backColor: 4, foreColor: 1, font: 1, border: [2, null, 3, 4] }, { backColor: 4, foreColor: 1, font: 1, border: [2, null, 3] }, { backColor: 4, foreColor: 1, font: 1, border: [2, 4, 3, 4] }, { backColor: 4, foreColor: 1, font: 1, border: [null, null, 3] }, { backColor: 6, foreColor: 5, border: [3, 3, 3, 3] }, { backColor: 9, border: [5, 5, 5, 5] }, { backColor: 2, foreColor: 1, font: 1, border: [2, null, null, 2] }, { backColor: 2, foreColor: 1, font: 1, border: [2, 2] }, { backColor: 3, font: 1, wordWrap: true, border: [2, null, null, 2] }, { backColor: 3, font: 1, wordWrap: true, border: [2, 2] }, { backColor: 3, font: 1, border: [2, null, null, 2] }, { backColor: 3, font: 1, border: [2, 2] }, { backColor: 2, foreColor: 1, font: 1, border: [null, null, 2, 2] }, { backColor: 2, foreColor: 1, font: 1, border: [null, 2, 2] }, { backColor: 3, font: 1, wordWrap: true, border: [null, null, 2, 2] }, { backColor: 3, font: 1, wordWrap: true, border: [null, 2, 2] }, { backColor: 3, font: 1, border: [null, null, 2, 2] }, { backColor: 3, font: 1, border: [null, 2, 2] }, { backColor: 4, foreColor: 1, font: 1, border: [null, null, 3, 4] }, { backColor: 4, foreColor: 1, font: 1, border: [null, 4] }, { backColor: 4, foreColor: 1, font: 1, wordWrap: true, border: [2] }, { backColor: 6, foreColor: 5, border: [3, null, 3, 3] }, { backColor: 3, font: 1, wordWrap: true, border: [2, null, 2, 2] }, { backColor: 3, font: 1, wordWrap: true, border: [2, 2, 2] }, { backColor: 4, foreColor: 1, font: 1 }, { backColor: 4, foreColor: 1, font: 1, wordWrap: true, border: [2, 4] }, { backColor: 4, foreColor: 1, font: 1, border: [null, null, null, 4] }, { backColor: 2, foreColor: 1, font: 1, border: [null, null, null, 2] }, { backColor: 2, foreColor: 1, font: 1, border: [null, 2] }, { backColor: 3, font: 1, wordWrap: true, border: [null, null, null, 2] }, { backColor: 3, font: 1, wordWrap: true, border: [null, 2] }, { backColor: 4, foreColor: 1, font: 1, border: [null, null, null, 2] }, { backColor: 4, foreColor: 1, font: 1, border: [null, 2] }, { backColor: 6, foreColor: 5, border: [3, null, 3] }, { backColor: 4, foreColor: 1, font: 1, border: [2, null, null, 2] }, { backColor: 4, foreColor: 1, font: 1, border: [2, 2] }, { backColor: 4, foreColor: 1, font: 1, border: [null, 2, 2, 2] }, ], borders: [ { color: "#accdea", style: 5 }, { color: "#9bc3e6", style: 2 }, { color: "#000000", style: 1 }, { color: "#7f7f7f", style: 1 }, { color: "#9bc3e6", style: 1 }, { color: "#b2b2b2", style: 1 }, ], colors: [ "#44546a", "#ffffff", "#4472c4", "#d9e3f2", "#5b9bd5", "#3f3f76", "#ffcc99", "#fa7d00", "#f2f2f2", "#ffffcc", ], fonts: ["700 17.3px Calibri", "700 14.7px Calibri"], }, others: { columnWidth: { "0": 30, "1": 86, "2": 86, "3": 186, "4": 179, "5": 39, "6": 39, "7": 39, "8": 86, "9": 86, "10": 275, "11": 187, "12": 112, "13": 39, "15": 68, "16": 68, "17": 68, "18": 68, }, rowHeight: { "1": 24, "2": 21, "3": 21, "16": 21, "29": 21, "43": 21, "57": 21, "73": 21 }, spans: [ "B78:C78", "B79:C79", "B80:C80", "B70:C70", "I70:J70", "B71:C71", "B72:C72", "B76:C76", "B77:C77", "K63:L68", "B65:C65", "B66:C66", "B67:C67", "B68:C68", "B69:C69", "I69:J69", "B61:C61", "I61:J61", "B62:C62", "I62:J62", "B63:C64", "D63:E64", "I63:J68", "B55:C55", "I55:J55", "B56:C56", "I56:J56", "I59:L59", "B60:C60", "I60:J60", "B52:C52", "I52:J52", "B53:C53", "I53:J53", "B54:C54", "I54:J54", "B49:C50", "D49:E50", "I49:J49", "K49:L49", "I50:J50", "B51:C51", "I51:J51", "B47:C47", "D47:E47", "I47:J47", "B48:C48", "D48:E48", "I48:J48", "B40:C40", "I40:J40", "B41:C41", "I41:J41", "I45:L45", "B46:C46", "D46:E46", "I46:J46", "B37:C37", "I37:J37", "B38:C38", "I38:J38", "B39:C39", "I39:J39", "B34:C34", "I34:J34", "K34:L34", "B35:C36", "D35:E36", "I35:J36", "K35:L36", "B31:E31", "I31:J31", "B32:C32", "I32:J32", "K32:L32", "B33:C33", "I33:J33", "K33:L33", "B23:C23", "I23:J23", "P23:S23", "O24:O25", "B30:D30", "I30:J30", "B21:C21", "D21:E21", "I21:J21", "B22:C22", "D22:E22", "I22:J22", "B18:E18", "I18:L18", "B19:C19", "D19:E19", "I19:J19", "B20:C20", "D20:E20", "I20:J20", "B14:C14", "I14:J14", "B15:C15", "I15:J15", "B17:D17", "I17:K17", "B11:C11", "I11:J11", "B12:C12", "I12:J12", "B13:C13", "I13:J13", "B9:C9", "D9:E9", "I9:J9", "K9:L9", "B10:C10", "I10:J10", "B7:C7", "D7:E7", "I7:J7", "K7:L7", "B8:C8", "D8:E8", "I8:J8", "K8:L8", "B2:C2", "I4:K4", "B5:E5", "I5:L5", "B6:C6", "D6:E6", "I6:J6", "K6:L6", ], }, customNames: { CountWords: 'LAMBDA(text,IF(LEN(TRIM(text))=0,0,LEN(TRIM(text))-LEN(SUBSTITUTE(TRIM(text)," ",""))+1))', CountWordsRange: "LAMBDA(range,SUM(CountWords(range)))", EasterDate: 'LAMBDA(year,FLOOR("5/"&DAY(MINUTE(year/38)/2+56)&"/"&year,7)-34)', GetDuplicates: "LAMBDA(values, UNIQUE(FILTER(values, COUNTIFS(values,values)>1)))", Guid: 'LAMBDA(CONCATENATE(DEC2HEX(RANDBETWEEN(0,4294967295),8),"-",DEC2HEX(RANDBETWEEN(0,65535),4),"-",DEC2HEX(RANDBETWEEN(16384,20479),4),"-",DEC2HEX(RANDBETWEEN(32768,49151),4),"-",DEC2HEX(RANDBETWEEN(0,65535),4),DEC2HEX(RANDBETWEEN(0,4294967295),8)))', Hypotenuse: "LAMBDA(a,b,SQRT(a^2+b^2))", Hypotenuse2: "LAMBDA(a,b,IF(AND(ISOMITTED(b),COUNT(a)=2),SQRT(SUM(a^2)),SQRT(a^2+b^2)))", SphereVolume: "LAMBDA(r, 4/3*PI()*r^3)", ThanksgivingDate: 'LAMBDA(year, TEXT(DATE(year, 11, CHOOSE(WEEKDAY(DATE(year, 11, 1)), 26, 25, 24, 23, 22, 28, 27)), "mm/dd/yyyy"))', ToCelsius: "LAMBDA(temp,(5/9)*(temp-32))", ToFarenheit: "LAMBDA(temp,9/5*temp+32)", }, }; 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); }); initSheet1(workbook.getSheet(0)); workbook.resumePaint(); }; function initSheet1(sheet) { setSheetPr(sheet); setCells(sheet); } function setCells(sheet) { 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]); }); } 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) { if (v === undefined || v === null) return; var styles = data.styles; 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) { // 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); } } 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; }