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" }) 75 <input type="button" value="删除" onclick="delList()" class="btn btn-danger" /> 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>
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);
}
}
总结：以上是全部代码的实现，如有问题欢迎批评指正，谢谢！
