Syntax
The SCAN function syntax has the following arguments and parameters:
initial_value
Sets the starting value for the accumulator.
array
An array to be scanned.
lambda
A LAMBDA that is called to scan the array. The LAMBDA takes two parameters:
accumulator
The value totaled up and returned as the final result.
value
The calculation applied to each element in the array.
Tip
Before using this function, you need to turn on the allowDynamicArray option
Basic Usage
Create a list of factorials
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
<gc-worksheet>
</gc-worksheet>
</gc-spread-sheets>
</div>
</template>
<script setup>
import '@mescius/spread-sheets-vue';
import { ref } from "vue";
import GC from "@mescius/spread-sheets";
const spreadRef = ref(null);
let initSpread = function (workbook) {
workbook.options.allowDynamicArray = true;
workbook.suspendPaint();
initSheet1(workbook.getSheet(0), getData());
workbook.resumePaint();
}
let getData = function () {
return {
values: {
1: { 1: "Examples:" },
3: { 1: "Example 1: Create a list of factorials" },
4: { 1: "Data:", 5: "Formula:" },
5: { 1: 1, 2: 2, 3: 3, 5: "Result:" },
6: { 1: 4, 2: 5, 3: 6 },
8: { 1: "Example 2: Concatenate characters in an array" },
9: { 1: "Data:", 5: "Formula:" },
10: { 1: "a", 2: "b", 3: "c", 5: "Result:" },
11: { 1: "d", 2: "e", 3: "f" },
},
formulas: {
4: { 6: "FORMULATEXT(G6)" },
5: { 6: "SCAN(1,B6:D7,LAMBDA(a,b,a*b))" },
9: { 6: "FORMULATEXT(G11)" },
10: { 6: 'SCAN("",B11:D12,LAMBDA(a,b,a&b))' },
},
cellStyles: {
"B2:C2": 0,
"B4:E4": 1,
B5: 2,
C5: 3,
D5: 4,
F5: 5,
"G5:I7": 6,
"B6:D7": 7,
F6: 8,
"B9:E9": 1,
B10: 2,
C10: 3,
D10: 4,
F10: 5,
"G10:I12": 6,
"B11:D12": 7,
F11: 8,
},
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: [null, null, 2, 3] },
{ backColor: 2, foreColor: 1, font: 1, border: [4, null, 2] },
{ backColor: 2, foreColor: 1, font: 1, border: [4, 3, 2] },
{ backColor: 2, foreColor: 1, font: 1, border: [3, 2, 3, 3] },
{ backColor: 4, foreColor: 3, font: 1, border: [2, 2, 2, 2] },
{ backColor: 6, foreColor: 5, border: [2, 2, 2, 2] },
{ backColor: 2, foreColor: 1, font: 1, border: [3, 3, 3, 3] },
],
borders: [
{ color: "#accdea", style: 5 },
{ color: "#9bc3e6", style: 2 },
{ color: "#7f7f7f", style: 1 },
{ color: "#9bc3e6", style: 1 },
{ color: "#000000", style: 1 },
],
colors: ["#44546a", "#ffffff", "#5b9bd5", "#fa7d00", "#f2f2f2", "#3f3f76", "#ffcc99"],
fonts: ["700 17.3px Calibri", "700 14.7px Calibri"],
},
others: {
columnWidth: { 0: 30, 6: 80, 7: 80, 8: 80 },
rowHeight: { 1: 24, 2: 21, 3: 21, 8: 21 },
spans: ["B5:D5", "B10:D10"],
},
};
}
let initSheet1 = function (sheet, data) {
setSheetPr(sheet, data);
setCells(sheet, data);
}
let setCells = function (sheet, data) {
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], data.styles);
});
}
let setValue = function (sheet, r, c, v) {
if (v === undefined || v === null) return;
sheet.setValue(r, c, v);
}
let setFormula = function (sheet, r, c, v) {
if (v === undefined || v === null) return;
sheet.setFormula(r, c, v);
}
let setStyle = function (sheet, ref, v, styles) {
if (v === undefined || v === null) return;
let range = sheet.getRange(ref);
let foreColor = styles.colors[v.foreColor];
let backColor = styles.colors[v.backColor];
let font = styles.fonts[v.font];
let wordWrap = v.wordWrap;
if (foreColor) {
range.foreColor(foreColor);
}
if (backColor) {
range.backColor(backColor);
}
if (font) {
range.font(font);
}
if (wordWrap) {
range.wordWrap(wordWrap);
}
let border = v.border || [];
let borderTop = styles.borders[border[0]];
let borderRight = styles.borders[border[1]];
let borderBottom = styles.borders[border[2]];
let 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));
}
}
let setSheetPr = function (sheet, data) {
// 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
let spans = data.others.spans || [];
for (let i = 0; i < spans.length; i++) {
let range = sheet.getRange(spans[i]);
sheet.addSpan(range.row, range.col, range.rowCount, range.colCount);
}
}
let createLineStyle = function (v) {
return new GC.Spread.Sheets.LineBorder(v.color, v.style);
}
let foreachObj = function (obj, func) {
if (!obj) return;
let keys = Object.keys(obj);
for (let i = 0; i < keys.length; i++) {
let key = keys[i];
let v = obj[key];
func(key, v);
}
}
</script>
<style scoped>
#app {
height: 100%;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
#switchAutoMergeMode {
margin: 10px 0px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
</style>
<!DOCTYPE html>
<html 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: {
'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",
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-resources-en': 'npm:@mescius/spread-sheets-resources-en/index.js',
'@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js'
},
meta: {
'*.css': { loader: 'systemjs-plugin-css' },
'*.vue': { loader: "../plugin-vue/index.js" }
}
});
})(this);
