php输出EXCEL文件总是出现乱码,而且输出纯中文、英文或数字都是乱码,关键是存放在服务器上,然后下载下来后没有乱码现象。
网上搜了很多方法,都不行,而且在windows下没问题,服务器是Linux的就是乱码。经过两天的研究终于解决了。
在最上面,也就是header之前加入
[php]@ob_end_clean[/php]
完整代码:
[php]
@ob_end_clean();
header("Content-type: text/html;charset=utf-8");
// by www.kangka.net
//kangka
// 2012-08-11
require_once ‘class/PHPExcel/PHPExcel.php';
require_once ‘class/PHPExcel/PHPExcel/Writer/Excel2007.php';
require_once ‘class/PHPExcel/PHPExcel/Writer/Excel5.php';
include_once ‘class/PHPExcel/PHPExcel/IOFactory.php';
include_once ‘CEL/include.php';
//创建一个处理对象实例(此对象对于2003 2007是相同的)$objExcel = new PHPExcel();
//设置属性(这段代码无关紧要,其中的内容可以替换为你需要的)
$objExcel->getProperties()->setCreator("andy");
$objExcel->getProperties()->setLastModifiedBy("andy");
$objExcel->getProperties()->setTitle("Office 2003 XLS TestDocument");
$objExcel->getProperties()->setSubject("Office 2003 XLS TestDocument");
$objExcel->getProperties()->setDescription("Test documentfor Office 2003 XLS, generated using PHP classes.");
$objExcel->getProperties()->setKeywords("office 2003 openxmlphp");
$objExcel->getProperties()->setCategory("Test resultfile");
//mysql_query("set names utf8’");//这就是指定数据库字符集,一般放在连接数据库后面就行了(非常重要)
function createHeader($objExcel)
{
//表头$k1="id";
/* $k2="项目";
$k3="工作性质";
$k4="任务内容";
$k5="用时数(小时)";
$k6="姓名";*/
/*———–转码———–*/
$k1=iconv("gb2312","utf-8",$k1);
/* $k2=iconv("gb2312","utf-8",$k2);
$k3=iconv("gb2312","utf-8",$k3);
$k4=iconv("gb2312","utf-8",$k4);
$k5=iconv("gb2312","utf-8",$k5);
$k6=iconv("gb2312","utf-8",$k6);*/
/*———————栏目名称———————–*/
$objExcel->getActiveSheet()->setCellValue(‘a1′, "$k1");
/* $objExcel->getActiveSheet()->setCellValue(‘b1′,"$k2");
$objExcel->getActiveSheet()->setCellValue(‘c1′, "$k3");
$objExcel->getActiveSheet()->setCellValue(‘d1′, "$k4");
$objExcel->getActiveSheet()->setCellValue(‘e1′, "$k5");
$objExcel->getActiveSheet()->setCellValue(‘f1′,"$k6");*/
}
$mysql = "select * fromonline_2012";
$myresult=mysql_query($mysql,$conn);
$i=0;
createHeader($objExcel);
$sheetname;
$flag=false;
while($arr=mysql_fetch_array($myresult))
{
$flag=true;
/*———-从数据库读取数据——————–*/
$logdate=$arr["id"];
// $projectname=$arr["project_name"];
//1:需求2:设计3:编码4:代码走查5测试用例6系统测试7维护(these code to avoid generating messycode)
/*$message="未填写";
switch ($arr["work_property_id"])
{
case 1:
$message="需求";
break;
case 2:
$message="设计";
break;
case 3:
$message="编码";
break;
case 4:
$message="代码走查";
break;
case 5:
$message="测试用例";
break;
case 6:
$message="系统测试";
break;
case 7:
$message="维护";
break;
default:
$message;
break;
}
$workproperty=$message;
$taskname=$arr["task_name"];
$hours=$arr["hours"];
$name=$arr["fullname"];
/*———–转码———–*/
// $workproperty=iconv("gb2312","utf-8", $workproperty);
/*$logdate=iconv("gb2312","utf-8",$logdate);
$projectname=iconv("gb2312","utf-8",$projectname);
$workproperty=iconv("gb2312","utf-8", $workproperty);
$taskname=iconv("gb2312","utf-8",$taskname);
$hours=iconv("gb2312","utf-8",$hours);*/
$sheetname="123";
$u1=$i+2;
/*———-写入内容————-*/
$objExcel->getActiveSheet()->setCellValue(‘a’.$u1,"$logdate");
/* $objExcel->getActiveSheet()->setCellValue(‘b’.$u1,"$projectname");
$objExcel->getActiveSheet()->setCellValue(‘c’.$u1,"$workproperty");
$objExcel->getActiveSheet()->setCellValue(‘d’.$u1,"$taskname");
$objExcel->getActiveSheet()->setCellValue(‘e’.$u1,"$hours");
$objExcel->getActiveSheet()->setCellValue(‘f’.$u1,"$name");*/
$i++;
}
if($flag)
{
//高置列的宽度//echo date(‘H:i:s’) . " Set column widths\n";
$objExcel->getActiveSheet()->getColumnDimension(‘A’)->setWidth(30);
//$objExcel->getActiveSheet()->getColumnDimension(‘B’)->setWidth(12);
//添加条件格式设置字体echodate(‘H:i:s’) . " Set fonts\n";
$objExcel->getActiveSheet()->getStyle(‘A1′)->getFont()->setBold(true);
/*$objExcel->getActiveSheet()->getStyle(‘B1′)->getFont()->setBold(true);
$objExcel->getActiveSheet()->getStyle(‘C1′)->getFont()->setBold(true);
$objExcel->getActiveSheet()->getStyle(‘D1′)->getFont()->setBold(true);
$objExcel->getActiveSheet()->getStyle(‘E1′)->getFont()->setBold(true);
$objExcel->getActiveSheet()->getStyle(‘F1′)->getFont()->setBold(true);*/
//设置页眉和页脚。如果没有不同的标题奇数/即使是使用单头假定.echo date(‘H:i:s’) . " Set header/footer\n";
$objExcel->getActiveSheet()->getHeaderFooter()->setOddHeader(‘&L&BPersonalcash register&RPrinted on &D’);
$objExcel->getActiveSheet()->getHeaderFooter()->setOddFooter(‘&L&B’. $objExcel->getProperties()->getTitle() . ‘&RPage&P of &N’);
//设置页方向和规模//echo date(‘H:i:s’) . " Set page orientation and size\n";
$objExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
$objExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
//重命名表//echo date(‘H:i:s’) . " Rename sheet\n";
$objExcel->getActiveSheet()->setTitle($sheetname);
// Set active sheet index to the first sheet,so Excel opens this as the first sheet
$objExcel->setActiveSheetIndex(0);
//输出内容(保存到一个默认的路径,用户无法选择路径)//$objWriter->save(str_replace(‘.php’, ‘.xls’, __FILE__));
// or phpexcel保存时可以选择路径//保存为excel2007格式//$sheetname=iconv("utf-8","gb2312", $sheetname);
$sheetname=iconv("utf-8", "gb2312//IGNORE", $sheetname);
$filename=date("Y-m-d-H-i-s").’-‘.$sheetname.’.xls';
//下面注释掉的代码,会产生上述问题//header(‘Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’);
//header(‘Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8′);
/* Header("Content-type: application/x-msdownload");
//header(‘Content-Disposition: attachment;filename="01simple.xls"’);
header(‘Content-Disposition: attachment;filename="’.$filename.’"’);
header(‘Cache-Control: max-age=0′);
$objWriter = PHPExcel_IOFactory::createWriter($objExcel, ‘Excel2007′);
$objWriter->save(‘php://output’);
exit;*/
//解决方法header("Content-type: text/csv");//重要header(‘Content-Disposition: attachment;filename="’.$filename.’"’);
header(‘Cache-Control: must-revalidate, post-check=0,pre-check=0′);
header(‘Expires:0′);
header(‘Pragma:public’);
$objWriter = PHPExcel_IOFactory::createWriter($objExcel, ‘Excel5′);
$objWriter->save(‘php://output’);
exit;
}
?>[/php]