There is very little ASP.NET MVC specific code in this post but since I love the framework so much I included it in the title anyway.
Say you have an online address book and you want your users to be able to download the content of this address book in a Microsoft Excel format.
I had a similar problem and wanted a light generic solution. What I came up with was a CSVExport ActionResult class. To export data to Excel all I have to do is:
public ActionResult Export()
{
//Fetch some data
var addressbook = Repository.GetAllPeople();
//Generate and return the csv file
return new CSVResult<Person>(addressbook );
}
I use Microsoft Excel but since the data returned is a CSV(Comma-separated values)-file it can be used by a lot of different applications.
So where’s the magic?
With the help of reflection I get a list of all the public properties defined on the T class. I then enumerate all object in the supplied IList of T and pulls the data from the properties reflected. All this data is added to a StringBuilder and the result is a CSV file.
To make things a little more customizable i created a CSVExportAttribute class that you can use to control what and how you want to export data to CSV-format.
[AttributeUsage(AttributeTargets.Class
| AttributeTargets.Property, AllowMultiple=false)]
public class CSVExportAttribute : Attribute
{
//Let's you specify a custom header text
public string Header { get; set; }
//If true and set on a property that
//property will not be exported.
public bool Exclude { get; set; }
}
You can set this attribute on a class or/and on a property. When set on a class that means that all properties in that class, except if explicitly specified, will be exported.
Okay, lets look at the main code
public class CSVResult<T> : ActionResult
{
public string Filename { get; set; }
public char Delimiter { get; set; }
public IList<T> Data { get; set; }
public CSVResult(IList<T> data)
: this(data, null) { }
public CSVResult(IList<T> data, string filename)
{
this.Data = data;
this.Filename = filename;
this.Delimiter = ';';
}
The CSVResult<T> inherits ActionResult and takes a IList<T> in the constructor. Nothing crazy there. The important code is in the ExceuteResult method.
public override void ExecuteResult(ControllerContext context)
{
//The file name to display in the Open/Save-file dialog.
string filename = this.Filename ?? "CSV_" + DateTime.Now.ToString("yyyyMMddHHmmss");
//Set up headers
context.HttpContext.Response.ContentType = "text/csv";
context.HttpContext.Response.ContentEncoding = Encoding.Unicode;
context.HttpContext.Response.AppendHeader("Content-Disposition",
string.Format("filename=\"{0}.csv\";attachment", filename));
First we set up the response headers to make the browser understand that the response data is as a file and with that done we can finally get to the real code.
//Enumerate the list and output header + data
if (this.Data != null)
{
bool exportAll = false;
StringBuilder sb = new StringBuilder();
var exportProperties = new Dictionary<00ff">string, System.Reflection.PropertyInfo>();
var type = typeof(T);
//If the csv export attribute is set on the
//class we export data from all properties.
foreach (CSVExportAttribute attrib in type.GetCustomAttributes(typeof(CSVExportAttribute), false))
{
if (!attrib.Exclude)
{
exportAll = true;
break;
}
}
Make sure we have some data and then we look if the class has the CSVExport attribute, if so, we set a flag and moves on.
//Find properties with CSVExportAttribute
foreach (var prop in type.GetProperties(System.Reflection.BindingFlags.Public
| System.Reflection.BindingFlags.Instance))
{
bool hasAttribute = false;
foreach (CSVExportAttribute attrib in prop.GetCustomAttributes(typeof(CSVExportAttribute), false))
{
hasAttribute = true;
if (!attrib.Exclude)
{
//If we have a custom header lets use it;
//otherwise we go with the property name
string header = attrib.Header ?? prop.Name;
exportProperties.Add(header, prop);
}
}
//If the property doesn't have any ExportAttributes
//but the class has then we export the property anyway.
if (hasAttribute == false && exportAll)
{
exportProperties.Add(prop.Name, prop);
}
}
We enumerate all public properties and look for the CSVExport attribute. If we find it or the exportAll is true from before then we add the property to a properties-to-export collection. If we find the attribute and the Header value is set we use that value instead of the property name for our key that later will the the column header in the CSV-file.
We have all we need, let’s build the CSV
//Add headers to the csv data
foreach (var p in exportProperties)
sb.Append(p.Key).Append(this.Delimiter);
sb.Append(Environment.NewLine);
//Rows
foreach (var row in this.Data)
{
//Columns
foreach (var p in exportProperties)
{
try
{
sb.Append(RemoveInvalidCharacters(p.Value.GetValue(row, null)
.ToString())).Append(this.Delimiter);
}
catch (Exception)
{
//Unable to get value.
//Add delimiter to make sure columns are correct.
sb.Append(this.Delimiter);
}
}
sb.Append(Environment.NewLine);
}
//Write the data to the response stream
context.HttpContext.Response.Write(sb.ToString());
First me make a row with the headers each separated by a delimiter(; as default). We then enumerate all the rows in our list and for each row we get the value for each property found earlier.
The RemoveInvalidCharecters function does exactly what is says.
private string RemoveInvalidCharacters(string v)
{
string replaceWith = " ";
if (string.IsNullOrEmpty(v))
return v;
return v.Replace(this.Delimiter.ToString(), replaceWith)
.Replace(Environment.NewLine, replaceWith)
.Replace(((char)10).ToString(), replaceWith) //Carrige return char(10)
.Replace(((char)13).ToString(), replaceWith); //Line feed char(13)
}
With this done all we have to do is decorate the classes want to be able to export with the CSVExport attribute.
[CSVExport]
public class Person
{
[CSVExport(Header="First name")]
public string FirstName { get; set; }
[CSVExport(Header = "Last name")]
public string LastName { get; set; }
public string Telephone { get; set; }
[CSVExport(Exclude = true)]
public int Id { get; set; }
}
That’s all folks!
edc9e17b-24ee-4f84-9645-2cbec37484b9|2|5.0
ASP.NET MVC
asp.net mvc, excel, csv