tp5 Excel表格导入_tp5导出Excel

#### thinkphp5使用PHPExcel导入Excel、csv表格,thinkphp5表格导入导出
##### tp5使用PHPExcel导入Excel表格文件：
```php
PS:将PHPExcel扩展包放到vendor目录下  
/vendor/PHPExcel.php  
/vendor/PHPExcel  
  
public function uploadUser(){  
  if(Request::instance()->isPost()){  
    $file = request()->file('file');        // 获取表单提交过来的文件  
    $error = $_FILES['file']['error'];  // 如果$_FILES['file']['error']>0,表示文件上传失败  
    if(!$error){  
      $dir = ROOT_PATH . 'public' . DS . 'upload';  
      // 验证文件并移动到框架应用根目录/public/uploads/ 目录下  
      $info = $file->validate(['size'=>3145728,'ext'=>'xls,xlsx,csv'])->rule('uniqid')->move($dir);  
      /*判断是否符合验证*/  
      if($info){    //  符合类型  
        //$file_type = $info->getExtension();  
        $filename = $dir. DS .$info->getSaveName();  
        //echo $filename;  
        Vendor("PHPExcel.IOFactory");  
        $reader = \PHPExcel_IOFactory::createReader('Excel2007'); //设置以Excel2007格式  
        $PHPExcel = $reader->load($filename); // 载入excel文件  
        $sheet = $PHPExcel->getSheet(0); // 读取第一個工作表  
        $highestRow = $sheet->getHighestRow(); // 取得总行数  
        $highestColumm = $sheet->getHighestColumn(); // 取得总列数  
        /** 循环读取每个单元格的数据 */  
        $User = new User;  
        for ($row = 2; $row <= $highestRow; $row++){//行数是以第1行开始，这里示例中excel有3列字段  
          $userName = $sheet->getCell('A'.$row)->getValue();;  
          $website = $sheet->getCell('B'.$row)->getValue();;  
          $phone = $sheet->getCell('C'.$row)->getValue();;  
          $where = array();  
          $where['website'] = $website ? $website : 'https://www.fity.cn';  
          $where['phone'] = $phone;  
          $userInfo = $User->where($where)->find();  
          if($userInfo){  
            $userInfo = $userInfo->toArray();  
          }  
          $data = array();  
          if (!$userInfo) {  
            $data = array(  
              'userName'  =>  $userName,  
              'website'  =>  $website,  
              'phone'    =>  $phone  
            );  
            $User->data($data,true)->isUpdate(false)->save();  
          }  
        }  
        $this->success('导入数据库成功',url('index'));  
      } else{ //  不符合类型业务  
        $this->error('请选择上传3MB内的excel表格文件...');  
        //echo $file->getError();  
      }  
    }else{  
      $this->error('请选择需要上传的文件...');  
    }  
       
  }  
}
```

##### tp5使用PHPExcel导出数据为Excel表格：
```php
PS:将PHPExcel扩展包放到vendor目录下  
/vendor/PHPExcel.php  
/vendor/PHPExcel  
  
public function exportUser(){  
  //引入PHPExcel库文件  
  Vendor("PHPExcel");  
  //创建对象  
  $excel = new \PHPExcel();  
  //Excel表格式,这里简略写了3列  
  $letter = array('A','B','C','D');  
  //表头数组         
  $tableheader = array('姓名','手机号','网址');  
  //填充表头信息  
  for($i = 0;$i < count($tableheader);$i++) {  
    $excel->getActiveSheet()->setCellValue("$letter[$i]1","$tableheader[$i]");  
  }  
  //表格数组  
  $data = array(  
      array('测试1','18888888888','https://www.fity.cn'),  
      array('测试2','18888888888','https://www.fity.cn'),  
      array('测试2','18888888888','https://www.fity.cn'),  
  );  
  //填充表格信息  
  for ($i = 2;$i <= count($data) + 1;$i++) {  
    $j = 0;  
    foreach ($data[$i - 2] as $key=>$value) {  
      $excel->getActiveSheet()->setCellValue("$letter[$j]$i","$value");  
      $j++;  
    }  
  }  
  //创建Excel输入对象  
  $write = new \PHPExcel_Writer_Excel2007($excel);  
  header("Pragma: public");  
  header("Expires: 0");  
  header("Cache-Control:must-revalidate, post-check=0, pre-check=0");  
  header("Content-Type:application/force-download");  
  header("Content-Type:application/vnd.ms-execl");  
  header("Content-Type:application/octet-stream");  
  header("Content-Type:application/download");;  
  header('Content-Disposition:attachment;filename="导出测试.xls"');  
  header("Content-Transfer-Encoding:binary");  
  $write->save('php://output');  
}

```
##### tp5使用自定义函数导出数据为Excel表格：
```php
public function exportUser(){  
  $userList = Db::name('user')->select();  
  $string = "姓名\t手机号\t网址\t\n";  
  $string = iconv('utf-8','gb2312',$string);  
  foreach ($userList as $key=>$val) {  
    $userName  = iconv('utf-8','gb2312',$val['userName']);  
    $phone  = $val['phone'];  
    $website  = iconv('utf-8','gb2312',$val['website']);  
    $time  = date('Y-m-d H:i:s',$val['time']);  
    $string .= $userName."\t".$phone."\t".$website."\t".$time."'\t\n";  
  }  
   $fileName = date("YmdHis").".xls";  
   $this->exportExcelDriver($fileName,$string); //调用Excel导出函数  
}  
  
//Excel导出函数  
protected function exportExcelDriver($filename,$content){  
   header("Cache-Control: must-revalidate, post-check=0, pre-check=0");  
   header("Content-Type: application/vnd.ms-execl");  
   header("Content-Type: application/force-download");  
   header("Content-Type: application/download");  
   header("Content-Disposition: attachment; filename=".$filename);  
   header("Content-Transfer-Encoding: binary");  
   header("Pragma: no-cache");  
   header("Expires: 0");  
   echo $content;  
 }  
```
<br>
##### 补充：建议对获取的单元格数据进行数据类型检测，降低出错概率
```php
if(is_object($userName)){ //如果变量是对象格式化为字符串
	$userName= $userName->__toString();
}
```

本文最后更新于 2019-06-27 13:52:19 并被添加「tp5 thinkphp5」标签，已有 8527 位童鞋阅读过。

本文作者：未来往事

本文链接：https://felixway.cn/post/621.html

本站使用「署名 4.0 国际」创作共享协议，可自由转载、引用，但需署名作者且注明文章出处

未来往事

tp5 Excel表格导入_tp5导出Excel

相关文章

此处评论已关闭