Early in my career I learned that whenever you display a grid of data within an application, someone will want it exported to Excel. This really should not need to be listed as a requirement since users just expect it to happen. As it is an inevitable part of any application, we need to make it easy to provide.

What Can We Learn from MVC

Let’s take a page from the MVC playbook to make this a bit easier to provide. From the MVC pattern’s playbook we learn to break the problem into three separate areas. Fortunately for us, we can reuse the Model and the Controller that we used to put the data on the screen in the first place.

This leaves us with the View.

Razor is Microsoft’s most recent View Engine for the MVC framework and probably the most popular, but it is far from the only option. Spark, for example, might be useful if you are already familiar with the Castle Project MonoRail. HAML, too is preferred when the primary goal is in making the markup more readable, because it can result in elegant minimal markup.

These View Engines are focused on outputting HTML but this is not the only option. In fact, given how easy HTML is to write, a View Engine may be overkill. In our case, we want a View Engine that will shield us from some of the complexities of manipulating the Excel Library that we plan to use.

EPPlus Library

EPPLus is an open-source .Net library for creating and reading Excel files licensed under the GNU Library General Public License (LGPL). It is a pretty nice library, fully managed code, no messy COM interop, and the API is fairly intuitive. Still it is much too low level for something that we might find ourselves having to deal with on a regular basis.

To fully appreciate this library, let’s look at what it might look like to have to code by hand a simple table export.    Suppose we have a typical web page that shows a list of Orders in a grid and we want to export this grid to Excel. This grid may have columns like:

• Item Number
• Description
• Quantity
• Price
• Total

This is a fairly common type grid to have in a web page, and one that a user is likely to want to have in Excel. Using this library directly, our code might look like this:

While this could clearly be much worse, it is not ideal. There is a lot of boilerplate code that you would not want to have to duplicate very often, but at the same time there is some key components that make this spreadsheet different from any other spreadsheet. We can therefore hide the mechanics of dealing with the library behind a layer of abstraction that allows us to just specify those things that are unique to individual spreadsheets.

This layer of abstraction will be our View Engine

Wait, I Can Write My Own View Engine?

The task of writing your own View Engine sounds daunting at first: After all, Microsoft had a full team working on Razor for years; but we are not trying to recreate Razor. We are not even coming anywhere close. All we need from the View Engine is a way of specifying the handful of components that make each individual spreadsheet.

It turns out that the mechanics for creating a View Engine are actually rather simple. We will define a class derived from VirtualPathProviderViewEngine. In this class, there are just a couple of methods that we will need to override.

So this first step is deceptively simple. The main thing that we have to worry about here is registering which Views this View Engine will be responsible for handling.

Here we stipulate our Views will end with .xcl and we have specified all of the standard locations to look for the views. We are explicitly specifying that we don’t support partial views. This is fine, because in the context of an Excel file, a partial View wouldn’t really make sense anyway.

Most of the work is actually going to be handled in the ExcelView<> class. When we declare this ExcelView<> class, we pass in the type of the Model as a generic argument. This will be very important shortly.

Now that we have this class created, we need to let .Net know about it and the role we expect it to play. In the Application_Start event handler, add the following line of code:

So the Tricky Part is Creating the View

 To create the View, we don’t have a practical base class to start from. The key thing is to implement the IView interface. Like most interfaces, this one is rather simple

Obviously the complexities come into play with the implementation of this Render method. Let’s start by investigating this ViewContext object that we get.

There are a couple of properties here that we will initially be very interested in:

• viewContext.HttpContext gives us access to Server, Request, Response, etc.
• viewContext.ViewData.Model gives us access to the Model that was passed to the View
• viewContext.RouteData gives us the full route information. Most importantly, we can trace back to the Action with viewContext.RouteData.Values["action"].ToString()

While the Interface does not require a Constructor, we know that we will need one based on how the View was instantiated in the ViewEngine. Fortunately, our constructor is rather simple:

The ViewPath will give us access to the file location where our View definition is stored. This will come in handy soon.

Working with a top-down design, our Render method can be as simple as this:

This method is simple because we skip over two key details.

• What will be in the View file
• What are we doing in the CreateWorksheet method

It turns out that both of these questions are related.

Towards a Simple Grammar

 We need to define a grammar for what will be in the View file. This sounds complicated, but it really is not. This will be the simplest grammar you can imagine. Our needs are rather modest.     We have already seen that we are stipulating that the Model must be an ICollection of some base type, which makes sense because are going to be displaying a list of data from a grid. All our grammar needs to do is allow us to define Headers for this grid and specify which property from the Model goes into which column.

At this point, our grammar will allow 2 lines. Any other data will be ignored. So our grammar can be as simple as this:

• HEADER: Vertical bar separated list of column headers
• LIST: Vertical bar separated list of property references from the Model

 With this simple grammar, the View file to export the grid that we saw initially would look like this:

• HEADER: Description| Inem Number | Quantity | Price | Total
• LIST:Description| InemNumber | Quantity | Price | Total

So our View Definition is very simple. This is good because this is what will need to written whenever we want to export data to Excel.

CreateWorksheet

Continuing with a Top Down Design, we might structure our CreateWorksheet like this:

Note here that we explicitly cap the name of the worksheet at 30 characters. If you try to give it a longer name, you won’t get an error message until you try to open the workbook. Even then, it will be a very cryptic error message. Beyond this subtle bit of logic, everything else is handled in the two methodsProcessHeader and ProcessValues.

ProcessHeader is fairly straight forward.

All we have to do here is to verify that the line in question starts with our keyword HEADER, then we strip out the Keyword and split on the delimiter character.    This will give us a list of headers that we then loop through and make bold.

We also want to mark this as virtual so that, if we derive a new View from this class, it can potentially override this functionality and provide support for multiline headers or alternate formatting options.

Now ProcessValues will be just a little bit more complicated:

The first few parts should look very similar to what we did with ProcessHeader. Once we have a list of columns, we need to loop through the model. Even here, the heavy lifting of extracting the value from the model has been deferred to the EvaluateListValue method.

There is a bit more going on here than would be immediately obvious. For example, this method can evaluate property references to any depth returning an empty string if any along the way evaluate to null.     This means that we could evaluate references such as:

 OrderedBy.NameLastOrder.OrderDateItem.ItemNumber

If you structure your Model properly, this should not be needed, but we don’t always structure our Models properly.

A nice extension to consider would be to programmatically determine if a component is a method of a property. Such a change would allow us to make references like:

OrderedBy.Name.ToUpper() LastOrder.OrderDate.ToShortDateString() OrderDate.AddDays(8).ToShortDateString()

Taking it Further

Along the way, I have reminded you to make sure that you methods can be overridden in a derived class. While this implementation is useful as is, there are many steps that you could take to make it more feature rich. You may want to:

•  Add a Keyword to define the widths of individual columns
•  Add a Keyword to define the formatting for individual columns
•  Add support for exporting more than one sheet at a time
•  Add support for defining formulas

Be creative, and you will find many uses for this basic functionality

Conclusion

Here we have seen how to apply the MVC framework and design pattern to solve a common recurring problem that crops up in most application. By breaking the problem down in terms of MVC we have developed a rather simple and elegant solution that could find its way into every project that you work on.

I would love to hear back from you on the uses you find as well as the extensions you make to this approach.