Click here to Skip to main content
Click here to Skip to main content
Go to top

Fluent CSV/XML Exporter for List

, 5 Apr 2011
Rate this:
Please Sign up or sign in to vote.
A small utility Fluent interface class to export a List.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sacha Barber
Software Developer (Senior)
United Kingdom United Kingdom
I currently hold the following qualifications (amongst others, I also studied Music Technology and Electronics, for my sins)
 
- MSc (Passed with distinctions), in Information Technology for E-Commerce
- BSc Hons (1st class) in Computer Science & Artificial Intelligence
 
Both of these at Sussex University UK.
 
Award(s)

I am lucky enough to have won a few awards for Zany Crazy code articles over the years

  • Microsoft C# MVP 2014
  • Codeproject MVP 2014
  • Microsoft C# MVP 2013
  • Codeproject MVP 2013
  • Microsoft C# MVP 2012
  • Codeproject MVP 2012
  • Microsoft C# MVP 2011
  • Codeproject MVP 2011
  • Microsoft C# MVP 2010
  • Codeproject MVP 2010
  • Microsoft C# MVP 2009
  • Codeproject MVP 2009
  • Microsoft C# MVP 2008
  • Codeproject MVP 2008
  • And numerous codeproject awards which you can see over at my blog

Comments and Discussions

 
GeneralRe: My vote of 5 PinmvpSacha Barber6-Apr-11 18:57 
GeneralI think you have some typos PinmemberJohn C5-Apr-11 11:46 
GeneralRe: I think you have some typos PinmvpSacha Barber5-Apr-11 20:01 
GeneralAn idea PinmemberJuan Pablo G.C.5-Apr-11 8:28 
GeneralRe: An idea PinmvpSacha Barber5-Apr-11 19:57 
GeneralIt's all so orange... PinmemberPIEBALDconsult5-Apr-11 2:54 
GeneralRe: It's all so orange... PinmvpSacha Barber5-Apr-11 3:08 
GeneralRe: It's all so orange... PinmvpSacha Barber5-Apr-11 3:13 
Try now, nothing was wrong I feel, but I just got rid of some empty paragraphs
Sacha Barber
  • Microsoft Visual C# MVP 2008-2011
  • Codeproject MVP 2008-2011
Open Source Projects
Cinch SL/WPF MVVM

Your best friend is you.
I'm my best friend too. We share the same views, and hardly ever argue
 
My Blog : sachabarber.net

GeneralRe: It's all so orange... PinmemberPIEBALDconsult5-Apr-11 4:42 
GeneralRe: It's all so orange... PinmvpSacha Barber5-Apr-11 4:46 
GeneralMy vote of 5 PinmemberJV99995-Apr-11 2:14 
GeneralRe: My vote of 5 PinmvpSacha Barber5-Apr-11 3:07 
GeneralGood one Pinmemberjarimba4-Apr-11 22:07 
GeneralSmall idea.... PinmemberAndrew Rissing4-Apr-11 14:32 
GeneralRe: Small idea.... PinmvpSacha Barber4-Apr-11 19:55 
GeneralExport to XML and use XSL stylesheet to show in report like layout in a browser [modified] PinmemberCorridorwarrior4-Apr-11 9:45 
GeneralRe: Export to XML and use XSL stylesheet to show in report like layout in a browser PinmvpSacha Barber4-Apr-11 19:54 
GeneralRe: Export to XML and use XSL stylesheet to show in report like layout in a browser [modified] PinmemberCorridorwarrior4-Apr-11 23:52 
GeneralRe: Export to XML and use XSL stylesheet to show in report like layout in a browser PinmvpSacha Barber5-Apr-11 0:23 
GeneralRe: Export to XML and use XSL stylesheet to show in report like layout in a browser PinmvpSacha Barber5-Apr-11 2:01 
QuestionWhat about a CSV Importer? Pinmemberdevnet2471-Apr-11 20:48 
AnswerRe: What about a CSV Importer? PinmvpSacha Barber1-Apr-11 21:25 
GeneralMy vote of 5 Pinmembers.kirchner1-Apr-11 1:17 
GeneralRe: My vote of 5 PinmvpSacha Barber1-Apr-11 21:26 
GeneralMy vote of 5 PinmemberSreejith Gopinathan31-Mar-11 23:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140905.1 | Last Updated 6 Apr 2011
Article Copyright 2011 by Sacha Barber
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid