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>