Click here to monitor SSC
  • Av rating:
  • Total votes: 28
  • Total comments: 3
Nick Harrison

Building an Excel View Engine for ASP.NET MVC

22 August 2014

When we see a grid with some interesting data in it in an MVC application we, as users, expect to be able to download it as an Excel spreadsheet. If your application can't do it, it is no use telling the user that it is too difficult, because it isn't, as Nick Harrison explains.

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:

public void GetExcel()

{

    var list = GetData();

    using (var excelPackage = new ExcelPackage())

    {

        excelPackage.Workbook.Properties.Author = "Web App";

        excelPackage.Workbook.Properties.Title = "Export from the Web";

        var sheet = excelPackage.Workbook.Worksheets.Add("Export Results");

        // output a line for the headers

        CreateHeader(sheet);

        sheet.Name = "export results";

        // all indexes start at 1

        var rowIndex = 2;

        foreach (var item in list)

        {

            var col = 1;

            sheet.Cells[rowIndex, col++].Value = item.Description;

            sheet.Cells[rowIndex, col++].Value = item.ItemNumber;

            sheet.Cells[rowIndex, col++].Value = item.Quantity;

            sheet.Cells[rowIndex, col++].Value = item.Price;

            sheet.Cells[rowIndex, col++].Value = item.Total;

            rowIndex++;

        }

        sheet.Column(4).Style.Numberformat.Format = "$#,##0.00";

        sheet.Column(5).Style.Numberformat.Format = "$#,##0.00";

        // You could just save on ExcelPackage here but we need it in

        // memory to stream it back to the browser

        Response.ClearContent();

        Response.BinaryWrite(excelPackage.GetAsByteArray());

        Response.AddHeader("content-disposition",

                  "attachment;filename=results.xlsx");

        Response.ContentType = "application/excel";

        Response.Flush();

        Response.End();

    }

}

 

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.

public class ExcelViewEngine : VirtualPathProviderViewEngine

{

    public ExcelViewEngine()

    {

        // Specify which views will be handled by this View Engine

        FileExtensions = new[] { ".xcl" };

        ViewLocationFormats = new[] { "~/Views/{1}/{0}.xcl"};

        AreaViewLocationFormats = new[] { "~/Areas/{2}/Views/{1}/{0}.xcl" };

        PartialViewLocationFormats = ViewLocationFormats;

        MasterLocationFormats = new[] { "" };

    }

 

    protected override IView CreatePartialView(ControllerContext controllerContext,

                    string partialPath)

    {

        throw new NotImplementedException();

    }

 

    protected override IView CreateView(ControllerContext controllerContext,

                   string viewPath, string masterPath)

    {

        // Grab the data type for the Model

        var modelType = controllerContext.Controller.ViewData.ModelMetadata.ModelType;

        // Create an Instance of ExcelView<> using the data type of the model as the

        // generic argument

        var item = CreateGeneric(typeof(ExcelView<>), modelType

                                    , viewPath) as IView;

        return item;

    }

}

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:

            ViewEngines.Engines.Add(new ExcelViewEngine());

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

    public interface IView

    {

        void Render(ViewContext viewContext, TextWriter writer);

    }

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:

        public ExcelView(string viewPath)

        {

            ViewPath = viewPath;

        }

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:

public void Render(ViewContext viewContext, TextWriter writer)

        {

            string filePath = viewContext.HttpContext.Server.MapPath(ViewPath);

            List<string> fileContents = File.ReadAllLines(filePath).ToList();

            var modelList = viewContext.ViewData.Model;

            string action = viewContext.RouteData.Values["action"].ToString();

            using (var excelPackage = new ExcelPackage())

            {

                 // If we don’t get a list there is nothing to do

                if (modelList is ICollection)

                {

                    CreateWorksheet(excelPackage, action, fileContents,

                            modelList, viewContext);

                }

 

                var response = viewContext.HttpContext.Response;

                // We could have saved to a file from the Excel Package

                // but we want to keep it as a memory stream to send to browser

                response.BinaryWrite(excelPackage.GetAsByteArray());

                response.AddHeader("content-disposition",

                          string.Format("attachment;filename={0}.xlsx", action));

                response.ContentType = "application/excel";

                response.Flush();

                response.End();

            }

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:

protected virtual void CreateWorksheet(ExcelPackage excelPackage,

             string name, List<string> fileContents, object model,

             ViewContext viewContext)

        {

            ActiveSheet = excelPackage.Workbook.Worksheets.Add(name);

            // Sheets names can be at most 30 characters

            if (name.Length > 30)

                ActiveSheet.Name = name.Substring(0, 29);

            else

                ActiveSheet.Name = name;

            viewContext.HttpContext.Response.ClearContent();

            CurrentRow = 1;

 

            foreach (var line in fileContents)

            {

                ProcessHeader(line);

                ProcesValues(line, model);

            }

 

        }

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.

    protected virtual void ProcessHeader(string line)

    {

        if (line.StartsWith("HEADER:"))

        {

            var template = line.Replace("HEADER:", "");

 

            var headers = template.Split('|');

            int col = 1;

            foreach (var header in headers)

            {

                ActiveSheet.Cells[CurrentRow, col++].Value = header;

            }

            ActiveSheet.Cells[CurrentRow, 1, 1, col]

                .Style.Font.Bold = true;

            CurrentRow++;

        }

    }

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:

private void ProcesValues<T>(string line, T model)

{

    if (line.StartsWith("LIST:"))

    {

        var template = line.Replace("LIST:", "");

        var columns = template.Split('|');

        if (model != null && model is ICollection)

        {

            foreach (var item in (IEnumerable)model)

            {

                int col = 1;

                foreach (var column in columns)

                {

                    string value = EvaluateListValue(item, column);

                        ActiveSheet.Cells[CurrentRow, col++].Value = value;

                }

                CurrentRow++;

            }

        }

    }

}

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.

protected virtual string EvaluateListValue<T>(T record, string value)

{

    value = value.Trim();

    if (string.IsNullOrEmpty(value))

        return "";

    var components = value.Split('.');

    object currentObject = record;

    foreach (var component in components)

    {

        currentObject = currentObject.GetType().GetProperty(component)

              .GetValue(currentObject, null);

        if (currentObject == null)

        {

            currentObject = "";

            break;

        }

    }

    return currentObject.ToString();

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.Name
  • LastOrder.OrderDate
  • Item.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.

Nick Harrison

Author profile:

Nick Harrison is a Software Architect and .NET advocate in Columbia, SC. Nick has over 18 years experience in software developing, starting with Unix system programming and then progressing to the DotNet platform. You can read his blog as www.geekswithblogs.net/nharrison

Search for other articles by Nick Harrison

Rate this article:   Avg rating: from a total of 28 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Problems with integration of views and viewengine
Posted by: babadin (view profile)
Posted on: Wednesday, January 21, 2015 at 3:12 AM
Message: Hello, this method is very interesting, and seems like an elegant solution to an old problem.
By the way, could you provide a sample project ? I have some difficulties to gather all the snippets in my code, and it would be great if I could see the whole picture.
Thanks

Subject: Code Question
Posted by: adharbert (view profile)
Posted on: Sunday, March 8, 2015 at 8:43 PM
Message: Looking at the example, did you forgot to add the function for CreateGeneric()? All I see is this var item = CreateGeneric(typeof(ExcelView<>), modelType
, viewPath) as IView;

There is nothing showing what this does. Like the person before me, could you provide sample code for this as well?

Subject: Code suggestion
Posted by: sunny_lnct (view profile)
Posted on: Friday, April 10, 2015 at 1:35 PM
Message: Nice article Nick. If we are simply trying to export the excel then can't we use FileResult action type instead of creating a new one.

 

Top Rated

Building a Customised ALM Platform with TFS and VSO
 The latest versions of Team Foundation Server are not only sophisticated, but extensible. Continue... Read more...

Rethinking the Practicalities of Recursion
 We all love recursion right up to the point of actually using it in production code. Why? Recursion... Read more...

Acceptance Testing with FitNesse: Multiplicities and Comparisons
 FitNesse is one of the most popular tools for unit testing since it is designed with a Wiki-style... Read more...

Acceptance Testing with FitNesse: Documentation and Infrastructure
 FitNesse is a popular general-purpose wiki-based framework for writing acceptance tests for software... Read more...

Prototyping Desktop Deblector
 Deblector is an open-source debugging add-in for .NET Reflector; the Reflector team investigated... Read more...

Most Viewed

A Complete URL Rewriting Solution for ASP.NET 2.0
 Ever wondered whether it's possible to create neater URLS, free of bulky Query String parameters?... Read more...

Visual Studio Setup - projects and custom actions
 This article describes the kinds of custom actions that can be used in your Visual Studio setup project. Read more...

.NET Application Architecture: the Data Access Layer
 Find out how to design a robust data access layer for your .NET applications. Read more...

Calling Cross Domain Web Services in AJAX
 The latest craze for mashups involves making cross-domain calls to Web Services from APIs made publicly... Read more...

10 Reasons Why Visual Basic is Better Than C#
 After having converted a whole lot of training materials based on VB.NET into C#, Andy ‘Wise Owl’ Brown... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.