PHP 类实现Excel 导入Mysql

   //导入excel
    public function importExcle(Request $request)
    {
      if($request->isPost()){

        if (!empty($_FILES)) {
            $start_time = time();

            if(empty($_FILES['file-2']['name']))
            {
                $this->error('请上传你的文件！');
            }

            $file = request()->file('file-2');
           //print_r($file);die;
            // 移动到框架应用根目录/public/uploads/ 目录下
            $info = $file->validate(['ext' => 'xlsx,xls'])->move('./excle');

            if($info){
                if(empty($info)){
                    $this->error('Excle 导入失败');
                }
                //获取文件路径

                //$filename =  ROOT_PATH.'excle'.DS.$info->getSaveName();
                $filename = './excle/'.$info->getSaveName();



                // 判断截取文件
                $extension = strtolower( pathinfo($filename, PATHINFO_EXTENSION) );

                //区分文件格式
                if($extension == 'xlsx')
                {
                    $objReader =\PHPExcel_IOFactory::createReader('Excel2007');
                    $objPHPExcel = $objReader->load($filename, $encode = 'utf-8');
                } else if($extension == 'xls'){
                    $objReader =\PHPExcel_IOFactory::createReader('Excel5');
                    $objPHPExcel = $objReader->load($filename, $encode = 'utf-8');
                }
                ################################################### 第一种处理成数组形式 #########################################################################
                $excel_array = $objPHPExcel->getsheet(0)->toArray();   //转换为数组格式
                array_shift($excel_array);  //删除第一个数组(标题);
                $res = $this->TbOrderData->orderExcleImport($excel_array);




                ######################################################## 第二种 #####################################################################


                //$objPHPExcel = $objReader->load($filename); //$filename可以是上传的文件，或者是指定的文件
//                $sheet = $objPHPExcel->getSheet(0);
//                $highestRow = $sheet->getHighestRow(); // 取得总行数
//
//                $res = $this->TbOrderData->orderExcleImport_new($highestRow,$objPHPExcel);

                $end_time =  time();
//
                echo  $end_time - $start_time .'s';die;

                if($res == null ){
                     $this->success('导入成功！','tbOrder/index'); 
                }else{
                     $this->error('上传失败！'); 
                }

                // 成功上传后 获取上传信息
                // 输出 jpg
                //var_dump($info->getExtension()) ;
                // 输出 
                //var_dump($info->getSaveName());
                // 输出 42a79759f284b767dfcb2a0197904287.jpg
                //var_dump($info->getFilename());

            }else{
                // 上传失败获取错误信息
                echo $file->getError();
            }
        }

      }else{

         return $this->fetch('importform');

      }

  public function orderExcleImport($data)
    {
        set_time_limit(0);
       // $start_time = time();
       $count =  count($data);
        $start = floor($count/500)*500;
        for ($i=0; $i <$count ; $i++) {
            if($i %500 ==0) {
                $sql="insert into sp_coupon_tb_order (`creattime`,`goods_info`,`goods_id`,`purchase_num`,`order_status`,`order_price`,`eprediction`,`order_sn`) value ";
            }
            $num = $i+1;
            $sql.="('".$data[$i][0]."','".$data[$i][2]."','".$data[$i][3]."','".$data[$i][6]."','".$data[$i][8]."','".$data[$i][12]."','".$data[$i][13]."','".$data[$i][24]."'),";
            //500 条数据入库一次
            if($num %500 ==0)
            {
                $sql = trim($sql,',');
                //echo $sql;die;
                Db::query($sql);
                unset($sql);
                $sql = '';
            }
            //处理取模剩下的数据
            if($i==$count-1)
            {
                $sql = trim($sql,',');
                Db::query($sql);
            }

             $getHaveOrder = Db::table('sp_coupon_ordersn')->where('tb_order_sn',$data[$i][24])->find();
             if(!empty($getHaveOrder))
             {
                 $score = sprintf('%.1f',$data[$i][13]);

//处理数据中订单付款状态，而用户已更新为订单结算-------- 将不确定积分减到确定积分中，修改订单为结算的状态， 添加积分在日志
                 if($data[$i][8] == '订单结算' && $getHaveOrder['status'] ==1){ //人工或脚本第二次导入处理

                         //减去对应的未确认积分 ，修改status=2
                         $sql1 =  'update sp_coupon_ordersn set status=2 , score = score-"'.$score.'" where tb_order_sn="'.$data[$i][24].'"  ';
                         Db::query($sql1);

                         //加到确认积分中
                         //加到确认积分中 , 减去未确认积分
                         $sql2 = 'update sp_member_coupon set score_available = score_available+"'.$score.'" ,score_unavailable= score_unavailable -'.$score.' where id= '.$getHaveOrder['uid'];
                         Db::query($sql2);

                         //加入日志
                         $insertData  = [
                             'description'=>'订单由付款更新为订单结算',
                             'uid'=>$getHaveOrder['uid'],
                             'type'=>1,
                             'score'=>$score,
                             'createtime'=>time()
                         ];
                         Db::table('sp_score_coupon_info')->insert($insertData);

                         //更新接口缓存

                 }

                 if($getHaveOrder['status']==0){ //默认订单非人工处理----触发
                     Db::startTrans();
                     try{
                         if($data[$i][8] == '订单付款' ){//

                             //更新sp_coupon_ordersn status = 1 score+=$data[$i][13] sprintf('%.1f',$data[$i][13]);
                             $sql1 =  'update sp_coupon_ordersn set status=1 , score = score+"'.$score.'" where tb_order_sn="'.$data[$i][24].'"  ';
                             Db::query($sql1);
                             //用户表 sp_member_coupon score_unavailable+=$data[$i][13]
                             $sql2 = 'update sp_member_coupon set score_unavailable = score_unavailable+"'.$score.'" where id= '.$getHaveOrder['uid'];
                             Db::query($sql2);

                             //日志表 sp_score_coupon_info description='订单付款' type=1 score=$data[$i]p[13]
                             $insertData  = [
                                 'description'=>'订单付款',
                                 'uid'=>$getHaveOrder['uid'],
                                 'type'=>1,
                                 'score'=>$score,
                                 'createtime'=>time()
                             ];
                             Db::table('sp_score_coupon_info')->insert($insertData);

                         }else if($data[$i][8] == '订单结算'){
                             //更新sp_coupon_ordersn status = 2
                             $sql1 =  'update sp_coupon_ordersn set status=2  where tb_order_sn="'.$data[$i][24].'"  ';
                             Db::query($sql1);
                             //用户表 sp_member_coupon score_available+=$data[$i][13]
                             $sql2 = 'update sp_member_coupon set score_available = score_available+"'.$score.'" where id= '.$getHaveOrder['uid'];
                             Db::query($sql2);

                             //日志表 sp_score_coupon_info description='订单结算' type=1 score=$data[$i]p[13]
                             $insertData  = [
                                 'description'=>'订单结算',
                                 'uid'=>$getHaveOrder['uid'],
                                 'type'=>1,
                                 'score'=>$score,
                                 'createtime'=>time()
                             ];
                             Db::table('sp_score_coupon_info')->insert($insertData);

                         }else if($data[$i][8] == '订单失效'){
                             //订单失效 ,更新订单status
                             Db::table('sp_coupon_ordersn')->where('tb_order_sn',$data[$i][24])->update(['status'=>3]);
                         }
                         //更新缓存


                         // 提交事务
                         Db::commit();
                     } catch (\Exception $e) {
                         // 回滚事务
                         Db::rollback();
                     }

                 }

             }

        }
      //  $sql = trim($sql,',');

       // Db::query($sql);

       // $end_time = time();
       // echo '一共花费了'.($end_time-$start_time).'s';die;
    }

public function orderExcleImport_new($highestRow,$objPHPExcel)
{
    //取余
    $start = floor($highestRow/500)*500;
    $sql  = '';
    for($j=2;$j<=$highestRow;$j++)
    {
        $sql .= "('" . $objPHPExcel->getActiveSheet()->getCell('A' . $j)->getValue() . "','" . $objPHPExcel->getActiveSheet()->getCell('C' . $j)->getValue() . "','" . $objPHPExcel->getActiveSheet()->getCell('D' . $j)->getValue() . "','" . $objPHPExcel->getActiveSheet()->getCell('G' . $j)->getValue() . "','" . $objPHPExcel->getActiveSheet()->getCell('I' . $j)->getValue() . "','" . $objPHPExcel->getActiveSheet()->getCell('M' . $j)->getValue() . "','" . $objPHPExcel->getActiveSheet()->getCell('N' . $j)->getValue() . "','" . $objPHPExcel->getActiveSheet()->getCell('Y' . $j)->getValue() . "'),";

        $num = $j+1;

        if($num%(500+2) ==0)
        {
            $sql2="insert into sp_coupon_tb_order (`creattime`,`goods_info`,`goods_id`,`purchase_num`,`order_status`,`order_price`,`eprediction`,`order_sn`) value ";
            $sql3  =  $sql2.$sql;
            $sql = trim($sql3,',');
            Db::query($sql);
            unset($sql);
            $sql  = '';
        }

        if($j ==  $highestRow){
            $sql2="insert into sp_coupon_tb_order (`creattime`,`goods_info`,`goods_id`,`purchase_num`,`order_status`,`order_price`,`eprediction`,`order_sn`) value ";
            $sql3  =  $sql2.$sql;
            $sql = trim($sql3,',');
            Db::query($sql);
            unset($sql);
            return true;
        }

    }

}




    }