JQuery jqGrid Export to Excel Using ASP.NET MVC Framework
In this article we are going to demonstrate jqGrid, JQuery Grid plugin which is used to display tabular data on the form. The article will also discuss how to export the Grid to the Excel format.
Why JQuery Grid Plugin?
You can always use the plain vanilla HTML code to create your tables but JQuery Grid gives you the ability perform sorting, paging, searching and many other operations which are very cumbersome to write.
Setting Up the Project:
We are using ASP.NET MVC 1.0 for our demo. First, you can download the JQuery library and the JGrid library from the following URLs:
The JGrid documentation explains how and where to put all the .js files. If you find it hard to follow then just download the source code at the end of this article and set up your project similar to the download sample.
Populating Grid with Data:
Before we start retrieving products from the database we should make a reference to all the required scripts in our page. Since, we are using master pages we will include all the script and CSS references in the master page as shown below:
After creating all the references our next task is to populate the Grid with data. We will be using the Northwind database "Products" table. Here is our simple repository which returns all the products from the Products table as a List<Product>.
The loadProducts JavaScript function is responsible for populating the View with the data.
Let's take a look at the loadProducts method.
The above code assigns different values to the jqGrid. The url attribute represents the controller action that will be fired. The colNames represent the header text of the table that will be displayed. The colModel refers to the individual grid columns as an array of properties.
Now, let's see what the controller looks like:
The _productRepository.GetAll() method is responsible for fetching all the products from the Products table. Finally, we created an Anonymous type jsonData which contains the properties required by the jqGrid. The rows collection contains an object with id property and cells collection.
When you run the above example you will see the following output:
The above image shows that Grid is rendered on the page successfully. There are few columns which are not displayed because they were not included in the jqGrid column collection.
The code in the GetProducts action of the HomeController is very nasty. We should move this code to a different place where it can be reused.
Creating ToJsonForjqGrid<T> Extension Method:
The purpose of ToJsonForjqGrid<T> extension method is to convert a List<T> collection into a format supported by the jqGrid.
ToJsonForJqGrid<T> takes two arguments. The first argument is the primary key of the object which will be assigned to the id property of the row. The second argument is the columnNames[] which contains the name of the columns to be included in the jqGrid.
And here is the GetProducts action using the ToJsonForJqGrid<T> method.
And the result is shown below:
If we need to include a couple of more columns we can add the names in the string[] collection for the columnNames parameter to the ToJsonForjqGrid<T> method.
A little better!
And here is the result:
Exporting Grid to Excel:
When the export to excel button is clicked the form is submitted and "ExportToExcel" action is fired.
NOTE: You should not put the export to excel code right into the controller action. Create a ExportToExcel service and use that to perform the exportation task.
Inside the ExportToExcel action we simply retrieve the products from the database and creates an anonymous type collection and assign to the GridView control. Later the GridView control is rendered to the HtmlTextWriter and send as an attachement with the response.
Conclusion:
In this article we learned how to use jqGrid to display data from the database. We also learned how to export the grid to excel.
