详解phpexcel导出excel(支持excel2003,excel2007)+多个sheet(按人按选项卡导出)

按时间段统计所有员工的工作日志，导出为excel（支持2003，2007），按人按选项卡导出。完整例子，下面是代码，如有不足的地方，希望大家留言交流。

 

附件中是完整代码和例子的效果图！

 

<?php
详解phpexcel导出excel(支持excel2003,excel2007)+多个sheet(按人按选项卡导出)
/*按时间段统计所有员工的工作日志，导出为excel（支持2003，2007），按人按选项卡导出。完整例子，下面是代码，如有不足的地方，希望大家留言交流。

 引入所需的文件(提示：请将下载下来的包里的名为clsasses整个文件夹引入到你的项目中)
 */
 function  createHeader($objExcel)
{
 //表头
 $k1="日期";
 $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");
}
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");
$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);
//此段代码是导出单个sheet
if($_POST['submitone'])
{
 $mysql="select concat(c.contact_first_name,c.contact_last_name) as fullname,
   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 contacts as c
   on c.contact_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;
 createHeader($objExcel);
 $sheetname;
 $flag=false;
 while($arr=mysql_fetch_array($myresult))
 {
  $flag=true;
  /*----------从数据库读取数据--------------------*/
  $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"];
  $name=$arr["fullname"];
  $sheetname=$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);*/
  $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);
  
  // 添加条件格式 设置字体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($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);
  $filename=$showtime=date("Y-m-d-H-i-s").'-'.$sheetname.'.xlsx';
  header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  //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;
  // 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;
  */
 }
 else
 {
  echo "<script>alert('所查记录为空')</script>";
  echo "<script>javascript:window.history.go(-1)</script>";
  
 }
}
//此段代码是导出一个excel文件，包含多个sheet(按人按选项卡导出和命名)
if ($_POST['submitall'])
{
 $mysql="select concat(c.contact_first_name,c.contact_last_name) as fullname,
   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 contacts as c
   on c.contact_id=ut.user_id
   where
    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 ut.user_id,logdate"; 
 $myresult=mysql_query($mysql,$db);
 //定义标识变量
 $flag_newperson;
 $flag_oldperson;
 $flag_oldperson_control=true; 
 $i=0;
 //查询语句是否返回值
 $has_data_flag=false;
 //为每个sheet命名
 $sheetname;
 //设置sheet的索引(你也可以设置为0)
 $sheet_index=1;
 while($arr=mysql_fetch_assoc($myresult))
 {
     //如果进入循环，则说明有数据
  $has_data_flag=true;
  $flag_newperson=$arr["fullname"];
  if ($flag_oldperson!=$flag_newperson)
  { 
     //判断避免异常（我们在这里为新的sheet命名（我的例子中是员工的姓名），
     这里会有一个bug,就是我们无法对最后一个sheet命名，不过您不用担心，我在后面的代码中会单独处理最后一个sheet）
   if($sheet_index!=1)
   {
    // 重命名表
    //echo date('H:i:s') . " Rename sheet\n";
    $objExcel->getActiveSheet()->setTitle($sheetname);
   }
   $flag_oldperson=$arr["fullname"];
   $flag_oldperson_control=true;
  }
  if ($flag_oldperson_control)
   {
   $objExcel->createSheet();
   $objExcel->setActiveSheetIndex($sheet_index);
   $sheet_index++;
   createHeader($objExcel);
   $flag_oldperson_control=false;
   $i=0;
   }
  
  /*----------从数据库读取数据--------------------*/
  $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"];
  $name=$arr["fullname"];
  $sheetname=$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);*/
  $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($has_data_flag)
 {
  // 为最后一个sheet命名（单独处理最后一个sheet）
  //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(1);
  //输出内容 （保存到一个默认的路径，用户无法选择路径）
  //$objWriter->save(str_replace('.php', '.xls', __FILE__));
  // or  phpexcel 保存时可以选择路径
  //保存为excel2007格式
  $sheetname=iconv("utf-8","gb2312", $sheetname);
  $filename=$showtime=date("Y-m-d-H-i-s").'-'.$sheetname.'.xlsx';
  header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  //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;
  // 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;
  */
 }
 else
 {
  echo "<script>alert('所查记录为空')</script>";
  echo "<script>javascript:window.history.go(-1)</script>";
  
 }
}

?>

评论

1 楼 jasonchan1987 2011-09-07  

能不能把数据库表提供一下呢？或者用别的测试数据呢？能加您QQ吗？