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.

score 48 · Answer 1 · answered Apr 04 '16 at 20:37

48

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

answered Apr 04 '16 at 20:37

Cristiano Gehring

631
1
5
7

1

Awesome bud..you save my lots of time..keep it up...(y) – Nilesh Daldra Jul 24 '17 at 14:07
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 Aug 05 '18 at 07:27
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 with download attribute was hard to implement waiting indicator. So thanks for sharing!!! :) (I just use it without an array and json encoding.) – felixRo Jan 17 '20 at 07:13
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. – Roberto Sepúlveda Oct 13 '20 at 13:54

Wiram Rathod · Accepted Answer · 2014-12-30T08:47:13.990

15

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.

edited Dec 30 '14 at 08:47

answered Dec 30 '14 at 08:40

Wiram Rathod

1,805
1
19
39

great happy new year in advance enjoy :) – Wiram Rathod Dec 30 '14 at 08:51
1

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. – Marco Muciño Aug 17 '17 at 23:51
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. – Roshimon Aug 05 '18 at 07:33

score 8 · Answer 3 · edited May 23 '17 at 12:17

8

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>

edited May 23 '17 at 12:17

Community

1
1

answered Dec 30 '14 at 08:35

Rohan Kumar

38,998
11
69
99

window.location on the success callback has worked for me :) – Roshimon Aug 05 '18 at 07:25

dcarb · Answer 4 · 2020-06-20T14:03:21.153

1

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

edited Jun 20 '20 at 14:03

answered Jun 18 '20 at 13:26

dcarb

11
3

i have found the solution after many many tries: \PhpOffice\PhpSpreadsheet\Shared\File::setUseUploadTempDirectory("..."); – dcarb Jun 18 '20 at 15:02

PHPExcel download using ajax call

4 Answers4

Linked