创建返回Excel的自定义ActionResult
该篇文章是Stephen Walther博客中的文章，个人觉得不错就翻译了一下。但由于个人英语水平有限，还望各位海量。如果想看原文，请点击ASP.NET MVC Tip #2 - Create a custom Action Result that returns Microsoft Excel Documents查看原文
在这篇条目中，我会展示给大家创建一个能够返回Asp.net MVC的Controller Action的自定的Action Result。这个action result从一个Linq to SQL查询获取数据并创建一个Microsoft Excel的文件。
在一个MVC的应用程序中，一个Controller控制器的操作返回一个Action result。特别的这些操作返回值是来源于基础的ActionResult类就像下面这些：
· ViewResult
· EmptyResult
· RedirectResult
· RedirectToRouteResult
· JsonResult
· ContentResult
例如，你使用ViewResult向浏览器返回一个特别的View，使用一个ContentResult向浏览器返回文本内容。
但是如果你要向浏览器返回一些象image,PDF,或者Microsoft Excel的其他类型的内容该怎么办？在该种情况下，你只能创建一个自己的action result。这个条目中，我会给大家展示怎样创建一个返回Microsoft Excel文件的action result。
列表一是ExcelResult的代码：
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 }
151
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 }
151
每个action result必须继承基础的ActionResult类。列表一中的ExcelResult类事实上也是继承基础的ActionResult类。基础的ActionResult类的Execute()方法是你必须要重写的。Excute()方法是产生action result返回的内容。
在列表一中，Excute()方法被用来从Linq to SQL的数据产生一个Excel文件。Excute()方法调用WriteFile()方法来向浏览器写包含正确MIME类型的以完成的Excel文件。
通常的，你不会直接从控制器（controller）返回一个action result。你使用Controller类中的方法来代替：
· View()
· Redirect()
· RedirectToAction()
· RedirectToRoute()
· Json()
· Content()
例如，如果你想要从Controller的操作中返回一个view，你不用返回一个ViewResult，而是用View()方法代替。View()方法实例化一个ViewResult并想浏览器返回一个新的ViewResult。
应用于Controller由三个扩展方法组成的类的代码在类表二中。这些扩展方法向Controller类中添加名叫Excel()的方法。Excel()方法返回ExcelResult。
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 public static ActionResult Excel
13 (
14 this Controller controller,
15 DataContext dataContext,
16 IQueryable rows,
17 string fileName
18 )
19 {
20 return new ExcelResult(dataContext, rows, fileName, null, null, null, null);
21 }
22
23 public static ActionResult Excel
24 (
25 this Controller controller,
26 DataContext dataContext,
27 IQueryable rows,
28 string fileName,
29 string[] headers
30 )
31 {
32 return new ExcelResult(dataContext, rows, fileName, headers, null, null, null);
33 }
34
35 public static ActionResult Excel
36 (
37 this Controller controller,
38 DataContext dataContext,
39 IQueryable rows,
40 string fileName,
41 string[] headers,
42 TableStyle tableStyle,
43 TableItemStyle headerStyle,
44 TableItemStyle itemStyle
45 )
46 {
47 return new ExcelResult(dataContext, rows, fileName, headers, tableStyle, headerStyle, itemStyle);
48 }
49
50 }
51 }
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 public static ActionResult Excel
13 (
14 this Controller controller,
15 DataContext dataContext,
16 IQueryable rows,
17 string fileName
18 )
19 {
20 return new ExcelResult(dataContext, rows, fileName, null, null, null, null);
21 }
22
23 public static ActionResult Excel
24 (
25 this Controller controller,
26 DataContext dataContext,
27 IQueryable rows,
28 string fileName,
29 string[] headers
30 )
31 {
32 return new ExcelResult(dataContext, rows, fileName, headers, null, null, null);
33 }
34
35 public static ActionResult Excel
36 (
37 this Controller controller,
38 DataContext dataContext,
39 IQueryable rows,
40 string fileName,
41 string[] headers,
42 TableStyle tableStyle,
43 TableItemStyle headerStyle,
44 TableItemStyle itemStyle
45 )
46 {
47 return new ExcelResult(dataContext, rows, fileName, headers, tableStyle, headerStyle, itemStyle);
48 }
49
50 }
51 }
在列表三中的Controller代码向你阐明怎样在一个Controller使用Excel()扩展方法。这个控制器Controller包含三个名为GenerateExcel1()，GenerateExcel2()和GenerateExcel3()方法。这个三个控制器操作方法返回一个从Movies数据表获取数据的Excel文件。
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
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
最后，在Index.aspx View展示怎样调用GenerateExcel()控制器操作并创建Excel文件。注意三个连接指向三个不同的GenerateExcel版本。
1 <h1>Generate Microsoft Excel Document</h1>
2
3
4 <ul>
5 <li>
6 <a href="/Home/GenerateExcel1">Generate</a> - Generates an Excel document by using the entity property names for column headings and the default
7 formatting.
8 </li>
9 <li>
10 <a href="/Home/GenerateExcel2">Generate</a> - Generates an Excel document by using supplied header names and default formatting.
11 </li>
12 <li>
13 <a href="/Home/GenerateExcel3">Generate</a> - Generates an Excel document by using supplied header names and supplied formatting.
14 </li>
15
16 </ul>
2
3
4 <ul>
5 <li>
6 <a href="/Home/GenerateExcel1">Generate</a> - Generates an Excel document by using the entity property names for column headings and the default
7 formatting.
8 </li>
9 <li>
10 <a href="/Home/GenerateExcel2">Generate</a> - Generates an Excel document by using supplied header names and default formatting.
11 </li>
12 <li>
13 <a href="/Home/GenerateExcel3">Generate</a> - Generates an Excel document by using supplied header names and supplied formatting.
14 </li>
15
16 </ul>
