Getting Started
Steps for getting started with FlexSheet in AngularJS applications:
Add references to AngularJS, Wijmo, and Wijmo's AngularJS directives.
Add a component to provide data and logic.
Add a FlexGrid to the page and bind it to the data.
Add a Sheet to the FlexSheet.
Add some CSS to customize the grid's appearance.
HTML
<!DOCTYPE html>
<html>
<head>
<!-- Supporting libraries -->
<!-- Polyfill(s) for older browsers -->
<script src="node_modules/core-js/client/shim.min.js"></script>
<script src="node_modules/zone.js/dist/zone.js"></script>
<script src="node_modules/reflect-metadata/Reflect.js"></script>
<script src="node_modules/systemjs/dist/system.src.js"></script>
<!-- Configure SystemJS -->
<script src="systemjs.config.js"></script>
<!-- Bootstrap -->
<link rel="stylesheet" type="text/css" href="bin/Devel/external/bootstrap/css/bootstrap.css" />
<!-- Wijmo -->
<link href="bin/Devel/sources/styles/wijmo.css" rel="stylesheet" />
<!-- Configure and load application -->
<link href="src/styles/app.css" rel="stylesheet" />
<!-- Load the root application module -->
<script>
System.import('./src/app');
</script>
</head>
<body>
<!-- Add root application component -->
<app-cmp></app-cmp>
</body>
</html>
<!-- Add root application component -->
<wj-flex-sheet #flexSheetIntro (initialized)="flexSheetInit(flexSheetIntro)">
<wj-sheet name="Country"></wj-sheet>
<wj-sheet name="Empty Sheet"></wj-sheet>
</wj-flex-sheet>
TS
import { Injectable } from '@angular/core';
// Common data service
@Injectable()
export class DataSvc {
private _products = ['Widget', 'Gadget', 'Doohickey'];
private _countries = ['US', 'Germany', 'UK', 'Japan', 'Italy', 'Greece'];
get products(): string[] {
return this._products;
}
get countries(): string[] {
return this._countries;
}
// get matches for a search term
getData(count: number): any[] {
var data = [],
i = 0,
countryId,
productId;
for (var i = 0; i < count; i++) {
countryId = Math.floor(Math.random() * this._countries.length);
productId = Math.floor(Math.random() * this._products.length);
data.push({
id: i,
countryId: countryId,
productId: productId,
date: new Date(2014, i % 12, i % 28),
amount: Math.random() * 10000,
active: i % 4 === 0
});
}
return data;
}
}
---------------------Component-------------------
// Angular
import * as wjcGridSheet from 'wijmo/wijmo.grid.sheet';
import * as wjcInput from 'wijmo/wijmo.input';
import * as wjcGrid from 'wijmo/wijmo.grid';
import * as wjcCore from 'wijmo/wijmo';
import { Component, EventEmitter, Inject, enableProdMode, ViewChild, NgModule } from '@angular/core';
import { FormsModule } from '@angular/forms';
import { CommonModule } from '@angular/common';
import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';
import { BrowserModule } from '@angular/platform-browser';
import { WjGridSheetModule } from 'wijmo/wijmo.angular2.grid.sheet';
import { WjInputModule } from 'wijmo/wijmo.angular2.input';
import { TabsModule } from './components/AppTab';
import { DataSvc } from './services/DataSvc';
'use strict';
// The Explorer application root component.
@Component({
selector: 'app-cmp',
templateUrl: 'src/app.html'
})
export class AppCmp {
protected dataSvc: DataSvc;
data: any[];
// references FlexSheet named 'flexSheet' in the view
@ViewChild('flexSheetIntro') flexSheetIntro: wjcGridSheet.FlexSheet;
constructor(@Inject(DataSvc) dataSvc: DataSvc) {
this.dataSvc = dataSvc;
this.data = dataSvc.getData(50);
}
flexSheetInit(flexSheetIntro: wjcGridSheet.FlexSheet) {
var self = this;
if (flexSheetIntro) {
flexSheetIntro.deferUpdate(() => {
flexSheetIntro.selectedSheetIndex = 0;
flexSheetIntro.selectedSheet.itemsSource = this.data;
self._initDataMapForBindingSheet(flexSheetIntro);
});
}
}
// initialize the dataMap for the bound sheet.
private _initDataMapForBindingSheet(flexSheet) {
var column;
if (flexSheet) {
column = flexSheet.columns.getColumn('countryId');
if (column && !column.dataMap) {
column.dataMap = this._buildDataMap(this.dataSvc.countries);
}
column = flexSheet.columns.getColumn('productId');
if (column && !column.dataMap) {
column.dataMap = this._buildDataMap(this.dataSvc.products);
}
}
}
// build a data map from a string array using the indices as keys
private _buildDataMap(items) {
var map = [];
for (var i = 0; i < items.length; i++) {
map.push({ key: i, value: items[i] });
}
return new wjcGrid.DataMap(map, 'key', 'value');
}
}
@NgModule({
imports: [WjInputModule, WjGridSheetModule, BrowserModule, FormsModule, TabsModule],
declarations: [AppCmp],
providers: [DataSvc],
bootstrap: [AppCmp]
})
export class AppModule {
}
enableProdMode();
// Bootstrap application with hash style navigation and global services.
platformBrowserDynamic().bootstrapModule(AppModule);
CSS
/* set default grid height and some shadow */
.wj-flexsheet {
height: 400px;
background-color: white;
box-shadow: 4px 4px 10px 0px rgba(50, 50, 50, 0.75);
margin-bottom: 12px;
}
Result (live):
Sorting
FlexSheet can be sorted by any of its columns.
The SortManager helps FlexSheet to manage the sort process. The following example uses SortManager to specify the order of the sorting, add or remove sort columns, and change the order of the sort columns.
HTML
<wj-flex-sheet #sortSheet (initialized)="sortSheetInit(sortSheet)">
<wj-sheet name="Country"></wj-sheet>
</wj-flex-sheet>
<table class="table table-bordered">
<thead>
<tr>
<th class="text-center">Column</th>
<th class="text-center">Order</th>
</tr>
</thead>
<tbody *ngIf="sortManager">
<tr *ngFor="let sortItem of sortManager.sortDescriptions.items"
(click)="sortManager.sortDescriptions.moveCurrentTo(sortItem)"
[ngClass]="{success: sortItem === sortManager.sortDescriptions.currentItem}">
<td>
<select class="form-control" (change)="applySortColumnIndex($event, sortItem)">
<option value=-1></option>
<option *ngFor="let column of columns; let i = index" [selected]="i === sortItem.columnIndex"
value={{i}}>
{{column}}
</option>
</select>
</td>
<td>
<select class="form-control" (change)="applySortAscending($event, sortItem)">
<option [value]="true" [selected]="sortItem.ascending">Ascending</option>
<option [value]="false" [selected]="!sortItem.ascending">Descending</option>
</select>
</td>
</tr>
</tbody>
</table>
<div class="btn-group">
<button type="button" class="btn btn-default" (click)="addSortLevel()">
Add Level
</button>
<button type="button" class="btn btn-default" (click)="deleteSortLevel()">
Delete Level
</button>
<button type="button" class="btn btn-default" (click)="copySortLevel()">
Copy Level
</button>
</div>
<div class="btn-group">
<button id="moveup" type="button"
class="btn btn-default"
[disabled]="sortManager && sortManager.sortDescriptions.currentPosition <= 0"
(click)="moveSortLevel(-1)">
<span class="glyphicon glyphicon-arrow-up"></span>
</button>
<button id="movedown" type="button"
class="btn btn-default"
[disabled]="sortManager && sortManager.sortDescriptions.currentPosition >= sortManager.sortDescriptions.itemCount - 1"
(click)="moveSortLevel(1)">
<span class="glyphicon glyphicon-arrow-down"></span>
</button>
</div>
<div class="btn-group">
<button type="button" class="btn btn-default" (click)="commitSort()">OK</button>
<button type="button" class="btn btn-default" (click)="cancelSort()">Cancel</button>
</div>
TS
// references FlexSheet named 'sortSheet' in the view
@ViewChild('sortSheet') sortSheet: wjcGridSheet.FlexSheet;
sortSheetInit(sortSheet: wjcGridSheet.FlexSheet) {
var self = this;
if (sortSheet) {
sortSheet.deferUpdate(() => {
sortSheet.selectedSheetIndex = 0;
sortSheet.selectedSheet.itemsSource = this.data;
self._initDataMapForBindingSheet(sortSheet);
self.sortManager = sortSheet.sortManager;
self.columns = self._getColumns(sortSheet);
});
sortSheet.selectedSheetChanged.addHandler(() => {
self.columns = self._getColumns(sortSheet);
if (!self.sortManager) {
self.sortManager = sortSheet.sortManager;
}
});
}
}
// commit the sorts
commitSort() {
this.sortManager.commitSort();
};
// cancel the sorts
cancelSort() {
this.sortManager.cancelSort();
};
// add new sort level
addSortLevel() {
this.sortManager.addSortLevel();
};
// delete current sort level
deleteSortLevel() {
this.sortManager.deleteSortLevel();
};
// copy a new sort level by current sort level setting.
copySortLevel() {
this.sortManager.copySortLevel();
};
// move the sort level
moveSortLevel(offset) {
this.sortManager.moveSortLevel(offset);
};
// apply column index property for sort item
applySortColumnIndex(e, sortItem) {
sortItem.columnIndex = +e.target.value;
}
// apply asceding property for sort item
applySortAscending(e, sortItem) {
if (e.target.value === 'true') {
sortItem.ascending = true;
} else {
sortItem.ascending = false;
}
}
private _getColumns(flexSheet: wjcGridSheet.FlexSheet): string[] {
var columns = [],
i = 0;
if (flexSheet) {
for (; i < flexSheet.columns.length; i++) {
columns.push('Column ' + wjcGridSheet.FlexSheet.convertNumberToAlpha(i));
}
}
return columns;
}
Result (live):
Column
Order
{{column}}
Ascending
Descending
Add Level
Delete Level
Copy Level
= sortManager.sortDescriptions.itemCount - 1"
(click)="moveSortLevel(1)">
OK
Cancel
FlexSheet allows you to set format for each cell. This includes setting font style, data format of cell value (Date/Number format), cell's fill color and horizontal alignment.
HTML
<wj-flex-sheet #formatSheet (initialized)="formatSheetInit(formatSheet)">
<wj-sheet name="Number" [rowCount]="20" [columnCount]="8"></wj-sheet>
<wj-sheet name="Date" [rowCount]="20" [columnCount]="8"></wj-sheet>
</wj-flex-sheet>
<wj-color-picker #colorPicker style="display:none;position:fixed;z-index:100"> (initialized)="colorPickerInit(colorPicker)"</wj-color-picker>
<div class="well well-lg">
<div>
Format:
<wj-menu header="Format" [(value)]="format">
<wj-menu-item value="0">Decimal Format</wj-menu-item>
<wj-menu-item value="n2">Number Format</wj-menu-item>
<wj-menu-item value="p">Percentage Format</wj-menu-item>
<wj-menu-item value="c2">Currency Format</wj-menu-item>
<wj-menu-separator></wj-menu-separator>
<wj-menu-item value="d">Short Date</wj-menu-item>
<wj-menu-item value="D">Long Date</wj-menu-item>
<wj-menu-item value="f">Full Date/TIme (short time)</wj-menu-item>
<wj-menu-item value="F">Full Date/TIme (long time)</wj-menu-item>
</wj-menu>
</div>
<div>
Font:
<wj-combo-box #cboFontName
style="width:120px"
[itemsSource]="fonts"
[selectedIndex]="10"
[displayMemberPath]="'name'"
[selectedValuePath]="'value'"
[isEditable]="false"
(initialized)="cboFontNameInit(cboFontName)">
</wj-combo-box>
<wj-combo-box #cboFontSize
style="width:80px"
[itemsSource]="fontSizeList"
[selectedIndex]="5"
[displayMemberPath]="'name'"
[selectedValuePath]="'value'"
[isEditable]="false"
(initialized)="cboFontSizeInit(cboFontSize)">
</wj-combo-box>
<div class="btn-group">
<button type="button" class="btn btn-default {{selectionFormatState.isBold ? 'active' : ''}}" (click)="applyBoldStyle()">Bold</button>
<button type="button" class="btn btn-default {{selectionFormatState.isItalic ? 'active' : ''}}" (click)="applyItalicStyle()">Italic</button>
<button type="button" class="btn btn-default {{selectionFormatState.isUnderline ? 'active' : ''}}" (click)="applyUnderlineStyle()">Underline</button>
</div>
</div>
<div>
Color:
<div class="btn-group">
<button type="button" class="btn btn-default" (click)="showColorPicker($event, false)">Fore Color</button>
<button type="button" class="btn btn-default" (click)="showColorPicker($event, true)">Fill Color</button>
</div>
Alignment:
<div class="btn-group">
<button type="button" class="btn btn-default {{selectionFormatState.textAlign === 'left' ? 'active' : ''}}" (click)="applyCellTextAlign('left')">Left</button>
<button type="button" class="btn btn-default {{selectionFormatState.textAlign === 'center' ? 'active' : ''}}" (click)="applyCellTextAlign('center')">Center</button>
<button type="button" class="btn btn-default {{selectionFormatState.textAlign === 'right' ? 'active' : ''}}" (click)="applyCellTextAlign('right')">Right</button>
</div>
</div>
</div>
TS
// references FlexSheet named 'formatSheet' in the view
@ViewChild('formatSheet') formatSheet: wjcGridSheet.FlexSheet;
// references Combobox named 'cboFontName' in the view
@ViewChild('cboFontName') cboFontName: wjcInput.ComboBox;
// references Combobox named 'cboFontSize' in the view
@ViewChild('cboFontSize') cboFontSize: wjcInput.ComboBox;
// references Combobox named 'cboFontSize' in the view
@ViewChild('colorPicker') colorPicker: wjcInput.ColorPicker;
// Gets or sets _format for the formatSheet.
get format(): string {
return this._format;
}
set format(value: string) {
if (this._format !== value) {
this._format = value;
this.formatSheet.applyCellsStyle({ format: this._format });
}
}
formatSheetInit(formatSheet: wjcGridSheet.FlexSheet) {
var self = this;
if (formatSheet) {
formatSheet.deferUpdate(() => {
var sheetIdx,
sheetName,
colIdx,
rowIdx,
date;
for (sheetIdx = 0; sheetIdx < formatSheet.sheets.length; sheetIdx++) {
formatSheet.selectedSheetIndex = sheetIdx;
sheetName = formatSheet.selectedSheet.name;
for (colIdx = 0; colIdx < formatSheet.columns.length; colIdx++) {
for (rowIdx = 0; rowIdx < formatSheet.rows.length; rowIdx++) {
if (sheetName === 'Number') {
formatSheet.setCellData(rowIdx, colIdx, colIdx + rowIdx);
} else {
date = new Date(2015, colIdx, rowIdx + 1);
formatSheet.setCellData(rowIdx, colIdx, date);
}
}
}
}
formatSheet.selectedSheetIndex = 0;
self._updateSelection(formatSheet, formatSheet.selection);
});
formatSheet.selectionChanged.addHandler((sender: any, args: wjcGrid.CellRangeEventArgs) => {
self._updateSelection(formatSheet, args.range);
self.selectionFormatState = formatSheet.getSelectionFormatState();
});
}
}
cboFontNameInit(cboFontName: wjcInput.ComboBox) {
var self = this;
if (cboFontName) {
cboFontName.selectedIndexChanged.addHandler(() => {
// apply the font family for the selected cells
if (!self._updatingSelection) {
self.formatSheet.applyCellsStyle({ fontFamily: cboFontName.selectedItem.value });
}
});
}
}
cboFontSizeInit(cboFontSize: wjcInput.ComboBox) {
var self = this;
if (cboFontSize) {
cboFontSize.selectedIndexChanged.addHandler(() => {
// apply the font size for the selected cells
if (!self._updatingSelection) {
self.formatSheet.applyCellsStyle({ fontSize: cboFontSize.selectedItem.value });
}
});
}
}
colorPickerInit(colorPicker: wjcInput.ColorPicker) {
var self = this,
ua = window.navigator.userAgent,
blurEvt: string;
if (colorPicker) {
// if the browser is firefox, we should bind the blur event. (TFS #124387)
// if the browser is IE, we should bind the focusout event. (TFS #124500)
blurEvt = /firefox/i.test(ua) ? 'blur' : 'focusout';
// Hide the color picker control when it lost the focus.
colorPicker.hostElement.addEventListener(blurEvt, () => {
setTimeout(() => {
if (!colorPicker.containsFocus()) {
self._applyFillColor = false;
colorPicker.hostElement.style.display = 'none';
}
}, 0);
});
// Initialize the value changed event handler for the color picker control.
colorPicker.valueChanged.addHandler(() => {
if (self._applyFillColor) {
self.formatSheet.applyCellsStyle({ backgroundColor: colorPicker.value });
} else {
self.formatSheet.applyCellsStyle({ color: colorPicker.value });
}
});
}
}
// apply the text alignment for the selected cells
applyCellTextAlign(textAlign) {
if (this.formatSheet) {
this.formatSheet.applyCellsStyle({ textAlign: textAlign });
this.selectionFormatState.textAlign = textAlign;
}
}
// apply the bold font weight for the selected cells
applyBoldStyle() {
if (this.formatSheet) {
this.formatSheet.applyCellsStyle({ fontWeight: this.selectionFormatState.isBold ? 'none' : 'bold' });
this.selectionFormatState.isBold = !this.selectionFormatState.isBold;
}
}
// apply the underline text decoration for the selected cells
applyUnderlineStyle() {
if (this.formatSheet) {
this.formatSheet.applyCellsStyle({ textDecoration: this.selectionFormatState.isUnderline ? 'none' : 'underline' });
this.selectionFormatState.isUnderline = !this.selectionFormatState.isUnderline;
}
}
// apply the italic font style for the selected cells
applyItalicStyle() {
if (this.formatSheet) {
this.formatSheet.applyCellsStyle({ fontStyle: this.selectionFormatState.isItalic ? 'none' : 'italic' });
this.selectionFormatState.isItalic = !this.selectionFormatState.isItalic;
}
}
Result (live):
Format:
Decimal Format
Number Format
Percentage Format
Currency Format
Short Date
Long Date
Full Date/TIme (short time)
Full Date/TIme (long time)
Font:
Bold
Italic
Underline
Color:
Fore Color
Fill Color
Alignment:
Left
Center
Right
Cell Merging
FlexSheet supports merging the selected cells into one by invoking the mergeRange method.
If the selected cells contain merged cell, the mergeRange method will un-merge the merged cell. Otherwise, it will merge the selected cells into one cell.
FlexSheet allows merging of cells that contain any data. This is different from FlexGrid, which supports content-driven cell merging.
HTML
<wj-flex-sheet #mergeCellSheet (initialized)="mergeCellSheetInit(mergeCellSheet)">
<wj-sheet name="Unbound" [rowCount]="20" [columnCount]></wj-sheet>
</wj-flex-sheet>
<button type="button" class="btn btn-default" (click)="mergeCells()">{{mergeState.isMergedCell ? 'UnMerge' : 'Merge'}}</button>
TS
// references FlexSheet named 'mergeCellSheet' in the view
@ViewChild('mergeCellSheet') mergeCellSheet: wjcGridSheet.FlexSheet;
mergeCellSheetInit(mergeCellSheet: wjcGridSheet.FlexSheet) {
var self = this;
if (mergeCellSheet) {
mergeCellSheet.selectionChanged.addHandler(function () {
self.mergeState = mergeCellSheet.getSelectionFormatState();
});
}
}
mergeCells() {
if (this.mergeCellSheet) {
this.mergeCellSheet.mergeRange();
this.mergeState = this.mergeCellSheet.getSelectionFormatState();
}
}
Result (live):
{{mergeState.isMergedCell ? 'UnMerge' : 'Merge'}}
Drag & Drop
FlexSheet supports dragging and dropping the columns or rows into other columns or rows.
FlexSheet not only copies or moves the data of the cells, but also copies or moves the style of the cells.
When you drag and drop columns/rows without pressing any key, it will move the selected columns or rows into the target columns or rows.
When you drag and drop columns/rows with 'Ctrl' key pressed, it will copy the selected columns or rows into the target columns or rows.
When you drag and drop columns/rows with 'Shift' key pressed, it will change the position of the selected columns or rows with the target columns or rows.
HTML
<wj-flex-sheet #dragDropSheet (initialized)="dragDropSheetInit(dragDropSheet)">
<wj-sheet name="Unbound" [rowCount]="12" [columnCount]="8"></wj-sheet>
</wj-flex-sheet>
Result (live):
Frozen Cells
FlexSheet allows you to freeze the rows and columns of the selected cell by the freezeAtCursor method.
HTML
<wj-flex-sheet #frozenSheet (initialized)="frozenSheetInit(frozenSheet)">
<wj-sheet name="Unbound" [rowCount]="20" [columnCount]="8"></wj-sheet>
</wj-flex-sheet>
<button type="button" class="btn btn-default" (click)="freezeCells()">{{isFrozen ? 'UnFreeze' : 'Freeze'}}</button>
TS
// references FlexSheet named 'frozenSheet' in the view
@ViewChild('frozenSheet') frozenSheet: wjcGridSheet.FlexSheet;
frozenSheetInit(frozenSheet: wjcGridSheet.FlexSheet) {
var self = this;
if (frozenSheet) {
frozenSheet.selectedSheetChanged.addHandler(() => {
if (frozenSheet.frozenColumns > 0 || frozenSheet.frozenRows > 0) {
self.isFrozen = true;
} else {
self.isFrozen = false;
}
});
}
}
freezeCells() {
if (this.frozenSheet) {
this.frozenSheet.freezeAtCursor();
if (this.frozenSheet.frozenColumns > 0 || this.frozenSheet.frozenRows > 0) {
this.isFrozen = true;
} else {
this.isFrozen = false;
}
}
}
Result (live):
{{isFrozen ? 'UnFreeze' : 'Freeze'}}
Undo / Redo
The FlexSheet control enables you to undo/redo following operations:
Editing cells
Resizing row/column
Adding/Removing rows/columns
Changing cell style
Merging cells
Sorting
Dragging and Dropping rows/columns
HTML
<wj-flex-sheet #undoSheet (initialized)="undoSheetInit(undoSheet)">
<wj-sheet name="Unbound" [rowCount]="20" [columnCount]="8"></wj-sheet>
</wj-flex-sheet>
<button type="button" class="btn btn-default" [disabled]="!(undoStack && undoStack.canUndo)" (click)="undo()">Undo</button>
<button type="button" class="btn btn-default" [disabled]="!(undoStack && undoStack.canRedo)" (click)="redo()">Redo</button>
TS
// references FlexSheet named 'undoSheet' in the view
@ViewChild('undoSheet') undoSheet: wjcGridSheet.FlexSheet;
// Excutes undo command.
undo () {
this.undoSheet.undo();
}
// Excutes redo command.
redo () {
this.undoSheet.redo();
}
Result (live):
Undo
Redo
The FlexSheet control has a built-in calculation engine that supports over 80 functions just like Microsoft Excel. (See complete list. )
HTML
<wj-flex-sheet #formulaSheet (initialized)="formulaSheetInit(formulaSheet)">
<wj-sheet name="Expence Report" [rowCount]="14" [columnCount]="6"></wj-sheet>
</wj-flex-sheet>
<div><b>Cell Content: </b>{{currentCellData}}</div>
Result (live):
Cell Content: {{currentCellData}}
Custom Function
Although the functions provided in FlexSheet should cover a vast majority of use scenarios, still there may be some cases where users may need additional functions.
FlexSheet provides two methods that allow you to add your own custom functions: addFunction and unknownFunction .
The addFunction method adds a custom function to the list of built-in functions.
The addFunction method is usually the best way to add custom functions to the FlexSheet calculation engine. However, there are scenarios where the function names are variable or unknown ahead of time. For example, named ranges or value dictionaries.
In these situations, you can use the unknownFunction event to look up the value of a function dynamically. When the FlexSheet detects an unknown function name, it raises the unknownFunction event and provides parameters that contain the function name and parameters. The event handler then calculates the result and returns the value.
HTML
<wj-flex-sheet #customFuncSheet (initialized)="customFuncSheetInit(customFuncSheet)">
<wj-sheet name="Custom Function" [rowCount]="20" [columnCount]="6"></wj-sheet>
<wj-sheet name="Data" [rowCount]="20" [columnCount]="6"></wj-sheet>
</wj-flex-sheet>
TS
// references FlexSheet named 'customFuncSheet' in the view
@ViewChild('customFuncSheet') customFuncSheet: wjcGridSheet.FlexSheet;
customFuncSheetInit(customFuncSheet: wjcGridSheet.FlexSheet) {
if (customFuncSheet) {
customFuncSheet.addFunction('customSumProduct', (...params: any[][][]) => {
let result = 0,
range1 = params[0],
range2 = params[1];
if (range1.length > 0 && range1.length === range2.length && range1[0].length === range2[0].length) {
for (let i = 0; i < range1.length; i++) {
for (let j = 0; j < range1[0].length; j++) {
result += range1[i][j] * range2[i][j];
}
}
}
return result;
}, 'Custom SumProduct Function', 2, 2);
customFuncSheet.unknownFunction.addHandler((sender: any, e: wjcGridSheet.UnknownFunctionEventArgs) => {
let result = '';
if (e.params) {
for (let i = 0; i < e.params.length; i++) {
result += e.params[i];
}
}
e.value = result;
});
}
}
Result (live):
Table
To make managing and analyzing a group of related data easier, you can turn a range of cells into a Table.
If you want to add an empty table, you need create a Table instance then add the Table instance into the tables collection of FlexSheet.
FlexSheet also allows to add table with data source by addTableFromDataSource method.
HTML
<wj-flex-sheet #tableSheet (initialized)="tableSheetInit(tableSheet)">
<wj-sheet name="Table" [rowCount]="20" [columnCount]="7"></wj-sheet>
</wj-flex-sheet>
<div class="well well-lg" *ngIf="selectedTable != null">
<h4>Table Style Options</h4>
<div>
<label>
<input class="form-check-input" type="checkbox" [(ngModel)]="selectedTable.showHeaderRow">
Header Row
</label>
<label>
<input class="form-check-input" type="checkbox" [(ngModel)]="selectedTable.showTotalRow">
Total Row
</label>
</div>
<div>
<label>
<input class="form-check-input" type="checkbox" [(ngModel)]="selectedTable.showBandedRows">
Banded Rows
</label>
<label>
<input class="form-check-input" type="checkbox" [(ngModel)]="selectedTable.showBandedColumns">
Banded Columns
</label>
</div>
<div>
<label>
<input class="form-check-input" type="checkbox" [(ngModel)]="selectedTable.alterFirstColumn">
First Column
</label>
<label>
<input class="form-check-input" type="checkbox" [(ngModel)]="selectedTable.alterLastColumn">
Last Column
</label>
</div>
<div>
<label style="width:auto">
Built-in Styles:
</label>
<wj-combo-box #cboTableStyles [itemsSource]="tableStyleNames" [isEditable]="false" (initialized)="cboTableStylesInit(cboTableStyles)">
</wj-combo-box>
</div>
</div>
TS
// references FlexSheet named 'tableSheet' in the view
@ViewChild('tableSheet') tableSheet: wjcGridSheet.FlexSheet;
tableSheetInit(tableSheet: wjcGridSheet.FlexSheet) {
var tableStyle: wjcGridSheet.TableStyle,
table: wjcGridSheet.Table,
i: number,
self = this;
tableStyle = tableSheet.getBuiltInTableStyle('TableStyleDark9');
table = tableSheet.selectedSheet.addTableFromArray(2, 1, self.dataSvc.getTableData(10), null, 'Table1', tableStyle, { showTotalRow: true });
tableSheet.selectionChanged.addHandler((sender: any, args: wjcGrid.CellRangeEventArgs) => {
var selection = args.range;
if (selection.isValid) {
self.selectedTable = tableSheet.selectedSheet.findTable(selection.row, selection.col);
} else {
self.selectedTable = null;
}
if (self.selectedTable && self.cboTableStyles) {
self.cboTableStyles.selectedValue = self.selectedTable.style.name;
}
});
}
Excel I/O
FlexSheet supports saving/loading xlsx file by using the client 'save' and 'load' method.
HTML
<wj-flex-sheet #excelIOSheet (initialized)="excelIOSheetInit(excelIOSheet)">
<wj-sheet name="Country"></wj-sheet>
<wj-sheet name="Unbound" [rowCount]="20" [columnCount]="8"></wj-sheet>
</wj-flex-sheet>
<div class="form-inline well well-lg">
<input type="file" class="form-control" id="importFile" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" />
<button class="btn btn-default" (click)="load()">Load</button>
</div>
<div class="form-inline well well-lg">
File Name:
<input type="text" class="form-control" [(ngModel)]="fileName" />
<button class="btn btn-default" (click)="save()">Save</button>
</div>
TS
// references FlexSheet named 'excelIOSheet' in the view
@ViewChild('excelIOSheet') excelIOSheet: wjcGridSheet.FlexSheet;
// Load xlsx file to FlexSheet.
load () {
var flexSheet = this.excelIOSheet,
fileInput = <HTMLInputElement>document.getElementById('importFile');
if (flexSheet && fileInput.files[0]) {
flexSheet.load(fileInput.files[0]);
}
}
// Save FlexSheet to xlsx file.
save () {
var flexSheet = this.excelIOSheet,
fileName;
if (flexSheet) {
if (!!this.fileName) {
fileName = this.fileName;
} else {
fileName = 'FlexSheet.xlsx';
}
flexSheet.save(fileName);
}
}