PHP excel导出（自定义样式，行高，合并单元格等）

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
        );
    }

<?php

namespace 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);
    }
}

<?php

namespace 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');
    }
}