Export Data Table To Excel in ASP.Net MVC 4

This article shows how to export a Data Table to Excel in ASP.NET MVC.

The following is my Data Table in design mode.

Image 1. Design Mode

Script Of Employee Table

CREATE TABLE [dbo].[Employee] (
    [ID] [int] IDENTITY(1,1) NOT NULL,
      NULL,
      NULL,
      NULL
) ON [PRIMARY]
GO

Data in Employee Table

Image 2. Employee Table

Now open Visual Studio 2012, then select New -> Project.

Image 3. New Project

Image 4. New ASP.Net MVC

Now add a ClosedXML reference.

Image 5. Export Data Table

Now right-click on the Model folder, then select 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 the Controller Folder, then select Add -> Controller.

Image 6. Controller

Image 7. Add Controller

Now here in ExportDataController, add the following 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 the Index Folder, then select Add View.

Image 8. Add View

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>
}

The following 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. Excel In MVC4

Image 10. Export DataTable to Excel

Image 11. Employee Report