C# 通过 AJAX MVC 下载 Excel 文件

Download Excel file via AJAX MVC

提问by Valuk

I have a large(ish) form in MVC.

我在 MVC 中有一个大（ish）表单。

I need to be able to generate an excel file containing data from a subset of that form.

我需要能够生成一个包含来自该表单子集的数据的 excel 文件。

The tricky bit is that this shouldn't affect the rest of the form and so I want to do it via AJAX. I've come across a few questions on SO that seem to be related, but I can't quite work out what the answers mean.

棘手的一点是这不应该影响表单的其余部分，所以我想通过 AJAX 来完成。我在 SO 上遇到了一些似乎相关的问题，但我无法弄清楚答案的含义。

This one seems the closest to what I'm after: asp-net-mvc-downloading-excel- but I'm not sure I understand the response, and it is a couple years old now. I also came across another article (can't find it anymore) about using an iframe to handle the file download, but I'm not sure how to get this working with MVC.

这个似乎最接近我所追求的：asp-net-mvc-downloading-excel- 但我不确定我是否理解响应，它现在已经有几年了。我还遇到了另一篇关于使用 iframe 处理文件下载的文章（再也找不到了），但我不确定如何使用 MVC 来实现这一点。

My excel file returns fine if I'm doing a full post back but I can't get it working with AJAX in mvc.

如果我正在做一个完整的回发，我的 excel 文件返回正常，但我无法在 mvc 中使用 AJAX。

采纳答案by connectedsoftware

You can't directly return a file for download via an AJAX call so, an alternative approach is to to use an AJAX call to post the related data to your server. You can then use server side code to create the Excel File (I would recommend using EPPlus or NPOI for this although it sounds as if you have this part working).

您不能通过 AJAX 调用直接返回要下载的文件，因此另一种方法是使用 AJAX 调用将相关数据发布到您的服务器。然后，您可以使用服务器端代码来创建 Excel 文件（我建议为此使用 EPPlus 或 NPOI，尽管这听起来好像您有这部分工作）。

UPDATE September 2016

2016 年 9 月更新

My original answer (below) was over 3 years old, so I thought I would update as I no longer create files on the server when downloading files via AJAX however, I have left the original answer as it may be of some use still depending on your specific requirements.

我的原始答案（如下）已经超过 3 年了，所以我想我会更新，因为我在通过 AJAX 下载文件时不再在服务器上创建文件但是，我保留了原始答案，因为它可能仍然有用，取决于您的具体要求。

A common scenario in my MVC applications is reporting via a web page that has some user configured report parameters (Date Ranges, Filters etc.). When the user has specified the parameters they post them to the server, the report is generated (say for example an Excel file as output) and then I store the resulting file as a byte array in the TempDatabucket with a unique reference. This reference is passed back as a Json Result to my AJAX function that subsequently redirects to separate controller action to extract the data from TempDataand download to the end users browser.

我的 MVC 应用程序中的一个常见场景是通过具有一些用户配置的报告参数（日期范围、过滤器等）的网页进行报告。当用户指定了他们将它们发布到服务器的参数时，将生成报告（例如作为输出的 Excel 文件），然后我将生成的文件作为字节数组存储在存储TempData桶中，并具有唯一引用。此引用作为 Json 结果传递回我的 AJAX 函数，该函数随后重定向到单独的控制器操作以从TempData最终用户浏览器中提取数据并下载到最终用户浏览器。

To give this more detail, assuming you have a MVC View that has a form bound to a Model class, lets call the Model ReportVM.

为了提供更多细节，假设您有一个绑定到 Model 类的表单的 MVC 视图，让我们调用 Model ReportVM。

First, a controller action is required to receive the posted model, an example would be:

首先，需要一个控制器动作来接收发布的模型，一个例子是：

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

}

The AJAX call that posts my MVC form to the above controller and receives the response looks like this:

将我的 MVC 表单发布到上述控制器并接收响应的 AJAX 调用如下所示：

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

The controller action to handle the downloading of the file:

处理文件下载的控制器动作：

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

One other change that could easily be accommodated if required is to pass the MIME Type of the file as a third parameter so that the one Controller action could correctly serve a variety of output file formats.

如果需要，可以轻松适应的另一个更改是将文件的 MIME 类型作为第三个参数传递，以便一个 Controller 操作可以正确地提供各种输出文件格式。

This removes any need for any physical files to created and stored on the server, so no housekeeping routines required and once again this is seamless to the end user.

这消除了在服务器上创建和存储任何物理文件的任何需要，因此不需要内务例程，这再次对最终用户来说是无缝的。

Note, the advantage of using TempDatarather than Sessionis that once TempDatais read the data is cleared so it will be more efficient in terms of memory usage if you have a high volume of file requests. See TempData Best Practice.

请注意，使用TempData而不是的优点Session是一旦TempData读取数据就会被清除，因此如果您有大量文件请求，则在内存使用方面会更有效。请参阅TempData 最佳实践。

ORIGINAL Answer

原始答案

You can't directly return a file for download via an AJAX call so, an alternative approach is to to use an AJAX call to post the related data to your server. You can then use server side code to create the Excel File (I would recommend using EPPlus or NPOI for this although it sounds as if you have this part working).

您不能通过 AJAX 调用直接返回要下载的文件，因此另一种方法是使用 AJAX 调用将相关数据发布到您的服务器。然后，您可以使用服务器端代码来创建 Excel 文件（我建议为此使用 EPPlus 或 NPOI，尽管这听起来好像您有这部分工作）。

Once the file has been created on the server pass back the path to the file (or just the filename) as the return value to your AJAX call and then set the JavaScript window.locationto this URL which will prompt the browser to download the file.

在服务器上创建文件后，将文件路径（或仅文件名）作为返回值传递给 AJAX 调用，然后将 JavaScriptwindow.location设置为该 URL，这将提示浏览器下载文件。

From the end users perspective, the file download operation is seamless as they never leave the page on which the request originates.

从最终用户的角度来看，文件下载操作是无缝的，因为他们永远不会离开请求发起的页面。

Below is a simple contrived example of an ajax call to achieve this:

以下是实现此目的的 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;
    }
});

• urlparameter is the Controller/Action method where your code will create the Excel file.
• dataparameter contains the json data that would be extracted from the form.
• returnValuewould be the file name of your newly created Excel file.
• The window.locationcommand redirects to the Controller/Action method that actually returns your file for download.

• url参数是控制器/操作方法，您的代码将在其中创建 Excel 文件。
• data参数包含将从表单中提取的 json 数据。
• returnValue将是您新创建的 Excel 文件的文件名。
• 该window.location的命令重定向到实际返回供下载的文件控制器/操作方法。

A sample controller method for the Download action would be:

下载操作的示例控制器方法是：

[HttpGet]
public virtual ActionResult Download(string file)
{   
  string fullPath = Path.Combine(Server.MapPath("~/MyFiles"), file);
  return File(fullPath, "application/vnd.ms-excel", file);
}

回答by Luchian

My 2 cents - you don't need to store the excel as a physical file on the server - instead, store it in the (Session) Cache. Use a uniquely generated name for your Cache variable (that stores that excel file) - this will be the return of your (initial) ajax call. This way you don't have to deal with file access issues, managing (deleting) the files when not needed, etc. and, having the file in the Cache, is faster to retrieve it.

我的 2 美分 - 您不需要将 excel 作为物理文件存储在服务器上 - 而是将其存储在（会话）缓存中。为您的 Cache 变量（存储该 excel 文件）使用唯一生成的名称 - 这将是您的（初始）ajax 调用的返回。这样您就不必处理文件访问问题、在不需要时管理（删除）文件等，并且将文件放在缓存中，可以更快地检索它。

回答by Andy S

I was recently able to accomplish this in MVC (although there was no need to use AJAX) without creating a physical file and thought I'd share my code:

我最近能够在 MVC 中完成此操作（尽管不需要使用 AJAX）而无需创建物理文件，并认为我会共享我的代码：

Super simple JavaScript function (datatables.net button click triggers this):

超级简单的 JavaScript 函数（datatables.net 按钮点击触发）：

function getWinnersExcel(drawingId) {
    window.location = "/drawing/drawingwinnersexcel?drawingid=" + drawingId;
}

C# Controller code:

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

In the ExportHelper class I do use a 3rd party tool (GemBox.Spreadsheet) to generate the Excel file and it has a Save to Stream option. That being said, there are a number of ways to create Excel files that can easily be written to a memory stream.

在 ExportHelper 类中，我确实使用了第 3 方工具 ( GemBox.Spreadsheet) 来生成 Excel 文件，并且它有一个 Save to Stream 选项。话虽如此，有多种方法可以创建可以轻松写入内存流的 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

     }
}

In IE, Chrome, and Firefox, the browser prompts to download the file and no actual navigation occurs.

在 IE、Chrome 和 Firefox 中，浏览器会提示下载文件，但不会发生实际导航。

回答by Machinegon

This thread helped me create my own solution that I will share here. I was using a GET ajax request at first without issues but it got to a point where the request URL length was exceeded so I had to swith to a POST.

这个线程帮助我创建了自己的解决方案，我将在这里分享。起初我使用 GET ajax 请求没有问题，但它达到了超出请求 URL 长度的地步，所以我不得不切换到 POST。

The javascript uses JQuery file download plugin and consists of 2 succeeding calls. One POST (To send params) and one GET to retreive the file.

javascript 使用 JQuery 文件下载插件，由 2 个连续调用组成。一个 POST（发送参数）和一个 GET 来检索文件。

 function download(result) {
        $.fileDownload(uri + "?guid=" + result,
        {
            successCallback: onSuccess.bind(this),
            failCallback: onFail.bind(this)
        });
    }

    var uri = BASE_EXPORT_METADATA_URL;
    var data = createExportationData.call(this);

    $.ajax({
        url: uri,
        type: 'POST',
        contentType: 'application/json',
        data: JSON.stringify(data),
        success: download.bind(this),
        fail: onFail.bind(this)
    });

Server side

服务器端

    [HttpPost]
    public string MassExportDocuments(MassExportDocumentsInput input)
    {
        // Save query for file download use
        var guid = Guid.NewGuid();
        HttpContext.Current.Cache.Insert(guid.ToString(), input, null, DateTime.Now.AddMinutes(5), Cache.NoSlidingExpiration);
        return guid.ToString();
    }

   [HttpGet]
    public async Task<HttpResponseMessage> MassExportDocuments([FromUri] Guid guid)
    {
        //Get params from cache, generate and return
        var model = (MassExportDocumentsInput)HttpContext.Current.Cache[guid.ToString()];
          ..... // Document generation

        // to determine when file is downloaded
        HttpContext.Current
                   .Response
                   .SetCookie(new HttpCookie("fileDownload", "true") { Path = "/" });

        return FileResult(memoryStream, "documents.zip", "application/zip");
    }

回答by Niclas

I used the solution posted by CSL but I would recommend you dont store the file data in Session during the whole session. By using TempData the file data is automatically removed after the next request (which is the GET request for the file). You could also manage removal of the file data in Session in download action.

我使用了 CSL 发布的解决方案，但我建议您不要在整个会话期间将文件数据存储在 Session 中。通过使用 TempData，文件数据会在下一个请求（即文件的 GET 请求）后自动删除。您还可以在下载操作中管理会话中文件数据的删除。

Session could consume much memory/space depending on SessionState storage and how many files are exported during the session and if you have many users.

会话可能会消耗大量内存/空间，具体取决于会话状态存储以及会话期间导出的文件数量以及您是否有很多用户。

I've updated the serer side code from CSL to use TempData instead.

我已经更新了 CSL 的 Serer 端代码以使用 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();
   }      

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

回答by Can OTUR

I am using Asp.Net WebForm and just I wanna to download a file from server side. There is a lot article but I cannot find just basic answer. Now, I tried a basic way and got it.

我正在使用 Asp.Net WebForm，只是想从服务器端下载一个文件。有很多文章，但我找不到基本的答案。现在，我尝试了一种基本的方法并得到了它。

That's my problem.

那是我的问题。

I have to create a lot of input button dynamically on runtime. And I want to add each button to download button with giving an unique fileNumber.

我必须在运行时动态创建很多输入按钮。我想将每个按钮添加到下载按钮并提供唯一的文件编号。

I create each button like this:

我像这样创建每个按钮：

fragment += "<div><input type=\"button\" value=\"Create Excel\" onclick=\"CreateExcelFile(" + fileNumber + ");\" /></div>";

Each button call this ajax method.

每个按钮调用这个ajax方法。

$.ajax({
    type: 'POST',
    url: 'index.aspx/CreateExcelFile',
    data: jsonData,
    contentType: 'application/json; charset=utf-8',
    dataType: 'json',
    success: function (returnValue) {
      window.location = '/Reports/Downloads/' + returnValue.d;
    }
});

Then I wrote a basic simple method.

然后我写了一个基本的简单方法。

[WebMethod]
public static string CreateExcelFile2(string fileNumber)
{
    string filePath = string.Format(@"Form_{0}.xlsx", fileNumber);
    return filePath;
}

I am generating this Form_1, Form_2, Form_3.... And I am going to delete this old files with another program. But if there is a way to just sending byte array to download file like using Response. I wanna to use it.

我正在生成这个 Form_1、Form_2、Form_3...。我将用另一个程序删除这个旧文件。但是，如果有一种方法可以像使用响应一样发送字节数组来下载文件。我想用它。

I hope this will be usefull for anyone.

我希望这对任何人都有用。

回答by Rajesh Kumar

On Submit form

在提交表单上

public ActionResult ExportXls()
{   
 var filePath="";
  CommonHelper.WriteXls(filePath, "Text.xls");
}

 public static void WriteXls(string filePath, string targetFileName)
    {
        if (!String.IsNullOrEmpty(filePath))
        {
            HttpResponse response = HttpContext.Current.Response;
            response.Clear();
            response.Charset = "utf-8";
            response.ContentType = "text/xls";
            response.AddHeader("content-disposition", string.Format("attachment; filename={0}", targetFileName));
            response.BinaryWrite(File.ReadAllBytes(filePath));
            response.End();
        }
    }

回答by Elvin Acevedo

First Create the controller action that will create the Excel File

首先创建将创建 Excel 文件的控制器操作

[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 = "" });
}

then create the Download action

然后创建下载操作

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

if you want to delete the file after downloaded create this

如果要在下载后删除文件，请创建此文件

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

and finally ajax call from you MVC Razor view

最后从你的 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;
    }
});

回答by wilsjd

CSL's answer was implemented in a project I'm working on but the problem I incurred was scaling out on Azure broke our file downloads. Instead, I was able to do this with one AJAX call:

CSL 的答案是在我正在进行的一个项目中实现的，但我遇到的问题是在 Azure 上扩展破坏了我们的文件下载。相反，我可以通过一个 AJAX 调用来做到这一点：

SERVER

服务器

[HttpPost]
public FileResult DownloadInvoice(int id1, int id2)
{
    //necessary to get the filename in the success of the ajax callback
    HttpContext.Response.Headers.Add("Access-Control-Expose-Headers", "Content-Disposition");

    byte[] fileBytes = _service.GetInvoice(id1, id2);
    string fileName = "Invoice.xlsx";
    return File(fileBytes, System.Net.Mime.MediaTypeNames.Application.Octet, fileName);
}

CLIENT(modified version of Handle file download from ajax post)

CLIENT（从 ajax post 下载的Handle 文件的修改版本）

$("#downloadInvoice").on("click", function() {
    $("#loaderInvoice").removeClass("d-none");

    var xhr = new XMLHttpRequest();
    var params = [];
    xhr.open('POST', "@Html.Raw(Url.Action("DownloadInvoice", "Controller", new { id1 = Model.Id1, id2 = Model.Id2 }))", true);
    xhr.responseType = 'arraybuffer';
    xhr.onload = function () {
        if (this.status === 200) {
            var filename = "";
            var disposition = xhr.getResponseHeader('Content-Disposition');
            if (disposition && disposition.indexOf('attachment') !== -1) {
                var filenameRegex = /filename[^;=\n]*=((['"]).*?|[^;\n]*)/;
                var matches = filenameRegex.exec(disposition);
                if (matches != null && matches[1]) filename = matches[1].replace(/['"]/g, '');
            }
            var type = xhr.getResponseHeader('Content-Type');

            var blob = typeof File === 'function'
                ? new File([this.response], filename, { type: type })
                : new Blob([this.response], { type: type });
            if (typeof window.navigator.msSaveBlob !== 'undefined') {
                // IE workaround for "HTML7007: One or more blob URLs were revoked by closing the blob for which they were created. These URLs will no longer resolve as the data backing the URL has been freed."
                window.navigator.msSaveBlob(blob, filename);
            } else {
                var URL = window.URL || window.webkitURL;
                var downloadUrl = URL.createObjectURL(blob);

                if (filename) {
                    // use HTML5 a[download] attribute to specify filename
                    var a = document.createElement("a");
                    // safari doesn't support this yet
                    if (typeof a.download === 'undefined') {
                        window.location = downloadUrl;
                    } else {
                        a.href = downloadUrl;
                        a.download = filename;
                        document.body.appendChild(a);
                        a.click();
                    }
                } else {
                    window.location = downloadUrl;

                }

                setTimeout(function() {
                        URL.revokeObjectURL(downloadUrl);
                    $("#loaderInvoice").addClass("d-none");
                }, 100); // cleanup
            }
        }
    };
    xhr.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');
    xhr.send($.param(params));
});

回答by G.V.K.RAO

using ClosedXML.Excel;

使用 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);
    }