一、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 }
三、在页面上点击导出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 }
四、前端页面示意
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