Friday, 14 November 2014

MVC Export Data to Excel using C# || Export Data from Database Table to Excel File in MVC ASP.NET

Hi friends,in this article I will explain about how to Export Data to Excel using MVC || Export Data from Database Table to Excel File in MVC ASP.NET.
Let us start with an example user details. Add User_id, User name, Gender, Country to the database then we can export it to the excel sheet.
Model(MVCExportToExcelModel.cs):
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace MVCExportToExcel.Models
{
    public class MVCExportToExcelModel
    {
        public int USER_ID { get; set; }
        public string Username { get; set; }
        public string Gender { get; set; }
        public string Country { get; set; }
    }
}

Controller(ExportToExcelController.cs):
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using MVCExportToExcel.Models;
using System.Web.UI;
using System.IO;
using System.Web.UI.WebControls;


namespace MVCExportToExcel.Controllers
{
    public class ExportToExcelController : Controller
    {

        public ActionResult Index()
        {

            string constring = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
            SqlConnection con = new SqlConnection(constring);
            string query = "SELECT * FROM User_Details ORDER BY USER_ID";
            DataTable dt = new DataTable();
            con.Open();
            SqlDataAdapter da = new SqlDataAdapter(query, con);
            da.Fill(dt);
            con.Close();
            IList<MVCExportToExcelModel> model = new List<MVCExportToExcelModel>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                model.Add(new MVCExportToExcelModel()
                {
                    USER_ID = Convert.ToInt32(dt.Rows[i]["USER_ID"]),
                    Username = dt.Rows[i]["Username"].ToString(),
                    Gender = dt.Rows[i]["Gender"].ToString(),
                    Country = dt.Rows[i]["Country"].ToString(),
                });
            }
            Session["model"] = model;
            return View(model);
        }

        public ActionResult ExportToExcel()
        {
            GridView gv = new GridView();
            gv.DataSource = Session["model"];
            gv.DataBind();
            Response.ClearContent();
            Response.Buffer = true;
            string strDateFormat = string.Empty;
            strDateFormat = string.Format("{0:yyyy-MMM-dd-hh-mm-ss}", DateTime.Now);
            Response.AddHeader("content-disposition", "attachment; filename=UserDetails_" + strDateFormat + ".xls");
            Response.ContentType = "application/ms-excel";
            Response.Charset = "";
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            gv.RenderControl(htw);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
            return RedirectToAction("Index", "ExportToExcel");
        }
    }
}


View(Index.cshtml):
@model IEnumerable<MVCExportToExcel.Models.MVCExportToExcelModel>
@{
    ViewBag.Title = "Export To Excel";
}
<h2>
    Index</h2>
<script src="../../Scripts/jquery-1.7.1.min.js" type="text/javascript"></script>
<script type="text/javascript" language="javascript">
    $(document).ready(function () {
        $('#btnExportToExcel').click(function () {
            window.location = '/ExportToExcel/ExportToExcel';
        });
    });
</script>
<style type="text/css">
    table
    {
        border-collapse: collapse;
    }
    table, th, td
    {
        border: 1px solid black;
        font-family: Tahoma;
    }
    th
    {
        font-weight: bold;
        font-size: small;
        background-color: Maroon;
        color: White;
    }
</style><p>
<input type="submit" value="Export To Excel" id="btnExportToExcel" />
</p>
<table>
    <tr>
        <th>
            User ID
        </th>
        <th>
            User Name
        </th>
        <th>
            Gender
        </th>
        <th>
            Country
        </th>
    </tr>
    @foreach (var item in Model)
    {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.USER_ID)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Username)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Gender)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Country)
            </td>
        </tr>
    }
</table>


The output of the above program as shown in the below figure.


When we click on Export To Excel button then excel file will be downloaded.
  

You can download the code by clicking on the below Download image. 

No comments:

Post a Comment

© 2012-2014 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.