使用AJAX调用下载PHPExcel文件

Question

I have a PHPExcel code which I use to export file, It works completely fine when i run it through normal static inputs but when tried with ajax calls the file is not getting downloaded. the php code to generate code is as follows

include "dbconnect.php";
include 'PHPExcel/Classes/PHPExcel/IOFactory.php';
include 'PHPExcel/Classes/PHPExcel.php';
$objPHPExcel = new PHPExcel();
if (isset($_POST['sql'])) {
    $sql=mysql_query($_POST['sql']);
    echo $sql;
    if($sql === FALSE) {
        die(mysql_error()); // TODO: better error handling
    }
    $objPHPExcel->setActiveSheetIndex(0);
    $objPHPExcel->getActiveSheet()->setCellValue('A1', 'A');
    $objPHPExcel->getActiveSheet()->setCellValue('B1', 'B');
    $objPHPExcel->getActiveSheet()->setCellValue('C1', 'C');
    $objPHPExcel->getActiveSheet()->setCellValue('D1', 'D');
    $objPHPExcel->getActiveSheet()->setCellValue('E1', 'E');
    $objPHPExcel->getActiveSheet()->setCellValue('F1', 'F');
    $objPHPExcel->getActiveSheet()->setCellValue('G1', 'G');
    $objPHPExcel->getActiveSheet()->setCellValue('H1', 'H');
    $objPHPExcel->getActiveSheet()->setCellValue('I1', 'I');
    $objPHPExcel->getActiveSheet()->setCellValue('J1', 'J');
    $objPHPExcel->getActiveSheet()->setCellValue('K1', 'K');
    $objPHPExcel->getActiveSheet()->setCellValue('L1', 'L');
    $objPHPExcel->getActiveSheet()->setCellValue('M1', 'M');

    $n=2;

    while($sqlr= mysql_fetch_array($sql)) {
        $objPHPExcel->getActiveSheet()->setCellValue('A'.$n, $sqlr['a']);
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$n, $sqlr['b']);
        $objPHPExcel->getActiveSheet()->setCellValue('C'.$n, $sqlr['c']);
        $objPHPExcel->getActiveSheet()->setCellValue('D'.$n, $sqlr['d']);
        $objPHPExcel->getActiveSheet()->setCellValue('E'.$n, $sqlr['e']);
        $objPHPExcel->getActiveSheet()->setCellValue('F'.$n, $sqlr['f']);
        $objPHPExcel->getActiveSheet()->setCellValue('G'.$n, $sqlr['g']);
        $objPHPExcel->getActiveSheet()->setCellValue('H'.$n, $sqlr['h']);
        $objPHPExcel->getActiveSheet()->setCellValue('I'.$n, $sqlr['i']);
        $objPHPExcel->getActiveSheet()->setCellValue('J'.$n, $sqlr['j']);
        $objPHPExcel->getActiveSheet()->setCellValue('K'.$n, $sqlr['k']);
        $objPHPExcel->getActiveSheet()->setCellValue('L'.$n, $sqlr['l']);
        $objPHPExcel->getActiveSheet()->setCellValue('M'.$n, $sqlr['m']);
        $n++;
    }
    $objPHPExcel->getActiveSheet()->setTitle('SORTED');

    $objPHPExcel->setActiveSheetIndex(0);
    for($col = 'A'; $col !== 'Z'; $col++) {
        $objPHPExcel->getActiveSheet()
            ->getColumnDimension($col)
            ->setAutoSize(true);

        $objPHPExcel->getActiveSheet()->getStyle("A1:M1")->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()
            ->getStyle('A1:M1')
            ->getFill()
            ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()
            ->setARGB('75b847');
    }

    $objPHPExcel->setActiveSheetIndex(0);
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    ob_end_clean();
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="reports.xls"');
    header('Cache-Control: max-age=0');
    $objWriter->save('php://output');
}
else {
    echo "no luck";
}
?>

And the JS is as follows

$(document).on("click", "#submit2", function() {
    var sql=$("#sql").val();
    console.log(sql);
    $.ajax({
        type: "POST",
        url: 'exreports.php',
        data: {
            sql : sql
        },
        success: function () {
            window.open(this.url,'_blank' );
        }
    });

});

But this is not downloading the file, I dont want to refresh the page as user might perform some other operations, if there is any way to download in the same window it will be great.

Thanks in advance

Answer 1

https://stackoverflow.com/a/27702111/8221063

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.