Export Data In Excel Sheet In ASP.NET MVC

In this article, we will learn how we export a data sheet in Excel using ASP.NET MVC. In this demo, we will use Epplus library that is used to export an Excel using the Open Office XML format. We will be using MongoDB here.

In this article, we will learn how to export a data sheet in Excel using ASP.NET MVC. In this demo, we will use Epplus library that is used to export an Excel using the Open Office XML format. We will be using MongoDB here./div>

Steps required to export data in an Excel

Step 1

Open Visual Studio and create a new project. Name it as ExportExcel.
 
Export Excel In ASP.NET MVC 

Choose the Template type as MVC.

Export Excel In ASP.NET MVC 

Step 2

Add MongoDB Drivers for C# using NuGet Package Manager.

Export Excel In ASP.NET MVC 

Add the required namespaces for MongoDB.

  1. using MongoDB.Driver;        
  2. using MongoDB.Bson;       

Step 3

Now, add a connection string in the web.config file and add the following line in the App Settings section of that file.

  1. <add key="connectionString" value="mongodb://localhost"/>  

Step 4

Add a class in Models folder and name it EmployeeDetails.

  1.         [BsonRepresentation(BsonType.ObjectId)]  
  2.         public String Id { getset; }  
  3.         public string Name { getset; }  
  4.         public string Department { getset; }  
  5.         public string Address { getset; }  
  6.         public string City { getset; }  
  7.         public string Country { getset; }  

Step 5

Click on the Controllers folder and add a new empty controller. Name it as Student Controller.

Step 6

Add EPPlus library from NuGet Package Manager.
 
Export Excel In ASP.NET MVC 

And, add the required namespace.

  1. using OfficeOpenXml;  

Now, add an actionmethod and name it as list. In this, add the following code.

  1. public ActionResult List()  
  2.         {  
  3.             string constr = ConfigurationManager.AppSettings["connectionString"];  
  4.             var Client = new MongoClient(constr);  
  5.             var db = Client.GetDatabase("Employee");  
  6.   
  7.             var collection = db.GetCollection<EmployeeDetails>("EmployeeDetails").Find(new BsonDocument()).ToList();  
  8.   
  9.             return View(collection);  
  10.         }  

Right-click on the Method and add a View.

Export Excel In ASP.NET MVC

Add a link button in the View and name it DownloadExcel.

  1. <div>  
  2.     <a href="@Url.Action("DownloadExcel","Student")">Download Excel</a>  
  3. </div>  

Complete View

  1. @model IEnumerable<ExportExcel.Models.EmployeeDetails>  
  2.   
  3. @{  
  4.     ViewBag.Title = "List";  
  5. }  
  6.   

  7. <table class="table table-striped table-bordered">  
  8.     <tr>  
  9.         <th>  
  10.             @Html.DisplayNameFor(model => model.Name)  
  11.         </th>  
  12.         <th>  
  13.             @Html.DisplayNameFor(model => model.Department)  
  14.         </th>  
  15.         <th>  
  16.             @Html.DisplayNameFor(model => model.Address)  
  17.         </th>  
  18.         <th>  
  19.             @Html.DisplayNameFor(model => model.City)  
  20.         </th>  
  21.         <th>  
  22.             @Html.DisplayNameFor(model => model.Country)  
  23.         </th>  
  24.          
  25.     </tr>  
  26.   
  27. @foreach (var item in Model) {  
  28.     <tr>  
  29.         <td>  
  30.             @Html.DisplayFor(modelItem => item.Name)  
  31.         </td>  
  32.         <td>  
  33.             @Html.DisplayFor(modelItem => item.Department)  
  34.         </td>  
  35.         <td>  
  36.             @Html.DisplayFor(modelItem => item.Address)  
  37.         </td>  
  38.         <td>  
  39.             @Html.DisplayFor(modelItem => item.City)  
  40.         </td>  
  41.         <td>  
  42.             @Html.DisplayFor(modelItem => item.Country)  
  43.         </td>  
  44.          
  45.     </tr>  
  46. }  
  47.   
  48. </table>  
  49.   
  50. <div>  
  51.     <a href="@Url.Action("DownloadExcel","Student")">Download Excel</a>  
  52. </div>  

Now, create a Method in Controller and add the following code to Export the data in Excel.

  1. public void DownloadExcel()  
  2.        {  
  3.            string constr = ConfigurationManager.AppSettings["connectionString"];  
  4.            var Client = new MongoClient(constr);  
  5.            var db = Client.GetDatabase("Employee");  
  6.   
  7.            var collection = db.GetCollection<EmployeeDetails>("EmployeeDetails").Find(new BsonDocument()).ToList();  
  8.              
  9.   
  10.            ExcelPackage Ep = new ExcelPackage();  
  11.            ExcelWorksheet Sheet = Ep.Workbook.Worksheets.Add("Report");  
  12.            Sheet.Cells["A1"].Value = "Name";  
  13.            Sheet.Cells["B1"].Value = "Department";  
  14.            Sheet.Cells["C1"].Value = "Address";  
  15.            Sheet.Cells["D1"].Value = "City";  
  16.            Sheet.Cells["E1"].Value = "Country";  
  17.            int row = 2;  
  18.            foreach (var item in collection)  
  19.            {  
  20.                  
  21.                Sheet.Cells[string.Format("A{0}", row)].Value = item.Name;  
  22.                Sheet.Cells[string.Format("B{0}", row)].Value = item.Department;  
  23.                Sheet.Cells[string.Format("C{0}", row)].Value = item.Address;  
  24.                Sheet.Cells[string.Format("D{0}", row)].Value = item.City;  
  25.                Sheet.Cells[string.Format("E{0}", row)].Value = item.Country;  
  26.                row++;  
  27.            }  
  28.              
  29.   
  30.            Sheet.Cells["A:AZ"].AutoFitColumns();  
  31.            Response.Clear();  
  32.            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";  
  33.            Response.AddHeader("content-disposition""attachment: filename=" + "Report.xlsx");  
  34.            Response.BinaryWrite(Ep.GetAsByteArray());  
  35.            Response.End();  
  36.        }  

Complete Controller code 

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.Mvc;  
  6. using MongoDB.Driver;  
  7. using MongoDB.Bson;  
  8. using System.Configuration;  
  9. using ExportExcel.Models;  
  10. using OfficeOpenXml;  
  11. using System.Drawing;  
  12.   
  13. namespace ExportExcel.Controllers  
  14. {  
  15.     public class StudentController : Controller  
  16.     {  
  17.       
  18.          
  19.         public ActionResult List()  
  20.         {  
  21.             string constr = ConfigurationManager.AppSettings["connectionString"];  
  22.             var Client = new MongoClient(constr);  
  23.             var db = Client.GetDatabase("Employee");  
  24.   
  25.             var collection = db.GetCollection<EmployeeDetails>("EmployeeDetails").Find(new BsonDocument()).ToList();  
  26.   
  27.             return View(collection);  
  28.         }  
  29.         public void DownloadExcel()  
  30.         {  
  31.             string constr = ConfigurationManager.AppSettings["connectionString"];  
  32.             var Client = new MongoClient(constr);  
  33.             var db = Client.GetDatabase("Employee");  
  34.   
  35.             var collection = db.GetCollection<EmployeeDetails>("EmployeeDetails").Find(new BsonDocument()).ToList();  
  36.               
  37.   
  38.             ExcelPackage Ep = new ExcelPackage();  
  39.             ExcelWorksheet Sheet = Ep.Workbook.Worksheets.Add("Report");  
  40.             Sheet.Cells["A1"].Value = "Name";  
  41.             Sheet.Cells["B1"].Value = "Department";  
  42.             Sheet.Cells["C1"].Value = "Address";  
  43.             Sheet.Cells["D1"].Value = "City";  
  44.             Sheet.Cells["E1"].Value = "Country";  
  45.             int row = 2;  
  46.             foreach (var item in collection)  
  47.             {  
  48.                   
  49.                 Sheet.Cells[string.Format("A{0}", row)].Value = item.Name;  
  50.                 Sheet.Cells[string.Format("B{0}", row)].Value = item.Department;  
  51.                 Sheet.Cells[string.Format("C{0}", row)].Value = item.Address;  
  52.                 Sheet.Cells[string.Format("D{0}", row)].Value = item.City;  
  53.                 Sheet.Cells[string.Format("E{0}", row)].Value = item.Country;  
  54.                 row++;  
  55.             }  
  56.               
  57.   
  58.             Sheet.Cells["A:AZ"].AutoFitColumns();  
  59.             Response.Clear();  
  60.             Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";  
  61.             Response.AddHeader("content-disposition""attachment: filename=" + "Report.xlsx");  
  62.             Response.BinaryWrite(Ep.GetAsByteArray());  
  63.             Response.End();  
  64.         }  
  65.     }  
  66. }  

Now, run the project and click on the "Download Excel" button.

Export Excel In ASP.NET MVC 

The data is downloaded in Excel format.

Export Excel In ASP.NET MVC 

Summary

In this article, we learned how to export the data in Excel using ASP.NET MVC.