Friday, May 31, 2013

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.

What you should take from this

My solution here is not specific to Web Forms. This pattern is just hooking to data and serializing/deserializing it in a way that can be passed between the server and client, no matter what language the server is running.

8 comments:

Tonia Roddick said...

Could you post your code on how you accomplished: " 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."
I am working on a similar task.
Thank you.

Bryan Wood said...

I will try and build out a full solution and post it to github as per your request. This will take a few hours to get presentable. In the meantime, what specifically are you having trouble with?

Tonia Roddick said...

I think I mostly have it figured out now. So, no need to take the time on my account. I'm able to do JSON.stringify(handsontable.getData()) and pass that to my mvc controller action waiting for all the table data. Works, but I need to pass along a second parameter, projectId, to maintain as well. I'm playing with ways to stringify the table and my second parameter into one input now. Thanks though!

Bryan Wood said...

The original solution I was working with was an ASP.NET webforms page that was doing a standard POST pattern, not AJAX. What you could do is to create a 'form' object and attach different content which is then parsed by MVC.

var hst = JSON.stringify(handsontable.getData());
var form = { 'field1': $("field1").val(), 'tableData': hstData };

Michael said...

Hello Bryan, How are you?

I'm Michael from Brazil.
I was reading this article and i hard tried to make it work but i could not, would you have any working example that you can send me?
any simple example would help me

thanks in advance

Shahid said...

Hi,

I am trying populate JQuery HandsonTable from codebehind and also want to get data from HandsonTable to save back into the database.

Problem.

1. I successfully populate my database data into the HandsonTable BUT whenever postback happend data disappear from the HandsonTable. ( in code postable back happend due to save button click event)

2. Second when i click save button how i can read data from HandsonTable and convert back to my data table structre.

here is code behind

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

public partial class TableExample : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
loadData();
}
protected void loadData() {
DbHandler handler = new DbHandler();
DataTable dt = new DataTable();
dt = handler.dbQuery("Select Convert(varchar(5),Timeline) [Tid],Isnull(Field1,'Information regarding') [Info], Isnull(Field2,'special') [Special], isnull(Field3,'Hello') [Ans] From RumBokTimeline");
string data = GetJson(dt);
Page.ClientScript.RegisterStartupScript(this.GetType(), "example", "example("+data+");", true);
}
// Convert DataTable into Json format
public string GetJson(DataTable dt)
{
System.Web.Script.Serialization.JavaScriptSerializer serializer = new

System.Web.Script.Serialization.JavaScriptSerializer();
List> rows =
new List>();
Dictionary row = null;

foreach (DataRow dr in dt.Rows)
{
row = new Dictionary();
foreach (DataColumn col in dt.Columns)
{
row.Add(col.ColumnName.Trim(), dr[col]);
}
rows.Add(row);
}
return serializer.Serialize(rows);
}
protected void btnSave_Click(object sender, EventArgs e)
{

}
}

Bryan Wood said...

Shahid

The key to your success lays in the fact that handsontable does not post back and my solution provides just that.

1) You need to include a hidden field that represents the data for postback

2) Use the Javascript that I have here to move the data from handsontable into the hidden field.

3) Use the C# that I have here to parse the hidden field content

Shahid said...

Thanks bryan for your reply. I suppose to get mail when you post you reply, that's why i did not reply back to you immediately. today i check again your post i find your reply.

I need to ask more question to understand properly.

1. If its not post back we get data from database.
2. convert it the data to jason format and save jason converted into hidden field.
3. shows the jason data into handOntable.


Now the things create trouble for me.

1. User edit the data in handonTable.
2. user press save button.
3. Save button generate post back event.
4. Now basically i have to read the handontable data in java script and store the new data into the hidden field to access the data in code behind.

step 4 i cant figure out how to implement.

Could you do fever me can you post a simple webform so i can download and check your solution.

Thanks alot

Shahid


Ajax Lesson

NOTE : This lesson uses jQuery as the interface for basic AJAX work. By no means is AJAX a product of jQuery and you do not need jQuery to ...