注:
1.官网下载phpexcel
2.引入文件vendor("PHPExcel.PHPExcel.IOFactory");
3.数据处理:
if(IS_POST){
$file=I('file');
$data = $this->model->create();
$data['cateid'] = $data['cateid'];
$data['create_time'] = time();
$bookData = $this->model->importExcel('../Uploads/'.$file);
//正则验证手机
foreach($bookData['data'] as $k=>$v){
if(!preg_match("/^1[34578]{1}\d{9}$/",$v['phone'])){
unset($bookData['data'][$k]); //删除不正确手机号
}
}
foreach($bookData['data'] as $k=>$v){
$data['name'] = $v['name'];
$data['phone'] = $v['phone'];
$data['birthday'] = strtotime($v['birthday']);
$data['address'] = $v['address'];
$data['email'] = $v['email'];
$data['qq'] = $v['qq'];
$data['remark'] = $v['remark'];
if($v['sex'] == '男'){
$data['sex'] = 1;
}else{
$data['sex'] = 2;
}
$result = $this->model->add($data);
if(!$result) return show('300','导入失败');
}
return show(200,'导入成功');
}
4.调用的方法
public function importExcel($file){
if(!file_exists($file)){
return show(300,'文件目录不存在');
}
vendor("PHPExcel.PHPExcel.IOFactory");
//获取excel文件:获取Excel第1张表即(Sheet1)
$objPHPExcel= \PHPExcel_IOFactory::load($file);
$objPHPExcel->setActiveSheetIndex(0);
$sheet1=$objPHPExcel->getSheet(0);
//获取行数,并把数据读取出来$data数组
$rowCount=$sheet1->getHighestRow();//excel行数
$data=array();
for($i=2;$i<=$rowCount;$i++){
$item['name']=$sheet1->getCellByColumnAndRow(0,$i)->getValue();
$item['sex']=$sheet1->getCellByColumnAndRow(1,$i)->getValue();
$item['phone']=$sheet1->getCellByColumnAndRow(2,$i)->getValue();
$item['birthday']=$sheet1->getCellByColumnAndRow(3,$i)->getValue();
$item['address']=$sheet1->getCellByColumnAndRow(4,$i)->getValue();
$item['email']=$sheet1->getCellByColumnAndRow(5,$i)->getValue();
$item['qq']=$sheet1->getCellByColumnAndRow(6,$i)->getValue();
$item['remark']=$sheet1->getCellByColumnAndRow(7,$i)->getValue();
$data[]=$item;
}
$success=0;
$error=0;
$sum=count($data);
$message['sum'] = $sum;
$message['success'] = $success;
$message['error'] = $error;
$message['data'] = $data;
return $message;
}