FlexSheet 101

Getting Started

Steps for getting started with FlexSheet in AngularJS applications:

  1. Add references to AngularJS, Wijmo, and Wijmo's AngularJS directives.
  2. Include the Wijmo 5 directives in the app module:
    var app = angular.module('app', ['wj']);
  3. Add a controller to provide data and logic.
  4. Add a FlexSheet control to the page.
  5. Add a Sheet to the FlexSheet.
  6. Add some CSS to customize the flexsheet's appearance.
<html> <head> <link rel="stylesheet" type="text/css" href="css/bootstrap.css"/> <link rel="stylesheet" type="text/css" href="css/wijmo.css" /> <link href="css/app.css" rel="stylesheet" type="text/css" /> <script src="http://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script> <script src="scripts/angular.js" type="text/javascript"></script> <script src="scripts/wijmo.js" type="text/javascript"></script> <script wj-src="wijmo.input" src="bin/Devel/loaders/wijmo.load.module.js" type="text/javascript"></script> <script src="scripts/wijmo.grid.js" type="text/javascript"></script> <script wj-src="wijmo.grid.filter" src="bin/Devel/loaders/wijmo.load.module.js" type="text/javascript"></script> <script wj-src="wijmo.grid.sheet" src="bin/Devel/loaders/wijmo.load.module.js" type="text/javascript"></script> <script wj-src="wijmo.grid.xlsx" src="bin/Devel/loaders/wijmo.load.module.js" type="text/javascript"></script> <script wj-src="wijmo.xlsx" src="bin/Devel/loaders/wijmo.load.module.js" type="text/javascript"></script> <script src="scripts/wijmo.angular.js" type="text/javascript"></script> <script src="scripts/app.js" type="text/javascript"></script> <script src="scripts/services/dataService.js" type="text/javascript"></script> <script src="scripts/directives/appDctv.js" type="text/javascript"></script> <script src="scripts/controllers/appCtrl.js" type="text/javascript"></script> </head> <body ng-app="app" ng-controller="appCtrl"> <!-- this is the flexsheet --> <wj-flex-sheet initialized="initialized(s)"> <wj-sheet name="Country" items-source="ctx.data"></wj-sheet> <wj-sheet name="Empty Sheet"></wj-sheet> </wj-flex-sheet> </body> </html>
// 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'); }; })
/* set default grid style */ .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.

<wj-flex-sheet control="ctx.sortSheet" initialized="initialized(s)"> <wj-sheet name="Country" items-source="ctx.data"></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> <tr ng-repeat="sortItem in ctx.sortManager.sortDescriptions.items" ng-click="ctx.sortManager.sortDescriptions.moveCurrentTo(sortItem)" ng-class="{success: sortItem === ctx.sortManager.sortDescriptions.currentItem}"> <td> <select class="form-control" ng-model="sortItem.columnIndex"> <option value=-1></option> <option ng-repeat="column in ctx.columns" ng-selected="$index === sortItem.columnIndex" value={​{$index}}> {​{column}} </option> </select> </td> <td> <select class="form-control" ng-model="sortItem.ascending" ng-options="o.v as o.n for o in [{n: 'Ascending', v: true}, {n: 'Descending', v: false}]"></select> </td> </tr> </tbody> </table> <div class="btn-group"> <button type="button" class="btn btn-default" ng-click="addSortLevel()"> Add Level </button> <button type="button" class="btn btn-default" ng-click="deleteSortLevel()"> Delete Level </button> <button type="button" class="btn btn-default" ng-click="copySortLevel()"> Copy Level </button> </div> <div class="btn-group"> <button id="moveup" type="button" class="btn btn-default" ng-disabled="ctx.sortManager.sortDescriptions.currentPosition <= 0" ng-click="moveSortLevel(-1)"> <span class="glyphicon glyphicon-arrow-up"></span> </button> <button id="movedown" type="button" class="btn btn-default" ng-disabled="ctx.sortManager.sortDescriptions.currentPosition >= ctx.sortManager.sortDescriptions.itemCount - 1" ng-click="moveSortLevel(1)"> <span class="glyphicon glyphicon-arrow-down"></span> </button> </div> <div class="btn-group"> <button type="button" class="btn btn-default" ng-click="commitSort()">OK</button> <button type="button" class="btn btn-default" ng-click="cancelSort()">Cancel</button> </div>
$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.

<wj-flex-sheet control="ctx.formatSheet" initialized="initializeFormatSheet(s)"> <wj-sheet name="Number" row-count="20" column-count="8"></wj-sheet> <wj-sheet name="Date" row-count="20" column-count="8"></wj-sheet> </wj-flex-sheet> <wj-color-picker style="display:none;position:fixed;z-index:100" control="ctx.colorPicker"></wj-color-picker> <div class="well well-lg"> <div> Format: <wj-menu header="Format" visible="true" value="ctx.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="'p2'">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 style="width:120px" control="ctx.cboFontName" items-source="ctx.fonts" selected-index="0" display-member-path="name" selected-value-path="value" is-editable="false"> </wj-combo-box> <wj-combo-box style="width:80px" control="ctx.cboFontSize" items-source="ctx.fontSizeList" selected-index="5" display-member-path="name" selected-value-path="value" is-editable="false"> </wj-combo-box> <div class="btn-group"> <button type="button" class="btn btn-default {​{ctx.selectionFormatState.isBold ? 'active' : ''}}" ng-click="applyBoldStyle()">Bold</button> <button type="button" class="btn btn-default {​{ctx.selectionFormatState.isItalic ? 'active' : ''}}" ng-click="applyItalicStyle()">Italic</button> <button type="button" class="btn btn-default {​{ctx.selectionFormatState.isUnderline ? 'active' : ''}}" ng-click="applyUnderlineStyle()">Underline</button> </div> </div> <div> Color: <div class="btn-group"> <button type="button" class="btn btn-default" ng-click="showColorPicker($event, false)">Fore Color</button> <button type="button" class="btn btn-default" ng-click="showColorPicker($event, true)">Fill Color</button> </div> Alignment: <div class="btn-group"> <button type="button" class="btn btn-default {​{ctx.selectionFormatState.textAlign === 'left' ? 'active' : ''}}" ng-click="applyCellTextAlign('left')">Left</button> <button type="button" class="btn btn-default {​{ctx.selectionFormatState.textAlign === 'center' ? 'active' : ''}}" ng-click="applyCellTextAlign('center')">Center</button> <button type="button" class="btn btn-default {​{ctx.selectionFormatState.textAlign === 'right' ? 'active' : ''}}" ng-click="applyCellTextAlign('right')">Right</button> </div> </div> </div>
// 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; };

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:
Color:
Alignment:

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.

<wj-flex-sheet control="ctx.mergeCellSheet" initialized="initializeMergeCellSheet(s)"> <wj-sheet name="Unbound" row-count="20" column-count="8"></wj-sheet> </wj-flex-sheet> <button type="button" class="btn btn-default" ng-click="mergeCells()">{​{ctx.mergeState.isMergedCell ? 'UnMerge' : 'Merge'}}</button>
$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.

<wj-flex-sheet initialized="initializeDragDropSheet(s)"> <wj-sheet name="Unbound" row-count="12" column-count="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.

<wj-flex-sheet control="ctx.frozenSheet" initialized="initializeFrozenSheet(s)"> <wj-sheet name="Unbound" row-count="20" column-count="8"></wj-sheet> </wj-flex-sheet> <button type="button" class="btn btn-default" ng-click="freezeCells()">{​{ctx.isFrozen ? 'UnFreeze' : 'Freeze'}}</button>
$scope.freezeCells = function () { var flexSheet = $scope.ctx.frozenSheet; if (flexSheet) { flexSheet.freezeAtCursor(); if (flexSheet.frozenColumns > 0 || flexSheet.frozenRows > 0) { $scope.ctx.isFrozen = true; } else { $scope.ctx.isFrozen = false; } } };

Result (live):

Undo / Redo

The FlexSheet control enables you to undo/redo following operations:

  1. Editing cells
  2. Resizing row/column
  3. Adding/Removing rows/columns
  4. Changing cell style
  5. Merging cells
  6. Sorting
  7. Dragging and Dropping rows/columns
<wj-flex-sheet control="ctx.undoSheet" initialized="initializeUndoSheet(s)"> <wj-sheet name="Unbound" row-count="20" column-count="8"></wj-sheet> </wj-flex-sheet> <button type="button" class="btn btn-default" ng-disabled="!(ctx.undoStack && ctx.undoStack.canUndo)" ng-click="undo()">Undo</button> <button type="button" class="btn btn-default" ng-disabled="!(ctx.undoStack && ctx.undoStack.canRedo)" ng-click="redo()">Redo</button>
// Excutes undo command. $scope.undo = function () { $scope.ctx.undoSheet.undo(); }; // Excutes redo command. $scope.redo = function () { $scope.ctx.undoSheet.redo(); };

Result (live):

Formulas

The FlexSheet control has a built-in calculation engine that supports over 80 functions just like Microsoft Excel. (See complete list.)

<wj-flex-sheet control="ctx.formulaSheet" initialized="initializeFormulaSheet(s)"> <wj-sheet name="Expence Report" row-count="14" column-count="6"></wj-sheet> </wj-flex-sheet> <div><b>Cell Content: </b>{​{ctx.currentCellData}}</div>

Result (live):

Cell Content: {{ctx.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: addCustomFunction and unknownFunction.

The addCustomFunction method adds a custom function to the list of built-in functions.

The addCustomFunction 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.

<wj-flex-sheet control="ctx.customFuncSheet" initialized="initializeCustomFuncSheet(s)"> <wj-sheet name="Custom Function" row-count="25" column-count="6"></wj-sheet> </wj-flex-sheet>
flexSheet.addCustomFunction('customSumProduct', function (range1, range2) { var flexSheet = $scope.ctx.customFuncSheet, result = 0, val1, val2; if (range1.rowSpan === range2.rowSpan && range1.columnSpan === range2.columnSpan) { for (var rowIndex = 0; rowIndex < range1.rowSpan; rowIndex++) { for (var columnIndex = 0; columnIndex < range1.columnSpan; columnIndex++) { val1 = +flexSheet.getCellValue(range1.topRow + rowIndex, range1.leftCol + columnIndex, false); val2 = +flexSheet.getCellValue(range2.topRow + rowIndex, range2.leftCol + columnIndex, false); result += val1 * val2; } } } return result; }, 'Custom SumProduct Function', 2, 2); flexSheet.unknownFunction.addHandler(function (sender, e) { var result = ''; if (e.params) { for (var i = 0; i < e.params.length; i++) { result += e.params[i]; } } e.value = result; });

Result (live):

Excel I/O

FlexSheet supports saving/loading xlsx file by using the client 'save' and 'load' method.

<wj-flex-sheet control="ctx.excelIOSheet" initialized="initializeExcelIOSheet(s)"> <wj-sheet name="Country" items-source="ctx.data"></wj-sheet> <wj-sheet name="Unbound" row-count="20" column-count="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" ng-click="load()">Load</button> </div> <div class="form-inline well well-lg"> File Name: <input type="text" class="form-control" ng-model="ctx.fileName" /> <button class="btn btn-default" ng-click="save()">Save</button> </div>
$scope.load = function () { var flexSheet = $scope.ctx.excelIOSheet, fileInput = document.getElementById('importFile'); if (flexSheet && fileInput.files[0]) { flexSheet.load(fileInput.files[0]); } }; $scope.save = function () { var flexSheet = $scope.ctx.excelIOSheet, fileName; if (flexSheet) { if (!!$scope.ctx.fileName) { fileName = $scope.ctx.fileName; } else { fileName = 'FlexSheet.xlsx'; } flexSheet.save(fileName); } };

Result (live):

File Name: