C# 一般处理程序导出excel

2022/8/17 1:56:10

编程Tag： string excel c# dataTable 处理程序 system Using document

本文主要是介绍C# 一般处理程序导出excel，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

步骤：

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;
}
}
}
}

这篇关于C# 一般处理程序导出excel的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

C# 一般处理程序导出excel

相关编程文章