Sometimes, it is required to export your data into the Excel file in the Web Application. Thus, I am going to show you, how we can export data into Excel files from ASP.NET MVC Applications. In this demonstration, I will show you the data on the Application, and on the button click, we will export the data into an Excel file and download it on our local system.
Create ASP.NET MVC Project
To create a new ASP.NET MVC Application, open Visual Studio 2015, move to the File menu, and choose New < Project.
It will open the “New Project” dialog Window. Thus, 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 OK.
![ASP.NET]()
It will give you another dialog Window, where you can choose the type of ASP.NET project[Web Forms, MVC, Web APIs, etc]. Thus, you need to choose MVC and click OK.
![MVC]()
Add Entity Classes
It will create an “ExportExcelDemo” ASP.NET MVC Application for you. Hence, we are going to get the data from the database. Add some entity classes and database access classes. To add the new Model class, right-click on the Models folder and choose Add. Choose Class and it will give you the dialog Window, where you can provide the name of the class.
![Entity Classes]()
Thus, I am going to add the 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. there, in this demo, I am using the Code First approach to get 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 an 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 follows and click Install.
![ClosedXml]()
It will take a few minutes and it will install the ClosedXml library into your Application.
![Application]()
Add Controller
I am going to add a new controller "EmployeeController". Here, I will write the code to get the data from the database, using the Code First Approach, and on the button click, it will export the data to an Excel file.
Thus, right-click on the Controllers folder and add a new controller "EmployeeController". The code changes are as follows.
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.
![Export Data]()
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.
![Export to Excel]()
Click the downloaded DemoExcel.xls file and it will open with your data, shown below.
![DemoExcel]()
Thanks for reading this article. Hope, you enjoyed it.