TP5 PhpExcel 导出多张sheet表

导出多班级名单,sheet表名称使用班级名称命名

vendor('phpexcel.PHPExcel');
$objPHPExcel = new \PHPExcel();      //实例化Excel类
foreach ($banji as $key => $val){
          $sheet_title = $val['title'];
            if (0 < $key){
                //第二个及以后sheet写入
                $objPHPExcel->createSheet();
            }
            $objPHPExcel->setActiveSheetIndex($key);

            $objPHPExcel->getActiveSheet()->setCellValue('A1', '班级名称:'.$val['title']);
            //合并单元格
            $objPHPExcel->getActiveSheet()->mergeCells('A1:B1');
            //设置居中
            $objPHPExcel->getActiveSheet()->getStyle('A1:B1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            $objPHPExcel->getActiveSheet()->setCellValue('A2', '关联课程名称:'.$val['arc_title']);
            //合并单元格
            $objPHPExcel->getActiveSheet()->mergeCells('A2:B2');
            //设置居中
            $objPHPExcel->getActiveSheet()->getStyle('A2:B2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            //设置单元格宽
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);

            //写入到第一个sheet
            $col = 3;
            $objPHPExcel->getactivesheet()->setcellvalue('A' . $col, '学员姓名');//第A列 第$k行
            $objPHPExcel->getactivesheet()->setcellvalue('B' . $col, '联系手机号码');
            if (!empty($val['stu_arr'])){
                foreach ($val['student_arr'] as $k => $v) {
                    $col = $col + 1;
                    $objPHPExcel->getactivesheet()->setcellvalue('A' . $col, $v['student_name']);//第A列 第$k行
                    $objPHPExcel->getactivesheet()->setcellvalue('B' . $col, $v['mobile']);
                }
            }

            /*保存到本地*/
            $objPHPExcel->getActiveSheet()->setTitle($sheet_title);
}

vendor('phpexcel.PHPExcel.IOFactory');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

 $path = UPLOAD_PATH . 'excel/班级.xlsx';
$objWriter->save($path); //保存文件
return $path;

导出效果

9RO5~7BQAZC_U0@VST2~(PX.png

一些常用导出设置

//所有单元格文字居中
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//所有单元格文字垂直居中
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//所有单元格（行）默认高度
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20);
//所有单元格（列）默认宽度
$objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(20);
//设置行高度
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
//设置列宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
//设置文字大小
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
//设置是否加粗
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
// 设置文字颜色
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
//设置文字居左（HORIZONTAL_LEFT，默认值）中（HORIZONTAL_CENTER）右（HORIZONTAL_RIGHT）
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//垂直居中
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//设置填充颜色
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
//设置填充颜色 setARGB用大写,setRGB用小写
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF7F24');