详解thinkphp实现excel数据的导入导出（附完整案例）

实现步骤：

一：在http://phpexcel.codeplex.com/下载最新phpexcel放到vendor下，注意位置：thinkphp\extend\vendor\phpexcel\phpexcel.php。

二：导出excel代码实现

/**方法**/
function index(){
    $this->display();
  }
public function exportexcel($exptitle,$expcellname,$exptabledata){
    $xlstitle = iconv('utf-8', 'gb2312', $exptitle);//文件名称
    $filename = $_session['account'].date('_ymdhis');//or $xlstitle 文件名称可根据自己情况设定
    $cellnum = count($expcellname);
    $datanum = count($exptabledata);
    vendor("phpexcel.phpexcel");
    
    $objphpexcel = new phpexcel();
    $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');
    
    $objphpexcel->getactivesheet(0)->mergecells('a1:'.$cellname[$cellnum-1].'1');//合并单元格
    // $objphpexcel->setactivesheetindex(0)->setcellvalue('a1', $exptitle.' export time:'.date('y-m-d h:i:s')); 
    for($i=0;$i<$cellnum;$i++){
      $objphpexcel->setactivesheetindex(0)->setcellvalue($cellname[$i].'2', $expcellname[$i][1]); 
    } 
     // miscellaneous glyphs, utf-8  
    for($i=0;$i<$datanum;$i++){
     for($j=0;$j<$cellnum;$j++){
      $objphpexcel->getactivesheet(0)->setcellvalue($cellname[$j].($i+3), $exptabledata[$i][$expcellname[$j][0]]);
     }       
    } 
    
    header('pragma:public');
    header('content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlstitle.'.xls"');
    header("content-disposition:attachment;filename=$filename.xls");//attachment新窗口打印inline本窗口打印
    $objwriter = phpexcel_iofactory::createwriter($objphpexcel, 'excel5'); 
    $objwriter->save('php://output'); 
    exit;  
  }
/**
   *
   * 导出excel
   */
  function expuser(){//导出excel
    $xlsname = "user";
    $xlscell = array(
    array('id','账号序列'),
    array('truename','名字'),
    array('sex','性别'),
    array('res_id','院系'),
    array('sp_id','专业'),
    array('class','班级'),
    array('year','毕业时间'),
    array('city','所在地'),
    array('company','单位'),
    array('zhicheng','职称'),
    array('zhiwu','职务'),
    array('jibie','级别'),
    array('tel','电话'),
    array('qq','qq'),
    array('email','邮箱'),
    array('honor','荣誉'),
    array('remark','备注')  
    );
    $xlsmodel = m('member');
  
    $xlsdata = $xlsmodel->field('id,truename,sex,res_id,sp_id,class,year,city,company,zhicheng,zhiwu,jibie,tel,qq,email,honor,remark')->select();
    foreach ($xlsdata as $k => $v)
    {
      $xlsdata[$k]['sex']=$v['sex']==1?'男':'女';
    }
    $this->exportexcel($xlsname,$xlscell,$xlsdata);
     
  }

第三：导入excel数据代码

function impuser(){
    if (!empty($_files)) {
      import("@.org.uploadfile");
      $config=array(
        'allowexts'=>array('xlsx','xls'),
        'savepath'=>'./public/upload/',
        'saverule'=>'time',
      );
      $upload = new uploadfile($config);
      if (!$upload->upload()) {
        $this->error($upload->geterrormsg());
      } else {
        $info = $upload->getuploadfileinfo();
        
      }
    
      vendor("phpexcel.phpexcel");
        $file_name=$info[0]['savepath'].$info[0]['savename'];
        $objreader = phpexcel_iofactory::createreader('excel5');
        $objphpexcel = $objreader->load($file_name,$encode='utf-8');
        $sheet = $objphpexcel->getsheet(0);
        $highestrow = $sheet->gethighestrow(); // 取得总行数
        $highestcolumn = $sheet->gethighestcolumn(); // 取得总列数
        for($i=3;$i<=$highestrow;$i++)
        {  
          $data['account']= $data['truename'] = $objphpexcel->getactivesheet()->getcell("b".$i)->getvalue(); 
          $sex = $objphpexcel->getactivesheet()->getcell("c".$i)->getvalue();
          // $data['res_id']  = $objphpexcel->getactivesheet()->getcell("d".$i)->getvalue();
          $data['class'] = $objphpexcel->getactivesheet()->getcell("e".$i)->getvalue();
          $data['year'] = $objphpexcel->getactivesheet()->getcell("f".$i)->getvalue();
          $data['city']= $objphpexcel->getactivesheet()->getcell("g".$i)->getvalue();
          $data['company']= $objphpexcel->getactivesheet()->getcell("h".$i)->getvalue();
          $data['zhicheng']= $objphpexcel->getactivesheet()->getcell("i".$i)->getvalue();
          $data['zhiwu']= $objphpexcel->getactivesheet()->getcell("j".$i)->getvalue();
          $data['jibie']= $objphpexcel->getactivesheet()->getcell("k".$i)->getvalue();
          $data['honor']= $objphpexcel->getactivesheet()->getcell("l".$i)->getvalue();
          $data['tel']= $objphpexcel->getactivesheet()->getcell("m".$i)->getvalue();
          $data['qq']= $objphpexcel->getactivesheet()->getcell("n".$i)->getvalue();
          $data['email']= $objphpexcel->getactivesheet()->getcell("o".$i)->getvalue();
          $data['remark']= $objphpexcel->getactivesheet()->getcell("p".$i)->getvalue();
          $data['sex']=$sex=='男'?1:0;
          $data['res_id'] =1;
          
          $data['last_login_time']=0;
          $data['create_time']=$data['last_login_ip']=$_server['remote_addr'];
          $data['login_count']=0;
          $data['join']=0;
          $data['avatar']='';
          $data['password']=md5('123456');       
          m('member')->add($data);
     
        } 
         $this->success('导入成功！');
    }else
      {
        $this->error("请选择上传的文件");
      }  
     

  }

四、模板代码

<html>
  <head>
    
  </head>
  <body>
  <p><a href="{:u('index/expuser')}" >导出数据并生成excel</a></p><br/>
    <form action="{:u('index/impuser')}" method="post" enctype="multipart/form-data">
      <input type="file" name="import"/>
      <input type="hidden" name="table" value="tablename"/>
      <input type="submit" value="导入"/>
    </form>
  </body>
  
</html>

最后下载：

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。