Export Data In Excel File With ASP.NET MVC

Mukesh Kumar
7y
94.5k
0
8

Article

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

It will give you another dialog Window, where you can choose the type of the 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 “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 class. 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.

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.cs

Here, 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 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 Install.

It will take a 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 to get the data from the database, using the Code First Approach and on the button click, it will export the data to Excel file.

Thus, right click on the Controllers folder and add 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 the time to add view to show the data. Therefore, add Index View with a button, which will be used to export the data to 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>

Thus, everything completes. 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 Excel file.

application