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