Download Excel file via AJAX MVC

Question

I have a large (ish) form in MVC.

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

The hard bit is that this should not affect the rest of the form, and therefore I want to do this through AJAX. I came across several questions about SO that seem to be related, but I cannot fully understand what the answers mean.

This one seems the closest to what I need: asp-net-mvc-downloading-excel - but I'm not sure I understand the answer, and it's a couple of years old now. I also came across another article (I can’t find it anymore) about using iframes to handle file uploads, but I'm not sure how to get this to work with MVC.

My excel file returns a fine if I do a full post, but I cannot get it to work with AJAX in mvc.

Answer 1

You cannot directly return the file to be downloaded via an AJAX call, so an alternative approach is to use an AJAX call to send related data to your server. You can then use the server side code to create the Excel file (I would recommend using EPPlus or NPOI for this, although it seems like you have this part of the job).

UPDATE September 2016

My original answer (below) was over 3 years old, so I decided to upgrade since I no longer create files on the server when uploading files via AJAX, however I left the original answer, as this may be useful, it still depends on your specific requirements.

The usual script in my MVC applications reports via a web page with some user report settings configured (Date Ranges, Filters, etc.). When the user specified the parameters that they send to the server, a report is created (for example, an Excel file as output), and then I save the resulting file as a byte array in a TempData bucket with a unique link. This link is passed as a Json result for my AJAX function, which then redirects to a separate controller action to retrieve data from TempData and load the end users into the browser.

To give this in more detail, if you have an MVC View that has a form associated with the Model class, call Model ReportVM .

Firstly, to get a hosted model, a controller action is required, 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" } }; } 

An AJAX call that submits my MVC form to the above controller and receives a response is as follows:

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

Controller action to handle file upload:

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

Another change that can be easily done if necessary is to pass the MIME type of the file as the third parameter, so that one controller action can correctly serve different output file formats.

This eliminates the need for any physical files that have been created and stored on the server, so no housekeeping routines are required, and once again this becomes clear to the end user.

Note. The advantage of using TempData rather than Session is that after reading TempData data is cleared, so it will be more efficient in terms of memory usage if you have a large volume of file requests. See Best Practices for TempData .

ORIGINAL RESPONSE

You cannot directly return the file to be downloaded via an AJAX call, so an alternative approach is to use an AJAX call to send related data to your server. Then you can use the server side code to create the Excel file (I would recommend using EPPlus or NPOI for this, although it sounds like you have this part of the job).

Once the file was created on the server, return the file path (or just the file name) as the return value for your AJAX call, and then set the JavaScript URL window.location for this, which will force the browser to download the file.

From the point of view of end users, the file upload operation is seamless, as they never leave the page on which the request appears.

Below is a simple, contrived example of calling ajax to achieve this:

 $.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 is the Controller / Action method in which your code will create an Excel file.
• data strong> contains json data to be extracted from the form.
• returnValue will be the file name of your newly created Excel file.
• The window.location command redirects to the Controller / Action method, which actually returns your file to load.

Example controller method for the Download action:

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

Answer 2

My 2 cents - you don’t need to store Excel as a physical file on the server - instead, save it to (Session) Cache. Use the uniquely generated name for your Cache variable (which stores this Excel file) - this will be the return of your (initial) ajax call. Thus, you do not have to deal with file access problems, manage (delete) files when they are not needed, etc. And, having the file in the cache is faster to retrieve.

Answer 3

Recently, I was able to accomplish this in MVC (although I did not need to use AJAX) without creating a physical file, and thought I would share my code:

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

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

C # controller code:

  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 use a third-party tool ( GemBox.Spreadsheet ) to generate the Excel file, and it has the “Save to Stream” option. There are several ways to create Excel files that can be easily written to the memory stream.

 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 requests a file download, and the actual navigation does not occur.

Answer 4

I used the solution sent by CSL, but I would recommend that you do not keep file data in the session for the entire session. Using TempData, the file data is automatically deleted after the next request (which is the GET request for the file). You can also control the deletion of files in a session during the download process.

A session can consume a lot of memory / space depending on the SessionState storage and the number of files exported during the session, and if you have many users.

I updated the Serer code from CSL to use TempData instead.

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

Answer 5

First create a controller action that will create an Excel file

 [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 a 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 downloading 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 View MVC Razor

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

Answer 6

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

Answer 7

 $ .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) {

                 }

             });

Answer 8

This thread helped me create my own solution, which I will share here. At first I used the GET ajax request without any problems, but it got to the point where the request URL was exceeded, so I had to iterate over the POST.

javascript uses the jQuery file upload plugin and consists of 2 subsequent calls. One POST (for sending parameters) and one GET for file recovery.

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

Answer 9

The CSL answer was implemented in a project that I am working on, but the problem I encountered was that scaling on Azure interrupted the loading of our files. Instead, I was able to do this with a single AJAX call:

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 upload from ajax post )

 $("#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]*=((['"]).*?\2|[^;\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)); }); 

Answer 10

The accepted answer did not work for me, as I got the 502 Bad Gateway result from the ajax call, although everything seemed to return normally from the controller.

I may have reached the limit with TempData - not sure, but I found that if I used IMemoryCache instead of TempData , it worked fine, so here is my adapted version of the code in the accepted answer:

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

The AJAX call remains as with the accepted answer (I made no changes):

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

Controller action to handle file upload:

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

...

Now there is additional code to configure MemoryCache ...

To use "_cache", I inserted it into the controller constructor like this:

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

And make sure you have the following in ConfigureServices in Startup.cs:

 services.AddDistributedMemoryCache(); 

Answer 11

Maybe I sound pretty naive and can cause serious criticism, but here's how I did it,

( It does not include ajax for export , but it also does not complete the full postback )

Thanks for this post and this answer.

Create a simple controller

 public class HomeController : Controller { /* A demo action public ActionResult Index() { return View(model); } */ [HttpPost] public FileResult ExportData() { /* An example filter var filter = TempData["filterKeys"] as MyFilter; TempData.Keep(); */ var someList = db.GetDataFromDb(/*filter*/) // filter as an example /*May be here the trick, I'm setting my filter in TempData["filterKeys"] in another action,(GetFilteredPartial() illustrated below ) when searching for the data, so do not really need ajax here..to pass my filters.. */ var dt = Utility.ConvertToDataTable(someList); //Some utility to convert list to Datatable // I am using EPPlus nuget package using (ExcelPackage pck = new ExcelPackage()) { ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Sheet1"); ws.Cells["A1"].LoadFromDataTable(dt, true); using (var memoryStream = new MemoryStream()) { pck.SaveAs(memoryStream); return File(memoryStream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "ExportFileName.xlsx"); } } } //This is just a supporting example to illustrate setting up filters .. /* [HttpPost] public PartialViewResult GetFilteredPartial(MyFilter filter) { TempData["filterKeys"] = filter; var filteredData = db.GetConcernedData(filter); var model = new MainViewModel(); model.PartialViewModel = filteredData; return PartialView("_SomePartialView", model); } */ } 

And here are the views .. or view

 /* @model Models.MainViewModel @{ ViewBag.Title = "Home Page"; Layout = "~/Views/Shared/_Layout.cshtml"; } <div class="main-container">... Some code for, say, a partial View <div id="tblSampleBody"> @Html.Partial("_SomePartialView", Model.PartialViewModel) </div> */ //The actual part.. Just post this bit of data... Here, you are not posting the Main form.. @using (Html.BeginForm("ExportData", "Home", FormMethod.Post)) { <input type="submit" value="Export Data" /> } ... </div> /*And you may require to pass search/filter values.. as said in the accepted answer.. That can be done while searching the data.. and not while we need an export..for instance:- <script> var filterData = { SkipCount: someValue, TakeCount: 20, UserName: $("#UserName").val(), DepartmentId: $("#DepartmentId").val(), } function GetFilteredData() { $("#loader").show(); filterData.SkipCount = 0; $.ajax({ url: '@Url.Action("GetFilteredPartial","Home")', type: 'POST', dataType: "html", data: filterData, success: function (dataHTML) { if ((dataHTML === null) || (dataHTML == "")) { $("#tblSampleBody").html('<tr><td>No Data Returned</td></tr>'); $("#loader").hide(); } else { $("#tblSampleBody").html(dataHTML); $("#loader").hide(); } } }); } </script>*/ 

The whole point here is that we publish the form in the middle of Razor View, call the Action method, which returns FileResult , and in the returned file we return the file. ..
And to publish filter values, as was said (and, if required), I publish them in another action that they tried to describe.

Answer 12

I am using Asp.Net WebForm and just want to download the file from the server side. There are many articles, but I cannot find a simple answer. Now I tried the easy way and got it.

It is my problem.

I need to create many input buttons dynamically at runtime. And I want to add each button to load the button with a unique fileNumber.

I create each button as follows:

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

Run codeHide result

Each button calls this ajax method.

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

Run codeHide result

Then I wrote a simple simple method.

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

I generate this Form_1, Form_2, Form_3 .... And I'm going to delete these old files with another program. But if there is a way to simply send an array of bytes to load the file, for example using Response. I want to use it.

I hope this will be useful to everyone.

Answer 13

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