ASP.NET MVC: Enhancing The WebGrid - Exporting Data to CSV

April 2nd, 2015

Part three of this WebGrid series provides us with a way to export records to a CSV file. This CSV exporter builds off of our previous project when we built an Excel export...and answer a question.

In our last post, we went through a way to export your WebGrid data to an Excel spreadsheet. We walked through creating a custom ActionResult and a custom dialog box for selecting options for how you want your data formatted and exported.

Today, we'll build on that by adding a second export format: CSV.

CSV Format

CSV is what I consider the poor-man's Excel file. CSV stands for Comma-Separated Values. It's a simple text file format with comma-delimited data and can be easily imported into Excel (Another reason people love this format).

This is also one of the most popular file types for exporting data.

The only thing that will be difficult with this export format is the creation of the CsvResult ActionResult, which we'll look at later.

But first, we'll look at the small adjustments that comprise our CSV export.

An Adjustment Here, An Adjustment There

First adjustment is the ExportParameters. We need to add a CSV option to our Output enum.

public enum Output { Excel, Csv }

Next is our ExportViewModelBinder.cs, which needs to know how to handle the CSV type when we post our data.

.
.
var pagingOptions = range == "pageCurrent" 
    ? RangeOptions.Current 
    : RangeOptions.All;
 
// Output
var output = request.Form.Get("exportType");
var outputType = Output.Excel;
switch (output)
{
    case "exportCsv":
        outputType = Output.Csv;
        break;
}
 
int currentPage;
.
.

Now we need to add our CSV option on our dialog box so they can select the output format. This change happens in the UserGrid.cshtml:

.
.
<div class="col-md-3">Output:</div>
<div class="col-md-9">
    <div class="radio">
        <label>
            <input type="radio" name="exportType" id="exportExcel" value="exportExcel" checked>
            Excel
        </label>
    </div>
    <div class="radio">
        <label>
            <input type="radio" name="exportType" id="exportCsv" value="exportCsv" >
            Csv
        </label>
    </div>
</div>
.
.

And finally, before we work on our CsvResult, we need to modify the UserController.cs so it knows how to process and send our CSV file back to the user.

.
.
[HttpPost]
public ActionResult Export(ExportParameters model)
{
    var records = _repository.GetAll();
    if (model.PagingEnabled)
    {
        records = records.Skip((model.CurrentPage-1) * model.PageSize)
           .Take(model.PageSize);
    }
 
    if (model.OutputType.Equals(Output.Excel))
    {
        var excelFormatter = new ExcelFormatter(records);
        return new ExcelResult(excelFormatter.CreateXmlWorksheet(), "Sample.xlsx");
    }
    
    if (model.OutputType.Equals(Output.Csv))
    {
        return new CsvResult(records, "Sample.csv");
    }
   
    return Redirect(Url.Content("~/"));
}
.
.

Of course, at this point, you should be receiving an unknown class called CsvResult. But first, we need to talk about an extension method to help us generate our CSV lines.

CsvHelpers

For this ActionResult, we need a way to convert these objects into a CSV format. While this extension wasn't included in my 10 Extremely Useful .NET Extension Methods, if I created a list of 20, I would place this extension at number 11. ;-)

Formatters\CsvHelpers.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
 
namespace WebGridExample.Formatters
{
    public static class CsvHelpers
    {
        public static string ToCsv<T>(this IEnumerable<T> objectlist, string separator)
        {
            Type t = typeof(T);
            PropertyInfo[] props = t.GetProperties();
 
            string header = String.Join(separator, props.Select(f => f.Name).ToArray());
 
            StringBuilder csvdata = new StringBuilder();
            csvdata.AppendLine(header);
 
            foreach (var o in objectlist)
                csvdata.AppendLine(ToCsvFields(separator, props, o));
 
            return csvdata.ToString();
        }
 
        private static string ToCsvFields(string separator, PropertyInfo[] properties, object o)
        {
            var line = new StringBuilder();
 
            foreach (var f in properties)
            {
                if (line.Length > 0)
                    line.Append(separator);
 
                var x = f.GetValue(o);
 
                if (x != null)
                    line.Append(x);
            }
 
            return line.ToString();
        }         
    }
}

This extension (ToCsv) takes an IEnumerable list of objects and creates a header and a list of CSV records based on the object's properties.

The second method is a helper to the ToCsv extension method. It takes the properties from an object, uses reflection to get the values, and proceeds to build a CSV line to add into our StringBuilder.

CsvResult! It's About Time!

"Sheesh, Jonathan, get on with it!"

Finally, we get to our ActionResult.

using System;
using System.Linq;
using System.Text;
using System.Web.Mvc;
using WebGridExample.Formatters;
using WebGridExample.Models;
 
namespace WebGridExample.ActionResults
{
    public class CsvResult : ActionResult
    {
        private readonly IQueryable<User> _records;
        private readonly string _filename;
 
        public CsvResult(IQueryable<User> records, string filename)
        {
            _records = records;
            _filename = filename;
        }
 
        public override void ExecuteResult(ControllerContext context)
        {
            var sb = new StringBuilder();
            sb.Append(_records.ToCsv<User>(","));
 
            var response = context.HttpContext.Response;
            response.ContentType = "text/csv";
            response.AddHeader("content-disposition", 
                String.Format("attachment; filename={0}", _filename));
 
            response.Write(sb.ToString());
 
            response.Flush();
            response.End();
        }
    }
}

I know, right? I got you all worked up for something spectacular and BAM! Very little code in the ExecuteResult. That's because we have code in other places that takes care of the heavy lifting.

Notice the second line in the ExecuteResult? _records.ToCsv<User>(",") gives us our CSV header and records.

Set your headers and content type, write out the CSV string, flush the buffer, and end the response...

...and we're done. Another export format is complete.

Conclusion

This post covered how to export a CSV format from a WebGrid. When broken down into smaller chunks of code, this makes the application more manageable and easier to read.

While I certainly enjoy writing this WebGrid series, I want to ask everyone a question before we continue.

For my next WebGrid topic, are you interested in learning about exporting to PDF...

...OR...

...would you be interested in learning how to incorporate SignalR into a WebGrid?

Post your answer in the comments below.

Series: Enhancing the WebGrid