ASP.NET MVC 异步Excel数据选择导出

以前习惯用一些框架来实现Excel文件数据导出，工作中也经常用到：比如extJs、easyUI、angularJs等，最近在做mvc程序的时候要实现该功能，相信这种功能在我们实际工作中是很常见，尤其是一些后台系统，导出报表之类的。因为本人在实际工作项目中开发一直都是用Ajax实现，所以这里也是一样，废话就不多说了，直接上代码：

1.后台新闻Index页面

  1 @model IEnumerable<MSCampus.MvcWebSite.Areas.Admin.Models.NewsViewModel>
  2 
  3 @{
  4     ViewBag.Title = "Index";
  5 }
  6 </script>
  7 <script>
  8     //全选 反选功能
  9     function selectAll(o) {
 10         var chklist = document.getElementsByName("chkList");
 11         for (var i = 0; i < chklist.length; i++) {
 12             chklist[i].checked = o.checked;
 13         }
 14     }
 15     //批量删除
 16     function delList() {
 17         var flag = false;
 18         var ids = '';
 19         var chklist = document.getElementsByName("chkList");
 20         for (var i = 0; i < chklist.length; i++) {
 21             if (chklist[i].checked) {
 22                 flag = true;
 23                 ids += chklist[i].value + ",";
 24             }
 25         }
 26         if (flag) {
 27             if (confirm("确定删除吗？")) {
 28                 $.ajax({
 29                     type: "post",
 30                     url: "/Admin/News/Delete",
 31                     data: { ids: ids.substr(0, ids.length - 1) },
 32                     success: function (data) {
 33                         if (data.result) {
 34                             alert(data.msg);
 35                             window.location.href = "/Admin/News/Index";
 36                         } else {
 37                             alert(data.msg);
 38                         }
 39                     }
 40                 });
 41             }
 42         } else {
 43             alert("至少选中一项进行操作");
 44             return false;
 45         }
 46     }
 47 
 48     //导出Excel
 49     function ExportData() {
 50         var ids = '';
 51         var chklist = document.getElementsByName("chkList");
 52         for (var i = 0; i < chklist.length; i++) {
 53             if (chklist[i].checked) {
 54                 flag = true;
 55                 ids += chklist[i].value + ",";
 56             }
 57         }
 58         $.ajax({
 59             type: 'POST',
 60             url: '/Admin/News/ExportExcelFile',
 61             data: { idlist: ids },
 62             //contentType: 'application/json; charset=utf-8',
 63             //dataType: 'json',
 64             success: function (data) {
 65                 if (data.fileName != "") {
 66                     window.location = '/Admin/News/Download?file=' + data.fileName;
 67                 }
 68             }
 69         });
 70     }
 71 </script>
 72 
 73 <p>
 74     @Html.ActionLink("发布新闻", "Create", null, new { @class = "btn btn-primary" }) &nbsp;
 75     <input type="button" value="删除" onclick="delList()" class="btn btn-danger" />&nbsp;    
 76     <input type="button" value="导出Excel" class="btn btn-warning" onclick="ExportData()" />  
 77 </p>
 78 <div class="main-content">
 79     <div class="panel panel-default">
 80         <a href="#page-stats" class="panel-heading" data-toggle="collapse">新闻列表 </a>
 81         <div id="page-stats" class="panel-collapse panel-body collapse in">
 82             <table class="table">
 83                 <tr>
 84                     <th><input type="checkbox" id="selectAll" onclick="selectAll(this)" /></th>
 85                     <th>
 86                         @Html.DisplayNameFor(model => model.Title)
 87                     </th>
 88                     <th>
 89                         @Html.DisplayNameFor(model => model.ViewTimes)
 90                     </th>
 91                     <th>
 92                         @Html.DisplayNameFor(model => model.CreateDate)
 93                     </th>
 94                     <th>操作</th>
 95                 </tr>
 96                 @foreach (var item in Model)
 97                 {
 98                     <tr>
 99                         <td>
100                             <input type="checkbox" name="chkList" value="@item.Id" />
101                         </td>
102                         <td title="@item.Title">
103                             @if (item.Title.Length > 50)
104                             {
105                                 @(item.Title.Substring(0, 50) + "...")
106                             }
107                             else
108                             {
109                                 @item.Title
110                             }
111                         </td>
112                         <td>
113                             @Html.DisplayFor(modelItem => item.ViewTimes)
114                         </td>
115                         <td>
116                             @Html.DisplayFor(modelItem => item.CreateDate)
117                         </td>
118                         <td>
119                             @Html.ActionLink("编辑", "Edit", new { id = item.Id }) |
120                             @Html.ActionLink("详细", "Details", new { id = item.Id })
121                         </td>
122                     </tr>
123                 }
124             </table>
125             @Html.Partial("Paging")
126         </div>
127     </div>
128 </div>

View Code

2.请求控制器代码实现

[HttpPost]
        public JsonResult ExportExcelFile(string idlist)
        {
            DataTable dt = _NewsBll.GetDataTableByIds(idlist.TrimEnd(','));

            var fileName = string.Format("{0}新闻信息.xls", DateTime.Now.ToString("yyyyMMddHHssmm"));
            //判断文件目录是否存在，不存在则创建
            if (!Directory.Exists(Server.MapPath("~/MyTempFiles")))
            {
                Directory.CreateDirectory(Server.MapPath("~/MyTempFiles"));
            }
            //将生成的文件保存到服务器临时文件夹中
            string fullPath = Path.Combine(Server.MapPath("~/MyTempFiles"), fileName);

            using (var exportData = NPOIExcelHelper.ExportToExcelStream(dt, "新闻列表"))
            {
                //创建一个文件
                FileStream file = new FileStream(fullPath, FileMode.Create, FileAccess.Write);
                exportData.WriteTo(file);
                file.Close();
            }
            //返回生成的文件名
            return Json(new { fileName = fileName });
        }

　　3.使用NPOI生成文件返回IO流

   public static MemoryStream ExportToExcelStream(DataTable dt, string sheetName)
        {
            //创建一个工作簿
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetName); //创建sheet

            //Excel表头
            NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); //创建行
            ICellStyle style = book.CreateCellStyle(); //创建单元格
            style.Alignment = HorizontalAlignment.Center; //对齐方式
            style.VerticalAlignment = VerticalAlignment.Center;  //单元格居中对齐

            //表头
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.CellStyle = style;
                cell.SetCellValue(dt.Columns[i].ColumnName);
            }

            #region 填充数据
            for (int i = 1; i <= dt.Rows.Count; i++)//遍历DataTable行
            {
                DataRow dataRow = dt.Rows[i - 1];
                row = sheet.CreateRow(i);//在工作表中添加一行

                for (int j = 0; j < dt.Columns.Count; j++)//遍历DataTable列
                {
                    ICell cell = row.CreateCell(j);//在行中添加一列
                    cell.SetCellValue(dataRow[j].ToString());//设置列的内容	 
                }
            }
            #endregion
            MemoryStream ms = new MemoryStream();
            book.Write(ms);
            return ms; //返回文件流
        }

4.后台请求处理完成数据生成的文件然后进行下载

/// <summary>
        /// 下载完后需要自动删除该文件 过滤器
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        [HttpGet]
        [DeleteFile]
        public ActionResult Download(string file)
        {
            string fullPath = Path.Combine(Server.MapPath("~/MyTempFiles"), file);
            return File(fullPath, "application/vnd.ms-excel", file);
        }

　　5.同时考虑到服务器资源磁盘空间的占用，这里写了一个过滤器，文件生成下载成功后删除。

/// <summary>
    /// 下载完文件后删除
    /// </summary>
    public class DeleteFileAttribute:ActionFilterAttribute
    {
        public override void OnResultExecuted(ResultExecutedContext filterContext)
        {
            filterContext.HttpContext.Response.Flush();
            //将当前filtercontext 转换成具体操作的文件并获取文件路径
            string filePath = (filterContext.Result as FilePathResult).FileName;
            //有文件路径就可以直接删除相应文件
            System.IO.File.Delete(filePath);
            base.OnResultExecuted(filterContext);
        }
    }

　　总结：以上是全部代码的实现，如有问题欢迎批评指正，谢谢！