/*
* 封装
* @ $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) as date_1,CONCAT(effective_date_Y,'-',effective_date_M,'-',effective_date_D) as 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]);
}
// 生成2007Excel文件(.xlsx)
$objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,"Excel2007");
/*保存到本地*/
$objPHPExcel->getActiveSheet()->setTitle($sheet_title);
return true;
}
实例化上面的方法之后,可选择输出到浏览器
$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, 'Excel2007');
$objWriter->save('php://output');
exit;