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 { Component, NgModule, enableProdMode } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';
import { SpreadSheetsModule } from '@mescius/spread-sheets-angular';
import GC from '@mescius/spread-sheets';
import './styles.css';
@Component({
selector: 'app-component',
templateUrl: 'src/app.component.html'
})
export class AppComponent {
spread: GC.Spread.Sheets.Workbook;
hostStyle = {
width: '100%',
height: '100%',
overflow: 'hidden',
float: 'left'
};
constructor() {
}
initSpread($event: any) {
this.spread = $event.spread;
let spread = this.spread;
let 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]
];
let sheet = spread.getSheet(0);
sheet.suspendPaint();
sheet.setArray(8, 1, salesData);
sheet.setFormula(12, 6, "=E13*F13");
let filter = new GC.Spread.Sheets.Filter.HideRowFilter(new GC.Spread.Sheets.Range(9, 1, salesData.length - 1, salesData[0].length));
sheet.rowFilter(filter);
let 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: GC.Spread.Sheets.Worksheet) {
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);
let lineStyle = GC.Spread.Sheets.LineStyle.thin;
let sheetArea = GC.Spread.Sheets.SheetArea.viewport;
let 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);
}
}
@NgModule({
imports: [BrowserModule, SpreadSheetsModule],
declarations: [AppComponent],
exports: [AppComponent],
bootstrap: [AppComponent]
})
export class AppModule {}
enableProdMode();
// Bootstrap application with hash style navigation and global services.
platformBrowserDynamic().bootstrapModule(AppModule);
<!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/angular/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- Polyfills -->
<script src="$DEMOROOT$/en/angular/node_modules/core-js/client/shim.min.js"></script>
<script src="$DEMOROOT$/en/angular/node_modules/zone.js/fesm2015/zone.min.js"></script>
<!-- SystemJS -->
<script src="$DEMOROOT$/en/angular/node_modules/systemjs/dist/system.js"></script>
<script src="systemjs.config.js"></script>
<script>
// workaround to load 'rxjs/operators' from the rxjs bundle
System.import('rxjs').then(function (m) {
System.import('@angular/compiler');
System.set(SystemJS.resolveSync('rxjs/operators'), System.newModule(m.operators));
System.import('$DEMOROOT$/en/lib/angular/license.ts');
System.import('./src/app.component');
});
</script>
</head>
<body>
<app-component></app-component>
</body>
</html>
<div class="sample-tutorial">
<gc-spread-sheets [hostStyle]="hostStyle" (workbookInitialized)="initSpread($event)">
<gc-worksheet>
</gc-worksheet>
<gc-worksheet>
</gc-worksheet>
</gc-spread-sheets>
</div>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
(function (global) {
System.config({
transpiler: 'ts',
typescriptOptions: {
tsconfig: true
},
meta: {
'typescript': {
"exports": "ts"
},
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'core-js': 'npm:core-js/client/shim.min.js',
'zone': 'npm:zone.js/fesm2015/zone.min.js',
'rxjs': 'npm:rxjs/dist/bundles/rxjs.umd.min.js',
'@angular/core': 'npm:@angular/core/fesm2022',
'@angular/common': 'npm:@angular/common/fesm2022/common.mjs',
'@angular/compiler': 'npm:@angular/compiler/fesm2022/compiler.mjs',
'@angular/platform-browser': 'npm:@angular/platform-browser/fesm2022/platform-browser.mjs',
'@angular/platform-browser-dynamic': 'npm:@angular/platform-browser-dynamic/fesm2022/platform-browser-dynamic.mjs',
'@angular/common/http': 'npm:@angular/common/fesm2022/http.mjs',
'@angular/router': 'npm:@angular/router/fesm2022/router.mjs',
'@angular/forms': 'npm:@angular/forms/fesm2022/forms.mjs',
'jszip': 'npm:jszip/dist/jszip.min.js',
'typescript': 'npm:typescript/lib/typescript.js',
'ts': './plugin.js',
'tslib':'npm:tslib/tslib.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',
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-angular': 'npm:@mescius/spread-sheets-angular/fesm2020/mescius-spread-sheets-angular.mjs',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'ts'
},
rxjs: {
defaultExtension: 'js'
},
"node_modules": {
defaultExtension: 'js'
},
"node_modules/@angular": {
defaultExtension: 'mjs'
},
"@mescius/spread-sheets-angular": {
defaultExtension: 'mjs'
},
'@angular/core': {
defaultExtension: 'mjs',
main: 'core.mjs'
}
}
});
})(this);
