{"id":1857,"date":"2014-08-22T00:00:00","date_gmt":"2014-08-22T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/building-an-excel-view-engine-for-asp-net-mvc\/"},"modified":"2021-05-17T18:34:52","modified_gmt":"2021-05-17T18:34:52","slug":"building-an-excel-view-engine-for-asp-net-mvc","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/development\/dotnet-development\/building-an-excel-view-engine-for-asp-net-mvc\/","title":{"rendered":"Building an Excel View Engine for ASP.NET MVC"},"content":{"rendered":"<div id=\"pretty\">\n<p class=\"start\">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.<\/p>\n<h1>What Can We Learn from MVC<\/h1>\n<p>Let&#8217;s take a page from the MVC playbook to make this a bit easier to provide. From the MVC pattern&#8217;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.<\/p>\n<p>This leaves us with the View.<\/p>\n<p>Razor is Microsoft&#8217;s most recent View Engine for the MVC framework and probably the most popular, but it is far from the only option. <a href=\"https:\/\/github.com\/SparkViewEngine\">Spark<\/a><span class=\"MsoHyperlink\">, for example, <\/span> might be useful if you are already familiar with the Castle Project MonoRail. <a href=\"http:\/\/haml.info\/\">HAML<\/a>, too is preferred when the primary goal is in making the markup more readable, because it can result in elegant minimal markup.<\/p>\n<p>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.<\/p>\n<h1>EPPlus Library<\/h1>\n<p><a href=\"http:\/\/epplus.codeplex.com\/\">EPPLus<\/a> 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.<\/p>\n<p>To fully appreciate this library, let&#8217;s look at what it might look like to have to code by hand a simple table export.\u00a0 \u00a0\u00a0Suppose 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:<\/p>\n<ul>\n<li>Item Number<\/li>\n<li>Description<\/li>\n<li>Quantity<\/li>\n<li>Price<\/li>\n<li>Total<\/li>\n<\/ul>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2040-Clip1.png\" alt=\"2040-Clip1.png\" \/><\/p>\n<p>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:<\/p>\n<pre class=\"lang:c# theme:vs2012\">public void GetExcel()\r\n{\r\n\u00a0\u00a0\u00a0 var list = GetData();\r\n\u00a0\u00a0\u00a0 using (var excelPackage = new ExcelPackage())\r\n\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 excelPackage.Workbook.Properties.Author = \"Web App\";\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 excelPackage.Workbook.Properties.Title = \"Export from the Web\";\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 var sheet = excelPackage.Workbook.Worksheets.Add(\"Export Results\");\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ output a line for the headers\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CreateHeader(sheet);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sheet.Name = \"export results\";\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ all indexes start at 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 var rowIndex = 2;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 foreach (var item in list)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 var col = 1;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sheet.Cells[rowIndex, col++].Value = item.Description;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sheet.Cells[rowIndex, col++].Value = item.ItemNumber;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sheet.Cells[rowIndex, col++].Value = item.Quantity;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sheet.Cells[rowIndex, col++].Value = item.Price;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sheet.Cells[rowIndex, col++].Value = item.Total;\r\n\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0rowIndex++;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sheet.Column(4).Style.Numberformat.Format = \"$#,##0.00\";\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sheet.Column(5).Style.Numberformat.Format = \"$#,##0.00\";\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ You could just save on ExcelPackage here but we need it in \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ memory to stream it back to the browser\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Response.ClearContent();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Response.BinaryWrite(excelPackage.GetAsByteArray());\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Response.AddHeader(\"content-disposition\", \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"attachment;filename=results.xlsx\");\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Response.ContentType = \"application\/excel\";\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Response.Flush();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Response.End();\r\n\u00a0\u00a0\u00a0 }\r\n}\r\n\u00a0\r\n<\/pre>\n<p>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.<\/p>\n<p>This layer of abstraction will be our View Engine<\/p>\n<h1>Wait, I Can Write My Own View Engine?<\/h1>\n<p>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.<\/p>\n<p>It turns out that the mechanics for creating a View Engine are actually rather simple. We will define a class derived from <code>VirtualPathProviderViewEngine<\/code>. In this class, there are just a couple of methods that we will need to override.<\/p>\n<p>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.<\/p>\n<pre class=\"lang:c# theme:vs2012\">public class ExcelViewEngine : VirtualPathProviderViewEngine\r\n{\r\n\u00a0\u00a0\u00a0 public ExcelViewEngine()\r\n\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ Specify which views will be handled by this View Engine\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FileExtensions = new[] { \".xcl\" };\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ViewLocationFormats = new[] { \"~\/Views\/{1}\/{0}.xcl\"};\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AreaViewLocationFormats = new[] { \"~\/Areas\/{2}\/Views\/{1}\/{0}.xcl\" };\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PartialViewLocationFormats = ViewLocationFormats;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MasterLocationFormats = new[] { \"\" };\r\n\u00a0\u00a0\u00a0 }\r\n\u00a0\r\n\u00a0\u00a0\u00a0 protected override IView CreatePartialView(ControllerContext controllerContext, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 string partialPath)\r\n\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 throw new  NotImplementedException();\r\n\u00a0\u00a0\u00a0 }\r\n\u00a0\r\n\u00a0\u00a0\u00a0 protected override IView CreateView(ControllerContext controllerContext, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 string viewPath, string masterPath)\r\n\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ Grab the data type for the Model\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 var modelType = controllerContext.Controller.ViewData.ModelMetadata.ModelType;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ Create an Instance of ExcelView&lt;&gt; using the data type of the model as the\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ generic argument\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 var item = CreateGeneric(typeof(ExcelView&lt;&gt;), modelType\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , viewPath) as IView;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return item;\r\n\u00a0\u00a0\u00a0 }\r\n}\r\n<\/pre>\n<p>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&#8217;t support partial views. This is fine, because in the context of an Excel file, a partial View wouldn&#8217;t really make sense anyway.<\/p>\n<p>Most of the work is actually going to be handled in the <code>ExcelView&lt;&gt;<\/code> class. When we declare this <code>ExcelView&lt;&gt;<\/code> class, we pass in the type of the Model as a generic argument. This will be very important shortly.<\/p>\n<p>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 <code>Application_Start <\/code>event handler, add the following line of code:<\/p>\n<pre class=\"lang:c# theme:vs2012\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0  ViewEngines.Engines.Add(new ExcelViewEngine());<\/pre>\n<h1>So the Tricky Part is Creating the View<\/h1>\n<p>\u00a0To create the View, we don&#8217;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<\/p>\n<pre class=\"lang:c# theme:vs2012\">\u00a0\u00a0\u00a0 public interface IView\r\n\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 void Render(ViewContext viewContext, TextWriter writer);\r\n\u00a0\u00a0\u00a0 }\r\n<\/pre>\n<p>Obviously the complexities come into play with the implementation of this Render method. Let&#8217;s start by investigating this ViewContext object that we get.<\/p>\n<p>There are a couple of properties here that we will initially be very interested in:<\/p>\n<ul>\n<li><code>viewContext.HttpContext<\/code> gives us access to Server, Request, Response, etc.<\/li>\n<li><code>viewContext.ViewData.Model<\/code> gives us access to the Model that was passed to the View<\/li>\n<li><code>viewContext.RouteData<\/code> gives us the full route information. Most importantly, we can trace back to the Action with <code>viewContext.RouteData.Values[\"action\"].ToString()<\/code><\/li>\n<\/ul>\n<p>While the Interface does not require a Constructor, we know that we will need one based on how the View was instantiated in the <code>ViewEngine.<\/code> Fortunately, our constructor is rather simple:<\/p>\n<pre class=\"lang:c# theme:vs2012\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public ExcelView(string viewPath)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ViewPath = viewPath;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n<\/pre>\n<p>The <code>ViewPath<\/code> will give us access to the file location where our View definition is stored. This will come in handy soon.<\/p>\n<p>Working with a top-down design, our Render method can be as simple as this:<\/p>\n<pre class=\"lang:c# theme:vs2012\">public void Render(ViewContext viewContext, TextWriter writer)\r\n\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0{\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 string filePath = viewContext.HttpContext.Server.MapPath(ViewPath);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 List&lt;string&gt; fileContents = File.ReadAllLines(filePath).ToList();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 var modelList = viewContext.ViewData.Model;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 string action = viewContext.RouteData.Values[\"action\"].ToString();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 using (var excelPackage = new ExcelPackage())\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ If we don't get a list there is nothing to do\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if (modelList is ICollection)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CreateWorksheet(excelPackage, action, fileContents, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 modelList, viewContext);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 var response = viewContext.HttpContext.Response;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ We could have saved to a file from the Excel Package \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ but we want to keep it as a memory stream to send to browser\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 response.BinaryWrite(excelPackage.GetAsByteArray());\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 response.AddHeader(\"content-disposition\",\r\n\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0string.Format(\"attachment;filename={0}.xlsx\", action));\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 response.ContentType = \"application\/excel\";\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 response.Flush();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 response.End();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n<\/pre>\n<p>This method is simple because we skip over two key details.<\/p>\n<ul>\n<li>What will be in the View file<\/li>\n<li>What are we doing in the <code>CreateWorksheet<\/code> method<\/li>\n<\/ul>\n<p>It turns out that both of these questions are related.<\/p>\n<h1>Towards a Simple Grammar<\/h1>\n<p>\u00a0We 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.\u00a0\u00a0\u00a0\u00a0 We have already seen that we are stipulating that the Model must be an<code> ICollection <\/code>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.<\/p>\n<p>At this point, our grammar will allow 2 lines. Any other data will be ignored. So our grammar can be as simple as this:<\/p>\n<ul>\n<li><strong>HEADER:<\/strong> Vertical bar separated list of column headers<\/li>\n<li><strong>LIST:<\/strong> Vertical bar separated list of property references from the Model<\/li>\n<\/ul>\n<p>\u00a0With this simple grammar, the View file to export the grid that we saw initially would look like this:<\/p>\n<ul>\n<li><code>HEADER: Description| Inem Number | Quantity | Price | Total<\/code><\/li>\n<li><code>LIST:Description| InemNumber | Quantity | Price | Total<\/code><\/li>\n<\/ul>\n<p>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.<\/p>\n<h1>CreateWorksheet<\/h1>\n<p>Continuing with a Top Down Design, we might structure our CreateWorksheet like this:<\/p>\n<pre class=\"lang:c# theme:vs2012\">protected virtual void CreateWorksheet(ExcelPackage excelPackage, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 string name, List&lt;string&gt; fileContents, object model, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ViewContext viewContext)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ActiveSheet = excelPackage.Workbook.Worksheets.Add(name);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ Sheets names can be at most 30 characters\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if (name.Length &gt; 30)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ActiveSheet.Name = name.Substring(0, 29);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 else\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ActiveSheet.Name = name;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 viewContext.HttpContext.Response.ClearContent();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CurrentRow = 1;\r\n\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 foreach (var line in fileContents)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ProcessHeader(line);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ProcesValues(line, model);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n<\/pre>\n<p>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&#8217;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 methods<code>ProcessHeader<\/code> and <code>ProcessValues.<\/code><\/p>\n<p><code>ProcessHeader <\/code> is fairly straight forward.<\/p>\n<pre class=\"lang:c# theme:vs2012\">\u00a0\u00a0\u00a0 protected virtual void ProcessHeader(string line)\r\n\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if (line.StartsWith(\"HEADER:\"))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 var template = line.Replace(\"HEADER:\", \"\");\r\n\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 var headers = template.Split('|');\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 int col = 1;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 foreach (var header in headers)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ActiveSheet.Cells[CurrentRow, col++].Value = header;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ActiveSheet.Cells[CurrentRow, 1, 1, col]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .Style.Font.Bold = true;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CurrentRow++;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0 }\r\n<\/pre>\n<p>All we have to do here is to verify that the line in question starts with our keyword <code>HEADER<\/code>, then we strip out the Keyword and split on the delimiter character. \u00a0\u00a0\u00a0This will give us a list of headers that we then loop through and make bold.<\/p>\n<p>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.<\/p>\n<p>Now <code>ProcessValues<\/code> will be just a little bit more complicated:<\/p>\n<pre class=\"lang:c# theme:vs2012\">private void ProcesValues&lt;T&gt;(string line, T model)\r\n{\r\n\u00a0\u00a0\u00a0 if (line.StartsWith(\"LIST:\"))\r\n\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 var template = line.Replace(\"LIST:\", \"\");\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 var columns = template.Split('|');\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if (model != null &amp;&amp; model is ICollection)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 foreach (var item in (IEnumerable)model)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 int col = 1;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 foreach (var column in columns)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 string value = EvaluateListValue(item, column);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ActiveSheet.Cells[CurrentRow, col++].Value = value;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CurrentRow++;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0 }\r\n}\r\n<\/pre>\n<p>The first few parts should look very similar to what we did with <code>ProcessHeader<\/code>. 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 <code>EvaluateListValue<\/code> method.<\/p>\n<pre class=\"lang:c# theme:vs2012\">protected virtual string EvaluateListValue&lt;T&gt;(T record, string value)\r\n{\r\n\u00a0\u00a0\u00a0 value = value.Trim();\r\n\u00a0\u00a0\u00a0 if (string.IsNullOrEmpty(value))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return \"\";\r\n\u00a0\u00a0\u00a0 var components = value.Split('.');\r\n\u00a0\u00a0\u00a0 object currentObject = record;\r\n\u00a0\u00a0\u00a0 foreach (var component in components)\r\n\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 currentObject = currentObject.GetType().GetProperty(component)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .GetValue(currentObject, null);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if (currentObject == null)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 currentObject = \"\";\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 break;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0 return currentObject.ToString();\r\n<\/pre>\n<p>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.\u00a0\u00a0\u00a0\u00a0 This means that we could evaluate references such as:<\/p>\n<p><code>\u00a0OrderedBy.NameLastOrder.OrderDateItem.ItemNumber<\/code><\/p>\n<p>If you structure your Model properly, this should not be needed, but we don&#8217;t always structure our Models properly.<\/p>\n<p>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:<\/p>\n<p><code>OrderedBy.Name.ToUpper() LastOrder.OrderDate.ToShortDateString() OrderDate.AddDays(8).ToShortDateString()<\/code><\/p>\n<h1>Taking it Further<\/h1>\n<p>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:<\/p>\n<ul>\n<li>\u00a0Add a Keyword to define the widths of individual columns<\/li>\n<li>\u00a0Add a Keyword to define the formatting for individual columns<\/li>\n<li>\u00a0Add support for exporting more than one sheet at a time<\/li>\n<li>\u00a0Add support for defining formulas<\/li>\n<\/ul>\n<p>Be creative, and you will find many uses for this basic functionality<\/p>\n<h1>Conclusion<\/h1>\n<p>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.<\/p>\n<p>I would love to hear back from you on the uses you find as well as the extensions you make to this approach.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>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&#8217;t do it, it is no use telling the user that it is too difficult, because it isn&#8217;t, as Nick Harrison explains.&hellip;<\/p>\n","protected":false},"author":221853,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143538],"tags":[4143,4156,4157,5166],"coauthors":[11316],"class_list":["post-1857","post","type-post","status-publish","format-standard","hentry","category-dotnet-development","tag-net","tag-asp","tag-asp-net","tag-mvc"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1857","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221853"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1857"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1857\/revisions"}],"predecessor-version":[{"id":72868,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1857\/revisions\/72868"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1857"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1857"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1857"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1857"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}