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

本篇文章主要介绍了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].&#39;2&#39;, $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(&#39;pragma:public&#39;);
    header(&#39;Content-type:application/vnd.ms-excel;charset=utf-8;name="&#39;.$xlsTitle.&#39;.xls"&#39;);
    header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, &#39;Excel5&#39;); 
    $objWriter->save(&#39;php://output&#39;); 
    exit;  
  }
/**
   *
   * 导出Excel
   */
  function expUser(){//导出Excel
    $xlsName = "User";
    $xlsCell = array(
    array(&#39;id&#39;,&#39;账号序列&#39;),
    array(&#39;truename&#39;,&#39;名字&#39;),
    array(&#39;sex&#39;,&#39;性别&#39;),
    array(&#39;res_id&#39;,&#39;院系&#39;),
    array(&#39;sp_id&#39;,&#39;专业&#39;),
    array(&#39;class&#39;,&#39;班级&#39;),
    array(&#39;year&#39;,&#39;毕业时间&#39;),
    array(&#39;city&#39;,&#39;所在地&#39;),
    array(&#39;company&#39;,&#39;单位&#39;),
    array(&#39;zhicheng&#39;,&#39;职称&#39;),
    array(&#39;zhiwu&#39;,&#39;职务&#39;),
    array(&#39;jibie&#39;,&#39;级别&#39;),
    array(&#39;tel&#39;,&#39;电话&#39;),
    array(&#39;qq&#39;,&#39;qq&#39;),
    array(&#39;email&#39;,&#39;邮箱&#39;),
    array(&#39;honor&#39;,&#39;荣誉&#39;),
    array(&#39;remark&#39;,&#39;备注&#39;)  
    );
    $xlsModel = M(&#39;Member&#39;);
  
    $xlsData = $xlsModel->Field(&#39;id,truename,sex,res_id,sp_id,class,year,city,company,zhicheng,zhiwu,jibie,tel,qq,email,honor,remark&#39;)->select();
    foreach ($xlsData as $k => $v)
    {
      $xlsData[$k][&#39;sex&#39;]=$v[&#39;sex&#39;]==1?&#39;男&#39;:&#39;女&#39;;
    }
    $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[&#39;account&#39;]= $data[&#39;truename&#39;] = $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue(); 
          $sex = $objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue();
          // $data[&#39;res_id&#39;]  = $objPHPExcel->getActiveSheet()->getCell("D".$i)->getValue();
          $data[&#39;class&#39;] = $objPHPExcel->getActiveSheet()->getCell("E".$i)->getValue();
          $data[&#39;year&#39;] = $objPHPExcel->getActiveSheet()->getCell("F".$i)->getValue();
          $data[&#39;city&#39;]= $objPHPExcel->getActiveSheet()->getCell("G".$i)->getValue();
          $data[&#39;company&#39;]= $objPHPExcel->getActiveSheet()->getCell("H".$i)->getValue();
          $data[&#39;zhicheng&#39;]= $objPHPExcel->getActiveSheet()->getCell("I".$i)->getValue();
          $data[&#39;zhiwu&#39;]= $objPHPExcel->getActiveSheet()->getCell("J".$i)->getValue();
          $data[&#39;jibie&#39;]= $objPHPExcel->getActiveSheet()->getCell("K".$i)->getValue();
          $data[&#39;honor&#39;]= $objPHPExcel->getActiveSheet()->getCell("L".$i)->getValue();
          $data[&#39;tel&#39;]= $objPHPExcel->getActiveSheet()->getCell("M".$i)->getValue();
          $data[&#39;qq&#39;]= $objPHPExcel->getActiveSheet()->getCell("N".$i)->getValue();
          $data[&#39;email&#39;]= $objPHPExcel->getActiveSheet()->getCell("O".$i)->getValue();
          $data[&#39;remark&#39;]= $objPHPExcel->getActiveSheet()->getCell("P".$i)->getValue();
          $data[&#39;sex&#39;]=$sex==&#39;男&#39;?1:0;
          $data[&#39;res_id&#39;] =1;
          
          $data[&#39;last_login_time&#39;]=0;
          $data[&#39;create_time&#39;]=$data[&#39;last_login_ip&#39;]=$_SERVER[&#39;REMOTE_ADDR&#39;];
          $data[&#39;login_count&#39;]=0;
          $data[&#39;join&#39;]=0;
          $data[&#39;avatar&#39;]=&#39;&#39;;
          $data[&#39;password&#39;]=md5(&#39;123456&#39;);       
          M(&#39;Member&#39;)->add($data);
     
        } 
         $this->success(&#39;导入成功！&#39;);
    }else
      {
        $this->error("请选择上传的文件");
      }  
     

  }

四、模板代码

<html>
  <head>
    
  </head>
  <body>
  <P><a href="{:U(&#39;Index/expUser&#39;)}" >导出数据并生成excel</a></P><br/>
    <form action="{:U(&#39;Index/impUser&#39;)}" 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>

最后下载：demo下载

相关推荐：

ThinkPHP基本的增删查改操作实例教程

ThinkPHP实现更新数据实例详解(demo)

以上是thinkphp实现excel数据的导入导出（附完整案例）的详细内容。更多信息请关注PHP中文网其他相关文章！