PHPExcel download using ajax call

Question

App::import('Vendor', 'PHPExcel/Classes/PHPExcel');
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle('ReceivedMessages');
header('Content-Type: application/vnd.ms-excel');
$file_name = "kpi_form_".date("Y-m-d_H:i:s").".xls";
header("Content-Disposition: attachment; filename=$file_name");
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');

When I call above code directly from the browser, the result file is downloaded. But if I make an ajax call to above code, I don't get the download prompt. I can see from console tab that the ajax call was successfully completed and a bunch of random characters is seen in the response data. I'm assuming that is the excel object.

Does anyone know how I can achieve the download excel feature using ajax? I don't want to refresh the page. When the user clicks on the "export" button, there should be an ajax call to the php file and prompt the user to download.

Solution 1

add target=_blank in your ajax success function like below

success: function(){
  window.open('http://YOUR_URL','_blank' );
},

otherwise you can handle smartly to open your Excel download link in new tab with jQuery trigger function or etc.

Solution 2

PHP

$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
ob_start();
$objWriter->save("php://output");
$xlsData = ob_get_contents();
ob_end_clean();

$response =  array(
        'op' => 'ok',
        'file' => "data:application/vnd.ms-excel;base64,".base64_encode($xlsData)
    );

die(json_encode($response));

JS

$.ajax({
    type:'POST',
    url:"MY_URL.php",
    data: {},
    dataType:'json'
}).done(function(data){
    var $a = $("<a>");
    $a.attr("href",data.file);
    $("body").append($a);
    $a.attr("download","file.xls");
    $a[0].click();
    $a.remove();
});

Solution 3

You cannot download a file using ajax neither by phpexcel nor by php itself as its a security reason and almost browsers doesn't support it. But, you can try window.location in success callback like,

var page='mydownload.php';
$.ajax({
    url: page,
    type: 'POST',
    success: function() {
        window.location = page;// you can use window.open also
    }
});

Also @freakish answered for this type of question

Even, you don't need of ajax you can use hyperlink for the page like,

<a href="mydownload.php" target="_blank" >Download</a>

Solution 4

i am trying to do the same thing in spreadsheet and it works so well with a xls file but not with mpdf file

IOFactory::registerWriter('Pdf', $this->classNamePdf);
ob_start();
IOFactory::createWriter($this->exportType, 'Pdf')->save('php://output');
$pdfData = ob_get_contents();
ob_end_clean();

return "data:application/pdf; base64,".base64_encode($pdfData);

success: function(response){
    var win = window.open("", "_blank");
    win.location.href = response;
}

View more solutions

Comments

[Wiram Rathod]

great happy new year in advance enjoy :)

[Nilesh Daldra]

Awesome bud..you save my lots of time..keep it up...(y)

[Marco Muciño]

This works but at least in Chrome the new window is blocked and you must give explicit permision to open and download the generated file.

[Roshimon]

window.location on the success callback has worked for me :)

[Roshimon]

This unfortunately didn't work for me. What worked for me is just setting the window.location to the endpoint url in the done callback.

[Roshimon]

The problem with window.open is that on browsers you gotta enable permission the first time. So instead of window.open I've used window.location.

[felixRo]

After searching this is the best answer that worked for me, because I can handle error messages and simply create a waiting indicator (or message) for larger files. window.location was not enough for error handling, and <a href> with download attribute was hard to implement waiting indicator. So thanks for sharing!!! :) (I just use it without an array and json encoding.)

[dcarb]

i have found the solution after many many tries: \PhpOffice\PhpSpreadsheet\Shared\File::setUseUploadTempDirec‌​tory("...");

[Roberto Sepúlveda]

This worked for me in PHPExcel, but is not working in phpSpreadSheet (current PHPExcel). I'll be glad if someone know how to make this work with phpSpreadSheet.

[Illegal Operator]

GAWD, thank you, this really shouldn't be this difficult, yet here we are.