Creating an excel-like grid for ASP.NET

I have been searching around for a while because I was in the need for an ASP.NET control similar to asp:GridView, but with following huge differences:

• All rows and their cells must be in edit mode all the time. On postback (e.g. using a “Save” button), the data must be written back to the datasource.
  In GridView, in contrast, you need to hit a row’s  “Edit” to turn the row into edit mode, and hit the row’s “Update” button to save its changes to the datasource.
• You should be able to navigate through the cells using the arrow keys.
  In GridView, this doesn’t even apply since only one row at a time is in edit mode.
• You should be able to copy-paste multiple cells at a time from Excel into the grid. If you copy 3×2 cells in Excel, paste the cursor on a cell in the grid and Hit Ctrl+V, the cell values must be spread over the cells below and right from the selected cell.
  In GridView, in contrast, all cell values are pasted as a concatenated string into the selected cell.
• The grid should work in IE, Firefox and possibly other browsers. Well, I have to admit that my solution presented here only works for IE so far, but it should be feasible to extend the code for Firefox and Safari.

Having searched for quite a long time, the only really interesting thing I found was Srikanth Reddy’s Blog “Creating an Excel Like GridView”. His solution was really close to what I needed, and I want to thank Srikanth in this place for sharing his work. The original blog is not available anymore, but there now is a copy of Srikanth’s post at: http://rschandrastechblog.blogspot.com/2010/11/client-gridview-iii-httpwwwaspboycomcat.html. Yet I decided to post the whole of my code (which is mostly to be attributed to Srikanth) in this blog.

His version didn’t support pasting cells either, but it was quite easy to extend it such that this is now supported, too.

Apart from the new features, I also changed the structure of the code a bit. Let’s have closer look at the new code:

1. General Methods for Navigation

• There are 4 methods “getLeftNeighbor”, “getRightNeighbor”, “getAboveNeighbor” and “getBelowNeighbor”. They find and return the cell that is found right next to the given one.
• The code to actually select a certain cell is found in “NavigateToCell”.
• The code to format the previously selected and newly selected cells is found in the onfocus event. It’s the best place to do the formatting becausee it will take place no matter whether you click a cell, or use the arrows, tab or shift-tab to navigate to a neighbored cell.

2. Intercepting the paste event and spreading multiple values over the grid

When the user copies multiple cells from an Excel sheet and pastes it in a cell, you’d expect the values to be spread over multiple rows and columns, just like they would in Excel. However, the default behavior of a HTML textbox is to regard the string as a single value and paste everything in that particular text box.

Suppose that the user copies the following 3 x 2 cells from excel:

a	b
c	d
e	f

These values are represented in the clipboard as a text formatted as “a\tb\r\nc\td\r\ne\tf\r\n“.

In order to have these values be spread over the appropriate text boxes we need to intercept the paste event, split the clipboard text into a 2-dimensional array of values and then reuse our get-neighbor methods to find the cells below and right from the selected cell:

    function tupelizeText(text) {
        text = text.replace(/\r\n/g, '\n');
        text = text.replace(/\r/g, '\n');

        var tmparray = text.split("\n");
        var result = new Array();

        for (var i = 0; i < tmparray.length - 1; i++) {
            result[i] = tmparray[i].split('\t');
        }

        return result;
    }

    function txt_paste(element) {
        var strPasteData = window.clipboardData.getData("Text");
        var values = tupelizeText(strPasteData);
        var leftAreaBorderCell = curCell;
        for (i = 0; i < values.length; i++) {
            var pasteCell = leftAreaBorderCell;
            for (j = 0; j < values[i].length; j++) {
                pasteCell.firstChild.innerText = values[i][j];

                if (getRightNeighbor(pasteCell) != null)
                    pasteCell = getRightNeighbor(pasteCell);
                else
                    break;
            }

            if (getLowerNeighbor(leftAreaBorderCell) != null)
                leftAreaBorderCell = getLowerNeighbor(leftAreaBorderCell);
            else
                break;
        }

        return false;
    }

Imagine the special case where the selection in our grid is placed such that not all values from the clipboard can be pasted. To cover this we always check whether there is another row below or another column at the right. If not, we break that particular iteration.

To register the onpaste event I added a line in the C# code. I also added the onfocus event and a css class:

txt.Attributes.Add("onfocus", "txt_focus(this)");
txt.Attributes.Add("onpaste", "return txt_paste(this)");
txt.Attributes.Add("class", "SpreadsheetView-TextBox");

So much about some particularities. I don’t (yet) take the effort to explain the parts that Srikanth already introduced… I trust in your own development skills 🙂 So as promised, the full code below.

I hope this inspires you. If there are things unclear, please let me know! And again, much credits to Srikanth Reddy for his post. If you improve or extend this code (e.g. make it work in Firefox and/or Safari), please let me know! If you publish this code or an adaption of it, please attribute and put a link on this blog post.

SpreadsheetView.ascx

<%@ Control Language="C#" AutoEventWireup="true"
CodeBehind="SpreadsheetView.ascx.cs"
Inherits="WebBased.SpreadsheetView" %>

<div id="divSpreadsheetView" onkeydown="divSpreadsheetView_keydown(event)">
<table ID="tblTable" cellspacing="0" cellpadding="0" runat="server">
<tr>
<td><input type="text" /></td>
<td><input type="text" /></td>
</tr>
<tr>
<td><input type="text" /></td>
<td><input type="text" /></td>
</tr>
</table>
</div>

<script language="javascript" type="text/javascript">

var curCell = null;

// Called when user clicks on the textbox of a SpreadsheetView cell, selects the cell.
function txt_focus(element) {
if (curCell != null) curCell.className = "SpreadsheetView-Cell";
curCell = element.parentNode;
curCell.className = "SpreadsheetView-SelectedCell";
curCell.firstChild.select();
}

// Returns the cell left from the given cell, or null if the given cell is the left-most.
function getLeftNeighbor(cell) {
if (cell.previousSibling != null) {
return cell.previousSibling;
} else {
return null;
}
}

// Returns the cell right from the given cell, or null if the given cell is the right-most.
function getRightNeighbor(cell) {
if (cell.nextSibling != null) {
return cell.nextSibling;
} else {
return null;
}
}

// Returns the cell above the given cell, or null if the given cell is the top-most (excluding header).
function getUpperNeighbor(cell) {
if (cell.parentNode.previousSibling.rowIndex != 0) {
return cell.parentNode.previousSibling.children[cell.cellIndex];
} else {
return null;
}
}

// Returns the cell below the given cell, or null if the given cell is the bottom-most.
function getLowerNeighbor(cell) {
if (cell.parentNode.nextSibling != null) {
return cell.parentNode.nextSibling.children[cell.cellIndex];
} else {
return null;
}
}

// Helper method to select a cell.
function NavigateToCell(cell) {
cell.firstChild.focus();
}

// Called when user hits an arrow key while focus is on the SpreadsheetView, selects a neighbor cell.
function divSpreadsheetView_keydown(event) {
var keyCode;

if (!event) event = window.event;

if (event.which) {
keyCode = event.which;
} else if (event.keyCode) {
keyCode = event.keyCode;
}

if (keyCode == 37 && getLeftNeighbor(curCell) != null) {
NavigateToCell(getLeftNeighbor(curCell));
}
else if (keyCode == 38 && getUpperNeighbor(curCell) != null) {
NavigateToCell(getUpperNeighbor(curCell));
}
else if (keyCode == 39 && getRightNeighbor(curCell) != null) {
NavigateToCell(getRightNeighbor(curCell));
}
else if (keyCode == 40 && getLowerNeighbor(curCell) != null) {
NavigateToCell(getLowerNeighbor(curCell));
}
}

// Register keydown event.
//document.getElementById("divSpreadsheetView").onkeydown = divSpreadsheetView_keydown;

// Parses a string containing a copy of multiple excel cells and returns the cells in an array of arrays.
function tupelizeText(text) {
text = text.replace(/\r\n/g, '\n');
text = text.replace(/\r/g, '\n');

var tmparray = text.split("\n");

var result = new Array();

// text copied from excel always ends with a \n too much, so ignore last element.
for (var i = 0; i < tmparray.length - 1; i++) {
result[i] = tmparray[i].split('\t');
}

return result;
}

// Called when user pasts text in a textbox. If consists of multiple cells, spreads the values among textboxes.
function txt_paste(element) {
var strPasteData = window.clipboardData.getData("Text");

if (curCell == element.parentNode) {
var values = tupelizeText(strPasteData);

// this variable will iterate down the rows but always be the left-most of the area to paste into
var leftAreaBorderCell = curCell;

for (i = 0; i < values.length; i++) {

// this variable will iterate through all cells of the row
var pasteCell = leftAreaBorderCell;

for (j = 0; j < values[i].length; j++) {
pasteCell.firstChild.innerText = values[i][j];

if (getRightNeighbor(pasteCell) != null) {
pasteCell = getRightNeighbor(pasteCell);
} else {
break;
}
}

if (getLowerNeighbor(leftAreaBorderCell) != null) {
leftAreaBorderCell = getLowerNeighbor(leftAreaBorderCell);
} else {
break;
}
}

return false;
}

return true;
}

</script>

SpreadsheetView.ascx.cs

using System;
using System.Web.UI.WebControls;
using System.Data;
using System.Web.UI.HtmlControls;
using System.Web.UI;

namespace WebBased
{
public partial class SpreadsheetView : System.Web.UI.UserControl
{
public DataTable Model { get; set; }

public override void DataBind()
{
tblTable.Rows.Clear();
GenerateHeaderRow(tblTable, Model);

for (int iRow = 0; iRow < Model.Rows.Count; iRow++)
{
GenerateDataRow(tblTable, iRow, Model);
}
}

protected void Page_Load(object sender, EventArgs e)
{
if (this.IsPostBack)
{
// Read data from UI back into DataTable
for (int iRow = 1; iRow < tblTable.Rows.Count; iRow++)
{
for (int iCol = 0; iCol < tblTable.Rows[iRow].Cells.Count; iCol++)
{
HtmlTableCell cell = tblTable.Rows[iRow].Cells[iCol];
TextBox txt = cell.Controls[0] as TextBox;
Model.Rows[iRow - 1][iCol] = txt.Text;
}
}
}
}

private static void GenerateDataRow(HtmlTable tblTable, int iRow, DataTable dtDataTable)
{
HtmlTableRow row = new HtmlTableRow();
tblTable.Rows.Add(row);
row.Attributes.Add("class", "SpreadsheetView-Row");

for (int iCol = 0; iCol < dtDataTable.Columns.Count; iCol++)
{
HtmlTableCell cell = new HtmlTableCell();
row.Cells.Add(cell);
cell.Attributes.Add("class", "SpreadsheetView-Cell");

TextBox txt = new TextBox();
txt.ID = String.Format("txt_{0}_{1}", iRow, iCol);
txt.Width = Unit.Percentage(100);
txt.Text = dtDataTable.Rows[iRow][iCol].ToString();
txt.Attributes.Add("onfocus", "txt_focus(this)");
txt.Attributes.Add("onpaste", "return txt_paste(this)");
txt.Attributes.Add("class", "SpreadsheetView-TextBox");

cell.Controls.Add(txt);
}
}

private static void GenerateHeaderRow(HtmlTable tblTable, DataTable dtDataTable)
{
HtmlTableRow rowHeader = new HtmlTableRow();
tblTable.Rows.Add(rowHeader);
rowHeader.Attributes.Add("class", "SpreadsheetView-HeaderRow");

for (int iCol = 0; iCol < dtDataTable.Columns.Count; iCol++)
{
HtmlTableCell cellHeader = new HtmlTableCell("th");
rowHeader.Cells.Add(cellHeader);
cellHeader.Attributes.Add("class", "SpreadsheetView-HeaderCell");

cellHeader.InnerText = dtDataTable.Columns[iCol].Caption;
}
}

}
}

Putting it into Action

To use the control, just add a tag to your asp.net code:

<%@ Register TagPrefix="x" TagName="SpreadsheetView" Src="~/SpreadsheetView.ascx" %>

<x:SpreadsheetView ID="SpreadsheetView1" runat="server"/>

and feed it with an empty grid of your required size:

DataTable dataTable = new DataTable();

dataTable.Columns.Add("A");
dataTable.Columns.Add("B");
dataTable.Columns.Add("C");
dataTable.Columns.Add("D");

dataTable.Rows.Add(new string[] { "", "", "", "" });
dataTable.Rows.Add(new string[] { "", "", "", "" });
dataTable.Rows.Add(new string[] { "", "", "", "" });
dataTable.Rows.Add(new string[] { "", "", "", "" });
dataTable.Rows.Add(new string[] { "", "", "", "" });

SpreadsheetView1.Model = dataTable;
SpreadsheetView1.DataBind();

For completeness, these are some of the resources I found before trying it myself:

• http://aspnetrealworldcontr.codeplex.com/
  (the only “excel-like” thing is frozen headers…)
• http://www.nitobi.com/products/grid/copypaste/
  Allows for copy-paste! But is commercial and very buggy.
• http://stackoverflow.com/questions/587120/good-asp-net-excel-like-grid-control
  Thread on stack overflow to this question.
• http://www.microsoft.com/downloads/details.aspx?FamilyID=982B0359-0A86-4FB2-A7EE-5F3A499515DD&displaylang=EN
  (activex object)