LAMBDA関数の一例を紹介します。
最初のシートは、再帰的なLAMBDA関数の例をいくつか示しています。
2枚目と3枚目には、より複雑な例が2つあります。
import { Component, NgModule, enableProdMode } from '@angular/core';
import { bootstrapApplication, 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 '@mescius/spread-sheets-resources-ja';
GC.Spread.Common.CultureManager.culture('ja-jp');
import './styles.css';
const spreadNS = GC.Spread.Sheets,
SheetArea = spreadNS.SheetArea;
@Component({
standalone: true,
imports: [SpreadSheetsModule, BrowserModule, FormsModule],
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": "例" },
"3": { "1": "例1： 階乗を計算する" },
"4": { "1": "［名前の管理］で以下のように定義します：" },
"5": { "1": "名前：", "2": "myFact" },
"6": { "1": "範囲：", "2": "Workbook" },
"7": { "1": "コメント：", "2": "数値の階乗を計算する" },
"8": {
"1": "参照：",
"2": "=LAMBDA(num,\r\n IF(num<2,\r\n 1,\r\n num * myFact(num - 1)\r\n )\r\n)",
},
"14": { "1": "データ", "2": "数式", "3": "結果" },
"15": { "1": 4 },
"16": { "1": 16 },
"17": { "1": 52 },
"19": { "1": "例2： 文字列内の文字を置換する" },
"20": { "1": "［名前の管理］で以下のように定義します：" },
"21": { "1": "名前：", "2": "ReplaceChars" },
"22": { "1": "範囲：", "2": "Workbook" },
"23": { "1": "コメント：", "2": "文字列中の指定された文字を置き換える" },
"24": {
"1": "参照：",
"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": "データ", "2": "数式", "3": "結果" },
"35": { "1": "WARNING!! <script>" },
"36": { "1": "#4 & #7 + $803*" },
"37": { "1": "Generally (#25) free" },
"39": { "1": "例3： 文字列の値を反転させる" },
"40": { "1": "3a. ［名前の管理］で以下のように定義します：" },
"41": { "1": "名前：", "2": "HEAD" },
"42": { "1": "範囲：", "2": "Workbook" },
"43": { "1": "コメント：", "2": "文字列の最初の文字を返す" },
"44": {
"1": "参照：",
"2": '=LAMBDA(str,\r\n IF(str="",\r\n "",\r\n LEFT(str, 1)\r\n )\r\n)',
},
"50": { "1": "3b. ［名前の管理］で以下のように定義します：" },
"51": { "1": "名前：", "2": "TAIL" },
"52": { "1": "範囲：", "2": "Workbook" },
"53": { "1": "コメント：", "2": "文字列から1文字目を除いたものを返す" },
"54": {
"1": "参照：",
"2": '=LAMBDA(str,\r\n IF(str="",\r\n "",\r\n RIGHT(str, LEN(str) - 1)\r\n )\r\n)',
},
"60": { "1": "3c. ［名前の管理］で以下のように定義します：" },
"61": { "1": "名前：", "2": "REVERSE" },
"62": { "1": "範囲：", "2": "Workbook" },
"63": { "1": "コメント：", "2": "文字列を逆順で返す" },
"64": {
"1": "参照：",
"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": "データ", "2": "数式", "3": "結果" },
"71": { "1": "palindrome" },
"72": { "1": "backwards" },
"73": { "1": "forwards" },
"75": { "1": "例4： 文字列が回文であるかどうかを調べる" },
"76": { "1": "［名前の管理］で以下のように定義します：" },
"77": { "1": "名前：", "2": "IsPalindrome" },
"78": { "1": "範囲：", "2": "Workbook" },
"79": { "1": "コメント：", "2": "文字列が回文である場合に TRUE を返します" },
"80": {
"1": "参照：",
"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": "データ", "2": "数式", "3": "結果" },
"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関数" },
"2": {
"1": "この例のLAMBDA関数は、連続した日付からセル範囲にカレンダーを生成します。",
},
"5": { "1": "Calendarの構文：" },
"6": { "1": "=CALENDAR(serial, mark)" },
"7": { "1": "serial： カレンダーを生成するための連続した日付。" },
"8": { "1": 'mark： 連続した日付に "X " 印を付ける場合はTrue、それ以外はFalse。' },
"10": { "1": "この例のLAMBDA関数は複雑で、いくつかの定義済みの名前が必要です：" },
"12": { "1": "名前：", "2": "months" },
"13": {
"1": "参照：",
"2": '={"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"}',
},
"15": { "1": "名前：", "2": "days" },
"16": { "1": "参照：", "2": '={"Mon";"Tue";"Wed";"Thu";"Fri";"Sat";"Sun"}' },
"18": { "1": "名前：", "2": "getDay" },
"19": { "1": "範囲：", "2": "Workbook" },
"20": { "1": "コメント：", "2": "連続する日付の曜日をテキストで取得します。" },
"21": { "1": "参照：", "2": "=LAMBDA(serial, INDEX(days, WEEKDAY(serial, 2),1))" },
"23": { "1": "名前：", "2": "vcat" },
"24": { "1": "範囲：", "2": "Workbook" },
"25": {
"1": "コメント：",
"2": "2つの配列を順に重ねて1つの配列に連結します。",
},
"27": {
"1": "参照：",
"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の実装" },
"42": { "1": "［名前の管理］で以下のように定義します：" },
"43": { "1": "名前：", "2": "Calendar" },
"44": { "1": "範囲：", "2": "Workbook" },
"45": { "1": "コメント：", "2": "連続した日付からセル範囲にカレンダーを生成します。" },
"46": {
"1": "参照：",
"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": "例" },
"85": { "1": "日付：", "2": "2021-12-31T16:00:00.000Z", "4": "数式：" },
"86": {
"1": "マーク：",
"2": false,
"4": "結果："
}
},
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関数" },
"2": {
"1": "この例のLAMBDA関数は、グラフ化するデータの最小値と最大値から、適切な軸の境界と増分値を計算する。",
},
"5": { "1": "NiceAxisの構文：" },
"6": { "1": "=NiceAxis(min, max)" },
"7": { "1": "min： グラフ化するデータの最小値。" },
"8": { "1": "max： グラフ化するデータの最大値。" },
"10": { "1": "LAMBDAの実装" },
"11": { "1": "［名前の管理］で以下のように定義します：" },
"12": { "1": "名前：", "2": "Calendar" },
"13": { "1": "範囲：", "2": "Workbook" },
"14": { "1": "コメント：", "2": "グラフの適切な軸の境界と増分値を計算します" },
"15": {
"1": "参照：",
"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": "例" },
"57": { "1": "データ", "3": "数式：" },
"58": { "1": 12.5, "3": "最小値" },
"59": { "1": 23.6, "3": "最大値" },
"60": { "1": 22.8, "3": "増分値" },
"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);
}
}
enableProdMode();
bootstrapApplication(AppComponent);
<!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$/ja/angular/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- Polyfills -->
<script src="$DEMOROOT$/ja/angular/node_modules/core-js/client/shim.min.js"></script>
<script src="$DEMOROOT$/ja/angular/node_modules/zone.js/fesm2015/zone.min.js"></script>
<!-- SystemJS -->
<script src="$DEMOROOT$/ja/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$/ja/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',
'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',
'tslib':'npm:tslib/tslib.js',
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-resources-ja': 'npm:@mescius/spread-sheets-resources-ja/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',
meta: {
"*.component.ts": {
loader: "system.component-loader.js"
}
}
},
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);
