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.
I already explained in the previous articles about MVC Jquery UI accordion expand/collaspe all in ASP.NET, How to Upload and delete Uploaded file in MVC and MVC:JQuery UI Datepicker Calender Control In Asp.Net Mvc Application || How to Use jQuery Calender In MVC3
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