search

Home >Backend Development >PHP Tutorial >thinkphp implements the import and export of excel data (complete case attached)

thinkphp implements the import and export of excel data (complete case attached)

不言
不言Original
2018-06-06 15:25:223875browse

This article mainly introduces thinkphp to implement the import and export of excel data. It has certain reference value. Interested friends can refer to it.

Implementation steps:

1: Download the latest PHPExcel at http://phpexcel.codeplex.com/ and put it under Vendor. Note the location: ThinkPHP\Extend\Vendor\PHPExcel\ PHPExcel.php.

Two: Export excel code implementation

/**方法**/
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);
     
  }

Third: Import excel data code

function impUser(){
    if (!empty($_FILES)) {
      import("@.ORG.UploadFile");
      $config=array(
        &#39;allowExts&#39;=>array(&#39;xlsx&#39;,&#39;xls&#39;),
        &#39;savePath&#39;=>&#39;./Public/upload/&#39;,
        &#39;saveRule&#39;=>&#39;time&#39;,
      );
      $upload = new UploadFile($config);
      if (!$upload->upload()) {
        $this->error($upload->getErrorMsg());
      } else {
        $info = $upload->getUploadFileInfo();
        
      }
    
      vendor("PHPExcel.PHPExcel");
        $file_name=$info[0][&#39;savepath&#39;].$info[0][&#39;savename&#39;];
        $objReader = PHPExcel_IOFactory::createReader(&#39;Excel5&#39;);
        $objPHPExcel = $objReader->load($file_name,$encode=&#39;utf-8&#39;);
        $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("请选择上传的文件");
      }  
     

  }

4. Template code

<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>

Last download: demo download

Related recommendations:

ThinkPHP Basic Add, Delete, Check and Change Operation Example Tutorial

ThinkPHP Implementation of Update Data Example Detailed explanation (demo)

The above is the detailed content of thinkphp implements the import and export of excel data (complete case attached). For more information, please follow other related articles on the PHP Chinese website!

Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：Three ways to store arrays as text files in phpNext article：Three ways to store arrays as text files in php

Related articles

See more