Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Download Excel file via AJAX MVC

I have a large(ish) form in MVC.

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

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.

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.

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

like image 428
Valuk Avatar asked May 21 '13 12:05

Valuk


2 Answers

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.

like image 29
Luchian Avatar answered Oct 11 '22 20:10

Luchian


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

UPDATE September 2016

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.

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 TempData bucket 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 TempData and download to the end users browser.

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.

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:

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

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 TempData rather than Session is that once TempData is 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.

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

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.location to this URL which will prompt the browser to download the file.

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({     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 parameter is the Controller/Action method where your code will create the Excel file.
  • data parameter contains the json data that would be extracted from the form.
  • returnValue would be the file name of your newly created Excel file.
  • The window.location command redirects to the Controller/Action method that actually returns your file for download.

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); } 
like image 169
connectedsoftware Avatar answered Oct 11 '22 22:10

connectedsoftware