How to export data to Excel in ASP.NET Core 3.0

Learn how to use the ClosedXML NuGet package to export data as a CSV or XLSX file for Excel from an ASP.NET Core application

How to export data to Excel in ASP.NET Core 3.0
Iaremenko / Getty Images

When building web applications, you will often need to import or export data from or to Word or Excel documents. There are several ways to achieve this, and plenty of NuGet packages to work with Word or Excel. This article discusses how we can work with ClosedXML in ASP.NET Core to export data to Excel.

To work with the code examples provided in this article, you should have Visual Studio 2019 installed in your system. If you don’t already have a copy, you can download Visual Studio 2019 here.

Create an ASP.NET Core MVC project in Visual Studio

First off, let’s create an ASP.NET Core project in Visual Studio 2019. Assuming Visual Studio 2019 is installed in your system, follow the steps outlined below to create a new ASP.NET Core project in Visual Studio.

  1. Launch the Visual Studio IDE.
  2. Click on “Create new project.”
  3. In the “Create new project” window, select “ASP.NET Core Web Application” from the list of templates displayed.
  4. Click Next.
  5. In the “Configure your new project” window, specify the name and location for the new project.
  6. Optionally, select the “Place solution and project in the same directory” check box.
  7. Click Create.
  8. In the “Create a New ASP.NET Core Web Application” window shown next, select .NET Core as the runtime and ASP.NET Core 2.2 (or later) from the drop-down list at the top. I’ll be using ASP.NET Core 3.0. 
  9. Select “Web Application (Model-View-Controller)” as the project template to create a new ASP.NET Core MVC application. 
  10. Ensure that the check boxes “Enable Docker Support” and “Configure for HTTPS” are unchecked as we won’t be using those features here.
  11. Ensure that Authentication is set to “No Authentication” as we won’t be using authentication either.
  12. Click Create. 

Following these steps should create a new ASP.NET Core MVC project in Visual Studio. We’ll use this project to illustrate exporting data for Excel in the sections below.

Install the ClosedXML NuGet package 

There are several libraries to choose from if you want to export data to Excel. One of them is named ClosedXML. You can install this package either via the NuGet package manager inside the Visual Studio 2019 IDE, or by executing the following command in the NuGet package manager console:

Install-Package ClosedXML

Export data as a CSV file from ASP.NET Core 3.0

Exporting data as a comma-separated (CSV) file is simple. You could take advantage of a NuGet package such as CsvExport or AWright18.SimpleCSVExporter to achieve this, or you could do it manually. For the sake of simplicity, we’ll generate a CSV file manually. Consider the following class named Author.

public class Author
{
  public int Id { get; set; }
  public string FirstName { get; set; }
  public string LastName { get; set; }
}

Next, you can populate data into a list of authors as shown in the code snippet given below.

List<Author> authors = new List<Author>
{
    new Author { Id = 1, FirstName = "Joydip", LastName = "Kanjilal" },
    new Author { Id = 2, FirstName = "Steve", LastName = "Smith" },
    new Author { Id = 3, FirstName = "Anand", LastName = "Narayaswamy"}
};

The following code snippet shows how you can generate a CSV file in an action method of your controller.

public IActionResult DownloadCommaSeperatedFile()
{
    try
    {
       StringBuilder stringBuilder = new StringBuilder();
       stringBuilder.AppendLine("Id,FirstName,LastName");
       foreach (var author in authors)
       {
           stringBuilder.AppendLine($"{author.Id},
           {author.FirstName},{author.LastName}");
       }
      return File(Encoding.UTF8.GetBytes
      (stringBuilder.ToString()), "text/csv", "authors.csv");
    }
    catch
    {
       return Error();
    }
}

Export data as an XLSX file in ASP.NET Core 3.0

A workbook in Excel consists of several worksheets. You can create an Excel workbook using the following code.

var workbook = new XLWorkbook();

You can then take advantage of the IXLWorkSheet interface to create and add worksheets to the workbook as shown below.

IXLWorksheet worksheet = workbook.Worksheets.Add("Authors");
worksheet.Cell(1, 1).Value = "Id";
worksheet.Cell(1, 2).Value = "FirstName";
worksheet.Cell(1, 3).Value = "LastName";
for (int index = 1; index <= authors.Count; index++)
{
   worksheet.Cell(index + 1, 1).Value = authors[index - 1].Id;
   worksheet.Cell(index + 1, 2).Value = authors[index - 1].FirstName;
   worksheet.Cell(index + 1, 3).Value = authors[index - 1].LastName;
}

Lastly, you can save the workbook as a memory stream and then create a FileContentResult instance as shown below.

using (var stream = new MemoryStream())
{
     workbook.SaveAs(stream);
     var content = stream.ToArray();
     return File(content, contentType, fileName);
}

Download an Excel document in ASP.NET Core 3.0

Here is the complete source code of the action method that can be used to download an Excel document.

public IActionResult DownloadExcelDocument()
        {
            string contentType = "application/vnd.openxmlformats-
            officedocument.spreadsheetml.sheet";
            string fileName = "authors.xlsx";
            try
            {
                using (var workbook = new XLWorkbook())
                {
                    IXLWorksheet worksheet =
                    workbook.Worksheets.Add("Authors");
                    worksheet.Cell(1, 1).Value = "Id";
                    worksheet.Cell(1, 2).Value = "FirstName";
                    worksheet.Cell(1, 3).Value = "LastName";
                    for (int index = 1; index <= authors.Count; index++)
                    {
                        worksheet.Cell(index + 1, 1).Value =
                        authors[index - 1].Id;
                        worksheet.Cell(index + 1, 2).Value =
                        authors[index - 1].FirstName;
                        worksheet.Cell(index + 1, 3).Value =
                        authors[index - 1].LastName;
                    }
                    using (var stream = new MemoryStream())
                    {
                        workbook.SaveAs(stream);
                        var content = stream.ToArray();
                        return File(content, contentType, fileName);
                    }
                }
            }
            catch(Exception ex)
            {
                return Error();
            }
        }

While we have used ClosedXML in this article, there are several other packages for reading, writing, and manipulating Excel data in ASP.NET Core including EPPlus and NPOI. You can learn more about ClosedXML on GitHub at https://github.com/ClosedXML/ClosedXML. I’ll discuss importing Excel data in an ASP.NET Core application in a future post here.

How to do more in ASP.NET and ASP.NET Core:

Copyright © 2020 IDG Communications, Inc.