.net mvc 用NPOI将DataTable生成EXCEL文件到服务器 然后下载该EXCEL文件

MVC的View文件(*.cshtml):

@{string yesterday = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");}
<input type="text" id="startDate" value="@yesterday" readonly="readonly" onclick="WdatePicker()"/>
<input type="button" id="ExportExcel" class="btnClass" onclick="ExportExcel()" value=" 导出Excel " />

<script>
function ExportExcel() {
     var startDate = $("#startDate").val();
        $.ajax({
            type: "get",
            url: "/Controllers/ExportExcel",//(1)前端 AJAX调用MVC的Action,
            data: {
                "startDate": startDate,       
            },
            success: function (file) {//file就是服务器上excel文件路径:http://...略.../excle.xlsx
                var tempwindow = window.open('_blank');
                tempwindow.location = file;//(5)AJAX根据该Excle路径下载Excle文件.
            },
        });
}
</script>

MVC的Controller文件:

        using NPOI.HSSF.Util;
        using NPOI.SS.UserModel;
        using NPOI.SS.Util;
        using NPOI.XSSF.UserModel;
        using System.IO;

        public ActionResult ExportExcel(string startDate)
        {
            DataTable dt = bll.GetData(startDate);//(2)执行Action查询数据获得DataTable,
            var file = DataTableExportExcel(dt, "Excel_", startDate);//生成excle文件
            return Content(file);//excle文件路径
        }
        /// <summary>
        /// DataTable导出Excel
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="excelName">excel文件名</param>
        /// <param name="startDate">sheet页名称(默认：Sheet1)</param>
        /// <returns></returns>
        public string DataTableExportExcel(DataTable dt, string excelName = "", string sheetName = "Sheet1")
        {
            //创建Excel文件的对象           
            XSSFWorkbook book = new XSSFWorkbook();//office 2007 Excel       
            //添加一个sheet1
            ISheet sheet1 = book.CreateSheet(sheetName);//sheet的名称   
            //创建sheet1的数据
            IRow row1 = sheet1.CreateRow(0);//第一行是表头
            //给sheet1添加第一行的头部标题
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                row1.CreateCell(i).SetCellValue(dt.Columns[i].ToString());//DataTable的表头
                sheet1.SetColumnWidth(i, 15 * 256);//设置每列宽度, 256 的整数倍???
            }
            //将数据逐步写入sheet1各个行 
            int rowIndex = 1;//行号
            foreach (DataRow row in dt.Rows)
            {
                IRow dataRow = sheet1.CreateRow(rowIndex);
                foreach (DataColumn column in dt.Columns)
                {
                    var rowCell = row[column].ToString();//当前行
                    dataRow.CreateCell(column.Ordinal).SetCellValue(rowCell);                                              
                }
                rowIndex++;
            }
            //(3)根据DataTable在服务器上生成Excel文件,
            string filePath = Server.MapPath("/") + "xlsx\\Temp\\" + excelName + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";//excel文件路径
            using (FileStream file = new FileStream(filePath, FileMode.Create))
            {
                book.Write(file);//生成excle物理文件
                file.Close();
            }
            string fPath = "/" + filePath.Replace(Server.MapPath("/"), "").Replace("\\", "/");//去掉物理路径,返回相对路径...
            return fPath; //(4)返回该Excel文件的路径给前端的AJAX,
        }   

说明:

① 前端 AJAX调用MVC的Action,

② 执行Action查询数据获得DataTable,

③ 根据DataTable在服务器上生成Excel文件,

④ 返回该Excel文件的路径给前端的AJAX,

⑤ AJAX根据该Excle路径下载Excle文件.