详解phpexcel导出excel(支持excel2003,excel2007)

最近公司要改一个php的开源项目，需要实现导出excel的功能，上网查了一下，决定采用phpexcel.网上的例子我修改了一下，又添加了自己的注释，测试过是可以运行的，把代码贴出来，和大家一起分享。

<?php
/*
引入所需的文件(提示：请将下载下来的包里的名为clsasses整个文件夹引入到你的项目中)
*/
require_once 'Classes/PHPExcel.php';
require_once 'Classes/PhpExcel/Writer/Excel2007.php';
require_once 'Classes/PhpExcel/Writer/Excel5.php';
include_once 'Classes/PhpExcel/IOFactory.php'; 
// 创建一个处理对象实例（此对象对于2003 2007是相同的）
$objExcel = new PHPExcel(); 
//设置属性 (这段代码无关紧要，其中的内容可以替换为你需要的)
$objExcel->getProperties()->setCreator("andy");
$objExcel->getProperties()->setLastModifiedBy("andy");
$objExcel->getProperties()->setTitle("Office 2003 XLS Test Document");
$objExcel->getProperties()->setSubject("Office 2003 XLS Test Document");
$objExcel->getProperties()->setDescription("Test document for Office 2003 XLS, generated using PHP classes.");
$objExcel->getProperties()->setKeywords("office 2003 openxml php");
$objExcel->getProperties()->setCategory("Test result file");
//开始处理数据(索引从0开始)
$objExcel->setActiveSheetIndex(0);
$userid=$_POST["user_id"];
$startdate=$_POST["log_start_date"];
$enddate=$_POST["log_end_date"];
$dbuser="root";
$dbpwd="123456";
$dbhost="localhost";
$dbdatabase="project";
$db=mysql_connect($dbhost,$dbuser,$dbpwd);
//mysql_query("set names 'gbk'");//这就是指定数据库字符集，一般放在连接数据库后面就行了(非常重要)
mysql_select_db($dbdatabase,$db);
$mysql="select u.user_username,date(log.task_log_date)as logdate,p.project_name,
   t.work_property_id,t.task_name,sum(log.task_log_hours) as hours
   from task_log as log
   join tasks    as t
   on  log.task_log_task=t.task_id
   join projects as  p
   on p.project_id=t.task_project
   join  user_tasks  as ut
   on t.task_id=ut.task_id
   join users as u
   on u.user_id=ut.user_id
   where ut.user_id=".$userid."
  and date_format(task_log_date,'%Y%m%d') between '".$startdate."' and '".$enddate.
 "' group by t.task_name,ut.user_id,date_format(task_log_date,'%Y%m%d'),p.project_name order by logdate";
$myresult=mysql_query($mysql,$db);
$i=0;
//表头
$k1="日期";
$k2="项目";
$k3="工作性质";
$k4="任务内容";
$k5="用时数(小时)";
/*-----------转码-----------*/
$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);
/*---------------------栏目名称-----------------------*/
$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");
while($arr=mysql_fetch_array($myresult))
{
 /*----------从数据库读取数据--------------------*/
 $logdate=$arr["logdate"];
 $projectname=$arr["project_name"];
 //1:需求  2:设计 3：编码  4：代码走查   5测试用例   6系统测试  7维护(these  code  to  avoid generating messy code)
 $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"];
 
 /*-----------转码-----------*/
 $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);*/
 $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");
 $i++;
}
// 高置列的宽度
//echo date('H:i:s') . " Set column widths\n";
$objExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
$objExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);

// 添加条件格式
// 设置字体
//echo date('H:i:s') . " Set fonts\n";
$objExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
$objExcel->getActiveSheet()->getStyle('A7')->getFont()->setBold(true);
$objExcel->getActiveSheet()->getStyle('B7')->getFont()->setBold(true);

// 设置页眉和页脚。如果没有不同的标题奇数/即使是使用单头假定.
//echo date('H:i:s') . " Set header/footer\n";
$objExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&BPersonal cash 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('Invoice');

// 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__));
//phpexcel 保存时可以选择路径
//保存为excel2007格式
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="01simple.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
$objWriter->save('php://output');
exit;

// Redirect output to a client’s web browser (Excel5)保存为excel2003格式
/*
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="01simple.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
$objWriter->save('php://output');
exit;
*/

?>

评论

1 楼 ldl_xz 2014-07-08  

http://www.9958.pw/post/phpexcel_2003_2007 试试这个