Syntax
The REDUCE function syntax has the following arguments and parameters:
initial_value
Sets the starting value for the accumulator.
array
An array to be reduced.
lambda
A LAMBDA that is called to reduce 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
Sum the squared values
Count only even values
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import './styles.css';
import { AppFunc } from './app-func';
import { App } from './app-class';
// 1. Functional Component sample
ReactDOM.render(<AppFunc />, document.getElementById('app'));
// 2. Class Component sample
// ReactDOM.render(<App />, document.getElementById('app'));
import * as React from "react";
import { SpreadSheets, Worksheet } from "@mescius/spread-sheets-react";
import GC from "@mescius/spread-sheets";
import "./styles.css";
const data = {
values: {
1: { 1: "Examples:" },
3: { 1: "Example 1: Sum the squared values" },
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: Create a customized "PRODUCTIF" function to multiply only values greater than 50' },
9: { 1: "Table2:" },
10: { 1: "Nums", 5: "Formula:" },
11: { 1: 24, 5: "Result:" },
12: { 1: 100 },
13: { 1: 97 },
14: { 1: 11 },
15: { 1: 37 },
16: { 1: 42 },
17: { 1: 76 },
18: { 1: 66 },
19: { 1: 4 },
20: { 1: 62 },
22: { 1: "Example 3: Count only even values" },
23: { 1: "Table4:" },
24: { 1: "Nums", 5: "Formula:" },
25: { 1: 24, 5: "Result:" },
26: { 1: 100 },
27: { 1: 97 },
28: { 1: 11 },
29: { 1: 37 },
30: { 1: 42 },
31: { 1: 76 },
32: { 1: 66 },
33: { 1: 4 },
34: { 1: 62 },
},
formulas: {
4: { 6: "FORMULATEXT(G6)" },
5: { 6: "REDUCE(,B6:D7,LAMBDA(a,b,a+b^2))" },
10: { 6: "FORMULATEXT(G12)" },
11: { 6: "REDUCE(1,Table2[Nums],LAMBDA(a,b,IF(b>50,a+b,a)))" },
24: { 6: "FORMULATEXT(G26)" },
25: { 6: "REDUCE(0,Table4[Nums],LAMBDA(a,n,IF(ISEVEN(n),1+a,a)))" },
},
cellStyles: {
"B2:C2": 0,
"B4:E4": 1,
B5: 2,
C5: 3,
D5: 4,
F5: 5,
"G5:J5": 6,
"B6:D7": 7,
F6: 8,
G6: 6,
"B9:K9": 1,
B10: 9,
"F11:F12": 8,
"G11:L11": 6,
G12: 6,
"B23:E23": 1,
B24: 9,
"F25:F26": 8,
"G25:L25": 6,
G26: 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: [1, null, 2, 3] },
{ backColor: 2, foreColor: 1, font: 1, border: [1, null, 2] },
{ backColor: 2, foreColor: 1, font: 1, border: [1, 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] },
{ foreColor: 0, font: 1 },
],
borders: [
{ color: "#accdea", style: 5 },
{ color: "#9bc3e6", style: 2 },
{ color: "#7f7f7f", style: 1 },
{ color: "#9bc3e6", style: 1 },
],
colors: ["#44546a", "#ffffff", "#5b9bd5", "#fa7d00", "#f2f2f2", "#3f3f76", "#ffcc99"],
fonts: ["700 17.3px Calibri", "700 14.7px Calibri"],
},
others: {
columnWidth: { 0: 30 },
rowHeight: { 1: 24, 2: 21, 3: 21, 8: 21, 22: 21 },
spans: ["B5:D5"],
tables: [
{ name: "Table4", ref: "B25:B35" },
{ name: "Table2", ref: "B11:B21" },
],
},
};
export function AppFunc() {
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={(spread) => initWorkbook(spread, data)}>
<Worksheet></Worksheet>
</SpreadSheets>
</div>
</div>
);
}
function initWorkbook(workbook, data) {
workbook.options.allowDynamicArray = true;
workbook.suspendPaint();
initSheet1(workbook.getSheet(0), data);
workbook.resumePaint();
}
function initSheet1(sheet, data) {
setSheetPr(sheet, data);
setCells(sheet, data);
}
function setCells(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);
});
}
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, styles) {
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, 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
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) {
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);
}
}
import * as React from "react";
import { SpreadSheets, Worksheet } from "@mescius/spread-sheets-react";
import GC from "@mescius/spread-sheets";
import "./styles.css";
const Component = React.Component;
export class App extends Component {
constructor(props) {
super(props);
this.data = {
values: {
1: { 1: "Examples:" },
3: { 1: "Example 1: Sum the squared values" },
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: Create a customized "PRODUCTIF" function to multiply only values greater than 50' },
9: { 1: "Table2:" },
10: { 1: "Nums", 5: "Formula:" },
11: { 1: 24, 5: "Result:" },
12: { 1: 100 },
13: { 1: 97 },
14: { 1: 11 },
15: { 1: 37 },
16: { 1: 42 },
17: { 1: 76 },
18: { 1: 66 },
19: { 1: 4 },
20: { 1: 62 },
22: { 1: "Example 3: Count only even values" },
23: { 1: "Table4:" },
24: { 1: "Nums", 5: "Formula:" },
25: { 1: 24, 5: "Result:" },
26: { 1: 100 },
27: { 1: 97 },
28: { 1: 11 },
29: { 1: 37 },
30: { 1: 42 },
31: { 1: 76 },
32: { 1: 66 },
33: { 1: 4 },
34: { 1: 62 },
},
formulas: {
4: { 6: "FORMULATEXT(G6)" },
5: { 6: "REDUCE(,B6:D7,LAMBDA(a,b,a+b^2))" },
10: { 6: "FORMULATEXT(G12)" },
11: { 6: "REDUCE(1,Table2[Nums],LAMBDA(a,b,IF(b>50,a+b,a)))" },
24: { 6: "FORMULATEXT(G26)" },
25: { 6: "REDUCE(0,Table4[Nums],LAMBDA(a,n,IF(ISEVEN(n),1+a,a)))" },
},
cellStyles: {
"B2:C2": 0,
"B4:E4": 1,
B5: 2,
C5: 3,
D5: 4,
F5: 5,
"G5:J5": 6,
"B6:D7": 7,
F6: 8,
G6: 6,
"B9:K9": 1,
B10: 9,
"F11:F12": 8,
"G11:L11": 6,
G12: 6,
"B23:E23": 1,
B24: 9,
"F25:F26": 8,
"G25:L25": 6,
G26: 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: [1, null, 2, 3] },
{ backColor: 2, foreColor: 1, font: 1, border: [1, null, 2] },
{ backColor: 2, foreColor: 1, font: 1, border: [1, 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] },
{ foreColor: 0, font: 1 },
],
borders: [
{ color: "#accdea", style: 5 },
{ color: "#9bc3e6", style: 2 },
{ color: "#7f7f7f", style: 1 },
{ color: "#9bc3e6", style: 1 },
],
colors: ["#44546a", "#ffffff", "#5b9bd5", "#fa7d00", "#f2f2f2", "#3f3f76", "#ffcc99"],
fonts: ["700 17.3px Calibri", "700 14.7px Calibri"],
},
others: {
columnWidth: { 0: 30 },
rowHeight: { 1: 24, 2: 21, 3: 21, 8: 21, 22: 21 },
spans: ["B5:D5"],
tables: [
{ name: "Table4", ref: "B25:B35" },
{ name: "Table2", ref: "B11:B21" },
],
},
};
}
render() {
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={(spread) => initWorkbook(spread, this.data)}>
<Worksheet></Worksheet>
</SpreadSheets>
</div>
</div>
);
}
}
function initWorkbook(workbook, data) {
workbook.options.allowDynamicArray = true;
workbook.suspendPaint();
initSheet1(workbook.getSheet(0), data);
workbook.resumePaint();
}
function initSheet1(sheet, data) {
setSheetPr(sheet, data);
setCells(sheet, data);
}
function setCells(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);
});
}
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, styles) {
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, 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
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) {
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/react/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/spread/source/data/data.js" type="text/javascript"></script>
<!-- SystemJS -->
<script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('$DEMOROOT$/en/lib/react/license.js').then(function () {
System.import('./src/app');
});
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
input {
padding: 4px 6px;
}
.options-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
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;
}
#app {
height: 100%;
}
(function (global) {
System.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true,
react: true
},
meta: {
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-react': 'npm:@mescius/spread-sheets-react/index.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js',
'react': 'npm:react/umd/react.production.min.js',
'react-dom': 'npm:react-dom/umd/react-dom.production.min.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'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'jsx'
},
"node_modules": {
defaultExtension: 'js'
},
}
});
})(this);
