Fluent CSV/XML Exporter for List

• Download demo project and source code - 22.3 KB

Introduction

I don't know about you, but I work with lots and lots of lists at work and from time to time, I have the requirement to export some List<T> to a CSV file, and I used to just write a little helper method that use a bit of Reflection that got out all the public properties of the T objects stored in my list and then get the names of the properties and use those as headers, and then go through the List<T> and get the values out using some Reflection over the properties.

This works fine, but it is just not that generic a solution, and only really worked with that particular type of T that I was storing in the list, and all the code to decide what columns to use and what the column headers should be was hidden away from the users of the method that did the export.

So I had a think about this and thought there must be a better way, so this article represents a generic solution to this problem where I can do the following:

1. Could be used as an extension method to any IEnumerable<T>
2. Let the using code specify what columns should be exported
3. Use a Fluent API (as they are all the rage these days)
4. Allow auto header named to be obtained using Expression trees
5. Allow the the user to supply custom header names
6. Allow the user to also supply custom format strings for the data being exported
7. Allow the data to be exported to a CSV file or allow the data to be exported to a CSV string
8. Allow the exporter to be able to deal with NULL values
9. Allow the user to specify a custom separator (if none is supplied, a comma "," is used)

What can be done with it

If we assume we have a default object to use as T (which can be any class that has properties) that is like this:

public class Person
{
    public int Age { get; set; }
    public String Name { get; set; }

    public Person(int age, String name)
    {
        this.Age = age;
        this.Name = name;
    }
}

And we have a List<Person> object configured as follows:

List<Person> people = new List<Person>();
people.Add(new Person(1, "sam"));
people.Add(new Person(2, "john"));
people.Add(new Person(3, "paul"));

Then using my little exporter, we can carry out the following functions, where the code snippets below are actually working examples of how to use it for the scenarios outlined below.

Export to CSV string

We can export the data of a List<Person> to a CSV string as follows:

Use default headers and use no formatting on the exported data

Note: In this example, I supply a custom separator of ":" to use.

//Get it as a String result, using default Headers, and default Columns, 
//and custom seperator
using (StringWriter writer = new StringWriter())
{
    people.GetExporter(":")
        .AddExportableColumn((x) => x.Age)
        .AddExportableColumn((x) => x.Name)
        .AsCSVString(writer);

    String resultsWithDefaultHeadersAndDefaultColumns = writer.ToString();
}

Which produces the following output:

Age:Name
  1:sam
  2:john
  3:paul

Use default headers and use formatting on the exported data

//Get it as a String result, using automatic Headers, but formatted Columns, 
//and standard "," seperator
using (StringWriter writer = new StringWriter())
{
    people.GetExporter()
        .AddExportableColumn((x) => x.Age, customFormatString: "The Person Age Is {0}")
        .AddExportableColumn((x) => x.Name, customFormatString: "The Persons Name {0}")
        .AsCSVString(writer);

    String resultsWithDefaultHeadersAndFormattedColumns = writer.ToString();
}

Which produces the following output:

Age,Name
  The Person Is 1,The Person Name Is sam
  The Person Is 2,The Person Name Is john
  The Person Is 3,The Person Name Is paul

Use custom headers and use no formatting on the exported data

//Get it as a String result, using custom Headers, but default Columns
//and standard "," seperator
using (StringWriter writer = new StringWriter())
{
    people.GetExporter()
        .AddExportableColumn((x) => x.Age, headerString: "AgeColumn")
        .AddExportableColumn((x) => x.Name, headerString: "NameColumn")
        .AsCSVString(writer);

    String resultsWithCustomHeadersAndDefaultColumns = writer.ToString();
}

Which produces the following output:

AgeColumn,NameColumn
  1,sam
  2,john
  3,paul

Export to CSV file

We can also choose to export to a CSV file using the little helper class, where we can do things like this:

Use default headers and use no formatting on the exported data

//Get it as a CSV file, using default Headers, and default Columns, 
//and standard "," seperator
using (StreamWriter writer = 
     new StreamWriter(@"c:\temp\exportedWithDefaultHeadersAndDefaultColumns.csv"))
{
    people.GetExporter()
        .AddExportableColumn((x) => x.Age)
        .AddExportableColumn((x) => x.Name)
        .AsCSVString(writer);
}

Use default headers and use formatting on the exported data, which produces the following

//Get it as a CSV file, using automatic Headers, but formatted Columns, 
//and standard "," seperator
using (StreamWriter writer = 
	new StreamWriter(@"c:\temp\exportedWithDefaultHeadersAndFormattedColumns.csv"))
{
    people.GetExporter()
        .AddExportableColumn((x) => x.Age, customFormatString: "The Person Age Is {0}")
        .AddExportableColumn((x) => x.Name, customFormatString: "The Persons Name {0}")
        .AsCSVString(writer);
}

Use custom headers and use no formatting on the exported data

//Get it as a CSV file, using custom Headers, but default Columns
//and standard "," seperator
using (StreamWriter writer = 
	new StreamWriter(@"c:\temp\erxportedWithCustomHeadersAndDefaultColumns.csv"))
{
    people.GetExporter()
        .AddExportableColumn((x) => x.Age, headerString: "AgeColumn")
        .AddExportableColumn((x) => x.Name, headerString: "NameColumn")
        .AsCSVString(writer);
}

Export to XML file

We can also export to an XML file, thanks to some extra effort of one of the readers who posted to this article's forum. We can do something like this:

using (XmlTextWriter sw = new XmlTextWriter("LastResults.xml", 
	System.Text.Encoding.UTF8))
{
    people.GetExporter()
        .AddExportableColumn((x) => x.Age, headerString: "AgeColumn")
        .AddExportableColumn((x) => x.Name, headerString: "NameColumn")
        .ToXML(sw);
}

Note: We must have the following XSL file in the same folder as the binaries. The downloadable code includes this file for you.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="html" version="4.0" 
     encoding="ISO-8859-1" indent="no" omit-xml-declaration="yes"/>

  <xsl:template match="dump">
    <head>
      <meta NAME="ROBOTS" CONTENT="NOINDEX,NOFOLLOW"/>
      <meta HTTP-EQUIV="PRAGMA" CONTENT="NO-CACHE"/>
      <title>
        Dump (<xsl:value-of select="count(/dump/item)"/> items)
      </title>
      <style media="screen" type="text/css">
        body {
        background-color: #F1F0EB;
        font-family: Verdana, Tahoma, Helvetica, sans-serif;
        font-size: .8em;
        margin: 10, 10, 10, 10;
        }
        h1, h2, h3 {
        font-family: Tahoma, Arial, sans-serif;
        font-weight: bolder;
        }
        h2 {
        font-size: 2em;
        color: #A0A0A0;
        }
        table {
        border-width: 1px;
        border-spacing: 2px;
        border-style: solid;
        border-color: gray;
        border-collapse: separate;
        background-color: #F1F0EB;
        }
        table th {
        border-width: 0px;
        font-size: .9em;
        line-height: 1.2em;
        padding: 1px;
        border-style: none;
        border-color: gray;
        background-color: #F1F0EB;
        -moz-border-radius: ;
        }
        table td {
        border-width: 0px;
        padding: 1px;
        font-size: .9em;
        line-height: 1.2em;
        border-style: none;
        border-color: gray;
        background-color: white;
        -moz-border-radius: ;
        }
      </style>
    </head>
    <html>
      <body>
        <h2>List dump</h2>
        <p>
          Date: <xsl:value-of select="/dump/@date"/>
        </p>
        <table border="1">
          <thead>
            <tr bgcolor="#9acd32">
              <xsl:apply-templates select="item[1]/*" mode="th" />
            </tr>
          </thead>
          <tbody>
            <xsl:apply-templates select="item" />
          </tbody>
        </table>
      </body>
    </html>
  </xsl:template>

  <xsl:template match="item">
    <tr>
      <xsl:apply-templates select="*" mode="td" />
    </tr>

  </xsl:template>
  <xsl:template match="item/*" mode="th">
    <th>
      <xsl:value-of select="local-name()" />
    </th>
  </xsl:template>

  <xsl:template match="item/*" mode="td">
    <td>
      <xsl:value-of select="." />
    </td>
  </xsl:template>
</xsl:stylesheet>

Which when examined in a browser will look like this:

How does it work

Well, as some of you may have already guessed, the secret lies in using Expression trees/optional parameters and named parameters. But before we get into that, one thing of note is that by using this sort of technique, the control of what to export is firmly in the hands of the consumer of the export helper class contained in this article, which as I said is important, as by doing this, it is totally generic, and allows the user to specify what columns they want to export and also the order in which they are exported. And as can also be seen from the above examples, the user can choose to use custom column headers/format strings, or have them inferred (via expression tree parsing).

One reader suggested re-factoring it to allow the exporter as an extension method on IEnumerable<T>, so I added that. Thanks for that, David Sehnal.

The export also uses a Fluent interface which I talked about a bit in one of my older articles, which you can read here: http://www.codeproject.com/KB/WPF/fluentAPI.aspx.

Anyway without further ado, here is the complete code for the exporter code presented in this article:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Linq.Expressions;
using System.Reflection;
using System.IO;
using System.Xml;

namespace FluentListExporterColumns
{

    public static class IEnumerableExtensions
    {
        /// <summary>
        /// Exporter extension method for all IEnumerableOfT
        /// </summary>
        public static FluentExporter<T> GetExporter<T>(
            this IEnumerable<T> source, String seperator = ",") where T : class
        {
            return new FluentExporter<T>(source, seperator);
        }
    }

    /// <summary>
    /// Represents custom exportable column with a expression for the property name
    /// and a custom format string
    /// </summary>
    public class ExportableColumn<T>
    {
        public Expression<Func<T, Object>> Func { get; private set; }
        public String HeaderString { get; private set; }
        public String CustomFormatString { get; private set; }

        public ExportableColumn(
            Expression<Func<T, Object>> func, 
            String headerString = "", 
            String customFormatString = "")
        {
            this.Func = func;
            this.HeaderString = headerString;
            this.CustomFormatString = customFormatString;
        }
    }

    /// <summary>
    /// Exporter that uses Expression tree parsing to work out what values to export for 
    /// columns, and will use additional data as specified in the List of ExportableColumn
    /// which defines whethere to use custom headers, or formatted output
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class FluentExporter<T> where T : class
    {
        private List<ExportableColumn<T>> columns = 
          new List<ExportableColumn<T>>();
        private Dictionary<Expression<Func<T, Object>>, Func<T, Object>> 
          compiledFuncLookup = 
          new Dictionary<Expression<Func<T, Object>>, Func<T, Object>>();
        private List<String> headers = new List<String>();
        private IEnumerable<T> sourceList;
        private String seperator;
        private bool doneHeaders;


        public FluentExporter(IEnumerable<T> sourceList, String seperator = ",")
        {
            this.sourceList = sourceList;
            this.seperator = seperator;
        }

        public FluentExporter<T> AddExportableColumn(
            Expression<Func<T, Object>> func,
            String headerString = "",
            String customFormatString = "")
        {
            columns.Add(new ExportableColumn<T>(
                             func,headerString,customFormatString));
            return this;
        }

        /// <summary>
        /// Export all specified columns as a string, 
        /// using seperator and column data provided
        /// where we may use custom or default headers 
        /// (depending on whether a custom header string was supplied)
        /// where we may use custom fomatted column data or default data 
        /// (depending on whether a custom format string was supplied)
        /// </summary>
        public void AsCSVString(TextWriter writer)
        {
            if (columns.Count == 0)
                throw new InvalidOperationException(
                    "You need to specify at least one column to export value");

            int i = 0;
            foreach (T item in sourceList)
            {
                List<String> values = new List<String>();
                foreach (ExportableColumn<T> exportableColumn in columns)
                {
                    if (!doneHeaders)
                    {
                        if (String.IsNullOrEmpty(exportableColumn.HeaderString))
                        {
                            headers.Add(GetPropertyName(exportableColumn.Func));
                        }
                        else
                        {
                            headers.Add(exportableColumn.HeaderString);
                        }

                        Func<T, Object> func = exportableColumn.Func.Compile();
                        compiledFuncLookup.Add(exportableColumn.Func, func);
                        if (!String.IsNullOrEmpty(exportableColumn.CustomFormatString))
                        {
                            var value = func(item);
                            values.Add(value != null ?
                                String.Format(exportableColumn.CustomFormatString, 
                                value.ToString()) : "");

                        }
                        else
                        {
                            var value = func(item);
                            values.Add(value != null ? value.ToString() : "");
                        }
                    }
                    else
                    {
                        if (!String.IsNullOrEmpty(exportableColumn.CustomFormatString))
                        {
                            var value = compiledFuncLookup[exportableColumn.Func](item);
                            values.Add(value != null ?
                                String.Format(exportableColumn.CustomFormatString, 
                                value.ToString()) : "");
                        }
                        else
                        {
                            var value = compiledFuncLookup[exportableColumn.Func](item);
                            values.Add(value != null ? value.ToString() : "");
                        }
                    }
                }
                if (!doneHeaders)
                {
                    writer.WriteLine(headers.Aggregate(
                          (start, end) => start + seperator + end));
                    doneHeaders = true;
                }

                writer.WriteLine(values.Aggregate(
                       (start, end) => start + seperator + end));
            }

        }

        // <summary>
        /// Export all specified columns as a XML string, using column data provided
        /// and use custom headers depending on whether a custom header string was supplied.
        /// Use custom formatted column data or default data depending
        /// on whether a custom format string was supplied.
        /// </summary>
        public void ToXML(XmlTextWriter writer)
        {
            if (columns.Count == 0)
                throw new InvalidOperationException(
                   "You need to specify at least one element to export value");

            foreach (T item in sourceList)
            {
                List<String> values = new List<String>();
                foreach (ExportableColumn<T> exportableColumn in columns)
                {
                    if (!doneHeaders)
                    {
                        if (String.IsNullOrEmpty(exportableColumn.HeaderString))
                        {
                            headers.Add(MakeXMLNameLegal(
                                    GetPropertyName(exportableColumn.Func)));
                        }
                        else
                        {
                            headers.Add(MakeXMLNameLegal(exportableColumn.HeaderString));
                        }

                        Func<T, Object> func = exportableColumn.Func.Compile();
                        compiledFuncLookup.Add(exportableColumn.Func, func);
                        if (!String.IsNullOrEmpty(exportableColumn.CustomFormatString))
                        {
                            var value = func(item);
                            values.Add(value != null ?
                                String.Format(exportableColumn.CustomFormatString, 
                                              value.ToString()) : "");

                        }
                        else
                        {
                            var value = func(item);
                            values.Add(value != null ? value.ToString() : "");
                        }
                    }
                    else
                    {
                        if (!String.IsNullOrEmpty(exportableColumn.CustomFormatString))
                        {
                            var value = compiledFuncLookup[exportableColumn.Func](item);
                            values.Add(value != null ?
                                String.Format(exportableColumn.CustomFormatString, 
                                              value.ToString()) : "");
                        }
                        else
                        {
                            var value = compiledFuncLookup[exportableColumn.Func](item);
                            values.Add(value != null ? value.ToString() : "");
                        }
                    }
                }
                if (!doneHeaders)
                {
                    writer.Formatting = Formatting.Indented;
                    writer.WriteStartDocument(true);
                    writer.WriteProcessingInstruction("xml-stylesheet", 
                           "type='text/xsl' href='dump.xsl'");
                    writer.WriteComment("List Exporter dump");

                    // Write main document node and document properties
                    writer.WriteStartElement("dump");
                    writer.WriteAttributeString("date", DateTime.Now.ToString());

                    doneHeaders = true;
                }

                writer.WriteStartElement("item");
                for (int i = 0; i < values.Count; i++)
                {
                    writer.WriteStartElement(headers[i]);
                    writer.WriteString(values[i]);
                    writer.WriteEndElement();
                }
                writer.WriteEndElement();
            }
            writer.WriteEndElement();
            writer.Flush();
        }

        /// <summary>
        /// Export to file, using the AsCSVString() method to supply the exportable data
        /// </summary>
        public void WhichIsExportedToFileLocation(StreamWriter fileWriter)
        {
            AsCSVString(fileWriter);
        }

        /// <summary>
        /// Gets a Name from an expression tree that is assumed to be a
        /// MemberExpression
        /// </summary>
        private static string GetPropertyName<T>(
            Expression<Func<T, Object>> propertyExpression)
        {
            var lambda = propertyExpression as LambdaExpression;
            MemberExpression memberExpression;
            if (lambda.Body is UnaryExpression)
            {
                var unaryExpression = lambda.Body as UnaryExpression;
                memberExpression = unaryExpression.Operand as MemberExpression;
            }
            else
            {
                memberExpression = lambda.Body as MemberExpression;
            }

            var propertyInfo = memberExpression.Member as PropertyInfo;

            return propertyInfo.Name;
        }

        private string MakeXMLNameLegal(string aString)
        {
            StringBuilder newName = new StringBuilder();

            if (!char.IsLetter(aString[0]))
                newName.Append("_");

            // Must start with a letter or underscore.
            for (int i = 0; i <= aString.Length - 1; i++)
            {
                if (char.IsLetter(aString[i]) || char.IsNumber(aString[i]))
                {
                    newName.Append(aString[i]);
                }
                else
                {
                    newName.Append("_");
                }
            }
            return newName.ToString();
        }
    }
}

The most important part of this to understand is that we use some neat .NET 4 optional properties, so we can assume default headers are wanted unless we are told otherwise. The only other thing of note is that we can actually obtain a Func delegate by compiling an Expression, and then call that and obtain a value for the property.

The only other significant part is how we obtain a property name from an expression tree, that is done by the GetPropertyName() method, and it is that, that is used to obtain a header name if we are using default headers.

I think the code is fairly self-explanatory, but if you get lost, just let me know, and I can try and flesh this article out a bit more if needs be.

That's all

I know this is a very small article, but I think it is actually quite a useful little utility that is quite flexible, and pretty easy to use. So if you think it may help you, please give it a vote.

History

1. 28/03/2011 : Initial API.
2. 29/03/2011 : Refactored to use Extension Methods on IEnumerable.
3. 01/04/2011 : Refactored to accept TextWriter based classes for storage to save writing data several times.
4. 05/04/2011 : Refactored to include AsXML() support that one reader posted on the forum. Thanks. Corridorwarrior.
5. 06/04/2011 : Fixed typos for second string example output produced.