How to Easily Create an ASP.NET Excel-like Grid by Extending Telerik’s RadGrid

Why do you need Excel-like Grid in ASP.NET?

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:

• A Web application that introduces Excel functionality is more convenient for the end user because it allows remote access to the data.
• It allows the user to customize the look and feel of the application by changing its skins.
• It allows you to control how many items and columns will be shown in your ASP.NET Excel grid control.
• It allows you to use different editor controls, such as ComboBox, DatePicker, CheckBox and TextBox controls.
• It allows you to implement circular navigation, e.g. when you are in the left most column and you press the left arrow key, you will be taken to the right most column of the row above.

Will my Grid ever be exactly like Excel?

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:

• Excel is a stand-alone application and thus it’s independent from the web browser; web Grids on other hand are limited by the browser implementation and performance.
• Excel supports over a million rows and sixteen thousand columns; however, none of the modern browsers will be able to handle that amount of cells.
• Excel allows formulas to be entered but in a web environment you will need to put a lot of effort to iterate through the cells and persist their values.

The easiest way to achieve Excel-like functionality in your ASP.NET project

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:

1. Add RadGrid to the page.
2. Bind the Grid using the NeedDataSource event to DataTable or other offline object. This will help you to persist the values that the users enter but will not connect each time to the database when changes are made.
3. Put all the items of RadGrid in edit mode.
4. Once the editor controls are initialized modify their settings. These include:
   1. onclick handler for taking the focus of the clicked cell.
      control.Attributes.Add("onclick", "cellClick('" + control.ClientID + "');");
   2. ondblclick handler for bringing the editor control’s advanced functionality.
      //Adds the click and dblclick event handlers to the HTML element

      control.Attributes.Add("ondblclick", "cellDoubleClickFunction('" + control.ClientID + "');");
   3. CssClass containing the “row” + itemIndex + “col” + columnIndex. You will use this class to find the next cell when an arrow key is pressed.
      //Assign a unique CSS class to each editor control so we could access it afterwards through JQuery.
      

      control.CssClass = "row" + itemIndex.ToString() + "col" + colIndex.ToString();
   4. RadGrid’s onkeydown client-side event should be hooked in order to catch the user input.
      //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);");
   5. The last thing on the server is to synchronize the DataTable object with the database. Because of the characteristics of the DataTable control you need to use only a few lines of code to sync the changes. The SqlCommandBuilder object will build the expressions for you and will perform the operations.
      //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");

          }

      }
   6. On the client-side you need to present JavaScript handlers for the various events you have hooked on the server-side and the different cases that they should handle.
   7. If you want to have filtering with a ComboBox that has all the distinct values from the column, you could use the approach from this code-library project.
   8. Sorting and filtering could be enabled through the respective properties of RadGrid.
   9. If you want you could add a row-context menu that will be used to insert/delete a row, save the data, sync the changes directly to the database or discard that changes made by the user so far.
      <ClientEvents OnRowContextMenu="RowContextMenu"></ClientEvents>

If you followed these steps, you should now have a web application with the look and feel of Microsoft Excel.

Explore the Excel functionality with RadGrid!

As you see when you have the right tools in your hands, in few days you could easily implement functionality that would otherwise take months. If you haven’t tried RadGrid and the other 70+ RadControls, you can start here.