Export Data Table To Excel in ASP.NET MVC 4

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.