// declare app module
var app = angular.module('app', ['wj']);
// app controller provides data
app.controller('appCtrl', function ($scope, dataService) {
$scope.ctx = {
data: dataService.getData(50),
flexSheet: null
}
});
// initialize the flexSheet control when document ready.
$scope.initialized = function (s) {
s.deferUpdate(function () {
var column;
for (var i = 0; i < s.sheets.length; i++) {
s.sheets.selectedIndex = i;
if (s.sheets[i].name === 'Country') {
initDataMapForBindingSheet(s);
}
}
s.selectedSheetIndex = 0;
});
};
// initialize the dataMap for the bound sheet.
function initDataMapForBindingSheet(flexSheet) {
var column;
if (flexSheet) {
column = flexSheet.columns.getColumn('countryId');
if (column && !column.dataMap) {
column.dataMap = buildDataMap(dataService.getCountries());
}
column = flexSheet.columns.getColumn('productId');
if (column && !column.dataMap) {
column.dataMap = buildDataMap(dataService.getProducts());
}
}
};
// build a data map from a string array using the indices as keys
function buildDataMap(items) {
var map = [];
for (var i = 0; i < items.length; i++) {
map.push({ key: i, value: items[i] });
}
return new wijmo.grid.DataMap(map, 'key', 'value');
};
})
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.
$scope.$watch('ctx.sortSheet', function () {
var flexSheet = $scope.ctx.sortSheet;
if (flexSheet) {
$scope.ctx.columns = getColumns();
if (!$scope.ctx.sortManager) {
$scope.ctx.sortManager = flexSheet.sortManager;
}
flexSheet.selectedSheetChanged.addHandler(function (sender, args) {
$scope.ctx.columns = getColumns();
safeApply('ctx.sortManager');
});
}
});
// commit the sorts
$scope.commitSort = function () {
$scope.ctx.sortManager.commitSort();
};
// cancel the sorts
$scope.cancelSort = function () {
$scope.ctx.sortManager.cancelSort();
};
// add new sort level
$scope.addSortLevel = function () {
$scope.ctx.sortManager.addSortLevel();
};
// delete current sort level
$scope.deleteSortLevel = function () {
$scope.ctx.sortManager.deleteSortLevel();
};
// copy a new sort level by current sort level setting.
$scope.copySortLevel = function () {
$scope.ctx.sortManager.copySortLevel();
};
// move the sort level
$scope.moveSortLevel = function (offset) {
$scope.ctx.sortManager.moveSortLevel(offset);
};
// get the columns with the column header text for the column selection for sort setting.
function getColumns() {
var columns = [],
flex = $scope.ctx.sortSheet,
i = 0;
if (flex) {
for (; i < flex.columns.length; i++) {
columns.push('Column ' + wijmo.grid.sheet.FlexSheet.convertNumberToAlpha(i));
}
}
return columns;
}
// Safe invoking the $apply function.
function safeApply(property) {
if (!$scope.$root.$$phase) {
$scope.$apply(property);
}
};
Result (live):
Column
Order
Format Cells
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.
// initialize the colorPicker control.
$scope.$watch('ctx.colorPicker', function () {
var colorPicker = $scope.ctx.colorPicker,
ua = window.navigator.userAgent,
blurEvt;
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, function () {
setTimeout(function () {
if (!colorPicker.containsFocus()) {
applyFillColor = false;
colorPicker.hostElement.style.display = 'none';
}
}, 0);
});
// Initialize the value changed event handler for the color picker control.
colorPicker.valueChanged.addHandler(function () {
if (applyFillColor) {
$scope.ctx.formatSheet.applyCellsStyle({ backgroundColor: colorPicker.value });
} else {
$scope.ctx.formatSheet.applyCellsStyle({ color: colorPicker.value });
}
});
}
});
$scope.$watch('ctx.format', function () {
var flexSheet = $scope.ctx.formatSheet;
if (flexSheet && !updatingSelection) {
flexSheet.applyCellsStyle({ format: $scope.ctx.format });
}
});
// initialize the cboFontName control.
$scope.$watch('ctx.cboFontName', function () {
var cboFontName = $scope.ctx.cboFontName;
if (cboFontName) {
cboFontName.selectedIndexChanged.addHandler(function () {
// apply the font family for the selected cells
if (!updatingSelection) {
$scope.ctx.formatSheet.applyCellsStyle({ fontFamily: $scope.ctx.cboFontName.selectedItem.value });
}
});
}
});
// initialize the cboFontSize control.
$scope.$watch('ctx.cboFontSize', function () {
var cboFontSize = $scope.ctx.cboFontSize;
if (cboFontSize) {
cboFontSize.selectedIndexChanged.addHandler(function () {
// apply the font size for the selected cells
if (!updatingSelection) {
$scope.ctx.formatSheet.applyCellsStyle({ fontSize: $scope.ctx.cboFontSize.selectedItem.value });
}
});
}
})
// apply the text alignment for the selected cells
$scope.applyCellTextAlign = function (textAlign) {
$scope.ctx.formatSheet.applyCellsStyle({ textAlign: textAlign });
$scope.ctx.selectionFormatState.textAlign = textAlign;
};
// apply the bold font weight for the selected cells
$scope.applyBoldStyle = function () {
$scope.ctx.formatSheet.applyCellsStyle({ fontWeight: $scope.ctx.selectionFormatState.isBold ? 'none' : 'bold' });
$scope.ctx.selectionFormatState.isBold = !$scope.ctx.selectionFormatState.isBold;
};
// apply the underline text decoration for the selected cells
$scope.applyUnderlineStyle = function () {
$scope.ctx.formatSheet.applyCellsStyle({ textDecoration: $scope.ctx.selectionFormatState.isUnderline ? 'none' : 'underline' });
$scope.ctx.selectionFormatState.isUnderline = !$scope.ctx.selectionFormatState.isUnderline;
};
// apply the italic font style for the selected cells
$scope.applyItalicStyle = function () {
$scope.ctx.formatSheet.applyCellsStyle({ fontStyle: $scope.ctx.selectionFormatState.isItalic ? 'none' : 'italic' });
$scope.ctx.selectionFormatState.isItalic = !$scope.ctx.selectionFormatState.isItalic;
};
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.
$scope.mergeCells = function () {
var flexSheet = $scope.ctx.mergeCellSheet;
if (flexSheet) {
flexSheet.mergeRange();
$scope.ctx.mergeState = flexSheet.getSelectionFormatState();
safeApply('ctx.mergeState');
}
}
Result (live):
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.
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.