Using PHPExcel to export to xlsx

问题:

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..?

回答1:

Spreadsheets 101

There are many different spreadsheet file formats, each with their own different filename extensions, and that can be sent to a web browser using different mime types. These are described in the PHPExcel documentation, and each has its own different Writer in PHPExcel. You're mismatching two different formats

BIFF Format

• Used by Microsoft Excel between versions 95 and 2003 File

• extension: xls

• PHPEXcel Writer: PHPExcel_Writer_Excel5

• Mime Type: application/vnd.ms-excel

OfficeOpenXML Format

• Used by Microsoft Excel since version 2007

• File extension: xlsx

• PHPEXcel Writer: PHPExcel_Writer_Excel2007

• Mime Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Don't mix and match: if you do, then Excel will (and justifiably) complain. If you want a BIFF file, use PHPExcel's BIFF Writer (Excel5), a file extension of .xls, and the mime type listed above for BIFF Format. If you want an OfficeOpenXML file, then use PHPExcel's Excel2007 Writer, a file extension of .xlsx, and the mime type listed above for OfficeOpenXML.

EDIT

Note that the examples provided with the PHPExcel distribution include 01simple-download-xls.php and 01simple-download-xlsx.php to demonstrate exactly what you want

回答2:

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

回答3:

try this ..

header('Content-Type: application/vnd.ms-excel');
    $filename = "Reports".date("d-m-Y-His").".xls";
    header('Content-Disposition: attachment;filename='.$filename .' ');
    header('Cache-Control: max-age=0');
    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');