PHP 类实现Excel 导入Mysql

来源：互联网发布：php 即时聊天编辑：程序博客网时间：2024/06/05 06:05

   //导入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;        }    }}    }

阅读全文

0 0