Examples

import { Component, NgModule, enableProdMode } from "@angular/core"; import { BrowserModule } from "@angular/platform-browser"; import { FormsModule } from "@angular/forms"; import { platformBrowserDynamic } from "@angular/platform-browser-dynamic"; import { SpreadSheetsModule } from "@mescius/spread-sheets-angular"; import GC from "@mescius/spread-sheets"; import "./styles.css"; const spreadNS = GC.Spread.Sheets, SheetArea = spreadNS.SheetArea; @Component({ selector: "app-component", templateUrl: "src/app.component.html", }) export class AppComponent { spread: GC.Spread.Sheets.Workbook; hostStyle = { width: "100%", height: "100%", overflow: "hidden", float: "left", }; 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) ) ) ) ) )", }, }; constructor() {} init($event: any) { let workbook = (this.spread = $event.spread); initWorkbook(workbook, this.data); } } function initWorkbook(workbook: GC.Spread.Sheets.Workbook, data: any) { workbook.options.allowDynamicArray = true; workbook.suspendPaint(); // add custom name foreachObj(data.customNames, function (name: string, formula: string) { 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: GC.Spread.Sheets.Worksheet, data: any) { setSheetPr(sheet, data); setCells(sheet, data); } function setCells(sheet: GC.Spread.Sheets.Worksheet, data: any) { foreachObj(data.values, function (r: string, row: any) { foreachObj(row, function (c: string, v: any) { setValue(sheet, Number(r), Number(c), v); }); }); foreachObj(data.formulas, function (r: string, row: any) { foreachObj(row, function (c: string, v: any) { setFormula(sheet, Number(r), Number(c), v); }); }); foreachObj(data.cellStyles, function (ref: string, id: number) { setStyle(sheet, ref, data.styles.records[id], data.styles); }); } function setValue(sheet: GC.Spread.Sheets.Worksheet, r: number, c: number, v: any) { if (v === undefined || v === null) return; sheet.setValue(r, c, v); } function setFormula(sheet: GC.Spread.Sheets.Worksheet, r: number, c: number, v: string) { if (v === undefined || v === null) return; sheet.setFormula(r, c, v); } function setStyle(sheet: GC.Spread.Sheets.Worksheet, ref: string, v: any, styles: any) { 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: GC.Spread.Sheets.Worksheet, data: any) { // set column width foreachObj(data.others.columnWidth, function (index: string, v: any) { sheet.setColumnWidth(Number(index), v); }); // set row height foreachObj(data.others.rowHeight, function (index: string, v: any) { 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: any) { return new GC.Spread.Sheets.LineBorder(v.color, v.style); } function foreachObj(obj: any, func: Function) { 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); } } @NgModule({ imports: [BrowserModule, SpreadSheetsModule, FormsModule], declarations: [AppComponent], exports: [AppComponent], bootstrap: [AppComponent], }) export class AppModule {} enableProdMode(); // Bootstrap application with hash style navigation and global services. platformBrowserDynamic().bootstrapModule(AppModule);

<!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/angular/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <!-- Polyfills --> <script src="$DEMOROOT$/en/angular/node_modules/core-js/client/shim.min.js"></script> <script src="$DEMOROOT$/en/angular/node_modules/zone.js/fesm2015/zone.min.js"></script> <!-- SystemJS --> <script src="$DEMOROOT$/en/angular/node_modules/systemjs/dist/system.js"></script> <script src="systemjs.config.js"></script> <script> // workaround to load 'rxjs/operators' from the rxjs bundle System.import('rxjs').then(function (m) { System.import('@angular/compiler'); System.set(SystemJS.resolveSync('rxjs/operators'), System.newModule(m.operators)); System.import('$DEMOROOT$/en/lib/angular/license.ts'); System.import('./src/app.component'); }); </script> </head> <body> <app-component></app-component> </body> </html>

<div class="sample-tutorial"> <gc-spread-sheets [hostStyle]="hostStyle" (workbookInitialized)="init($event)"> <gc-worksheet> </gc-worksheet> </gc-spread-sheets> </div>

.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .options-container { float: right; width: 280px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } .options-row { font-size: 14px; padding: 5px; margin-top: 10px; } input { padding: 4px 6px; display: inline-block; } input[type="text"] { width: 200px; } label { display: block; margin-bottom: 6px; } input[type=button] { margin-top: 6px; display: block; width:216px; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }

(function (global) { System.config({ transpiler: 'ts', typescriptOptions: { tsconfig: true }, meta: { 'typescript': { "exports": "ts" }, '*.css': { loader: 'css' } }, paths: { // paths serve as alias 'npm:': 'node_modules/' }, // map tells the System loader where to look for things map: { 'core-js': 'npm:core-js/client/shim.min.js', 'zone': 'npm:zone.js/fesm2015/zone.min.js', 'rxjs': 'npm:rxjs/dist/bundles/rxjs.umd.min.js', '@angular/core': 'npm:@angular/core/fesm2022', '@angular/common': 'npm:@angular/common/fesm2022/common.mjs', '@angular/compiler': 'npm:@angular/compiler/fesm2022/compiler.mjs', '@angular/platform-browser': 'npm:@angular/platform-browser/fesm2022/platform-browser.mjs', '@angular/platform-browser-dynamic': 'npm:@angular/platform-browser-dynamic/fesm2022/platform-browser-dynamic.mjs', '@angular/common/http': 'npm:@angular/common/fesm2022/http.mjs', '@angular/router': 'npm:@angular/router/fesm2022/router.mjs', '@angular/forms': 'npm:@angular/forms/fesm2022/forms.mjs', 'jszip': 'npm:jszip/dist/jszip.min.js', 'typescript': 'npm:typescript/lib/typescript.js', 'ts': './plugin.js', 'tslib':'npm:tslib/tslib.js', 'css': 'npm:systemjs-plugin-css/css.js', 'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js', 'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js', '@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js', '@mescius/spread-sheets-angular': 'npm:@mescius/spread-sheets-angular/fesm2020/mescius-spread-sheets-angular.mjs', '@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js' }, // packages tells the System loader how to load when no filename and/or no extension packages: { src: { defaultExtension: 'ts' }, rxjs: { defaultExtension: 'js' }, "node_modules": { defaultExtension: 'js' }, "node_modules/@angular": { defaultExtension: 'mjs' }, "@mescius/spread-sheets-angular": { defaultExtension: 'mjs' }, '@angular/core': { defaultExtension: 'mjs', main: 'core.mjs' } } }); })(this);