- A+
首先我们创建抽象工具类Import:
namespace app\common\tool; abstract class Import { private static $instance = []; /** * @return static */ public static function getInstance() { $class = get_called_class(); if(!isset(self::$instance[$class]) || !self::$instance[$class] instanceof $class){ self::$instance[$class] = new static(); } return self::$instance[$class]; } }
然后我们定义一个抽象方法,要求子类必须返回和Excel表格中数据对应的字段
abstract protected function getFieldNames(): array;
实现Excel数据导入:
fileName: Excel 的地址
extraData: 额外的数据,和合并到每一行的数据内
public function import($fileName, $extraData = []) { if (!is_file($fileName)) { throw new ToolException('文件不存在'); } $spreadSheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($fileName); $importData = $this->getImportData($spreadSheet); $importImages = $this->getImportImages($spreadSheet, $importData); if (!empty($importImages)) { foreach ($importImages as $image) { $importData[$image['row']][$image['column']] = $image['image']; } } return $this->formatData($importData, $extraData); }
获取导入的图片数据:
private function getImportImages($spreadsheet) { $importImages = []; foreach ($spreadsheet->getActiveSheet()->getDrawingCollection() as $drawing) { list($startColumn, $startRow) = Coordinate::coordinateFromString($drawing->getCoordinates()); $imageFileName = md5(microtime(true)); $fileName = Upload::getSavePath(strtolower(Request::instance()->controller()), 'img', false, true); if(!is_dir(Upload::$rootPath.DS.$fileName)){ mkdir(Upload::$rootPath.DS.$fileName, 0777, true); } switch ($drawing->getExtension()) { case 'jpg': case 'jpeg': $imageFileName .= '.jpg'; $source = imagecreatefromjpeg($drawing->getPath()); imagejpeg($source, $fileName .DS. $imageFileName); break; case 'gif': $imageFileName .= '.gif'; $source = imagecreatefromgif($drawing->getPath()); imagegif($source, $fileName .DS. $imageFileName); break; case 'png': $imageFileName .= '.png'; $source = imagecreatefrompng($drawing->getPath()); imagepng($source, $fileName.DS. $imageFileName); break; } $startColumn = $this->ABC2decimal($startColumn); $importImages[] = [ 'row' => $startRow - 1, 'column' => $startColumn, 'image' => $fileName .DS. $imageFileName, ]; } return $importImages; } public function ABC2decimal($abc) { $ten = 0; $len = strlen($abc); for ($i = 1; $i <= $len; $i++) { $char = substr($abc, 0 - $i, 1);//反向获取单个字符 $int = ord($char); $ten += ($int - 65) * pow(26, $i - 1); } return $ten; }
获取导入的Excel 数字、文字数据:
private function getImportData($spreadSheet) { return $spreadSheet->getActiveSheet()->toArray(null, true, true, false); }
对应Excel文件内的字段数据并合并入ExtraData:
protected function formatData($importData, $extraData) { $formatData = []; foreach ($importData as $index => $import) { $data = []; if ($index == 0) { continue; } foreach ($this->getFieldNames() as $index => $field) { $data[$field] = isset($import[$index]) ?$import[$index]: ''; } if (!empty($extraData)) { $data = array_merge($extraData, $data); } $formatData[] = $data; } return $formatData; }
我们来实现一个商品Excel导入的功能:
namespace app\common\tool\import; use app\common\tool\Import; class GoodsImport extends Import { protected function getFieldNames():array { return ['brand_name', 'top_cate_name', 'cate_name', 'import_product_name', 'goods_img', 'goods_spec', 'goods_unit', 'sale_price', 'on_sale']; } protected function formatData($importData, $extraData) { $data = parent::formatData($importData, $extraData); foreach ($data as $index => $goods) { $data[$index]['goods_img'] = empty($goods['goods_img']) ? 'static/img/default.png' : $goods['goods_img']; } return $data; } }