In this article I am going to show how we can export
Data table to excel in ASP.NET MVC.
Below is my Data Table in design mode:

Image 1.
Script Of Employee Table:
CREATE TABLE [dbo].[Employee](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Email] [varchar](500) NULL,
[Country] [varchar](50) NULL
) ON [PRIMARY]
GO
Data in Employee Table:

Image 2.
Now Open Visual Studio 2012 -> New -> Project:

Image 3.

Image 4.
Now add a ClosedXML reference:

Image 5.
Now right click on Model folder -> Add New Class. ExportDataTableToExcelModel.cs
using System;
using
System.Collections.Generic;
using System.Linq;
using System.Web;
namespace
ExportDataTableToExcelInMVC4.Models
{
public class ExportDataTableToExcelModel
{
public int Id { get; set; }
public string Name { get; set; }
public string Email { get; set; }
public string Country { get; set; }
}
}
Now Right
click on Controller Folder -> Add -> Controller

Image 6.

Image 7.
Now here in this ExportDataController
add below code:
using System;
using
System.Collections.Generic;
using
System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using
ExportDataTableToExcelInMVC4.Models;
using ClosedXML;
using ClosedXML.Excel;
using System.IO;
namespace
ExportDataTableToExcelInMVC4.Controllers
{
public class ExportDataController : Controller
{
public ActionResult Index()
{
String constring = ConfigurationManager.ConnectionStrings["RConnection"].ConnectionString;
SqlConnection con = new SqlConnection(constring);
string query = "select * From
Employee";
DataTable dt = new DataTable();
con.Open();
SqlDataAdapter da = new SqlDataAdapter(query, con);
da.Fill(dt);
con.Close();
IList<ExportDataTableToExcelModel> model = new List<ExportDataTableToExcelModel>();
for (int i = 0; i < dt.Rows.Count; i++)
{
model.Add(new ExportDataTableToExcelModel()
{
Id = Convert.ToInt32(dt.Rows[i]["Id"]),
Name = dt.Rows[i]["Name"].ToString(),
Email = dt.Rows[i]["Email"].ToString(),
Country = dt.Rows[i]["Country"].ToString(),
});
}
return View(model);
}
public ActionResult ExportData()
{
String constring = ConfigurationManager.ConnectionStrings["RConnection"].ConnectionString;
SqlConnection con = new SqlConnection(constring);
string query = "select * From
Employee";
DataTable dt = new DataTable();
dt.TableName = "Employee";
con.Open();
SqlDataAdapter da = new SqlDataAdapter(query, con);
da.Fill(dt);
con.Close();
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt);
wb.Style.Alignment.Horizontal =
XLAlignmentHorizontalValues.Center;
wb.Style.Font.Bold = true;
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=
EmployeeReport.xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
return RedirectToAction("Index", "ExportData");
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch
{
obj = null;
}
finally
{
GC.Collect();
}
}
}
}
Now right click on Index Folder -> Add View.

Image 8.
View.cshtml
@model IEnumerable<ExportDataTableToExcelInMVC4.Models.ExportDataTableToExcelModel>
@{
ViewBag.Title = "Index";
}
@using (Html.BeginForm("ExportData", "ExportData", FormMethod.Post))
{
<p style="background-color: red; color: white; font-size: 20pt; font-weight: bold; padding: 10px; text-align: center;">
Export DataTable To Excel In MVC 4
</p>
<table style="background-color: white; width: 100%;">
<tr>
<th style="border: 2px solid black; text-align: left; width: 20%; padding-left: 20px;">
@Html.DisplayNameFor(model
=> model.Name)
</th>
<th style="border: 2px solid black; text-align: center; width: 20%">
@Html.DisplayNameFor(model
=> model.Email)
</th>
<th style="border: 2px solid black; text-align: center; width: 20%">
@Html.DisplayNameFor(model
=> model.Country)
</th>
<th></th>
</tr>
@foreach (var item in Model)
{
<tr>
<td style="padding-left: 20px;">
@Html.DisplayFor(modelItem
=> item.Name)
</td>
<td style="padding-left: 20px;">
@Html.DisplayFor(modelItem
=> item.Email)
</td>
<td style="padding-left: 50px;">
@Html.DisplayFor(modelItem
=> item.Country)
</td>
</tr>
}
<tr>
<td></td>
<td></td>
<td>
<input type="submit" name="btnExportLicensing" style="width: 140px;"
value="Export" id="exportLicensing" class="button" /></td>
</tr>
</table>
}
Below is my connection string in Web.config:
<connectionStrings>
<add name="RConnection" connectionString="Server=INDIA\MSSQLServer2k8;database=TestDB;UID=sa;
pwd=india;" />
</connectionStrings>
Now
run your application:

Image 9.

Image 10.

Image 11.