PHPExcel 运用

在页面显示一下效果， 并写入excel

control层，重要的是控制层这个写入excel方法

function team(){
  	$matchid = $_SESSION['adminmatch'];
		error_reporting(E_ALL);     
		set_include_path(get_include_path() . PATH_SEPARATOR . 'download/Classes/'); 
		include 'PHPExcel.php';   
		include 'PHPExcel/Writer/Excel2007.php'; 
		  
		//echo date('H:i:s') . " 创建新的PHPExcel对象<br/>";   
		$objPHPExcel = new PHPExcel();   
		   
		// 设置导出excel格式
		//echo date('H:i:s') . " Set properties\n";   
		$objPHPExcel->getProperties()->setCreator("Maarten Balliauw");   
		$objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");   
		$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");   
		$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");   
		$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");   
		$objPHPExcel->getProperties()->setKeywords("office 2007 openxml php");   
		$objPHPExcel->getProperties()->setCategory("Test result file");   
		   
		// 添加数据  
		//echo date('H:i:s') . " Add some data\n";   
		$objPHPExcel->setActiveSheetIndex(0);   
		$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('F2', '男');
		$objPHPExcel->getActiveSheet()->setCellValue('G2', '女');
		
		//==============================设置单元格格式=====================================
		$objStyleA1 = $objPHPExcel->getActiveSheet()->getStyle('A1:l1');  
    $objAlignA1 = $objStyleA1->getAlignment();  
		$objAlignA1->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);    //左右居中  
    $objAlignA1->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  //上下居中  
    //字体及颜色  
    $objFontA1 = $objStyleA1->getFont();  
    $objFontA1->setName('黑体');  
    $objFontA1->setSize(12);  
		//==============================合并单元格=====================================
		$objActSheet = $objPHPExcel->getActiveSheet();
		$objActSheet->mergeCells('A1:A2'); 
		$objActSheet->mergeCells('B1:B2');
		$objActSheet->mergeCells('C1:C2');
		$objActSheet->mergeCells('D1:D2');
		$objActSheet->mergeCells('E1:E2');
		$objActSheet->mergeCells('F1:G1'); 
		//$B = "B3:B6";
		//$objActSheet->mergeCells($B); 
		//$objActSheet->mergeCells('G1:G2');
		
		//--------------------------写入数据到excel--------------开始------------------------------------------ 
		//$arrGroup = $this->Search_model->getGroupEcel();
		//var_dump($arrGroup[0]);
		//var_dump(count($arrGroup));
		//$intCount = count($arrGroup);

		//得到所有本赛事的报名分组数据，循环遍历进行重构数组数据
		//本赛事队伍分组后的信息group by team.teamid
		$teamInfo = $this->Search_model->getteaminfo($matchid);  
		//本赛事队伍信息，没有group by team.teamid的
		$arrAllTeam = $this->Search_model->getClothSize($matchid); 

		$NewarrTeam = $this->Search_model->constructArrteam($teamInfo,$arrAllTeam);
		
		//var_dump($NewarrCloth);
		//die();
		//var_dump($arrClothSize[0]);
		//var_dump(count($arrClothSize));
		//var_dump($NewarrCloth[]["realname"]);
		//var_dump($NewarrCloth['1']['headname']);

		$intCount = count($NewarrTeam);

		if($NewarrTeam){
			$intrecord = 2;
			$nu = 0;
			foreach($NewarrTeam as $key => $value){
				//var_dump ($value["realname"]);
				$intrecord= $intrecord+1;
				$nu = $nu + 1;
				$objPHPExcel->getActiveSheet()->setCellValue('A'.$intrecord, $nu);   
				$objPHPExcel->getActiveSheet()->setCellValue('B'.$intrecord, $value['teamname']);   
				$objPHPExcel->getActiveSheet()->setCellValue('C'.$intrecord, $value['headname']);
				$objPHPExcel->getActiveSheet()->setCellValue('D'.$intrecord, "'".$value['telphone']);
				$objPHPExcel->getActiveSheet()->setCellValue('E'.$intrecord, $value['nucount']);
				$objPHPExcel->getActiveSheet()->setCellValue('F'.$intrecord, " ".$value['mname']);
				$objPHPExcel->getActiveSheet()->setCellValue('G'.$intrecord, " ".$value['fnmae']);
				
			}
			$huang = $intrecord;
			$intrecord= $intrecord+1;
			$objPHPExcel->getActiveSheet()->setCellValue('A'.$intrecord, "汇总求和");
			$objPHPExcel->getActiveSheet()->setCellValue('E'.$intrecord, "=SUM(E3:E".$huang.")");
			$objPHPExcel->getActiveSheet()->setCellValue('F'.$intrecord, "=SUM(F3:F".$huang.")");
			$objPHPExcel->getActiveSheet()->setCellValue('G'.$intrecord, "=SUM(G3:G".$huang.")");
		
			$strTableData="";
			
			for ($i = 1;$i <= 100;$i++){
				if(!isset($NewarrTeam[$i])){break;}
				$strTableData = $strTableData."<tbody><tr><td>".$i."</td><td>".$NewarrTeam[$i]['teamname']."</td><td>".$NewarrTeam[$i]['headname']."</td><td>".
				$NewarrTeam[$i]['telphone']."</td><td>".$NewarrTeam[$i]['nucount']."</td><td>".$NewarrTeam[$i]['mname']."</td><td>".
				$NewarrTeam[$i]['fnmae']."</td></tr></tbody>";
			}
			
		}else{
			$strTableData="";
		}
		//--------------------------写入数据到excel--------------结束------------------------------------------   
		// 重命名sheet   
		//echo "<br/>".date('H:i:s') . " 重命名sheet<br/>";   
		$objPHPExcel->getActiveSheet()->setTitle('sheet1');   
		   
		// 第一张工作表组活跃表索引,所以Excel打开这是第一个表   
		$objPHPExcel->setActiveSheetIndex(0);   
		   
		// Save Excel 2007 file  //保存为 Excel2007格式
		//echo date('H:i:s') . " 保存为Excel2007格式<br/>";   
		$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
		//var_dump(__FILE__); 
		//var_dump($_SERVER['DOCUMENT_ROOT']);
		$excelname = "team_summary".date('YmdHis');
		$path = $_SERVER['DOCUMENT_ROOT']."/download/excel/".$excelname.".xlsx";
		$pathgbk = mb_convert_encoding($path, "GBK", "UTF-8");
		//echo "<br/>".date('H:i:s') . "保存文件开始>>".$path;  
		//$objWriter->save(str_replace("/","\\",$pathgbk));
		$objWriter->save($path);
		//$objWriter->save(str_replace('application\\controllers\\down.php', 'download\\excel\\text1.xlsx', __FILE__));     
		//echo "<br/>".date('H:i:s') . "保存文件>>".$path; 
		
		$strTable = "队伍统计信息生成成功。生成文件时间：".date('Y-m-d H:i:s',time())."。";
		//$strTableData="";
		
		$data['strTitle'] = "导出队伍统计信息";
		$data['tableheader'] = "<thead><tr>
					      <th rowspan = \"2\">序号</th>
					      <th rowspan = \"2\">队伍名称</th>
					      <th rowspan = \"2\">领队</th>
					      <th rowspan = \"2\">联系电话</th>
					      <th rowspan = \"2\">人数</th>
					      <th colspan=\"2\">队员</th>
					      <tr><td>男</td><td>女</td></tr>
				      </tr><thead>

				      ";
		$data['strTable'] = $strTable;
		$data['excelname'] = $excelname;
		$data['strTableData'] = $strTableData;
		$data['intCount'] = $intCount;
		$this->load->view('header');
		$this->load->view('downexcel',$data);
		$this->load->view('footer');

  }
}

view层

<div tyle=" width:100%;   margin-left:auto; margin-right:auto;">
		<ul class="breadcrumb">
			<li>
				<a href="<?=base_url()?>admin">首页</a> <span class="divider">/</span>
			</li>
			<li>
				<a href="#">导出EXCEL</a>
			</li>
		</ul>
                
	</div>

	<div class="row-fluid">

        <div class="box span12">				
			<div class="box-header well">
				<h2><i class="icon-th"></i> 导出的数据</h2>
			</div>
			<div class="box-content">
				<p><h4><?=$strTable ?></h4></p>
				<a  class="btn btn-large btn-primary" href="<?=base_url() ?>../download/excel/<?=$excelname ?>.xlsx">请点击此处下载</a>
			</div>
		</div>
	</div>
	<!----------------------------数据预览----------------------------->
    <div class="row-fluid sortable">		
	    <div class="box span12">
		    <div class="box-header well" data-original-title>
			    <h2><i class="icon-user"></i> 数据预览</h2>
			    <div class="box-icon">
				    <a href="#" class="btn btn-minimize btn-round"><i class="icon-chevron-up"></i></a>
			    </div>
		    </div>
		    <div class="box-content">
			    <table class="table table-striped table-bordered bootstrap-datatable ">
			      <thead>
				      <?=$tableheader ?>
			      </thead>   
			      <tbody>
				   <?=$strTableData ?>
			      </tbody>
		      </table>            
		    </div>
	    </div><!--/span-->

    </div>