June 17, 2016 09:58
This article shows how to import and export csv data in an ASP.NET Core application. The InputFormatter and the OutputFormatter classes are used to convert the csv data to the C# model classes.
Code: https://github.com/damienbod/AspNetCoreCsvImportExport
2019-09-14: Updated to ASP.NET Core 3.0
2019-02-15: Updated to ASP.NET Core 2.2
2018-07-09: Updated to ASP.NET Core 2.1
2017-10-29: Support for Encoding and ASP.NET Core 2.0
2017.02.12: Updated to VS2017 msbuild
2016.11.25: Updated ASP.NET Core 1.1
2016.06.29: Updated to ASP.NET Core RTM
The LocalizationRecord class is used as the model class to import and export to and from csv data.
using System;
namespace AspNetCoreCsvImportExport.Model
{
public class LocalizationRecord
{
public long Id { get; set; }
public string Key { get; set; }
public string Text { get; set; }
public string LocalizationCulture { get; set; }
public string ResourceKey { get; set; }
}
}
The MVC Controller CsvTestController makes it possible to import and export the data. The Get method exports the data using the Accept header in the HTTP Request. Per default, Json will be returned. If the Accept Header is set to ‘text/csv’, the data will be returned as csv. The GetDataAsCsv method always returns csv data because the Produces attribute is used to force this. This makes it easy to download the csv data in a browser.
The Import method uses the Content-Type HTTP Request Header, to decide how to handle the request body. If the ‘text/csv’ is defined, the custom csv input formatter will be used.
using System.Collections.Generic;
using AspNetCoreCsvImportExport.Model;
using Microsoft.AspNetCore.Mvc;
namespace AspNetCoreCsvImportExport.Controllers
{
[Route("api/[controller]")]
public class CsvTestController : Controller
{
// GET api/csvtest
[HttpGet]
public IActionResult Get()
{
return Ok(DummyData());
}
[HttpGet]
[Route("data.csv")]
[Produces("text/csv")]
public IActionResult GetDataAsCsv()
{
return Ok( DummyData());
}
private static IEnumerable<LocalizationRecord> DummyData()
{
var model = new List<LocalizationRecord>
{
new LocalizationRecord
{
Id = 1,
Key = "test",
Text = "test text",
LocalizationCulture = "en-US",
ResourceKey = "test"
},
new LocalizationRecord
{
Id = 2,
Key = "test",
Text = "test2 text de-CH",
LocalizationCulture = "de-CH",
ResourceKey = "test"
}
};
return model;
}
// POST api/csvtest/import
[HttpPost]
[Route("import")]
public IActionResult Import([FromBody]List<LocalizationRecord> value)
{
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}
else
{
List<LocalizationRecord> data = value;
return Ok();
}
}
}
}
The csv input formatter implements the InputFormatter class. This checks if the context ModelType property is a type of IList and if so, converts the csv data to a List of Objects of type T using reflection. This is implemented in the read stream method. The implementation is very basic and will not work if you have more complex structures in your model class.
using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Reflection;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc.Formatters;
using Microsoft.Net.Http.Headers;
using System.Text;
using Newtonsoft.Json;
using System.Linq;
namespace AspNetCoreCsvImportExport.Formatters
{
/// <summary>
/// ContentType: text/csv
/// </summary>
public class CsvInputFormatter : InputFormatter
{
private readonly CsvFormatterOptions _options;
public CsvInputFormatter(CsvFormatterOptions csvFormatterOptions)
{
SupportedMediaTypes.Add(Microsoft.Net.Http.Headers.MediaTypeHeaderValue.Parse("text/csv"));
if (csvFormatterOptions == null)
{
throw new ArgumentNullException(nameof(csvFormatterOptions));
}
_options = csvFormatterOptions;
}
public override Task<InputFormatterResult> ReadRequestBodyAsync(InputFormatterContext context)
{
var type = context.ModelType;
var request = context.HttpContext.Request;
MediaTypeHeaderValue requestContentType = null;
MediaTypeHeaderValue.TryParse(request.ContentType, out requestContentType);
var result = ReadStream(type, request.Body);
return InputFormatterResult.SuccessAsync(result);
}
public override bool CanRead(InputFormatterContext context)
{
var type = context.ModelType;
if (type == null)
throw new ArgumentNullException("type");
return IsTypeOfIEnumerable(type);
}
private bool IsTypeOfIEnumerable(Type type)
{
foreach (Type interfaceType in type.GetInterfaces())
{
if (interfaceType == typeof(IList))
return true;
}
return false;
}
private object ReadStream(Type type, Stream stream)
{
Type itemType;
var typeIsArray = false;
IList list;
if (type.GetGenericArguments().Length > 0)
{
itemType = type.GetGenericArguments()[0];
list = (IList)Activator.CreateInstance(type);
}
else
{
typeIsArray = true;
itemType = type.GetElementType();
var listType = typeof(List<>);
var constructedListType = listType.MakeGenericType(itemType);
list = (IList)Activator.CreateInstance(constructedListType);
}
var reader = new StreamReader(stream, Encoding.GetEncoding(_options.Encoding));
bool skipFirstLine = _options.UseSingleLineHeaderInCsv;
while (!reader.EndOfStream)
{
var line = reader.ReadLine();
var values = line.Split(_options.CsvDelimiter.ToCharArray());
if(skipFirstLine)
{
skipFirstLine = false;
}
else
{
var itemTypeInGeneric = list.GetType().GetTypeInfo().GenericTypeArguments[0];
var item = Activator.CreateInstance(itemTypeInGeneric);
var properties = _options.UseNewtonsoftJsonDataAnnotations
? item.GetType().GetProperties().Where(pi => !pi.GetCustomAttributes<JsonIgnoreAttribute>().Any()).ToArray()
: item.GetType().GetProperties();
// TODO: Maybe refactor to not use positional mapping?, mapping by index could generate errors pretty easily đ
for (int i = 0; i < values.Length; i++)
{
properties[i].SetValue(item, Convert.ChangeType(values[i], properties[i].PropertyType), null);
}
list.Add(item);
}
}
if(typeIsArray)
{
Array array = Array.CreateInstance(itemType, list.Count);
for(int t = 0; t < list.Count; t++)
{
array.SetValue(list[t], t);
}
return array;
}
return list;
}
}
}
The csv output formatter is implemented using the code from Tugberk Ugurlu’s blog with some small changes. Thanks for this. This formatter uses ‘;’ to separate the properties and a new line for each object. The headers are added tot he first line.
using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Reflection;
using Microsoft.AspNetCore.Mvc.Formatters;
using System.ComponentModel.DataAnnotations;
using Newtonsoft.Json;
namespace AspNetCoreCsvImportExport.Formatters
{
/// <summary>
/// Original code taken from
/// http://www.tugberkugurlu.com/archive/creating-custom-csvmediatypeformatter-in-asp-net-web-api-for-comma-separated-values-csv-format
/// Adapted for ASP.NET Core and uses ; instead of , for delimiters
/// </summary>
public class CsvOutputFormatter : OutputFormatter
{
private readonly CsvFormatterOptions _options;
public string ContentType { get; private set; }
public CsvOutputFormatter(CsvFormatterOptions csvFormatterOptions)
{
ContentType = "text/csv";
SupportedMediaTypes.Add(Microsoft.Net.Http.Headers.MediaTypeHeaderValue.Parse("text/csv"));
_options = csvFormatterOptions ?? throw new ArgumentNullException(nameof(csvFormatterOptions));
}
protected override bool CanWriteType(Type type)
{
if (type == null)
throw new ArgumentNullException("type");
return IsTypeOfIEnumerable(type);
}
private bool IsTypeOfIEnumerable(Type type)
{
foreach (Type interfaceType in type.GetInterfaces())
{
if (interfaceType == typeof(IList))
return true;
}
return false;
}
/// <summary>
/// Returns the JsonProperty data annotation name
/// </summary>
/// <param name="pi">Property Info</param>
/// <returns></returns>
private string GetDisplayNameFromNewtonsoftJsonAnnotations(PropertyInfo pi)
{
if (pi.GetCustomAttribute<JsonPropertyAttribute>(false)?.PropertyName is string value)
{
return value;
}
return pi.GetCustomAttribute<DisplayAttribute>(false)?.Name ?? pi.Name;
}
public async override Task WriteResponseBodyAsync(OutputFormatterWriteContext context)
{
var response = context.HttpContext.Response;
Type type = context.Object.GetType();
Type itemType;
if (type.GetGenericArguments().Length > 0)
{
itemType = type.GetGenericArguments()[0];
}
else
{
itemType = type.GetElementType();
}
var streamWriter = new StreamWriter(response.Body, Encoding.GetEncoding(_options.Encoding));
if (_options.UseSingleLineHeaderInCsv)
{
var values = _options.UseNewtonsoftJsonDataAnnotations
? itemType.GetProperties().Where(pi => !pi.GetCustomAttributes<JsonIgnoreAttribute>(false).Any()) // Only get the properties that do not define JsonIgnore
.Select(GetDisplayNameFromNewtonsoftJsonAnnotations)
: itemType.GetProperties().Select(pi => pi.GetCustomAttribute<DisplayAttribute>(false)?.Name ?? pi.Name);
await streamWriter.WriteLineAsync(string.Join(_options.CsvDelimiter, values));
}
foreach (var obj in (IEnumerable<object>)context.Object)
{
//IEnumerable<ObjectValue> vals;
var vals = _options.UseNewtonsoftJsonDataAnnotations
? obj.GetType().GetProperties()
.Where(pi => !pi.GetCustomAttributes<JsonIgnoreAttribute>().Any())
.Select(pi => new
{
Value = pi.GetValue(obj, null)
})
: obj.GetType().GetProperties().Select(
pi => new
{
Value = pi.GetValue(obj, null)
});
string valueLine = string.Empty;
foreach (var val in vals)
{
if (val.Value != null)
{
var _val = val.Value.ToString();
//Check if the value contains a comma and place it in quotes if so
if (_val.Contains(","))
_val = string.Concat("\"", _val, "\"");
//Replace any \r or \n special characters from a new line with a space
if (_val.Contains("\r"))
_val = _val.Replace("\r", " ");
if (_val.Contains("\n"))
_val = _val.Replace("\n", " ");
valueLine = string.Concat(valueLine, _val, _options.CsvDelimiter);
}
else
{
valueLine = string.Concat(valueLine, string.Empty, _options.CsvDelimiter);
}
}
await streamWriter.WriteLineAsync(valueLine.TrimEnd(_options.CsvDelimiter.ToCharArray()));
}
await streamWriter.FlushAsync();
}
}
}
The custom formatters need to be added to the MVC middleware, so that it knows how to handle media types ‘text/csv’.
public void ConfigureServices(IServiceCollection services)
{
var csvFormatterOptions = new CsvFormatterOptions();
services.AddControllers(options =>
{
options.InputFormatters.Add(new CsvInputFormatter(csvFormatterOptions));
options.OutputFormatters.Add(new CsvOutputFormatter(csvFormatterOptions));
options.FormatterMappings.SetMediaTypeMappingForFormat("csv", MediaTypeHeaderValue.Parse("text/csv"));
}).SetCompatibilityVersion(CompatibilityVersion.Version_3_0);
}
When the data.csv link is requested, a csv type response is returned to the client, which can be saved. This data contains the header texts and the value of each property in each object. This can then be opened in excel.
http://localhost:10336/api/csvtest/data.csv
Id;Key;Text;LocalizationCulture;ResourceKey 1;test;test text;en-US;test 2;test;test2 text de-CH;de-CH;test
This data can then be used to upload the csv data to the server which is then converted back to a C# object. I use fiddler, postman or curl can also be used, or any HTTP Client where you can set the header Content-Type.
http://localhost:10336/api/csvtest/import User-Agent: Fiddler Content-Type: text/csv Host: localhost:10336 Content-Length: 110 Id;Key;Text;LocalizationCulture;ResourceKey 1;test;test text;en-US;test 2;test;test2 text de-CH;de-CH;test
The following image shows that the data is imported correctly.
Notes
The implementation of the InputFormatter and the OutputFormatter classes are specific for a list of simple classes with only properties. If you require or use more complex classes, these implementations need to be changed.
Links
https://www.nuget.org/packages/WebApiContrib.Core.Formatter.Csv/
http://www.strathweb.com/2014/11/formatters-asp-net-mvc-6/
https://wildermuth.com/2016/03/16/Content_Negotiation_in_ASP_NET_Core
Posted by damienbod
Categories: .NET, ASP.NET Core, ASPNET5, dotnet, MVC, Web
Tags: ASP.NET Core, aspnetcore, Content Type, CSV, dotnet, InputFormatter, Media formatter, MediaTypeFormatter, MVC, MVC 6, MVC Core, OutputFormatter, reflection, Web API, WebApi
Mobile Site | Full Site
Get a free blog at WordPress.com Theme: WordPress Mobile Edition by Alex King.
[…] Lâimportation et lâexportation de fichiers .csv avec .NET Core. […]
By Les liens de la semaine â Ădition #190 | French Coding on June 27, 2016 at 11:01
[…] I want to export CSV in ASP.NET Core with other NonâEnglish characters UTF-8. I found solution here Solution For English Characters […]
By Export CSV in ASP.NET Core with other NonâEnglish characters UTF-8 - Technology on September 18, 2016 at 03:30
I am getting an error on the following line: CsvInputFormatter.cs line 68: list = (IList)Activator.CreateInstance(itemType); … it says unable to cast object type (MyModel which is just two string properties) to IList… not sure why it’s attempting to cast a single model value into an IList though. I copied everything exactly so not sure what the problem might be.
By Liberty Survival (@libertysurvival) on October 11, 2016 at 21:12
Hi Librerty, we’ll continue this on gitHub:
https://github.com/damienbod/AspNetCoreCsvImportExport/issues/4
Greetings Damien
By damienbod on October 12, 2016 at 02:57
I have a project ASP.NET CORE need read data from excel file(.xlsx or .xls). I try Epplus and System.Data.OleDb… but asp.net core not support.
Can you help me?
By ThanhXuan on November 29, 2016 at 09:59
Yes only csv formatters work at present. If you only require csv, which you can then open with excel, then you can use the NuGet package or this code. If the Epplus is required, (Excel formatting etc), you could target net46 only and you can use it.
Greetings Damien
By damienbod on November 29, 2016 at 10:04
I have a project ASP.NET CORE need read data from excel file(.xlsx or .xls). I try Epplus and System.Data.OleDb⌠but asp.net core not support.
Can you help me?
By thanhxuanhd007 on November 29, 2016 at 09:59
There is an unofficial version of EPPlus exists named EPPlus.core which supprots XLSX on ASP.NET Core. Take a look at this post
http://www.talkingdotnet.com/import-export-xlsx-asp-net-core/
By Virendra Dugar on December 7, 2016 at 05:30
Line 123 of the CsvOutputFormatter will truncate everything in the line until the last null value property. It should be changed to
_valueLine = string.Concat(_valueLine, string.Empty, _options.CsvDelimiter);
Question: In the CsvOutputFormatter, why did you call “GetValue(obj, null)” on the PropertyInfo objects instead of just using “GetValue(obj)”?
By Svarr on February 3, 2017 at 15:11
Thanks for the improvement, will have a look at the second point, I’ll add you comment to a gitHub issue and will follow this up when I get time
Greetings Damien
By damienbod on February 3, 2017 at 16:26
Hi Svarr
Added your improvements, thanks for the feedback.
https://github.com/damienbod/AspNetCoreCsvImportExport
greetings Damien
By damienbod on February 12, 2017 at 15:35
using asp.net core 2, here is the error i get
System.InvalidCastException: ‘Unable to cast object of type ‘CryptoCal.Support.LocalizationRecord’ to type ‘System.Collections.IList’.’
it happens on list = (IList)Activator.CreateInstance(itemType);
By K on September 20, 2017 at 22:29
Hi,
Thanks for the code, works great !
I saw the TODO comment in the code, in order to manage the headers by name, and not by indexes.
I added some extra implementation to complete it, and I wanted to share it with you, I created a Patch proposition on GitHub
By Tim on July 2, 2018 at 12:53
thanks Tim , which repo?
Gruss Damien
By damienbod on July 3, 2018 at 05:25
How to use this response to get download file at client side
By S.K. Bharat on September 14, 2018 at 10:46
Do anyone receive this basic and weird error?
The type or namespace name ‘CsvFormatterOptions’ could not be found (are you missing a using directive or an assembly reference?)
By Arul M on December 13, 2018 at 11:06
> Do anyone receive this basic and weird error?
> The type or namespace name âCsvFormatterOptionsâ could not be found (are you missing a using directive or an assembly reference?)
@Arul, Please run WebApiContrib.Core.Formatter.Csv; for more information
https://www.nuget.org/packages/WebApiContrib.Core.Formatter.Csv/2.0.5
By Rohil Patel on December 27, 2018 at 13:23
Please provide a project download thanks.
By Nick Donnelly on March 25, 2021 at 15:44
[…] Reference: https://damienbod.com/2016/06/17/import-export-csv-in-asp-net-core/?WT.mc_id=DP-MVP-36769 […]
By ASP.NET Blazor Call Web Api that return text/csv and save the file | Chanmingman's Blog on August 29, 2021 at 02:45