new_api/app/libs/helpers/Excel.php

<?php


namespace App\libs\helpers;


use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing;
class Excel
{
    /**
     * 初始实例化
     * @var Spreadsheet|null
     */
    private $excel = null;


    /**
     * 导出暂存地址
     * @var string
     */
    private $path  = '';


    /**
     * 定义表格格子   最高到AG
     * @var string[]
     */
    private $ziMu = ['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','AA','AB','AC','AD','AE','AF','AG'];


    /**
     * 标题
     * @var array
     */
    private $title = [];


    /**
     * 导入数据源
     * @var array
     */
    private $data  = [];


    /**
     * 默认域名
     * @var string
     */
    private $defaultDomain = 'https://yxd.wangkuncheng.cn';
    /**
     * 是否自动换行
     * @var bool
     */
    private $setWrapText = true;


    /**
     * 导出表格名称
     * @var string
     */
    private $excelName = '';


    /**
     * 实例化
     * @var Spreadsheet|null
     */
    private $spreadsheet = null;


    /**
     * 是否输出导出地址
     *
     * @var bool
     */
    private $isExportPath = false;


    /**
     * 导出的地址
     * @var string
     */
    public $exportPath = '';


    /**
     * 项目编号
     * @var string
     */
    private $projectName = '';


    /**
     * 项目导出文件暂存地址
     * @var string
     */
    private $projectExcelPath = '';


    /**
     * 是否开本地地址
     *
     * @var bool
     */
    private $isLocalhost = false;

    /**
     * 格式是否为图片
     *
     * @var array
     */
    private $ceilImage   = [];


    /**
     * 格式图片样式
     *
     * @var array
     */
    private $ceilImageStyle = [];
    /**
     * 初始化
     *
     * @param string $project       项目文件名   例如
     * @param string $path          路径地址
     * @param array $data           表数据
     * @param array $title          表头
     * Excel constructor.
     */
    public function __construct(string $project = '', string $path = '', array $data = [], array $title = [])
    {
        $this->spreadsheet          = new Spreadsheet();
        $this->excel                = $this->spreadsheet->getActiveSheet();
        $this->path                 = $_SERVER['DOCUMENT_ROOT'].'/'.$project.'/'.$path;
        $this->projectName          = $project;
        $this->projectExcelPath     = $path;
        $this->data                 = $data;
        $this->title                = $title;
//        exec("chmod 777 ".$this->path,$output,$status);
    }


    /**
     * 设置表格名称
     *
     * @param string $excelName
     * @return $this
     */
    public function setExcelName(string $excelName = ''){
        $this->excelName   = $excelName;
        return $this;
    }


    /**
     * 设置是否导出地址
     *
     * @param bool $isExportPath
     */
    public function setIsExportPath(bool $isExportPath)
    {
        $this->isExportPath = $isExportPath;
        return $this;
    }


    /**
     * 设置是否开启本地
     *
     * @param bool $isLocalhost   是否开启  true   false
     * @param string $localhostPath   本地域名地址  如果域名下一级目录不能直接直接对应项目名称 请传入域名下的相对路径
     */
    public function setIsLocalhost(bool $isLocalhost ,string $localhostPath)
    {
        $this->defaultDomain = $localhostPath;
        $this->isLocalhost   = $isLocalhost;
        return $this;
    }


    /**
     * 设置边框
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    public function borderThin(){
        $this->excel->getStyle($this->getRange())->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        return $this;
    }


    /**
     * 设置自动换行
     *
     * @param bool $wrap
     * @return $this
     */
    public function setAutoWrapText(bool $wrap = true){
        $this->setWrapText = $wrap;
        return $this;
    }


    /**
     * 设置行宽
     * 数据示例：['rowName'=>'A','value'=>50]
     *
     * @param array $data
     * @return $this
     */
    public function setRowWidth(array $data = []){
        foreach ($data as $value){
            $this->excel->getColumnDimension($value['rowName'])->setWidth($value['value']);
        }
        return $this;
    }


    /**
     * 设置行高：两种形式
     * 1.整数：则默认设置全部行高
     * 2.数组：单独设置每一行行高
     *        数组格式[30,30] 一维数组形式传入值即可 如果传入数组count小于data数据count   那么剩余的  默认30行高
     *
     * @param int $height
     * @return $this
     */
    public function setRowHeight($height = 30){
        $index = 2;
        for ($i = 0; $i < count($this->data); $i++){
            $this->excel->getRowDimension($index)->setRowHeight(is_array($height) ? ($height[$i] ?? 30) : $height);
            $index++;
        }
        return $this;
    }


    /**
     * 设置全局居中
     *
     * @return $this
     */
    public function setCenter(){
        $this->excel->getStyle($this->getRange())->applyFromArray([
            'alignment' => [
                'horizontal'    => Alignment::HORIZONTAL_CENTER,// 水平居中
                'vertical'      => Alignment::VERTICAL_CENTER //垂直居中
            ]
        ]);
        return $this;
    }


    /**
     * 保存并导出
     *
     * @param array $data
     * @return $this
     */
    public function save(){
        $this->setWrap();
        $this->setCellValue();
        $this->exportData();
        $writer = new Xlsx($this->spreadsheet);
        if (empty($this->excelName)){
            $this->excelName = date('Y-m-d');
        }
        $writer->save($this->path.'/'.$this->excelName . '.xlsx');
        if ($this->isExportPath){
            $this->exportPath = $this->defaultDomain.'/'.$this->projectName.'/'.$this->projectExcelPath.'/'.$this->excelName . '.xlsx';
        }else{
            header("Location: ".$this->defaultDomain.'/'.$this->projectName.'/'.$this->projectExcelPath.'/'.$this->excelName . '.xlsx');
        }
        return $this;
    }


    /**
     * 创建图片
     * @param string $ceil
     * @param string $url
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    private function setImage(string $url = '',string $ceil = ''){
        if (!empty($url)){
//            $src = imagecreatefromstring(file_get_contents($url));
            $exp = explode('/',$url);
//            $url = '/www/wwwroot/1001/1001-staff/uploads/'.$exp[5].$exp[6];
//            $url = 'E:\SiWeiDingZhi\1001\1001-staff\uploads/'.$exp[5].'/'.$exp[6];
            $url = '/www/wwwroot/1001/1001-staff/uploads/'.$exp[5].'/'.$exp[6];
            $src = imagecreatefromstring(file_get_contents($url));
            list($src_w, $src_h) = getimagesize($url);
            $img = imagecreatetruecolor($src_w,$src_h);
            imagecopymerge($img,$src,0,0,0,0,$src_w,$src_h,100);
            $drawing = new MemoryDrawing();
            $drawing->setName($url);
            $drawing->setDescription($url);
            $drawing->setCoordinates($ceil);
            $drawing->setImageResource($img);
            $drawing->setHeight(80);
            $drawing->setOffsetX(10);
            $drawing->setOffsetY(10);
            $drawing->setRenderingFunction(MemoryDrawing::RENDERING_JPEG);
            $drawing->setMimeType(MemoryDrawing::MIMETYPE_DEFAULT);
            if (!empty($this->ceilImageStyle)){
                $ceilText = substr($ceil,0,1);
                if (!empty($this->ceilImageStyle[$ceilText])){
                    $drawing->setHeight($this->ceilImageStyle[$ceilText]['height']);
                    $drawing->setWidth($this->ceilImageStyle[$ceilText]['width']);
                    $drawing->setOffsetX($this->ceilImageStyle[$ceilText]['OffsetX']);
                    $drawing->setOffsetY($this->ceilImageStyle[$ceilText]['OffsetY']);
                }
            }
            $drawing->setWorksheet($this->spreadsheet->getActiveSheet());
        }
    }


    /**
     * 设置格子是否导出为图片
     *
     * @param array $images
     * @return $this
     */
    public function setExportCeilImage(array $images = []){

        if (count($images) != count($images,1)){
            foreach ($images ?? [] as $v){
                $this->ceilImage[] =  key($images);
                next($images);
            }
            $this->ceilImageStyle = $images;
        }else{
            $this->ceilImage = $images;
        }
        return $this;
    }


    /**
     * 设置导出数据
     *
     */
    private function exportData(){
        $index = 2;
        $keyName = $this->getArrayKey();
        for ($i = 0;$i < count($this->data);$i++){
            for ($j = 0;$j < count($this->title);$j++){
                if (in_array($this->ziMu[$j],$this->ceilImage)){
                    if (!empty($this->data[$i][$keyName[$j]])){

                        $this->setImage($this->data[$i][$keyName[$j]],$this->ziMu[$j] . $index);
                    }
                }else{
                    $this->excel->setCellValue($this->ziMu[$j] . $index, $this->data[$i][$keyName[$j]]);
                }
            }
            $index++;
        }
    }


    /**
     * 获取数组指针
     *
     * @return array
     */
    private function getArrayKey(){
        $result = [];
        if (empty($this->data)){
            throw new \think\Exception('传入表数据为空', 40004);
        }
        for ($i=0,$len=count($this->data[0]); $i<$len; $i++) {
            $result[] =  key($this->data[0]);
            next($this->data[0]);
        }
        return $result;
    }


    /**
     * 设置自动换行
     *
     */
    private function setWrap(){
        if ($this->setWrapText){
            $this->excel->getStyle($this->getRange())->getAlignment()->setWrapText(true);
        }
    }


    /**
     * 获取数据范围值
     *
     * @return string
     */
    private function getRange(){
        if(empty($this->title) || empty($this->data)){
            throw new \think\Exception('初始化请传入数据', 40006);
        }
        return 'A1:'.$this->ziMu[count($this->title) - 1].(count($this->data) + 1);
    }


    /**
     * 设置表头
     *
     */
    private function setCellValue(){
        if (empty($this->title)){
            throw new \think\Exception('传入表头为空', 40005);
        }
        foreach ($this->title as $key=>$value){
            $this->excel->setCellValue($this->ziMu[$key].'1', $this->title[$key]);
        }
    }
}