hb/server/app/libs/helpers/Excel.php

<?php

namespace App\libs\helpers;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing;

class Excel
{
    /**
     * 初始实例化.
     *
     * @var Spreadsheet|null
     */
    private $excel;

    /**
     * 导出暂存地址
     *
     * @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 = '';
    /**
     * 是否自动换行.
     *
     * @var bool
     */
    private $setWrapText = true;

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

    /**
     * 保存导出图片的配置.
     *
     * @var null
     */
    private $images;
    /**
     * 实例化.
     *
     * @var Spreadsheet|null
     */
    private $spreadsheet;

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

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

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

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

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

    /**
     * 格式图片样式.
     *
     * @var array
     */
    private $ceilImageStyle = [];
    /**
     * 设置行高.
     *
     * @var int
     */
    public $height = -1;

    /**
     * 初始化.
     */
    public function __construct(array $data = [], array $title = [], string $path = 'uploads')
    {
        $this->spreadsheet      = new Spreadsheet();
        $this->excel            = $this->spreadsheet->getActiveSheet();
        $this->projectName      = $this->getProjectName();
        $this->path             = $_SERVER['DOCUMENT_ROOT'] . '/' . $this->getProjectExcelPath() . '/' . $path;
        $this->projectExcelPath = $path;
        $this->data             = $data;
        $this->title            = $title;
        $this->getUrl();
        if ($this->checkWindows()) {
            if (!is_dir($this->path)) {
                mkdir($this->path, 0777, true);
                chmod($this->path, 0777);
            }
        }
    }

    /**
     * 获取项目多字段地址
     */
    private function getProjectExcelPath(): string
    {
        $url   = $_SERVER['REQUEST_URI'];
        $parts = explode('/', $url);
        $keys  = array_keys($parts);
        $key   = array_filter($parts, function ($value) {
            return false !== strpos($value, 'index.php');
        });
        $keySa = array_keys($key);
        $key   = count($keys) - ($keySa[0] - 1);

        return implode('/', array_slice($parts, 1, $keys[count($keys) - $key]));
    }

    /**
     * 设置自动行高.
     *
     * @return void
     */
    private function setAutoHeight()
    {
        for ($i = 0; $i < count($this->data); $i++) {
            $this->excel->getRowDimension($i + 2)->setRowHeight($this->height);
        }
    }

    /**
     * 获取域名地址
     *
     * @return void
     */
    private function getUrl()
    {
        $host                = $_SERVER['HTTP_HOST'];
        $scriptName          = $_SERVER['SCRIPT_NAME'];
        $this->defaultDomain = isset($_SERVER['HTTPS']) && 'on' === $_SERVER['HTTPS'] ? 'https://' : 'http://' . $host . substr($scriptName, 0, strrpos($scriptName, '/') + 1);
    }

    /**
     * 设置自动行宽.
     *
     * @return void
     */
    private function setAutoWidth()
    {
        foreach ($this->title ?? [] as $key => $value) {
            $this->excel->getColumnDimension($this->ziMu[$key])->setAutoSize(true);   // 全局自动宽度
            $columnDimension = $this->excel->getColumnDimension($this->ziMu[$key]);
            $maxWidth        = 50; // 自定义最大宽度
            if ($columnDimension->getWidth() > $maxWidth) {
                $columnDimension->setWidth($maxWidth);
            }
        }
    }

    /**
     * 获取项目名称.
     *
     * @return false|string
     */
    private function getProjectName()
    {
        $route   = $_SERVER['REQUEST_URI'];
        $pattern = '/\/(\w+)\/index\.php/';
        preg_match($pattern, $route, $matches);
        if (isset($matches[1])) {
            return $matches[1];
        }

        return false;
    }

    /**
     * 验证是否Windows系统
     */
    private function checkWindows(): bool
    {
        $os = PHP_OS;
        if ('WIN' === strtoupper(substr($os, 0, 3))) {
            return true;
        }

        return false;
    }

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

        return $this;
    }

    /**
     * 设置格子颜色.
     *
     * @param int    $height //表头高度
     * @param string $color  //表头颜色
     * @param bool   $isBold //表头是否加粗
     *
     * @return $this
     *
     * @throws \think\Exception
     */
    public function setHeaderColor(int $height = 20, string $color = 'FFFF00', bool $isBold = true): Excel
    {
        $this->excel->getStyle($this->getRange(1))->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB($color);
        $this->excel->getStyle($this->getRange(1))->getFont()->setBold($isBold);
        $this->excel->getRowDimension(1)->setRowHeight($height);

        return $this;
    }

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

        return $this;
    }

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

        return $this;
    }

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

        return $this;
    }

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

        return $this;
    }

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

    /**
     * 保存并导出.
     *
     * @return $this
     */
    public function save(): Excel
    {
        $this->setCenter();
        $this->setWrap();
        $this->setCellValue();
        $this->setAutoWidth();
        $this->setAutoHeight();
        $this->exportData();
        $writer = new Xlsx($this->spreadsheet);
        if (empty($this->excelName)) {
            $this->excelName = date('YmdHis');
        }
        $writer->save($this->path . '/' . $this->excelName . '.xlsx');
        if ($this->isExportPath) {
            $this->exportPath = $this->defaultDomain . '/' . $this->projectExcelPath . '/' . $this->excelName . '.xlsx';
        } else {
            header('Location: ' . $this->defaultDomain . '/' . $this->projectExcelPath . '/' . $this->excelName . '.xlsx');
        }

        return $this;
    }

    /**
     * 创建图片.
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    private function setImage(string $url = '', string $ceil = '')
    {
        $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());
    }

    /**
     * 设置格子是否导出为图片.
     *
     * @return $this
     */
    public function setExportCeilImage(array $images = []): Excel
    {
        $this->images = $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->excel->getColumnDimension($this->ziMu[$j])->setWidth($this->images[$this->ziMu[$j]]['width']);   // 设置图片宽度
                        $this->excel->getRowDimension($i + 2)->setRowHeight($this->images[$this->ziMu[$j]]['height']);
                        $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++;
        }
    }

    /**
     * 获取数组指针.
     */
    private function getArrayKey(): array
    {
        $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
     *
     * @throws \think\Exception
     */
    private function getRange($type = 0)
    {
        if (empty($this->title) || empty($this->data)) {
            throw new \think\Exception('初始化请传入数据', 40006);
        }
        if (0 == $type) {
            return 'A1:' . $this->ziMu[count($this->title) - 1] . (count($this->data) + 1);
        }

        return 'A1:' . $this->ziMu[count($this->title) - 1] . '1';
    }

    /**
     * 设置表头.
     *
     * @return void
     *
     * @throws \think\Exception
     */
    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]);
        }
    }

    /**
     * 获取Xlsx表格数据.
     *
     * @return array|false
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    public static function getXlsxData($file, array $cell = [])
    {
        if (!$file) {
            return false;
        }
        $reader = IOFactory::createReader('Xlsx');
        $reader->setReadDataOnly(true);
        $spreadsheet = $reader->load($file);
        $worksheet   = $spreadsheet->getActiveSheet();
        $arr         = [];
        $i           = 0;
        $field       = $cell;
        $max         = count($field);
        foreach ($worksheet->getRowIterator() as $row) {
            $k            = 0;
            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(false);
            foreach ($cellIterator as $cell) {
                if ($k == $max) {
                    break;
                }
                $arr[$i][$field[$k]] = $cell->getCalculatedValue();
                $k++;
            }
            $i++;
        }

        return $arr;
    }
}