ExcelImporter.php

<?php

/**
 * Created by PhpStorm.
 * User: zero
 * Date: 2020/6/1
 * Time: 14:06
 * Description:
 */

namespace Meibuyu\Micro\Tools;

use Hyperf\Contract\ConfigInterface;
use Meibuyu\Micro\Exceptions\HttpResponseException;
use Meibuyu\Micro\Manager\UploadManager;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class ExcelImporter
{
    /**
     * @var ConfigInterface
     */
    private $config;

    /**
     * @var Spreadsheet|null
     */
    private $reader = null;

    /**
     * @var Worksheet|null
     */
    private $sheet = null;

    private $rootPath;
    private $sheetIndex = 0;
    private $filePath;
    private $fileType;
    private $highestRow = 0; // 总行数
    private $highestColumn = 0; // 最后一列字符
    private $errorColumn;

    /**
     * Importer constructor.
     * @param $file
     * @param int $sheetIndex
     * @throws \Meibuyu\Micro\Exceptions\HttpResponseException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    public function __construct($file, $sheetIndex = 0)
    {
        $this->config = container(ConfigInterface::class);
        $this->fileType = ucfirst($file->getExtension());
        $this->rootPath = $this->config->get('server.settings.document_root', BASE_PATH . '/public');
        $path = UploadManager::uploadExcelGetRealPath($file, ['temp' => true]);
        $this->reader = IOFactory::createReaderForFile($path)->load($path);
        $this->filePath = $path;
        $this->sheet = $this->reader->getSheet($sheetIndex);
        $this->sheetIndex = $sheetIndex;
        $this->highestRow = $this->sheet->getHighestRow();
        $this->highestColumn = $this->sheet->getHighestColumn();
        $this->highestColumn++;
    }

    /**
     * 判断excel表中数据不能为空
     * @param $least
     * @return bool
     * @throws HttpResponseException
     */
    public function checkEmpty($least)
    {
        if ($this->highestRow < $least) {
            return false;
        } else {
            return true;
        }
    }

    /**
     * 获取表格字符串
     * @param string $startCol 开始的列,默认为A
     * @param null $endCol 结束的列,默认为表格已有数据的最后一列
     * @return string
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    public function getHeaderStr($startCol = 'A', $endCol = null): string
    {
        $str = '';
        $endCol = $endCol ? $endCol : $this->highestColumn;
        for ($i = $startCol; $i != $endCol; $i++) {
            $str .= $this->sheet->getCell($i . 1)->getFormattedValue();
        }
        return $str;
    }

    /**
     * 验证表头
     * @param string $headerStr 对比的表头字符串
     * @param string $startCol 开始的列,默认为A
     * @param null $endCol 结束的列,默认为表格已有数据的最后一列
     * @return bool
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    public function checkHeaderStr(string $headerStr, $startCol = 'A', $endCol = null)
    {
        $excelField = $this->getHeaderStr($startCol, $endCol);
        if (md5($excelField) == md5($headerStr)) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 检测必填数据
     * @param $requires
     * @param $errorCount
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    public function checkRequire($requires, &$errorCount)
    {
        foreach ($requires as $k => $v) {
            if ($v == '0') {
                continue;
            }
            if (!$v || $v === '') {
                $this->setErrorMessage($k . "不能为空", $errorCount);
                return false;
            }
        }
        return true;
    }

    /**
     * 检测数字类型数据
     * @param $data
     * @param $errorCount
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    public function checkNumeric($data, &$errorCount)
    {
        foreach ($data as $k => $v) {
            if (!$v || $v === '' || $v == '0') {
                continue;
            } else {
                if (!is_numeric($v)) {
                    $this->setErrorMessage("{$k}只能是数字", $errorCount);
                    return false;
                }
            }
        }
        return true;
    }

    /**
     * 设置错误标识
     * @param $p
     * @param $message
     * @param $count
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    public function setErrorMessage($message, &$count)
    {
        $count++;
        $this->sheet->getCell($this->errorColumn)->setValue($message);
        $this->sheet->getStyle($this->errorColumn)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);
    }

    /**
     * 保存
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function save()
    {
        $this->reader->setActiveSheetIndex(0);
        IOFactory::createWriter($this->reader, $this->fileType)->save($this->filePath);
    }

    /**
     * 返回当前激化的sheet表
     * @return null|Worksheet
     */
    public function getSheet()
    {
        return $this->sheet;
    }

    /**
     * 返回原生的PhpOffice Reader 手动处理数据
     * @return null|Spreadsheet
     */
    public function getReader()
    {
        return $this->reader;
    }

    /**
     * 获取总行数
     * @return int
     */
    public function getHighestRow()
    {
        return $this->highestRow;
    }

    /**
     * 获取总列数
     * @return int
     */
    public function getHighestColumn()
    {
        return $this->highestColumn;
    }

    /**
     * 返回文件本地地址
     * @return string
     */
    public function getFilePath()
    {
        return $this->filePath;
    }

    /**
     * 返回文件网络地址
     * @return string
     */
    public function getFileUrl()
    {
        return $this->config->get('app_domain') . str_replace($this->rootPath, '', $this->filePath);

    }

    public function setErrorColumn($column)
    {
        $this->errorColumn = $column;
    }

    public function getErrorColumn()
    {
        return $this->errorColumn;
    }

    /**
     * 对比不同的数据,返回是否有不同
     * @param array $list
     * @param array $data
     * @return bool
     * @see check_diff_val()
     * @deprecated 此方法废弃,直接调用check_diff_val()
     */
    public static function checkDiffVal(array $list, array $data)
    {
        foreach ($list as $key => $val) {
            if (isset($data[$key]) && $data[$key]) {
                if (is_array($val)) {
                    if (self::checkDiffVal($val, $data[$key])) {
                        return true;
                    }
                } else {
                    if ($list[$key] != $data[$key]) {
                        return true;
                    }
                }
            }
        }
        return false;
    }

}