Using PHPExcel to export to xlsx

Question

I am using PHPEXxcel to export an HTML Table generated using MYSQL and its like this.

<?php $query = "SELECT `Firstname`,`Lastname`,`Branch`,`Gender`,`Mobileno`, `Email`  
      FROM `student_details` WHERE Branch IN ('$branch') and `Year`='$year' 
         and Tenthresult > '$tenth' and 
      Twelthresult > '$twelth' and (CGPA > '$cgpa' || CGPA = '$cgpa')";  

$result = mysql_query($query);
confirm_query($result);
$objPHPExcel = new PHPExcel(); 
$objPHPExcel->setActiveSheetIndex(0); 

$rowCount = 1; 
$objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount,'Firstname');
$objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount,'Lastname');
$objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount,'Branch');
$objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount,'Gender');
$objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount,'Mobileno');
$objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowCount,'Email');

while($row = mysql_fetch_array($result)){ 
    $rowCount++;
    $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $row['0']);
    $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $row['1']);
    $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, $row['2']);
    $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount, $row['3']);
    $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount, $row['4']);
    $objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowCount, $row['5']);
} 

$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); 
$objWriter->save('some_excel_file.xlsx'); 
?>

Its working but it saves the xlsx file in the root folder without showing to user any signs that its being downloaded. This code rund when i click a button.now, can i make it to be downloaded like we download a mail attachment and showing the user in the front end that its being downloaded along with the location.

I tried using

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="01simple.xls"');
header('Cache-Control: max-age=0'); 

With this, i am getting what i wanted above but the xls file downloaded when opened shows the message 'The File you are trying to open 'filename' is in a different format than the specified extension.....etc.Do you want to open now?

On opening it contains either the entire HTML Page or its simply blank... Can anybody help me..?

Answer 1

I do it with using below snippet.

// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="test_file.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');

Answer 2

Just adding those headers only sends those headers. The rest of your code is still the same, so you're saving your xls to your root folder like you used to.

Sending the headers only makes the page you would normally see be send with xls headers. Something which is not what you want, and you're getting your HTML page but with the wrong headers.

What you need to do is send those headers and then stream the xlsx.

Looking at a random thread (I know, bad idea, but this'll get you a headstart on what to do) here, you can find examples like this:

header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");;
header("Content-Disposition: attachment;filename=$filename.xls");
header("Content-Transfer-Encoding: binary ");
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); 
$objWriter->setOffice2003Compatibility(true);
$objWriter->save('php://output');