Skip to main content Skip to footer

Getting started with FlexSheet for MVC, a Web-based Spreadsheet Control

Provide users with a familiar, Excel-like web-based spreadsheet control with FlexSheet for ASP.NET MVC. FlexSheet for ASP.NET MVC offers a unique combination of server and client side features:

  • Export\Import excel files from both client and server
  • Load\Save files from remote storage
  • Format
  • Filter
  • Sort the data
  • Apply formulas
  • Freeze or merge rows\column, cell ranges
  • Supports unbound sheets
  • Data-bind the sheets using MVC models

Build an Excel-like App with FlexSheet Excel like App with FlexSheet

Let's walk through each feature and understand the basics.

Adding FlexSheet Spreadsheet Control to MVC Project

Use the C1MVC Template, then select the FlexSheet library in the wizard to include the control in the project. C1MVC Template wizard C1MVC Template wizard

Note: If you've used a standard Visual Studio template to create a project, there are a few steps required to add FlexSheet to the project. Please refer FlexSheet quick-start documentation to know about adding FlexSheet. [ADD LINK]

Spreadsheet Control Features

DataBound FlexSheet

You could data bind the FlexSheet with the model data easily by adding a bound sheet to the sheet collection. AddBoundSheet also supports remote data binding using the url.action and specifying the controller action that sends C1JSON formatted data.


@(Html.C1().FlexSheet().Id("flex")  
    .AddBoundSheet(s => s.Bind(Model).GroupBy("Product").Name("Product"))  
.Height(520).Width(640)  
);  

Unbound Sheets

Adding unbound sheets is also possible with FlexSheet control. Use the AddUnboundSheet property to add an unbound sheet:


@(Html.C1().FlexSheet().Id("flex")  
    .AddUnboundSheet(s => s.ColumnCount(8).RowCount(25).Name("Unbound Sheet"))  
    .AddBoundSheet(s => s.Bind(Model).GroupBy("Product").Name("Product"))  
.Height(520).Width(640)  
);  

Add Data to Sheets

FlexSheet supports Excel-like data editing, but it's also possible to programmatically add data to cells. The setCellData method should be used to add data to particular cell:


<script><br/>&nbsp;var&nbsp;flex<br/>&nbsp;&nbsp;&nbsp;&nbsp;c1.documentReady(function&nbsp;()&nbsp;{<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;flex&nbsp;=&nbsp;wijmo.Control.getControl("#flex");<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;});<br/>function&nbsp;setData(){<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;flex.setCellData(0,&nbsp;1,&nbsp;"Apple");<br/>}<br/></script>  

@(Html.C1().FlexSheet().Id("flex")  
    .AddUnboundSheet(s => s.ColumnCount(8).RowCount(25).Name("Unbound Sheet"))  
    .AddBoundSheet(s => s.Bind(Model).GroupBy("Product").Name("Product"))  
.Height(520).Width(640)  

Format Data and Cells

You can format the data of cells and cell range using the applyCellsStyle method of FlexSheet. Refer to the documentation and demo for all available formatting options:



    function setBold() {  
        flex.applyCellsStyle({ fontWeight: 'bold' });  
    }  
    function changeFont() {  
       flex.applyCellsStyle({ fontFamily: 'Unicode' });  
    }  
    function changeFontSize() {  
     flex.applyCellsStyle({ fontSize: '18px' });  
    }  
    function setColor() {  
       flex.applyCellsStyle({ backgroundColor: '#CCCCFF' });  
    }  
    function align() {          
       flex.applyCellsStyle({ textAlign: 'Right' });  
    }  



Apply Formulas

FlexSheet supports around 100 different types of basic, logical, mathematical, aggregate, date, and financial formulas. These formulas can be applied by the user via the dropdown UI in cells or programmatically evaluated. Add Formulas to Spreadsheets Add Formulas to Spreadsheets

Programmatically Evaluate Formulas

 function setFormula() {  
        flex.setCellData(2, 2, "=sum(A3:B3)");  
        flex.setCellData(3, 2, "=abs(A4)");  
    }  

FlexSheet also supports user-defined functions. Read more in our Custom Function demo.

Sorting

FlexSheet supports sorting in sheets via the client side SortManager class. sortDescriptions can be added to the SortManager object and then commitSort() method can be called to sort a column programmatically:


<script><br/>var&nbsp;flex,sortManager;<br/>&nbsp;&nbsp;&nbsp;&nbsp;c1.documentReady(function&nbsp;()&nbsp;{<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;flex&nbsp;=&nbsp;wijmo.Control.getControl("#flex");<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sortManager&nbsp;=&nbsp;flex.sortManager;<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;});<br/>function&nbsp;sortFlex()&nbsp;{<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sortManager.sortDescriptions.items[0].columnIndex&nbsp;=&nbsp;2;<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sortManager.sortDescriptions.items[0].ascending&nbsp;=&nbsp;false;<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sortManager.commitSort();<br/>&nbsp;&nbsp;&nbsp;&nbsp;}<br/></script>  



@(Html.C1().FlexSheet().Id("flex")  
    .AddUnboundSheet(s => s.ColumnCount(8).RowCount(25).Name("Unbound Sheet"))  
    .AddBoundSheet(s => s.Bind(Model).GroupBy("Product").Name("Product"))  
.Height(520).Width(640)  
);  



Filter Data

FlexSheet uses the FlexGridFilter extension to handle the filtering of each column. When the showColumnFilter() method of FlexSheet is invoked, it displays the filter UI of the selected column. The following code display the Filter UI for the selected column:


function filterFlex() {  

        flex.showColumnFilter();  
    }  

Excel style column filtering Excel style column filtering

Note that the same UI could be used to sort the selected column, as well.

Merge Cells

FlexSheet supports merging the selected cells by invoking the mergeRange method. If the selected cells contain a merged cell, the mergeRange method will un-merge the merged cell. Otherwise, it merges the selected cells into one cell. The following code programmatically merges a predefined cell range. It's also possible to merge a cell range selected by the user through the mouse.


function mergeSelection()  
    {  
        //merge predefined range  
        flex.mergeRange(new wijmo.grid.CellRange(1, 1, 5, 2));  
        flex.refresh();  
        //cell range selected through mouse by user  
       // flex.mergeRange();  
    }  

Freezing Rows and Columns

The freezeAtCursor method enables freezing of rows and columns at cursor position, that means all rows above and all column before the cursor position will be frozen. You can also set predefined frozen rows and columns at server side using the FrozenRows and FrozenColumns properties of a sheet.


function freezeCells() {  
        flex.freezeAtCursor();  
    }  

Client-side Excel IO

Load & Save XLSX file from FlexSheet Load & Save XLSX file from FlexSheet FlexSheet supports support loading and saving Excel files at client-side through the load and save methods:


<script><br/>&nbsp;var&nbsp;flex;<br/>&nbsp;&nbsp;&nbsp;&nbsp;c1.documentReady(function&nbsp;()&nbsp;{<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;flex&nbsp;=&nbsp;wijmo.Control.getControl("#flex");<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;});<br/>&nbsp;function&nbsp;Save()&nbsp;{<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;flex.save("Sales.xlsx");<br/>&nbsp;&nbsp;&nbsp;&nbsp;}<br/>&nbsp;&nbsp;&nbsp;&nbsp;function&nbsp;Load()<br/>&nbsp;&nbsp;&nbsp;&nbsp;{<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;var&nbsp;fileInput&nbsp;=&nbsp;wijmo.getElement("#fileName");<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;flex.load(fileInput.files[0]);<br/>&nbsp;&nbsp;&nbsp;&nbsp;}<br/></script>  










@(Html.C1().FlexSheet().Id("flex")  
    .AddUnboundSheet(s => s.ColumnCount(8).RowCount(25).Name("Unbound Sheet"))  
    .AddBoundSheet(s => s.Bind(Model).GroupBy("Product").Name("Product"))  
.Height(520).Width(640)  
);  



JSON Load and Save

FlexSheet has built-in support to save to and load from JSON. FlexSheet is converted to a Workbook Object Model before saving to JSON.


<script><br/>var&nbsp;flex,jsonString;<br/>&nbsp;&nbsp;&nbsp;&nbsp;c1.documentReady(function&nbsp;()&nbsp;{<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;flex&nbsp;=&nbsp;wijmo.Control.getControl("#flex");<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;});<br/>&nbsp;function&nbsp;SaveToJson()&nbsp;{<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;var&nbsp;workBook&nbsp;=&nbsp;flex.saveToWorkbookOM();<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;jsonString&nbsp;=&nbsp;JSON.stringify(workBook);<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br/>&nbsp;&nbsp;&nbsp;&nbsp;}<br/>&nbsp;&nbsp;&nbsp;&nbsp;function&nbsp;LoadJson()&nbsp;{<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;var&nbsp;workBook&nbsp;=&nbsp;JSON.parse(jsonString);<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;flex.loadFromWorkbookOM(workBook);<br/>&nbsp;&nbsp;&nbsp;&nbsp;}<br/></script>  








@(Html.C1().FlexSheet().Id("flex")  
    .AddUnboundSheet(s => s.ColumnCount(8).RowCount(25).Name("Unbound Sheet"))  
    .AddBoundSheet(s => s.Bind(Model).GroupBy("Product").Name("Product"))  
.Height(520).Width(640)  
);  



Remote Load and Save

FlexSheet can load and save Excel files from server and remote storages using the server-side RemoteLoad and RemoteSave. The following controller code defines a Action that returns a Excel file saved on server in JSON format.


public ActionResult RemoteLoad()  
        {  
            return this.C1Json(FlexSheetHelper.Load("~/Files/flexgroup.xlsx"),  
                  null, null, JsonRequestBehavior.AllowGet);  
        }  

The following code will save a Excel file on server:


private const string \_FILE\_PATH = "~/files/uploadFile/flexgroupupdated.xlsx";  
        public ActionResult RemoteSave([FlexSheetRequest]FlexSheetSaveRequest request)  
        {  
            var success = true;  
            var error = "";  

            try  
            {  
            string savePath=Server.MapPath(\_FILE\_PATH);  
            Stream st = request.GetFileStream();  
            using(FileStream fs=new FileStream(savePath,FileMode.Create))  
            {  
                if (st != null)  
                {  
                byte[] byteArray=new byte[st.Length];  
                st.Read(byteArray,0,(int)st.Length);  
                fs.Write(byteArray, 0, byteArray.Length);  
                }  

            }  
            }  
            catch (Exception e)  
            {  
                success = false;  
                error=e.ToString();  
            }  
            return this.C1Json(FlexSheetHelper.Save(success, error));  

        }  

This Razor code sets the relevant properties and subscribes to required client side methods for FlexSheet. It also demonstrates saving a FlexSheet to Excel file on server by calling the client side remoteSave method on button click:


<script><br/>&nbsp;&nbsp;&nbsp;&nbsp;function&nbsp;Save()&nbsp;{<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;var&nbsp;flex&nbsp;=&nbsp;wijmo.Control.getControl("#remoteSheet");<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;flex.remoteSave(c1.mvc.grid.sheet.ContentType["Xlsx"]);<br/>&nbsp;&nbsp;&nbsp;&nbsp;}<br/>&nbsp;&nbsp;&nbsp;&nbsp;function&nbsp;OnfileSaved(sender,args){<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if&nbsp;(args.success)&nbsp;{<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;alert("File&nbsp;Saved");<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}&nbsp;else&nbsp;{<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;alert(args.error);<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}<br/>&nbsp;&nbsp;&nbsp;&nbsp;}<br/></script>  

Index
-----









@(Html.C1().FlexSheet().Id("remoteSheet").Height(520).Width(640)  
.RemoteLoad(Url.Action("RemoteLoad"))  
.RemoteSave(Url.Action("RemoteSave")).OnClientRemoteSaved("OnfileSaved")  
)  



MESCIUS inc.

comments powered by Disqus