Array Formulas

Formulas may include functions that operate on arrays. An array formula is a formula that can perform multiple calculations on one or more of the items in an array.

Description
app.vue
index.html
Copy to CodeMine

Array formulas are often referred to as CSE (Ctrl+Shift+Enter) formulas because instead of just pressing Enter, you press Ctrl+Shift+Enter to complete the formula.

SpreadJS supports array constants in formulas. Use curly brackets ({ }) to enclose the array elements. Use a comma to separate elements within a row. Use a semicolon to separate rows within the array. Individual elements can be number values, text values, logical values, or error values. For example:

    ={1, 2, 3} * {4; 5; 6}
    /* Returns an array as follows:
       [[4,  8, 12],
        [5, 10, 15],
        [6, 12, 18]]
    */

You can use the setArrayFormula method to set an array formula, similar to the setFormula method. The formula text box shows the array formula surrounded by curly brackets ({ }).

    // set array formula use range
    sheet.setArrayFormula(6, 4, 4, 3, '=A1:A4*A1:C1');
Array formulas are often referred to as CSE (Ctrl+Shift+Enter) formulas because instead of just pressing Enter, you press Ctrl+Shift+Enter to complete the formula. SpreadJS supports array constants in formulas. Use curly brackets ({ }) to enclose the array elements. Use a comma to separate elements within a row. Use a semicolon to separate rows within the array. Individual elements can be number values, text values, logical values, or error values. For example: You can use the setArrayFormula method to set an array formula, similar to the setFormula method. The formula text box shows the array formula surrounded by curly brackets ({ }).
<template> <div class="sample-tutorial"> <gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread"> <gc-worksheet> </gc-worksheet> </gc-spread-sheets> <div class="options-container"> <textarea id="formulaBar" rows="10" readonly="readonly"></textarea> </div> </div> </template> <script setup> import GC from "@mescius/spread-sheets"; const spreadNS = GC.Spread.Sheets; function initSpread (spread) { spread.suspendPaint(); var fbx = new spreadNS.FormulaTextBox.FormulaTextBox(document.getElementById('formulaBar')); fbx.workbook(spread); var oldArrayRange; var arrayFormulaStyle = new spreadNS.Style(); arrayFormulaStyle.backColor = "#E0E0FF"; for (var i = 0; i < spread.sheets.length; i++) { (function (sheet1) { sheet1.bind(spreadNS.Events.SelectionChanged, null, function (args) { sheet1.suspendPaint(); var formulaBar = document.getElementById("formulaBar"); var formulaInfo = sheet1.getFormulaInformation(sheet1.getActiveRowIndex(), sheet1.getActiveColumnIndex()); if (oldArrayRange && (!formulaInfo || !formulaInfo.isArrayFormula || !formulaInfo.baseRange.equals(oldArrayRange))) { for (var r = oldArrayRange.row; r < oldArrayRange.row + oldArrayRange.rowCount; r++) { for (var c = oldArrayRange.col; c < oldArrayRange.col + oldArrayRange.colCount; c++) { sheet1.setStyle(r, c, null); } } oldArrayRange = null; } if (formulaInfo && formulaInfo.hasFormula) { var formula = "=" + formulaInfo.formula; formula = formulaInfo.isArrayFormula ? "{" + formula + "}" : formula; formulaBar.value=formula; if (formulaInfo.isArrayFormula && !formulaInfo.baseRange.equals(oldArrayRange)) { oldArrayRange = formulaInfo.baseRange; for (var r = oldArrayRange.row; r < oldArrayRange.row + oldArrayRange.rowCount; r++) { for (var c = oldArrayRange.col; c < oldArrayRange.col + oldArrayRange.colCount; c++) { sheet1.setStyle(r, c, arrayFormulaStyle); } } } } else { formulaBar.value=sheet1.getValue(sheet1.getActiveRowIndex(), sheet1.getActiveColumnIndex()); } sheet1.resumePaint(); }); })(spread.sheets[i]); } setBasic(spread.sheets[0]); spread.resumePaint(); } function setBasic (sheet) { sheet.name("Simple"); sheet.setArray(0, 0, [[1, 2, 3, 4, 5, 6], [4, 5, 6], [7, 8, 9], [10, 11, 12]]); sheet.addSpan(0, 7, 4, 3); sheet.setValue(0, 7, "Set array formula by:\n 1st select the cell / range;\n 2nd enter the formula;\n last press Ctrl+Shift+Enter."); setDescriptionStyle(sheet.getCell(0, 7)); sheet.setRowHeight(5, 40); sheet.addSpan(5, 0, 1, 3); sheet.setValue(5, 0, "Array formula use constants\n ex. ={1,2,3}*{4;5;6}"); setDescriptionStyle(sheet.getCell(5, 0)); sheet.setArrayFormula(6, 0, 3, 3, "={1,2,3}*{4;5;6}"); sheet.addSpan(5, 4, 1, 3); sheet.setValue(5, 4, "Array formula use range\n ex. =A1:A4*A1:C1"); setDescriptionStyle(sheet.getCell(5, 4)); sheet.setArrayFormula(6, 4, 4, 3, "=A1:A4*A1:C1"); sheet.addSpan(5, 8, 1, 4); sheet.setValue(5, 8, "Array formula use range & function\n ex. =SUM(A1:A4*A1:C1)"); setDescriptionStyle(sheet.getCell(5, 8)); sheet.setArrayFormula(6, 8, 4, 2, "=SUM(A1:A4*A1:C1)"); sheet.setRowHeight(11, 40); sheet.addSpan(11, 0, 1, 4); sheet.setValue(11, 0, "Array formula copy column range\n ex. =A1:A2"); setDescriptionStyle(sheet.getCell(11, 0)); sheet.setArrayFormula(12, 0, 2, 3, "=A1:A2"); sheet.addSpan(11, 5, 1, 3); sheet.setValue(11, 5, "Array formula copy row range\n ex. =A1:B1"); setDescriptionStyle(sheet.getCell(11, 5)); sheet.setArrayFormula(12, 5, 4, 2, "=A1:B1"); sheet.setRowHeight(17, 40); sheet.addSpan(17, 0, 1, 4); sheet.setValue(17, 0, "Array formula out of range value is #N/A:\n ex. ={1,2,3}*{4;5;6}"); setDescriptionStyle(sheet.getCell(17, 0)); sheet.setArrayFormula(18, 0, 4, 4, "={1,2,3}*{4;5;6}"); } function setDescriptionStyle (cell) { cell.wordWrap(true).backColor("Accent 5 80").vAlign(spreadNS.VerticalAlign.center); } </script> <style scoped> #app { height: 100%; } .sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: calc(100% - 280px); height: 100%; overflow: hidden; float: left; } .options-container { float: right; width: 280px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } textarea { width: 100%; box-sizing: border-box; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } </style>
<!DOCTYPE html> <html lang="en" style="height:100%;font-size:14px;"> <head> <meta charset="utf-8" /> <meta http-equiv="X-UA-Compatible" content="IE=edge" /> <title>SpreadJS VUE</title> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/vue3/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/vue3/node_modules/systemjs/dist/system.src.js"></script> <script src="./systemjs.config.js"></script> <script src="./compiler.js" type="module"></script> <script> var System = SystemJS; System.import("./src/app.js"); System.import('$DEMOROOT$/en/lib/vue3/license.js'); </script> </head> <body> <div id="app"></div> </body> </html>
(function (global) { SystemJS.config({ transpiler: 'plugin-babel', babelOptions: { es2015: true }, paths: { // paths serve as alias 'npm:': 'node_modules/' }, packageConfigPaths: [ './node_modules/*/package.json', "./node_modules/@mescius/*/package.json", "./node_modules/@babel/*/package.json", "./node_modules/@vue/*/package.json" ], map: { '@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js', '@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js', 'vue': "npm:vue/dist/vue.esm-browser.js", 'tiny-emitter': 'npm:tiny-emitter/index.js', 'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js', "systemjs-babel-build": "npm:systemjs-plugin-babel/systemjs-babel-browser.js", }, meta: { '*.css': { loader: 'systemjs-plugin-css' }, '*.vue': { loader: "../plugin-vue/index.js" } } }); })(this);