(untagged)

Load and Save Handsontable Data From/To Controller in ASP.NET MVC

yehia2amer

0.00/5 (No votes)

26 Jan 2016

This method will use jQuery Ajax to POST and GET Handsontable JSON data from/to Controller in ASP.NET MVC.

Introduction

There are a lot of packages that provide "Excel-like spreadsheets" in ASP.NET MVC. For example: "Grid.Mvc"
but in my opinion "Handsontable" is the most customize-able and feature loaded one, with compatibility with most common languages.

Background

Handsontable is very easy to use"Excel-like spreadsheet" and it has tons of features, but when it gets to binding the table data with the controller, it became a little tricky as we should use Ajax to do the job.

Here, we will use Handsontable Official Guide example and customize it to ASP.NET MVC4 to pass data from/to the controller.

Using the Code (Only 3 Steps)

1) Enable "Handsontable" in your Project

Download it from here.
Add "handsontable.full.min.js" to the Scripts folder and add "handsontable.full.css" to the content folder.
After Handsontable is downloaded, add those lines to the "_Layout.cshtml" <head> tag.

<script src="~/Scripts/handsontable.full.min.js"></script>
<link href="~/Content/handsontable.full.css" rel="stylesheet"/>

2) Edit the View

2.1) Add the HTML Code

It contains:

Two buttons for loading and saving data
A checkbox for autosave feature
A console tag for "User Messages"
and finally, a div used to load Handsontable

2.2) Add the Script Code

It contains:

Variables declaration
Handsontable Settings
Ajax Get Method (Load)
Ajax POST Method (Save)
Ajax POST Method (AutoSave AfterChange)

<div class="container">
    <div class="row">
        <div class="col-sm-12 col-md-12 text-center">
            <div class="ajax-container">
                <div class="controls">
                    <button class="intext-btn btn btn-primary center-block" 
                    id="load" name="load">Load</button>
                    <button class="intext-btn btn btn-primary center-block" 
                    id="save" name="save">Save</button>
                    <label><input type="checkbox" autocomplete="off" 
                    checked="checked" id="autosave" 
                    name="autosave">Autosave</label>
                </div>
                <pre class="console" 
                id="example1console">Click "Load" to load data from server</pre>
                <div id="example1" 
                class="hot handsontable dataTable table-striped center-block"></div>
            </div>
        </div>
    </div>
</div>

<script type="text/javascript">

    // Variables declaration & to make our life easier we set our HTML tags to variables too
    var
        $$ = function(id) {return document.getElementById(id);},
        container = $$('example1'),
        exampleConsole = $$('example1console'),
        autosave = $$('autosave'),
        save = $$('save'),
        load = $$('load'),
        autosaveNotification,
        hot;

    hot = new Handsontable(container, {
        startRows: 8,
        startCols: 6,
        rowHeaders: true,
        colHeaders: true,
        // This is for the AutoSave func
        afterChange: function(change, source) {
            if (source === 'loadData') {
                return; //don't save this change
            }
            if (!autosave.checked) {
                return;
            }
            clearTimeout(autosaveNotification);
            jQuery.ajax({
                url: '@Url.Action("AddCar")',
                type: "POST",
                dataType: "json",
                contentType: 'application/json; charset=utf-8',
                data: JSON.stringify(hot.getData()),
                async: true,
                processData: false,
                cache: false,
                success: function (data) {
                    exampleConsole.innerHTML = 'Changes will be autosaved';
                    autosaveNotification = setTimeout(function () {
                        exampleConsole.innerHTML = 'Autosaved (' + change.length + 
                        ' ' + 'cell' + 
                        (change.length > 1 ? 's' : '') + ')';
                    }, 1000);
                    //alert(data);
                },
                error: function(xhr) {
                    exampleConsole.innerHTML = 'Autosave: No Response from Controller';
                    //alert('error');
                }
            });
        }
      // End of AutoSave func
    });

    // GET method gets data from the Controller
    Handsontable.Dom.addEvent(load, 'click', function () {
        jQuery.ajax({
            url: '/Home/GetCar', //Controller to Get the 
            			//JsonResult From -- Json(jsonData, JsonRequestBehavior.AllowGet);
            type: "GET",
            dataType: "json",
            contentType: 'application/json; 
            charset=utf-8', // dataType and contentType should be json
            async: true,
            processData: false,
            cache: false,
            success: function (data) {      // on Success send the Json data 
            					// to the table by using loaddata function""
                //alert(data);
                hot.loadData(data);
                exampleConsole.innerHTML = 'Data loaded';
            },
            error: function (xhr) {
                alert('error');
            }
        });

        // POST method gets data to the Controller
        Handsontable.Dom.addEvent(save, 'click', function () {
            jQuery.ajax({
                url: '/Home/AddCar',
                type: "POST",
                dataType: "json",
                contentType: 'application/json; charset=utf-8',
                data: JSON.stringify(hot.getSourceData()),
                async: true,
                processData: false,
                cache: false,
                success: function (data) {
                    exampleConsole.innerHTML = 'Data saved';
                    //alert(data);
                },
                error: function (xhr) {
                    exampleConsole.innerHTML = 'Save error';
                    //alert('error');
                }
            });
        });

        Handsontable.Dom.addEvent(autosave, 'click', function() {
            if (autosave.checked) {
                exampleConsole.innerHTML = 'Changes will be autosaved';
            }
            else {
                exampleConsole.innerHTML = 'Changes will not be autosaved';
            }
        });
    });

</script>

2) Edit the Controller

public class HomeController : Controller
{
    public ActionResult Index()
    {
        return View();
    }

    //you can also use list of objects
    //(ex: public ActionResult AddCar(IEnumerable<Cars> CarsListFromTable))
    public ActionResult AddCar(List<string[]> dataListFromTable)
    {
        var dataListTable = dataListFromTable;
        return Json("Response, Data Received Successfully");
    }
    public JsonResult GetCar()
    {
        var jsonData = new[]
                     {
                         new[] {" ", "Kia", "Nissan",
                         "Toyota", "Honda", "Mazda", "Ford"},
                         new[] {"2012", "10", "11",
                         "12", "13", "15", "16"},
                         new[] {"2013", "10", "11",
                         "12", "13", "15", "16"},
                         new[] {"2014", "10", "11",
                         "12", "13", "15", "16"},
                         new[] {"2015", "10", "11",
                         "12", "13", "15", "16"},
                         new[] {"2016", "10", "11",
                         "12", "13", "15", "16"}
                    };

        return Json(jsonData, JsonRequestBehavior.AllowGet);
    }
}

Now, you are done and you should load and save data from the table.

Points of Interest

Now, I am very interested in "Handsontable" the AngularJS directive for Handsontable, I think databinding will be a lot easier especially with MVC5.

History

Handsontable_MVC_4.zip V1

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here