Creating a Google Docs Spreadsheet-like interface

misc2As you may know, a Google Docs Spreadsheet acts almost exactly like Microsoft Excel but is completely web-based. In addition, it automatically saves the changes to a cell once the user has exited the cell. So the features that I am going to demonstrate are:

  1. Creating the basic html table
  2. Highlighting editable cells when moused over
  3. Adding a textbox to make cells editable
  4. Making the textbox invisible
  5. Using JavaScript & jQuery to capture changes when edited
  6. Adding an MVC Action method to save the changes
  7. Report that the changes were saved

Creating the basic html table

The first thing that I will do is create a basic html table with some sample data. In a real world application, this table would be created programmatically with data from the server, but for simplicity sake, I will used static data.

My sample data is simply a list of people and some basic information about them.

HTML Code Example
<table class="table1">
    <tr>
        <th>Company</th>
        <th>First Name</th>
        <th>Last Name</th>
        <th>Title</th>
    </tr>
    <tr>
        <td>IBS</td>
        <td>Fred</td>
        <td>Gohsman</td>
        <td>Software Developer</td>
    </tr>
    <tr>
        <td>Microsoft</td>
        <td>Scott</td>
        <td>Guthrie</td>
        <td>Vice President</td>
    </tr>
    <tr>
        <td>Oracle</td>
        <td>Larry</td>
        <td>Ellison</td>
        <td>CEO</td>
    </tr>
</table>
CSS Example
.table1 th
{
    padding: 4px 8px;
    background-color: lightblue;
    color: darkblue;
}
        
.table1 td
{
    padding: 4px 8px;
    background-color: beige;
    color: #333333;
}

And the resulting output is as follows. As you can see, I have added some styles to the table to make it bit more presentable. For breviety, I will omit these styles.

Company First Name Last Name Title
IBS Fred Gohsman Software Developer
Microsoft Scott Guthrie Vice President
Oracle Larry Ellison CEO

Highlighting editable cells when moused over

In order to make it more obvious to the user that a cell can be edited, I will add some styling using CSS to highlight a cell when the cursor moves over top of the cell and remove the highlighting when the cursor moves outside of the cell. In order to do this, I will first decorate the <td> tag with some properties that I will later reference from JavaScript.

HTML Code Example
<tr>
    <td class="editable">IBS</td>
    <td class="editable">Fred</td>
    <td class="editable">Gohsman</td>
    <td class="editable">Software Developer</td>
</tr>
CSS Example
.editable
{
    border: 2px solid transparent;
}
.editable:hover
{
    border: 2px solid red;
}
Company First Name Last Name Title
IBS Fred Gohsman Software Developer
Microsoft Scott Guthrie Vice President
Oracle Larry Ellison CEO

Adding a textbox to make cells editable

In order to make the cells editable, I will add a textbox to each editable cell.

In addition, I will give each textbox a unique id in the form of XXXX-9999, where XXXX represents the purpose of the data in the textbox and 9999 represents a unique identifier for that row of data.

In my example, I have used comp to specify that this textbox is used to edit the Company Name, fnam for the First Name, lnam for the Last Name, and titl for the Title. These values will be passed to the ASP.NET MVC method later on, which will be responsible for handling the data to be saved apropriately.

I will come back to these ids later.

HTML Code Example
<table class="table1">
    <tr>
        <th>Company</th>
        <th>First Name</th>
        <th>Last Name</th>
        <th>Title</th>
    </tr>
    <tr>
        <td class="editable"><input type="textbox" id="comp-1" value="IBS" /></td>
        <td class="editable"><input type="textbox" id="fnam-1" value="Fred" /></td>
        <td class="editable"><input type="textbox" id="lnam-1" value="Gohsman" /></td>
        <td class="editable"><input type="textbox" id="titl-1" value="Software Developer" /></td>
    </tr>
    <tr>
        <td class="editable"><input type="textbox" id="comp-2" value="Microsoft" /></td>
        <td class="editable"><input type="textbox" id="fnam-2" value="Scott" /></td>
        <td class="editable"><input type="textbox" id="lnam-2" value="Guthrie" /></td>
        <td class="editable"><input type="textbox" id="titl-2" value="Vice President" /></td>
    </tr>
    <tr>
        <td class="editable"><input type="textbox" id="comp-3" value="Oracle" /></td>
        <td class="editable"><input type="textbox" id="fnam-3" value="Larry" /></td>
        <td class="editable"><input type="textbox" id="lnam-3" value="Ellison" /></td>
        <td class="editable"><input type="textbox" id="titl-3" value="CEO" /></td>
    </tr>
</table>
Company First Name Last Name Title

Making the textbox invisible

Since the textbox makes the table look like a clumsy web form, I will add some styling to the textbox to make it less obtrusive.

CSS Example
.editable input
{
    border: 0;
    background-color: transparent;
}
Company First Name Last Name Title

Using JavaScript & jQuery to capture changes when edited

In a Google Doc Spreadsheet, the saving happens automatically in the backend. I will use JavaScript to capture the changes as they are made, then make an Ajax call to a url which is backed up by an ASP.NET MVC method which will save the data to a database.

First, the following code captures the textbox’s data when the textbox’s onchange event is fired. Then the code extracts the property’s short four letter name and the unique row id from the textbox’s id attribute and then passes them along with the value inside the textbox to the ASP.NET MVC method.

JavaScript Example
//  Capture the input's change event and call a function to save the data
$('.editable input').live('change', function () { SaveValue($(this)); });

function SaveValue(me) {
    var value = me.val();
    var prop = me.attr('id').substr(0, 4);
    var id = me.attr('id').substr(5, me.attr('id').length);
    
    // Show that the field has been edited and changes are not saved back to the database yet
    me.addClass('edited');

    // Save changes to the database
    $.ajax({
        type: "GET",
        url: "/Controler/SaveValue",
        data: { property: prop, id: id, value: value },
        cache: false,
        dataType: "json",
        success: function (data) {
            if (data.Saved)
                me.removeClass('edited');
            if (data.Message != '')
                ShowStatus(data.Message);
        },
        error: function (jqXHR, textStatus, errorThrown) {
            ShowError('Changes could not be saved.');
        }
    });
}

Adding an MVC Action method to save the changes

I will not go in to detail on how to save data to the database, but I will show an MVC method that can accept the parameters that were sent from the Ajax call. Once the data has been saved, the method returns the status to the Ajax call via the JsonResult class. This JsonResult is the data variable passed to the function which handles the success callback of the Ajax call.

ASP.NET MVC Code Example
public JsonResult SaveValue(string name, int id, string value)
{
    ... save data to database here ...           

    // Return the status
    return Json(new
    {
        Message = message,
        Saved = saved
    }, JsonRequestBehavior.AllowGet);
}        

Report that the changes were saved

Now I add a placeholder to display status and errors messages, add some css styles for these messages, and implement the JavaScript functions to display the messages when the Ajax call returns. And that’s all there is to making a webpage as user-responsive as the Google Docs Spreadsheet. Well almost anyhow.

CSS Example
#Message-Box
{
    width:550px !important
    min-height: 20px;
    padding: 3px 5px 3px 5px;
}
.highlight
{
    border: 1px solid brown;
    color: brown;
    background: beige;
 
}
.error
{
    border: 1px solid #CD0A0A;
    color: #CD0A0A;
    background: #FEF1EC;
}
JavaScript Code Example
function ShowStatus(message) {
    $('#Message-Box').removeClass('highlight');
    $('#Message-Box').removeClass('error');

    $('#Message-Box').text(message);
    $('#Message-Box').addClass('highlight');
}

function ShowError(error) {
    $('#Message-Box').removeClass('highlight');
    $('#Message-Box').removeClass('error');

    $('#Message-Box').text(error);
    $('#Message-Box').addClass('error');
}

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s