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

User · Answer

ajax       global  false      url  SitePath     User ExportTeamMembersInExcel        data      UserName   UserName   RoleId   RoleId  UserIds  AppraseeId         type    POST        dataType    JSON       success   function  result            debugger         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

User · Answer

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   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       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  amp  amp  disposition indexOf  attachment       -1                    var filenameRegex    filename     n              2     n                      var matches   filenameRegex exec disposition                   if  matches    null  amp  amp  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

User · Answer

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

User · Answer

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   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    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 lt HttpResponseMessage gt  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

User · Answer

I may sound quite naive  and may attract quite a criticism  but here s how I did it   It doesn t involve ajax for export  but it doesn t do a full postback  either    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 s the trick  I m setting my filter in TempData  filterKeys         in an action  GetFilteredPartial   illustrated below  when  searching  for the data       so do not really need ajax here  to pass my filters              Some utility to convert list to Datatable      var dt   Utility ConvertToDataTable someList               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      Commenting out the View code  in order to focus on the imp  code        model Models MainViewModel    Layout                 Some code for  say  a partial View          lt div id  tblSampleBody  gt           Html Partial   SomePartialView   Model PartialViewModel         lt  div gt                                                                The actual part   Just   posting   this bit of data from the complete View      Here  you are not posting the full Form  or the complete View     using  Html BeginForm  ExportData    Home   FormMethod Post                  lt input type  submit  value  Export Data    gt                 lt  div gt     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 -                lt script gt                 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   lt tr gt  lt td gt No Data Returned lt  td gt  lt  tr gt                         loader   hide                  else                       tblSampleBody   html dataHTML                                           loader   hide                                               lt  script gt      The whole point of the trick seems that  we are posting a form  a part of the Razor View   upon which we are  calling an Action method  which returns  a FileResult  and this FileResult returns  the Excel File    And for posting the filter values  as said    and if you require to   I am making a post request to another action  as has been attempted to describe

User · 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        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                                 amp 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

User · Answer

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

User · Answer

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   Super simple JavaScript function  datatables net button click triggers 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 lt DrawingWinner gt  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   public static class ExportHelper       internal static void GetWinnersAsExcelMemoryStream MemoryStream stream  List lt DrawingWinner gt  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

User · Answer

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   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    x000D   x000D  fragment      lt div gt  lt input type   button   value   Create Excel   onclick   CreateExcelFile     fileNumber           gt  lt  div gt    x000D   x000D   x000D    Each button call this ajax method    x000D   x000D    ajax   x000D      type   POST   x000D      url   index aspx CreateExcelFile   x000D      data  jsonData  x000D      contentType   application json  charset utf-8   x000D      dataType   json   x000D      success  function  returnValue    x000D        window location     Reports Downloads     returnValue d  x000D        x000D      x000D   x000D   x000D    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   I hope this will be usefull for anyone

User · Answer

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

User · Answer

The accepted answer didn t quite work for me as I got a 502 Bad Gateway result from the ajax call even though everything seemed to be returning fine from the controller   Perhaps I was hitting a 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               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                                 amp filename     response FileName             The controller action to handle the downloading of the file    HttpGet  public virtual ActionResult Download string fileGuid  string fileName           if   cache Get lt byte   gt  fileGuid     null                byte   data    cache Get lt byte   gt  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 some extra code for setting up MemoryCache     In order to use   cache  I injected in the constructor for the controller like so   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

User · Answer

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

User · Answer

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

User · Answer

First Create the controller action that will create the 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 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     I use blockUI for loading      blockUI   message    lt h3 gt Please wait a moment    lt  h3 gt             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

[c#] Download Excel file via AJAX MVC

Examples related to c#

Examples related to jquery

Examples related to asp.net-mvc

Examples related to vb.net

Examples related to export-to-excel