Abstract:
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:
1) JQuery
2) JGrid
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:
<link href="../../Content/Site.css" rel="stylesheet" type="text/css" />
<link href="../../Content/ui.jqgrid.css" rel="stylesheet" type="text/css" />
<script src="../Scripts/jquery-1.3.2.js" type="text/javascript"></script>
<script src="../Scripts/jquery.jqGrid.js" type="text/javascript"></script>
<script src="../../Scripts/Site.js" type="text/javascript"></script>
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
.
public class ProductRepository : IProductRepository
{
public List<Product> GetAll()
{
using(var db = new NorthwindDataContext())
{
return (from p in db.Products
select p).ToList();
}
}
}
The loadProducts JavaScript function is responsible for populating the View with the data.
<script language="javascript" type="text/javascript">
// load the products
loadProducts();
</script>
<% using (Html.BeginForm(new { Action = "ExportToExcel" }))
{%>
<table id="list"></table>
<input type="submit" value="Export to Excel" />
<%
}%>
Let's take a look at the loadProducts method.
function loadProducts()
{
jQuery(document).ready(function() {
jQuery("#list").jqGrid({
url: '/Home/GetProducts/',
datatype: 'json',
mtype: 'GET',
colNames: ['Id', 'Name', 'QuantityPerUnit',"UnitPrice"],
colModel: [
{ name: 'Id', index: 'Id', width: 40, align: 'left' },
{ name: 'Name', index: 'Name', width: 40, align: 'left' },
{ name: 'QuantityPerUnit', index: 'QuantityPerUnit', width: 200, align: 'left' },
{ name: 'UnitPrice', index: 'UnitPrice', width: 200, align: 'left' }],
rowNum: 10,
rowList: [5, 10, 20, 50],
sortname: 'Id',
sortorder: "desc",
viewrecords: true,
caption: 'My first grid'
});
});
}
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:
public ActionResult GetProducts(string sidx, string sord, int page, int rows)
{
var products = _productRepository.GetAll();
var totalPages = 1; // we'll implement later
var totalRecords = 3; // implement later
var jsonData = new
{
total = totalPages,
page = page,
records = totalRecords,
rows = (from p in products
select new
{
id = p.ProductID,
cell = new string[]
{
p.ProductID.ToString(), p.ProductName,
p.ProductName
}
}).ToArray()
};
return Json(jsonData);
}
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
Extension Method:
The purpose of ToJsonForjqGrid extension method is to convert a List collection into a format supported by the jqGrid.
public static object ToJsonForjqGrid<T>(this List<T> list,string primaryKey,string[] columnNames) where T : new()
{
if(list.Count() == 0)
throw new ArgumentException("List does not contain any items!");
var jsonData = new
{
rows = (from p in list
select new
{
id = p.GetPropertyValue(primaryKey),
cell = p.GetPropertyValues(columnNames)
}).ToArray()
};
return jsonData;
}
ToJsonForJqGrid 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 method.
public ActionResult GetProducts(string sidx, string sord, int page, int rows)
{
var jsonData = _productRepository.GetAll().ToJsonForjqGrid("ProductID", new[] { "ProductID", "ProductName" });
return Json(jsonData);
}
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 method.
public ActionResult GetProducts(string sidx, string sord, int page, int rows)
{
var jsonData = _productRepository.GetAll().ToJsonForjqGrid("ProductID", new[] { "ProductID", "ProductName", "QuantityPerUnit","UnitPrice" });
return Json(jsonData);
}
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.
public ActionResult ExportToExcel()
{
var products = _productRepository.GetAll();
var grid = new GridView();
grid.DataSource = from p in products
select new
{
ProductName = p.ProductName,
SomeProductId = p.ProductID
};
grid.DataBind();
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls");
Response.ContentType = "application/excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
grid.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
return View("Index");
}
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.
References:
1) Using JQuery Grid with ASP.NET MVC
[Download Sample]
中文問題可參考此篇文章：http://ccdccs0240.blogspot.com/2010/01/asp.html
轉自：http://www.highoncoding.com/Articles/566_JQuery_jqGrid_Export_to_Excel_Using_ASP_NET_MVC_Framework.aspx
