2017/04/28 PHP/Python/Java No Comments 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; } ``` ##### 补充:建议对获取的单元格数据进行数据类型检测,降低出错概率 ```php if(is_object($userName)){ //如果变量是对象格式化为字符串 $userName= $userName->__toString(); } ``` 本文最后更新于 2019-06-27 13:52:19 并被添加「tp5 thinkphp5」标签,已有 8403 位童鞋阅读过。 本文作者:未来往事 本文链接:https://felixway.cn/post/621.html 本站使用「署名 4.0 国际」创作共享协议,可自由转载、引用,但需署名作者且注明文章出处 相关文章 TP5 save遍历更新,过滤相邻重复字段,问题汇总 tp5分页 搜索条件不保留/失效处理方法 tp5 save遍历新增数据第二次变为update问题处理
此处评论已关闭