Here, we will learn about downloading excel file in ASP.NET MVC. We will generate the excel file from static data, but in real life, data are coming from the database. So both have the same structure.
Prerequisite:
- Basic knowledge of ASP.NET MVC
- Basic knowledge of jQuery
Open the NuGet package manager console and add the EPPlus library.
Type the following command in it.
Install-Package EPPlus -Version 4.5.3.2
So, create a new project in ASP.NET MVC and open the _Layout.cshtml file and add the following code in it.
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>@ViewBag.Title - My ASP.NET Application</title>
@Styles.Render("~/Content/css")
@Scripts.Render("~/bundles/modernizr")
@Scripts.Render("~/bundles/jquery")
</head>
<body>
<div class="navbar navbar-inverse navbar-fixed-top">
<div class="container">
<div class="navbar-header">
<button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
@Html.ActionLink("Download Excel File", "Index", "Home", new { area = "" }, new { @class = "navbar-brand" })
</div>
<div class="navbar-collapse collapse">
<ul class="nav navbar-nav">
<li>@Html.ActionLink("Home", "Index", "Home")</li>
<li>@Html.ActionLink("About", "About", "Home")</li>
<li>@Html.ActionLink("Contact", "Contact", "Home")</li>
</ul>
</div>
</div>
</div>
<div class="container body-content">
@RenderBody()
<hr />
<footer>
<p>© @DateTime.Now.Year - My ASP.NET Application</p>
</footer>
</div>
@Scripts.Render("~/bundles/bootstrap")
@RenderSection("scripts", required: false)
</body>
</html>
Create a class as ExcelViewModel in the Model folder with following parameters.
public class ExcelViewModel
{
public string EnrollmentNo { get; set; }
public string Semester { get; set; }
public string Month { get; set; }
public string Year { get; set; }
public string StatementNo { get; set; }
}
Open the Index.cshtml and add the code in it.
@{
ViewBag.Title = "Home Page";
}
<button class="btn btn-success" id="btnDownload">Download Excel</button>
<script>
$(document).on('click', '#btnDownload', function () {
window.open('/Home/DownloadExcelFile', '_blank');
});
</script>
Finally, open the Home Controller and add the following code in it.
using DownloadExcelFileInMvc.Models;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Web.Mvc;
namespace DownloadExcelFileInMvc.Controllers
{
public class HomeController : Controller
{
public ActionResult Index() => View();
public void DownloadExcelFile()
{
List<ExcelViewModel> viewModel = new List<ExcelViewModel>();
viewModel.Add(new ExcelViewModel() { EnrollmentNo = "1660", Semester = "5", Month = "Jan", Year = "2019", StatementNo = "2000" });
viewModel.Add(new ExcelViewModel() { EnrollmentNo = "1660", Semester = "5", Month = "Jan", Year = "2019", StatementNo = "2000" });
viewModel.Add(new ExcelViewModel() { EnrollmentNo = "1660", Semester = "5", Month = "Jan", Year = "2019", StatementNo = "2000" });
viewModel.Add(new ExcelViewModel() { EnrollmentNo = "1660", Semester = "5", Month = "Jan", Year = "2019", StatementNo = "2000" });
viewModel.Add(new ExcelViewModel() { EnrollmentNo = "1660", Semester = "5", Month = "Jan", Year = "2019", StatementNo = "2000" });
viewModel.Add(new ExcelViewModel() { EnrollmentNo = "1660", Semester = "5", Month = "Jan", Year = "2019", StatementNo = "2000" });
ExcelPackage Ep = new ExcelPackage();
ExcelWorksheet Sheet = Ep.Workbook.Worksheets.Add("MarksheetExcel");
Sheet.Cells["A1"].Value = "Enrollement No.";
Sheet.Cells["B1"].Value = "Semester";
Sheet.Cells["C1"].Value = "Month";
Sheet.Cells["D1"].Value = "Year";
Sheet.Cells["E1"].Value = "Statement No.";
int row = 2;
foreach (var item in viewModel)
{
Sheet.Cells[string.Format("A{0}", row)].Value = item.EnrollmentNo;
Sheet.Cells[string.Format("B{0}", row)].Value = item.Semester;
Sheet.Cells[string.Format("C{0}", row)].Value = item.Month;
Sheet.Cells[string.Format("D{0}", row)].Value = item.Year;
Sheet.Cells[string.Format("E{0}", row)].Value = item.StatementNo;
row++;
}
Sheet.Cells["A:AZ"].AutoFitColumns();
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment: filename=" + $"MarksheetExcel_{DateTime.Now.Ticks.ToString()}.xlsx");
Response.BinaryWrite(Ep.GetAsByteArray());
Response.End();
}
}
}
Output:
thank you!