[PHP知识库]PHP 生成Excel 简易封装类

使用前请先安装 phpoffice/phpexcel
安装命令：composer require phpoffice/phpexcel

namespace App\Tools;

use Exception;
use PHPExcel;
use PHPExcel_CachedObjectStorageFactory;
use PHPExcel_Cell_DataValidation;
use PHPExcel_Exception;
use PHPExcel_IOFactory;
use PHPExcel_Reader_Exception;
use PHPExcel_Settings;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Worksheet;
use PHPExcel_Writer_Exception;
use Swoft\Log\Log;

class ExcelTpl
{
    /**
     * Excel句柄
     * @var
     */
    private $objExcel;

    /**
     * sheet和文件名
     * @var
     */
    private $title;

    /**
     * 文件临时保存路径
     * @var string
     */
    private $filePath = "/var/cache/nginx/demo/";

    /**
     * @param string $title sheet和文件名
     * @param array $fieldData 表头字段及格式
     * @param array $dataInfo 数据信息（key,value必须与$fieldData字段对应）
     * @param array $userInfo 用户信息（用于发送邮件,不为空则为邮件方式发送）
     * @throws PHPExcel_Exception
     * @throws PHPExcel_Reader_Exception
     * @throws PHPExcel_Writer_Exception
     * fieldData.*.pColumn    = 列          (string)
     * fieldData.*.pValue     = 值          (string)
     * fieldData.*.width      = 列宽,默认20  (string)
     * fieldData.*.select     = 下拉列表      (array)
     * fieldData.*.comment    = 备注         (string)
     * fieldData.*.fontColor  = 表头字体颜色   (string)
     * fieldData.*.dateFormat = 字体格式      (string)
     * fieldData.*.dateType   = 单元格格式    (string)
     * $fieldData = [
     *      [
     *           'pColumn' => 'A',
     *           'pValue'  => '外观颜色',
     *           'width'   => 20,
     *           'comment' => "多个颜色：多个以英文逗号隔开，如\n黑色,白色",
     *           'fontColor' => "FFFFFF",
     *      ], [
     *           'pColumn' => 'B',
     *           'pValue'  => '金额',
     *           'width'   => 20,
     *           'dateType' => PHPExcel_Cell_DataType::TYPE_STRING,
     *           'dateFormat' => "0.00",
     *      ], [
     *           'pColumn' => 'C',
     *           'pValue'  => '变速箱类型',
     *           'width'   => 20,
     *           'select'  => [
     *                   '手动变速箱', '自动变速箱', '手自一体变速箱', '无极变速箱', '双离合变速箱',
     *               ],
     *      ],
     * ];
     *
     * $dataInfo = [
     *      [
     *          '红色'，       //对应 A:外观颜色
     *          '100',        //对应 B:金额
     *          '手动变速箱',   //对应 C:变速箱类型 (值需在select列表里)
     *      ], [
     *          '白色'，
     *          '999',
     *          '双离合变速箱',
     *      ],
     * ];
     */
    public function handle(string $title, array $fieldData, array $dataInfo = [], array $userInfo = [])
    {
        ini_set('memory_limit','4096M');
        $this->title = $title;
        $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
        $cacheSettings = array('memoryCacheSize'=>'15MB');
        PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

        $objSheet = $this->getHandle();
        $this->setHeader($objSheet, $fieldData);
        $this->setData($objSheet, $fieldData, $dataInfo);
        empty($userInfo) ? $this->outPut() : (new SendEmail())->handle($userInfo, [
            'title' => $title,
            'content' => "您导出的数据为 $title 请及时下载",
            'attachment' => $this->saveFile()
        ]);
    }

    /**
     * 返回工作表操作句柄
     * @return PHPExcel_Worksheet 工作表操作句柄
     * @throws PHPExcel_Exception
     */
    protected function getHandle(): PHPExcel_Worksheet
    {
        $this->objExcel = new PHPExcel();
        return $this->objExcel->getActiveSheet();
    }

    /**
     * 设置表头
     * @param PHPExcel_Worksheet $objSheet 工作表句柄
     * @param array $fieldData 表头字段及格式
     * @throws PHPExcel_Exception
     */
    protected function setHeader(PHPExcel_Worksheet $objSheet, array $fieldData)
    {
        //设置sheet名
        $objSheet->setTitle($this->title);
        //所有单元格横向居中
        $this->objExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //所有单元格纵向居中
        $this->objExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        //设置表头，行宽
        $pCoordinate = '';
        $isFontColor = false;
        foreach ($fieldData as $fieldDatum) {
            $pCoordinate = $fieldDatum['pColumn'] . '1';
            $objSheet->setCellValue($pCoordinate, $fieldDatum['pValue']);
            //设置行宽
            $objSheet->getColumnDimension($fieldDatum['pColumn'])->setWidth($fieldDatum['width'] ?? 20);
            if (!empty($fieldDatum['comment'])) {
                $objSheet->getComment($pCoordinate)->getText()->createTextRun($fieldDatum['comment']);
            }
            if (!empty($fieldDatum['fontColor'])) {
                $objSheet->getStyle($pCoordinate)->getFont()->getColor()->setRGB($fieldDatum['fontColor']);
                $isFontColor = true;
            }
            if (!empty($fieldDatum['dateFormat'])) {
                $objSheet->getStyle($fieldDatum['pColumn'])->getNumberFormat()->setFormatCode($fieldDatum['dateFormat']);
            }
            /*设置下拉*/
            if (!empty($fieldDatum['select'])) {
                $selectStr = implode(',', $fieldDatum['select']);
                foreach ($fieldDatum['select'] as $key => $val) {
                    $objSheet->getCell($fieldDatum['pColumn'] . ($key + 2))->getDataValidation()
                        ->setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
                        ->setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
                        ->setAllowBlank(false)
                        ->setShowInputMessage(true)
                        ->setShowErrorMessage(true)
                        ->setShowDropDown(true)
                        ->setErrorTitle('输入的值有误')
                        ->setError('您输入的值不在下拉框列表内（请不要自己填写，应在下拉列表中选择）')
                        ->setPromptTitle()
                        ->setPrompt()
                        ->setFormula1('"' . $selectStr . '"');
                }
            }
        }

        if (!$isFontColor) {
            //表头字体颜色
            $objSheet->getStyle('A1:' . $pCoordinate)->getFont()->getColor()->setRGB('FFFFFF');
            //表头背景颜色
            $objSheet->getStyle('A1:' . $pCoordinate)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('595959');
        }
        //设置表头字体大小和加粗
        $objSheet->getStyle('A1:' . $pCoordinate)->getFont()->setSize(11)->setBold(true);
        //表头填充样式
        $objSheet->getStyle('A1:' . $pCoordinate)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
    }

    /**
     * 设置数据
     * @param PHPExcel_Worksheet $objSheet 工作表句柄
     * @param array $fieldData 表头字段及格式
     * @param array $dataInfo 数据信息
     */
    protected function setData(PHPExcel_Worksheet $objSheet, array $fieldData, array $dataInfo): void
    {
        if (empty($dataInfo)) {
            return;
        }

        foreach ($dataInfo as $key => $value) {
            $key += 2;
            foreach ($fieldData as $fieldKey => $fieldDatum) {
                if (!empty($fieldDatum['dateType'])) {
                    $objSheet->setCellValueExplicit($fieldDatum['pColumn'] . $key, $value[$fieldKey], $fieldDatum['dateType']);
                    continue;
                }
                $objSheet->setCellValue($fieldDatum['pColumn'] . $key, $value[$fieldKey]);
            }
        }
    }

    /**
     * @throws PHPExcel_Reader_Exception
     * @throws PHPExcel_Writer_Exception
     */
    protected function outPut()
    {
        $fileName = $this->title . date('YmdHis ') . '.xlsx';
        $objWriter = PHPExcel_IOFactory::createWriter($this->objExcel, 'Excel2007');
        ob_start();
        $objWriter->save('php://output');
        //设置header头
        $res = response()->getSwooleResponse();
        $res->header('Content-Type', 'application/vnd.ms-excel');
        $res->header('Content-Disposition', 'attachment;filename=' . $fileName);
        $res->header('Cache-Control', 'max-age=0');
        $res->write(ob_get_clean());
    }

    /**
     * 保存文件
     * @return string
     */
    protected function saveFile(): string
    {
        if (!file_exists($this->filePath)) {
            mkdir($this->filePath, 0777, true);
        }

        $path = $this->filePath . $this->title . '.xlsx';
        try {
            $objWriter = PHPExcel_IOFactory::createWriter($this->objExcel, 'Excel2007');
            $objWriter->save($path);
            return $path;
        } catch (Exception $e) {
            Log::error(date("Y-m-d H:i:s") . '--' . $path . '--文件保存失败', [$e->getMessage(), $e->getFile(), $e->getLine()]);
            return false;
        }
    }

    /**
     * 数字生成字母，1=A开始
     * @param int $num
     * @return string
     */
    public function numToExcelLetter(int $num): string
    {
        $result = '';
        while ($num > 0) {
            $mod = $num % 26;
            $num = (int)($num / 26);
            if ($mod == 0) {
                $num--;
                $temp = 'Z' . $result;
            } else {
                $temp = chr(64 + $mod) . $result;
            }
            $result = $temp;
        }

        return $result;
    }
}