PHPExcel导出excel表格
下载PHPExcel放在vendor文件夹下面
public function export(){
//1.从数据库中取出数据
$list_arr = $this->out();
$list = $list_arr['result'];
$title = $list_arr['title'];
//2.加载PHPExcle类库
vendor('PHPExcel.PHPExcel');
//3.实例化PHPExcel类
$objPHPExcel = new \PHPExcel();
//4.激活当前的sheet表
$objPHPExcel->setActiveSheetIndex(0);
//5.设置表格头(即excel表格的第一行)
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', '时间')
->setCellValue('B1', '00:00')
->setCellValue('C1', '01:00')
->setCellValue('D1', '02:00')
->setCellValue('E1', '03:00')
->setCellValue('F1', '04:00')
->setCellValue('G1', '05:00')
->setCellValue('H1', '06:00')
->setCellValue('I1', '07:00')
->setCellValue('J1', '08:00')
->setCellValue('K1', '09:00')
->setCellValue('L1', '10:00')
->setCellValue('M1', '11:00')
->setCellValue('N1', '12:00')
->setCellValue('O1', '13:00')
->setCellValue('P1', '14:00')
->setCellValue('Q1', '15:00')
->setCellValue('R1', '16:00')
->setCellValue('S1', '17:00')
->setCellValue('T1', '18:00')
->setCellValue('U1', '19:00')
->setCellValue('V1', '20:00')
->setCellValue('W1', '21:00')
->setCellValue('X1', '22:00')
->setCellValue('Y1', '23:00')
->setCellValue('Z1', '总数');
//设置列水平居中
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('D')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('E')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('F')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('G')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('H')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('I')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('J')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('K')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('L')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('M')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('N')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('O')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('P')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('Q')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('R')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('S')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('T')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('U')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('V')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('W')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('X')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('Y')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('Z')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//设置单元格宽度
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(30);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('H')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('I')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('J')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('K')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('L')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('M')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('N')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('O')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('P')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('Q')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('R')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('S')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('T')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('U')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('V')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('W')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('X')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('Y')->setWidth(15);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('Z')->setWidth(30);
//6.循环刚取出来的数组,将数据逐一添加到excel表格。
for($i=0;$i<count($list);$i++){
$objPHPExcel->getActiveSheet()->setCellValue('A'.($i+2),$list[$i]['time']);
$objPHPExcel->getActiveSheet()->setCellValue('B'.($i+2),$list[$i]['hour0']);
$objPHPExcel->getActiveSheet()->setCellValue('C'.($i+2),$list[$i]['hour1']);
$objPHPExcel->getActiveSheet()->setCellValue('D'.($i+2),$list[$i]['hour2']);
$objPHPExcel->getActiveSheet()->setCellValue('E'.($i+2),$list[$i]['hour3']);
$objPHPExcel->getActiveSheet()->setCellValue('F'.($i+2),$list[$i]['hour4']);
$objPHPExcel->getActiveSheet()->setCellValue('G'.($i+2),$list[$i]['hour5']);
$objPHPExcel->getActiveSheet()->setCellValue('H'.($i+2),$list[$i]['hour6']);
$objPHPExcel->getActiveSheet()->setCellValue('I'.($i+2),$list[$i]['hour7']);
$objPHPExcel->getActiveSheet()->setCellValue('J'.($i+2),$list[$i]['hour8']);
$objPHPExcel->getActiveSheet()->setCellValue('K'.($i+2),$list[$i]['hour9']);
$objPHPExcel->getActiveSheet()->setCellValue('L'.($i+2),$list[$i]['hour10']);
$objPHPExcel->getActiveSheet()->setCellValue('M'.($i+2),$list[$i]['hour11']);
$objPHPExcel->getActiveSheet()->setCellValue('N'.($i+2),$list[$i]['hour12']);
$objPHPExcel->getActiveSheet()->setCellValue('O'.($i+2),$list[$i]['hour13']);
$objPHPExcel->getActiveSheet()->setCellValue('P'.($i+2),$list[$i]['hour14']);
$objPHPExcel->getActiveSheet()->setCellValue('Q'.($i+2),$list[$i]['hour15']);
$objPHPExcel->getActiveSheet()->setCellValue('R'.($i+2),$list[$i]['hour16']);
$objPHPExcel->getActiveSheet()->setCellValue('S'.($i+2),$list[$i]['hour17']);
$objPHPExcel->getActiveSheet()->setCellValue('T'.($i+2),$list[$i]['hour18']);
$objPHPExcel->getActiveSheet()->setCellValue('U'.($i+2),$list[$i]['hour19']);
$objPHPExcel->getActiveSheet()->setCellValue('V'.($i+2),$list[$i]['hour20']);
$objPHPExcel->getActiveSheet()->setCellValue('W'.($i+2),$list[$i]['hour21']);
$objPHPExcel->getActiveSheet()->setCellValue('X'.($i+2),$list[$i]['hour22']);
$objPHPExcel->getActiveSheet()->setCellValue('Y'.($i+2),$list[$i]['hour23']);
$objPHPExcel->getActiveSheet()->setCellValue('Z'.($i+2),$list[$i]['total']);
}
//7.设置保存的Excel表格名称
$filename = $title.date('Y-m-d H:i:s',time()).'.xls';
//8.设置当前激活的sheet表格名称;
$objPHPExcel->getActiveSheet()->setTitle($title);
//9.设置浏览器窗口下载表格
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-Disposition:attachment;filename="'.$filename.'"');
//生成excel文件
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
//下载文件在浏览器窗口
$res = $objWriter->save('php://output');
exit;
}