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();23using (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:
- The Get method return type is Task<FileResult> to define that the return type should be a file.
- In the attributes I have [Produces("text/csv")] to inform the swagger docs that this API returns CSV files.
- The final response is calling return File.
1using CsvHelper;2using MyApp.Database;3using Microsoft.AspNetCore.Mvc;4using System.Globalization;5using Microsoft.EntityFrameworkCore;67namespace API.Controllers8{9 [Route("api/[controller]")]10 [ApiController]11 public class CSVExportController : ControllerBase12 {13 private readonly Context _context;1415 public CSVExportController(Context context)16 {17 _context = context;18 }1920 // GET: api/CSVExport21 [HttpGet]22 [ProducesResponseType(StatusCodes.Status200OK)]23 [Produces("text/csv")]24 public async Task<FileResult> Get()25 {2627 var data = await _context.ExportData.FromSqlRaw("EXEC [ExportData]").ToListAsync();2829 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 }3637 return File(memoryStream.ToArray(), "text/csv", $"Export-{DateTime.Now.ToString("s")}.csv");38 }39 }4041 }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.
