php一次导出三个sheet表,phpexcel 简单导出多个sheet

public function exportExcel(){

require_once 'include/PHPExcel.php';

$objPHPExcel = new PHPExcel(); //实例化Excel类

//第一个sheet填入主标题

$first_sheet_title = '入职';

$objPHPExcel->getActiveSheet()->setCellValue('A1', $title.$first_sheet_title);

//合并单元格

$objPHPExcel->getActiveSheet()->mergeCells('A1:D1');

//设置居中

$objPHPExcel->getActiveSheet()->getStyle('A1:D1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

//设置单元格宽

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(6);

$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);

$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);

$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);

$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);

//字体加粗

$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setName('宋体') //字体

->setBold(true); //字体加粗

$objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setName('宋体') //字体

->setBold(true); //字体加粗

$objPHPExcel->getActiveSheet()->getStyle('B2')->getFont()->setName('宋体') //字体

->setBold(true); //字体加粗

$objPHPExcel->getActiveSheet()->getStyle('C2')->getFont()->setName('宋体') //字体

->setBold(true); //字体加粗

$objPHPExcel->getActiveSheet()->getStyle('D2')->getFont()->setName('宋体') //字体

->setBold(true); //字体加粗

$objPHPExcel->getActiveSheet()->getStyle('E2')->getFont()->setName('宋体') //字体

->setBold(true); //字体加粗

//第一sheet入职

$array = array(

array("序号","部门","职位","姓名",'入职时间'),

);

$field = "id,department,post_title,real_name,CONCAT(entry_date_Y,'-',entry_date_M,'-',entry_date_D) as date";

//入职人员：入职时间+3个月时间 > 当前时间

$thirty_month = 3*30*24*3600;

$where = " unix_timestamp(now()) < UNIX_TIMESTAMP(CONCAT(entry_date_Y,'-',entry_date_M,'-',entry_date_D))+$thirty_month ".$where_1;

$array1 = M('files')->field($field)->where($where)->select();

$data = array();

if(is_array($array1)){

foreach($array1 as $key=>$value){

$data[$key][]=$value['id'];

$data[$key][]=$value['department'];

$data[$key][]=$value['post_title'];

$data[$key][]=$value['real_name'];

$data[$key][]=$value['date'];

}

}

//合并title

$new_arr = array_merge($array,$data);

//写入到第一个sheet

$k = 1;

foreach ($new_arr as $val){

$k = $k+1;

$objPHPExcel->getactivesheet()->setcellvalue('A'.$k, $val[0]);//第A列 第$k行

$objPHPExcel->getactivesheet()->setcellvalue('B'.$k, $val[1]);

$objPHPExcel->getactivesheet()->setcellvalue('C'.$k, $val[3]);

$objPHPExcel->getactivesheet()->setcellvalue('D'.$k, $val[4]);

$objPHPExcel->getactivesheet()->setcellvalue('E'.$k, $val[5]);

}

/*保存到第一个sheet*/

$objPHPExcel->getActiveSheet()->setTitle($first_sheet_title);

// Set active sheet index to the first sheet, so Excel opens this as the first sheet

$objPHPExcel->setActiveSheetIndex(0);

//第一sheet入职 -end

//第二sheet转正

$objPHPExcel->createSheet();

$objPHPExcel->setActiveSheetIndex(1);

$second = 1;

$sheet_title = '转正';

$A1_title = $title;

$A2_title = array(

array("序号","部门","职位","姓名",'入职时间','转正时间'),

);

$where = 'transfer_type = 6';

$where .= $where_2;

$this->makeData($objPHPExcel,$second,$sheet_title,$A1_title,$A2_title,$where);

//写入到第二个sheet-end

//最后通过浏览器输出

$fn = getUniName()."导出信息.xls";

header('Content-Type: application/vnd.ms-excel; charset=utf-8');

header("Content-Disposition: attachment;filename=$fn");

header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

$objWriter->save('php://output');

exit;

}

/*

* 封装

* @ $objPHPExcel phpexcel 对象

* @ $num int 第n个工作空间

* @ $sheet_title 工作空间sheet名称

* @ param string $A1_title 大标题

* @ param array $A2_title 每个字段名

* @ where sting or array 查询的条件

* @ return true or false;

*/

protected function makeData($objPHPExcel,$num,$sheet_title,$A1_title,$A2_title,$where){

$objPHPExcel->createSheet();

$objPHPExcel->setActiveSheetIndex($num);

$objPHPExcel->getActiveSheet()->setCellValue('A1', $A1_title);

//合并单元格

$objPHPExcel->getActiveSheet()->mergeCells('A1:F1');

//设置居中

$objPHPExcel->getActiveSheet()->getStyle('A1:F1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

//设置单元格宽

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(6);

$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);

$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);

$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);

$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);

$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);

//字体加粗

$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setName('宋体') //字体

->setBold(true); //字体加粗

$objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setName('宋体') //字体

->setBold(true); //字体加粗

$objPHPExcel->getActiveSheet()->getStyle('B2')->getFont()->setName('宋体') //字体

->setBold(true); //字体加粗

$objPHPExcel->getActiveSheet()->getStyle('C2')->getFont()->setName('宋体') //字体

->setBold(true); //字体加粗

$objPHPExcel->getActiveSheet()->getStyle('D2')->getFont()->setName('宋体') //字体

->setBold(true); //字体加粗

$objPHPExcel->getActiveSheet()->getStyle('E2')->getFont()->setName('宋体') //字体

->setBold(true); //字体加粗

$objPHPExcel->getActiveSheet()->getStyle('F2')->getFont()->setName('宋体') //字体

->setBold(true); //字体加粗

$field = "id,department_at,post_title_at,real_name,CONCAT(entry_date_Y,'-',entry_date_M,'-',entry_date_D) date_1,CONCAT(effective_date_Y,'-',effective_date_M,'-',effective_date_D) date_2";

// transfer_type 调动类型，1为调岗，2为晋升，3为加薪，4为降职，5为离职,6为转正

$array_3 = M('fer')->field($field)->where($where)->select();

$data = array();

if(is_array($array_3)){

foreach($array_3 as $key=>$value){

$data[$key][]=$value['id'];

$data[$key][]=$value['department'];

$data[$key][]=$value['post_title'];

$data[$key][]=$value['real_name'];

$data[$key][]=$value['date_1'];

$data[$key][]=$value['date_2'];

}

}

$new_arr = array_merge($A2_title,$data);

//写入到第n个sheet

$i = 1;

foreach ($new_arr as $val){

$i = $i+1;

$objPHPExcel->getactivesheet()->setcellvalue('A'.$i, $val[0]);//第A列 第$k行

$objPHPExcel->getactivesheet()->setcellvalue('B'.$i, $val[1]);

$objPHPExcel->getactivesheet()->setcellvalue('C'.$i, $val[2]);

$objPHPExcel->getactivesheet()->setcellvalue('D'.$i, $val[3]);

$objPHPExcel->getactivesheet()->setcellvalue('E'.$i, $val[4]);

$objPHPExcel->getactivesheet()->setcellvalue('F'.$i, $val[5]);

}

$objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,"Excel2007");

/*保存到本地*/

$objPHPExcel->getActiveSheet()->setTitle($sheet_title);

return true;

}