记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'=>'导入完成']);

    }