MVC : Ajax Gridview Paging / Search / Export to Excel

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("
");
                if (CurrentPage > 1)
                {
                    int iPreviousPage = CurrentPage - 1;
                    sb.Append("
");
                    sb.Append(" First ");
                    sb.Append("
");
                    sb.Append("
");
                    sb.Append(" Previous ");
                    sb.Append("
");
                }

                for (var page = StartPage; page <= EndPage; page++)
                {
                    if (page != CurrentPage)
                        CssClass = "";

                    sb.Append("
");
                    sb.Append(" " + page.ToString() + " ");
                    sb.Append("
");
                }

                if (CurrentPage < TotalPages)
                {
                    int iNextPage = CurrentPage + 1;
                    sb.Append("
");
                    sb.Append(" Next ");
                    sb.Append("
");
                    sb.Append("
");
                    sb.Append(" Last ");
                    sb.Append("
");
                }
                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.