Export data to Excel with ASP.NET Core

I had to export some data to Excel from an ASP.NET Core website. I have been implementing a lot of exporting in the past with ASP.NET and ASP.NET MVC. Supporting the same in ASP.NET Core was a good excuse to look at different possibilities and write about it 😃

When needing to export data to Excel from ASP.NET Core, you have a couple of different options. If you need simple tabular data, generating a CSV file may be sufficient. If you need to embed colors and formulas, you will need something more advanced. Let's start simple.

For the examples in this post, I'll use an ASP.NET Core controller with some hard-coded data. In a real application, these data would come from user input, a database, or similar:

public class HomeController : Controller
{
    private List<User> users = new List<User>
    {
        new User { Id = 1, Username = "DoloresAbernathy" },
        new User { Id = 2, Username = "MaeveMillay" },
        new User { Id = 3, Username = "BernardLowe" },
        new User { Id = 4, Username = "ManInBlack" }
    };

    public HomeController()
    {
    }
}

Export as a CSV

The simplest way to export data is as a comma-separated values (CSV) file. There are NuGet packages available to help to that, but for this post, I'll create the CSV manually:

public IActionResult Csv()
{
    var builder = new StringBuilder();
    builder.AppendLine("Id,Username");
    foreach (var user in users)
    {
        builder.AppendLine($"{user.Id},{user.Username}");
    }

    return File(Encoding.UTF8.GetBytes(builder.ToString()), "text/csv", "users.csv");
}

Using the StringBuilder class available in the System.Text namespace, I'm adding headers in the first line and then appending a line per user. Finally, I'm returning the generated CSV using the File helper available in ASP.NET Core. By returning the content as a file, the browser will automatically download the content as a CSV file named users.csv.

Export as an XLSX

If you need more complexity in your exported file than simple tabular data, you need to export data as a real Excel file. CSV files cannot contain colors, formulas, etc.

Since XLSX files are XML based, you can generate the content yourself. I wouldn't recommend doing that since the format is complex and there are a lot of good NuGet packages available. I have been using the ClosedXML package in the past, why I was happy to see that the package is still being developed and supported in .NET Core. I also looked at alternative packages like EPPlus and DocumentFormat.OpenXml from Microsoft, but neither provides an API as simple as ClosedXML (IMO).

To generate an XLSX file with ClosedXML start by installing the following NuGet package:

Install-Package ClosedXML

Then add a new controller action. For this post, I have formatted the output like in the CSV example for consistency. ClosedXML provides a rich set of features to include colors, calculations, and much more. Check out their documentation for details. Here's the code:

public IActionResult Excel()
{
    using (var workbook = new XLWorkbook())
    {
        var worksheet = workbook.Worksheets.Add("Users");
        var currentRow = 1;
        worksheet.Cell(currentRow, 1).Value = "Id";
        worksheet.Cell(currentRow, 2).Value = "Username";
        foreach (var user in users)
        {
            currentRow++;
            worksheet.Cell(currentRow, 1).Value = user.Id;
            worksheet.Cell(currentRow, 2).Value = user.Username;
        }

        using (var stream = new MemoryStream())
        {
            workbook.SaveAs(stream);
            var content = stream.ToArray();

            return File(
                content,
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                "users.xlsx");
        }
    }
}

Let's go through the code. To create the Excel document, I'm creating a new XLWorkbook. You can see the benefits of ClosedXML in line 5 where I create a new worksheet named Users. Excel worksheets aren't available using a CSV file.

Like in the previous example, I add a headline row and put in the values from the users list in the following rows. The API doesn't require a lot of introduction since it is pretty easy to understand. You reference rows and cells using the worksheet object.

Finally, I export the workbook to a stream and return it from the controller as a file.

As it turns out, exporting data to Excel from ASP.NET Core is pretty straight-forward. Depending on the requirements of the outputted file, you can choose either CSV or XLSX. Since ClosedXML is my go-to framework, I would love to hear your experience with good Excel packages.

See how we can help you monitor your website for crashes Monitor your website