How Can Export Data from WebGrid to Excel in MVC

How Can Export Data from WebGrid to Excel in MVC

First Create a Database and Table

The database name is "APCRUD "

Table Name is "Contac"

How Can Export Data from WebGrid to Excel in MVC

How Can Export Data from WebGrid to Excel in MVC

The Second Step is to create a new project in the visual studio in MVC(empty) 

How Can Export Data from WebGrid to Excel in MVC

Third Step is to create a repository pattern with Entity Framework

How Can Export Data from WebGrid to Excel in MVC

1). IBookRepository.cs

using System;

using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MvcRepo.Models.DAL
{
    interface IBookRepository
    {
        IEnumerable<BookTbl> GetBooks();
        BookTbl GetBookByID(int bookId);
        void InsertBook(BookTbl book);
        void DeleteBook(int bookId);
        void UpdateBook(BookTbl book);
        void Save();
    }
}

2). BookRepository.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
namespace MvcRepo.Models.DAL
{
    public class BookRepository : IBookRepository
    {
        private DataContext _context;
        public BookRepository(DataContext context)
        {
            _context = context;
        }
        public IEnumerable<BookTbl> GetBooks()
        {
            return _context.BookTbls.ToList();
        }
        public BookTbl GetBookByID(int bookId)
        {             return _context.BookTbls.Find(bookId);
        }
        public void InsertBook(BookTbl book)
        {
            _context.BookTbls.Add(book);
        }
        public void DeleteBook(int bookId)
        {
            BookTbl book = _context.BookTbls.Find(bookId);
            _context.BookTbls.Remove(book);
        }
        public void UpdateBook(BookTbl book)
        {
            _context.Entry(book).State = EntityState.Modified;
        }
        public void Save()
        {
            _context.SaveChanges();
        }
    }
}

3). Add a new class file "Print" in the model folder

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Web;
namespace MvcRepo.Models
{
    public class Print
    {
        public static Dictionary<string, string> Table = new Dictionary<string, string>();
        public static DataTable ToDataTable<T>(List<T> items, Dictionary<string, string> CustomeColumn)
        {
            DataTable dataTable = new DataTable(typeof(T).Name);
            ////Get all the properties
            PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo prop in Props)
            {
                foreach (KeyValuePair<string, string> cust in CustomeColumn)
                {
                    if (cust.Key == prop.Name)
                        dataTable.Columns.Add(cust.Value);
                }
            }
            foreach (T item in items)
            {
                var values = new object[dataTable.Columns.Count];
                for (int i = 0; i < Props.Length; i++)
                {
                    foreach (KeyValuePair<string, string> cust in CustomeColumn)
                  {
                      if (cust.Key == Props[i].Name)
                      {
                          int index = dataTable.Columns[cust.Value].Ordinal;
                          values[index] = Props[i].GetValue(item, null);
                      }
                   }
                }
                dataTable.Rows.Add(values);
            }
           //put a breakpoint here and check datatable
           return dataTable;
        }
    }
}

4). Add a cs file HomeController.cs in Controller folder

using MvcRepo.Models;

using MvcRepo.Models.DAL;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Helpers;
using System.Web.Mvc;

namespace MvcRepo.Controllers
{
    public class HomeController : Controller
    {
        private IBookRepository interfaceobj;
        public HomeController()
        {
            interfaceobj = new BookRepository(new DataContext());
        }
        public ActionResult Index()
        {
            var data = from m in interfaceobj.GetBooks() select m;
            return View(data);
        }
        public ActionResult PrintReport()
        {   Export();
            return RedirectToAction(string.Format("/Images/Temp/{0}.xls", "demo"));
        }
        public void Export()
        {
            DataContext db = new DataContext();
            var data = db.BookTbls.ToList();
            //Print.Table);
            WebGrid grid = new WebGrid(source: data, canPage: false, canSort: false);
            List<WebGridColumn> columns = new List<WebGridColumn>();
            foreach (KeyValuePair<string, string> item in Print.Table)
            {
                columns.Add(new WebGridColumn() { ColumnName = item.Key, Header = item.Value });
            }             string gridData = grid.GetHtml(
            columns: columns).ToString();
            Response.ClearContent();
            //give name to excel sheet.
            Response.AddHeader("content-disposition", "attachment; filename=UserData.xls");
            //specify content type
            Response.ContentType = "application/excel";

            //write excel data using this method
            Response.Write(gridData);
            if (!Directory.Exists(Server.MapPath("~/Images/Temp")))
                Directory.CreateDirectory(Server.MapPath("~/Images/Temp"));
            string pdfPath = Server.MapPath(string.Format("~/Images/Temp/{0}.xls", "demo"));
            StreamWriter wr = new StreamWriter(pdfPath);
            wr.Write(Response.OutputStream);
            wr.Close();
            Response.End();
        }
    }
}

5). Add an "Index" file in the View folder

@model IEnumerable<MvcRepo.Models.BookTbl>
@{  Layout = null; }
<script src="~/Scripts/jquery-1.8.2.min.js"></script>
@using MvcRepo.Models;
@using MvcRepo.Models;
@{
    WebGrid grid = new WebGrid(Model, canPage: true, rowsPerPage: 5, selectionFieldName: "selectedRow", ajaxUpdateContainerId: "gridContent");
    grid.Pager(WebGridPagerModes.All);
    List<WebGridColumn> listColumn = new[]{
        grid.Column("ID", "ID"),
        grid.Column("BookName", "BookName"),
        grid.Column("BookAuthor", "BookAuthor") ,
        grid.Column(format: (item) => @Html.ActionLink("Details","home", new {@id = @item.ID}))}.ToList();
}
@grid.GetHtml(tableStyle: "webGrid", headerStyle: "header", alternatingRowStyle: "alt", selectedRowStyle: "select", columns: listColumn)
@Html.ActionLink("Print", "PrintReport", "Home", new { @class = "ExportToExcel" })
@{
    Print.Table.Clear();
    foreach (var item in listColumn)
    {
        if (!string.IsNullOrEmpty(item.ColumnName) && !string.IsNullOrEmpty(item.Header))
        {
            Print.Table.Add(item.ColumnName, item.Header);
        }
    }

How Can Export Data from WebGrid to Excel in MVC

Thank You...

Last updated:4/19/2020 7:16:02 AM
Sanat Shukla

Sanat Shukla

इश्वर सत्य है | सत्य ही शिव है | शिव ही सुन्दर है | सत्यम शिवम सुन्दरम ||

0 Comments

Leave Comment