Geeks With Blogs

News Google

Nick Harrison Blog<Nick>.Next()

In a recent blog, I wrote about Building A Custom View Engine to Create Excel Files in MVC.   In this blog, I skipped over the grammar that we might use in the View definition.

I have had a few people email me to fill in this gap.

If you haven’t already read this blog, I encourage you to do so before continuing.   Most of the content here will assume that you are already familiar with what we have already covered.

In this previous blog, we defined a new View Engine that could be used to build  and send Excel files back to the user as the view.   The goal being to separate the content of the View from the mechanics of creating the actual spread sheet.

Let’s consider a typical use case.  

You have a page that displays a list of search results and you want to export these results to Excel.   This is a very common user request.    By structuring this as an Excel view, we don’t have to get bogged down in the mechanics of creating the spread sheet and the grammar that we follow allows the View Engine to be oblivious to what is actually going into the final spreadsheet.

In defining our simple grammar.   We are going to make a couple of assumptions:

  • The Model passed to the View will be an IList<T>  where T is the type for the data being displayed
  • All of the values mapped to the excel file will be retrieved through properties of T (or properties of properties of T)
  • Our grammar will not support calling functions or conditional logic only property references

With these assumptions in mind, let’s consider what we may need to define for our Excel file.   We want to be able to specify:

  • Column Headers
  • Column Values
  • Column Widths

For this grammar we will have three keywords:


To keep things simple, we will require that each key word be on a line by itself and that the values for the key word be separated by the pipe character “|”

With these rules in place, a sample View Definition may look this:

   1:  HEADER:Unique Id|Name| PhoneNumber|Status|Payment Scheduled Date
   2:  VALUES: UniqueId | Name |  TelephoneNumber | DecodedStatus| PaymentsDate 
   3:  WIDTH:14|32|15|30|30

This will define that we 5 columns with these specified headers and we have specified widths for each of these columns.    The values listed after the VALUES: keyword will correspond to properties in the object making up the model.

We want to keep this grammar simple because we don’t have editor support for maintaining this file.   Also as long as you are using an View Model for your Model the restriction of requiring property references is not as restrictive as it may seem at first.    You can easily implement your property in the View Model to accommodate any function calls that would be required.

Our View Engine will stay the same from the previous blog with one exception.   The CreateView method now looks like this:

   1:  protected override IView CreateView(ControllerContext controllerContext, string viewPath, string masterPath)
   2:  {
   3:     var modelType = controllerContext.Controller.ViewData.ModelMetadata.ModelType;
   4:     var contentType = modelType.GetGenericArguments().FirstOrDefault();
   5:     var item = CreateGeneric(typeof (ExcelView<>), contentType
   6:                                       , viewPath) as IView;
   7:     return item;
   8:  }

Because we assume that the Model will be of type IList<T>, we only need to let the View know about the T.

Most of the changes in the View will be seen in the render method:

   1:  public void Render(ViewContext viewContext, TextWriter writer)

   2:  {
   3:      string filePath = viewContext.HttpContext.Server.MapPath(ViewPath);
   4:      string[] fileContents = File.ReadAllLines(filePath);
   5:      var model = viewContext.ViewData.Model as IList<ContentType>;
   6:      string action = viewContext.RouteData.Values["action"].ToString();
   7:      using (var excelPackage = new ExcelPackage())
   8:      {
   9:          ActiveSheet = excelPackage.Workbook.Worksheets.Add(action);
  10:          ActiveSheet.Name = action;
  11:          viewContext.HttpContext.Response.ClearContent();
  12:          CurrentRow = 1;
  13:          foreach (var line in fileContents)
  14:          {
  15:              ProcessHeader(line);
  16:              ProcessWidth(line);
  17:              ProcesValues(line,model);
  18:          }
  19:          var response = viewContext.HttpContext.Response;
  20:          response.BinaryWrite(excelPackage.GetAsByteArray());
  21:          response.AddHeader("content-disposition",
  22:                              string.Format("attachment;filename={0}.xlsx", action));
  23:          response.ContentType = "application/excel";
  24:          response.Flush();
  25:          response.End();
  26:      }
  27:  }

Everything should look familiar from the earlier blog up the three methods called called inside the for loop.

These three methods check for their specific keywords and handle the appropriate processing.


   1:  private void ProcessHeader( string line)
   2:  {
   3:      if (line.StartsWith("HEADER:"))
   4:      {
   5:          var template = line.Replace("HEADER:", "");
   7:          var headers = template.Split('|');
   8:          int col = 1;
   9:          foreach (var header in headers)
  10:          {
  11:              ActiveSheet.Cells[CurrentRow, col++].Value = header;
  12:          }
  13:          ActiveSheet.Cells[CurrentRow, 1, 1, col]
  14:              .Style.Font.Bold = true;
  15:          CurrentRow++;
  16:      }
  17:  }

We will simply output the values associated with the HEADER keyword unchanged.  

   1:  private void ProcessWidth( string line)
   2:  {
   3:      if (line.StartsWith("WIDTH:"))
   4:      {
   5:          var template = line.Replace("WIDTH:", "");
   6:          int col = 1;
   7:          foreach (var width in template.Split( '|'))
   8:          {
   9:              ActiveSheet.Column(col).Width = Int32.Parse(width);
  10:              col++;
  11:          }
  13:      }
  14:  }
The EPPlus library makes it easy to set the width of a column.


   1:  private void ProcesValues( string line, IEnumerable<ContentType> model)
   2:  {
   3:      if (line.StartsWith( "VALUES:"))
   4:      {
   5:          var template = line.Replace("LIST:", "");
   6:          var columns = template.Split('|');
   7:          foreach (var item in model)
   8:          {
   9:              int col = 1;
  10:              foreach (var column in columns)
  11:              {
  12:                  ActiveSheet.Cells[CurrentRow, col++].Value = 
  13:                      EvaluateValue(item, column);
  14:              }
  15:              CurrentRow++;
  16:          }
  17:      }
  18:  }


The outer loop loops through the items in the model while the inner loop loops through the values associated with the VALUES keyword.

The magic of pulling the requested value from the model happens in the EvaluateValue method:

   1:  private static string EvaluateValue (ContentType record, string value)
   2:  {
   3:      var genericArgument = typeof (ContentType);
   4:      var pe = Expression.Parameter(genericArgument, "p");
   5:      value = value.Trim();
   6:      if (string.IsNullOrEmpty(value))
   7:          return "";
   8:      var components = value.Split('.');
   9:      Expression referenceExpression = null;
  10:      if (components.Count() > 0)
  11:      {
  12:          referenceExpression = Expression.Property(pe, components[0]);
  13:          foreach (var component in components.Skip(1))
  14:          {
  15:              referenceExpression = Expression.Property(referenceExpression, component);
  16:          }
  17:      }
  18:      if (value.StartsWith("\""))
  19:          referenceExpression = Expression.Constant(value);
  20:      if (referenceExpression == null)
  21:          return "";
  22:      referenceExpression = Expression.Convert(referenceExpression, typeof (object));
  23:      var expression = Expression.Lambda<Func<ContentType, object>>
  24:          (referenceExpression, pe).Compile();
  25:      var data = expression.Invoke(record );
  26:      return data != null ? data.ToString() : "";
  27:  }


The foreach loop on line 13 shows the recursive calls that are needed to pull nested properties.    This allows you to refer to properties such as Data.Contact.Address.State

When you start worrying about performance or if you are dealing with large excel exports, you may want to consider caching the reference expressions to optimize this functions, but that should be fairly straightforward.

This simple grammar allows us to define some fairly complex Excel exports without ever having to write any excel code or deal with any of the objects in the EPPlus library outside of the View Engine.

This should provide a good foundation to build up your own grammar for defining Excel views.

Please share the grammars that you come up with or the use cases that you find.

Posted on Tuesday, March 5, 2013 4:17 PM MVC | Back to top

Comments on this post: A Simple Grammar for Excel Views in MVC

comments powered by Disqus

Copyright © Nick Harrison | Powered by: