我有一个相对较大的MVC表单。
我需要能够生成包含来自该表单子集的数据的Excel文件。
棘手的部分是这不应影响表单的其余部分，因此我想通过AJAX完成。我遇到了一些在SO上看起来相关的问题，但我无法完全理解答案的意思。
这个似乎是最接近我要求的：asp-net-mvc-downloading-excel，但我不确定我理解了回答，而且它已经过去几年了。我还发现了另一篇文章（找不到它了），关于使用iframe处理文件下载，但我不知道如何在MVC中使其工作。
如果我进行完整的后台提交，我的Excel文件会返回正确，但我无法在MVC中使用AJAX使其正常工作。
ReportVM。public ActionResult PostReportPartial(ReportVM model){
// Validate the Model is correct and contains valid data
// Generate your report output based on the model parameters
// This can be an Excel, PDF, Word file - whatever you need.
// As an example lets assume we've generated an EPPlus ExcelPackage
ExcelPackage workbook = new ExcelPackage();
// Do something to populate your workbook
// Generate a new unique identifier against which the file can be stored
string handle = Guid.NewGuid().ToString();
using(MemoryStream memoryStream = new MemoryStream()){
workbook.SaveAs(memoryStream);
memoryStream.Position = 0;
TempData[handle] = memoryStream.ToArray();
}
// Note we are returning a filename as well as the handle
return new JsonResult() {
Data = new { FileGuid = handle, FileName = "TestReportOutput.xlsx" }
};
}
$ajax({
cache: false,
url: '/Report/PostReportPartial',
data: _form.serialize(),
success: function (data){
var response = JSON.parse(data);
window.location = '/Report/Download?fileGuid=' + response.FileGuid
+ '&filename=' + response.FileName;
}
})
[HttpGet]
public virtual ActionResult Download(string fileGuid, string fileName)
{
if(TempData[fileGuid] != null){
byte[] data = TempData[fileGuid] as byte[];
return File(data, "application/vnd.ms-excel", fileName);
}
else{
// Problem - Log the error, generate a blank file,
// redirect to another controller action - whatever fits with your application
return new EmptyResult();
}
}
如果需要，可以很容易地进行其他更改，将文件的MIME类型作为第三个参数传递，以便一个控制器操作可以正确地提供各种输出文件格式。
这样就可以避免在服务器上创建和存储任何物理文件，因此不需要任何清理例程，对最终用户来说也是无缝的。
请注意，使用TempData而不是Session的优点在于，一旦读取TempData，数据就会被清除，因此如果您有大量文件请求，则在内存使用方面更有效率。请参见TempData Best Practice。
原始答案
您无法通过AJAX调用直接返回要下载的文件，因此，另一种方法是使用AJAX调用将相关数据发布到服务器。然后，您可以使用服务器端代码创建Excel文件（我建议使用EPPlus或NPOI进行此操作，尽管听起来好像您已经完成了此部分）。
一旦服务器上创建了文件，请将文件路径（或仅文件名）作为返回值传递回您的AJAX调用，然后设置JavaScript window.location 到此URL，这将提示浏览器下载文件。从最终用户的角度来看，文件下载操作是无缝的，因为他们从未离开发出请求的页面。以下是一个简单的编写示例以实现此目的的AJAX调用：
$.ajax({
type: 'POST',
url: '/Reports/ExportMyData',
data: '{ "dataprop1": "test", "dataprop2" : "test2" }',
contentType: 'application/json; charset=utf-8',
dataType: 'json',
success: function (returnValue) {
window.location = '/Reports/Download?file=' + returnValue;
}
});
- url参数是您的代码将创建Excel文件的控制器/操作方法。
- data参数包含从表单中提取的json数据。
- returnValue将是您新创建的Excel文件的文件名。
- window.location命令重定向到实际返回文件以供下载的控制器/操作方法。
下载操作的示例控制器方法如下：
[HttpGet]
public virtual ActionResult Download(string file)
{
string fullPath = Path.Combine(Server.MapPath("~/MyFiles"), file);
return File(fullPath, "application/vnd.ms-excel", file);
}
我个人认为，你不需要在服务器上将Excel文件作为物理文件进行存储，而是应将其存储在（Session）缓存中。为缓存变量（存储Excel文件）使用一个唯一生成的名称 - 这将是您（最初）的ajax调用的返回值。这样您就不必处理文件访问问题、管理（删除）不需要时的文件等问题，并且将文件存储在缓存中，可以更快地检索它。
最近我在MVC中成功实现了这一点（尽管没有必要使用AJAX），而且没有创建物理文件，所以想分享我的代码：
超级简单的JavaScript函数（datatables.net按钮点击会触发此函数）：
function getWinnersExcel(drawingId) {
window.location = "/drawing/drawingwinnersexcel?drawingid=" + drawingId;
}
C# 控制器代码：
public FileResult DrawingWinnersExcel(int drawingId)
{
MemoryStream stream = new MemoryStream(); // cleaned up automatically by MVC
List<DrawingWinner> winnerList = DrawingDataAccess.GetWinners(drawingId); // simple entity framework-based data retrieval
ExportHelper.GetWinnersAsExcelMemoryStream(stream, winnerList, drawingId);
string suggestedFilename = string.Format("Drawing_{0}_Winners.xlsx", drawingId);
return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml", suggestedFilename);
}
在ExportHelper类中，我使用第三方工具（GemBox.Spreadsheet）来生成Excel文件，并且它提供了一个保存到流选项。话说回来，有许多方法可以创建可轻松写入内存流的Excel文件。
public static class ExportHelper
{
internal static void GetWinnersAsExcelMemoryStream(MemoryStream stream, List<DrawingWinner> winnerList, int drawingId)
{
ExcelFile ef = new ExcelFile();
// lots of excel worksheet building/formatting code here ...
ef.SaveXlsx(stream);
stream.Position = 0; // reset for future read
}
}
[HttpPost]
public JsonResult ExportExcel()
{
DataTable dt = DataService.GetData();
var fileName = "Excel_" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls";
//save the file to server temp folder
string fullPath = Path.Combine(Server.MapPath("~/temp"), fileName);
using (var exportData = new MemoryStream())
{
//I don't show the detail how to create the Excel, this is not the point of this article,
//I just use the NPOI for Excel handler
Utility.WriteDataTableToExcel(dt, ".xls", exportData);
FileStream file = new FileStream(fullPath, FileMode.Create, FileAccess.Write);
exportData.WriteTo(file);
file.Close();
}
var errorMessage = "you can return the errors in here!";
//return the Excel file name
return Json(new { fileName = fileName, errorMessage = "" });
}
[HttpGet]
[DeleteFileAttribute] //Action Filter, it will auto delete the file after download,
//I will explain it later
public ActionResult Download(string file)
{
//get the temp folder and file path in server
string fullPath = Path.Combine(Server.MapPath("~/temp"), file);
//return the file for download, this is an Excel
//so I set the file content type to "application/vnd.ms-excel"
return File(fullPath, "application/vnd.ms-excel", file);
}
如果你希望在下载完成后删除文件，请创建以下内容
public class DeleteFileAttribute : ActionFilterAttribute
{
public override void OnResultExecuted(ResultExecutedContext filterContext)
{
filterContext.HttpContext.Response.Flush();
//convert the current filter context to file and get the file path
string filePath = (filterContext.Result as FilePathResult).FileName;
//delete the file after download
System.IO.File.Delete(filePath);
}
}
最后，在您的MVC Razor视图中调用ajax
//I use blockUI for loading...
$.blockUI({ message: '<h3>Please wait a moment...</h3>' });
$.ajax({
type: "POST",
url: '@Url.Action("ExportExcel","YourController")', //call your controller and action
contentType: "application/json; charset=utf-8",
dataType: "json",
}).done(function (data) {
//console.log(data.result);
$.unblockUI();
//get the file name for download
if (data.fileName != "") {
//use window.location.href for redirect to download action for download the file
window.location.href = "@Url.RouteUrl(new
{ Controller = "YourController", Action = "Download"})/?file=" + data.fileName;
}
});
public ActionResult PostReportPartial(ReportVM model){
// Validate the Model is correct and contains valid data
// Generate your report output based on the model parameters
// This can be an Excel, PDF, Word file - whatever you need.
// As an example lets assume we've generated an EPPlus ExcelPackage
ExcelPackage workbook = new ExcelPackage();
// Do something to populate your workbook
// Generate a new unique identifier against which the file can be stored
string handle = Guid.NewGuid().ToString()
using(MemoryStream memoryStream = new MemoryStream()){
workbook.SaveAs(memoryStream);
memoryStream.Position = 0;
TempData[handle] = memoryStream.ToArray();
}
// Note we are returning a filename as well as the handle
return new JsonResult() {
Data = new { FileGuid = handle, FileName = "TestReportOutput.xlsx" }
};
}
[HttpGet]
public virtual ActionResult Download(string fileGuid, string fileName)
{
if(TempData[fileGuid] != null){
byte[] data = TempData[fileGuid] as byte[];
return File(data, "application/vnd.ms-excel", fileName);
}
else{
// Problem - Log the error, generate a blank file,
// redirect to another controller action - whatever fits with your application
return new EmptyResult();
}
}
使用 ClosedXML.Excel;
public ActionResult Downloadexcel()
{
var Emplist = JsonConvert.SerializeObject(dbcontext.Employees.ToList());
DataTable dt11 = (DataTable)JsonConvert.DeserializeObject(Emplist, (typeof(DataTable)));
dt11.TableName = "Emptbl";
FileContentResult robj;
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt11);
using (MemoryStream stream = new MemoryStream())
{
wb.SaveAs(stream);
var bytesdata = File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "myFileName.xlsx");
robj = bytesdata;
}
}
return Json(robj, JsonRequestBehavior.AllowGet);
}
$.ajax({
type: "GET",
url: "/Home/Downloadexcel/",
contentType: "application/json; charset=utf-8",
data: null,
success: function (Rdata) {
debugger;
var bytes = new Uint8Array(Rdata.FileContents);
var blob = new Blob([bytes], { type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" });
var link = document.createElement('a');
link.href = window.URL.createObjectURL(blob);
link.download = "myFileName.xlsx";
link.click();
},
error: function (err) {
}
});
以上是一段关于使用Ajax技术进行Excel文件下载的代码。其中，通过指定请求类型、请求地址和请求数据，向服务器发送请求，并在成功时执行相关操作，如获取文件内容并将其转化为Blob对象，创建超链接并设置下载属性等。如果出现错误，则执行相应的错误处理逻辑。 $.ajax({
global: false,
url: SitePath + "/User/ExportTeamMembersInExcel",
"data": { 'UserName': UserName, 'RoleId': RoleId, UserIds: AppraseeId },
"type": "POST",
"dataType": "JSON",
"success": function (result) {
var bytes = new Uint8Array(result.FileContents);
var blob = new Blob([bytes], { type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" });
var link = document.createElement('a');
link.href = window.URL.createObjectURL(blob);
link.download = "myFileName.xlsx";
link.click();
},
"error": function () {
alert("error");
}
})
[HttpPost]
public JsonResult ExportTeamMembersInExcel(string UserName, long? RoleId, string[] UserIds)
{
MemoryStream stream = new MemoryStream();
FileContentResult robj;
DataTable data = objuserservice.ExportTeamToExcel(UserName, RoleId, UserIds);
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(data, "TeamMembers");
using (stream)
{
wb.SaveAs(stream);
}
}
robj = File(stream.ToArray(), System.Net.Mime.MediaTypeNames.Application.Octet, "TeamMembers.xlsx");
return Json(robj, JsonRequestBehavior.AllowGet);
}
对于我来说，被接受的答案并没有完全起作用，因为我从ajax调用中得到了一个502 Bad Gateway的结果，尽管从控制器返回的一切似乎都很好。
也许我是在使用TempData时达到了限制——不确定，但我发现如果我使用IMemoryCache代替TempData，它就可以正常工作，所以这里是我对被接受答案中代码的改编版本：
public ActionResult PostReportPartial(ReportVM model){
// Validate the Model is correct and contains valid data
// Generate your report output based on the model parameters
// This can be an Excel, PDF, Word file - whatever you need.
// As an example lets assume we've generated an EPPlus ExcelPackage
ExcelPackage workbook = new ExcelPackage();
// Do something to populate your workbook
// Generate a new unique identifier against which the file can be stored
string handle = Guid.NewGuid().ToString();
using(MemoryStream memoryStream = new MemoryStream()){
workbook.SaveAs(memoryStream);
memoryStream.Position = 0;
//TempData[handle] = memoryStream.ToArray();
//This is an equivalent to tempdata, but requires manual cleanup
_cache.Set(handle, memoryStream.ToArray(),
new MemoryCacheEntryOptions().SetSlidingExpiration(TimeSpan.FromMinutes(10)));
//(I'd recommend you revise the expiration specifics to suit your application)
}
// Note we are returning a filename as well as the handle
return new JsonResult() {
Data = new { FileGuid = handle, FileName = "TestReportOutput.xlsx" }
};
}
$ajax({
cache: false,
url: '/Report/PostReportPartial',
data: _form.serialize(),
success: function (data){
var response = JSON.parse(data);
window.location = '/Report/Download?fileGuid=' + response.FileGuid
+ '&filename=' + response.FileName;
}
})
[HttpGet]
public virtual ActionResult Download(string fileGuid, string fileName)
{
if (_cache.Get<byte[]>(fileGuid) != null)
{
byte[] data = _cache.Get<byte[]>(fileGuid);
_cache.Remove(fileGuid); //cleanup here as we don't need it in cache anymore
return File(data, "application/vnd.ms-excel", fileName);
}
else
{
// Something has gone wrong...
return View("Error"); // or whatever/wherever you want to return the user
}
}
现在有一些额外的代码来设置MemoryCache...
为了使用在控制器构造函数中注入的 "_cache"，可以这样做：
using Microsoft.Extensions.Caching.Memory;
namespace MySolution.Project.Controllers
{
public class MyController : Controller
{
private readonly IMemoryCache _cache;
public LogController(IMemoryCache cache)
{
_cache = cache;
}
//rest of controller code here
}
}
请确保在Startup.cs的ConfigureServices方法中添加以下内容：
services.AddDistributedMemoryCache();
这对我有效。请确保从您的控制器操作中返回一个文件，其contentType为“application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”，文件名例如为“List.xlsx”，该文件名应与AJAX success调用中的相同。我已经使用了ClosedXML NuGet软件包来生成Excel文件。
$.ajax({
url: "Home/Export",
type: 'GET',
contentType: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
xhrFields: { responseType: 'blob' },
success: function (data) {
var a = document.createElement('a');
var url = window.URL.createObjectURL(data);
a.href = url;
a.download = 'List.xlsx';
a.click();
window.URL.revokeObjectURL(url);
}
});