FlexSheet 101

入门

在AngularJS应用中开始使用FlexSheet的步骤:

  1. 添加对AngularJS,Wijmo和Wijmo的AngularJS指令的引用。
  2. 在应用模板中引用Wijmo5指令:
    var app = angular.module('app', ['wj']);
  3. 添加一个控制器来提供数据和逻辑。
  4. 向页面添加一个FlexSheet控件。
  5. 向FlexSheet添加Sheet。
  6. 添加一些CSS来自定义FlexSheet的外观
<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):

排序

FlexSheet可以对它的任何一列排序。

SortManager可以帮助FlexSheet处理排序工作。下面的 样例使用SortManager来指定排序的顺序,添加或移除列,改变排序列的顺序。

<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

格式化单元格

FlexSheet允许你设置每个单元格的格式, 这包括设置单元格值的(Date/Number格式)字体样式,数据格式,单元格的填充色和水平对齐。

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

单元格合并

FlexSheet支持通过调用mergeRange方法来合并选中单元格为一个单元格。

如果选中的单元格包含分组单元格,mergeRange方法将会对合并单元格去除合并。 否则它会合并选中的单元格为一个单元格。

FlexSheet允许合并包含任何数据的单元格。这与FlexGrid是不同的。它是内容驱动的单元格合并。

<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):

拖拽操作

FlexSheet支持拖放行或列到其它的行或列。

FlexSheet不仅可以复制或移动单元格数据,也可以复制或移动单元格的样式。

当拖放时没有按键按下时,它会移动选中的行或列到放置的行或列。

当拖放时按下'Ctrl'键,它会复制选中行或列到放置的行或列。

当拖放时按下'Shift'键,它会交换选中行与放置行的位置。

<wj-flex-sheet initialized="initializeDragDropSheet(s)"> <wj-sheet name="Unbound" row-count="12" column-count="8"></wj-sheet> </wj-flex-sheet>

Result (live):

冻结单元格

FlexSheet允许通过freezeAtCursor方法在选中的单元格来冻结行和列。

<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):

撤销/重做

FlexSheet控件允许你撤销/重做下列的操作:

  1. 编辑单元格
  2. 调整行/列的大小
  3. 添加/移除行/列
  4. 更改单元格样式
  5. 合并单元格
  6. 排序
  7. 拖放行/列
<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):

公式

FlexSheet控件有内置的计算引擎,就像微软Excel,支持超过80个函数 ( 查看完整列表

<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}}

自定义函数

虽然FlexSheet提供的函数已经覆盖绝大多数使用情况,但还可能会有一些情况,用户需要额外的函数。

FlexSheet提供了两个方法允许你添加你自己的自定义函数:addCustomFunctionunknownFunction

addCustomFunction方法添加一个自定义函数到内置函数列表:

addCustomFunction方法通常是对FlexSheet计算引擎添加自定义函数的最好方法。然而,有一些场景,函数名称是变量或者提前不知道,例如,命名为ranges或者value dictionaries。

在这些情况下,你可以使用unknownFunction事件来动态查找一个函数的值。当FlexSheet检测到未知函数的名字, 它会触发unknownFunction事件,并提供包含函数名和参数的参数。然后事件处理器会计算结果并返回值。

<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支持使用客户端'save''load'方法保存\加载xlsx文件。

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