In this article I will explain with an example, how to export to Excel file in ASP.Net Core Razor Pages.
The data from the database is fetched using Entity Framework and then the data is exported and downloaded as Microsoft Excel file using ClosedXML Excel library which is a wrapper for OpenXml Excel library.
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 Razor Pages: Export to Excel]()
2. Now you will need to look for ClosedXML package and once found, you need to click the Install Button.
![ASP.Net Core Razor Pages: Export to Excel]()
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 Razor Pages: Export to Excel]()
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; }
}
Configuring and connecting to Database using Entity Framework
The very first step is to create an ASP.Net Core Razor Pages Application and connect it to the Northwind Database using Entity Framework.
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;
using Microsoft.AspNetCore.Mvc.RazorPages;
Razor PageModel (Code-Behind)
The Database Context is injected into the IndexModel class using Dependency Injection method.
Handler method for handling GET operation
Inside the Handler method that handles the GET calls, the Top 10 records are fetched from the Customers Table of the Northwind Database and returned to the Razor Page.
Handler method for handling the Excel File Export and Download operation
This Handler 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 IndexModel : PageModel
{
private DBCtx Context { get; }
public IndexModel(DBCtx _context)
{
this.Context = _context;
}
public List<Customer> Customers { get; set; }
public void OnGet()
{
this.Customers = (from customer in this.Context.Customers.Take(10)
select customer).ToList();
}
public FileResult OnPostExport()
{
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");
}
}
}
}
Razor Page (HTML)
The HTML of Razor Page consists of an HTML Table for displaying the records.
A loop will be executed over the Model which will generate the HTML Table rows with the Customer records.
Below the HTML Table, Page consists of a Form with a Submit Button.
The Submit Button has been set with the POST Handler method using the asp-page-handler attribute.
Note: In the Razor PageModel, the Handler method name is OnPostExport but here it will be specified as Export when calling from the Razor HTML Page.
When this Export Button will be clicked, the Grid (Html Table) data will be exported and downloaded as Excel file.
@page
@model Razor_Export_Excel.Pages.IndexModel
@using Razor_Export_Excel.Models
@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.Customers)
{
<tr>
<td>@customer.CustomerID</td>
<td>@customer.ContactName</td>
<td>@customer.City</td>
<td>@customer.Country</td>
</tr>
}
</table>
<br/>
<br/>
<form method="post">
<input type="submit" value="Export" asp-page-handler="Export"/>
</form>
</body>
</html>
Screenshots
Grid (Html Table)
![ASP.Net Core Razor Pages: Export to Excel]()
Exported Excel File
![ASP.Net Core Razor Pages: Export to Excel]()
Downloads
