php电子表格 导入/导出excel实例

【核心代码】

<?php
namespace Home\Controller;
use Think\Controller;
class ExcelController extends Controller {
   
   //列表页面
    public function index(){
		
		$map['status']=1;
		if(!empty($_POST['nickname'])){
			$map['nickname']  = array('like',"%$_POST[nickname]%");
			$Page->parameter =   "nickname=".$_POST['nickname'].'&';
			$this->assign('nickname',$_POST['nickname']);
			}
		if(!empty($_POST['email'])){
			$map['email']  = array('like',"%$_POST[email]%");
			$Page->parameter .=   "email=".$_POST['email'];
			$this->assign('email',$_POST['email']);
			}
		
		$User=M("user"); // 实例化User对象// 进行分页数据查询 注意page方法的参数的前面部分是当前的页数使用 $_GET[p]获取
		$user_list = $User->where($map)->page($_GET['p'].',3')->select();
		dump($User->getLastSql());
		$this->assign('user_list',$user_list);// 赋值数据集
		$count      = $User->where($map)->count();// 查询满足要求的总记录数
		$Page       = new \Think\Page($count,3);// 实例化分页类 传入总记录数和每页显示的记录数
		// 设置分页显示
		$Page->setConfig('header', '<li class="rows">共<b>%TOTAL_ROW%</b>条记录&nbsp;第<b>%NOW_PAGE%</b>页/共<b>%TOTAL_PAGE%</b>页</li>');
    	$Page->setConfig('prev', '上一页');
    	$Page->setConfig('next', '下一页');
    	$Page->setConfig('last', '末页');
   	 	$Page->setConfig('first', '首页');
    	$Page->setConfig('theme', '%FIRST%%UP_PAGE%%LINK_PAGE%%DOWN_PAGE%%END%%HEADER%');
		
		$show       = $Page->show();// 分页显示输出
		
		$this->assign('page',$show);// 赋值分页输出
		$this->display(); // 输出模板
    }
	//导入弹窗
	public function import(){
		
		
		$this->display();
		}
	//导入执行方法
	public function import_do(){
		////上传文件
//		 $upload = new \Think\Upload();// 实例化上传类    
//		 $upload->maxSize   =     3145728 ;// 设置附件上传大小    
//		 $upload->exts      =     array('jpg', 'gif', 'png', 'jpeg');// 设置附件上传类型    
//		 $upload->savePath  =      'Uploads/'; // 设置附件上传目录    // 上传文件     
//		 $info   =   $upload->uploadOne($_FILES['file']);    
//		 if(!$info) {// 上传错误提示错误信息        
//		 	$this->error($upload->getError());    
//		 }else{// 上传成功        
//		 	$this->success('上传成功！');    
//		 }
//		
		//验证文件格式
	    $file_array = explode ( ".", $_FILES['file']['name']);
	    $file_extension = strtolower ( array_pop ( $file_array ) );
	    if($file_extension!='xls'&&$file_extension!='xlsx'){
	        $json['status']=0;
	        $json['mess']='只可以上传电子表格，请重新上传文件';
	        $this->ajaxReturn($json);
	    }
		//读取文件数据
        vendor("PHPExcel.PHPExcel.IOFactory"); 
        
        $objReader=\PHPExcel_IOFactory::createReader('Excel5');
        $PHPReader=\PHPExcel_IOFactory::load($_FILES['file']['tmp_name']);
		if( empty($PHPReader) ){
            $this->_json_echo( 0, 'read error!' );
        }
        $currentSheet = $PHPReader->getSheet(0);
        $allRow = $currentSheet->getHighestRow();       

        //最大条数限制
        if( $allRow > 100 ){
            $this->ajaxReturn(array(),'一次最多录入100条',0);
        }
		
		$user=M('user');
		/**从第二行开始输出，因为excel表中第一行为列名*/
		for($currentRow = 2;$currentRow <= $allRow;$currentRow  ){

            $data[account] = $currentSheet->getCellByColumnAndRow(0,$currentRow)->getValue();
            $data[nickname] = $currentSheet->getCellByColumnAndRow(1,$currentRow)->getValue();
            $data[email] = $currentSheet->getCellByColumnAndRow(2,$currentRow)->getValue();
            $data[remark] = $currentSheet->getCellByColumnAndRow(3,$currentRow)->getValue();
			$data[status]=1;
			
			$add_res=$user->add($data);
		}
		if($add_res){
			$this->success('导入成功');
		}else{
			$this->error('导入失败');
		}
		
						
		
	}
	//电子表格导出
	public function export_do(){
	    $map['status']=1;
	    if(!empty($_POST['nickname'])){
	        $map['nickname']  = array('like',"%$_POST[nickname]%");
	    }
	    if(!empty($_POST['email'])){
	        $map['email']  = array('like',"%$_POST[email]%");
	    }
	    
	    $User=M("user");
	    $user_list = $User->where($map)->select();
	    foreach ($user_list as &$user){
	        $user['create_time']=date('Y-m-d H:i:s',$user['create_time']);
	    }

	    $xlsCell  = array('account'=>'账户','nickname'=>'昵称','email'=>'邮箱','create_time'=>'注册时间'
// 	        array('account','账户'),
// 	        array('nickname','昵称'),
// 	        array('email','邮箱'),
// 	        array('create_time','注册时间')
	    );
	    //exportExcel('user', $xlsCell, $user_list);
	    daochuExcel('user', $xlsCell, $user_list);
	    
	}
	
	
	
	
	
    
    public function verify(){
        $Verify = new \Think\Verify();
        $Verify->fontSize = 14;//验证码字体大小（像素） 默认为25
        $Verify->length   = 4;//验证码位数
        $Verify->useNoise = false;//是否添加杂点 默认为true
        $Verify->codeSet = '0123456789';// 设置验证码字符为纯数字
//         $Verify->fontttf = '5.ttf';
//         $Verify->useZh = true;//中文验证码  如果无法正常显示，请确认你的 ThinkPHP/Library/Think/Verify/zhttfs/ 目录下面存在中文字体文件。
        $Verify->entry();
    }
    
    public function check_vcode(){
        $code=I("get.code");
        $this->ajaxReturn(check_verify($code,''));
    }
}