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:
- Could be used as an extension method to any
IEnumerable<T>
- Let the
using code specify what columns should be exported
- Use a Fluent API (as they are all the rage these days)
- Allow auto header named to be obtained using Expression trees
- Allow the the user to supply custom header names
- Allow the user to also supply custom format strings for the data being exported
- Allow the data to be exported to a CSV file or allow the data to be exported to a CSV string
- Allow the exporter to be able to deal with NULL values
- 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.
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
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
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
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
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
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.
="1.0" ="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
{
public static FluentExporter<T> GetExporter<T>(
this IEnumerable<T> source, String seperator = ",") where T : class
{
return new FluentExporter<T>(source, seperator);
}
}
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;
}
}
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;
}
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));
}
}
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");
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();
}
public void WhichIsExportedToFileLocation(StreamWriter fileWriter)
{
AsCSVString(fileWriter);
}
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("_");
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
- 28/03/2011 : Initial API.
- 29/03/2011 : Refactored to use Extension Methods on
IEnumerable.
- 01/04/2011 : Refactored to accept
TextWriter based classes for storage to save writing data several times.
- 05/04/2011 : Refactored to include
AsXML() support that one reader posted on the forum. Thanks. Corridorwarrior.
- 06/04/2011 : Fixed typos for second string example output produced.
Submit an article or tip
Post your Blog
News 
Bug 
Joke 
Rant 
Admin 
