It is not uncommon to decide that you want to have Excel functionality inside a web page, if you have a requirement where you need to enter information in batches or modify a lot of data at once.
An easy way to achieve Excel-like look and feel is to take the third-party Grid control you are using in your application, in my case Telerik’s ASP.NET Data Grid, and adjust its behavior. In this blog post I am going to show you how to implement exactly this scenario.
By the way, if you introduce such functionality in ASP.NET, in some respects your application will even be better than Microsoft Excel because:
While web applications and Excel have a lot in common, such as displaying data in a tabular form and allowing users to enter and manipulate data, Microsoft Excel is a desktop application and third-party Grid controls are designed and used for ASP.NET web development, so there are some Excel features that can hardly be implemented in an ASP.NET Grid:
The easiest way to have Excel functionality in a browser is to use the latest version of Telerik’s Grid control. It allows many of the functions to be implemented with less code than with the ASP GridView control.
You can download the complete project here, but here are the basic steps you need to follow:
control.Attributes.Add(
"onclick"
,
"cellClick('"
+ control.ClientID +
"');"
);
//Adds the click and dblclick event handlers to the HTML element
control.Attributes.Add(
"ondblclick"
,
"cellDoubleClickFunction('"
+ control.ClientID +
"');"
);
control.CssClass =
"row"
+ itemIndex.ToString() +
"col"
+ colIndex.ToString();
//We hook the OnKeyDown Dom event of RadGrid's element. This event will helps us to build the keyboard
//navigation through the cells.
RadGrid1.Attributes.Add(
"onkeydown"
,
"onKeyDown(this,event);"
);
//Used to sync the changes between the DataTable object and the actual database.
private
void
SynchDataBase()
{
using
(SqlConnection conn =
new
SqlConnection(ConnectionString))
{
SqlDataAdapter adapter =
new
SqlDataAdapter();
//Based on the select statement defined at the top of the current class,
//the adapter will be able to build the respective Insert, Update, Delete statements.
adapter.SelectCommand =
new
SqlCommand(SELECT_QUERY);
//The connectino that will be used to communicate with the database.
adapter.SelectCommand.Connection = conn;
//The SqlCommandBuilder is used by the SqlAdapter to build the respective Insert, Update, Delete statements.
SqlCommandBuilder builder =
new
SqlCommandBuilder(adapter);
//Performs the actual update to the database. Here update means any operation performed by the user, Update, Delete, Insert.
adapter.Update(GridSource);
RadAjaxManager1.Alert(
"The database was successfully updated"
);
}
}
<
ClientEvents
OnRowContextMenu
=
"RowContextMenu"
></
ClientEvents
>
If you followed these steps, you should now have a web application with the look and feel of Microsoft Excel.
Andrey Simeonov is a support officer at one of Telerik's ASP.NET AJAX teams. He joined the company two years ago and since then he’s been providing assistance to Telerik’s customers in different areas, including ORM tools like Entity Framework and Open Access, SQL Server, Web Services, PowerShell, ASP.NET Web Forms and ASP.NET MVC. Other than that he likes to engage in DIY projects and learn new stuff, even new solutions to already solved problems.