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.