C# mvc导出excel

前端页面：

@{
Layout = null;
}
@model List<ClobDM_Model.sys_userinfo>
<form id="pagerForm" method="post" action="@Url.Action("Index")">
<input type="hidden" name="pageNum" value="@ViewData["pageIndex"]" />
<input type="hidden" name="numPerPage" value="@ViewData["pageSize"]" />
<input name="S_username" id="S_username" type="hidden" value="@ViewData["S_username"]" />
<input name="S_realName" id="S_realName" type="hidden" value="@ViewData["S_realName"]" />
</form>
<div class="pageHeader">
<form onsubmit="return navTabSearch(this);" action="@Url.Action("Index")" method="post">
<div class="searchBar">
<ul class="searchContent">
<li>
<label>
用户名：
</label>
<input name="S_username" type="text" value="@ViewData["S_username"]" />
</li>
<li>
<label>
姓名：
</label>
<input name="S_realName" type="text" value="@ViewData["S_realName"]" />
</li>
</ul>
<div class="subBar">
<ul>
<li>
<div class="buttonActive">
<div class="buttonContent">
<button type="submit">
检索
</button>
</div>
</div>
</li>
</ul>
</div>
</div>
</form>
</div>
<div class="pageContent">
<div class="panelBar">
<ul class="toolBar">
<li><a class="add" href="/sys_userinfo/AddUserInfo" mask="false" width="800" height="250" target="dialog" rel="SysUserInfo"><span>添加用户</span></a></li>
<li>
<a class="delete" href="/SysUserInfo/DeleteUserInfoByIds" rel="ids" target="selectedTodo" posttype="string"
title="确定要删除吗?"><span>批量删除</span></a>
</li>
<li>
<a class="delete" href="/SysUserInfo/RecoverUserInfoByIds" rel="ids" target="selectedTodo" posttype="string"
title="确定要启用吗?"><span>批量启用</span></a>
</li>

<li>
<a class="icon" onclick="ExportExcel()"><span>导出查询数据</span></a>
</li>

<li class="line">line</li>
</ul>
</div>
<table class="table" width="100%" layouth="138">
<thead>
<tr>
<th width="40">
<input type="checkbox" group="ids" class="checkboxCtrl">
</th>
<th>
编号
</th>
<th>
用户名
</th>
<th>
姓名
</th>
<th>
用户角色
</th>
<th>
状态
</th>
<th>
创建时间
</th>
<th width="120">
操作
</th>
</tr>
</thead>
<tbody>
@foreach (var item in Model)
{
<tr>
<td>
<input name="ids" type="checkbox" value="@item.ID" />
</td>
<td>@item.ID</td>
<td>@item.S_username</td>
<td>@item.S_realName</td>
<td>@item.S_roleId</td>
<td>@(item.S_state == 0 ? "正常" : "删除")</td>
<td>@item.CreateTime</td>
<td width="120">
<a title="查看" target="dialog" href="/sys_userinfo/UserInfoDetail?OID=@item.ID" mask="false" width="800" height="250" class="btnInfo">详情</a>
<a title="编辑" target="dialog" href="/sys_userinfo/AddUserInfo?OID=@item.ID" class="btnEdit" mask="false" width="800" height="250"> 编辑</a>
@if (item.S_state == 0)
{
<a title="确定要删除吗?" target="ajaxTodo" href="/sys_userinfo/DeleteUserInfo?otype=delete&OID=@item.ID" class="btnDel">
删除
</a>
}
else
{
<a title="确定要启用吗?" target="ajaxTodo" href="/sys_userinfo/DeleteUserInfo?otype=recover&OID=@item.ID" class="btnSelect">
启用
</a>
}
</td>

</tr>
}
</tbody>
</table>
<div class="panelBar">
<div class="pages">
<span>每页20条，共 @ViewData["recordCount"] 条</span>
</div>
<div class="pagination" targettype="navTab" totalcount="@ViewData["recordCount"]" numperpage="@ViewData["pageSize"]" pagenumshown="10"
currentpage="@ViewData["pageIndex"]">
</div>
</div>
</div>
<script type="text/javascript">
function ExportExcel()
{
var S_username = $("#S_username").val();
var S_realName = $("#S_realName").val();
window.location.href = "/sys_userinfo/ExportUserinfo/?S_username=" + S_username + "";
}

</script>

后端代码

#region 导出数据
/// <summary>
/// 导出数据
/// </summary>
/// <param name="OID"></param>
public ActionResult ExportUserinfo(sys_userinfo model)
{
string ReturnMsg = "";
Expression<Func<sys_userinfo, bool>> wherelambad = u => 1 == 1;//查询条件;
if (!string.IsNullOrWhiteSpace(model.S_username))
{
wherelambad = wherelambad = u => u.S_username.Contains(model.S_username);
}
if (!string.IsNullOrWhiteSpace(model.S_realName))
{
wherelambad = wherelambad.And(u => u.S_realName == model.S_realName);// 多个条件使用and连接
}
var Userdata = Bll.GetEntitysByStrwhere(wherelambad).ToList();

//var res = from m in Userdata
// orderby m.ID descending
// select m;

string[] FileData = null;
if (Userdata != null)
{
if (ExportOrderAllToExcel(Userdata, ref ReturnMsg))
{

FileData = ReturnMsg.Split(',');
if (FileData != null && FileData.Length > 0)
{
string FilePath = FileData[1] != null ? FileData[1].ToString() : "";
if (System.IO.File.Exists(Server.MapPath(FilePath)))
{
return File(Server.MapPath(FilePath), "application/octet-stream", DateTime.Now.ToString("yyyyMMddHHmmssfff") + "." + "xls");
}
else
{
return Content("该文件已丢失");
}
}
}
else
{
FileData = ReturnMsg.Split(',');
string FailMsg="";
if (FileData != null && FileData.Length > 0)
{
FailMsg = FileData[1].ToString();
}
return Content(FailMsg);

}
}
return Content("下载异常");
}
#endregion

#region 导出查询数据
/// <summary>
/// 导出查询数据
/// </summary>
/// <param name="strWhere"></param>
/// <param name="ReturnMsg"></param>
/// <returns></returns>
public bool ExportOrderAllToExcel(List<sys_userinfo> listUserinfo, ref string ReturnMsg)
{
try
{

IWorkbook wb = new HSSFWorkbook();
//创建表
ISheet sh = wb.CreateSheet("sheet1");

#region 创建表头

ICellStyle cellstyle = wb.CreateCellStyle();
cellstyle.VerticalAlignment = VerticalAlignment.CENTER;
cellstyle.Alignment = HorizontalAlignment.CENTER;
IRow row0 = sh.CreateRow(0);
IRow row1 = sh.CreateRow(1);
ICell cellR0 = row0.CreateCell(0);
cellR0.SetCellValue("ID");
var cellS0 = row0.GetCell(0);
cellS0.CellStyle = cellstyle;

row0.Height = 20 * 20;
ICell cellR1 = row0.CreateCell(1);
cellR1.SetCellValue("用户名");
var cellS1 = row0.GetCell(1);
cellS1.CellStyle = cellstyle;

//姓名
row0.Height = 20 * 20;
ICell cellR2 = row0.CreateCell(2);
cellR2.SetCellValue("姓名");
var cellS2 = row0.GetCell(2);
cellS2.CellStyle = cellstyle;

//状态
row0.Height = 20 * 20;
ICell cellR3 = row0.CreateCell(3);
cellR3.SetCellValue("状态");
var cellS3 = row0.GetCell(3);
cellS3.CellStyle = cellstyle;

//创建时间
sh.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 4, 4));
row0.Height = 20 * 20;
ICell cellR4 = row0.CreateCell(4);
cellR4.SetCellValue("创建时间");
var cellS4 = row0.GetCell(4);
cellS4.CellStyle = cellstyle;
#endregion

#region 数据写入
int m = 1;
IRow rowTest = null;
if (listUserinfo != null && listUserinfo.Count > 0)
{
for (int j = 0; j < listUserinfo.Count;j++ )
{
rowTest = sh.CreateRow(m);//创建新行

var model = listUserinfo[j];
#region 基础信息
ICell cell0 = rowTest.CreateCell(0);
cell0.SetCellValue(model.ID);
ICell cell1 = rowTest.CreateCell(1);
cell1.SetCellValue(model.S_username);
ICell cell2 = rowTest.CreateCell(2);
cell2.SetCellValue(model.S_realName);
ICell cell3 = rowTest.CreateCell(3);
cell3.SetCellValue(model.S_state);
ICell cell4 = rowTest.CreateCell(4);
cell4.SetCellValue(Convert.ToDateTime(model.CreateTime).ToString("yyyy-MM-dd"));
#endregion
m++;
}

//保存
string path = "/FileRoot/temp/";
string fileName = "用户数据报表" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
path += fileName;
System.IO.FileStream file = new System.IO.FileStream(Server.MapPath(path), System.IO.FileMode.Create);
wb.Write(file);
//关闭文件，释放对象
file.Close();
wb = null;
ReturnMsg = "1," + path;//返回文件路径
return true;
}
else
{
ReturnMsg = "2,没有查到要导出的数据!";
return false;
}

#endregion
}
catch (Exception ex)
{
string logMsg = " 报错信息:" + ex.Message.ToString();
logMsg += " 报错详情:" + ex.StackTrace.ToString();
// T9.Util.LogUtil.WriteLog(logMsg, "WebLog");
ReturnMsg = "2,导出数据失败!";
return false;
}
}
#endregion