Export Data In Excel File With ASP.NET MVC

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.

ExportExcelDemo

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.

Entity Classes

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

Employee.cs

  1. using System;  
  2. using System.ComponentModel.DataAnnotations;  
  3. namespace ExportExcelDemo.Models  
  4. {  
  5.     public class Employee   
  6.     {  
  7.         [Key]  
  8.         public int Id {  
  9.             get;  
  10.             set;  
  11.         }  
  12.         public string Name {  
  13.             get;  
  14.             set;  
  15.         }  
  16.         public string Email {  
  17.             get;  
  18.             set;  
  19.         }  
  20.         public int Age {  
  21.             get;  
  22.             set;  
  23.         }  
  24.         public string Address {  
  25.             get;  
  26.             set;  
  27.         }  
  28.         public int DepartmentId {  
  29.             get;  
  30.             set;  
  31.         }  
  32.     }  
  33. }  
Department.cs
  1. using System.ComponentModel.DataAnnotations;  
  2. namespace ExportExcelDemo.Models  
  3. {  
  4.     public class Department  
  5.     {  
  6.         [Key]  
  7.         public int DepartmentId {  
  8.             get;  
  9.             set;  
  10.         }  
  11.         public string DepartmentName {  
  12.             get;  
  13.             set;  
  14.         }  
  15.     }  
  16. }  
DbAccessContext.cs

Here, in this demo, I am using the Code First approach to get the data.
  1. using System.Data.Entity;  
  2. namespace ExportExcelDemo.Models  
  3. {  
  4.     public class DbAccessContext: DbContext   
  5.     {  
  6.         public DbAccessContext(): base("DefaultConnection") {}  
  7.         public DbSet < Employee > Employees   
  8.         {  
  9.             get;  
  10.             set;  
  11.         }  
  12.         public DbSet < Department > Departments {  
  13.             get;  
  14.             set;  
  15.         }  
  16.     }  
  17. }  
EmployeeViewModel.cs
  1. namespace ExportExcelDemo.Models   
  2. {  
  3.     public class EmployeeViewModel   
  4.     {  
  5.         public string Name {  
  6.             get;  
  7.             set;  
  8.         }  
  9.         public string Email {  
  10.             get;  
  11.             set;  
  12.         }  
  13.         public int Age {  
  14.             get;  
  15.             set;  
  16.         }  
  17.         public string Address {  
  18.             get;  
  19.             set;  
  20.         }  
  21.         public string Department {  
  22.             get;  
  23.             set;  
  24.         }  
  25.     }  
  26. }  
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.

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

Thus, right click on the Controllers folder and add new controller "EmployeeController". The code changes are as follows:

EmployeeController.cs
  1. using ClosedXML.Excel;  
  2. using ExportExcelDemo.Models;  
  3. using System;  
  4. using System.Collections.Generic;  
  5. using System.IO;  
  6. using System.Linq;  
  7. using System.Web;  
  8. using System.Web.Mvc;  
  9. using System.Web.UI;  
  10. using System.Web.UI.WebControls;  
  11. namespace ExportExcelDemo.Controllers   
  12. {  
  13.     public class EmployeeController: Controller   
  14.     {  
  15.         public IList < EmployeeViewModel > GetEmployeeList()   
  16.         {  
  17.                 DbAccessContext db = new DbAccessContext();  
  18.                 var employeeList = (from e in db.Employees join d in db.Departments on e.DepartmentId equals d.DepartmentId select new EmployeeViewModel {  
  19.                     Name = e.Name,  
  20.                         Email = e.Email,  
  21.                         Age = (int) e.Age,  
  22.                         Address = e.Address,  
  23.                         Department = d.DepartmentName  
  24.                 }).ToList();  
  25.                 return employeeList;  
  26.             }  
  27.             // GET: Employee  
  28.         public ActionResult Index() {  
  29.             return View(this.GetEmployeeList());  
  30.         }  
  31.         public ActionResult ExportToExcel() {  
  32.             var gv = new GridView();  
  33.             gv.DataSource = this.GetEmployeeList();  
  34.             gv.DataBind();  
  35.             Response.ClearContent();  
  36.             Response.Buffer = true;  
  37.             Response.AddHeader("content-disposition""attachment; filename=DemoExcel.xls");  
  38.             Response.ContentType = "application/ms-excel";  
  39.             Response.Charset = "";  
  40.             StringWriter objStringWriter = new StringWriter();  
  41.             HtmlTextWriter objHtmlTextWriter = new HtmlTextWriter(objStringWriter);  
  42.             gv.RenderControl(objHtmlTextWriter);  
  43.             Response.Output.Write(objStringWriter.ToString());  
  44.             Response.Flush();  
  45.             Response.End();  
  46.             return View("Index");  
  47.         }  
  48.     }  
  49. }  
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
  1. @model IList  
  2. <ExportExcelDemo.Models.EmployeeViewModel>  
  3. @{  
  4. ViewBag.Title = "Index";  
  5. Layout = "~/Views/Shared/_Layout.cshtml";  
  6. }  
  7.   
  8. @using (Html.BeginForm("ExportToExcel", "Employee", FormMethod.Post))  
  9. {  
  10.   
  11.     <br />  
  12.     <br />  
  13.     <h2>  
  14. Export Data To Excel  
  15. </h2>  
  16.     <table style="background-color: white; width: 100%;">  
  17.         <tr>  
  18.             <th style="border: 1px solid black; text-align: left; width: 20%; padding-left: 20px;">  
  19. Name  
  20. </th>  
  21.             <th style="border: 2px solid black; text-align: center; width: 20%">  
  22. Email  
  23. </th>  
  24.             <th style="border: 2px solid black; text-align: center; width: 20%">  
  25. Age  
  26. </th>  
  27.             <th style="border: 2px solid black; text-align: center; width: 20%">  
  28. Address  
  29. </th>  
  30.             <th style="border: 2px solid black; text-align: center; width: 20%">  
  31. Department  
  32. </th>  
  33.         </tr>  
  34. @foreach (var itm in Model)  
  35. {  
  36.   
  37.         <tr>  
  38.             <td style="padding-left: 20px;">  
  39. @Html.DisplayFor(m => itm.Name)  
  40. </td>  
  41.             <td style="padding-left: 20px;">  
  42. @Html.DisplayFor(m => itm.Email)  
  43. </td>  
  44.             <td style="padding-left: 20px;">  
  45. @Html.DisplayFor(m => itm.Age)  
  46. </td>  
  47.             <td style="padding-left: 50px;">  
  48. @Html.DisplayFor(m => itm.Address)  
  49. </td>  
  50.             <td style="padding-left: 50px;">  
  51. @Html.DisplayFor(m => itm.Department)  
  52. </td>  
  53.         </tr>  
  54. }  
  55.   
  56.         <tr>  
  57.             <td colspan="4">  
  58.                 <br />  
  59.                 <br />  
  60.                 <input type="submit" value="Export to Excel" class="button" />  
  61.             </td>  
  62.         </tr>  
  63.     </table>  
  64. }  
Web.Config
  1. <connectionStrings>  
  2.     <add name="DefaultConnection" connectionString="Data Source=my-computer;Initial Catalog=TestEmployee;Integrated Security=True; user id=mukesh; password=mukesh;" providerName="System.Data.SqlClient" />  
  3. </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:

application
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

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.


Similar Articles