当前位置:首页 > CMS教程 > Thinkphp > 列表

thinkphp实现excel数据的导入导出的实例代码

发布:smiling 来源: PHP粉丝网  添加日期:2018-06-01 10:02:25 浏览: 评论:0 

实现步骤:

一:在http://phpExcel.codeplex.com/下载最新PHPExcel放到Vendor下,注意位置:ThinkPHP\Extend\Vendor\PHPExcel\PHPExcel.php。

二:导出excel代码实现

  1. /**方法**/ 
  2. functionindex(){ 
  3.     $this->display(); 
  4.   } 
  5. publicfunctionexportExcel($expTitle,$expCellName,$expTableData){ 
  6.     $xlsTitle= iconv('utf-8','gb2312',$expTitle);//文件名称 
  7.     $fileName=$_SESSION['account'].date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定 
  8.     $cellNum=count($expCellName); 
  9.     $dataNum=count($expTableData); 
  10.     vendor("PHPExcel.PHPExcel"); 
  11.       
  12.     $objPHPExcel=newPHPExcel(); 
  13.     $cellName=array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ'); 
  14.       
  15.     $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格 
  16.     // $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.' Export time:'.date('Y-m-d H:i:s')); 
  17.     for($i=0;$i<$cellNum;$i++){ 
  18.       $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2',$expCellName[$i][1]); 
  19.     } 
  20.      // Miscellaneous glyphs, UTF-8  
  21.     for($i=0;$i<$dataNum;$i++){ 
  22.      for($j=0;$j<$cellNum;$j++){ 
  23.       $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3),$expTableData[$i][$expCellName[$j][0]]); 
  24.      }       
  25.     } 
  26.       
  27.     header('pragma:public'); 
  28.     header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"'); 
  29.     header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印 
  30.     $objWriter= PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5'); 
  31.     $objWriter->save('php://output'); 
  32.     exit;  
  33.   } 
  34. /** 
  35.    * 
  36.    * 导出Excel 
  37.    */ 
  38.   functionexpUser(){//导出Excel 
  39.     $xlsName="User"
  40.     $xlsCell=array
  41.     array('id','账号序列'), 
  42.     array('truename','名字'), 
  43.     array('sex','性别'), 
  44.     array('res_id','院系'), 
  45.     array('sp_id','专业'), 
  46.     array('class','班级'), 
  47.     array('year','毕业时间'), 
  48.     array('city','所在地'), 
  49.     array('company','单位'), 
  50.     array('zhicheng','职称'), 
  51.     array('zhiwu','职务'), 
  52.     array('jibie','级别'), 
  53.     array('tel','电话'), 
  54.     array('qq','qq'), 
  55.     array('email','邮箱'), 
  56.     array('honor','荣誉'), 
  57.     array('remark','备注')  
  58.     ); 
  59.     $xlsModel= M('Member'); 
  60.    //phpfensi.com 
  61.     $xlsData=$xlsModel->Field('id,truename,sex,res_id,sp_id,class,year,city,company,zhicheng,zhiwu,jibie,tel,qq,email,honor,remark')->select(); 
  62.     foreach($xlsDataas$k=>$v
  63.     { 
  64.       $xlsData[$k]['sex']=$v['sex']==1?'男':'女'
  65.     } 
  66.     $this->exportExcel($xlsName,$xlsCell,$xlsData); 
  67.        
  68.   } 

第三:导入excel数据代码

  1. functionimpUser(){ 
  2.     if(!emptyempty($_FILES)) { 
  3.       import("@.ORG.UploadFile"); 
  4.       $config=array
  5.         'allowExts'=>array('xlsx','xls'), 
  6.         'savePath'=>'./Public/upload/'
  7.         'saveRule'=>'time'
  8.       ); 
  9.       $upload=newUploadFile($config); 
  10.       if(!$upload->upload()) { 
  11.         $this->error($upload->getErrorMsg()); 
  12.       }else
  13.         $info=$upload->getUploadFileInfo(); 
  14.           
  15.       } 
  16.       
  17.       vendor("PHPExcel.PHPExcel"); 
  18.         $file_name=$info[0]['savepath'].$info[0]['savename']; 
  19.         $objReader= PHPExcel_IOFactory::createReader('Excel5'); 
  20.         $objPHPExcel=$objReader->load($file_name,$encode='utf-8'); 
  21.         $sheet=$objPHPExcel->getSheet(0); 
  22.         $highestRow=$sheet->getHighestRow();// 取得总行数 
  23.         $highestColumn=$sheet->getHighestColumn();// 取得总列数 
  24.         for($i=3;$i<=$highestRow;$i++) 
  25.         {  
  26.           $data['account']=$data['truename'] =$objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue(); 
  27.           $sex=$objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue(); 
  28.           // $data['res_id']  = $objPHPExcel->getActiveSheet()->getCell("D".$i)->getValue(); 
  29.           $data['class'] =$objPHPExcel->getActiveSheet()->getCell("E".$i)->getValue(); 
  30.           $data['year'] =$objPHPExcel->getActiveSheet()->getCell("F".$i)->getValue(); 
  31.           $data['city']=$objPHPExcel->getActiveSheet()->getCell("G".$i)->getValue(); 
  32.           $data['company']=$objPHPExcel->getActiveSheet()->getCell("H".$i)->getValue(); 
  33.           $data['zhicheng']=$objPHPExcel->getActiveSheet()->getCell("I".$i)->getValue(); 
  34.           $data['zhiwu']=$objPHPExcel->getActiveSheet()->getCell("J".$i)->getValue(); 
  35.           $data['jibie']=$objPHPExcel->getActiveSheet()->getCell("K".$i)->getValue(); 
  36.           $data['honor']=$objPHPExcel->getActiveSheet()->getCell("L".$i)->getValue(); 
  37.           $data['tel']=$objPHPExcel->getActiveSheet()->getCell("M".$i)->getValue(); 
  38.           $data['qq']=$objPHPExcel->getActiveSheet()->getCell("N".$i)->getValue(); 
  39.           $data['email']=$objPHPExcel->getActiveSheet()->getCell("O".$i)->getValue(); 
  40.           $data['remark']=$objPHPExcel->getActiveSheet()->getCell("P".$i)->getValue(); 
  41.           $data['sex']=$sex=='男'?1:0; 
  42.           $data['res_id'] =1; 
  43.             
  44.           $data['last_login_time']=0; 
  45.           $data['create_time']=$data['last_login_ip']=$_SERVER['REMOTE_ADDR']; 
  46.           $data['login_count']=0; 
  47.           $data['join']=0; 
  48.           $data['avatar']=''
  49.           $data['password']=md5('123456');       
  50.           M('Member')->add($data); 
  51.        
  52.         } 
  53.          $this->success('导入成功!'); 
  54.     }else 
  55.       { 
  56.         $this->error("请选择上传的文件"); 
  57.       }  
  58.        
  59.  
  60.   } 

四、模板代码

  1. <html> 
  2.   <head> 
  3.       
  4.   </head> 
  5.   <body> 
  6.   <P><ahrefahref="{:U('Index/expUser')}">导出数据并生成excel</a></P><br/> 
  7.     <formactionformaction="{:U('Index/impUser')}"method="post"enctype="multipart/form-data"> 
  8.       <inputtypeinputtype="file"name="import"/> 
  9.       <inputtypeinputtype="hidden"name="table"value="tablename"/> 
  10.       <inputtypeinputtype="submit"value="导入"/> 
  11.     </form> 
  12.   </body> 
  13.     
  14. </html> 

Tags: 实例 代码 数据

分享到: