Hello friends, Welcome to my blog. In this post I will do an small project on grid view paging, search and export to excel using Ajax and MVC 5. I will start from creating database in SQL Server 2014.
Step 1:
Create table
CustomerDetail and insert large data in it.
CREATE TABLE CustomerDetail
(
ID BIGINT PRIMARY KEY IDENTITY(1,1),
FullName VARCHAR(500),
Age INT,
Gender VARCHAR(500),
CreatedDate DATETIME DEFAULT(GETDATE())
)
--To Fill data in the above created table use this script
DECLARE @i INT, @gender VARCHAR(500)
SET @i=1
WHILE(@i <= 506)
BEGIN
IF(@i % 2 = 0)
SET @gender = 'Female'
ELSE
SET @gender = 'Male'
INSERT INTO CustomerDetail(FullName,Age,Gender)
VALUES ('Full Name '+ CONVERT(VARCHAR(500), @i) , FLOOR(RAND(CHECKSUM(NEWID()))*(18-100)+100), @gender)
SET @i = @i + 1
END
FLOOR(RAND(CHECKSUM(NEWID()))*(18-100)+100)
this code is used to generate random age between 18 and 100.
Step 2:
Create a folder called Data in this folder use ADO.Net Entity Framework and connect with database. You can check my previous post
here.
Now add class "
CustomerDetailRepository" this class is responsible for communicating between UI and database with entity framework
public class CustomerDetailRepository
{
public CustomerDetailRepository() { }
public List GetListOfCustomer(int page, int pageSize, string Search, out int totalRecord, out int totalPage)
{
List objLstCD = new List();
IEnumerable IDCount;
IEnumerable IData;
using (RegistrationDemoEntities dbContext = new RegistrationDemoEntities())
{
try
{
if (!string.IsNullOrEmpty(Search))
{
IDCount = (from cd in dbContext.CustomerDetails
where cd.FullName.Contains(Search) || cd.Gender == Search
select cd.ID);
IData = (from cd in dbContext.CustomerDetails
where cd.FullName.Contains(Search) || cd.Gender == Search
select new CustomerDetailModel
{
ID = cd.ID,
FullName = cd.FullName,
Age = cd.Age,
Gender = cd.Gender,
CreatedDate = cd.CreatedDate
}).OrderBy(x => x.ID).Skip((page - 1) * pageSize).Take(pageSize);
}
else
{
IDCount = (from cd in dbContext.CustomerDetails
select cd.ID);
IData = (from cd in dbContext.CustomerDetails
select new CustomerDetailModel
{
ID = cd.ID,
FullName = cd.FullName,
Age = cd.Age,
Gender = cd.Gender,
CreatedDate = cd.CreatedDate
}).OrderBy(x => x.ID).Skip((page - 1) * pageSize).Take(pageSize);
}
totalRecord = Convert.ToInt32(IDCount.ToList().Count());
totalPage = (totalRecord / pageSize) + ((totalRecord % pageSize) > 0 ? 1 : 0);
foreach (var item in IData)
{
objLstCD.Add(new CustomerDetailModel()
{
ID = item.ID,
FullName = item.FullName,
Age = item.Age,
Gender = item.Gender,
CreatedDate = (DateTime)item.CreatedDate,
});
}
}
catch (Exception Ex) { throw Ex; }
}
return objLstCD;
}
public List ExportToExcelListOfCustomer()
{
List objLstCD = new List();
IEnumerable IData;
using (RegistrationDemoEntities dbContext = new RegistrationDemoEntities())
{
try
{
IData = (from cd in dbContext.CustomerDetails
select new CustomerDetailModel
{
ID = cd.ID,
FullName = cd.FullName,
Age = cd.Age,
Gender = cd.Gender,
CreatedDate = cd.CreatedDate
});
foreach (var item in IData)
{
objLstCD.Add(new CustomerDetailModel()
{
ID = item.ID,
FullName = item.FullName,
Age = item.Age,
Gender = item.Gender,
CreatedDate = item.CreatedDate
});
}
}
catch (Exception Ex) { throw Ex; }
}
return objLstCD;
}
}
Step 3:
In model folder create
CustomerDetailModel class.
public class CustomerDetailModel
{
public long ID { get; set; }
public string FullName { get; set; }
public Nullable Age { get; set; }
public string Gender { get; set; }
public Nullable CreatedDate { get; set; }
}
Step 4:
In Data folder create
PagerRespository class. This class is use to generate paging for the grid.
public class PagerRespository
{
#region "Properties"
public int TotalItems { get; private set; }
public int CurrentPage { get; private set; }
public int PageSize { get; private set; }
public int TotalPages { get; private set; }
public int StartPage { get; private set; }
public int EndPage { get; private set; }
public string PagerHtmlCode { get; set; }
#endregion
public PagerRespository(int totalItems, int? page, int pageSize)
{
// calculate total, start and end pages
var totalPages = (int)Math.Ceiling((decimal)totalItems / (decimal)pageSize);
var currentPage = page != null ? (int)page : 1;
var startPage = currentPage - 5;
var endPage = currentPage + 4;
if (startPage <= 0)
{
endPage -= (startPage - 1);
startPage = 1;
}
if (endPage > totalPages)
{
endPage = totalPages;
if (endPage > 10)
{
startPage = endPage - 9;
}
}
TotalItems = totalItems;
CurrentPage = currentPage;
PageSize = pageSize;
TotalPages = totalPages;
StartPage = startPage;
EndPage = endPage;
PagerHtmlCode = GeneratePagerHtmlCode();
}
public string GeneratePagerHtmlCode()
{
string CssClass = "active";
StringBuilder sb = new StringBuilder();
if (EndPage > 1)
{
sb.Append("");
}
return sb.ToString();
}
}
Step 5:
Create ViewModel folder and add
CustomerDetailVM class.
public class CustomerDetailVM
{
public IEnumerable CustomerDetailList { get; set; }
public PagerRespository Pager { get; set; }
}
Step 6:
Create
CustomerDetailController class in your Controllers folder.
using System;
using System.Web.Mvc;
using RegistrationDemoApp.Data;
using RegistrationDemoApp.ViewModel;
using RegistrationDemoApp.Models;
using System.Collections.Generic;
using System.Data;
namespace RegistrationDemoApp.Controllers
{
public class CustomerDetailController : Controller
{
CustomerDetailRepository objCR = null;
public ActionResult Index()
{
return View();
}
[HttpPost]
public JsonResult Index(string pageNumber, string pageSizeVal, string Search)
{
int totalPage = 0;
int totalRecord = 0;
int pageSize = Convert.ToInt32(pageSizeVal);
int page = string.IsNullOrEmpty(pageNumber) ? 1 : Convert.ToInt32(pageNumber);
objCR = new CustomerDetailRepository();
var CDLst = objCR.GetListOfCustomer(page, pageSize, Search, out totalRecord, out totalPage);
var pagerData = new PagerRespository(totalRecord, page, pageSize);
var objCustomerDetailVM = new CustomerDetailVM
{
CustomerDetailList = CDLst,
Pager = pagerData
};
return Json(objCustomerDetailVM, JsonRequestBehavior.AllowGet);
}
public ActionResult ExportDataToExcel()
{
objCR = new CustomerDetailRepository();
List objLstCD = objCR.ExportToExcelListOfCustomer();
DataTable DT = UtilitiesFile.ConvertToDatatable(objLstCD);
string FileName = UtilitiesFile.GetGenerateExcelFile("CustomerDetails", DT);
return RedirectToAction("Index");
}
}
}
Step 7:
Now right click on Index action method and select "Add View". Keep the name as it is and make sure in Template dropdown menu 'Empty (without model)' is selected after that a "Index.cshtml" file will be generated in Views >> CustomerDetail folder. Check the view code mention below.
Gridview Paging / Search / Export to Excel
@Html.DropDownList("DDL_PageSize", new SelectList(new Dictionary { { "10", 10 }, { "25", 25 }, { "50", 50 }, { "100", 100 } }, "Key", "Value", "10"), new { @class = "form-control-static", id = "DDL_PageSize" })
|
ID
|
Full Name
|
Age
|
Gender
|
Created Date
|
Step 8:
Now add jquery code in you view, if you wish you can add it in separate js file and link to view.
Step 9:
I have used
EPPlus from codeplex to generate excel sheet. You can find it from
here. Download the files & right click on solution to add reference of EPPlus to your project.
Step 10:
Create "
UtilitiesFile.cs" class in data folder. This file will generate and download excel file. Check the code below.
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
namespace RegistrationDemoApp.Data
{
public class UtilitiesFile
{
public UtilitiesFile() { }
#region "Generate Excel File using EPP class"
public static string GetGenerateExcelFile(string strAppendText, DataTable DT)
{
string FullPath = "", strFileName = "";
try
{
strFileName = strAppendText + DateTime.Now.ToString("ddMMMyyyyHHmmssfff") + ".xlsx";
string FilePath = HttpContext.Current.Server.MapPath("~/DownloadedFiles/");
DirectoryInfo DirInfo = new DirectoryInfo(FilePath);
if (!DirInfo.Exists)
Directory.CreateDirectory(FilePath);
FullPath = FilePath + strFileName;
FileInfo FInfo = new FileInfo(FullPath);
using (ExcelPackage xlPackage = new ExcelPackage(FInfo))
{
ExcelWorksheet workSheet = xlPackage.Workbook.Worksheets.Add(strAppendText);
if (DT.Rows.Count > 0)
{
for (int j = 0; j <= DT.Rows.Count - 1; j++)
{
for (int k = 0; k <= DT.Columns.Count - 1; k++)
{
if (j == 0)
{
workSheet.Cells[1, k + 1].Value = Convert.ToString(DT.Columns[k]); //For Column Header
workSheet.Cells[1, k + 1].Style.Font.Bold = true;
}
workSheet.Cells[2 + j, k + 1].Value = Convert.ToString(DT.Rows[j][k]); //For Column Cell Value
}
}
xlPackage.Save();
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + strFileName);
HttpContext.Current.Response.WriteFile(FullPath);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
}
}
catch (Exception Ex) { throw Ex; }
return strFileName;
}
#endregion
#region "ListToDatatable"
public static DataTable ConvertToDatatable(IEnumerable data)
{
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
foreach (PropertyDescriptor prop in properties)
table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
foreach (T item in data)
{
DataRow row = table.NewRow();
foreach (PropertyDescriptor prop in properties)
{
row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
}
table.Rows.Add(row);
}
return table;
}
#endregion
}
}
Step 11:
Now run the application and check the output.
Thanks for reading, I appreciate your comments & feedback.
