Home > Article > Backend Development > How to use PHPExcel to upload tables in batches

How to use PHPExcel to upload tables in batches

php中世界最好的语言Original: 2017-12-20 14:25:261684browse

In many cases we will need to upload tables to the database in batches, so today I will teach you how to use PHP to upload tables in batches.

First of all, you need to download PHPExcel and put it in the vendor folder. My path is: project/vendor/PHPExcel/, put the downloaded PHPExcel file here

Front-end code

<!DOCTYPE html>
<html>
<head>
  <title>批量导入数据</title>
</head>
<body>
<form action="{:url(&#39;/index/index/importExcel&#39;)}" method="post" enctype="multipart/form-data">
  <input type="file" name="myfile"><br/>
  <input type="submit" value="批量的导入">
</form>
</body>
</html>

Backend code

/**
  * 导入表格数据
  * 先把文件上传到服务器，然后再读取数据存到数据库
  */
  public function importExcel(){
    header("content-type:text/html;charset=utf-8");
    //上传excel文件
    $file = request()->file(&#39;myfile&#39;);
    //移到/public/uploads/excel/下
    $info = $file->move(ROOT_PATH.&#39;public&#39;.DS.&#39;uploads&#39;.DS.&#39;excel&#39;);
    //上传文件成功
    if ($info) {
      //引入PHPExcel类
      vendor(&#39;PHPExcel.PHPExcel.Reader.Excel5&#39;);
      //获取上传后的文件名
      $fileName = $info->getSaveName();
      //文件路径
      $filePath = &#39;public/uploads/excel/&#39;.$fileName;
      //实例化PHPExcel类
      $PHPReader = new \PHPExcel_Reader_Excel5();
      //读取excel文件
      $objPHPExcel = $PHPReader->load($filePath);
      //读取excel文件中的第一个工作表
      $sheet = $objPHPExcel->getSheet(0);
      $allRow = $sheet->getHighestRow();  //取得总行数
      //$allColumn = $sheet->getHighestColumn();  //取得总列数
      //从第二行开始插入，第一行是列名
      for ($j=2; $j <= $allRow; $j++) {
        $data[&#39;name&#39;] = $objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue();
        $data[&#39;tel&#39;] = $objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();
        $data[&#39;addr&#39;] = $objPHPExcel->getActiveSheet()->getCell("C".$j)->getValue();
        $last_id = Db::table(&#39;users&#39;)->insertGetId($data);//保存数据，并返回主键id
        if ($last_id) {
          echo "第".$j."行导入成功，users表第:".$last_id."条！<br/>";
        }else{
          echo "第".$j."行导入失败！<br/>";
        }
      }
    }else{
      echo "上传文件失败！";
    }
  }

Output results

Note:

Introduce third party Class libraryUse vendor( ); is in the form of namespace. The underlying code will automatically replace "." with "/", so when using "/", use "." instead;

The above code can be copied and used directly, but the database related information must be changed to your own !

# I believe you have mastered the methods after reading these cases. For more exciting information, please pay attention to other related articles on the php Chinese website!

How to use PHPExcel to upload tables in batches

Related articles