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
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
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
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.
Just FYI: Not sure if this is a weblogs.asp.net issue or a feedburner issue – but the feedburner feed for this blog is on a 1-2 day delay…
@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.
Steve,
Thanks for this post. I ran into a similar use case today and you saved me lots of time!
++Alan
I’m getting an error
“Nullable object must have a value”
in
ExecuteResult()
– foreach (Object row in _rows)
For some reason it doesnt like _rows. I’m calling the actionresult thus:
var registrations = from reg in DataContext.Registrations
where reg.Date.Date >= startDate.Value.Date && reg.Date.Date < = endDate.Value.Date
orderby reg.Attendee.Surname, reg.Attendee.FirstName
select new { Surname=reg.Attendee.Surname, Firstname=reg.Attendee.FirstName };
return this.Excel(DataContext, registrations, “Registrations.xls”, new[] { “Surname”, “Firstname”});
Amd I doing something silly/wrong?
Thanks
Very well.Thank you,
There is a fix for the error prompt:
http://support.microsoft.com/kb/948615
they had used a function, say
window.extensions.mozilla.proto
they would not have had this problem.
how to fix this error, it does not accept null values? here is the error:
Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.
Source Error:
Line 109: {
Line 110: string strValue = null;
Line 111: strValue = row.GetType().GetProperty(header).GetValue(row, null).ToString();
Line 112: strValue = ReplaceSpecialCharacters(strValue);
Line 113: if (_itemStyle != null)
Source File: C:HrManagerHrManagerCustomActionResultsExcelResult.cs Line: 111
The sample is running fine… But i am not able to get the download options(popup window).can anyone guide me
h3424 It looks like DataContextExtensions.cs line 45 of the Save method should pass the primaryKeyName through to Update.
selam hi This sounds fascinating sıcak sohbet I’m going to read that tracing articlekısa aşk şiirleri when I have a moment.
Wow. erotik film izle is
şifalı bitkiler zayıflama de
çet sohbet fer
netlog ger
müzik dinle err
şarkı dinle
cüneyt arkın filmleri kk
isyan sözleri fer
hikayeler er
islami çet ff
adet sancısına ne iyi gelir hh
escort bayanlar der
bedava chat dd
chat odaları der
liseli kızlar derf
kızlarla sohbet fder
kızlarla chat
sohbet errFGVBNC
As the users of HD Camcorders like Sony, Canon, Panasonic, this HD Video Converter is necessary to help us convert hd Video easily and quickly. The Converter for HD provides several practical editing functions to help you achieve ideal output effect. Trim function is to cut videos into clips which you can just convert and transfer to your player. Crop function helps you remove black bars around the movie. You could use Effect function to adjust video brightness, contrast, saturation and more parameters. More powerful and considerate functions are waiting for you to explore.
TS Converter l TOD Converter l TRP Converter l H.264 Converter ..