How to: save csv/xls files using ajax only

[mtx-z]

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

[jafar-jabr]

Thank you very much it works good for CSV but not xls ,

[randomhoodie]

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

[vapdl]

thanks!!

[deepakagrawal2309]

i am getting "Class 'Excel' not found" Error .could u please help me?

[eldyvoon]

@randomhoodie any source how you came out with that solution?

[randomhoodie]

@eldyvoon like I said, "I tweaked" the original answer, take away what I didn't need, make it compact, and duckduckgo (search engine) for the mime type of ms office xlsx extension, I wasn't sure it was gonna work until I tried it, but I did tried it before posting, and since it worked I posted it, in case anyone would find it useful.

[omarjebari]

I found that no javascript nor ajax is needed at all. I have a web page with links to download a bunch of different csv/xls/xlsx files and i don't want the page refreshing at all. All i did was hook a link up to an action which returned the following...

public function getSpreadsheet() {
$items = Item::all();
Excel::create('items', function($excel) use($items) {
$excel->sheet('ExportFile', function($sheet) use($items) {
$sheet->fromArray($items);
});
})->export('xls');
}

[pmiranda-geo]

Wonderfull!!!!

[kynetiv]

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

[admont28]

Thanks @kynetiv, I use version 3.x, but I needed to put extension like: filename.xlsx

[MoFoLuWaSo]

If you are still having this issue in 2020. Note that version 3.x of Laravel excel has changed so here is the solution

1. Send your data through Ajax to your controller that interact with the Laravel Excel Object
2. Let the Laravel excel object send your data to a blade view
3. Store the blade view on the server
4. use js to download the file on the server.
   So the idea is to export a blade view as excel and store it on a disk location where you can download it with
   javascript.

Example:

 $exports = new ReportsExporter($data, $columns);
  Excel::store($exports , 'filename.xlsx', 'custom_disk_location');

define your custom disk location in config filesystem like this

'custom_disk_location' => [
            'driver' => 'local',
            'root' => public_path('files'),
        ],
...

this will ensure the excel file is not saved in the storage/app
but will save it in the public/files path on your server

back to your javascript, download the file like this

function download(filename, path) {
        let element = document.createElement('a');
        element.setAttribute('href', path);
        element.setAttribute('download', filename);

        element.style.display = 'none';
        document.body.appendChild(element);

        element.click();

        document.body.removeChild(element);
    }

call the download function by passing the filename and path
download("filename.xlsx", location.origin+"files/filename.xlsx");

after the download remember to go back to the server and remove the one stored on the server like this
unlink("files/filename.xlsx");

I hope this helps anyone finding it hard to download laravel-excel through ajax or javascript.
This is a better option since it gives you more flexibility to customize your user experience and
give them feedback as regards the status of download as well as naming your file the way you like.

[nagasawaja]

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

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

so good