步骤：
1.点击前台页面Excel下载按钮。
2.一般处理程序接收请求，
3.调用Npoi，将DataTable数据集（也可以是别的数据集list等）填充到Excel（提前创建好的模板），另存文件（原模板不会被写入数据）
4.拼接好另存文件地址，返回给前台
5.地址赋值给a标签，jquery模拟点击a标签事件，浏览器下载Excel
前台代码如下：使用a标签
<html>
<head>
<title></title>
<script src="../../Content/js/jquery/jquery-2.1.1.min.js" type="text/javascript"></script>
<script type="text/javascript">
function btn_excel() {
$.ajax({
url: "../../AjaxHandler/SIMManage/SIMInfoHandler.ashx?action=GetExcel",
async: false,
data: {
key:key
},
success: function (data) {
$("#downloadRul").attr("href", data);
$('#downloadRul>p').trigger("click");
}
});
}
</script>
</head>
<body>
<div>
<a id="NF-excel" onclick="btn_excel()">导出Excel</a>
<div style="display: none">
<a id="downloadRul" href="">
<p>
</p>
</a>
</div>
</div>
</body>
</html>
一般处理程序接收请求，利用NPOI进行导出Excel
Excel模板为提前定义好的模板。可在模板里面写死表头，写死样式。当然后台NPOI也可以设置样式
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.SessionState;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
namespace Web.AjaxHandler.SIMManage
{
/// <summary>
/// SIMInfoHandler 的摘要说明
/// </summary>
public class SIMInfoHandler : IHttpHandler
{
HttpRequest Request;
HttpResponse Response;
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
Request = context.Request;
Response = context.Response;
string action = Request.QueryString["action"];
switch (action)
{
case "GetExcel":
GetExcel();
break;
}
}
public void GetExcel()
{
string exportTemplatePath = "~/Document/TempletExcel/SIM档案管理.xls";
DataTable dt = new DataTable(); // DataTable 数据源
string download = GetPathByDataTableToExcel(dt,exportTemplatePath);
Response.Write(download);
}
/// <summary>
/// DataTable填充Excel
/// 存储excel
/// 返回excel下载路径
/// </summary>
/// <param name="sourceTable">数据源</param>
/// <param name="exportTemplatePath">模板路径</param>
/// <returns>下载路径</returns>
public string GetPathByDataTableToExcel(DataTable sourceTable, string exportTemplatePath)
{
/// ********************************需要引入NPOI组件*********************************************
HSSFWorkbook workbook = null;
MemoryStream ms = null;
ISheet sheet = null;
string templetFileName = HttpContext.Current.Server.MapPath(exportTemplatePath);
FileStream file = new FileStream(templetFileName, FileMode.Open, FileAccess.Read);
workbook = new HSSFWorkbook(file);
string httpurl = "";
try
{
ms = new MemoryStream();
sheet = workbook.GetSheetAt(0); //第一个Sheet页面
int rowIndex = 1; //行索引， 0为第一行，1为第二行
//遍历DataTable 填充所有数据
foreach (DataRow row in sourceTable.Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
++rowIndex;
}
workbook.Write(ms);
ms.Flush();
}
catch (Exception)
{
}
finally
{
ms.Close();
sheet = null;
workbook = null;
//~/Document/TemporaryDocuments/ 是项目下相对路径的文件存放地址，也可进行修改
string tempExcelName = Path.GetFileNameWithoutExtension(templetFileName) + DateTime.Now.ToString("yyyyMMddHHmmssfff") + Path.GetExtension(templetFileName);
string tempExcel = "~/Document/TemporaryDocuments/" + tempExcelName;
//文件另存
System.IO.File.WriteAllBytes(HttpContext.Current.Server.MapPath(tempExcel), ms.GetBuffer());
//获取项目绝对路径地址
string url = HttpContext.Current.Request.Url.AbsoluteUri.ToString().Split('/')[0] + "//" + HttpContext.Current.Request.Url.Authority.ToString();
var virtualPath = System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath;
string fileName = "";
if (virtualPath != "/")
{
//有子应用程序
fileName = virtualPath + "/Document/TemporaryDocuments/" + tempExcelName;
}else {
fileName = "/Document/TemporaryDocuments/" + tempExcelName;
}
//拼接文件相对地址
//string fileName = "/Document/TemporaryDocuments/" + tempExcelName;
//返回文件url地址
httpurl = url + fileName;
//清除历史文件，避免历史文件越来越多，可进行删除
DirectoryInfo dyInfo = new DirectoryInfo(HttpContext.Current.Server.MapPath("~/Document/TemporaryDocuments/"));
//获取文件夹下所有的文件
foreach (FileInfo feInfo in dyInfo.GetFiles())
{
//判断文件日期是否小于两天前，是则删除
if (feInfo.CreationTime < DateTime.Today.AddDays(-2))
feInfo.Delete();
}
}
//返回下载地址
return httpurl;
}
public bool IsReusable
{
get
{
return false;
}
}
}
}