PHP 导出多个sheet 或者导出带样式的excel 修改样式方式见下文 具体使用需要根据具体业务进行一些调整和封装
$PHPExcel = new \PHPExcel();$sheetIndex = 0;根据sheet的位置选择数值从0开始$PHPExcel->createSheet($sheetIndex);// 创建sheet $sheet = $PHPExcel->setActiveSheetIndex($sheetIndex); 选中sheet$sheet->setTitle('设置标题'); //设置标题$sheet->setCellValue(‘A1', '内容');//填充内容$objWriter = new \PHPExcel_Writer_Excel5($this->PHPExcel);$response = $this->createStreamedResponse( $objWriter);$dispositionHeader = $response->headers->makeDisposition(ResponseHeaderBag::DISPOSITION_ATTACHMENT,$exporter->getExportFileName(),'-');$response->headers->set('Content-Type', 'text/vnd.ms-excel; charset=utf-8');$response->headers->set('Pragma', 'public');$response->headers->set('Cache-Control', 'maxage=1');$response->headers->set('Content-Disposition', $dispositionHeader);return $response;protected function createStreamedResponse(\PHPExcel_Writer_IWriter $writer, $status = 200, $headers = []){return new StreamedResponse(function () use ($writer) {$writer->save('php://output');},$status,$headers);}//设置单元格行高$PHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20);//设置单元格宽度$PHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(200);//添加边框，加粗$style_array = ['borders' => ['allborders' => ['style' => \PHPExcel_Style_Border::BORDER_THIN,],], ];$PHPExcel->getActiveSheet()->getStyle('A1:F5')->applyFromArray($style_array);//设置文字大小$PHPExcel->getActiveSheet()->getStyle(‘A1’)->getFont()->setSize(16);//文字加粗#PHPExcel->getActiveSheet()->getStyle(‘A1’)->getFont()->setBold(true);//设置垂直居中$PHPExcel->getActiveSheet()->getStyle(‘A1’)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//设置水平居中$PHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//合并单元格$PHPExcel->getActiveSheet()->mergeCells(''A1:F1');//设置单元格背景色 颜色以FF开头加6位色码$PHPExcel->getActiveSheet()->getStyle('A1:F1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);$PHPExcel->getActiveSheet()->getStyle('A1:F1')->getFill()->getStartColor()->setARGB(‘FFadafb1’);//设置单元格内内容换行$this->PHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);然后内容中添加"\r\n\r\n" 换行
代码。例子 ： 基于Symfony 框架的。 可根据自己需要进行修改
导出入口文件public function taskLiveStatisticExportAction(Request $request, $taskId){$task = $this->getTaskService()->getTask($taskId);$exporter = (new CourseLiveStatisticExporter($this->getBiz()));$objWriter = $exporter->exporter(['taskId' => $task['id']], 0);$response = $this->createStreamedResponse($objWriter);$dispositionHeader = $response->headers->makeDisposition(ResponseHeaderBag::DISPOSITION_ATTACHMENT,$exporter->getExportFileName(),'-');$response->headers->set('Content-Type', 'text/vnd.ms-excel; charset=utf-8');$response->headers->set('Pragma', 'public');$response->headers->set('Cache-Control', 'maxage=1');$response->headers->set('Content-Disposition', $dispositionHeader);return $response;}protected function createStreamedResponse(\PHPExcel_Writer_IWriter $writer, $status = 200, $headers = []){return new StreamedResponse(function () use ($writer) {$writer->save('php://output');},$status,$headers);}
<?phpnamespace Biz\Exporter;use Codeages\Biz\Framework\Context\Biz;use PHPExcel_Exception;use PHPExcel_Writer_Exception;use Symfony\Component\Filesystem\Filesystem;abstract class BaseSheetAddStyleExporter{protected static $logger;protected $cols = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'];protected $filesystem = null;protected $biz = null;protected $PHPExcel = null;public function __construct(Biz $biz){$this->biz = $biz;}/*** @return mixed*//return xxxxxxxxxxxx.xls*/abstract public function getExportFileName();abstract public function getSortedHeadingRow();/*** @param array $params* @param int $save** @return bool** @throws PHPExcel_Exception* @throws PHPExcel_Writer_Exception*/public function exporter($params, $save = 1){$privateUploadDir = $this->biz['topxia.upload.private_directory'];$this->filesystem = new Filesystem();if (!empty($save)) {$path = "{$privateUploadDir}/data_export";if (!$this->filesystem->exists($path)) {$this->filesystem->mkdir($path, 0777);}}if (!empty($save) && $this->filesystem->exists($path.'/'.$this->getExportFileName())) {return true;}$this->PHPExcel = new \PHPExcel();$this->buildExportSheetData($params);$objWriter = new \PHPExcel_Writer_Excel5($this->PHPExcel);if (empty($save)) {return $objWriter;}$objWriter->save($path.'/'.$this->getExportFileName());return true;}// 自定义导出格式 行高 合并单元格 样式 数据 等abstract public function buildExportSheetData($params);protected function setSheetCellValue(\PHPExcel_Worksheet $sheet, $data, $start = 2){$i = 0;foreach ($this->getSortedHeadingRow() as $key => $useCol) {$sheet->setCellValue($this->cols[$i].$start, $key);$col = 1 + $start;foreach ($data as $key => $value) {$sheet->setCellValue($this->cols[$i].($key + $col), $value[$useCol]);}++$i;}}/*** A1/A1:G2** @param string[] $pCellCoordinates** 设置左对齐*/protected function setHorizontalLeft($pCellCoordinates = ['A1']){foreach ($pCellCoordinates as $pCellCoordinate) {$this->PHPExcel->getActiveSheet()->getStyle($pCellCoordinate)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);}}/*** A1/A1:G2** @param string[] $pCellCoordinates** 设置水平居中*/protected function setHorizontalCenter($pCellCoordinates = ['A1']){foreach ($pCellCoordinates as $pCellCoordinate) {$this->PHPExcel->getActiveSheet()->getStyle($pCellCoordinate)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);}}/*** A1/A1:G2** @param string[] $pCellCoordinates** 设置垂直居中*/protected function setVerticalCenter($pCellCoordinates = ['A1']){foreach ($pCellCoordinates as $pCellCoordinate) {$this->PHPExcel->getActiveSheet()->getStyle($pCellCoordinate)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::VERTICAL_CENTER);}}/*** A1/A1:G2** @param string[] $pCellCoordinates* @param int$size** 设置大小*/protected function setSize($pCellCoordinates, $size = 16){foreach ($pCellCoordinates as $pCellCoordinate) {$this->PHPExcel->getActiveSheet()->getStyle($pCellCoordinate)->getFont()->setSize($size);}}/*** A1/A1:G2** @param string $pCellCoordinate* @param string $color 'FFadafb1'** 设置背景色*/protected function setBackground($pCellCoordinate, $color){$this->PHPExcel->getActiveSheet()->getStyle($pCellCoordinate)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);$this->PHPExcel->getActiveSheet()->getStyle($pCellCoordinate)->getFill()->getStartColor()->setARGB($color);}/*** A** @param string[] $columns* @param int$size** 设置单元格宽度*/protected function setWidth($columns, $size = 20){foreach ($columns as $column) {$this->PHPExcel->getActiveSheet()->getColumnDimension($column)->setWidth($size);}}/*** @param int $size** 设置默认行高*/protected function setDefaultRowHeight($size = 20){$this->PHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight($size);}/*** A1/A1:G2** @param string[] $pCellCoordinates** 设置加粗*/protected function setBold($pCellCoordinates){foreach ($pCellCoordinates as $pCellCoordinate) {$this->PHPExcel->getActiveSheet()->getStyle($pCellCoordinate)->getFont()->setBold(true);}}/*** @param $pCellCoordinate** 添加边框，加粗*/protected function setBorders($pCellCoordinate){$style_array = ['borders' => ['allborders' => ['style' => \PHPExcel_Style_Border::BORDER_THIN,],], ];$this->PHPExcel->getActiveSheet()->getStyle($pCellCoordinate)->applyFromArray($style_array);}/*** @param $pCellCoordinate** @throws PHPExcel_Exception* 合并单元格*/protected function mergeCells($pCellCoordinate){$this->PHPExcel->getActiveSheet()->mergeCells($pCellCoordinate);}protected function createService($alias){return $this->biz->service($alias);}}
<?phpnamespace Biz\Exporter;use AppBundle\Common\ArrayToolkit;use AppBundle\Common\SimpleValidator;use Biz\Activity\Service\ActivityService;use Biz\Course\Service\CourseService;use Biz\Course\Service\CourseSetService;use Biz\LiveStatistics\Service\Impl\LiveCloudStatisticsServiceImpl;use Biz\Task\Service\TaskService;use Biz\User\Service\UserService;use PHPExcel_Exception;class CourseLiveStatisticExporter extends BaseSheetAddStyleExporter{protected $task = [];public function getExportFileName(){$time = date('Y_m_d_H_i', time());return "直播统计_{$time}.xls";}public function getSortedHeadingRow(){return ['用户名' => 'nickname','手机号' => 'mobile','邮箱' => 'email','进入直播间时间' => 'firstEnterTime','观看时长（分）' => 'watchDuration','签到数' => 'checkinNum','聊天数' => 'chatNum','答题数' => 'answerNum',];}public function buildExportSheetData($params){$sheetIndex = 0;try {$this->PHPExcel->createSheet($sheetIndex);$sheet = $this->PHPExcel->setActiveSheetIndex($sheetIndex);$this->setDefaultRowHeight();$this->PHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(110);//换行$this->PHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);$sheet->setCellValue('A1', $this->getHeadValue($params));$data = $this->buildData($params);$row = count($data) + 2;$this->setBorders('A1:H2');$this->setSize(['A1'], 14);$this->setBold(['A1:H2']);$this->setVerticalCenter(['A1']);$this->setHorizontalCenter(['A1', 'A2', 'B2:H'.$row]);$this->mergeCells('A1:H1');$sheet->setTitle('直播统计');$this->setWidth(['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H']);$this->setSheetCellValue($sheet, $data);} catch (PHPExcel_Exception $e) {throw $e;}}protected function getHeadValue($params){$this->task = $this->getTaskService()->getTask($params['taskId']);$course = $this->getCourseService()->getCourse($this->task['courseId']);$courseSet = $this->getCourseSetService()->getCourseSet($this->task['fromCourseSetId']);$result = $this->getLiveStatisticsService()->getLiveData($this->task);$title = (empty($course['title']) ? $courseSet['title'] : $course['title']).'-'.$this->task['title']."\r\n\r\n";$startTime = date('Y-m-d H:i', $result['startTime']);$endTime = date('Y-m-d H:i', $result['endTime']);$detail1 = "主讲人：{$result['teacher']} \t\t直播时间： {$startTime}至{$endTime} \t\t实际直播时长： {$result['length']} \r\n\r\n";$detail2 = "同时在线人数：{$result['maxOnlineNumber']} \t\t 观看人数：{$result['memberNumber']} \t\t 用户聊天数：{$result['chatNumber']} \t\t 人均观看时长：{$result['avgWatchTime']}\r\n";return "\r\n".$title.$detail1.$detail2;}protected function buildData($params){$activity = $this->getActivityService()->getActivity($this->task['activityId'], true);$params['liveId'] = $activity['ext']['liveId'];$conditions = ArrayToolkit::parts($this->buildUserConditions($params), ['courseId', 'liveId', 'userIds']);$members = $this->getLiveStatisticsService()->searchCourseMemberLiveData($conditions, 0, PHP_INT_MAX, ['firstEnterTime', 'watchDuration', 'checkinNum', 'chatNum', 'answerNum', 'userId']);$cloudStatisticData = $activity['ext']['cloudStatisticData'];$userIds = ArrayToolkit::column($members, 'userId');$users = $this->getUserService()->searchUsers(['userIds' => empty($userIds) ? [-1] : $userIds], [], 0, count($userIds), ['id', 'nickname', 'verifiedMobile', 'email', 'emailVerified']);$users = ArrayToolkit::index($users, 'id');foreach ($members as &$member) {$member['firstEnterTime'] = empty($member['firstEnterTime']) ? '--' : date('Y-m-d H:i', $member['firstEnterTime']);$member['nickname'] = empty($users[$member['userId']]) ? '--' : $users[$member['userId']]['nickname'];$member['email'] = empty($users[$member['userId']]) || empty($users[$member['userId']]['emailVerified']) ? '--' : $users[$member['userId']]['email'];$member['checkinNum'] = empty($cloudStatisticData['checkinNum']) ? '--' : $member['checkinNum'].'/'.$cloudStatisticData['checkinNum'];$member['mobile'] = empty($users[$member['userId']]) || empty($users[$member['userId']]['verifiedMobile']) ? '--' : $users[$member['userId']]['verifiedMobile'];$member['watchDuration'] = round($member['watchDuration'] / 60, 1);}return $members;}protected function buildUserConditions($params){if (!empty($params['nameOrMobile'])) {$mobile = SimpleValidator::mobile($params['nameOrMobile']);if ($mobile) {$user = $this->getUserService()->getUserByVerifiedMobile($params['nameOrMobile']);$users = empty($user) ? [] : [$user];} else {$users = $this->getUserService()->searchUsers(['nickname' => $params['nameOrMobile']],[],0,PHP_INT_MAX,['id']);}$userIds = ArrayToolkit::column($users, 'id');$params['userIds'] = empty($userIds) ? [-1] : $userIds;}unset($params['nameOrMobile']);return $params;}/*** @return ActivityService*/protected function getActivityService(){return $this->createService('Activity:ActivityService');}/*** @return UserService*/protected function getUserService(){return $this->createService('User:UserService');}/*** @return LiveCloudStatisticsServiceImpl*/protected function getLiveStatisticsService(){return $this->createService('LiveStatistics:LiveCloudStatisticsService');}/*** @return CourseSetService*/protected function getCourseSetService(){return $this->createService('Course:CourseSetService');}/*** @return CourseService*/protected function getCourseService(){return $this->createService('Course:CourseService');}/*** @return TaskService*/protected function getTaskService(){return $this->createService('Task:TaskService');}}
