SolvedLaravel Excel How to: save csv/xls files using ajax only

Hi guys,

I've seen that some of us are trying to serve file from ajax request. After some research, i didn't found any clear solution to do it. Tweaking some of them, i successfully exported csv and xls data from an Ajax request. The thing is, manipulation is different if the filetype is xls, because of the encoding, so theres a bit of tweaks.

Data is coming from a typical Eloquent query, converted to Array:

PHP

if(!empty(Input::get('exportType'))) { //i use a custom get parameter here
            $dd = Excel::create('testFileName', function($excel) use ($data) {
                $excel->sheet('testSheetName', function($sheet) use ($data) {
                    $sheet->fromArray($data->get()->toArray());
                });
                $excel->setTitle($filename);
                $excel->setLastModifiedBy(Carbon::now()->toDayDateTimeString()); //updated has Carbon::now() only now throw exception on vendor/phpoffice/phpexcel/Classes/PHPExcel/Writer/Excel5.php l847 strlen()
            });

            //tweak for serving XLS file from ajax (or go trough download() Excel method for csv files)
            if(Input::get('exportType') == 'xls') {
                $dd = $dd->string();
                $response =  array(
                    'filename' => 'testFileName', //as we serve a custom response, HTTP header for filename is not setted by Excel. From the JS, you need to retrieve this value if type is XLS to set filename
                    'file' => "data:application/vnd.ms-excel;base64,".base64_encode($dd)
                );
                return response()->success($response); //do a json encode
            } else {
                //direct use of Excel download method for non-xls files - xls files need special JS treatment
                $dd->download(Input::get('exportType')); //not XLS, so CSV (didnt tried xlsx, pdf is blank but not sure it's related to this)
            }
            die; //prevent malformed binary data stream, not sure if needed
        }

JS

$.ajax({
      cache: false,
      url: url, //GET route 
      responseType: 'ArrayBuffer', //not sure if needed
      data:  exportParam, //exportType parameter here
      success: function (data, textStatus, request) {

//you could need to decode json here, my app do it automaticly, use a try catch cause csv are not jsoned

        //already json decoded? custom return from controller so format is xls
        if(jQuery.isPlainObject(data)) {
          data = data.data; //because my return data have a 'data' parameter with the content
        }

        //V1 - http://stackoverflow.com/questions/35378081/laravel-excel-using-with-ajax-is-not-working-properly
        //+V3 - http://stackoverflow.com/questions/27701981/phpexcel-download-using-ajax-call
        var filename = "";
        var disposition = request.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, '');
        }
        if(!jQuery.isPlainObject(data)) { //is CSV - we use blob
           var type = request.getResponseHeader('Content-Type');
           var blob = new Blob([data], { type: type ,endings:'native'});
           var URL = window.URL || window.webkitURL;
           var downloadUrl = URL.createObjectURL(blob);
        }
        var a = document.createElement("a");
        a.href = jQuery.isPlainObject(data) ? data.file : downloadUrl; 
        a.download = jQuery.isPlainObject(data) ? data.filename : filename;
        document.body.appendChild(a);
        a.click();
        a.remove();
      },
      error: function (ajaxContext) {
        toastr.error('Export error: '+ajaxContext.responseText);
      }
    });

ps: this is not an issue

13 Answers

✔️Accepted Answer

I needed to return an xlsx from ajax, so I tweaked again a little and this is what I end up with:

PHP
$data is a Eloquent query converted to Array.

$myFile= Excel::create("filename", function($excel) use($data) {
   $excel->setTitle('title');
   $excel->sheet('sheet 1', function($sheet) use($data) {
     $sheet->fromArray($data, null, 'A1', true, true);
   });
});

$myFile = $myFile->string('xlsx'); //change xlsx for the format you want, default is xls
$response =  array(
   'name' => "filename", //no extention needed
   'file' => "data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,".base64_encode($myFile) //mime type of used format
);
return response()->json($response);

js

$.ajax({
      cache: false,
      url: url, //GET route 
      data:  params, //your parameters data here
      success: function (response, textStatus, request) {
        var a = document.createElement("a");
        a.href = response.file; 
        a.download = response.name;
        document.body.appendChild(a);
        a.click();
        a.remove();
      },
      error: function (ajaxContext) {
        toastr.error('Export error: '+ajaxContext.responseText);
      }
    });

Other Answers:

Thanks @randomhoodie!

For the 3.x package, I would update your PHP with something like this, per the upgrade guide:

        $myFile = Excel::raw(new YOUR_Export_Class, \Maatwebsite\Excel\Excel::XLSX);
        
        $response =  array(
           'name' => "filename", //no extention needed
           'file' => "data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,".base64_encode($myFile) //mime type of used format
        );
        
        return response()->json($response);

More Issues: