Geeks With Blogs

@neh123us
  • neh123us T4 sould be your outsourcing strategy https://t.co/1ZceCUKWKE T4 - The Insource Code Monkey about 563 days ago
  • neh123us Had a need for Dynamic Views in MVC today. Wanted to use a Partial view on two different views with different ViewModels about 611 days ago

News Google

Nick Harrison Blog<Nick>.Next()

In any project you often need to export data as excel.  This are several approaches that you can follow to do this.

One approach, would be to setup the environment for a GridView and then bind your list to this:

   1:  public void GetExcel()
   2:  {
   3:      var data = GetData();
   4:   
   5:      var list = Mapper.Map<IList<ViewModel>,
   6:          IList<Model>>(data);
   7:      var grd = new GridView { DataSource = list, AutoGenerateColumns = true };
   8:      grd.DataBind();
   9:   
  10:      Response.ClearContent();
  11:      Response.AddHeader("Content-type", "application/vnd.ms-excel");
  12:      Response.AddHeader("content-disposition", "attachment;filename=export.xls");
  13:      Response.ContentType = "application/excel";
  14:      var swr = new StringWriter();
  15:      var tw = new HtmlTextWriter(swr);
  16:      grd.RenderControl(tw);
  17:      Response.Write(swr.ToString());
  18:      Response.Flush();
  19:      Response.End();
  20:      tw.Close();
  21:      swr.Close();
  22:  }

This works for simple formatting, but sometimes you need more.

To have more control over the output, you may opt to use a library like the EPPlus implementation of OfficeOpenXML library    http://epplus.codeplex.com/.   

With this library, you can do something like this:

   1:  public void GetExcel()
   2:  {
   3:      var data = GetData();
   4:   
   5:      var list = Mapper.Map<IList<ViewModel>,
   6:          IList<Model>>(data);
   7:      using (var excelPackage = new ExcelPackage())
   8:      {
   9:          excelPackage.Workbook.Properties.Author = "DooLittle";
  10:          excelPackage.Workbook.Properties.Title = "Export from DooLittle";
  11:          var sheet = excelPackage.Workbook.Worksheets.Add("Export Results");
  12:   
  13:   
  14:          CreateHeader(sheet);
  15:          sheet.Name = "export results";
  16:          var rowIndex = 2;
  17:   
  18:          foreach (var item in list)
  19:          {
  20:              var col = 1;
  21:              sheet.Cells[rowIndex, col++].Value = item.ApprovalLetterDate.Value.ToShortDateString();
  22:              sheet.Cells[rowIndex, col++].Value = item.ApprovalGoodThruDate.Value.ToShortDateString();
  23:              sheet.Cells[rowIndex, col++].Value = item.ServicerName;
  24:              sheet.Cells[rowIndex, col++].Value = item.UniqueId;
  25:              rowIndex++;
  26:          }
  27:          sheet.Column(1).Style.Numberformat.Format = "mm/dd/yyyy";
  28:          Response.ClearContent();
  29:          Response.BinaryWrite(excelPackage.GetAsByteArray());
  30:          Response.AddHeader("content-disposition", "attachment;filename=results.xlsx");
  31:          Response.ContentType = "application/excel";
  32:          Response.Flush();
  33:          Response.End();
  34:      }
  35:  }

This is nice and gives us complete control over the output, but it is tedious code and the format and content is hard coded in the controller.

It would be ideal to be able to hide all of the tedious code in a View Engine and then specify the content in a custom excel View.    This way, you don’t have to change code and recompile to change the look and feel of the spread sheet.

Our View Engine can be as simple or as complicated as we want depending on how many features we want to support.   For this simple example, our view can consist simply of lines looking like this:

 

   1:  a1:"MORTGAGOR/CO-MTGR INFO"
   2:  a3:"MORTGAGOR NAME"
   3:  b3:Model.Name
   4:  a4:"MAILING ADDRESS"
   5:  b4:Model.StreetAddress
   6:  a5:"CITY"
   7:  b5:Model.City

The data before the colon identifies a cell in the spread sheet.   The value after the colon identifies the content.   If it is in quotes, it will be treated as a string literal, otherwise we will view it as a property of the Model.

Ultimately we would want to extend this to allow us to specify formatting, but we will keep it simple for now.

The actual View Engine is fairly straightforward.   Most of the magic will come into play rendering the ExcelView.   The major complication we have here is passing the strongly typed model to the View.   For this, we have to create a strongly typed view without knowing the type at compile time.

The other critical piece to note is the ViewLocationFormats.   Here we specify that our views will have an xcl extension.  Also note that we do not support Partial Views.   They don’t really make sense in the context of a spreadsheet.

   1:  public class ExcelViewEngine :VirtualPathProviderViewEngine
   2:  {
   3:      public ExcelViewEngine()
   4:      {
   5:          ViewLocationFormats = new[] { "~/Views/{1}/{0}.xcl" };
   6:          PartialViewLocationFormats = ViewLocationFormats;
   7:      }
   8:   
   9:      protected override IView CreatePartialView(ControllerContext controllerContext, string partialPath)
  10:      {
  11:          throw new NotImplementedException();
  12:      }
  13:          
  14:      protected override IView CreateView(ControllerContext controllerContext, string viewPath, string masterPath)
  15:      {
  16:          var modelType = controllerContext.Controller.ViewData.ModelMetadata.ModelType;
  17:          var item = CreateGeneric(typeof (ExcelView<>),modelType
  18:                                      , viewPath) as IView;
  19:          return item;
  20:      }
  21:   
  22:      public static object CreateGeneric(Type generic, Type innerType, params object[] args)
  23:      {
  24:          var specificType = generic.MakeGenericType(new[] { innerType });
  25:          return Activator.CreateInstance(specificType, args);
  26:      }
  27:  }

Now let’s turn our attention to the View itself.

In the object declaration, we specify the type for the model and note that we are implementing the IView interface.  

   1:  public class ExcelView <T>: IView where T : class
   2:  {
   3:      public ExcelView(string viewPath)
   4:      {
   5:          ViewPath = viewPath;
   6:      }
   7:      public string ViewPath { get; private set; }

The only thing that IView interface requires is that we provide a Render method.   This render method will get the ViewContext and a TextWriter.   Normally we would render our content directly to this TextWriter, but in this case, our content is not going out as text and will not be merged with any other content.   We will ignore this parameter and explicitly the Response and replace it with our content

   1:  public void Render(ViewContext viewContext, TextWriter writer)
   2:  {
   3:      string filePath = viewContext.HttpContext.Server.MapPath(this.ViewPath);
   4:      var fileContents = File.ReadAllLines(filePath);
   5:      var model = viewContext.ViewData.Model;
   6:      var action = viewContext.RouteData.Values["action"].ToString();
   7:      using (var excelPackage = new ExcelPackage())
   8:      {
   9:          excelPackage.Workbook.Properties.Author = "DooLittle";
  10:          excelPackage.Workbook.Properties.Title = "Export from DooLittle";
  11:          var sheet = excelPackage.Workbook.Worksheets.Add("Export Results");
  12:          sheet.Name = action;
  13:          viewContext.HttpContext.Response.ClearContent();
  14:          foreach (var line in fileContents)
  15:          {
  16:              var components = line.Split(':');
  17:              var match = Regex.Match(components[0], @"(?<col>[a-zA-Z]+)(?<row>\d+)");
  18:              var col = TranslateColumnNameToIndex(match.Groups["col"].Value);
  19:              var row = int.Parse(match.Groups["row"].Value);
  20:              sheet.Cells[row, col].Value = EvaluateValue(model as T, components[1]);
  21:          }
  22:          var response = viewContext.HttpContext.Response;
  23:          response.BinaryWrite(excelPackage.GetAsByteArray());
  24:          response.AddHeader("content-disposition", 
  25:              string.Format("attachment;filename={0}.xlsx", action));
  26:          response.ContentType = "application/excel";
  27:          response.Flush();
  28:          response.End();
  29:      }
  30:  }

The highlighted section in the foreach loop implements the simplistic grammar that we discussed earlier.    This is the section to change to provide a more robust grammar.

The EvaluateValue method is also particularly interesting.    Here we play with lambda expressions to get the specified value:

   1:  private static string EvaluateValue(T record, string value)
   2:  {
   3:      var type = typeof (T);
   4:      var pe = Expression.Parameter(type, "p");
   5:      value = value.Trim();
   6:      var components = value.Split('.');
   7:      Expression referenceExpression = null;
   8:      if (components.Count() == 2)
   9:      {
  10:          referenceExpression = Expression.Property(pe, components[1]);
  11:      }
  12:      if (value.StartsWith("\""))
  13:          referenceExpression = Expression.Constant(value);
  14:      if (referenceExpression == null)
  15:          return "";
  16:      var expression = Expression.Lambda<Func<T, object>>(referenceExpression, pe).Compile();
  17:      var data = expression.Invoke(record);
  18:      if (data != null)
  19:          return data.ToString();
  20:      return "";
  21:  }

For our simple grammar we will build either a constant expression or a property reference expression.    We will convert the expression to a Lambda expression, compile it, and then invoke it to get the value referenced.

This is ample room for improvement to the grammar for the View.    This also needs to be extended to support Areas, but the basics for providing a strongly typed view that will output Excel is in place.

As you build up the grammar, the same EvaluateValue method could be used to supply values to a PDFViewEngine or a DOCWordEngine, or whatever format besides HTML you might be called upon to support.

To use this view engine, our controller could be as simple as this:

 

   1:  public ActionResult CustomTest(int id)
   2:  {
   3:      var repository = new Repository();
   4:      var record = repository.Get(id);
   5:      return View(record);
   6:  }
As long as the View, ends with .xcl, our new View Engine will take it from there. Posted on Wednesday, February 13, 2013 1:29 PM MVC | Back to top


Comments on this post: Build A Custom View Engine to Create Excel Files in MVC

comments powered by Disqus

Copyright © Nick Harrison | Powered by: GeeksWithBlogs.net | Join free