Export to Excel in asp net Core Web API

In this article, we will learn how to Export Data from Database to Excel File in Asp.net Core MVC . In previous article we had discussed How to Upload multiple files using Asp.Net Core MVC C#.

Prerequisites

• Download and install .Net Core 6.0 SDK from here
• Download and Install Microsoft Visual Studio 2022 from here
• Sql-Server

Table of Contents

Create an ASP.NET Core MVC Project

Open visual studio, Go to File menu and click on New and select Project. Then new project window, select ASP.NET Core Web Application (Model-View-Controller) template.

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

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");
}

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");
}

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

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");
}

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");
}

Like in the previous example, I add a headline row and put in the values from the

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");
}

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");
}

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.

Previous post

Extending the date for removing the v2 API

Next post

Introducing Heartbeats

elmah.io: Error logging and Uptime Monitoring for your web apps

This blog post is brought to you by elmah.io. elmah.io is error logging, uptime monitoring, deployment tracking, and service heartbeats for your .NET and JavaScript applications. Stop relying on your users to notify you when something is wrong or dig through hundreds of megabytes of log files spread across servers. With elmah.io, we store all of your log messages, notify you through popular channels like email, Slack, and Microsoft Teams, and help you fix errors fast.