使用phpexcel将MySql导出为ex​​cel

Question

Hi everyone I have created a program to export MySQL data to excel but at some point I am getting the error the time I am trying to open the excel file.

The error: The file you are trying to open is in a different format,than specified by the file extension.Verify that the file is not corrupt and is from the trusted source before opening the file.Do you want to open the file now?

When I click yes i get the some funny characters on that excel file. Here is my code:

 include 'setup.php'; 
 _connectsurvey(); //Database
 require_once 'PHPExcel.php';

 $objPHPExcel = new PHPExcel();

 $query = "SELECT * FROM questionanswer";

 $result = mysql_query($query) or die(mysql_error());

 $objPHPExcel = new PHPExcel();

 $rowCount = 1;

 while($row = mysql_fetch_array($result)){
$objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $row['gender']);

  $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $row['city']);
  $rowCount++;
  pr($objPHPExcel);
}

 header('Content-Type: application/vnd.openxmlformats-   officedocument.spreadsheetml.sheet');
 header('Content-Disposition: attachment;filename="survey.xls"');
 header('Cache-Control: max-age=0');

 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
 $objWriter->save('php://output');

Answer 1

Near the bottom when you name the file use file extension .xlsx it is the proper file extension for openxml based documents from office 2007 should fix your issue.

header('Content-Disposition: attachment;filename="survey.xlsx"');

If you absolutely need to use xls change the output type to Excel2003 but then you need to change the content type header to match that as well.