Whenever I build an application that contains any kind of statistical data I commonly get the request to make all of the grids and/or tables to include a export to CSV capability. In my mind, there are two options to approach this functionality.
- Build a method that iterates over a control. For example in a grid you can iterate over the columns and rows to build the CSV file.
- Build a method that iterates over the data.
In my opinion approach #2 is more flexible and lends itself better to code re-use. Tying export functionality to a specific control is really not a good idea unless you have a unique situation which requires it.
In my MVC application I am using LINQ to SQL and I used the Repository pattern that you may have seen before if you have ever looked at the nerddinner.com source code. The majority of my data access code returns an IQueryable<T> result. Therefore my export function is an extension method on the IQueryable<T> type. In order to get the column names and values I use reflection and append the values to a CSV file. Here is the snippet of code that prints the header row of the CSV file...
foreach (var obj in list) {
Type type = obj.GetType();
PropertyInfo[] properties = type.GetProperties();
string s = String.Empty;
if ( !headerPrinted ) {
foreach (PropertyInfo propertyInfo in properties) {
if (exclude.Count(x => x.Contains(propertyInfo.Name)) > 0)
continue;
if (s.Length > 0)
s = String.Join(",", new string[] { s, propertyInfo.Name });
else
s = propertyInfo.Name;
}
sw.WriteLine(s);
headerPrinted = true;
}
The list variable is the IQueryable<T> object that was past into the method. For each object in the list I call GetType() to find out what kind of object it is and then I call GetProperties() on it. This allows me to enumerate over the properties and get the name of each property so I can write it to the CSV file. You may also notice that I added the ability to exclude certain columns as well. This is because that many of my Repository methods include extra columns which are used to build action links in my views. For example, If I am displaying information about a person in a grid I will commonly include their full name and the primary key of the record in the result set so I can produce a hyperlink that the end user can click on to view the details. In MVC, we would use the ActionLink HtmlHelper to create the hyperlink...
<%= Html.ActionLink(contact.FullName, "Details", new { ID= contact.ID })%>
The action link call above would produce the following HTML:
<a href="/Contact/Details/1">John Smith</a>
However, when I export the records to CSV, the primary key is generally not needed so I can exclude this from the export by specifying the "ContactID" in the exclude parameter. This allows me to re-use methods in my repository class. Otherwise, I would have to write special methods which excluded the key fields for exporting purposes.
Finally, when you look at the completed method below you will probably notice that at the very end of the method I added some web specific code. This code sets the content type and response headers so the CSV file can be viewed with Excel in a browser. If you want to make the method generic enough so it can be used on the Web and/or WinForms you can extract this section of code and put it in a separate method.
public static void ExportToCSV<T>(this IQueryable<T> list, string filename, string[] exclude ) {
StringWriter sw = new StringWriter();
bool headerPrinted = false;
foreach (var obj in list) {
Type type = obj.GetType();
PropertyInfo[] properties = type.GetProperties();
string s = String.Empty;
if ( !headerPrinted ) {
foreach (PropertyInfo propertyInfo in properties) {
if (exclude.Count(x => x.Contains(propertyInfo.Name)) > 0)
continue;
if (s.Length > 0)
s = String.Join(",", new string[] { s, propertyInfo.Name });
else
s = propertyInfo.Name;
}
sw.WriteLine(s);
headerPrinted = true;
}
s = String.Empty;
for (int idx = 0; idx < properties.Length; idx++) {
if (exclude.Count(x => x.Contains(properties[idx].Name)) > 0)
continue;
var value = properties[idx].GetValue(obj, null);
var formattedValue = value == null ? String.Empty : value.ToString();
if (value != null) {
if (value.GetType() == typeof(string))
formattedValue = "\"" + formattedValue + "\"";
}
if (s.Length > 0)
s = String.Join(",", new string[] { s, formattedValue });
else
s = formattedValue;
}
sw.WriteLine(s);
}
HttpResponse response = System.Web.HttpContext.Current.Response;
response.AddHeader("Content-Disposition", "attachment; filename=" + filename );
response.ContentType = "application/ms-excel";
response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
response.Write(sw);
response.End();
}
If you are going to use this code in a web application you may also be interested in this HTML code. It basically renders a select box and a button. When the button is clicked it opens a new window using the URL of the selected option. In this example, the controller action will fire my ExportToCSV routine which will create the CSV file and display it in a new browser window.
<span style="float:right;">
<b>Export:</b>
<select id="export">
<option value="/Instance/ExportAssignments">CSV</option>
</select>
<input type="button" value="Go" onclick="window.open( $('#export').val() )" />
</span>
I chose to use a drop down list here because I intend to add other export formats later on such as HTML and PDF. I will also probably create a HTMLHelper class to render the "export" HTML in my views. This way I can have a consistent and centrally maintainable export control which I can use across my entire MVC web application.
Additional Resouces:
