Sometime, it is required to export your data into the Excel file in web application. So, today I am going to show you how can we export data into excel file from Asp.Net MVC application. In this demonstration, I will show you the data on the application and on the button click, we will export the data into the excel file and download it on our local system.

Create Asp.Net MVC Project

To create new Asp.Net MVC application, Open Visual Studio 2015, move to File menu and choose New < Project;

It will open “New Project” dialog window, so, go to Visual C# < Web and then from the right pane, just choose Asp.Net Web Application. Provide the name of the application “ExportExcelDemo” and click to Ok.

 

It will give you another dialog window where you can choose the type of the asp.net project [Web Forms, MVC, Web APIs etc]. So, you need to choose MVC and click to OK.

 

 

Add Entity Classes

It will create “ExportExcelDemo” Asp.Net MVC application for you. So, we are going to get the data from database. Add some entity classes and database access class. To add the new Model class, Right Click on the Models folder and choose Add and then choose Class, It will give you the dialog window where you can provide the name of the class. 

 

 

So, I am going to add following entity classes and database context classes. 

Employee.cs

using System;
using System.ComponentModel.DataAnnotations;

namespace ExportExcelDemo.Models
{
    public class Employee
    {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
        public int Age { get; set; }
        public string Address { get; set; }
        public int DepartmentId { get; set; }
        
    }
}

 

Department.cs

using System.ComponentModel.DataAnnotations;

namespace ExportExcelDemo.Models
{
    public class Department
    {
        [Key]
        public int DepartmentId { get; set; }

        public string DepartmentName { get; set; }
    }
}

 

DbAccessContext.cs

Here in this demo, I am using the Code First approach for getting the data. 

using System.Data.Entity;

namespace ExportExcelDemo.Models
{
    public class DbAccessContext : DbContext
    {
        public DbAccessContext():base("DefaultConnection")
        {
        }
        public DbSet<Employee> Employees {get; set;}

        public DbSet<Department> Departments { get; set; }
    }
}

 

EmployeeViewModel.cs

namespace ExportExcelDemo.Models
{
    public class EmployeeViewModel
    {
        public string Name { get; set; }
        public string Email { get; set; }
        public int Age { get; set; }
        public string Address { get; set; }        
        public string Department { get; set; }
    }
}
 
Add ClosedXml Library

To export the MVC view data into Excel file, I am using the ClosedXml Library. To Add this to your application, Right Click on your project and choose NuGet Manager. Here you need to search as following and click to Install.

 

 

It will take few minutes and It will install the ClosedXml library into your application.

 

 
Add Controller

I am going to add a new controller "EmployeeController", here I will write the code for getting the data from the database using the Code First Approach and on the Button Click, It will export the data into the Excel file.

So, Right Click on the Controllers folder and add new controller "EmployeeController". And made you code changes as following.

 

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 View for showing the data. So, add Index View with a button which will be used to export the data into the 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="4">
                <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>

 

So, everything is completed, now we can run the application. So, to run the project just press F5, it will open in the browser as following.

 

 

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 into the excel file.

 

 

Click on the downloaded DemoExcel.xls file and it will open with your data as following.

 

 
Conclusion:

So, today we learned how to export asp.net mvc application data into the excel file.

I hope this post will help you. Please put your feedback using comment which helps me to improve myself for next post. If you have any doubts please ask your doubts or query in the comment section and If you like this post, please share it with your friends. Thanks