Export Data In Excel File With ASP.NET MVC

EmployeeController.cs

using ClosedXML.Excel;
using ExportExcelDemo.Models;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace ExportExcelDemo.Controllers
{
    public class EmployeeController : Controller
    {
        public IList<EmployeeViewModel> GetEmployeeList()
        {
            DbAccessContext db = new DbAccessContext();
            var employeeList = (from e in db.Employees
                                join d in db.Departments on e.DepartmentId equals d.DepartmentId
                                select new EmployeeViewModel
                                {
                                    Name = e.Name,
                                    Email = e.Email,
                                    Age = (int)e.Age,
                                    Address = e.Address,
                                    Department = d.DepartmentName
                                }).ToList();
            return employeeList;
        }

        // GET: Employee
        public ActionResult Index()
        {
            return View(this.GetEmployeeList());
        }

        public ActionResult ExportToExcel()
        {
            var gv = new GridView();
            gv.DataSource = this.GetEmployeeList();
            gv.DataBind();
            
            Response.ClearContent();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment; filename=DemoExcel.xls");
            Response.ContentType = "application/ms-excel";
            Response.Charset = "";

            StringWriter objStringWriter = new StringWriter();
            HtmlTextWriter objHtmlTextWriter = new HtmlTextWriter(objStringWriter);
            gv.RenderControl(objHtmlTextWriter);

            Response.Output.Write(objStringWriter.ToString());
            Response.Flush();
            Response.End();

            return View("Index");
        }
    }
}

Add View

Now, it is time to add a view to show the data. Therefore, add an Index View with a button, which will be used to export the data to an Excel file.

Index. cshtml

@model IList<ExportExcelDemo.Models.EmployeeViewModel>

@{
    ViewBag.Title = "Index";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

@using (Html.BeginForm("ExportToExcel", "Employee", FormMethod.Post))
{
    <br />
    <br />
    <h2>
        Export Data To Excel
    </h2>
    <table style="background-color: white; width: 100%;">
        <tr>
            <th style="border: 1px solid black; text-align: left; width: 20%; padding-left: 20px;">
                Name
            </th>
            <th style="border: 2px solid black; text-align: center; width: 20%">
                Email
            </th>
            <th style="border: 2px solid black; text-align: center; width: 20%">
                Age
            </th>
            <th style="border: 2px solid black; text-align: center; width: 20%">
                Address
            </th>
            <th style="border: 2px solid black; text-align: center; width: 20%">
                Department
            </th>
        </tr>
        @foreach (var itm in Model)
        {
            <tr>
                <td style="padding-left: 20px;">
                    @Html.DisplayFor(m => itm.Name)
                </td>
                <td style="padding-left: 20px;">
                    @Html.DisplayFor(m => itm.Email)
                </td>
                <td style="padding-left: 20px;">
                    @Html.DisplayFor(m => itm.Age)
                </td>
                <td style="padding-left: 50px;">
                    @Html.DisplayFor(m => itm.Address)
                </td>
                <td style="padding-left: 50px;">
                    @Html.DisplayFor(m => itm.Department)
                </td>
            </tr>
        }
        <tr>
            <td colspan="5">
                <br />
                <br />
                <input type="submit" value="Export to Excel" class="button" />
            </td>
        </tr>
    </table>
}

Web.Config

<connectionStrings>
    <add 
        name="DefaultConnection" 
        connectionString="Data Source=my-computer;Initial Catalog=TestEmployee;Integrated Security=True;user id=mukesh;password=mukesh;" 
        providerName="System.Data.SqlClient" />
</connectionStrings>

Thus, everything is complete. Now, we can run the Application. Hence, to run the project, just press F5. It will open in the Browser, shown below.

Here, you can see all the data, which is coming from the database. When you click on the button "Export to Excel". It will export your data to an Excel file.

Click the downloaded DemoExcel.xls file and it will open with your data, shown below.

Thanks for reading this article. Hope, you enjoyed it.