Exporting data to Excel is a common feature in modern web applications. In this blog, we will explain how to Do So using EPPlus in ASP.NET Core 8. We’ll cover everything from basic export functionality to advanced formatting (like headers and styles).
Introduction to EPPlus
EPPlus is a popular open-source .NET library that makes it easy to work with Excel files. It supports Excel 2007-2022 formats (.xlsx) and provides formatting, charts, formulas, and more features. It’s an excellent choice for exporting data to Excel, particularly in ASP.NET Core applications, because of its flexibility and simplicity.
Let’s start deep dive into it. We will use the same Reverse Engineering demo for this example. If you didn’t read that blog then please click here to read that blog.
Step 1: Install EPPlus Packages
You need to install the EPPlus library in your project. You can install it via NuGet. Use the following command in the NuGet Package Manager Console
Install-Package EPPlus
Step 2: Create the Data Export Method
We will add the Data Export method in the Home controller.
public IActionResult Export()
{
var empList = dbContext.Employees.ToList();
// If you are a commercial business and have
// purchased commercial licenses use the static property
// LicenseContext of the ExcelPackage class:
//ExcelPackage.LicenseContext = LicenseContext.Commercial;
// If you use EPPlus in a noncommercial context
// according to the Polyform Noncommercial license:
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("Employee");
// Add headers
worksheet.Cells[1, 1].Value = "Employee ID";
worksheet.Cells[1, 2].Value = "First Name";
worksheet.Cells[1, 3].Value = "Last Name";
worksheet.Cells[1, 4].Value = "Designation";
// Style headers
using (var range = worksheet.Cells[1, 1, 1, 4])
{
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightBlue);
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
// Adjust column widths
worksheet.Column(1).Width = 10;
worksheet.Column(2).Width = 20;
worksheet.Column(3).Width = 30;
worksheet.Column(4).Width = 15;
// Add student data
for (int i = 0; i < empList.Count; i++)
{
worksheet.Cells[i + 2, 1].Value = empList[i].EmployeeId;
worksheet.Cells[i + 2, 2].Value = empList[i].FirstName;
worksheet.Cells[i + 2, 3].Value = empList[i].LastName;
worksheet.Cells[i + 2, 4].Value = empList[i].Designation;
}
// Export as Excel file
var stream = new MemoryStream();
package.SaveAs(stream);
stream.Position = 0;
var fileName = $"Employee_{DateTime.Now.ToString("yyyyMMdd_HHmmss")}.xlsx";
return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
}
}
In the above method,
- We get all the employees from the database.
- EPPlus requires this setting for non-commercial use. For large-scale commercial projects, make sure to comply with EPPlus licensing requirements.
- We use ExcelPackage to create an Excel workbook and add a worksheet named “Employee”.
- Headers are added in the first row, followed by the employee data in subsequent rows.
- We use the range.Style to apply bold formatting, a solid fill colour, and center-align the text.
- worksheet.Column().Width is used to adjust column widths for better readability.
- The Excel file is written to a memory stream and returned as a downloadable file.
Step 3: Add a button in the Index view to export data
@model IEnumerable<WebApplication2.DBModels.Employee>
@{
ViewData["Title"] = "Home Page";
}
<div class="text-center">
<button class="btn btn-primary" onclick="Export()">Export</button>
<table class="table">
<thead>
<tr>
<th>Employee ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Designation</th>
</tr>
</thead>
@foreach (var item in Model)
{
<tr>
<td>@item.EmployeeId</td>
<td>@item.FirstName</td>
<td>@item.LastName</td>
<td>@item.Designation</td>
</tr>
}
</table>
</div>
<script type="text/javascript">
function Export(){
window.location.href = "/Home/Export";
}
</script>
Here is the full Home controller code
using Microsoft.AspNetCore.Mvc;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.Diagnostics;
using WebApplication2.DBModels;
using WebApplication2.Models;
namespace WebApplication2.Controllers
{
public class HomeController : Controller
{
private readonly EmployeeDbContext dbContext;
public HomeController(EmployeeDbContext dbContext)
{
this.dbContext = dbContext;
}
public IActionResult Index()
{
var empList = dbContext.Employees.ToList();
return View(empList);
}
public IActionResult Export()
{
var empList = dbContext.Employees.ToList();
// If you are a commercial business and have
// purchased commercial licenses use the static property
// LicenseContext of the ExcelPackage class:
//ExcelPackage.LicenseContext = LicenseContext.Commercial;
// If you use EPPlus in a noncommercial context
// according to the Polyform Noncommercial license:
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("Employee");
// Add headers
worksheet.Cells[1, 1].Value = "Employee ID";
worksheet.Cells[1, 2].Value = "First Name";
worksheet.Cells[1, 3].Value = "Last Name";
worksheet.Cells[1, 4].Value = "Designation";
// Style headers
using (var range = worksheet.Cells[1, 1, 1, 4])
{
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightBlue);
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
// Adjust column widths
worksheet.Column(1).Width = 10;
worksheet.Column(2).Width = 20;
worksheet.Column(3).Width = 30;
worksheet.Column(4).Width = 15;
// Add student data
for (int i = 0; i < empList.Count; i++)
{
worksheet.Cells[i + 2, 1].Value = empList[i].EmployeeId;
worksheet.Cells[i + 2, 2].Value = empList[i].FirstName;
worksheet.Cells[i + 2, 3].Value = empList[i].LastName;
worksheet.Cells[i + 2, 4].Value = empList[i].Designation;
}
// Export as Excel file
var stream = new MemoryStream();
package.SaveAs(stream);
stream.Position = 0;
var fileName = $"Employee_{DateTime.Now.ToString("yyyyMMdd_HHmmss")}.xlsx";
return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
}
}
When you run the project then you can see the employee list and Export button
When you click the Export button the Excel file will be downloaded.
Conclusion
Exporting data to Excel in ASP.NET Core 8 using EPPlus is not only easy but also highly customizable. We’ve covered the basics of exporting data, adding styles, and formatting headers. With this knowledge, you can now implement an efficient and user-friendly data export feature in your web applications. Whether you’re building a simple export tool or a full-fledged report generation system, EPPlus is a powerful tool in your ASP.NET Core toolkit.