#### 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('Excel'); //设置以Excel格式
$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 : '';
$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',''),
array('测试2','18888888888',''),
array('测试2','18888888888',''),
);
//填充表格信息
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_Excel($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();
}
```
最后更新于-06-27 13:52:19并被添加「tp5 thinkphp5」标签，已有 6027 位童鞋阅读过。
本站使用「署名 4.0 国际」创作共享协议，可自由转载、引用，但需署名作者且注明文章出处
相关文章
