rschandrastechblog: [CLIENT GRIDVIEW III] : http://www.aspboy.com/Categories/GridArticles/Excel_Like

http://www.aspboy.com/Categories/GridArticles/Excel_Like_GridView.aspx

reating an Excel Like GridView.

Author: Srikanth Reddy

There are many posts in the forums where people ask how to make a Grid which behaves like an excel sheet i.e. a Grid which has resizable columns, a Grid which has resizable rows, a Grid which has editable cells, a Grid whose cells can be navigated by using the arrow keys and the Tab key, a focussed cell in the Grid which responds to the key strokes when a user starts typing. When you Google for an “Excel Like GridView” or “Excel Like DataGrid” what you get is a link which doesn’t meet your expectations or a link which is already dead. I hope you will enjoy this article on making “An Excel Like GridView.” I would like thank Matt Berseth whose blog provoked my thought on making “An Excel Like GridView.”

So, let’s get it started.

The features this GridView provides are:

Resizable columns.
Resizable rows.
Editable cells.
Navigating through cells using the arrow keys and the tab key.
Focussed cell which responds to the key strokes when the user starts typing.

Before you start reading the article, Click here to view the demo. Currently the demo works only on Internet Explorer (version 6 and 7).

Now that you have seen the demo lets see on how An Excel Like GridView can be created. Add a GridView to your page which looks as shown below:

<asp:GridView ID="gvExcel" runat="server" Width="50%" OnRowDataBound="gvExcel_RowDataBound">

</asp:GridView>

Now do the following in the page load event:

protected void Page_Load(object sender, EventArgs e)

{

Char[] alpha ={ ' ', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J' };

DataTable dtExcel = new DataTable();

for (int i = 0; i < alpha.Length; i++)

{

dtExcel.Columns.Add(alpha[i].ToString());

}

for (int i = 0; i < 10; i++)

{

DataRow drRow = dtExcel.NewRow();

drRow[0] = i + 1;

dtExcel.Rows.Add(drRow);

}

gvExcel.BorderColor = System.Drawing.Color.FromArgb(0, 0, 0);

gvExcel.DataSource = dtExcel;

gvExcel.DataBind();

}

What am I doing here is, we are creating a DataTable which can be bound to the GridView. The character array is used to create columns in the GridView. In the example shown above I have created columns from A to J and an empty space column at the beginning. And then I have created 10 rows in the DataTable whose first column holds the sequence number of the row.

Now that the DataTable is bound to the GridView lets handle something in the RowDataBound event. The following is the RowDataBound event of the GridView:

protected void gvExcel_RowDataBound(object sender, GridViewRowEventArgs e)

{

e.Row.Cells[0].Width = Unit.Pixel(5);

e.Row.Cells[0].BorderColor = System.Drawing.Color.Black;

e.Row.Cells[0].BackColor = System.Drawing.Color.FromArgb(242, 242, 242);

if (e.Row.RowType == DataControlRowType.DataRow)

{

for (int i = 1; i < e.Row.Cells.Count; i++)

{

TextBox txt = new TextBox();

txt.TextMode = TextBoxMode.MultiLine;

txt.Rows = 1;

txt.CssClass = "textBox";

txt.BorderWidth = Unit.Pixel(0);

txt.Width = Unit.Pixel(70);

txt.Text = "";

e.Row.Cells[i].Controls.Add(txt);

e.Row.Cells[i].BorderColor = System.Drawing.Color.Black;

txt.Attributes.Add("onclick","javascript:setFocus(this)");

}

else if (e.Row.RowType == DataControlRowType.Header)

{

e.Row.BackColor = System.Drawing.Color.FromArgb(242, 242, 242);

for (int i = 1; i < e.Row.Cells.Count; i++)

{

e.Row.Cells[i].BorderColor = System.Drawing.Color.Black;

}

Every first cell of the Gridview in the RowDataBound event is set to 5 pixel width and its BorderColor set to Black and BackColor set to RGB values 242,242 and 242. And I have set each header cell’s BorderColor to black in the “else if” part(the header part) of the RowDataBound event.

Now in the “if” part(the DataRow part), Im creating Multiline TextBoxes in each cell of the row with the TextBox’s BorderWidth set to Zero and Width set to 70 pixels and I have added the TextBox to each cell of the Row with each cell’s border color set to Black. Pay attention to the Bolded and underlined part (txt.CssClass = "textBox";). This is a css class assigned to the TextBox which makes the scrollbar of the Multiline TextBox invisible. The textBox class is as shown below:

.textBox

{

overflow:hidden;

}

Finally, the onclick event is added to each text box to set the focus when the user clicks a particular cell in the GridView.

If you have seen the demo of this article, you may have noticed that unlike the Grids you have seen which restrict you to resize the columns within the boundaries of the Grid, this Grid lets you resize the column to any extent. i.e as you resize the column the GridView keeps on growing horizontally and as you resize the row the GridView keeps on growing vertically. So, now lets make the GridView resizable.

Add a script manager to your aspx page as shown below.

<asp:ScriptManager ID="ScriptManager1" runat="server">

</asp:ScriptManager>

Script manager implicitly calls the client side pageLoad event.

Add the following script tag to your page:

var headers;

var rows;

var grid;

var resizing;

var element;

var cursorType;

var currentCellReference;

function pageLoad(object)

{

grid=document.getElementById("gvExcel");

if(grid!=null)

{

$addHandler(document,'mousemove',mouseMove);

$addHandler(document,'mouseup',mouseUp);

$addHandler(document, 'selectstart', selectStart);

headers=document.getElementsByTagName('TH');

if(headers!=null)

{

for(i=0;i<headers.length;i++)

{

headers[i].style.width=Sys.UI.DomElement.getBounds(headers[i]).width+'px';

$addHandler(headers[i],'mousedown',mouseDown);

}

rows=document.getElementsByTagName('TR');

{

if(rows!=null)

{

for(i=1;i<rows.length;i++)

{

rows[i].style.width=Sys.UI.DomElement.getBounds(rows[i]).width+'px';

$addHandler(rows[i].children[0],'mousedown',mouseDown)

}

currentCellReference=rows[1].children[1];

rows[1].children[1].style.borderWidth=3;

}

function mouseMove(object)

{

if(resizing)

{

if(cursorType=='e-resize')

{

var bounds=Sys.UI.DomElement.getBounds(element);

var newColumnWidth =(object.clientX+document.documentElement.scrollLeft)-bounds.x;

if(newColumnWidth>0)

{

element.style.width=newColumnWidth;

for(i=1;i<rows.length;i++)

{

rows[i].children[element.cellIndex].children[0].style.width=newColumnWidth;

}

var newGridWidth=0;

for(i=0;i<headers.length;i++)

{

newGridWidth+=parseInt(headers[i].style.width);

}

grid.style.width=newGridWidth;

}

else if(cursorType=='n-resize')

{

var bounds=Sys.UI.DomElement.getBounds(element);

var newRowHeight=(object.clientY+document.documentElement.scrollTop)-bounds.y;

if(newRowHeight>0)

{

element.style.height=newRowHeight;

for(i=1;i<element.children.length;i++)

{

element.children[i].children[0].style.height=newRowHeight;

}

else

{

var bounds=Sys.UI.DomElement.getBounds(object.target);

if(Math.abs((bounds.x+bounds.width)-(object.clientX+document.documentElement.scrollLeft))<=2)

{

if(object.target.tagName=='TH' && object.target.cellIndex!=0)

{

object.target.style.cursor='e-resize';

}

else if(Math.abs((bounds.y+bounds.height)-(object.clientY+document.documentElement.scrollTop))<=2)

{

if(object.target.tagName=='TD' && object.target.cellIndex==0)

{

object.target.style.cursor='n-resize';

}

else

{

object.target.style.cursor='auto';

}

function mouseUp(object)

{

resizing=false;

}

function mouseDown(object)

{

if(object.target.style.cursor=='e-resize')

{

resizing=true;

cursorType=object.target.style.cursor;

element=object.target;

}

else if(object.target.style.cursor=='n-resize')

{

resizing=true;

cursorType=object.target.style.cursor;

element=object.target.parentElement;

}

function selectStart(object)

{

if(resizing)

{

object.preventDefault();

return false;

}

</script>

Now let me explain you what this code does.

In the pageLoad event I get the grid by using it’s id. If the Grid is not null then I have added three events (mousemove, mouseup and selectstart) to the document.

Note: The mousemove and mouseup events can be registered only to the header and row cells of the GridView but I came up with the idea of adding it to the document because while you resize the GridView (for example a column), if the mousemove event is registered just to the header cell, the user while resizing the column will have to take care that the mouse pointer doesn’t go out of the header and if it goes, the resizing stops. So by adding this event to the document ensures unattentive resizing i.e. even if the mouse pointer moves out of the header or row the resizing still continues. The resizing stops when the mouse button is released. And if the mouse button is released out of the window the resizing still continues when you bring back the pointer because this window still has not received the mouseup event.

Now the following line of code:

headers=document.getElementsByTagName('TH');

if(headers!=null)

{

for(i=0;i<headers.length;i++)

{

headers[i].style.width=Sys.UI.DomElement.getBounds(headers[i]).width+'px';

$addHandler(headers[i],'mousedown',mouseDown);

}

gets all the header cells in the document. As you can see that Im adding the mousedown event for each header cell.

Note: If your page contains only one GridView then using headers=document.getElementsByTagName('TH'); is fine but if your page contains multiple GridViews or HTML tables then using headers=$get('gvEmployees').getElementsByTagName('TH'); is a good practice as you will not end up in retrieving unnecessary headers in a document.

The following code gets all the rows in the document:

rows=document.getElementsByTagName('TR');

{

if(rows!=null)

{

for(i=1;i<rows.length;i++)

{

rows[i].style.width=Sys.UI.DomElement.getBounds(rows[i]).width+'px';

$addHandler(rows[i].children[0],'mousedown',mouseDown)

}

currentCellReference=rows[1].children[1];

rows[1].children[1].style.borderWidth=3;

}

A mousedown event is also added for the first cell of each row, this is where the user gets the control to resize the row.

The following two lines in the above code are used to set the current focussed cell and to set its borderWidth to 3 pixels. Here I have set the second row’s(remember that the first row is the header with cells as THs) second cell as the curentCellReference to make it as the defaul selection while the page first loads.

currentCellReference=rows[1].children[1];

rows[1].children[1].style.borderWidth=3;

Now that the pageLoad part is completed lets move on to the mouseMove part.

Let us start in a step by step process of understanding how each event is fired:

First the user moves his mouse pointer, at this point of time the else part of the mouseMove keeps on running to decide whether the mouse pointer is at the edge of the header cell or on the edge of the row cell and if it is, then the pointer itself changes to ‘e-resize’ or ‘n-resize’ indicating the user that the column or the row can be resized. The decision on how to change the cursor to e-resize is done by the following line of code:

if(Math.abs((bounds.x+bounds.width)-(object.clientX+document.documentElement.scrollLeft))<=2)

bounds.x – Gives the x coordinate value of the current column which is being resized.

bounds.width – Gives the current width of the column(‘TH’).

object.clientX – Gives the current x coordinate value of the mouse pointer.

document.documentElement.scrollLeft – Gives the value of the extent to which the window is horizontally scrolled. If the window is not scrolled then this returns zero.

So by adding bounds.x+bounds.width I get the x coordinate value of the egde of the column (TH. Now if this added value is less than or equal to 2 of the current x coordinate value of the mouse pointer then the pointer of the mouse is changed to e-resize.

Similarly, the decision on how to change the cursor to n-resize is done by the following line of code:

else if(Math.abs((bounds.y+bounds.height)-(object.clientY+document.documentElement.scrollTop))<=2)

bounds.y – Gives the y coordinate value of the current row which is being resized.

bounds.height – Gives the current height of the row(‘TR’).

object.clientY – Gives the current y coordinate value of the mouse pointer.

document.documentElement.scrollTop– Gives the value of the extent to which the window is vertically scrolled. If the window is not scrolled then this returns zero.

So by adding bounds.y+bounds.height I get the y coordinate value of the bottom egde of the row(TR) now if this added value is less than or equal to 2 of the current y coordinate value of the mouse pointer then the pointer of the mouse is changed to n-resize.

Now when the user sees the resize pointer, he clicks and drags it horizontally or vertically based on what he wants to resize(either a column or a row). So, when the user sees the e-resize or n-resize pointer he clicks in an attempt to resize. As soon as the user clicks, the mouseDown event is raised and the execution control instantly jumps to the mouseDown event. In the mouseDown event we are checking whether the user is trying to resize the column or resize the row. This can be determined just by the current cursor that is being displayed. i.e., if the current cursor is ‘e-resize’ then it means that the user is trying to resize a column and if it is ‘n-resize’ then it means that the user is trying to resize a row. So here we are setting resizing variable to true, cursorType variable to ‘e-resize’ or ‘n-resize’, and the element is a reference to the column or a row. Pay attention to how the element is being set in the ‘e-resize’ and ‘n-resize’ sections.

The line element=object.target; sets the element to the header(TH) and the line element=object.target.parentElement; sets the element to ‘TR’. To set the element to ‘TR’ we need to use the parentElement because the one which captures the mouseDown event is the cell ‘TD’(of a row), but when the user wants to resize he intends to resize the entire row but not a single cell in a row.

Now as the mouseDown event is finished the user continues dragging which makes the mouseMove event be raised, as the resizing variable is true, the control enters the if block of the mouseMove event. In the if block we determine whether its column resize or a row resize.

If it is column resize, then we get the bounds of the column(‘TH’) using the following line of code:

var bounds=Sys.UI.DomElement.getBounds(element);

The bounds of a column are nothing but the X,Y locations and Height and Width attributes of a Column(i.e TH). After that we calculate the new column width which is explained below:

var newColumnWidth =(object.clientX+document.documentElement.scrollLeft)-bounds.x;

Now I check if the new column width is greater than zero, if it is then the column which the user intends to resize is set to the newly calculated width. After that I set the width of the MultiLine textboxes present in that column to the newly calculated width. Also the entire GridView’s width is set to the summation of each column’s width.

And if the user wants to resize a row a similar calculation to determine the new height as shown below is done:

var newRowHeight=(object.clientY+document.documentElement.scrollTop)-bounds.y;

The new row height is set based on the above calculation. After that the multiline textboxes present in the resized row are set to the new row height.

As soon as the user releases the mouse pointer the mouseUp event is raised which sets the resizing variable to false.

The selectStart event simply avoids the text on the headers of the GridView being selected while the GridView is in resizing mode.

Now the last method which needs to be included in the script is the NavigateCell method which is as shown below:

Include this method within the script tags:

function NavigateCell()

{

if(event.keyCode==37)

{

if(currentCellReference.previousSibling.cellIndex!=0)

{

currentCellReference.style.borderWidth=1;

currentCellReference.previousSibling.style.borderWidth=3;

currentCellReference=currentCellReference.previousSibling;

grid.focus();

}

else if(event.keyCode==38)

{

if(currentCellReference.parentElement.previousSibling.rowIndex!=0)

{

currentCellReference.style.borderWidth=1;

currentCellReference.parentElement.previousSibling.children[currentCellReference.cellIndex].style.borderWidth=3;

currentCellReference=currentCellReference.parentElement.previousSibling.children[currentCellReference.cellIndex];

grid.focus();

}

else if(event.keyCode==39 || event.keyCode==9)

{

if(currentCellReference.nextSibling!=null)

{

currentCellReference.style.borderWidth=1;

currentCellReference.nextSibling.style.borderWidth=3;

currentCellReference=currentCellReference.nextSibling;

grid.focus();

}

else if(event.keyCode==40)

{

if(currentCellReference.parentElement.nextSibling!=null)

{

currentCellReference.style.borderWidth=1;

currentCellReference.parentElement.nextSibling.children[currentCellReference.cellIndex].style.borderWidth=3;

currentCellReference=currentCellReference.parentElement.nextSibling.children[currentCellReference.cellIndex];

grid.focus();

}

else

{

if(currentCellReference!=null)

currentCellReference.children[0].focus();

}

function setFocus(object)

{

currentCellReference.style.borderWidth=1;

object.parentElement.style.borderWidth=3;

currentCellReference=object.parentElement;

}

Modify your body tag so that it calls this method when the user presses a key:

What am I doing here is im comparing the ASCII values of the arrow keys 37, 38, 39, 40 and the tab key which is 9. If one of these values are encountered then the focus is navigated to the related cell in the Grid. And if other key codes are encountered then I assume that the user is trying to type and I set the focus to the multiline textbox which is present in the currently selected cell. Finally, the setFocus method is called when the user clicks on any particular cell.

With this we complete on how we can make a GridView behave like an Excel spreadsheet.

Note: A DataGrid can also be extended to behave like an excel sheet. The only difference between a GridView and DataGrid rendering is, GridView renders <TH>s where as a DataGrid doesn't. A DataGrid renders even the column names as <TD>s. So, the code must be slightly changed accordingly to treat the first row's <TD>s as columns.

I hope you enjoyed reading this article. Please post your valuable comments.

reating an Excel Like GridView.

Author: Srikanth Reddy

So, let’s get it started.

The features this GridView provides are:

Resizable columns.
Resizable rows.
Editable cells.
Navigating through cells using the arrow keys and the tab key.
Focussed cell which responds to the key strokes when the user starts typing.