Gembox Excel Downloads in ASP.NET MVC
Excel — the number-crunching tool of choice for business-bods the world over. If you’re developing complex, bespoke web-apps for these folk then Excel downloads are invaluable…inevitable…inexorable. They want their data in .csv, .xls, .xlsx and even .iWantThisTodayButIllWantThatTomorrowAndSomethingElseNextWeek.
My tool of choice for catering to Excel needs in .NET is Gembox. “Word” has it the Office Interop libraries lack the full capabilities you’ll need and most importantly are a drain on critical performance in a web environment.
Gembox, however great, likes to work with streams — output streams in particular. Give it a memory stream, and it will give you hassle. So this makes life impossible when working with the FileResult in an MVC controller.
The examples available on the official site show Gembox files being written directly to the response’s output stream. But no — not in an MVC controller????
Don’t be silly — unless you’re a cow boy. In which case, switch off now and lasso that tightly-coupled spaghetti code into all sorts of future-pain shaped pieces.
For those of use sitting on the scale between conscientious and perfectionist — and for the record, I only just make the grade — then for us people, we create a custom action result, and do all our interaction with the response in that little haven. We also do it in a flexible way which encapsulates the logic bound to ASP.NET MVC interfaces.
Alas, you’re probably gagging for code, so feast on this:
public abstract class ExcelResult : ActionResult
{
private readonly ExcelFile _file;
private readonly string _fileName;
protected ExcelResult(ExcelFile file, string fileName)
{
_file = file;
_fileName = fileName;
}
protected abstract string ContentType { get; }
protected abstract string Extension { get; }
protected abstract Action<ExcelFile, Stream> SaveAction { get; }
public override void ExecuteResult(ControllerContext context)
{
context.HttpContext.Response.Clear();
context.HttpContext.Response.ContentType = ContentType;
context.HttpContext.Response.AddHeader(“Content-disposition”, “filename=” + _fileName + Extension);
SaveAction(_file, context.HttpContext.Response.OutputStream);
}
}
Essentially, this abstract class contains the logic that sets up the response, but parameterises the actual data and the file name. It also defines an interface that types of Excel file (csv, xls, xlsx, blah-bluh-bleh) must provide implementations for –content type and the file extension.
SaveAction is the “parameter” which provides the data — it takes a method with two parameters — the Gembox ExcelFile you supply in the constructor and a Stream — which you can see is the Reponse.OutputStream. All the base class wants you to do is put the data in the stream and think happy thoughts.
If the interface for ExecuteMethod on ActionResult changes in a future version of MVC, we only have to tweak the method in this base class. If we also wanted to use different Excel providers, we could use an IExcelFile interface. Yagni, Yagni, Yagni. Ok, you get the get message. I’ve just done what’s needed here, but made life easy in the future without any cost now and been bone-DRY in the process.
An example of a concrete class, in this case for Csv files produced by Gembox, would look remarkably similar to this:
public class CsvResult : ExcelResult
{
public CsvResult(ExcelFile file, string fileName)
: base(file, fileName)
{
}
protected override string ContentType
{
get { return “application/csv”; }
}
protected override string Extension
{
get { return “.csv”; }
}
protected override Action<ExcelFile, Stream> SaveAction
{
get { return (f, s) => f.SaveCsv(s, CsvType.CommaDelimited); }
}
}
I’m lost for words. There’s absolutely nothing to talk about – so simple it explains itself. If you now need to create one for .xls, .xlsx or some of the other open formats supported by Gembox, then all the hard work has been done.
To use these custom results, return a new instance of the desired sub-class, passing the Gembox ExcelFile along with a filename. Your users get an Excel document and your colleagues will think you’re cool.