|
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:
<script language="javascript" type="text/javascript">
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:
<body onkeydown="NavigateCell()">
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.
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
Name:
|
|
|
Posted On:
|
|
|
Subject:
|
|
Comments:
|
|
|
|
Name: |
|
|
EMail: |
|
|
Subject:
|
|
|
Comments: |
|
Type the two words with a space in between:
(If the words doesn't appear(due to low connectivity) or if you are unable to recognize
them, click on Get a new challenge button adjacent to the text box.)
|
|
|
|
|