ASP.NET MVC Ajax下载文件(使用NPOI向现有的excel模板文件里面添加数据)

View

Html.DevExpress().Button(DevExpressButtonHelper.AddButton(ViewBag.Form, "Export", "Export", 70, false)).GetHtml();

Javascript

调用StoreLocalViewData controller的方法主要是用来把传入export的参数, 然后使用(ViewDataDictionary)LocalViewData 存储. 
var url = "@Url.Action("BillExportFile", ViewBag.Controller)"; 这个是在controller里面从数据获取需要导出的数据, 然后返回一个文件.

function MainExport(){
    
        var la_HAWB = GetSelectedValueByField(MainGridView,"HAWB",1);
        if(la_HAWB.length == 0 )
        {
            Messagebox("1012","","","","0")
            return false;
        }
        var ljs_Json = GenerateJson("HAWB", la_HAWB,"String");
        ljs_Json = "["+ ljs_Json.substring(0, ljs_Json.length - 1)+"]";
         
        $.ajax({
            url: '@Url.Action("StoreLocalViewData", ViewBag.Controller)',
            type: "POST",
            dataType: "html",
            //async: false,
            beforeSend:function () {
                lplLoadingPanel.Show();
            },
            data:
                {
                    "ajs_Value":ljs_Json
                },
            success: function (data) {
                var url = "@Url.Action("BillExportFile", ViewBag.Controller)";
                window.location = url;

                lplLoadingPanel.Hide();
            }
        });
    }

　　

Controller

        public void StoreLocalViewData(string ajs_Value)
        {
            LocalViewData["BillExportFile_Json"] = ajs_Value;
        }
        
        public FileResult BillExportFile()
        {
            DataTable ldt_DataTable = null;
            string ls_ExcelPath = "";
            using (var db = new agilitycpEntities())
            {

                ls_ExcelPath = db.sp_Sys_getSysPar("DOCUMENT", "CFS_RPT_FLIGHTPLAN").Select(c=>c.Value).FirstOrDefault();
                ls_ExcelPath = Server.MapPath(ls_ExcelPath);
            }

　　　　　　　　//从数据获取数据, 然后存储DataTable
              ldt_DataTable = DBUtils.ExecuteStoredProceReturnDataSet("sp_rpt_CFS_Report_FlightPlan", User.Identity.Name, (string)LocalViewData["BillExportFile_Json"], "Con_Shp", "getMainGrid").Tables[0];       　　　　　//将获取的数据使用NPOI组件生成excel文件. 
            MemoryStream stream = ExcelHelper.DataTableToExcel(ldt_DataTable, ls_ExcelPath,2,true);
            stream.Seek(0, SeekOrigin.Begin);
          //更改文件名后 return回去.
            string ls_filename = "Flight Plan" + DateTime.Now.ToString("yyyyMMdd") + "_" + User.Identity.Name + ".xls";
            return File(stream, "application/vnd.ms-excel", ls_filename);
        }

　　

ExcelHelper

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.Script.Serialization;
using NPOI.XSSF.UserModel;

namespace AgilityNorthAsiaPlatform.Code.Helpers
{
    public class ExcelHelper
    {

        public static MemoryStream ExportDataTableToExcel(DataTable adt_DataTable)
        {
            try
            {
                //文件流对象
                MemoryStream stream = new MemoryStream();
                if (adt_DataTable == null)
                {
                    return stream;
                }
                //打开Excel对象
                HSSFWorkbook workbook = new HSSFWorkbook();

                //Excel的Sheet对象
                var sheet = workbook.CreateSheet("sheet1");

                //set date format
                var cellStyleDate = workbook.CreateCellStyle();
                var format = workbook.CreateDataFormat();
                cellStyleDate.DataFormat = format.GetFormat("yyyyMMdd");

                //使用NPOI操作Excel表
                var row = sheet.CreateRow(0);
                int count = 0;
                for (int i = 0; i < adt_DataTable.Columns.Count; i++) //生成sheet第一行列名 
                {
                    var cell = row.CreateCell(count++);
                    cell.SetCellValue(adt_DataTable.Columns[i].Caption);
                }
                //将数据导入到excel表中
                for (int i = 0; i < adt_DataTable.Rows.Count; i++)
                {
                    var rows = sheet.CreateRow(i + 1);
                    count = 0;
                    for (int j = 0; j < adt_DataTable.Columns.Count; j++)
                    {
                        var cell = rows.CreateCell(count++);
                        Type type = adt_DataTable.Rows[i][j].GetType();
                        if (type == typeof(int) || type == typeof(Int16)
                            || type == typeof(Int32) || type == typeof(Int64))
                        {
                            cell.SetCellValue(Convert.ToInt32(adt_DataTable.Rows[i][j]));
                        }
                        else
                        {
                            if (type == typeof(float) || type == typeof(double) || type == typeof(Double))
                            {
                                cell.SetCellValue((Double)adt_DataTable.Rows[i][j]);
                            }
                            else
                            {
                                if (type == typeof(DateTime))
                                {
                                    cell.SetCellValue(((DateTime)adt_DataTable.Rows[i][j]).ToString("yyyy-MM-dd HH:mm"));
                                }
                                else
                                {
                                    if (type == typeof(bool) || type == typeof(Boolean))
                                    {
                                        cell.SetCellValue((bool)adt_DataTable.Rows[i][j]);
                                    }
                                    else
                                    {
                                        cell.SetCellValue(adt_DataTable.Rows[i][j].ToString());
                                    }
                                }
                            }
                        }
                    }
                }

                //保存excel文档
                sheet.ForceFormulaRecalculation = true;

                workbook.Write(stream);
                workbook = null;
                //workbook.Dispose();

                return stream;
            }
            catch (Exception ex)
            {
                return new MemoryStream();
            }
        }


        public static FileStream ModifyExcelFromDataTable(DataTable adt_DataTable)
        {
            //文件流对象
            FileStream stream = null;
            try
            {

                if (adt_DataTable == null)
                {
                    return stream;
                }

                string ls_FileName = "North Asia Platform-Flight Plan_Template.xlsx";
                string ls_ExcelPath = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/FileUploads/"), ls_FileName);
                XSSFWorkbook wk = null;

                using (FileStream fs = File.Open(ls_ExcelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
                    //把xls文件读入workbook变量里，之后就可以关闭了
                    wk = new XSSFWorkbook(fs);
                    fs.Close();
                }
                ISheet sheet = wk.GetSheetAt(0);
                IRow row = sheet.CreateRow(1);
                ICell cell = row.CreateCell(0);
                sheet.GetRow(2).GetCell(0).SetCellValue("AAAAAA");

                //保存excel文档
                sheet.ForceFormulaRecalculation = true;

                stream = File.Open(ls_ExcelPath, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite);
                wk.Write(stream);
                wk = null;
                //stream.Close();


                //打开Excel对象
                //HSSFWorkbook workbook = new HSSFWorkbook();
                return stream;
            }
            catch (Exception ex)
            {
                return stream;
            }
        }


        public static MemoryStream DataTableToExcel(DataTable adt_DataTable, string as_FilePath, int ai_HeaderRowCount, bool ab_FristColumnUID)
        {
            int i = 0;
            int j = 0;
            int count = 2;
            ISheet sheet = null;
            MemoryStream stream = new MemoryStream();
            if (adt_DataTable == null)
            {
                return stream;
            }
            if (ab_FristColumnUID)
                adt_DataTable.Columns.RemoveAt(0);


            FileStream fs = null;
            IWorkbook workbook = null;

            fs = new FileStream(as_FilePath, FileMode.Open, FileAccess.Read);
            if (as_FilePath.IndexOf(".xlsx") > 0) // 2007版本
                workbook = new XSSFWorkbook(fs);
            else if (as_FilePath.IndexOf(".xls") > 0) // 2003版本
                workbook = new HSSFWorkbook(fs);
            try
            {
                if (workbook != null)
                {
                    sheet = workbook.GetSheetAt(0);
                }
                else
                {
                    return stream;
                }
                IRow row = null;
                if (ai_HeaderRowCount == 0)
                    row = sheet.CreateRow(1);
                row = sheet.CreateRow(ai_HeaderRowCount);

                for (i = 0; i < adt_DataTable.Rows.Count; ++i)
                {

                    for (j = 0; j < adt_DataTable.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(adt_DataTable.Rows[i][j].ToString());
                    }
                    ++count;
                    row = sheet.CreateRow(count);
                }
                workbook.Write(stream); //写入到excel
                workbook = null;
                return stream;
            }
            catch (Exception ex)
            {
                return new MemoryStream();
            }
        }


    }
}