Export Data In Excel Sheet In ASP.NET MVC

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

public void DownloadExcel()  
{  
    string constr = ConfigurationManager.AppSettings["connectionString"];  
    var Client = new MongoClient(constr);  
    var db = Client.GetDatabase("Employee");  
    var collection = db.GetCollection<EmployeeDetails>("EmployeeDetails").Find(new BsonDocument()).ToList();  
    ExcelPackage Ep = new ExcelPackage();  
    ExcelWorksheet Sheet = Ep.Workbook.Worksheets.Add("Report");  
    Sheet.Cells["A1"].Value = "Name";  
    Sheet.Cells["B1"].Value = "Department";  
    Sheet.Cells["C1"].Value = "Address";  
    Sheet.Cells["D1"].Value = "City";  
    Sheet.Cells["E1"].Value = "Country";  
    int row = 2;  
    foreach (var item in collection)  
    {  
          
        Sheet.Cells[string.Format("A{0}", row)].Value = item.Name;  
        Sheet.Cells[string.Format("B{0}", row)].Value = item.Department;  
        Sheet.Cells[string.Format("C{0}", row)].Value = item.Address;  
        Sheet.Cells[string.Format("D{0}", row)].Value = item.City;  
        Sheet.Cells[string.Format("E{0}", row)].Value = item.Country;  
        row++;  
    }
    Sheet.Cells["A:AZ"].AutoFitColumns();  
    Response.Clear();  
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";  
    Response.AddHeader("content-disposition", "attachment: filename=" + "Report.xlsx");  
    Response.BinaryWrite(Ep.GetAsByteArray());  
    Response.End();  
}

Complete Controller code

using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Web;  
using System.Web.Mvc;  
using MongoDB.Driver;  
using MongoDB.Bson;  
using System.Configuration;  
using ExportExcel.Models;  
using OfficeOpenXml;  
using System.Drawing;  
  
namespace ExportExcel.Controllers  
{  
    public class StudentController : Controller  
    {
        public ActionResult List()  
        {  
            string constr = ConfigurationManager.AppSettings["connectionString"];  
            var Client = new MongoClient(constr);  
            var db = Client.GetDatabase("Employee");  
            var collection = db.GetCollection<EmployeeDetails>("EmployeeDetails").Find(new BsonDocument()).ToList();
            return View(collection);  
        }  
        public void DownloadExcel()  
        {  
            string constr = ConfigurationManager.AppSettings["connectionString"];  
            var Client = new MongoClient(constr);  
            var db = Client.GetDatabase("Employee");
            var collection = db.GetCollection<EmployeeDetails>("EmployeeDetails").Find(new BsonDocument()).ToList();  
            ExcelPackage Ep = new ExcelPackage();  
            ExcelWorksheet Sheet = Ep.Workbook.Worksheets.Add("Report");  
            Sheet.Cells["A1"].Value = "Name";  
            Sheet.Cells["B1"].Value = "Department";  
            Sheet.Cells["C1"].Value = "Address";  
            Sheet.Cells["D1"].Value = "City";  
            Sheet.Cells["E1"].Value = "Country";  
            int row = 2;  
            foreach (var item in collection)  
            {
                Sheet.Cells[string.Format("A{0}", row)].Value = item.Name;  
                Sheet.Cells[string.Format("B{0}", row)].Value = item.Department;  
                Sheet.Cells[string.Format("C{0}", row)].Value = item.Address;  
                Sheet.Cells[string.Format("D{0}", row)].Value = item.City;  
                Sheet.Cells[string.Format("E{0}", row)].Value = item.Country;  
                row++;  
            }
            Sheet.Cells["A:AZ"].AutoFitColumns();  
            Response.Clear();  
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";  
            Response.AddHeader("content-disposition", "attachment: filename=" + "Report.xlsx");  
            Response.BinaryWrite(Ep.GetAsByteArray());  
            Response.End();  
        }  
    }  
}

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

The data is downloaded in Excel format.

Summary

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