c# 利用ajax导出excel，封装NPOI

╰+攻爆jí腚メ 2022-05-19 04:24 201阅读 0赞

**步骤：**

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzE2OTk4OTQ1_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzE2OTk4OTQ1_size_16_color_FFFFFF_t_70]: /images/20220519/69e3dc5b0a444f87989ded909db3140d.png