SpreadJS supports the AGGREGATE function to do aggregate operations. The syntax is same as Excel.
The first argument is the function number (1-19) which indicate the function used.
Function numberFunction
1AVERAGE
2COUNT
3COUNTA
4MAX
5MIN
6PRODUCT
7STDEV.S
8STDEV.P
9SUM
10VAR.S
11VAR.P
12MEDIAN
13MODE.SNGL
14LARGE
15SMALL
16PERCENTILE.INC
17QUARTILE.INC
18APERCENTILE.EXC
19QUARTILE.EXC
The second argument specify the options to ignore values in the calculation.
OptionBehavior
0 or omittedIgnore nested SUBTOTAL and AGGREGATE functions
1Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions
2Ignore error values, nested SUBTOTAL and AGGREGATE functions
3Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
4Ignore nothing
5Ignore hidden rows
6Ignore error values
7Ignore hidden rows and error values
With function number 1-13, the rest arguments is reference to a range of cells.
Function number 14-19 needs an array formula or a reference to a range of cells as third argument, and k to specify the function's argument.
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import './styles.css';
import { AppFunc } from './app-func';
import { App } from './app-class';
// 1. Functional Component sample
createRoot(document.getElementById('app')).render(<AppFunc />);
// 2. Class Component sample
// createRoot(document.getElementById('app')).render(<App />);
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import './styles.css';
export function AppFunc() {
const initSpread = (spread) => {
var salesData = [
["Salesperson", "Birthdate", "Region", "SaleAmount", "Commission%", "CommissionAmount"],
["Joe", new Date("2000/01/23"), "North", 260, 0.1, 26],
["Robert", new Date("1988/08/21"), "South", 660, 0.15, 99],
["Michelle", new Date("1995/08/03"), "East", 940, 0.15, 141],
["Erich", new Date("1994/05/23"), "West", '', 0, 49.2],
["Dafna", new Date("1992/07/21"), "North", 800, 0.15, 120],
["Rob", new Date("1995/11/03"), "South", 900, 0.15, 135],
["Jonason", new Date("1987/02/11"), "West", 300, 0.17, 110],
["Enana", new Date("1997/04/01"), "West", 310, 0.16, 99.2],
["Robin", new Date("1991/12/28"), "East", 450, 0.18, 35],
["Dania", new Date("1997/02/15"), "North", 500, 0.10, 76]
];
var sheet = spread.getSheet(0);
sheet.suspendPaint();
sheet.setArray(8, 1, salesData);
sheet.setFormula(12, 6, "=E13*F13");
var filter = new GC.Spread.Sheets.Filter.HideRowFilter(new GC.Spread.Sheets.Range(9, 1, salesData.length - 1, salesData[0].length));
sheet.rowFilter(filter);
var condition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.textCondition, {
compareType: GC.Spread.Sheets.ConditionalFormatting.TextCompareType.contains,
expected: "th"
});
filter.addFilterItem(3, condition);
filter.filter(3);
sheet.setText(0, 2, "Filter the data and check the formula result changes.");
sheet.setArray(2, 2, [["Ignore hidden rows", "SUM CommissionAmount"], ["", "MAX CommissionAmount"], ["", ""], ["Ignore error", "SUM CommissionAmount"], ["", "MAX CommissionAmount"]]);
sheet.setFormula(2, 4, "=AGGREGATE(9,5,G10:G19)");
sheet.setFormula(3, 4, "=AGGREGATE(14,5,G10:G19, 1)");
sheet.setFormula(5, 4, "=AGGREGATE(9,6,G10:G19)");
sheet.setFormula(6, 4, "=AGGREGATE(14,6,G10:G19, 1)");
sheet.setFormula(2, 5, "=FORMULATEXT(E3)");
sheet.setFormula(3, 5, "=FORMULATEXT(E4)");
sheet.setFormula(5, 5, "=FORMULATEXT(E6)");
sheet.setFormula(6, 5, "=FORMULATEXT(E7)");
sheet.getRange(9, 5, 10, 1).formatter(new GC.Spread.Formatter.GeneralFormatter("0.00%"));
sheet.getRange(8, 1, 1, 6).backColor('rgb(219,225,240)');
initStyle(sheet);
sheet.resumePaint();
}
const initStyle = (sheet) => {
sheet.defaults.colWidth = 100;
sheet.setColumnWidth(2, 140);
sheet.getRange(9, 2, 10, 1).formatter("mm-dd-yyyy");
sheet.setColumnWidth(3, 160);
sheet.setColumnWidth(5, 160);
sheet.setColumnWidth(6, 160);
sheet.addSpan(2, 2, 2, 1);
sheet.addSpan(5, 2, 2, 1);
var lineStyle = GC.Spread.Sheets.LineStyle.thin;
var sheetArea = GC.Spread.Sheets.SheetArea.viewport;
var lineBorder = new GC.Spread.Sheets.LineBorder('orange', lineStyle);
sheet.getRange(0, 2, 1, 3).setBorder(lineBorder, { outline: true }, sheetArea);
lineBorder = new GC.Spread.Sheets.LineBorder('red', lineStyle);
sheet.getRange(2, 2, 2, 5).setBorder(lineBorder, { outline: true }, sheetArea);
lineBorder = new GC.Spread.Sheets.LineBorder('green', lineStyle);
sheet.getRange(5, 2, 2, 5).setBorder(lineBorder, { outline: true }, sheetArea);
lineBorder = new GC.Spread.Sheets.LineBorder('blue', lineStyle);
sheet.getRange(8, 1, 11, 6).setBorder(lineBorder, { outline: true }, sheetArea);
}
return <div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
<Worksheet>
</Worksheet>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
</div>;
}
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import './styles.css';
const Component = React.Component;
export class App extends Component {
constructor(props) {
super(props);
}
render() {
return <div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}>
<Worksheet>
</Worksheet>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
</div>;
}
initSpread(spread) {
var salesData = [
["Salesperson", "Birthdate", "Region", "SaleAmount", "Commission%", "CommissionAmount"],
["Joe", new Date("2000/01/23"), "North", 260, 0.1, 26],
["Robert", new Date("1988/08/21"), "South", 660, 0.15, 99],
["Michelle", new Date("1995/08/03"), "East", 940, 0.15, 141],
["Erich", new Date("1994/05/23"), "West", '', 0, 49.2],
["Dafna", new Date("1992/07/21"), "North", 800, 0.15, 120],
["Rob", new Date("1995/11/03"), "South", 900, 0.15, 135],
["Jonason", new Date("1987/02/11"), "West", 300, 0.17, 110],
["Enana", new Date("1997/04/01"), "West", 310, 0.16, 99.2],
["Robin", new Date("1991/12/28"), "East", 450, 0.18, 35],
["Dania", new Date("1997/02/15"), "North", 500, 0.10, 76]
];
var sheet = spread.getSheet(0);
sheet.suspendPaint();
sheet.setArray(8, 1, salesData);
sheet.setFormula(12, 6, "=E13*F13");
var filter = new GC.Spread.Sheets.Filter.HideRowFilter(new GC.Spread.Sheets.Range(9, 1, salesData.length - 1, salesData[0].length));
sheet.rowFilter(filter);
var condition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.textCondition, {
compareType: GC.Spread.Sheets.ConditionalFormatting.TextCompareType.contains,
expected: "th"
});
filter.addFilterItem(3, condition);
filter.filter(3);
sheet.setText(0, 2, "Filter the data and check the formula result changes.");
sheet.setArray(2, 2, [["Ignore hidden rows", "SUM CommissionAmount"], ["", "MAX CommissionAmount"], ["", ""], ["Ignore error", "SUM CommissionAmount"], ["", "MAX CommissionAmount"]]);
sheet.setFormula(2, 4, "=AGGREGATE(9,5,G10:G19)");
sheet.setFormula(3, 4, "=AGGREGATE(14,5,G10:G19, 1)");
sheet.setFormula(5, 4, "=AGGREGATE(9,6,G10:G19)");
sheet.setFormula(6, 4, "=AGGREGATE(14,6,G10:G19, 1)");
sheet.setFormula(2, 5, "=FORMULATEXT(E3)");
sheet.setFormula(3, 5, "=FORMULATEXT(E4)");
sheet.setFormula(5, 5, "=FORMULATEXT(E6)");
sheet.setFormula(6, 5, "=FORMULATEXT(E7)");
sheet.getRange(9, 5, 10, 1).formatter(new GC.Spread.Formatter.GeneralFormatter("0.00%"));
sheet.getRange(8, 1, 1, 6).backColor('rgb(219,225,240)');
this.initStyle(sheet);
sheet.resumePaint();
}
initStyle(sheet) {
sheet.defaults.colWidth = 100;
sheet.setColumnWidth(2, 140);
sheet.getRange(9, 2, 10, 1).formatter("mm-dd-yyyy");
sheet.setColumnWidth(3, 160);
sheet.setColumnWidth(5, 160);
sheet.setColumnWidth(6, 160);
sheet.addSpan(2, 2, 2, 1);
sheet.addSpan(5, 2, 2, 1);
var lineStyle = GC.Spread.Sheets.LineStyle.thin;
var sheetArea = GC.Spread.Sheets.SheetArea.viewport;
var lineBorder = new GC.Spread.Sheets.LineBorder('orange', lineStyle);
sheet.getRange(0, 2, 1, 3).setBorder(lineBorder, { outline: true }, sheetArea);
lineBorder = new GC.Spread.Sheets.LineBorder('red', lineStyle);
sheet.getRange(2, 2, 2, 5).setBorder(lineBorder, { outline: true }, sheetArea);
lineBorder = new GC.Spread.Sheets.LineBorder('green', lineStyle);
sheet.getRange(5, 2, 2, 5).setBorder(lineBorder, { outline: true }, sheetArea);
lineBorder = new GC.Spread.Sheets.LineBorder('blue', lineStyle);
sheet.getRange(8, 1, 11, 6).setBorder(lineBorder, { outline: true }, sheetArea);
}
}
<!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">
<!-- 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;
}
.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;
}
#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/cjs/react.production.js',
'react-dom': 'npm:react-dom/cjs/react-dom.production.js',
'react-dom/client': 'npm:react-dom/cjs/react-dom-client.production.js',
'scheduler': 'npm:scheduler/cjs/scheduler.production.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);
