Tuesday, April 1, 2014

Saving handsontable data

What prompted me to use handsontable

I was working on replacing an old Excel web control with a more user friendly and needed a JavaScript library that provided the functionality that I needed. After investigating several libraries I decided that handsontable provided me the best solution.

I thank Marcin Warpechowski, for creating this wonderful library. The community around this is very helpful and this library is rapidly evolving into an even better library!

What my solution involved

My project that I was implementing handsontable was a classic example of Web Forms with PostBack and code behind access of form elements directly.

How to save the data

I did not try to create a Web Form custom element, instead I just took advantage of what was already implemented. I started out with creating everything I needed to render a blank handsontable to my page where I was replacing this old control. You can get that from the handsontable demo pages.
I then added the JSON2 library, from Douglas Crockford's Github repository, to my project and included it in the page that the table is on.

Lastly, I used jQuery to bind an event on the form submission that ran a simple verification that there were no invalid cells in my table. If my table was completely valid, then the event would harvest the data from handsontable and with JSON2 serialize that data into a valid JSON string and save it into a hidden field that my code-behind would pick up. Once it is on the server you can do what you need to do to this.

How to retrieve the data

With Web Forms and the page I was working with, I needed to be able to repopulate the table with data that had traveled across my PostBack event. So just as I did with my form submission event, I wrote a specialized page load event that populated an object for my handsontable to use during it's creation.

My event just listens for the ready event of the page and then looks for that hidden field I created to save the data. It pulls the data out of the hidden field and uses JSON2 to deserialize the object out of the string and that result is passed to my handsontable create table method.


var hst = (function () {
  var $container = $("#hst");

  $container.handsontable({
    startRows: 8,
    startCols: 6,
    rowHeaders: true,
    colHeaders: true,
    minSpareRows: 1,
    contextMenu: true
  });

  hst = $container.data('handsontable');

  var $dataLocker = $("#dataLocker").val();

  if ($dataLocker.length > 0) {
    var data = JSON.parse($dataLocker);

    hst.loadData(data);
  }
  return hst;
})();

// Enable a click handler for the button to save data and submit the form
$("#saveContent").click(function (e) {
  // Save the table data before sending the form
  $("#dataLocker").val(JSON.stringify(hst.getData()));
  $("body > form").submit();

});




using System;
using System.Collections.Generic;
using System.Web.UI.HtmlControls;
using Newtonsoft.Json;

namespace HandsonDemo
{
    public partial class _Default : System.Web.UI.Page
    {
        protected HtmlInputHidden dataLocker;
        private List<List<int?>> Data = new List<List<int?>>
        {
            new List<int?> { 1, 2, 3, 4, 5, 6 },
            new List<int?> { 7, 8, 9, 10, 11, 12 },
            new List<int?> { 13, 14, 15, 16, 17, 18 },
            new List<int?> { 19, 20, 21, 22, 23, 24 },
        };

        protected void Page_Load(object sender, EventArgs e)
        {
            if (IsPostBack)
            {
                // if we are posting data to the server deserialize it so that we can manipulate it
                Data = JsonConvert.DeserializeObject<List<List<int?>>>(dataLocker.Value);
            }

            // store the data object to be rendered to the handsontable
            dataLocker.Value = JsonConvert.SerializeObject(Data);
        }
    }

}