Load and Save Handsontable Data From/To Controller in ASP.NET MVC
5.00/5 (5 votes)
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
divused to load Handsontable
2.2) Add the Script Code
It contains:
- Variables declaration
- Handsontable Settings
- Ajax
GetMethod (Load) - Ajax
POSTMethod (Save) - Ajax
POSTMethod (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