thinkphp 导出导入表格 PHPExcel

PHPExcel类是php一个excel表格处理插件了，下面我来给大家介绍利用PHPExcel类来导入与导出excel表格的应用方法，有需要了解的朋友不防参考参考(PHPExcel自己百度下载这里不介绍了)。

下载phpExcel插件的地址：https://files.cnblogs.com/williamhu/Classes.rar  

加压后放到\Extend\Vendor\里，然后就是编写代码了：

 /**
     * phpEscel导出用户表
     * @author H.J.H
     * date  2014.8.5 11:10
     */
    function pushExcel(){
    	
    	if($_SESSION['admin']['role_id'] != 1) {
    		$this->error('无权限操作');
    	}
    	$total=$this->_mod->count('id');//总数
    	$res=$this->_mod->field('id,share_id,username,mobile,gender,address,last_time')->select();
    	
    	Vendor("Classes.PHPExcel");
    	Vendor("Classes.PHPExcel.php");

    	//创建处理对象实例
    	$objPhpExcel=new PHPExcel();
    	$objPhpExcel->getActiveSheet()->getDefaultColumnDimension()->setAutoSize(true);//设置单元格宽度
    	//设置表格的宽度  手动
    	$objPhpExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
    	$objPhpExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
    	$objPhpExcel->getActiveSheet()->getColumnDimension('I')->setWidth(15);
    	$objPhpExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
    	//设置标题
    	$rowVal = array(0=>'编号',1=>'级', 2=>'用户名', 3=>'手机号', 4=>'性别',5=>'地址',6=>'推荐人数',7=>'登录时间',8=>'会员总数');
    	foreach ($rowVal as $k=>$r){
    		$objPhpExcel->getActiveSheet()->getStyleByColumnAndRow($k,1)
    		->getFont()->setBold(true);//字体加粗
    		$objPhpExcel->getActiveSheet()->getStyleByColumnAndRow($k,1)->
    		getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//文字居中
    		$objPhpExcel->getActiveSheet()->setCellValueByColumnAndRow($k,1,$r);
    	}
    	$objPhpExcel->getActiveSheet()->setCellValue('A1', '编号');
    	$objPhpExcel->getActiveSheet()->setCellValue('B1', '用户名');
    	$objPhpExcel->getActiveSheet()->setCellValue('C1', '推荐者');
    	$objPhpExcel->getActiveSheet()->setCellValue('D1', '手机号');
    	$objPhpExcel->getActiveSheet()->setCellValue('E1', '性别');
    	$objPhpExcel->getActiveSheet()->setCellValue('F1', '地址');
    	$objPhpExcel->getActiveSheet()->setCellValue('G1', '推荐人数');
    	$objPhpExcel->getActiveSheet()->setCellValue('H1', '最后登录时间');
    	$objPhpExcel->getActiveSheet()->setCellValue('I1', '总会员数');
    	//设置当前的sheet索引 用于后续内容操作
    	$objPhpExcel->setActiveSheetIndex(0);
    	$objActSheet=$objPhpExcel->getActiveSheet();
    	//设置当前活动的sheet的名称
    	$title="公司用户录";
    	$objActSheet->setTitle($title);
    	//设置单元格内容
    	foreach($res as $k => $v)
    	{   
    		$v['count']=$this->_mod->where('share_id='.$v['id'])->count('id');//推荐人数
    		$v['tname']=$this->_mod->where('id='.$v['share_id'])->getfield('username');//推荐人
    		
    		if($v['gender']==1){
    			$v['gender']="男";
    		}elseif ($v['gender']==0){
    			$v['gender']="女";
    		}else {
    			$v['gender']="";
    		}
    		$v['last_time']=$v['last_time']==0 ? '' : date('Y-m-d H:i',$v['last_time']);
    		$num=$k+2;
    		$objPhpExcel->setActiveSheetIndex(0)
    		//Excel的第A列，uid是你查出数组的键值，下面以此类推
    		->setCellValue('A'.$num, $v['id'])
    		->setCellValue('B'.$num, $v['username'])
    		->setCellValue('C'.$num, $v['tname'])
    		->setCellValue('D'.$num, $v['mobile'])
    		->setCellValue('E'.$num, $v['gender'])
    		->setCellValue('F'.$num, $v['address'])
    		->setCellValue('G'.$num, $v['count'])
    		->setCellValue('H'.$num, $v['last_time']);
    		
    	}
    	$objPhpExcel->setActiveSheetIndex(0)->setCellValue('I2', $total);
    	$title="公司用户录";
    	$name=date('Y-m-d');//设置文件名
    	header("Content-Type: application/force-download");
    	header("Content-Type: application/octet-stream");
    	header("Content-Type: application/download");
    	header("Content-Transfer-Encoding:utf-8");
    	header("Pragma: no-cache");
    	header('Content-Type: application/vnd.ms-excel');
    	header('Content-Disposition: attachment;filename="'.$title.'_'.urlencode($name).'.xls"');
    	header('Cache-Control: max-age=0');
    	$objWriter = PHPExcel_IOFactory::createWriter($objPhpExcel, 'Excel5');
    	$objWriter->save('php://output');
    }

 

还有个小小的提示，那就是：

 

在thinkphp中用phpexcel导出数据错误
<b>Fatal error</b>:  print_r() [&lt;a href='ref.outcontrol'&gt;ref.outcontrol&lt;/a&gt;]: Cannot use output buffering in
output buffering display handlers in <b>D:\www\bjydf\ThinkPHP\Common\common.php</b> on line <b>601</b><br />

导出文件打开显示以上错误，数据没有到成功，实质原因是在thinkphp配置文件中加上

'OUTPUT_ENCODE' =>  false,

是因为thinkphp3.0以后默认开启了”网页压缩输出”。关闭就好了

 

    //导入数据 2014.9.19 H.J.H
    public function out_put(){
        if($_POST['leadExcel'] == "true"){
             
            $tmp_name = $_FILES['inputExcel']['tmp_name'];
             
            Vendor("Classes.PHPExcel");
            Vendor("Classes.PHPExcel.php");
            Vendor("Classes.PHPExcel.IOFactory");
            Vendor("Classes.PHPExcel.Reader.Excel5");
            $objReader = PHPExcel_IOFactory::createReader('Excel5');
            $objPHPExcel = $objReader->load($tmp_name);
            $sheet = $objPHPExcel->getSheet(0);
            $highestRow = $sheet->getHighestRow(); // 取得总行数
            $highestColumn = $sheet->getHighestColumn(); // 取得总列数
            $k = 0;
            for($j=2;$j<=$highestRow;$j++){
    
                $data['username'] = $objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();
                $data['mobile']   = $objPHPExcel->getActiveSheet()->getCell("C".$j)->getValue();
                $data['address']  = $objPHPExcel->getActiveSheet()->getCell("D".$j)->getValue();
                $data['city_id']  = $objPHPExcel->getActiveSheet()->getCell("F".$j)->getValue();
                 
                $data['share_id']=1332;
                $data['password']='96e79218965eb72c92a549dd5a330112';
                $data['gender']=2;
                $data['last_time']=time()-60*60*24*$j;
                $data['reg_time']=time()-60*60*24*$j;
                $data['status']=1;
                 
                $last_id = $this->_mod->add($data);//生成id
                if($last_id){
                    echo "第".$j."行导入成功,fph_user表第：".$last_id."条 ";
                }else {
                    echo "第".$j."行导入失败<br/>";
                }
    
            }
             
        }
    }