<?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); $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 || $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 (!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 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 */ 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; } }