以前习惯用一些框架来实现Excel文件数据导出，工作中也经常用到：比如extJs、easyUI、angularJs等，最近在做mvc程序的时候要实现该功能，相信这种功能在我们实际工作中是很常见，尤其是一些后台系统，导出报表之类的。因为本人在实际工作项目中开发一直都是用Ajax实现，所以这里也是一样，废话就不多说了，直接上代码：
1.后台新闻Index页面
@model IEnumerable<MSCampus.MvcWebSite.Areas.Admin.Models.NewsViewModel>
@{
ViewBag.Title = "Index";
}
</script>
<script>
//全选 反选功能
function selectAll(o) {
var chklist = document.getElementsByName("chkList");
for (var i = ; i < chklist.length; i++) {
chklist[i].checked = o.checked;
}
}
//批量删除
function delList() {
var flag = false;
var ids = '';
var chklist = document.getElementsByName("chkList");
for (var i = ; i < chklist.length; i++) {
if (chklist[i].checked) {
flag = true;
ids += chklist[i].value + ",";
}
}
if (flag) {
if (confirm("确定删除吗？")) {
$.ajax({
type: "post",
url: "/Admin/News/Delete",
data: { ids: ids.substr(, ids.length - ) },
success: function (data) {
if (data.result) {
alert(data.msg);
window.location.href = "/Admin/News/Index";
} else {
alert(data.msg);
}
}
});
}
} else {
alert("至少选中一项进行操作");
return false;
}
}
//导出Excel
function ExportData() {
var ids = '';
var chklist = document.getElementsByName("chkList");
for (var i = ; i < chklist.length; i++) {
if (chklist[i].checked) {
flag = true;
ids += chklist[i].value + ",";
}
}
$.ajax({
type: 'POST',
url: '/Admin/News/ExportExcelFile',
data: { idlist: ids },
//contentType: 'application/json; charset=utf-8',
//dataType: 'json',
success: function (data) {
if (data.fileName != "") {
window.location = '/Admin/News/Download?file=' + data.fileName;
}
}
});
}
</script>
<p>
@Html.ActionLink("发布新闻", "Create", null, new { @class = "btn btn-primary" })
<input type="button" value="删除" onclick="delList()" class="btn btn-danger" />
<input type="button" value="导出Excel" class="btn btn-warning" onclick="ExportData()" />
</p>
<div class="main-content">
<div class="panel panel-default">
<a href="#page-stats" class="panel-heading" data-toggle="collapse">新闻列表 </a>
<div id="page-stats" class="panel-collapse panel-body collapse in">
<table class="table">
<tr>
<th><input type="checkbox" id="selectAll" onclick="selectAll(this)" /></th>
<th>
@Html.DisplayNameFor(model => model.Title)
</th>
<th>
@Html.DisplayNameFor(model => model.ViewTimes)
</th>
<th>
@Html.DisplayNameFor(model => model.CreateDate)
</th>
<th>操作</th>
</tr>
@foreach (var item in Model)
{
<tr>
<td>
<input type="checkbox" name="chkList" value="@item.Id" />
</td>
<td title="@item.Title">
@if (item.Title.Length > )
{
@(item.Title.Substring(, ) + "...")
}
else
{
@item.Title
}
</td>
<td>
@Html.DisplayFor(modelItem => item.ViewTimes)
</td>
<td>
@Html.DisplayFor(modelItem => item.CreateDate)
</td>
<td>
@Html.ActionLink("编辑", "Edit", new { id = item.Id }) |
@Html.ActionLink("详细", "Details", new { id = item.Id })
</td>
</tr>
}
</table>
@Html.Partial("Paging")
</div>
</div>
</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);
}
}
总结：以上是全部代码的实现，如有问题欢迎批评指正，谢谢！