Have you ever needed to transform data from in-memory C# objects to a CSV flat-file of a specific format? Here’s an easy way to get the job done with C#, LINQ, and Generics.
First you need the Attribute you will use to mark up the properties on your exportable class:
/// <summary>
/// The name of the column for the CSV
/// generated by a list of objects with
/// properties marked with this attribute,
/// if Export is true. Uses Order to order
/// the properties on export
/// </summary>
public class CsvColumnNameAttribute : Attribute
{
public bool Export { get; set; }
public int Order { get; set; }
public string Name { get; set; }
public CsvColumnNameAttribute()
{
Export = true;
Order = int.MaxValue; // so unordered columns are at the end
}
}
/// The name of the column for the CSV
/// generated by a list of objects with
/// properties marked with this attribute,
/// if Export is true. Uses Order to order
/// the properties on export
/// </summary>
public class CsvColumnNameAttribute : Attribute
{
public bool Export { get; set; }
public int Order { get; set; }
public string Name { get; set; }
public CsvColumnNameAttribute()
{
Export = true;
Order = int.MaxValue; // so unordered columns are at the end
}
}
And here is how you use the attributes to mark up your class so you can export a list of instantiated objects:
public class MyClassToExport
{
[CsvColumnAttribute(Name = "Activation Date", Order = 1)]
public DateTime Date { get; set; }
[CsvColumnAttribute(Name = "Full Name", Order = 2)]
public string User { get; set; }
[CsvColumnAttribute(Name = "Account Type", Order = 3)]
public int Level { get; set; }
[CsvColumnAttribute(Name = "Account Action", Order = 4)]
public string Action { get; set; }
[CsvColumnAttribute(Export = false)]
public DateTime Added { get; set; }
}
{
[CsvColumnAttribute(Name = "Activation Date", Order = 1)]
public DateTime Date { get; set; }
[CsvColumnAttribute(Name = "Full Name", Order = 2)]
public string User { get; set; }
[CsvColumnAttribute(Name = "Account Type", Order = 3)]
public int Level { get; set; }
[CsvColumnAttribute(Name = "Account Action", Order = 4)]
public string Action { get; set; }
[CsvColumnAttribute(Export = false)]
public DateTime Added { get; set; }
}
Then you need to code to actually create the CSV using LINQ and Generics:
/// <summary>
/// Generate a CSV as a string from a list
/// of objects that have the CsvColumnNameAttribute
/// applied
/// </summary>
public string GetCsv<T>(List<T> csvDataObjects)
{
PropertyInfo[] propertyInfos = typeof(T).GetProperties();
var sb = new StringBuilder();
sb.AppendLine(GetCsvHeaderSorted(propertyInfos));
csvDataObjects.ForEach(d => sb.AppendLine(GetCsvDataRowSorted(d, propertyInfos)));
return sb.ToString();
}
private string GetCsvDataRowSorted<T>(T csvDataObject, PropertyInfo[] propertyInfos)
{
IEnumerable<string> valuesSorted = propertyInfos
.Select(x => new
{
Value = x.GetValue(csvDataObject, null),
Attribute = (CsvColumnNameAttribute)Attribute.GetCustomAttribute(x, typeof(CsvColumnNameAttribute), false)
})
.Where(x => x.Attribute != null && x.Attribute.Export)
.OrderBy(x => x.Attribute.Order)
.Select(x => GetPropertyValueAsString(x.Value));
return String.Join(",", valuesSorted);
}
private string GetCsvHeaderSorted(PropertyInfo[] propertyInfos)
{
IEnumerable<string> headersSorted = propertyInfos
.Select(x => (CsvColumnNameAttribute)Attribute.GetCustomAttribute(x, typeof(CsvColumnNameAttribute), false))
.Where(x => x != null && x.Export)
.OrderBy(x => x.Order)
.Select(x => x.Name);
return String.Join(",", headersSorted);
}
private string GetPropertyValueAsString(object propertyValue)
{
string propertyValueString;
if (propertyValue == null)
propertyValueString = "";
else if (propertyValue is DateTime)
propertyValueString = ((DateTime)propertyValue).ToString("dd MMM yyyy");
else if (propertyValue is int)
propertyValueString = propertyValue.ToString();
else if (propertyValue is float)
propertyValueString = ((float)propertyValue).ToString("#.####"); // format as you need it
else if (propertyValue is double)
propertyValueString = ((double)propertyValue).ToString("#.####"); // format as you need it
else // treat as a string
propertyValueString = @"""" + propertyValue.ToString().Replace(@"""", @"""""") + @""""; // quotes with 2 quotes
return propertyValueString;
}
/// Generate a CSV as a string from a list
/// of objects that have the CsvColumnNameAttribute
/// applied
/// </summary>
public string GetCsv<T>(List<T> csvDataObjects)
{
PropertyInfo[] propertyInfos = typeof(T).GetProperties();
var sb = new StringBuilder();
sb.AppendLine(GetCsvHeaderSorted(propertyInfos));
csvDataObjects.ForEach(d => sb.AppendLine(GetCsvDataRowSorted(d, propertyInfos)));
return sb.ToString();
}
private string GetCsvDataRowSorted<T>(T csvDataObject, PropertyInfo[] propertyInfos)
{
IEnumerable<string> valuesSorted = propertyInfos
.Select(x => new
{
Value = x.GetValue(csvDataObject, null),
Attribute = (CsvColumnNameAttribute)Attribute.GetCustomAttribute(x, typeof(CsvColumnNameAttribute), false)
})
.Where(x => x.Attribute != null && x.Attribute.Export)
.OrderBy(x => x.Attribute.Order)
.Select(x => GetPropertyValueAsString(x.Value));
return String.Join(",", valuesSorted);
}
private string GetCsvHeaderSorted(PropertyInfo[] propertyInfos)
{
IEnumerable<string> headersSorted = propertyInfos
.Select(x => (CsvColumnNameAttribute)Attribute.GetCustomAttribute(x, typeof(CsvColumnNameAttribute), false))
.Where(x => x != null && x.Export)
.OrderBy(x => x.Order)
.Select(x => x.Name);
return String.Join(",", headersSorted);
}
private string GetPropertyValueAsString(object propertyValue)
{
string propertyValueString;
if (propertyValue == null)
propertyValueString = "";
else if (propertyValue is DateTime)
propertyValueString = ((DateTime)propertyValue).ToString("dd MMM yyyy");
else if (propertyValue is int)
propertyValueString = propertyValue.ToString();
else if (propertyValue is float)
propertyValueString = ((float)propertyValue).ToString("#.####"); // format as you need it
else if (propertyValue is double)
propertyValueString = ((double)propertyValue).ToString("#.####"); // format as you need it
else // treat as a string
propertyValueString = @"""" + propertyValue.ToString().Replace(@"""", @"""""") + @""""; // quotes with 2 quotes
return propertyValueString;
}
Once you have that code up in going in your application, you can export your list of objects to a CSV with just a couple lines of code:
// example usage
var export = new List<MyClassToExport>();
// TODO add items to list :)
var csv = GetCsv(export);
var export = new List<MyClassToExport>();
// TODO add items to list :)
var csv = GetCsv(export);
Hope this helps! And let me know if you have any suggestions or improvements.
