Component One ASP.Net MVC

ASP.NET MVC FlexSheet 101

This page shows how to get started with ASP.Net MVC's FlexSheet control.

Getting Started

Steps for getting started with the FlexSheet control in MVC applications:

  1. Create a new MVC project using the C1 ASP.NET MVC application template.
  2. Add controller and corresponding view to the project.
  3. Initialize the FlexSheet control in view using razor syntax.
  4. (Optional) Add some CSS to customize the FlexSheet control's appearance.

This will create a FlexSheet with default behavior.

<!DOCTYPE html> <html> <head> </head> <body> <!-- this is the FlexSheet --> @(Html.C1().FlexSheet().Id("gsFlexSheet").SelectedSheetIndex(0) .Height(300) .AddBoundSheet(sheet=> sheet.Name("Country").Bind(cv=> cv.Bind(Model.CountryData).DisableServerRead(true))) .AddUnboundSheet("EmptySheet",25,10) ) </body> </html>
.flexSheet { height: 500px; border: 2px solid #e0e0e0; margin: 6px; } .flexSheet .wj-header-row { background-color: #ABD0ED !important; color: #000000 !important; }
using System.Web.Mvc; using FlexSheet101.Models; namespace FlexSheet101.Controllers { public class HomeController : Controller { public ActionResult Index() { FlexSheetModel model = new FlexSheetModel(); model.CountryData = Sale.GetData(500); return View(model); } } }

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.C1().FlexSheet().CssClass("flexSheet").Id("sortingFlexSheet") .Height(300) .AddBoundSheet(sheet => sheet.Name("Country").Bind(cv => cv.Bind(Model.CountryData).DisableServerRead(true))) ) <div id="sortTable"> <table class="table table-bordered"> <thead> <tr> <th class="text-center">Column</th> <th class="text-center">Order</th> </tr> </thead> <tbody></tbody> </table> </div> <div class="btn-group"> <button type="button" class="btn btn-default" onclick="addSortLevel()"> Add Level </button> <button type="button" class="btn btn-default" onclick="deleteSortLevel()"> Delete Level </button> <button type="button" class="btn btn-default" onclick="copySortLevel()"> Copy Level </button> </div> <div class="btn-group"> <button id="moveup" type="button" class="btn btn-default" onclick="moveSortLevel(-1)"> <span class="glyphicon glyphicon-arrow-up"></span> </button> <button id="movedown" type="button" class="btn btn-default" onclick="moveSortLevel(1)"> <span class="glyphicon glyphicon-arrow-down"></span> </button> </div> <div class="btn-group"> <button type="button" class="btn btn-default" onclick="commitSort()">OK</button> <button type="button" class="btn btn-default" onclick="cancelSort()">Cancel</button> </div>
// Sorting var ctxSorting = { flexSheet: null, sortManager: null, moveup: null, movedown: null, tbody: null, columns: null }; function loadSorting() { ctxSorting.flexSheet = wijmo.Control.getControl('#sortingFlexSheet'); ctxSorting.sortManager = ctxSorting.flexSheet.sortManager; ctxSorting.moveup = wijmo.getElement('#moveup'); ctxSorting.movedown = wijmo.getElement('#movedown'); ctxSorting.tbody = wijmo.getElement('#sortTable tbody'); ctxSorting.columns = getColumns(); ctxSorting.flexSheet.selectedSheetChanged.addHandler(function (sender, args) { ctxSorting.columns = getColumns(); ctxSorting.sortManager = ctxSorting.flexSheet.sortManager; updateSortTable(); }); updateSortTable(); applyDataMap(ctxSorting.flexSheet); }; function changeBtnState() { moveup.disabled = ctxSorting.sortManager.sortDescriptions.currentPosition <= 0; movedown.disabled = ctxSorting.sortManager.sortDescriptions.currentPosition >= ctxSorting.sortManager.sortDescriptions.itemCount - 1; } function updateSortTable() { var i, j, html = '', tr, sortDescriptions = ctxSorting.sortManager.sortDescriptions, items = sortDescriptions.items; for (i = 0; i < items.length; i++) { tr = '<tr onclick="moveCurrentTo(' + i + ')" ' + (sortDescriptions.currentItem == items[i] ? 'class="success"' : '') + '>' + '<td>' + '<select class="form-control" onchange="columnIndexChanged(this, ' + i + ')">' + '<option value=-1></option>'; for (j = 0; j < ctxSorting.columns.length; j++) { tr += '<option value="' + j + '" ' + (j == items[i].columnIndex ? 'selected="selected"' : '') + '>' + ctxSorting.columns[j] + '</option>'; } tr += '</select></td>' + '<td>' + '<select class="form-control" onchange="ascendingChanged(this, ' + i + ')">' + '<option value="0" ' + (items[i].ascending ? 'selected="selected"' : '') + '>Ascending</option>' + '<option value="1" ' + (!items[i].ascending ? 'selected="selected"' : '') + '>Descending</option>' + '</select></td></tr>'; html += tr; } ctxSorting.tbody.innerHTML = html; changeBtnState(); } function moveCurrentTo(index) { var items = ctxSorting.sortManager.sortDescriptions.items, i = 0; ctxSorting.sortManager.sortDescriptions.moveCurrentTo(items[index]); for (; i < ctxSorting.tbody.children.length; i++) { ctxSorting.tbody.children[i].className = index == i ? 'success' : ''; } changeBtnState(); } function columnIndexChanged(ele, index) { if (ctxSorting.sortManager.sortDescriptions.items[index] != null) ctxSorting.sortManager.sortDescriptions.items[index].columnIndex = +ele.value; } function ascendingChanged(ele, index) { ctxSorting.sortManager.sortDescriptions.items[index].ascending = ele.value == "0"; } // commit the sorts function commitSort() { ctxSorting.sortManager.commitSort(); }; // cancel the sorts function cancelSort() { ctxSorting.sortManager.cancelSort(); }; // add new sort level function addSortLevel() { ctxSorting.sortManager.addSortLevel(); updateSortTable(); }; // delete current sort level function deleteSortLevel() { ctxSorting.sortManager.deleteSortLevel(); updateSortTable(); }; // copy a new sort level by current sort level setting. function copySortLevel() { ctxSorting.sortManager.copySortLevel(); updateSortTable(); }; // move the sort level function moveSortLevel(offset) { ctxSorting.sortManager.moveSortLevel(offset); updateSortTable(); }; // get the columns with the column header text for the column selection for sort setting. function getColumns() { var columns = [], flex = ctxSorting.flexSheet, i = 0; if (flex) { for (; i < flex.columns.length; i++) { columns.push('Column ' + wijmo.grid.sheet.FlexSheet.convertNumberToAlpha(i)); } } return columns; } function applyDataMap(flexSheet) { var countries = ['US', 'Germany', 'UK', 'Japan', 'Italy', 'Greece'], products = ['Widget', 'Gadget', 'Doohickey'], column; // initialize the dataMap for the bound sheet. if (flexSheet) { column = flexSheet.columns.getColumn('Country'); if (column && !column.dataMap) { column.dataMap = buildDataMap(countries); } column = flexSheet.columns.getColumn('Product'); if (column && !column.dataMap) { column.dataMap = buildDataMap(products); } } } 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'); };
using System.Web.Mvc; using FlexSheet101.Models; namespace FlexSheet101.Controllers { public class HomeController : Controller { public ActionResult Index() { FlexSheetModel model = new FlexSheetModel(); model.CountryData = Sale.GetData(500); return View(model); } } }

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.

@(Html.C1().FlexSheet().Id("fcFlexSheet").Height(300) .AddUnboundSheet("Number", 20, 8) .AddUnboundSheet("Date", 20, 8) ) @Html.C1().ColorPicker().Id("fcColorPicker").CssStyle("display", "none") .CssStyle("position", "fixed").CssStyle("z-index", "100") <div class="well well-lg"> <div> Format: @(Html.C1().Menu().Id("fcMenuFormat").Header("Format") .OnClientSelectedIndexChanged("fcMenuFormat_Changed") .MenuItems(mitem => { mitem.Add("Decimal Format", "0"); mitem.Add("Number Format", "n2"); mitem.Add("Percentage Format", "p2"); mitem.Add("Currency Format", "c2"); mitem.AddSeparator(); mitem.Add("Short Date", "d"); mitem.Add("Long Date", "D"); mitem.Add("Full Date/TIme (short time)", "f"); mitem.Add("Full Date/TIme (long time)", "F"); }) ) </div> <div> @{ IEnumerable<FontName> fontList = ViewBag.FontList; IEnumerable<FontSize> fontSizeList = ViewBag.FontSizeList; } Font: @(Html.C1().ComboBox<FontName>().Bind(fontList).SelectedIndex(0) .DisplayMemberPath("Name").SelectedValuePath("Value") .IsEditable(false).CssStyle("width", "120px") .OnClientSelectedIndexChanged("fontChanged") .Id("cboFontName") ) @(Html.C1().ComboBox<FontSize>().Bind(fontSizeList).SelectedIndex(5) .DisplayMemberPath("Name").SelectedValuePath("Value") .IsEditable(false).CssStyle("width", "80px") .OnClientSelectedIndexChanged("fontSizeChanged") .Id("cboFontSize") ) <div class="btn-group"> <button type="button" id="boldBtn" class="btn btn-default" onclick="applyBoldStyle()">Bold</button> <button type="button" id="italicBtn" class="btn btn-default" onclick="applyItalicStyle()">Italic</button> <button type="button" id="underlineBtn" class="btn btn-default" onclick="applyUnderlineStyle()">Underline</button> </div> </div> <div> Color: <div class="btn-group"> <button type="button" class="btn btn-default" onclick="showColorPicker(event, false)">Fore Color</button> <button type="button" class="btn btn-default" onclick="showColorPicker(event, true)">Fill Color</button> </div> Alignment: <div class="btn-group"> <button type="button" id="leftBtn" class="btn btn-default active" onclick="applyCellTextAlign('left')">Left</button> <button type="button" id="centerBtn" class="btn btn-default" onclick="applyCellTextAlign('center')">Center</button> <button type="button" id="rightBtn" class="btn btn-default" onclick="applyCellTextAlign('right')">Right</button> </div> </div> </div>
// Format Cells var applyFillColor = false, updatingSelection = false, formats = ['0', 'n2', 'p2', 'c2', '', 'd', 'D', 'f', 'F'], ctxFormatCells = { format: '', menuFormat: '', flexSheet: null, cboFontName: null, cboFontSize: null, boldBtn: null, italicBtn: null, underlineBtn: null, leftBtn: null, centerBtn: null, rightBtn: null, sheetName: '', selectionFormatState: {}, }; function loadFormatCells() { initFlexSheet(); initInputs(); }; function initInputs() { ctxFormatCells.cboFontName = wijmo.Control.getControl('#cboFontName'); ctxFormatCells.cboFontSize = wijmo.Control.getControl('#cboFontSize'); ctxFormatCells.menuFormat = wijmo.Control.getControl('#fcMenuFormat'); initBtns(); initColorPicker(); setMenuHeader(ctxFormatCells.menuFormat); } function initBtns() { ctxFormatCells.boldBtn = wijmo.getElement('#boldBtn'); ctxFormatCells.italicBtn = wijmo.getElement('#italicBtn'); ctxFormatCells.underlineBtn = wijmo.getElement('#underlineBtn'); ctxFormatCells.leftBtn = wijmo.getElement('#leftBtn'); ctxFormatCells.centerBtn = wijmo.getElement('#centerBtn'); ctxFormatCells.rightBtn = wijmo.getElement('#rightBtn'); } function formatCellsUpdateBtns() { updateActiveState(ctxFormatCells.selectionFormatState.isBold, ctxFormatCells.boldBtn); updateActiveState(ctxFormatCells.selectionFormatState.isItalic, ctxFormatCells.italicBtn); updateActiveState(ctxFormatCells.selectionFormatState.isUnderline, ctxFormatCells.underlineBtn); updateActiveState(ctxFormatCells.selectionFormatState.textAlign === 'left', ctxFormatCells.leftBtn); updateActiveState(ctxFormatCells.selectionFormatState.textAlign === 'center', ctxFormatCells.centerBtn); updateActiveState(ctxFormatCells.selectionFormatState.textAlign === 'right', ctxFormatCells.rightBtn); } function updateActiveState(condition, btn) { condition ? addClass(btn, "active") : removeClass(btn, "active"); } function initFlexSheet() { var sheetIdx, sheetName, colIdx, rowIdx, date, flexSheet; ctxFormatCells.flexSheet = wijmo.Control.getControl('#fcFlexSheet'); flexSheet = ctxFormatCells.flexSheet; if (flexSheet) { flexSheet.selectionChanged.addHandler(function (sender, args) { updateSelection(args.range); ctxFormatCells.selectionFormatState = flexSheet.getSelectionFormatState(); }); for (sheetIdx = 0; sheetIdx < flexSheet.sheets.length; sheetIdx++) { flexSheet.selectedSheetIndex = sheetIdx; sheetName = flexSheet.selectedSheet.name; for (colIdx = 0; colIdx < flexSheet.columns.length; colIdx++) { for (rowIdx = 0; rowIdx < flexSheet.rows.length; rowIdx++) { if (sheetName === 'Number') { flexSheet.setCellData(rowIdx, colIdx, colIdx + rowIdx); } else { date = new Date(2015, colIdx, rowIdx + 1); flexSheet.setCellData(rowIdx, colIdx, date); } } } } flexSheet.selectedSheetIndex = 0; updateSelection(flexSheet.selection); } }; // initialize the colorPicker control. function initColorPicker() { var colorPicker = ctxFormatCells.colorPicker = wijmo.Control.getControl('#fcColorPicker'), 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) { ctxFormatCells.flexSheet.applyCellsStyle({ backgroundColor: colorPicker.value }); } else { ctxFormatCells.flexSheet.applyCellsStyle({ color: colorPicker.value }); } }); } } function fcMenuFormat_Changed(sender) { var flexSheet = ctxFormatCells.flexSheet, menu = sender; if (menu.selectedValue) { ctxFormatCells.format = menu.selectedValue.CommandParameter; setMenuHeader(menu); if (flexSheet && !updatingSelection) { flexSheet.applyCellsStyle({ format: ctxFormatCells.format }); } } } function setMenuHeader(menu) { menu.header = "Format:" + menu.selectedValue === null ? "" : menu.selectedValue.Header + ""; } function fontChanged(sender) { if (!updatingSelection && ctxFormatCells.flexSheet) { ctxFormatCells.flexSheet.applyCellsStyle({ fontFamily: ctxFormatCells.cboFontName.selectedItem.Value }); } } function fontSizeChanged(sender) { if (!updatingSelection && ctxFormatCells.flexSheet) { ctxFormatCells.flexSheet.applyCellsStyle({ fontSize: ctxFormatCells.cboFontSize.selectedItem.Value }); } } // apply the text alignment for the selected cells function applyCellTextAlign(textAlign) { ctxFormatCells.flexSheet.applyCellsStyle({ textAlign: textAlign }); ctxFormatCells.selectionFormatState.textAlign = textAlign; formatCellsUpdateBtns(); }; // apply the bold font weight for the selected cells function applyBoldStyle() { ctxFormatCells.flexSheet.applyCellsStyle({ fontWeight: ctxFormatCells.selectionFormatState.isBold ? 'none' : 'bold' }); ctxFormatCells.selectionFormatState.isBold = !ctxFormatCells.selectionFormatState.isBold; formatCellsUpdateBtns(); }; // apply the underline text decoration for the selected cells function applyUnderlineStyle() { ctxFormatCells.flexSheet.applyCellsStyle({ textDecoration: ctxFormatCells.selectionFormatState.isUnderline ? 'none' : 'underline' }); ctxFormatCells.selectionFormatState.isUnderline = !ctxFormatCells.selectionFormatState.isUnderline; formatCellsUpdateBtns(); }; // apply the italic font style for the selected cells function applyItalicStyle() { ctxFormatCells.flexSheet.applyCellsStyle({ fontStyle: ctxFormatCells.selectionFormatState.isItalic ? 'none' : 'italic' }); ctxFormatCells.selectionFormatState.isItalic = !ctxFormatCells.selectionFormatState.isItalic; formatCellsUpdateBtns(); }; // show the color picker control. function showColorPicker(e, isFillColor) { var colorPicker = ctxFormatCells.colorPicker, offset = cumulativeOffset(e.target), winWidth = document.body.clientWidth; if (colorPicker) { colorPicker.hostElement.style.display = 'inline'; if (offset.left + colorPicker.hostElement.clientWidth > winWidth) { colorPicker.hostElement.style.left = 'auto'; colorPicker.hostElement.style.right = '0px'; } else { colorPicker.hostElement.style.right = 'auto'; colorPicker.hostElement.style.left = offset.left + 'px'; } colorPicker.hostElement.style.top = (offset.top - colorPicker.hostElement.clientHeight - 5) + 'px'; colorPicker.hostElement.focus(); } applyFillColor = isFillColor; }; // Update the selection object of the scope. function updateSelection(sel) { var flexSheet = ctxFormatCells.flexSheet, row = flexSheet.rows[sel.row], rowCnt = flexSheet.rows.length, colCnt = flexSheet.columns.length, r, c, cellStyle, cellContent, cellFormat; updatingSelection = true; if (ctxFormatCells.cboFontName && sel.row > -1 && sel.col > -1 && rowCnt > 0 && colCnt > 0 && sel.col < colCnt && sel.col2 < colCnt && sel.row < rowCnt && sel.row2 < rowCnt) { r = sel.row >= rowCnt ? rowCnt - 1 : sel.row; c = sel.col >= colCnt ? colCnt - 1 : sel.col; cellContent = flexSheet.getCellData(sel.row, sel.col); cellStyle = flexSheet.selectedSheet.getCellStyle(sel.row, sel.col); if (cellStyle) { ctxFormatCells.cboFontName.selectedIndex = checkFontfamily(cellStyle.fontFamily); ctxFormatCells.cboFontSize.selectedIndex = checkFontSize(cellStyle.fontSize); cellFormat = cellStyle.format; } else { ctxFormatCells.cboFontName.selectedIndex = 0; ctxFormatCells.cboFontSize.selectedIndex = 5; } if (!!cellFormat) { ctxFormatCells.format = cellFormat; } else { if (wijmo.isInt(cellContent)) { ctxFormatCells.format = '0'; } else if (wijmo.isNumber(cellContent)) { ctxFormatCells.format = 'n2'; } else if (wijmo.isDate(cellContent)) { ctxFormatCells.format = 'd'; } } ctxFormatCells.selectionFormatState = flexSheet.getSelectionFormatState() ctxFormatCells.menuFormat.selectedIndex = formats.indexOf(ctxFormatCells.format); formatCellsUpdateBtns(); } updatingSelection = false; }; // check font family for the font name combobox of the ribbon. function checkFontfamily(fontFamily) { var fonts = ctxFormatCells.cboFontName.itemsSource.items, fontIndex = 0, font; if (!fontFamily) { return fontIndex; } for (; fontIndex < fonts.length; fontIndex++) { font = fonts[fontIndex]; if (font.Name === fontFamily || font.Value === fontFamily) { return fontIndex; } } return 0; } // check font size for the font size combobox of the ribbon. function checkFontSize(fontSize) { var sizeList = ctxFormatCells.cboFontSize.itemsSource.items, index = 0, size; if (fontSize == undefined) { return 5; } for (; index < sizeList.length; index++) { size = sizeList[index]; if (size.Value === fontSize || size.Name === fontSize) { return index; } } return 5; } // Get the absolute position of the dom element. function cumulativeOffset(element) { var top = 0, left = 0, scrollTop = 0, scrollLeft = 0; do { top += element.offsetTop || 0; left += element.offsetLeft || 0; scrollTop += element.scrollTop || 0; scrollLeft += element.scrollLeft || 0; element = element.offsetParent; } while (element); return { top: top - scrollTop, left: left - scrollLeft }; }; function hasClass(obj, cls) { return obj && obj.className.match(new RegExp('(\\s|^)' + cls + '(\\s|$)')); } function addClass(obj, cls) { if (!this.hasClass(obj, cls)) obj.className += " " + cls; } function removeClass(obj, cls) { if (hasClass(obj, cls)) { var reg = new RegExp('(\\s|^)' + cls + '(\\s|$)'); obj.className = obj.className.replace(reg, ' '); } }
using System.Web.Mvc; namespace FlexSheet101.Controllers { public class HomeController : Controller { public ActionResult Index() { return View(); } } }

Result (live):

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

@(Html.C1().FlexSheet().CssClass("flexSheet").Id("cellMergeSheet") .Height(300).AddUnboundSheet("Sheet1", 20, 8)) <button type="button" class="btn btn-default" onclick="mergeCells()" id="cellMergeBtn">Merge</button>
// Cell Merging var cellMergeSheet = { flexSheet: null, selectionFormatState: {}, mergeBtn: null }; function loadcellMerging() { var flexSheet; cellMergeSheet.flexSheet = wijmo.Control.getControl('#cellMergeSheet'); cellMergeSheet.mergeBtn = document.getElementById('cellMergeBtn');//wijmo.getElement('#cellMergeBtn'); flexSheet = cellMergeSheet.flexSheet; if (flexSheet) { for (colIdx = 0; colIdx < flexSheet.columns.length; colIdx++) { for (rowIdx = 0; rowIdx < flexSheet.rows.length; rowIdx++) { flexSheet.setCellData(rowIdx, colIdx, colIdx + rowIdx); } } flexSheet.selectionChanged.addHandler(function () { cellMergeSheet.selectionFormatState = flexSheet.getSelectionFormatState(); cellMergeUpdateBtnText(); }); } }; function cellMergeUpdateBtnText() { var updateBtnText = cellMergeSheet.selectionFormatState.isMergedCell ? 'UnMerge' : 'Merge'; cellMergeSheet.mergeBtn.innerText = cellMergeSheet.selectionFormatState.isMergedCell ? 'UnMerge' : 'Merge'; }; function mergeCells() { var flexSheet = cellMergeSheet.flexSheet; if (flexSheet) { flexSheet.mergeRange(); cellMergeSheet.selectionFormatState = flexSheet.getSelectionFormatState(); cellMergeUpdateBtnText(); } };
using System.Web.Mvc; namespace FlexSheet101.Controllers { public class HomeController : Controller { public ActionResult Index() { return View(); } } }

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.

@(Html.C1().FlexSheet().Id("dragDropSheet").Height(300) .AddUnboundSheet("Sheet1", 20, 8))
// Drag & Drop var ctxDragDrop = { flexSheet: null }; function loadDragDrop() { var flexSheet; ctxDragDrop.flexSheet = wijmo.Control.getControl('#dragDropSheet'); flexSheet = ctxDragDrop.flexSheet; if (flexSheet) { for (colIdx = 0; colIdx < flexSheet.columns.length; colIdx++) { for (rowIdx = 0; rowIdx < flexSheet.rows.length; rowIdx++) { flexSheet.setCellData(rowIdx, colIdx, colIdx + rowIdx); } } flexSheet.applyCellsStyle({ fontWeight: 'bold' }, [new wijmo.grid.CellRange(0, 0, 9, 0), new wijmo.grid.CellRange(10, 1, 19, 1)]); flexSheet.applyCellsStyle({ textDecoration: 'underline' }, [new wijmo.grid.CellRange(0, 2, 9, 2), new wijmo.grid.CellRange(10, 3, 19, 3)]); flexSheet.applyCellsStyle({ fontStyle: 'italic' }, [new wijmo.grid.CellRange(0, 4, 9, 4), new wijmo.grid.CellRange(10, 5, 19, 5)]); flexSheet.applyCellsStyle({ format: 'c2' }, [new wijmo.grid.CellRange(0, 0, 9, 7)]); flexSheet.applyCellsStyle({ backgroundColor: '#4488CC' }, [new wijmo.grid.CellRange(0, 0, 19, 0), new wijmo.grid.CellRange(0, 2, 19, 2), new wijmo.grid.CellRange(0, 4, 19, 4)]); flexSheet.applyCellsStyle({ color: '#CC8844' }, [new wijmo.grid.CellRange(0, 1, 19, 1), new wijmo.grid.CellRange(0, 3, 19, 3), new wijmo.grid.CellRange(0, 5, 19, 5)]); flexSheet.applyCellsStyle({ color: '#336699' }, [new wijmo.grid.CellRange(0, 6, 9, 7)]); flexSheet.applyCellsStyle({ backgroundColor: '#996633' }, [new wijmo.grid.CellRange(10, 6, 19, 7)]); } };
using System.Web.Mvc; namespace FlexSheet101.Controllers { public class HomeController : Controller { public ActionResult Index() { return View(); } } }

Result (live):

Frozen Cells

FlexSheet allows you to freeze the rows and columns of the selected cell by the freezeAtCursor method.

@(Html.C1().FlexSheet().Id("frozenSheet") .AddUnboundSheet("Sheet1", 20, 8).Height(300) .OnClientSelectedSheetChanged("frozenSheet_updateFrozenState")) <button type="button" class="btn btn-default" onclick="freezeCells()" id="frozenBtn">Freeze</button>
// Frozen Cells var ctxfrozenCells = { flexSheet: null, isFrozen: false, frozenBtn: null }; function loadFrozenCells() { var flexSheet; ctxfrozenCells.flexSheet = wijmo.Control.getControl('#frozenSheet'); ctxfrozenCells.mergeBtn = wijmo.getElement('#frozenBtn'); flexSheet = ctxfrozenCells.flexSheet; if (flexSheet) { for (colIdx = 0; colIdx < flexSheet.columns.length; colIdx++) { for (rowIdx = 0; rowIdx < flexSheet.rows.length; rowIdx++) { flexSheet.setCellData(rowIdx, colIdx, colIdx + rowIdx); } } } }; function frozenCellsUpdateBtn() { ctxfrozenCells.mergeBtn.innerText = ctxfrozenCells.isFrozen ? 'UnFreeze' : 'Freeze'; } function freezeCells() { var flexSheet = ctxfrozenCells.flexSheet; if (flexSheet) { flexSheet.freezeAtCursor(); frozenSheet_updateFrozenState(); } } function frozenSheet_updateFrozenState() { var flexSheet = ctxfrozenCells.flexSheet; if (flexSheet) { if (ctxfrozenCells.flexSheet.frozenColumns > 0 || ctxfrozenCells.flexSheet.frozenRows > 0) { ctxfrozenCells.isFrozen = true; } else { ctxfrozenCells.isFrozen = false; } frozenCellsUpdateBtn(); } }
using System.Web.Mvc; namespace FlexSheet101.Controllers { public class HomeController : Controller { public ActionResult Index() { return View(); } } }

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
@(Html.C1().FlexSheet().Id("uredoSheet") .AddUnboundSheet("Sheet1", 20, 8).Height(300) ) <button id="btnUndo" type="button" class="btn btn-default" onclick="undoFunc()">Undo</button> <button id="btnRedo" type="button" class="btn btn-default" onclick="redoFunc()">Redo</button>
// Undo/Redo var ctxuredoSheet = { flexSheet: null, undoStack: null }; function loadUndoRedo() { var flexSheet; ctxuredoSheet.flexSheet = wijmo.Control.getControl('#uredoSheet'); flexSheet = ctxuredoSheet.flexSheet; flexSheet.deferUpdate(function () { var colIdx, rowIdx; ctxuredoSheet.undoStack = flexSheet.undoStack; // initialize the dataMap for the bound sheet. if (flexSheet) { for (colIdx = 0; colIdx < flexSheet.columns.length; colIdx++) { for (rowIdx = 0; rowIdx < flexSheet.rows.length; rowIdx++) { flexSheet.setCellData(rowIdx, colIdx, colIdx + rowIdx); } } } }); }; // Excutes undo command. function undoFunc() { if (ctxuredoSheet.flexSheet) ctxuredoSheet.flexSheet.undo(); }; // Excutes redo command. function redoFunc() { if (ctxuredoSheet.flexSheet) ctxuredoSheet.flexSheet.redo(); };
using System.Web.Mvc; namespace FlexSheet101.Controllers { public class HomeController : Controller { public ActionResult Index() { return View(); } } }

Result (live):

Formulas

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

@(Html.C1().FlexSheet().Id("formulaSheet") .AddUnboundSheet("Expense Report", 14, 6).Height(300) ) <div><b>Cell Content: </b><span id="dvCurrentCellData"></span></div>
// Formulas var ctxFormulas = { flexSheet: null, currentCellData:null }; function loadFormulasSheet() { ctxFormulas.flexSheet = wijmo.Control.getControl('#formulaSheet'); flexSheet = ctxFormulas.flexSheet; flexSheet.selectionChanged.addHandler(function (sender, args) { var selection = args.range; if (selection.isValid) { ctxFormulas.currentCellData = ctxFormulas.flexSheet.getCellData(selection.row, selection.col, true); //alert(ctxFormulas.currentCellData); document.getElementById('dvCurrentCellData').innerText = ctxFormulas.currentCellData; } }); flexSheet.deferUpdate(function () { generateExpenceReport(flexSheet); }); }; // Set content for the use case template sheet. function generateExpenceReport(flexSheet) { flexSheet.setCellData(1, 1, 'Expense Report'); flexSheet.setCellData(3, 1, 'Date'); flexSheet.setCellData(3, 2, 'Fuel'); flexSheet.setCellData(3, 3, 'Parking(per hour)'); flexSheet.setCellData(3, 4, 'Parking(hours)'); flexSheet.setCellData(3, 5, 'Total');; flexSheet.setCellData(9, 1, 'Total'); flexSheet.setCellData(10, 4, 'Subtotal'); flexSheet.setCellData(11, 4, 'Cash Advances'); flexSheet.setCellData(12, 4, 'Total'); setExpenseData(flexSheet); applyStyleForExpenceReport(flexSheet); } // set expense detail data for the use case template sheet. function setExpenseData(flexSheet) { var rowIndex, colIndex, value; for (rowIndex = 4; rowIndex <= 8; rowIndex++) { for (colIndex = 2; colIndex <= 5; colIndex++) { if (colIndex === 5) { flexSheet.setCellData(rowIndex, colIndex, '=C' + (rowIndex + 1) + ' + Product(C' + (rowIndex + 1) + ':D' + (rowIndex + 1) + ')'); } else if (colIndex === 4) { value = parseInt(7 * Math.random()) + 1; flexSheet.setCellData(rowIndex, colIndex, value); } else if (colIndex === 3) { flexSheet.setCellData(rowIndex, colIndex, 3.75); } else { value = 200 * Math.random(); flexSheet.setCellData(rowIndex, colIndex, value); } } } flexSheet.setCellData(4, 1, '2015-3-1'); flexSheet.setCellData(5, 1, '2015-3-3'); flexSheet.setCellData(6, 1, '2015-3-7'); flexSheet.setCellData(7, 1, '2015-3-11'); flexSheet.setCellData(8, 1, '2015-3-18'); flexSheet.setCellData(9, 2, '=Sum(C5:C9)'); flexSheet.setCellData(9, 4, '=Sum(Product(D5:E5), Product(D6:E6), Product(D7:E7), Product(D8:E8), Product(D9:E9))'); flexSheet.setCellData(9, 5, '=Sum(F5:F9)'); flexSheet.setCellData(10, 5, '=F13-F12'); flexSheet.setCellData(11, 5, 800); flexSheet.setCellData(12, 5, '=F10'); } // Apply styles for the use case template sheet. function applyStyleForExpenceReport(flexSheet) { flexSheet.columns[0].width = 10; flexSheet.columns[1].width = 90; flexSheet.columns[2].width = 80; flexSheet.columns[3].width = 140; flexSheet.columns[4].width = 120; flexSheet.columns[5].width = 80; for (var i = 2; i <= 3; i++) { flexSheet.columns[i].format = 'c2'; } flexSheet.columns[5].format = 'c2'; flexSheet.rows[1].height = 45; flexSheet.applyCellsStyle({ fontSize: '24px', fontWeight: 'bold', color: '#696964' }, [new wijmo.grid.CellRange(1, 1, 1, 3)]); flexSheet.mergeRange(new wijmo.grid.CellRange(1, 1, 1, 3)); flexSheet.applyCellsStyle({ fontWeight: 'bold', backgroundColor: '#FAD9CD', }, [new wijmo.grid.CellRange(3, 1, 3, 5), new wijmo.grid.CellRange(9, 1, 9, 5)]); flexSheet.applyCellsStyle({ textAlign: 'center' }, [new wijmo.grid.CellRange(3, 1, 3, 5)]); flexSheet.applyCellsStyle({ format: 'c2' }, [new wijmo.grid.CellRange(9, 4, 9, 4)]); flexSheet.applyCellsStyle({ backgroundColor: '#F4B19B' }, [new wijmo.grid.CellRange(4, 1, 8, 5)]); flexSheet.applyCellsStyle({ fontWeight: 'bold', textAlign: 'right' }, [new wijmo.grid.CellRange(10, 4, 12, 4)]); }
using System.Web.Mvc; namespace FlexSheet101.Controllers { public class HomeController : Controller { public ActionResult Index() { return View(); } } }

Result (live):

Cell Content:

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.

@(Html.C1().FlexSheet().Id("cFunctionSheet").Height(300) .AddUnboundSheet("", 25, 12).OnClientUnknownFunction("cFunctionSheet_unknownFunction"))
// Custom Function var ctxcFunctionSheet = { flexSheet: null }; function cFunctionSheet_unknownFunction(sender, e) { var result = ''; if (e.params) { for (var i = 0; i < e.params.length; i++) { result += e.params[i]; } } e.value = result; }; function loadCustomFunction() { var flexSheet = ctxcFunctionSheet.flexSheet = wijmo.Control.getControl('#cFunctionSheet'); flexSheet.addCustomFunction('customSumProduct', function (range1, range2) { var 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', true, 2, 2); for (var ri = 0; ri < flexSheet.rows.length; ri++) { for (var ci = 0; ci < 3; ci++) { flexSheet.setCellData(ri, ci, ri + ci); } } flexSheet.setCellData(0, 3, '=customSumProduct(A1:A10, B1:B10)'); flexSheet.setCellData(1, 3, '=customFunc(1, "B", 3)'); };
using System.Web.Mvc; namespace FlexSheet101.Controllers { public class HomeController : Controller { public ActionResult Index() { return View(); } } }

Result (live):

Excel I/O

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

@(Html.C1().FlexSheet().CssClass("flexSheet").Id("excelIOSheet").SelectedSheetIndex(0).Height(300) .AddBoundSheet(sheet => sheet.Bind(Model.CountryData).Name("Country")) .AddUnboundSheet("Unbound", 20, 8) ) <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" onclick="excelIOLoad()">Load</button> </div> <div class="form-inline well well-lg"> File Name: <input type="text" class="form-control" id="fileName" onchange="fileNameChanged()" /> <button class="btn btn-default" onclick="excelIOSave()">Save</button> </div>
// Excel I/O var ctxcExcelIO = { fileName: '', flexSheet: null, fileNameInput: null, fileInput: null }; function loadExcelIO() { var flexSheet; ctxcExcelIO.flexSheet = wijmo.Control.getControl('#excelIOSheet'); ctxcExcelIO.fileNameInput = wijmo.getElement('#fileName'); ctxcExcelIO.fileInput = wijmo.getElement('#importFile'); flexSheet = ctxcExcelIO.flexSheet; if (flexSheet) { for (sheetIdx = 0; sheetIdx < flexSheet.sheets.length; sheetIdx++) { flexSheet.selectedSheetIndex = sheetIdx; sheetName = flexSheet.selectedSheet.name; if (sheetName === 'Unbound') { for (colIdx = 0; colIdx < flexSheet.columns.length; colIdx++) { for (rowIdx = 0; rowIdx < flexSheet.rows.length; rowIdx++) { flexSheet.setCellData(rowIdx, colIdx, colIdx + rowIdx); } } } else { applyDataMap(flexSheet); } } flexSheet.selectedSheetIndex = 0; } }; excelIOLoad = function () { var flexSheet = ctxcExcelIO.flexSheet, fileInput = ctxcExcelIO.fileInput; if (flexSheet && fileInput.files[0]) { flexSheet.load(fileInput.files[0]); } } excelIOSave = function () { var flexSheet = ctxcExcelIO.flexSheet, fileName; if (flexSheet) { if (!!ctxcExcelIO.fileName) { fileName = ctxcExcelIO.fileName; } else { fileName = 'FlexSheet.xlsx'; } flexSheet.save(fileName); } } function fileNameChanged() { ctxcExcelIO.fileName = ctxcExcelIO.fileNameInput.value; } function applyDataMap(flexSheet) { var countries = ['US', 'Germany', 'UK', 'Japan', 'Italy', 'Greece'], products = ['Widget', 'Gadget', 'Doohickey'], column; // initialize the dataMap for the bound sheet. if (flexSheet) { column = flexSheet.columns.getColumn('Country'); if (column && !column.dataMap) { column.dataMap = buildDataMap(countries); } column = flexSheet.columns.getColumn('Product'); if (column && !column.dataMap) { column.dataMap = buildDataMap(products); } } } 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'); }
using System.Web.Mvc; using FlexSheet101.Models; namespace FlexSheet101.Controllers { public class HomeController : Controller { public ActionResult Index() { FlexSheetModel model = new FlexSheetModel(); model.CountryData = Sale.GetData(500); return View(model); } } }

Result (live):

File Name: