Exporting to CSV in C#

Every once in a while, our application is required to export the data it generates to some sort of format. CSV (comma-separated values) is one of the most popular formats to export/import data.

As the first post of my blog, I will show you a quick way to generate a CSV  file from a generic collection of objects in C#.

Suppose you have a domain object like the following:

using System;
using System.ComponentModel.DataAnnotations;

public class UserDto
{
	public int Id { get; set; }

	[Display(ResourceType = typeof(ModelStrings), Name = "Username", Order = 1)]
	public string Username { get; set; }

	[Required]
	[Display(ResourceType = typeof(ModelStrings), Name = "FirstName", Order = 2)]
	public string FirstName { get; set; }

	[Required]
	[Display(ResourceType = typeof(ModelStrings), Name = "LastName", Order = 3)]
	public string LastName { get; set; }

	[Display(ResourceType = typeof(ModelStrings), Name = "Status", Order = 4)]
	public bool IsActive { get; set; }

	[Display(ResourceType = typeof(ModelStrings), Name = "LastLogin", Order = 5)]
	public DateTime? LastLogin { get; set; }
}

Note: The code above assumes there exists a resource file called ModelStrings with all the specified entries (Username, FirstName, etc.)

Then we can convert a collection of such objects (or objects of any class) using this helper class:

Update (8/7/2014): Based on Goody’s feedback, I decided to add code to handle special characters properly.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;

namespace AxSoft.Data
{
    public static class CsvHelper
    {
        private const string Quote = "\"";
        private const string EscapedQuote = "\"\"";
        private static readonly char[] EscapableCharacters = { '"', ',', '\r', '\n' };

        public static void ToCsv<T>(IEnumerable<T> collection, Stream stream, params string[] onlyFields) where T : class
        {
            Dictionary<PropertyInfo, string> fieldNames = null;
            var sw = new StreamWriter(stream, Encoding.UTF8);

            foreach (var item in collection)
            {
                // Only on the first iteration we get the list of properties from the object type
                // We use a dictionary of <PropertyInfo, string> instead of just a list of PropertyInfo, 
                // because we extract the display name of the property (if exists) to use it as the "column" header
                if (fieldNames == null)
                {
                    fieldNames = GetProperties(typeof(T), onlyFields);
                    // Write the column headers
                    WriteRow(sw, fieldNames.Select(v => v.Value));
                }

                var current = item;
                var valueList = fieldNames.Keys.Select(prop => prop.GetValue(current, null))
                    .Select(Convert.ToString);

                WriteRow(sw, valueList);
            }

            // Reset the stream position to the beginning
            stream.Seek(0, SeekOrigin.Begin);
        }

        private static Dictionary<PropertyInfo, string> GetProperties(Type type, string[] onlyFields)
        {
            var properties = type.GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.FlattenHierarchy)
                                 .Where(prop => IsSimpleOrNullableType(prop.PropertyType))
                                 .OrderBy(prop =>
                                     {
                                         var displayAttr = prop.GetCustomAttributes(typeof(DisplayAttribute), true).FirstOrDefault() as DisplayAttribute;
                                         return displayAttr != null ? displayAttr.Order : int.MaxValue;
                                     });

            // If the entity has MetadataTypeAttribute's, use them
            var metadata = (MetadataTypeAttribute[])type.GetCustomAttributes(typeof(MetadataTypeAttribute), true);

            var names = new Dictionary<PropertyInfo, string>();
            foreach (var property in properties)
            {
                if (onlyFields.Length == 0 || onlyFields.Contains(property.Name, StringComparer.InvariantCultureIgnoreCase))
                {
                    var text = GetDisplayName(property, metadata);

                    names.Add(property, text);
                }
            }

            return names;
        }

        private static string GetDisplayName(PropertyInfo property, IEnumerable<MetadataTypeAttribute> metadata)
        {
            // Extract the display name from the DisplayAttribute on the object or on any of the MetadataTypeAttribute's 
            // it may contain
            var displayText = metadata.Select(m => m.MetadataClassType.GetProperty(property.Name))
                    .Where(p => p != null)
                    .SelectMany(p => (DisplayAttribute[])p.GetCustomAttributes(typeof(DisplayAttribute), true))
                    .Concat((DisplayAttribute[])property.GetCustomAttributes(typeof(DisplayAttribute), true))
                    .Select(m => m.GetName())
                    .FirstOrDefault(n => !string.IsNullOrEmpty(n));

            // Return the display text if found, otherwise return the property name
            return displayText ?? property.Name;
        }
 
        private static bool IsSimpleOrNullableType(Type t)
        {
            if (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>))
            {
                t = Nullable.GetUnderlyingType(t);
            }
 
            return IsSimpleType(t);
        }
 
        private static bool IsSimpleType(Type t)
        {
            return t.IsPrimitive || t.IsEnum || t == typeof(string) || t == typeof(Decimal) || t == typeof(DateTime) || t == typeof(Guid);
        }

        private static void WriteRow(TextWriter sw, IEnumerable<string> values)
        {
            int index = 0;
            foreach (var value in values)
            {
                if (index > 0)
                {
                    sw.Write(",");
                }

                WriteValue(sw, value);
                index++;
            }

            sw.Write(Environment.NewLine);
            sw.Flush();
        }

        private static void WriteValue(TextWriter sw, string value)
        {
            bool needsEscaping = value.IndexOfAny(EscapableCharacters) >= 0;

            if (needsEscaping)
            {
                sw.Write(Quote);
                sw.Write(value.Replace(Quote, EscapedQuote));
                sw.Write(Quote);
            }
            else
            {
                sw.Write(value);
            }
        }
    }
}

This helper class has the following features:

  1. Uses the user-friendly display name of the property if present (DisplayAttribute.Name property).
  2. Respects the order of the properties/columns (DisplayAttribute.Order property).
  3. Uses the DisplayAttribute from the class itself or from the MetadataTypeAttribute the class it’s decorated with (if any).

Note: The onlyFields params array lets us specify which properties we want to output to the CSV. If you want to include them all, just leave that empty.

Streams are used because they give us more control over where we want the results dumped, but if we want to get the resulting CSV as a string, we can add the following method:

public static string ToCsv<T>(IEnumerable<T> collection, params string[] onlyFields) where T : class
{
    using (var stream = new MemoryStream())
    {
        ToCsv(collection, stream, onlyFields);
        using (var reader = new StreamReader(stream))
        {
            return reader.ReadToEnd();
        }
    }
}

Finally, we can consume our helper class in ways like the following:

As a download in ASP .NET MVC

List<UserDto> users = /* Fetch the object list here */;
var outputStream = new MemoryStream();
CsvHelper.ToCsv(users, outputStream);
return File(outputStream, "text/csv", "Users.csv");

As a local file

List<UserDto> users = /* Fetch the object list here */;
using (var fileStream = File.Create("myFile.csv"))
{
	CsvHelper.ToCsv(users, fileStream);
}

16 thoughts on “Exporting to CSV in C#

    • Mario, estás un poco perdido en ese aspecto. ToCsv es un método genérico y T es el parámetro genérico. Las clases y métodos genéricos permiten delegar la especificación de uno o más tipos hasta el momento en que la clase o método son invocados. En este caso, ToCsv puede ser invocado con un IEnumerable de tipo T, que puede ser cualquier reference type (ya que existe un constraint where T : class).

      • Al final mostré algunos ejemplos de cómo se puede utilizar el método, incluyendo
        CsvHelper.ToCsv(users, outputStream);
        Donde users es de tipo List<UserDto>, pero puede ser cualquier objeto que implemente IEnumerable<T> (o si lo quieres ver de otra forma, cualquier colección genérica).

      • public static string ToCsv(IEnumerable collection, params string[] onlyFields) where T : class
        {
        using (var stream = new MemoryStream())
        {
        ToCsv(collection, stream, onlyFields);
        using (var reader = new StreamReader(stream))
        {
        return reader.ReadToEnd();
        }
        }
        }

        En el mismo metodo invocas a ese metodo a la torre sta medio confuso xD

  1. Wonderful code. I have a bunch of varchars with commas and it’s mucking up the final CSV. Any tips on how to wrap the values in “quotes” to prevent the values getting out of sync with the display names in the CSV?

    • Good point. Although I haven’t faced that issue, it has crossed my mind before. I would need to add some logic to escape some characters, including commas and carriage returns. I will update the code soon. Stay tuned.

      • It’s amazing code none the less.

        Here’s a quick fix I came up with:

        var valueList = fieldNames.Keys.Select(prop => prop.GetValue(item, null)).ToList();
        sw.Write(“\””);
        sw.Write(string.Join(“\”,\””, valueList));
        sw.Write(“\””);
        sw.Write(Environment.NewLine);
        sw.Flush();

        Not pretty…but it seems to work🙂

      • Sure, it works! However there are other characters that need special treatment, too, like quotation marks and carriage returns. It all depends on how robust you need the solution to be🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s