Overview
In this tutorial I will show you how you can use the ClosedXML nuget package to export data in Excel format using an ASP.NET 6.0 Razor Pages web app. The data we will is is hard-coded student data. However, the approach works for data from any data source.
Source code: https://github.com/medhatelmasry/ExcelStar.git
Companion Video: https://youtu.be/8olaWPDKXyU
The environment I am using is:
- https://github.com/medhatelmasry/OrdersChartRazorGoogleWrapper
- .NET version 6.0.100
- Visual Studio Code
Project setup
Run the following command to create an ASP.NET Core Razor Pages application using .NET 6.0 in a folder named ExcelStar:
dotnet new razor -f net6.0 -o ExcelStar
Change directory into the new folder:
cd ExcelStar
Install the ClosedXML nuget package with:
dotnet add package ClosedXML
Open the project inside VS Code with the following commands:
code .
Sample student data
Create a folder named Data. Inside the Data folder, create a Student.cs class file and add to it the following Student code:
public class Student {
public int? Id { get; set; }
public string? FirstName { get; set; }
public string? LastName { get; set; }
public string? School { get; set; }
public static IEnumerable<Student> GetStudents() {
int ndx = 0;
List<Student> students = new List<Student>() {
new Student() { Id = ++ndx, FirstName="Max", LastName="Pao", School="Science" },
new Student() { Id = ++ndx, FirstName="Tom", LastName="Fay", School="Mining" },
new Student() { Id = ++ndx, FirstName="Ann", LastName="Sun", School="Nursing" },
new Student() { Id = ++ndx, FirstName="Joe", LastName="Fox", School="Computing" },
new Student() { Id = ++ndx, FirstName="Sue", LastName="Mai", School="Mining" },
new Student() { Id = ++ndx, FirstName="Ben", LastName="Lau", School="Business" },
new Student() { Id = ++ndx, FirstName="Zoe", LastName="Ray", School="Mining" },
new Student() { Id = ++ndx, FirstName="Sam", LastName="Ash", School="Medicine" },
new Student() { Id = ++ndx, FirstName="Dan", LastName="Lee", School="Computing" },
new Student() { Id = ++ndx, FirstName="Pat", LastName="Day", School="Science" },
new Student() { Id = ++ndx, FirstName="Kim", LastName="Rex", School="Computing" },
new Student() { Id = ++ndx, FirstName="Tim", LastName="Ram", School="Business" },
new Student() { Id = ++ndx, FirstName="Rob", LastName="Wei", School="Mining" },
new Student() { Id = ++ndx, FirstName="Jan", LastName="Tex", School="Science" },
new Student() { Id = ++ndx, FirstName="Jim", LastName="Kid", School="Business" },
new Student() { Id = ++ndx, FirstName="Ben", LastName="Chu", School="Medicine" },
new Student() { Id = ++ndx, FirstName="Mia", LastName="Tao", School="Computing" },
new Student() { Id = ++ndx, FirstName="Ted", LastName="Day", School="Business" },
new Student() { Id = ++ndx, FirstName="Amy", LastName="Roy", School="Science" },
new Student() { Id = ++ndx, FirstName="Ian", LastName="Kit", School="Nursing" },
new Student() { Id = ++ndx, FirstName="Liz", LastName="Tan", School="Medicine" },
new Student() { Id = ++ndx, FirstName="Mat", LastName="Roy", School="Tourism" },
new Student() { Id = ++ndx, FirstName="Deb", LastName="Luo", School="Medicine" },
new Student() { Id = ++ndx, FirstName="Ana", LastName="Poe", School="Computing" },
new Student() { Id = ++ndx, FirstName="Lyn", LastName="Raj", School="Science" },
new Student() { Id = ++ndx, FirstName="Amy", LastName="Ash", School="Tourism" },
new Student() { Id = ++ndx, FirstName="Kim", LastName="Kid", School="Mining" },
new Student() { Id = ++ndx, FirstName="Bec", LastName="Fry", School="Nursing" },
new Student() { Id = ++ndx, FirstName="Eva", LastName="Lap", School="Computing" },
new Student() { Id = ++ndx, FirstName="Eli", LastName="Yim", School="Business" },
new Student() { Id = ++ndx, FirstName="Sam", LastName="Hui", School="Science" },
new Student() { Id = ++ndx, FirstName="Joe", LastName="Jin", School="Mining" },
new Student() { Id = ++ndx, FirstName="Liz", LastName="Kuo", School="Agriculture" },
new Student() { Id = ++ndx, FirstName="Ric", LastName="Mak", School="Tourism" },
new Student() { Id = ++ndx, FirstName="Pam", LastName="Day", School="Computing" },
new Student() { Id = ++ndx, FirstName="Stu", LastName="Gad", School="Business" },
new Student() { Id = ++ndx, FirstName="Tom", LastName="Bee", School="Tourism" },
new Student() { Id = ++ndx, FirstName="Bob", LastName="Lam", School="Agriculture" },
new Student() { Id = ++ndx, FirstName="Jim", LastName="Ots", School="Medicine" },
new Student() { Id = ++ndx, FirstName="Tom", LastName="Mag", School="Mining" },
new Student() { Id = ++ndx, FirstName="Hal", LastName="Doe", School="Agriculture" },
new Student() { Id = ++ndx, FirstName="Roy", LastName="Kim", School="Nursing" },
new Student() { Id = ++ndx, FirstName="Vis", LastName="Cox", School="Science" },
new Student() { Id = ++ndx, FirstName="Kay", LastName="Aga", School="Tourism" },
new Student() { Id = ++ndx, FirstName="Reo", LastName="Hui", School="Business" },
new Student() { Id = ++ndx, FirstName="Bob", LastName="Roe", School="Medicine" },
};
return students;
}
}
public int? Id { get; set; }
public string? FirstName { get; set; }
public string? LastName { get; set; }
public string? School { get; set; }
public static IEnumerable<Student> GetStudents() {
int ndx = 0;
List<Student> students = new List<Student>() {
new Student() { Id = ++ndx, FirstName="Max", LastName="Pao", School="Science" },
new Student() { Id = ++ndx, FirstName="Tom", LastName="Fay", School="Mining" },
new Student() { Id = ++ndx, FirstName="Ann", LastName="Sun", School="Nursing" },
new Student() { Id = ++ndx, FirstName="Joe", LastName="Fox", School="Computing" },
new Student() { Id = ++ndx, FirstName="Sue", LastName="Mai", School="Mining" },
new Student() { Id = ++ndx, FirstName="Ben", LastName="Lau", School="Business" },
new Student() { Id = ++ndx, FirstName="Zoe", LastName="Ray", School="Mining" },
new Student() { Id = ++ndx, FirstName="Sam", LastName="Ash", School="Medicine" },
new Student() { Id = ++ndx, FirstName="Dan", LastName="Lee", School="Computing" },
new Student() { Id = ++ndx, FirstName="Pat", LastName="Day", School="Science" },
new Student() { Id = ++ndx, FirstName="Kim", LastName="Rex", School="Computing" },
new Student() { Id = ++ndx, FirstName="Tim", LastName="Ram", School="Business" },
new Student() { Id = ++ndx, FirstName="Rob", LastName="Wei", School="Mining" },
new Student() { Id = ++ndx, FirstName="Jan", LastName="Tex", School="Science" },
new Student() { Id = ++ndx, FirstName="Jim", LastName="Kid", School="Business" },
new Student() { Id = ++ndx, FirstName="Ben", LastName="Chu", School="Medicine" },
new Student() { Id = ++ndx, FirstName="Mia", LastName="Tao", School="Computing" },
new Student() { Id = ++ndx, FirstName="Ted", LastName="Day", School="Business" },
new Student() { Id = ++ndx, FirstName="Amy", LastName="Roy", School="Science" },
new Student() { Id = ++ndx, FirstName="Ian", LastName="Kit", School="Nursing" },
new Student() { Id = ++ndx, FirstName="Liz", LastName="Tan", School="Medicine" },
new Student() { Id = ++ndx, FirstName="Mat", LastName="Roy", School="Tourism" },
new Student() { Id = ++ndx, FirstName="Deb", LastName="Luo", School="Medicine" },
new Student() { Id = ++ndx, FirstName="Ana", LastName="Poe", School="Computing" },
new Student() { Id = ++ndx, FirstName="Lyn", LastName="Raj", School="Science" },
new Student() { Id = ++ndx, FirstName="Amy", LastName="Ash", School="Tourism" },
new Student() { Id = ++ndx, FirstName="Kim", LastName="Kid", School="Mining" },
new Student() { Id = ++ndx, FirstName="Bec", LastName="Fry", School="Nursing" },
new Student() { Id = ++ndx, FirstName="Eva", LastName="Lap", School="Computing" },
new Student() { Id = ++ndx, FirstName="Eli", LastName="Yim", School="Business" },
new Student() { Id = ++ndx, FirstName="Sam", LastName="Hui", School="Science" },
new Student() { Id = ++ndx, FirstName="Joe", LastName="Jin", School="Mining" },
new Student() { Id = ++ndx, FirstName="Liz", LastName="Kuo", School="Agriculture" },
new Student() { Id = ++ndx, FirstName="Ric", LastName="Mak", School="Tourism" },
new Student() { Id = ++ndx, FirstName="Pam", LastName="Day", School="Computing" },
new Student() { Id = ++ndx, FirstName="Stu", LastName="Gad", School="Business" },
new Student() { Id = ++ndx, FirstName="Tom", LastName="Bee", School="Tourism" },
new Student() { Id = ++ndx, FirstName="Bob", LastName="Lam", School="Agriculture" },
new Student() { Id = ++ndx, FirstName="Jim", LastName="Ots", School="Medicine" },
new Student() { Id = ++ndx, FirstName="Tom", LastName="Mag", School="Mining" },
new Student() { Id = ++ndx, FirstName="Hal", LastName="Doe", School="Agriculture" },
new Student() { Id = ++ndx, FirstName="Roy", LastName="Kim", School="Nursing" },
new Student() { Id = ++ndx, FirstName="Vis", LastName="Cox", School="Science" },
new Student() { Id = ++ndx, FirstName="Kay", LastName="Aga", School="Tourism" },
new Student() { Id = ++ndx, FirstName="Reo", LastName="Hui", School="Business" },
new Student() { Id = ++ndx, FirstName="Bob", LastName="Roe", School="Medicine" },
};
return students;
}
}
The above provides us with suitable sample students data that we will later export into Excel format.
Export data to Excel format
Inside the Pages folder, add two files names Excel.cshtml & Excel.cshtml.cs.
Content of Excel.cshtml, representing the view, will contain the following minimal code:
@page@model ExcelModel
Excel.cshtml.cs is the code-behind file for the view and will contain the following minimal code:
using ClosedXML.Excel;
using ExcelStar.Data;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
namespace ExcelStar.Pages;
public class ExcelModel : PageModel {
private readonly ILogger<IndexModel> _logger;
public ExcelModel(ILogger<IndexModel> logger) {
_logger = logger;
}
public FileResult OnGet() {
var data = Student.GetStudents();
using (var workbook = new XLWorkbook()) {
IXLWorksheet worksheet =
workbook.Worksheets.Add("Students");
worksheet.Cell(1, 1).Value = "Id";
worksheet.Cell(1, 2).Value = "First";
worksheet.Cell(1, 3).Value = "Last";
worksheet.Cell(1, 4).Value = "School";
IXLRange range = worksheet.Range(worksheet.Cell(1, 1).Address, worksheet.Cell(1, 4).Address);
range.Style.Fill.SetBackgroundColor(XLColor.Almond);
int index = 1;
foreach (var item in data) {
index++;
worksheet.Cell(index, 1).Value = item.Id;
worksheet.Cell(index, 2).Value = item.FirstName;
worksheet.Cell(index, 3).Value = item.LastName;
worksheet.Cell(index, 4).Value = item.School;
}
using (var stream = new MemoryStream()) {
workbook.SaveAs(stream);
var content = stream.ToArray();
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
var strDate = DateTime.Now.ToString("yyyyMMdd");
string filename = string.Format($"Students_{strDate}.xlsx");
return File(content, contentType, filename);
}
}
}
}
What does the above code do?
- Students are read into a variable named data.
- A worksheet is created named Students with column titles Id, First, Last & School
- The Students worksheet is added to the workbook.
- The background color of the column titles is made to be the Almond color.
- Thereafter, each row is filled with student data whereby Id goes into column 1, FirstName goes into column 2, LastName goes into column 3 and School goes into column 4.
- The workbook is saved as a stream.
- The stream is returned to the user as a file with a fixed name and an appropriate HTTP content type.
Adding Excel page to the navigation
We need to add our new page to the main navigation system. Edit Pages/Shared/_Layout.cshtml and add the following to the bottom of the <ul> . . . </ul> block:
<li class="nav-item">
<a class="nav-link text-dark" asp-area="" asp-page="/Excel">Export to Excel</a>
</li>
Testing our app
It is time to run our application with the following terminal window command:
dotnet watch
The application opens in your default browser and looks like this:
When you click on "Export to Excel", a file with extension .xlsx is downloaded to your default downloads folder. If you open this file in Excel, it should look like this:
Go ahead and explore other things you can do, like adding links, count, sum etc...
I hope you found this brief tutorial useful and that it satisfies your expectations.
