Subscribe to this Blog

Subscribe to this Blog

ASP.NET MVC Tip #2 - Create a custom Action Result that returns Microsoft Excel Documents - Stephen Walther on ASP.NET MVC

ASP.NET MVC Tip #2 - Create a custom Action Result that returns Microsoft Excel Documents

In this tip, I show you how to create a custom action result that you can return from an ASP.NET MVC controller action. This action result generates a Microsoft Excel Document from a LINQ to SQL query.

In an MVC application, a controller action returns an action result. In particular, it returns something that derives from the base ActionResult class such as:

· ViewResult

· EmptyResult

· RedirectResult

· RedirectToRouteResult

· JsonResult

· ContentResult

For example, you use a ViewResult to return a particular view to the browser and a ContentResult to return text content to the browser.

But, what if you want to return some other type of content to a browser such as an image, a PDF file, or a Microsoft Excel document? In these cases, you can create your own action result. In this tip, I show you how to create an action result that returns a Microsoft Excel document.

The code for the ExcelResult is contained in Listing 1.

Listing 1 – ExcelResult.vb (VB)

   1: Imports System
   2: Imports System.Web.Mvc
   3: Imports System.Data.Linq
   4: Imports System.Collections
   5: Imports System.IO
   6: Imports System.Web.UI.WebControls
   7: Imports System.Linq
   8: Imports System.Web
   9: Imports System.Web.UI
  10: Imports System.Drawing
  11:  
  12:  
  13: Namespace Tip2
  14:  
  15:     Public Class ExcelResult
  16:         Inherits ActionResult
  17:  
  18:         Private _dataContext As DataContext
  19:         Private _fileName As String
  20:         Private _rows As IQueryable
  21:         Private _headers() As String = Nothing
  22:  
  23:         Private _tableStyle As TableStyle
  24:         Private _headerStyle As TableItemStyle
  25:         Private _itemStyle As TableItemStyle
  26:  
  27:         Public ReadOnly Property FileName() As String
  28:             Get
  29:                 Return _fileName
  30:             End Get
  31:         End Property
  32:  
  33:         Public ReadOnly Property Rows() As IQueryable
  34:             Get
  35:                 Return _rows
  36:             End Get
  37:         End Property
  38:  
  39:  
  40:         Public Sub New(ByVal dataContext As DataContext, ByVal rows As IQueryable, ByVal fileName As String)
  41:             Me.New(dataContext, rows, fileName, Nothing, Nothing, Nothing, Nothing)
  42:         End Sub
  43:  
  44:         Public Sub New(ByVal dataContext As DataContext, ByVal fileName As String, ByVal rows As IQueryable, ByVal headers() As String)
  45:             Me.New(dataContext, rows, fileName, headers, Nothing, Nothing, Nothing)
  46:         End Sub
  47:  
  48:         Public Sub New(ByVal dataContext As DataContext, ByVal rows As IQueryable, ByVal fileName As String, ByVal headers() As String, ByVal tableStyle As TableStyle, ByVal headerStyle As TableItemStyle, ByVal itemStyle As TableItemStyle)
  49:             _dataContext = dataContext
  50:             _rows = rows
  51:             _fileName = fileName
  52:             _headers = headers
  53:             _tableStyle = tableStyle
  54:             _headerStyle = headerStyle
  55:             _itemStyle = itemStyle
  56:  
  57:             ' provide defaults
  58:             If _tableStyle Is Nothing Then
  59:                 _tableStyle = New TableStyle()
  60:                 _tableStyle.BorderStyle = BorderStyle.Solid
  61:                 _tableStyle.BorderColor = Color.Black
  62:                 _tableStyle.BorderWidth = Unit.Parse("2px")
  63:             End If
  64:             If _headerStyle Is Nothing Then
  65:                 _headerStyle = New TableItemStyle()
  66:                 _headerStyle.BackColor = Color.LightGray
  67:             End If
  68:         End Sub
  69:  
  70:         Public Overrides Sub ExecuteResult(ByVal context As ControllerContext)
  71:             ' Create HtmlTextWriter
  72:             Dim sw As StringWriter = New StringWriter()
  73:             Dim tw As HtmlTextWriter = New HtmlTextWriter(sw)
  74:  
  75:             ' Build HTML Table from Items
  76:             If Not _tableStyle Is Nothing Then
  77:                 _tableStyle.AddAttributesToRender(tw)
  78:             End If
  79:             tw.RenderBeginTag(HtmlTextWriterTag.Table)
  80:  
  81:             ' Generate headers from table
  82:             If _headers Is Nothing Then
  83:                 _headers = _dataContext.Mapping.GetMetaType(_rows.ElementType).PersistentDataMembers.Select(Function(m) m.Name).ToArray()
  84:             End If
  85:  
  86:  
  87:             ' Create Header Row
  88:             tw.RenderBeginTag(HtmlTextWriterTag.Thead)
  89:             For Each header As String In _headers
  90:                 If Not _headerStyle Is Nothing Then
  91:                     _headerStyle.AddAttributesToRender(tw)
  92:                 End If
  93:                 tw.RenderBeginTag(HtmlTextWriterTag.Th)
  94:                 tw.Write(header)
  95:                 tw.RenderEndTag()
  96:             Next
  97:             tw.RenderEndTag()
  98:  
  99:  
 100:  
 101:             ' Create Data Rows
 102:             tw.RenderBeginTag(HtmlTextWriterTag.Tbody)
 103:             For Each row As Object In _rows
 104:                 tw.RenderBeginTag(HtmlTextWriterTag.Tr)
 105:                 Dim header As String
 106:                 For Each header In _headers
 107:                     Dim strValue As String = row.GetType().GetProperty(header).GetValue(row, Nothing).ToString()
 108:                     If Not _itemStyle Is Nothing Then
 109:                         _itemStyle.AddAttributesToRender(tw)
 110:                     End If
 111:                     tw.RenderBeginTag(HtmlTextWriterTag.Td)
 112:                     tw.Write(HttpUtility.HtmlEncode(strValue))
 113:                     tw.RenderEndTag()
 114:                 Next
 115:                 tw.RenderEndTag()
 116:             Next
 117:             tw.RenderEndTag() ' tbody
 118:  
 119:             tw.RenderEndTag() ' table
 120:             WriteFile(_fileName, "application/ms-excel", sw.ToString())
 121:         End Sub
 122:  
 123:  
 124:       
 125:  
 126:         Private Shared Sub WriteFile(ByVal fileName As String, ByVal contentType As String, ByVal content As String)
 127:             Dim context As HttpContext = HttpContext.Current
 128:             context.Response.Clear()
 129:             context.Response.AddHeader("content-disposition", "attachment;filename=" + fileName)
 130:             context.Response.Charset = ""
 131:             context.Response.Cache.SetCacheability(HttpCacheability.NoCache)
 132:             context.Response.ContentType = contentType
 133:             context.Response.Write(content)
 134:             context.Response.End()
 135:         End Sub
 136:     End Class
 137: End Namespace
 138:  

Listing 1 – ExcelResult.cs (C#)

   1: using System;
   2: using System.Web.Mvc;
   3: using System.Data.Linq;
   4: using System.Collections;
   5: using System.IO;
   6: using System.Web.UI.WebControls;
   7: using System.Linq;
   8: using System.Web;
   9: using System.Web.UI;
  10: using System.Drawing;
  11:  
  12:  
  13: namespace Tip2
  14: {
  15:     public class ExcelResult : ActionResult
  16:     {
  17:         private DataContext _dataContext;
  18:         private string _fileName;
  19:         private IQueryable _rows;
  20:         private string[] _headers = null;
  21:  
  22:         private TableStyle _tableStyle;
  23:         private TableItemStyle _headerStyle;
  24:         private TableItemStyle _itemStyle;
  25:  
  26:         public string FileName
  27:         {
  28:             get { return _fileName; }
  29:         }
  30:  
  31:         public IQueryable Rows
  32:         {
  33:             get { return _rows; }
  34:         }
  35:  
  36:  
  37:         public ExcelResult(DataContext dataContext, IQueryable rows, string fileName)
  38:             :this(dataContext, rows, fileName, null, null, null, null)
  39:         {
  40:         }
  41:  
  42:         public ExcelResult(DataContext dataContext, string fileName, IQueryable rows, string[] headers)
  43:             : this(dataContext, rows, fileName, headers, null, null, null)
  44:         {
  45:         }
  46:  
  47:         public ExcelResult(DataContext dataContext, IQueryable rows, string fileName, string[] headers, TableStyle tableStyle, TableItemStyle headerStyle, TableItemStyle itemStyle)
  48:         {
  49:             _dataContext = dataContext;
  50:             _rows = rows;
  51:             _fileName = fileName;
  52:             _headers = headers;
  53:             _tableStyle = tableStyle;
  54:             _headerStyle = headerStyle;
  55:             _itemStyle = itemStyle;
  56:  
  57:             // provide defaults
  58:             if (_tableStyle == null)
  59:             {
  60:                 _tableStyle = new TableStyle();
  61:                 _tableStyle.BorderStyle = BorderStyle.Solid;
  62:                 _tableStyle.BorderColor = Color.Black;
  63:                 _tableStyle.BorderWidth = Unit.Parse("2px");
  64:             }
  65:             if (_headerStyle == null)
  66:             {
  67:                 _headerStyle = new TableItemStyle();
  68:                 _headerStyle.BackColor = Color.LightGray;
  69:             }
  70:         }
  71:  
  72:         public override void ExecuteResult(ControllerContext context)
  73:         {
  74:             // Create HtmlTextWriter
  75:             StringWriter sw = new StringWriter();
  76:             HtmlTextWriter tw = new HtmlTextWriter(sw);
  77:  
  78:             // Build HTML Table from Items
  79:             if (_tableStyle != null)
  80:                 _tableStyle.AddAttributesToRender(tw);
  81:             tw.RenderBeginTag(HtmlTextWriterTag.Table);
  82:  
  83:             // Generate headers from table
  84:             if (_headers == null)
  85:             {
  86:                 _headers = _dataContext.Mapping.GetMetaType(_rows.ElementType).PersistentDataMembers.Select(m => m.Name).ToArray();
  87:             }
  88:  
  89:  
  90:             // Create Header Row
  91:             tw.RenderBeginTag(HtmlTextWriterTag.Thead);
  92:             foreach (String header in _headers)
  93:             {
  94:                 if (_headerStyle != null)
  95:                     _headerStyle.AddAttributesToRender(tw);
  96:                 tw.RenderBeginTag(HtmlTextWriterTag.Th);
  97:                 tw.Write(header);
  98:                 tw.RenderEndTag();
  99:             }
 100:             tw.RenderEndTag();
 101:  
 102:             
 103:  
 104:             // Create Data Rows
 105:             tw.RenderBeginTag(HtmlTextWriterTag.Tbody);
 106:             foreach (Object row in _rows)
 107:             {
 108:                 tw.RenderBeginTag(HtmlTextWriterTag.Tr);
 109:                 foreach (string header in _headers)
 110:                 {
 111:                     string strValue = row.GetType().GetProperty(header).GetValue(row, null).ToString();
 112:                     strValue = ReplaceSpecialCharacters(strValue);
 113:                     if (_itemStyle != null)
 114:                         _itemStyle.AddAttributesToRender(tw);
 115:                     tw.RenderBeginTag(HtmlTextWriterTag.Td);
 116:                     tw.Write( HttpUtility.HtmlEncode(strValue));
 117:                     tw.RenderEndTag();
 118:                 }
 119:                 tw.RenderEndTag();
 120:             }
 121:             tw.RenderEndTag(); // tbody
 122:  
 123:             tw.RenderEndTag(); // table
 124:             WriteFile(_fileName, "application/ms-excel", sw.ToString());            
 125:         }
 126:  
 127:  
 128:         private static string ReplaceSpecialCharacters(string value)
 129:         {
 130:             value = value.Replace("’", "'");
 131:             value = value.Replace("“", "\"");
 132:             value = value.Replace("”", "\"");
 133:             value = value.Replace("–", "-");
 134:             value = value.Replace("…", "...");
 135:             return value;
 136:         }
 137:  
 138:         private static void WriteFile(string fileName, string contentType, string content)
 139:         {
 140:             HttpContext context = HttpContext.Current;
 141:             context.Response.Clear();
 142:             context.Response.AddHeader("content-disposition", "attachment;filename=" + fileName);
 143:             context.Response.Charset = "";
 144:             context.Response.Cache.SetCacheability(HttpCacheability.NoCache);
 145:             context.Response.ContentType = contentType;
 146:             context.Response.Write(content);
 147:             context.Response.End();
 148:         }
 149:     }
 150: }

Every action result must inherit from the base ActionResult class. The ExcelResult class in Listing 1 does, in fact, inherit from the base ActionResult class. The base ActionResult class has one method that you must implement: the Execute() method. The Execute() method is called to generate the content created by the action result.

In Listing 1, the Execute() method is used to generate the Excel document from a Linq to SQL query. The Execute() method calls the WriteFile() method to write the finished Excel document to the browser with the correct MIME type.

Normally, you do not return an action result from a controller action directly. Instead, you take advantage of one of the methods of the Controller class:

· View()

· Redirect()

· RedirectToAction()

· RedirectToRoute()

· Json()

· Content()

For example, if you want to return a view from a controller action, you don’t return a ViewResult. Instead, you call the View() method. The View() method instantiates a new ViewResult and returns the new ViewResult to the browser.

The code in Listing 2 consists of three extension methods that are applied to the Controller class. These extension methods add a new method named Excel() to the Controller class. The Excel() method returns an ExcelResult().

Listing 2 –ExcelControllerExtensions.vb (VB)

   1: Imports System
   2: Imports System.Web.Mvc
   3: Imports System.Data.Linq
   4: Imports System.Collections
   5: Imports System.Web.UI.WebControls
   6: Imports System.Linq
   7: Imports System.Runtime.CompilerServices
   8:  
   9: Namespace Tip2
  10:     Public Module ExcelControllerExtensions
  11:  
  12:         <Extension()> _
  13:         Function Excel(ByVal controller As Controller, ByVal dataContext As DataContext, ByVal rows As IQueryable, ByVal fileName As String) As ActionResult
  14:             Return New ExcelResult(DataContext, rows, fileName, Nothing, Nothing, Nothing, Nothing)
  15:         End Function
  16:  
  17:         <Extension()> _
  18:         Function Excel(ByVal controller As Controller, ByVal dataContext As DataContext, ByVal rows As IQueryable, ByVal fileName As String, ByVal headers As String()) As ActionResult
  19:             Return New ExcelResult(dataContext, rows, fileName, headers, Nothing, Nothing, Nothing)
  20:         End Function
  21:  
  22:         <Extension()> _
  23:         Function Excel(ByVal controller As Controller, ByVal dataContext As DataContext, ByVal rows As IQueryable, ByVal fileName As String, ByVal headers As String(), ByVal tableStyle As TableStyle, ByVal headerStyle As TableItemStyle, ByVal itemStyle As TableItemStyle) As ActionResult
  24:             Return New ExcelResult(dataContext, rows, fileName, headers, tableStyle, headerStyle, itemStyle)
  25:         End Function
  26:  
  27:     End Module
  28: End Namespace
  29:  

Listing 2 –ExcelControllerExtensions.cs (C#)

   1: using System;
   2: using System.Web.Mvc;
   3: using System.Data.Linq;
   4: using System.Collections;
   5: using System.Web.UI.WebControls;
   6: using System.Linq;
   7:  
   8: namespace Tip2
   9: {
  10:     public static class ExcelControllerExtensions
  11:     {
  12:  
  13:         public static ActionResult Excel
  14:         (
  15:             this Controller controller,
  16:             DataContext dataContext,
  17:             IQueryable rows,
  18:             string fileName
  19:         )
  20:         {
  21:             return new ExcelResult(dataContext, rows, fileName, null, null, null, null);
  22:         }
  23:  
  24:         public static ActionResult Excel
  25:         (
  26:             this Controller controller,
  27:             DataContext dataContext,
  28:             IQueryable rows,
  29:             string fileName,
  30:             string[] headers
  31:         )
  32:         {
  33:             return new ExcelResult(dataContext, rows, fileName, headers, null, null, null);
  34:         }
  35:  
  36:         public static ActionResult Excel
  37:         (
  38:             this Controller controller, 
  39:             DataContext dataContext,
  40:             IQueryable rows, 
  41:             string fileName, 
  42:             string[] headers, 
  43:             TableStyle tableStyle, 
  44:             TableItemStyle headerStyle,
  45:             TableItemStyle itemStyle
  46:         )
  47:         {
  48:             return new ExcelResult(dataContext, rows, fileName, headers, tableStyle, headerStyle, itemStyle);
  49:         }
  50:  
  51:     }
  52: }

The controller in Listing 3 illustrates how you can use the Excel() extension method within a controller. This controller includes three methods named GenerateExcel1(), GenerateExcel2(), and GenerateExcel3(). All three of the controller action methods return an Excel document by generating the document from the Movies database table.

Listing 3 – HomeController.vb (VB)

   1: Imports System
   2: Imports System.Collections.Generic
   3: Imports System.Linq
   4: Imports System.Data.Linq
   5: Imports System.Data.Linq.Mapping
   6: Imports System.Web.UI.WebControls
   7: Imports System.Web
   8: Imports System.Web.Mvc
   9: Imports Tip2
  10:  
  11: Namespace Tip2.Controllers
  12:     Public Class HomeController
  13:         Inherits Controller
  14:  
  15:         Private db As New MovieDataContext()
  16:  
  17:         Public Function Index() As ActionResult
  18:             Return View()
  19:         End Function
  20:  
  21:         ''' <summary>
  22:         ''' Generates Excel document using headers grabbed from property names
  23:         ''' </summary>
  24:         Public Function GenerateExcel1() As ActionResult
  25:             Return Me.Excel(db, db.Movies, "data.xls")
  26:         End Function
  27:  
  28:         ''' <summary>
  29:         ''' Generates Excel document using supplied headers
  30:         ''' </summary>
  31:         Public Function GenerateExcel2() As ActionResult
  32:             Dim rows = From m In db.Movies Select New With {.Title = m.Title, .Director = m.Director}
  33:  
  34:             Return Me.Excel(db, rows, "data.xls", New String() {"Title", "Director"})
  35:         End Function
  36:  
  37:  
  38:         ''' <summary>
  39:         ''' Generates Excel document using supplied headers and using supplied styles
  40:         ''' </summary>
  41:         Public Function GenerateExcel3() As ActionResult
  42:             Dim rows = From m In db.Movies Select New With {.Title = m.Title, .Director = m.Director}
  43:  
  44:             Dim headerStyle As New TableItemStyle()
  45:             headerStyle.BackColor = System.Drawing.Color.Orange
  46:             Return Me.Excel(db, rows, "data.xls", New String() {"Title", "Director"}, Nothing, headerStyle, Nothing)
  47:         End Function
  48:  
  49:  
  50:     End Class
  51: End Namespace

Listing 3 – HomeController.cs (C#)

   1: using System;
   2: using System.Collections.Generic;
   3: using System.Linq;
   4: using System.Data.Linq;
   5: using System.Data.Linq.Mapping;
   6: using System.Web.UI.WebControls;
   7: using System.Web;
   8: using System.Web.Mvc;
   9: using Tip2.Models;
  10: using Tip2;
  11:  
  12: namespace Tip2.Controllers
  13: {
  14:     public class HomeController : Controller
  15:     {
  16:  
  17:         private MovieDataContext db = new MovieDataContext();
  18:  
  19:         public ActionResult Index()
  20:         {
  21:             return View();
  22:         }
  23:  
  24:         /// <summary>
  25:         /// Generates Excel document using headers grabbed from property names
  26:         /// </summary>
  27:         public ActionResult GenerateExcel1()
  28:         {
  29:             return this.Excel(db, db.Movies, "data.xls");            
  30:         }
  31:  
  32:         /// <summary>
  33:         /// Generates Excel document using supplied headers
  34:         /// </summary>
  35:         public ActionResult GenerateExcel2()
  36:         {
  37:             var rows = from m in db.Movies select new {Title=m.Title, Director=m.Director};
  38:             return this.Excel(db, rows, "data.xls", new[] { "Title", "Director" });
  39:         }
  40:  
  41:         /// <summary>
  42:         /// Generates Excel document using supplied headers and using supplied styles
  43:         /// </summary>
  44:         public ActionResult GenerateExcel3()
  45:         {
  46:             var rows = from m in db.Movies select new { Title = m.Title, Director = m.Director };
  47:             var headerStyle = new TableItemStyle();
  48:             headerStyle.BackColor = System.Drawing.Color.Orange;
  49:             return this.Excel(db, rows, "data.xls", new[] { "Title", "Director" }, null, headerStyle, null);
  50:         }
  51:     
  52:  
  53:     }
  54: }

Finally, the Index.aspx view in Listing 4 demonstrates how you can call the GenerateExcel() controller actions to generate the Excel documents. Notice the three links to the three different versions of GenerateExcel.

Listing 4 – Index.aspx

   1: <%@ Page Language="VB" AutoEventWireup="false" CodeBehind="Index.aspx.vb" Inherits="Tip2.Index" %>
   2: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
   3:  
   4: <html xmlns="http://www.w3.org/1999/xhtml" >
   5: <head id="Head1" runat="server">
   6:     <title>Index Page</title>
   7:     <style type="text/css">
   8:     
   9:     li
  10:     {
  11:         margin-bottom: 5px;
  12:     }
  13:     
  14:     </style>
  15: </head>
  16: <body>
  17:     <div>
  18:     
  19:     <h1>Generate Microsoft Excel Document</h1>
  20:     
  21:     
  22:     <ul>
  23:         <li>
  24:         <a href="/Home/GenerateExcel1">Generate</a> - Generates an Excel document by using the entity property names for column headings and the default
  25:         formatting.        
  26:         </li>
  27:         <li>
  28:         <a href="/Home/GenerateExcel2">Generate</a> - Generates an Excel document by using supplied header names and default formatting.        
  29:         </li>
  30:         <li>
  31:         <a href="/Home/GenerateExcel3">Generate</a> - Generates an Excel document by using supplied header names and supplied formatting.        
  32:         </li>
  33:  
  34:     </ul>
  35:     
  36:     
  37:     
  38:     
  39:     </div>
  40: </body>
  41: </html>

When you open the Index view, you see the page in Figure 1.

Figure 1 – The Index.aspx View

image

When you click one of the Generate Excel links, you get different Excel documents. For example, after you click on the first link, you get the Excel document in Figure 2.

Figure 2 – Data.xls

image

One disappointing note. When you click a link to generate the Excel document, you receive the warning in Figure 3. Unfortunately, there is no way around displaying this warning (to learn more about this warning, see http://blogs.msdn.com/vsofficedeveloper/pages/Excel-2007-Extension-Warning.aspx).

Figure 3 - Warning from Microsoft Internet Explorer

image

You can follow the same approach discussed in this tip to create other types of action results. For example, you can create image action results, Microsoft Word action results, or PDF action results.

Click Here to Download the Code

Published Monday, June 16, 2008 4:19 PM by swalther
Filed under: ,

Comments

# re: ASP.NET MVC Tip #2 - Create a custom Action Result that returns Microsoft Excel Documents

Thursday, June 19, 2008 11:19 AM by swalther

@Oskar, thanks for pointing out the feed issues. Feedburner doesn't like me because these posts tend to be long :) I think I have the issue resolved.

# re: ASP.NET MVC Tip #2 - Create a custom Action Result that returns Microsoft Excel Documents

Saturday, August 30, 2008 3:38 PM by alanstevens

Steve,

Thanks for this post. I ran into a similar use case today and you saved me lots of time!

++Alan