Creating a Google Docs Spreadsheet-like interface
As 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:
- Creating the basic html table
- Highlighting editable cells when moused over
- Adding a textbox to make cells editable
- Making the textbox invisible
- Using JavaScript & jQuery to capture changes when edited
- Adding an MVC Action method to save the changes
- 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.
<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>
.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.
<tr>
<td class="editable">IBS</td>
<td class="editable">Fred</td>
<td class="editable">Gohsman</td>
<td class="editable">Software Developer</td>
</tr>
.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.
<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.
.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.
// 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.
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.
#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;
}
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');
}
