thinkphp整合PHPExcel导入Excel数据到mysql(2)

 上一篇文章里面只是仅仅分享两个Thinkphp使用PHPExcel导入Excel数据到mysql的源码，实际上有了实例源码之后，把实例源码整合到自己的项目中是很简单的一件事情了，下面夏日博客再来分享一下自己使用 PHPExcel 批量导入 Excel 的数据到 Mysql 吧，只是分享的代码有点凌乱，因为我的项目涉及到了几个 Thinkphp 的表，要查询余额以及扣款，还有风险等级，风险比例的计算。

 
  1. function impUser(){  
  2.           
  3.         if (!emptyempty($_FILES)) {  
  4.             import('ORG.Net.UploadFile');  
  5.             $config=array(  
  6.                 'allowExts'=>array('xlsx','xls'),  
  7.                 'savePath'=>'Uploads/',  
  8.                 'saveRule'=>'time',  
  9.             );  
  10.             $upload = new UploadFile($config);  
  11.             if (!$upload->upload()) {  
  12.                 $this->error($upload->getErrorMsg());  
  13.             } else {  
  14.                 $info = $upload->getUploadFileInfo();  
  15.   
  16.             }  
  17.               
  18.             vendor("PHPExcel.PHPExcel");  
  19.               
  20.             $objPHPExcel = new PHPExcel();  
  21.               
  22.             $file_name=$info[0]['savepath'].$info[0]['savename'];  
  23.             $objReader = PHPExcel_IOFactory::createReader('Excel5');  
  24.             $objPHPExcel = $objReader->load($file_name,$encode='utf-8');  
  25.             $sheet = $objPHPExcel->getSheet(0);  
  26.             $highestRow = $sheet->getHighestRow(); // 取得总行数  
  27.             $highestColumn = $sheet->getHighestColumn(); // 取得总列数  
  28.               
  29.             if (!$_POST[xyb]){  
  30.             $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);  
  31.             $objWriteHTML = new PHPExcel_Writer_HTML($objPHPExcel); //输出网页格式的对象  
  32.             $objWriteHTML->save("php://output");  
  33.             }else{  
  34.                   
  35.                 for($i=2;$i<=$highestRow;$i++)  
  36.                 {  
  37.                       
  38.                     $data['realname']          = $objPHPExcel->getActiveSheet()->getCell("A".$i)->getValue();  //真实姓名  
  39.                     $data['cardno'] = $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();  //身份证号  
  40.                       
  41.                     if ($objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue() == '男'){  
  42.                         $data['sex'] = 1;//性别  
  43.                     }else{  
  44.                         $data['sex'] = 2;  
  45.                     }  
  46.                       
  47.                     $data['age']        = $objPHPExcel->getActiveSheet()->getCell("D".$i)->getValue();  //年龄  
  48.                       
  49.                     $data['phone']        = $objPHPExcel->getActiveSheet()->getCell("E".$i)->getValue();  //手机号  
  50.                     $data['title']        = $objPHPExcel->getActiveSheet()->getCell("F".$i)->getValue();  //投资内容  
  51.                     $data['nature']        = $objPHPExcel->getActiveSheet()->getCell("G".$i)->getValue();  //投资性质  
  52.                     $data['money']        = $objPHPExcel->getActiveSheet()->getCell("H".$i)->getValue();  //投资额度  
  53.                     $data['annualizedyield']        = $objPHPExcel->getActiveSheet()->getCell("I".$i)->getValue();  //年化收益率  
  54.                     $data['begindate']        = $objPHPExcel->getActiveSheet()->getCell("J".$i)->getValue();  //开始时间  
  55.                       
  56.                     $data['enddate']        = $objPHPExcel->getActiveSheet()->getCell("K".$i)->getValue();  //截止时间  
  57.                     $data['approach']        = $objPHPExcel->getActiveSheet()->getCell("L".$i)->getValue();  //收益方式  
  58.                       
  59.                     //$data['beneficiary']        = $objPHPExcel->getActiveSheet()->getCell("A".$i)->getValue();  //受益人  
  60.                     $data['create_time']        = date("Y-m-d h:i:s", time()); //创建时间  
  61.                     $data['tradingplatform']        = $_SESSION[C('COMPANY_AUTH_KEY')];  //交易平台  
  62.                   
  63.                     if(M('p2p')->add($data)){ //如果添加成功发送短信  
  64.   
  65.                     $list_p2pid = M("p2p")->order('id desc')->find();//查询刚添加的数据  
  66.                       
  67.                     //扣费开始  
  68.                     $listc = M("company")->where("id=".$_SESSION[C('COMPANY_AUTH_KEY')])->find(); //企业名称,余额  
  69.                     $amount = $listc[amount]; //余额  
  70.                     //余额结束  
  71.                           
  72.                     //投资天数  
  73.                     $limitdate = (strtotime($data['enddate']) - strtotime($data['begindate']))/86400;  
  74.                     //投资天数结束  
  75.                       
  76.                     //查询ratio里面的等级天数  
  77.                     $list = M("ratio")->where("gradeid=".$listc[gradeid])->order('id desc')->select();  
  78.                           
  79.                     for($j=0;$j<count($list);$j++){  
  80.                       
  81.                         if($limitdate>$list[$j]['upper'] and $limitdate<$list[$j]['lower']){  
  82.                             $kq = $amount-$list[$j]['proportion']*$data['money']/100; //扣除剩下的钱  
  83.                             if ($kq>=0){  
  84.                                 M('company')->where("id=".$_SESSION[C('COMPANY_AUTH_KEY')])->setField('amount',$kq); //更新余额值  
  85.                                 if(M('p2p')->where("id=".$list_p2pid[id])->setField('status',1)){  
  86.                                       
  87.                                     //发送短信开始  
  88.                                     //发送短信实例化  
  89.                                     Vendor('Sms.CCPRestSDK');  
  90.                                     //主帐号  
  91.                                     $accountSid= '11';  
  92.                                     //主帐号Token  
  93.                                     $accountToken= '22';  
  94.                                     //应用Id  
  95.                                     $appId='33';  
  96.                                     //请求地址，格式如下，不需要写https://  
  97.                                     $serverIP='app.cloopen.com';  
  98.                                     //请求端口  
  99.                                     $serverPort='8883';  
  100.                                     //REST版本号  
  101.                                     $softVersion='2013-12-26';  
  102.                                           
  103.                                     $arr=array();  
  104.                                     $arr['0']=$list_p2pid['realname']; //姓名  
  105.                                     $arr['1']=$listc['nickname']; //投资平台名称  
  106.                                     $arr['2']="http://www.xiariboke.com/?m=p&amp;i=".$list_p2pid[id]; //保单链接  
  107.                                           
  108.                                     $mobile = $list_p2pid[phone];  
  109.                                     if($mobile){  
  110.                                       
  111.                                         $rest = new REST($serverIP,$serverPort,$softVersion);  
  112.                                         $rest->setAccount($accountSid,$accountToken);  
  113.                                         $rest->setAppId($appId);  
  114.                                       
  115.                                         $result = $rest->sendTemplateSMS($mobile,$arr,"106439");  
  116.                                       
  117.                                         if($result == NULL ) {  
  118.                                             echo "result error!";  
  119.                                             break;  
  120.                                         }  
  121.                                         if($result->statusCode!=0) {  
  122.                                             echo $result->statusMsg . "  错误代码：".$result->statusCode;  
  123.                                             //TODO 添加错误处理逻辑  
  124.                                         }else{  
  125.                                             //return 1;  
  126.                                         }  
  127.                                     }  
  128.                                     //发送结束发送  
  129.                                       
  130.                                 }  
  131.                             }  
  132.                         }  
  133.                       
  134.                     }//扣费结束  
  135.   
  136.                 }  
  137.                 unset($data);  
  138.                 }  
  139.                   
  140.                 $highestRow = $highestRow - 1;  
  141.                 $this->success('成功导入'.$highestRow.'条数据',U('Baodan/index'));  
  142.             }  
  143.         }else  
  144.         {  
  145.             $this->error("请选择上传的文件");  
  146.         }  
  147.           
  148.         //$this->active=52;  
  149.         //$this->display();  
  150.               
  151.     }  

实际上真正批量导入的代码并不多，批量导入只是加了一个 for 的循环语句，我这里有点小小的复杂，是因为每循环一次，都要进行一次数据库的计算操作，并且要给每一个用户手机发送一条短信提示的信息，如果你不需要的话可以全部删除掉，至于 Thinkphp 的数组之类的就很好理解了。

扫一扫手机访问