Help converting generic List<T> to Excel spreadsheet

Question

I am trying to create a function that accepts a generic List<T> and iterates the list returning an excel file byte[]. The function needs be able to determine the objects properties. So if i pass a List<person> and person has properties first, last, age, etc i need to be able to determine the property names in order to create the excel column headers and then i need to iterate the list to assign the property values to the column cells. Can anyone point me to some sample code for working with List<T> in a generic function?

Answer 1

The easiest way is probably to convert your List to a DataTable, then convert the DataTable to an Excel spreadsheet.

The second link writes the spreadsheet directly out to an ASP.NET Response, this could easily be adapted to return a byte[].

Answer 2

Aside: for getting columns back in a known order: there is no defined order for members, except that which you create. For example (from MSDN):

The GetProperties method does not return properties in a particular order, such as alphabetical or declaration order. Your code must not depend on the order in which properties are returned, because that order varies.

If you don't need to rely on the order, either reflection or TypeDescriptor would do; for example (noting that this writes TSV text, not byte[] - my interpretation is that the problem is getting the data, not writing the Excel):

static void WriteTsv<T>(this IEnumerable<T> data, TextWriter output)
{
    PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
    foreach (PropertyDescriptor prop in props)
    {
        output.Write(prop.DisplayName); // header
        output.Write("\t");
    }
    output.WriteLine();
    foreach (T item in data)
    {
        foreach (PropertyDescriptor prop in props)
        {
            output.Write(prop.Converter.ConvertToString(
                 prop.GetValue(item)));
            output.Write("\t");
        }
        output.WriteLine();
    }
}

If you need order, you would need to either:

• pass it in (for example, as a params string[] propertyNames)
• use attributes on the properties
• use alphabetical

The TypeDescriptor approach above has the advantages (over GetType().GetProperties()) that:

• it works with custom object models (DataView, for example, if you use IList)
• you can tweak the implementation for performance - for example HyperDescriptor (useful if you are doing this lots)

Answer 3

Use an interface that is supported by your collection, such as IEnumerable:

public byte[] Process(IEnumerable input) {
    foreach (var elem in input) {
        foreach (PropertyInfo prop in elem.GetType().GetProperties()) {
            Object value = prop.GetValue(elem, null);
            // add value to byte[]
        }
    }
    return bytes;
}

Answer 4

Thanks Marc! I badly needed this solution.