laravel excel导入数据(文字和图片)

本文详细介绍了如何在Laravel项目中利用库进行Excel数据导入,并且涵盖了处理包含图片在内的复杂数据的步骤,为高效的数据管理工作提供了解决方案。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

//转化EXCEL表格行
    public function getalphnum($char){
        $array=array('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');
        $len=strlen($char);
        $sum=0;
        for($i=0;$i<$len;$i++){
            $index=array_search($char[$i],$array);
            $sum+=($index+1)*pow(26,$len-$i-1);
        }
        return $sum;
    }

    /**
     * 商品导入
     * @author : liumingxing
     */
    public function goodsImport(Request $request){

        $file = $request->file('file');
        $originalName = $file->getClientOriginalName(); // 文件原名
        $ext = $file->getClientOriginalExtension();     // 扩展名
        $realPath = $file->getRealPath();               //临时文件的绝对路径
        $type = $file->getClientMimeType();
        $filename = date('Y-m-d-H-i-s') . '-' . uniqid() . '.' . $ext;
        $bool = Storage::disk('uploads')->put($filename, file_get_contents($realPath));
        $filePath = 'public/uploads/'.iconv('UTF-8', 'GBK', $filename);

        $data = [];
        Excel::load($filePath, function ($reader) use(&$data){
            $reader = $reader->getSheet(0);
            $data = $reader->toArray();
        });

        //图片上传
        $imageFile = $request->file('file');
        $excel = \PHPExcel_IOFactory::load($imageFile);

        $path = base_path().'/public/uploads/';
        $drawing  = new \PHPExcel_Writer_Excel2007_Drawing();
        $drawingHashTable = new \PHPExcel_HashTable();

        $drawingHashTable->addFromSource($drawing->allDrawings($excel));

        for ($i = 0; $i < $drawingHashTable->count(); ++$i){
            $memoryDrawing = $drawingHashTable->getByIndex($i);

            if($memoryDrawing instanceof \PHPExcel_Worksheet_MemoryDrawing){
                $filePath = 'images/'.date('Y').'/'.date('m').'/'.date('d').'/'.$memoryDrawing->getCoordinates().'-'.$memoryDrawing->getHashCode().'.jpg';
                $filenameImg = $path .$filePath;
                //将图片存到指定的目录
                imagejpeg($memoryDrawing->getImageResource(),$filenameImg);
                //获取该图片所在的单元格
                $cell = $memoryDrawing->getWorksheet()->getCell($memoryDrawing->getCoordinates());
                $string = $memoryDrawing->getCoordinates();
                $coordinate = \PHPExcel_Cell::coordinateFromString($string);
            }
            $data[$coordinate[1] - 1][$this->getalphnum($coordinate[0]) - 1] = $filePath;
        }

        $admin_id = Admin::user()->{'id'};          //创建id
        $category_id = 8;                           //分类id
        $now_time = date('Y-m-d H:i:s');     //时间
        try{
            //事务
            DB::beginTransaction();

            for ($i = 1;$i <= count($data);$i++){
                if(empty($data[$i]) || empty($data[$i][0])){
                    continue;
                }
                //判断用户是否都填写
                if(!isset($data[$i][0]) || empty($data[$i][0])){
                    throw new \Exception('第'.($i+1).'行不能'.$data[0][0].'字段不能为空,请重新上传');
                }

                if(!isset($data[$i][1]) || empty($data[$i][1])){
                    throw new \Exception('第'.($i+1).'行不能'.$data[0][1].'字段不能为空,请重新上传');
                }

                if(!isset($data[$i][3]) || empty($data[$i][3])){
                    throw new \Exception('第'.($i+1).'行不能'.$data[0][3].'字段不能为空,请重新上传');
                }

                if(!isset($data[$i][4]) || empty($data[$i][4])){
                    throw new \Exception('第'.($i+1).'行不能'.$data[0][4].'字段不能为空,请重新上传');
                }

                //查询是否存在
                $goods = Goods::where([
                    'title' => $data[$i][0]
                ])->first();

                if(empty($goods)){
                    //新增
                    $temp = [
                        'title'     => $data[$i][0],
                        'price'     => $data[$i][1],
                        'img'       => $data[$i][2],
                        'describe'  => $data[$i][3],
                        'sort'      => $data[$i][4],
                        'created_id'=> $admin_id,
                        'updated_id'=> $admin_id,
                        'created_at'=> $now_time,
                        'updated_at'=> $now_time,
                        'is_putaway'=> 1,
                        'category_id'=> $category_id
                    ];
                    $res = DB::table('i_goods')->insertGetId($temp);
                    if(!$res){
                        throw new \Exception($data[$i][0].'新增失败,请重新上传新增');
                    }
                }else{
                    //更新
                    $temp = [
                        'price'     => $data[$i][1],
                        'img'       => $data[$i][2],
                        'describe'  => $data[$i][3],
                        'sort'      => $data[$i][4],
                        'updated_id'=> $admin_id,
                        'updated_at'=> $now_time,
                        'is_putaway'=> 1,
                    ];
                    $res = DB::table('i_goods')
                        ->where([
                            'title' => $data[$i][0]
                        ])
                        ->update($temp);
                    if(!$res){
                        throw new \Exception($data[$i][0].'更新失败,请重新上传更新');
                    }
                }
            }
            DB::commit();
            Storage::disk('uploads')->delete($filename);        //删除上传的暂存文件
            return response()->json(['code' => 200, 'message' => '文件上传成功', 'data' => []]);
        }catch (\Exception $e){
            $error = $e->getMessage();
            DB::rollBack();
            return response()->json(['code' => 300, 'message' => '文件上传失败,请重新上传', 'data' => []]);
        }
    }

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值