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,
[Name] [varchar](50) NULL,
[Email] [varchar](500) NULL,
[Country] [varchar](50) 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