使用phpexcel将MySql导出到excel [英] Using phpexcel to export MySql to excel

问题描述

大家好，我已经创建了一个将MySQL数据导出到excel的程序，但是在某些时候尝试打开excel文件时出现错误.

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?

当我单击是"时，我在该excel文件中得到了一些有趣的字符. 这是我的代码:

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

推荐答案

在使用文件扩展名.xlsx命名文件时，将其放在底部附近，这是Office 2007中基于openxml的文档的正确文件扩展名，可以解决您的问题.

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

如果您绝对需要使用xls将输出类型更改为Excel2003，但是还需要更改内容类型标头以使其匹配.

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.

这篇关于使用phpexcel将MySql导出到excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！