MAKEARRAY

Returns a calculated array of a specified row and column size, by applying a LAMBDA.

Syntax The MAKEARRAY function syntax has the following arguments and parameters: rows The number of rows in the array. Must be greater than zero. cols The number of columns in the array. Must be greater than zero. lambda A LAMBDA that is called to create the array. The LAMBDA takes two parameters: row The row index of the array. col The column index of the array. Tip Before using this function, you need to turn on the allowDynamicArray option Basic Usage
var data = { values: { 1: { 1: "Examples:" }, 3: { 1: "Example 1: Create a 2D array representing a simple multiplication table" }, 4: { 1: "Formula:" }, 5: { 1: "Result:" }, 9: { 1: "Example 2: Create a random list of values" }, 10: { 1: "Rows:", 2: 10, 3: "Cols:", 4: 5 }, 11: { 1: "Formula:" }, 12: { 1: "Result:" }, }, formulas: { 4: { 2: "FORMULATEXT(C6)" }, 5: { 2: "MAKEARRAY(3,3,LAMBDA(r,c,r*c))" }, 11: { 2: "FORMULATEXT(C13)" }, 12: { 2: 'MAKEARRAY(C11,E11,LAMBDA(row,col,CHOOSE(RANDBETWEEN(1,3),"Red","Blue","Green")))' }, }, cellStyles: { "B2:C2": 0, "B4:H4": 1, B5: 2, "C5:E8": 3, F5: 3, B6: 4, "B10:E10": 1, "B11:B12": 2, C11: 5, D11: 2, E11: 5, "C12:K12": 3, B13: 4, "C13:G22": 6, }, styles: { records: [ { foreColor: 0, font: 0, border: [null, null, 0] }, { foreColor: 0, font: 1, border: [null, null, 1] }, { backColor: 2, foreColor: 1, font: 1, border: [2, 3, 2, 2] }, { backColor: 4, foreColor: 3, font: 1, border: [3, 3, 3, 3] }, { backColor: 2, foreColor: 1, font: 1, border: [2, 2, 2, 2] }, { backColor: 6, foreColor: 5, border: [3, 3, 3, 3] }, { backColor: 4, foreColor: 7, font: 2, border: [4, 4, 4, 4] }, ], borders: [ { color: "#accdea", style: 5 }, { color: "#9bc3e6", style: 2 }, { color: "#9bc3e6", style: 1 }, { color: "#7f7f7f", style: 1 }, { color: "#000000", style: 1 }, ], colors: ["#44546a", "#ffffff", "#5b9bd5", "#fa7d00", "#f2f2f2", "#3f3f76", "#ffcc99", "#ed7d31"], fonts: ["700 17.3px Calibri", "700 14.7px Calibri", "normal bold 14.7px Calibri"], }, others: { columnWidth: { 0: 30, 8: 72, 9: 72, 10: 72 }, rowHeight: { 1: 24, 2: 21, 3: 21, 9: 21 }, }, }; window.onload = function () { var workbook = new GC.Spread.Sheets.Workbook(document.getElementById("ss")); workbook.options.allowDynamicArray = true; workbook.suspendPaint(); 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); }); } function createLineStyle(v) { return new GC.Spread.Sheets.LineBorder(v.color, v.style); } function foreachObj(obj, func) { if (!obj) return; var keys = Object.keys(obj); for (var i = 0; i < keys.length; i++) { var key = keys[i]; var v = obj[key]; func(key, v); } }
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script> <script src="app.js" type="text/javascript"></script> <link rel="stylesheet" type="text/css" href="styles.css"> </head> <body> <div class="sample-tutorial"> <div id="ss" class="sample-spreadsheets"></div> </div> </body> </html>
input[type="text"] { width: 200px; margin-right: 20px; } label { display: inline-block; width: 110px; } .sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: 100%; height: 100%; overflow: hidden; float: left; } label { display: block; margin-bottom: 6px; } input { padding: 4px 6px; } input[type=button] { margin-top: 6px; display: block; width:216px; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } code { border: 1px solid #000; }