Asp.Net MVC中点击按钮导出Excel

一、Excel导出帮助类，要安装包NPOI

 1 using NPOI.HSSF.UserModel;
 2 using NPOI.SS.UserModel;
 3 using System;
 4 using System.Collections.Generic;
 5 using System.Data;
 6 using System.IO;
 7 
 8 namespace JMB.Common.LogicTools
 9 {
10     /// <summary>
11     /// Excel导入导出组件
12     /// </summary>
13     public static class ExportHelper
14     {
15         /// <summary>
16         /// 导出数据到Excel
17         /// </summary>
18         /// <param name="SourceTable"></param>
19         /// <returns></returns>
20         public static Stream RenderDataTableToExcel(DataTable SourceTable)
21         {
22             HSSFWorkbook workbook = new HSSFWorkbook();
23             MemoryStream ms = new MemoryStream();
24             HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
25             HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
26 
27             // handling header. 
28             foreach (DataColumn column in SourceTable.Columns)
29                 headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
30 
31             // handling value. 
32             int rowIndex = 1;
33 
34             foreach (DataRow row in SourceTable.Rows)
35             {
36                 HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
37 
38                 foreach (DataColumn column in SourceTable.Columns)
39                 {
40                     dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
41                 }
42 
43                 rowIndex++;
44             }
45 
46             workbook.Write(ms);
47             ms.Flush();
48             ms.Position = 0;
49 
50             sheet = null;
51             headerRow = null;
52             workbook = null;
53 
54             return ms;
55         }
56     }
57 }

二、写一个把List集合转成DataTable的帮助类

 1 public class User
 2     {
 3         /// <summary>
 4         /// 用户名
 5         /// </summary>
 6         public string UserName { get; set; }
 7         /// <summary>
 8         /// 邮箱
 9         /// </summary>
10         public string Email { get; set; }
11         /// <summary>
12         /// 密码
13         /// </summary>
14         public string PassWord { get; set; }
15     }
16     public static class ListToDataTable
17    {
18         public static DataTable GetDataTable(List<User> list)
19         {
20             DataTable dt = new DataTable();
21             dt.Columns.Add("列1名称", typeof(string));//用户名
22             dt.Columns.Add("列2名称", typeof(string));//密码
23             dt.Columns.Add("列3名称", typeof(string));//邮箱
24             //加载明细
25             dt.Clear();
26             #region 加载数据
27             foreach (var item in list)
28             {
29                 DataRow dr = dt.NewRow();
30                 dr[0] = item.UserName;
31                 dr[1] = item.PassWord;
32                 dr[2] = item.Email;
33                 dt.Rows.Add(dr);
34             }
35 
36             #endregion
37             return dt;
38         }
39       
40     }

View Code

三、在页面上点击导出Excel的按钮控制器类中的某个Action

 1   [HttpGet]
 2         public ActionResult List()
 3         {
 4             return View();
 5         }
 6 
 7         [HttpPost]
 8         public ActionResult List(FormCollection Form)
 9         {
10             string datatoexcel = Form["getexcel"].ToString();
11             if (datatoexcel == "a1")
12             {
13                 List<User> list = new List<User>();
14                 list.Add(new User { UserName = "张三", Email = "1s45@qq.com", PassWord = "123" });
15                 list.Add(new User { UserName = "李四", Email = "14s5@qq.com", PassWord = "587" });
16                 list.Add(new User { UserName = "王五", Email = "1s45@qq.com", PassWord = "12s" });
17                 list.Add(new User { UserName = "麻子", Email = "145@qq.com", PassWord = "ss" });
18                 DataTable dt = ListToDataTable.GetDataTable(list);
19 
20                 using (MemoryStream ms = ExportHelper.RenderDataTableToExcel(dt) as MemoryStream)
21                 {
22                     /*输出文件流，浏览器自动提示下载*/
23                     string filename = "你要取的文件名" + ".xls";
24                     Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + filename));
25                     Response.BinaryWrite(ms.ToArray());
26                 }
27             }
28             return View();
29         }

View Code

四、前端页面示意

 1 <html>
 2  <script src="~/Content/js/jquery-1.11.1.js"></script>
 3 <script type="text/javascript">
 4 //导出数据到excel
 5 function doGetExcel() {
 6 
 7     var getexcelinput = document.createElement("input");
 8     getexcelinput.type = "hidden";
 9     getexcelinput.name = "getexcel";
10     getexcelinput.value = "1";
11     var form = document.getElementById('pageform');
12     form.appendChild(getexcelinput);
13     form.submit();
14     form.removeChild(getexcelinput);
15 }
16 </script>
17 
18 <body>
19 <div class="session">
20     <div class="wrap-shadow">
21         <div class="session-content-padding">
22             <form id="pageform" method="post" action="/Action/List">
23                 <div class="info-bar">
24                     <div class="info-bar-btn-group">
25                     <a class="info-bar-btn" onclick="doGetExcel();">导出Excel文件</a>
26                     </div>
27                     <div class="info-bar-content">
28                     </div>
29                 </div>
30             
31             
32             </form>
33         </div>
34     </div>
35 </div>
36     
37 </body>
38 </html>                
39

View Code