记tp5.1使用composer PhpOffice的xlsx表格文件导入数据库

在项目环境下
composer require phpoffice/phpspreadsheet
在项目中引用
use PhpOffice\PhpSpreadsheet\IOFactory;

 

下面是 上传xlsx导入数据库方法

    public function getExcel(){
        $user_id = input('user_id',1);     
        //ajax 文件跨域 验证
        $request_method = $_SERVER['REQUEST_METHOD'];
        if ($request_method === 'OPTIONS') {
            header('Access-Control-Allow-Origin:*');
            header('Access-Control-Allow-Credentials:true');
            header('Access-Control-Allow-Methods:GET, POST, OPTIONS');
            header('Access-Control-Max-Age:1728000');
            header('Content-Type:text/plain charset=UTF-8');
            header('Content-Length: 0',true);
            header('status: 204');
            header('HTTP/1.0 204 No Content');
        }

        $file = request()->file('file');
        if(!$file){
             return json(['errcode'=>-1,'errmsg'=>'请上传文件']); 
        }

        $info = $file->validate(['size'=>5*1024*1024,'ext'=>'xlsx,xls'])->move('uploads/excel/'.$user_id);
        if(!$info){
           return json(['errcode'=>-1,'errmsg'=>'请返回xlsx,xls格式表格','fileerr'=>$file->getError()]); 
        }
        //判断文件类型
        $suffix = $info->getExtension();
        
        if($suffix=="xlsx"){
            $objReader = IOFactory::createReader('Xlsx');
        }else{
            $objReader = IOFactory::createReader('Xls');
        }
        
        $objPHPExcel = $objReader->load(Env::get('root_path').'public/uploads/excel/'.$user_id.'/'.$info->getSaveName());
        $extension = strtolower( pathinfo($info->getSaveName(), PATHINFO_EXTENSION) );

        //读取默认工作表
        $worksheet = $objPHPExcel->getSheet(0);
        //取得一共有多少行
        $allRow = $worksheet->getHighestRow();
        $data = [];
        
        if($allRow > 101){
            return json(['errcode'=>-1,'errmsg'=>'最多一百条']); 
        }
        
        for ($i = 2; $i <= $allRow; $i++)
        {
            $data = array();
            $goods_data = array();
            $data['order_sn_third'] = $objPHPExcel->getActiveSheet()->getCell('A'.$i)->getValue();
            $goods_data['goods_name'] = $objPHPExcel->getActiveSheet()->getCell('B'.$i)->getValue();
            $goods_data['goods_sn'] = $objPHPExcel->getActiveSheet()->getCell('C'.$i)->getValue();
            $goods_data['goods_num'] = $objPHPExcel->getActiveSheet()->getCell('D'.$i)->getValue();
            
            $data['consignee'] = $objPHPExcel->getActiveSheet()->getCell('E'.$i)->getValue();
            $data['mobile'] = $objPHPExcel->getActiveSheet()->getCell('F'.$i)->getValue();
            $data['province'] = $objPHPExcel->getActiveSheet()->getCell('G'.$i)->getValue();
            $data['city'] = $objPHPExcel->getActiveSheet()->getCell('H'.$i)->getValue();
            $data['district'] = $objPHPExcel->getActiveSheet()->getCell('I'.$i)->getValue();
            $data['address'] = $objPHPExcel->getActiveSheet()->getCell('J'.$i)->getValue();
            $data['user_note'] = $objPHPExcel->getActiveSheet()->getCell('K'.$i)->getValue();
            
            
            //防止出现空白Excel导致mysql报错，对数据做下判断
            if(empty($data['order_sn_third']) && empty($data['goods_sn'])){
                //跳出循环
                break;
            }
            $data['create_time'] = time();
            $data['status'] = 1;
            $data['user_id'] = $user_id;
            $data['order_sn'] = date('Ymd').substr(implode(NULL, array_map('ord', str_split(substr(uniqid(), 7, 13), 1))), 0, 8);
            
            //插入数据库
            $order_id = db('order_info')->insertGetId($data);
            $goods_data['order_id'] = $order_id;
            $res = db('order_goods')->insert($goods_data);  
        }
        
        return json(['errcode'=>0,'errmsg'=>'导入完成']); 
        
        
    }