Export CSV in ASP.NET Core Web API

Export CSV in ASP.NET Core Web API

A common request for any application containing data, is for that data to be exportable to a CSV file so that users can easily use the data applications such as Excel.

Over the years I've seen many developers create and even try to read CSVs by assuming a CSV is as simple as a bunch of values with a comma between them. Now while a data in this format is a valid CSV, it's not the only possibility. For instance what if one of the values contains a comma? The CSVs spec supports all these scenarios and as a result, if you want to do anything with CSV files, I'd always use a library that has already been written to handle it for you.

Creating a CSV

The first part of exporting a CSV in ASP.NET is to first have a CSV file to export. To create the file I'm using a library called CsvHelper.

As good as this is, quite annoyingly the only instructions are for saving a file to a disc. While I'm sure this is relevant for many people, I'd rather hold it in memory and then return it.

In this example, I'm doing an EF Core query to a stored procedure to create my result set and then using a stream writer to a memory stream for the CSV writer to write to.

1var data = await _context.ExportData.FromSqlRaw("EXEC [ExportData]").ToListAsync();
2

3using (var memoryStream = new MemoryStream())
4{
5    using (var streamWriter = new StreamWriter(memoryStream))
6    using (var csvWriter = new CsvWriter(streamWriter, CultureInfo.InvariantCulture))
7    {
8       csvWriter.WriteRecords(data);
9    }
10}

Export the CSV File from a Controller

I now have some code which has loaded my CSV into a memory stream. The next part is to create a controller that returns the file.

This is a regular controller inheriting from ControllerBase, but there's a few things to note:

  1. The Get method return type is Task<FileResult> to define that the return type should be a file.
  2. In the attributes I have [Produces("text/csv")] to inform the swagger docs that this API returns CSV files.
  3. The final response is calling return File.
1using CsvHelper;
2using MyApp.Database;
3using Microsoft.AspNetCore.Mvc;
4using System.Globalization;
5using Microsoft.EntityFrameworkCore;
6

7namespace API.Controllers
8{
9    [Route("api/[controller]")]
10    [ApiController]
11    public class CSVExportController : ControllerBase
12    {
13        private readonly Context _context;
14

15        public CSVExportController(Context context)
16        {
17            _context = context;
18        }
19

20        // GET: api/CSVExport
21        [HttpGet]
22        [ProducesResponseType(StatusCodes.Status200OK)]
23        [Produces("text/csv")]
24        public async Task<FileResult> Get()
25        {
26

27            var data = await _context.ExportData.FromSqlRaw("EXEC [ExportData]").ToListAsync();
28

29            using (var memoryStream = new MemoryStream())
30            {
31                using (var streamWriter = new StreamWriter(memoryStream))
32                using (var csvWriter = new CsvWriter(streamWriter, CultureInfo.InvariantCulture))
33                {
34                    csvWriter.WriteRecords(data);
35                }
36

37                return File(memoryStream.ToArray(), "text/csv", $"Export-{DateTime.Now.ToString("s")}.csv");
38            }
39        }
40

41    }
42}
43

In the parameters for the File method I'm calling .ToArray() on the memory stream to turn it into a byte array. I'm also specifying the content type and a filename containing the current datetime. The .ToString("s") on the datetime will format the date into a sortable string.