In this article I will explain with an example, how to download Excel file in ASP.Net Core MVC.
The data from the database is fetched using Entity Framework and then the data is downloaded as Excel file using ClosedXML Excel library in ASP.Net Core MVC.
Database
Here I am making use of Microsoft’s Northwind Database. You can download it from here.
Note: The Customers Table of the Northwind Database will be used in this project.
Installing and adding reference of ClosedXML Library
In order to install and add reference of ClosedXML library, you will need to:-
1. Right Click the Project in Solution Explorer and click Manage NuGet Packages from the Context Menu.
![ASP.Net Core: Download Excel file in ASP.Net Core MVC]()
2. Now you will need to look for ClosedXML package and once found, you need to click the Install Button.
![ASP.Net Core: Download Excel file in ASP.Net Core MVC]()
Downloading Entity Framework Core
You will need to install the Microsoft.EntityFrameworkCore.SqlServer package using the following command.
Install-Package Microsoft.EntityFrameworkCore.SqlServer -Version 2.1.1
Model
1. Once the package is successfully installed, create a Folder named Models in your project and then a new class by right clicking the Models folder and then click on Add and then New Item option of the Context Menu.
![ASP.Net Core: Download Excel file in ASP.Net Core MVC]()
2. Create the following properties inside the Model class as shown below.
Note: In this article, only four Columns will be displayed and hence four properties are added to the class.
public class Customer
{
public string CustomerID { get; set; }
public string ContactName { get; set; }
public string City { get; set; }
public string Country { get; set; }
}
Creating an Entity Data Model
The very first step is to create an ASP.Net MVC Application and connect it to the Database using Entity Framework.
Once the Entity Framework is configured and connected to the database table, the Database Context will look as shown below.
using Export_Excel_Core_MVC.Models;
using Microsoft.EntityFrameworkCore;
namespace Export_Excel_Core_MVC
{
public class DBCtx : DbContext
{
public DBCtx(DbContextOptions<DBCtx> options) : base(options)
{
}
public DbSet<Customer> Customers { get; set; }
}
}
Namespaces
You will need to import the following namespaces.
using System.IO;
using System.Data;
using System.Linq;
using ClosedXML.Excel;
using Microsoft.AspNetCore.Mvc;
Controller
The Controller consists of two Action methods.
Action method for handling GET operation
Inside this Action method, the Top 10 Customer records are fetched and returned to the View.
Action method for handling the Excel File Export and Download operation
This Action method is executed when the Export Submit button is clicked.
The Top 10 Customer records are fetched from the Customers Table using Entity Framework and are added to a dynamic DataTable. Then the DataTable is added to the Worksheets collection of the ClosedXML’s XLWorkbook object.
Then XLWorkbook object is saved to a MemoryStream object which then is converted to Byte Array and exported and downloaded as Excel file using the File function.
public class HomeController : Controller
{
private DBCtx Context { get; }
public HomeController(DBCtx _context)
{
this.Context = _context;
}
public IActionResult Index()
{
List<Customer> customers = (from customer in this.Context.Customers.Take(10)
select customer).ToList();
return View(customers);
}
[HttpPost]
public IActionResult Export()
{
DataTable dt = new DataTable("Grid");
dt.Columns.AddRange(new DataColumn[4] { new DataColumn("CustomerId"),
new DataColumn("ContactName"),
new DataColumn("City"),
new DataColumn("Country") });
var customers = from customer in this.Context.Customers.Take(10)
select customer;
foreach (var customer in customers)
{
dt.Rows.Add(customer.CustomerID, customer.ContactName, customer.City, customer.Country);
}
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt);
using (MemoryStream stream = new MemoryStream())
{
wb.SaveAs(stream);
return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Grid.xlsx");
}
}
}
}
View
Inside the View, in the very first line the Customer Entity is declared as IEnumerable which specifies that it will be available as a Collection.
For displaying the records, an HTML Table is used. A loop will be executed over the Model which will generate the HTML Table rows with the Customer records.
Finally there’s an HTML Submit button enclosed inside a Form with the Action method specified as Export.
When this Button will be clicked, the Grid (Html Table) data will be exported and downloaded as Excel file.
@using Export_Excel_Core_MVC.Models;
@model IEnumerable<Customer>
@addTagHelper*, Microsoft.AspNetCore.Mvc.TagHelpers
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width"/>
<title>Index</title>
</head>
<body>
<h4>Customers</h4>
<hr/>
<table cellpadding="0" cellspacing="0">
<tr>
<th>CustomerID</th>
<th>ContactName</th>
<th>City</th>
<th>Country</th>
</tr>
@foreach (Customer customer in Model)
{
<tr>
<td>@customer.CustomerID</td>
<td>@customer.ContactName</td>
<td>@customer.City</td>
<td>@customer.Country</td>
</tr>
}
</table>
<br/>
<br/>
<form method="post" asp-controller="Home" asp-action="Export">
<input type="submit" value="Export"/>
</form>
</body>
</html>
Screenshots
Grid (Html Table)
![ASP.Net Core: Download Excel file in ASP.Net Core MVC]()
Exported Excel File
![ASP.Net Core: Download Excel file in ASP.Net Core MVC]()
Downloads
