How can I automatically turn an IEnumerable into a csv file?

Question

I am trying to create a user control that accepts any generic list so I can iterate through it and create a CSV export. Is it possible to expose a public property that can accept any type? (i.e. List<Product>, List<Customer>, etc.) If yes, how?

public IEnumerable<T> AnyList { get; set; }

Here's what I have as far as the utility methods I have:

public static byte[] ToCsv<T>(string separator, IEnumerable<T> objectlist)
{
    //Type t = typeof(T); Deleted this line.

    //Here's the line of code updated.
    PropertyInfo[] propertyNames = objectlist.First().GetType().GetProperties();

    string header = String.Join(separator, propertyNames.Select(f => f.Name).ToArray());

    StringBuilder csvdata = new StringBuilder();
    csvdata.AppendLine(header);

    foreach (var o in objectlist)
        csvdata.AppendLine(ToCsvFields(separator, propertyNames, o));

    return Encoding.ASCII.GetBytes(csvdata.ToString());
}

public static string ToCsvFields(string separator, PropertyInfo[] fields, object o)
{
    StringBuilder linie = new StringBuilder();

    foreach (var f in fields)
    {
        if (linie.Length > 0)
            linie.Append(separator);

        var x = f.GetValue(o, null);

        if (x != null)
            linie.Append(x.ToString());
    }

    return linie.ToString();
}

Answer 1

As you want to "Create a CSV export" from a list of objects, you should be using reflection to work out the columns.

Latest update 12 Feb 2016:

It makes more sense to have the delimiter default to a comma, and useful to make the output of an initial header row optional. It also now supports both fields and simple properties by use of Concat:

public static IEnumerable<string> ToCsv<T>(IEnumerable<T> objectlist, string separator = ",", bool header = true)
{
    FieldInfo[] fields = typeof(T).GetFields();
    PropertyInfo[] properties = typeof(T).GetProperties();
    if (header)
    {
        yield return String.Join(separator, fields.Select(f => f.Name).Concat(properties.Select(p=>p.Name)).ToArray());
    }
    foreach (var o in objectlist)
    {
        yield return string.Join(separator, fields.Select(f=>(f.GetValue(o) ?? "").ToString())
            .Concat(properties.Select(p=>(p.GetValue(o,null) ?? "").ToString())).ToArray());
    }
}

so you then use it like this for comma delimited:

foreach (var line in ToCsv(objects))
{
    Console.WriteLine(line);
}

or like this for another delimiter (e.g. TAB):

foreach (var line in ToCsv(objects, "\t"))
{
    Console.WriteLine(line);
}

Practical examples

write list to a comma-delimited CSV file

using (TextWriter tw = File.CreateText("C:\testoutput.csv"))
{
    foreach (var line in ToCsv(objects))
    {
        tw.WriteLine(line);
    }
}

or write it tab-delimited

using (TextWriter tw = File.CreateText("C:\testoutput.txt"))
{
    foreach (var line in ToCsv(objects, "\t"))
    {
        tw.WriteLine(line);
    }
}

If you have complex fields/properties you will need to filter them out of the select clauses.

---

Previous updates

Final thoughts first (so it is not missed):

If you prefer a generic solution (this ensures the objects are of the same type):

public static IEnumerable<string> ToCsv<T>(string separator, IEnumerable<T> objectlist)
{
    FieldInfo[] fields = typeof(T).GetFields();
    PropertyInfo[] properties = typeof(T).GetProperties();
    yield return String.Join(separator, fields.Select(f => f.Name).Union(properties.Select(p=>p.Name)).ToArray());
    foreach (var o in objectlist)
    {
        yield return string.Join(separator, fields.Select(f=>(f.GetValue(o) ?? "").ToString())
            .Union(properties.Select(p=>(p.GetValue(o,null) ?? "").ToString())).ToArray());
    }
}

This one includes both public fields and public properties.

In general with reflection you do not need to know the type of objects in the list (you just must assume they are all the same type).

You could just use:

public IEnumerable<object> AnyList { get; set; }

The basic process for what you want to do goes something like:

• Obtain the type from the first object in the list (e.g. GetType()).
• Iterate the properties of that type.
• Write out the CSV header, e.g. based on the names of the property (or an attribute).
• For each item in the list...
  • Iterate the properties of that type
  • Get the value for each property (as an object)
  • Write out the ToString() version of the object with delimiters

You can use the same algorithm to generate a 2D array (i.e. if you want the control to display something like CSV in tabular/grid form).

The only issue you than have may be converting from IEnumerables/lists of specific types to an IEnumerable. In these instances just use .Cast<object> on your specific typed enumerable.

Update:

As you are using code from http://www.joe-stevens.com/2009/08/03/generate-a-csv-from-a-generic-list-of-objects-using-reflection-and-extension-methods/

You need to make the following change to his code:

// Make it a simple extension method for a list of object
public static string GetCSV(this List<object> list)
{
    StringBuilder sb = new StringBuilder();

    //Get the properties from the first object in the list for the headers
    PropertyInfo[] propInfos = list.First().GetType().GetProperties();

If you want to support an empty list, add second parameter (e.g. Type type) which is the type of object you expected and use that instead of list.First().GetType().

note: I don't see anywhere else in his code where T is referenced, but if I missed it the compiler will find it for you :)

Update (complete & simplified CSV generator):

public static IEnumerable<string> ToCsv(string separator, IEnumerable<object> objectlist)
{
    if (objectlist.Any())
    {
        Type type = objectlist.First().GetType();
        FieldInfo[] fields = type.GetFields();
        yield return String.Join(separator, fields.Select(f => f.Name).ToArray());
        foreach (var o in objectlist)
        {
            yield return string.Join(separator, fields.Select(f=>(f.GetValue(o) ?? "").ToString()).ToArray());
        }
    }
}

This has the benefit of a low memory overhead as it yields results as they occur, rather than create a massive string. You can use it like:

foreach (var line in ToCsv(",", objects))
{
    Console.WriteLine(line);
}

I prefer the generic solution in practice so have placed that first.

Answer 2

You would have to do something like this:

public class MyUserControl<T> : UserControl
{
    public IEnumerable<T> AnyList { get; set; }
}

From a C# view, this is perfectly fine. However, if you want to be able to declare your user control in ASPX Markup, then a user control cannot contain class-level type parameters. This is not possible:

<controls:MyUserControl<Customer> runat="server" ID="Foo" />

Now, if your user control will only be created in the code-behind, and not in markup, then this is fine:

this.SomePlaceHolder.Controls.Add(new MyUserControl<Customer>());

If you need / want the ability to declare your user control in ASPX markup, the best you could do is create an IEnumerable<object> property.

Answer 3

I found a nice solution from here

public static class LinqToCSV
{
    public static string ToCsv<T>(this IEnumerable<T> items)
        where T : class
    {
        var csvBuilder = new StringBuilder();
        var properties = typeof(T).GetProperties();
        foreach (T item in items)
        {
            string line = string.Join(",",properties.Select(p => p.GetValue(item, null).ToCsvValue()).ToArray());
            csvBuilder.AppendLine(line);
        }
        return csvBuilder.ToString();
    }

    private static string ToCsvValue<T>(this T item)
    {
        if(item == null) return "\"\"";

        if (item is string)
        {
            return string.Format("\"{0}\"", item.ToString().Replace("\"", "\\\""));
        }
        double dummy;
        if (double.TryParse(item.ToString(), out dummy))
        {
            return string.Format("{0}", item);
        }
        return string.Format("\"{0}\"", item);
    }
}

Apparently it handles commas and double quotes. I'm gonna use it.

Answer 4

To have generic properties, the class must be generic too:

public class MyClass<T>
{
   public IEnumerable<T> AnyList { get; set; }
}

Answer 5

Since IEnumerable is a base class of IEnumerable<T>, any properly written IEnumerable<T> class could be passed to a property of type IEnumerable. Since it sounds like you are only interested in the "ToString() part", and not the "T part", IEnumerable should work just fine.

Answer 6

This is working for me.

using System.Reflection;
using System.Text;
//***    


public void ToCSV<T>(IEnumerable<T> items, string filePath)
{
    var dataTable = new DataTable(typeof(T).Name);
    PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
    foreach (var prop in props)
        dataTable.Columns.Add(prop.Name, prop.PropertyType);

    foreach (var item in items)
    {
        var values = new object[props.Length];
        for (var i = 0; i < props.Length; i++)
        {
            values[i] = props[i].GetValue(item, null);
        }
        dataTable.Rows.Add(values);
    }

    StringBuilder fileContent = new StringBuilder();
    foreach (var col in dataTable.Columns)
        fileContent.Append(col.ToString() + ",");

    fileContent.Replace(",", System.Environment.NewLine, fileContent.Length - 1, 1);

    foreach (DataRow dr in dataTable.Rows)
    {
        foreach (var column in dr.ItemArray)
            fileContent.Append("\"" + column.ToString() + "\",");

        fileContent.Replace(",", System.Environment.NewLine, fileContent.Length - 1, 1);
    }

    try
    {
        System.IO.File.WriteAllText(filePath, fileContent.ToString());
    }
    catch (Exception)
    {

        throw;
    }
}