基于PHPexecl类生成复杂的报表表头示例

require_once dirname(__file__) . '/../classes/phpexcel/iofactory.php';
class phpexeclcore extends phpexcel_iofactory{
 public static function summercreateexecl($head,$data)
 {
 self::summercreateexeclhead($head,$data,"excel2007");
 }
 public static function summerreadexecl($dir)
 {
 if(!file_exists($dir))
 {
 echo "execl not exist";
 }
 else
 {
 $phpexeclobj = self::load($dir);
 $sheetcount = $phpexeclobj->getsheetcount(); //得到execl中包含的sheet工作簿的数量
 for($i=0;$i<$sheetcount;$i++)
 {
 $activesheet = $phpexeclobj->getsheet($i);
 $highestrow = $activesheet->gethighestrow(); // 取得总列数
 $allcolumn = $activesheet->gethighestcolumn();
 //通过嵌套循环来读取sheet工作簿里面的内容
 for($col='a';$col<$allcolumn;$col++)
 {
 for($row=1;$row<$highestrow;$row++)
 {
 $data[$col][$row] = $activesheet->getcell($col.$row)->getvalue();
 }
 }
 }
 }
 return $data;
 }
 /*
 * 将数据写入到数据表中
 * $data array 表示要插入进execl数据
 * $ruledata array 表示数据格式的规则数组
 * $i int 表示从第几行起的插入数据
 * **/
 public static function summerinsertdatetoexecl($sheet,$head,$data,$n=3,$ruledata=array())
 {
 $simplehead = self::gethead($head);
 $row = $n;
 foreach($data as $key=>$valuearr)
 {
 $m = 0;
 foreach($valuearr as $k=>$v)
 {
 $startcol = phpexcel_cell::stringfromcolumnindex($m).$row;
 $sheet->getcell($startcol)->setvalue($v);
 $sheet->getstyle($startcol)->getalignment()->applyfromarray(
 array(
 'horizontal'=> phpexcel_style_alignment::horizontal_center,
 'vertical' => phpexcel_style_alignment::vertical_center,
 'rotation' => 0,
 'wrap' => true,
 )
 );
 if(isset($simplehead[$k]['col']))
 {
 $m = $m + $simplehead[$k]['col']-1;
 $endcol = phpexcel_cell::stringfromcolumnindex($m).$row;
 $sheet->mergecells($startcol.":".$endcol);
 }
 $m++;
 $type = false;
 if(isset($simplehead[$k]['type']))
 {
 $type = $simplehead[$k]['type'];
 $allowarray = $simplehead[$k]['allowarray'];
 }
 //设置单元格的数据验证
 if($type)
 {
 switch ($type)
 {
 case 'list':
 self::setselectionrange($sheet, $startcol,$allowarray);
 break;
 case 'range':
 self::setvaluerange($sheet, $startcol,$allowarray);
 break;
 }
 }
 }
 $row ++ ;
 }
 }
 /*
 * 生成execl单元格备注
 * $sheet 当前的工作簿对象
 * $cell 需要设置属性的单元格
 * $content 备注内容
 * */
 private static function setcomment($sheet,$cell,$content)
 {
 $sheet->getcomment($cell)->setauthor('4399om');
 $objcommentrichtext = $sheet->getcomment($cell)->gettext()->createtextrun('4399om:');
 $objcommentrichtext->getfont()->setbold(true);
 $sheet->getcomment($cell)->gettext()->createtextrun("rn");
 $sheet->getcomment($cell)->gettext()->createtextrun($content);
 $sheet->getcomment($cell)->setwidth('100pt');
 $sheet->getcomment($cell)->setheight('100pt');
 $sheet->getcomment($cell)->setmarginleft('150pt');
 $sheet->getcomment($cell)->getfillcolor()->setrgb('eeeeee');
 }
 /*
 * 现在单元格的有效数据范围，暂时仅限于数字
 * $sheet 当前的工作簿对象
 * $cell 需要设置属性的单元格
 * $valuerange array 允许输入数组的访问
 */
 private static function setvaluerange($sheet,$cell,$valuerange)
 {
 //设置单元格的的数据类型是数字，并且保留有效位数
 $sheet->getstyle($cell)->getnumberformat()->setformatcode(phpexcel_style_numberformat::format_number_00);
 $valuerange = explode(",",$valuerange);
 //开始数值有效访问设定
 $objvalidation = $sheet->getcell($cell)->getdatavalidation();
 $objvalidation->settype( phpexcel_cell_datavalidation:: type_whole );
 $objvalidation->seterrorstyle( phpexcel_cell_datavalidation:: style_stop );
 $objvalidation->setallowblank(true);
 $objvalidation->setshowinputmessage( true); //设置显示提示信息
 $objvalidation->setshowerrormessage( true); //设置显示错误信息
 $objvalidation->seterrortitle('输入错误'); //错误标题
 $objvalidation->seterror('请输入数据范围在从'.$valuerange[0].'到'.$valuerange[1].'之间的所有值'); //错误内容
 $objvalidation->setprompttitle('允许输入'); //设置提示标题
 $objvalidation->setprompt('请输入数据范围在从'.$valuerange[0].'到'.$valuerange[1].'之间的所有值'); //提示内容
 $objvalidation->setformula1($valuerange['0']); //设置最大值
 $objvalidation->setformula2($valuerange['1']); //设置最小值
 }
 private static function outinputheader($objwriter)
 {
 $filename = str_replace('.php', '.xlsx', pathinfo(__file__, pathinfo_basename));
 header("content-type: application/force-download");
 header("content-type: application/octet-stream");
 header("content-type: application/download");
 header('content-disposition:inline;filename="'.$filename.'"');
 header("content-transfer-encoding: binary");
 header("last-modified: " . gmdate("d, d m y h:i:s") . " gmt");
 header("cache-control: must-revalidate, post-check=0, pre-check=0");
 header("pragma: no-cache");
 $objwriter->save('php://output');
 exit;
 }
 //数据控制，设置单元格数据在一个可选方位类
 private static function setselectionrange($sheet,$cell,$rangestr,$title="数据类型")
 {
 $objvalidation = $sheet->getcell($cell)->getdatavalidation();
 $objvalidation -> settype(phpexcel_cell_datavalidation::type_list)
 -> seterrorstyle(phpexcel_cell_datavalidation::style_stop)
 -> setallowblank(true)
 -> setshowinputmessage(true)
 -> setshowerrormessage(true)
 -> setshowdropdown(true)
 -> seterrortitle('输入的值有误')
 -> seterror('您输入的值不在下拉框列表内.')
 -> setprompttitle('"'.$title.'"')
 -> setformula1('"'.$rangestr.'"');
 }
 /*
 * 构建表头
 * */
 public static function recursioncreateexecl($head,$data)
 {
 $phpexecl = new phpexcel();
 $objwriter = self::createwriter($phpexecl, 'excel2007');
 $phpexecl->getproperties()->setcreator("4399om")
 ->setlastmodifiedby("summer")
 ->settitle("office 2007 xlsx test document")
 ->setsubject("office 2007 xlsx test document")
 ->setdescription("test document for office 2007 xlsx, generated using php classes.")
 ->setkeywords("office 2007 openxml php")
 ->setcategory("test result file");
 $phpexecl->setactivesheetindex(0);
 $sheet = $phpexecl->getactivesheet();
 self::handleheadtonode($sheet, $head,1,0,0);
 self::summerinsertdatetoexecl($sheet,$head,$data,4);
 self::outinputheader($objwriter);
 }
 private static function handleheadtonode($sheet,$head,$beginrow,$col,$startcol)
 {
 foreach($head as $key=>$cells)
 {
 $row = $beginrow; //表示行
 $begincol = phpexcel_cell::stringfromcolumnindex($col).$row;
 $sheet->getcell($begincol)->setvalue($cells['value']);
 //设置表格样式
 $sheet->getstyle($begincol)->getalignment()->applyfromarray(
 array(
 'horizontal'=> phpexcel_style_alignment::horizontal_center,
 'vertical' => phpexcel_style_alignment::vertical_center,
 'rotation' => 0,
 'wrap' => true,
 )
 );
 $sheet->getstyle($begincol)->getfont()->getcolor()->setargb(phpexcel_style_color::color_darkgreen);
 //设置单元格的宽度
 if(isset($cells['width']))
 {
 $cell = $sheet->getcolumndimension(phpexcel_cell::stringfromcolumnindex($col));
 $cell->setwidth($cells['width']);
 }
 //哥元素打上标记
 if(isset($cells['content']))
 {
 self::setcomment($sheet, $begincol, $cells['content']);
 }
 $merge = false; //合并单元格
 if(isset($cells['col']))
 {
 $col += $cells['col']-1;
 $merge = true;
 }
 if(isset($cells['row']))
 {
 $row += $cells['row']-1;
 $merge = true;
 }
 if($merge)
 {
 $endcol = phpexcel_cell::stringfromcolumnindex($col).$row;
 $sheet->mergecells($begincol.":".$endcol);
 }
 $row ++;
 $col ++;
 //表示有存在孩子节点
 if(isset($cells['children']) && is_array($cells['children'])){
 $cols = $startcol;
 if(!self::isexistchildren($cells['children']))
 {
 $cols = $col-2;
 $startcol = $col;
 }
 self::handleheadtonode($sheet,$cells['children'],$row,$cols,$startcol);
 }else{
 $startcol = $col;
 }
 }
 }
 //判断自己的孩子节点中是否存在孙子节点
 private static function isexistchildren($data)
 {
 foreach($data as $key=>$value)
 {
 if(isset($value['children']) && is_array($value['children']))
 {
 return true;
 }
 }
 return false;
 }
 //获取底层数据
 private static function gethead($head,&$node=array())
 {
 foreach($head as $key=>$value)
 {
 if(isset($value['children']) && is_array($value['children']))
 {
 self::gethead($value['children'],$node);
 }
 else
 {
 $node[] = $value;
 }
 }
 return $node;
 }
}
$head = array(
 array('value'=>'姓名','col'=>2,'row'=>2,'width'=>20,'type'=>'list','allowarray'=>'php开发工程师,php开发'),
 array('value'=>'第一天','col'=>2,'row'=>1,'width'=>20,'content'=>'2014-12-29号',
 'children'=>
 array(
 array('value'=>'上午','col'=>1,'width'=>20,'type'=>'range','allowarray'=>'10,100'),
 array('value'=>'下午','width'=>20),
 ),
 ),
 array('value'=>'第二天','col'=>2,'row'=>1,'width'=>20,
 'children'=>
 array(
 array('value'=>'上午','width'=>20),
 array('value'=>'下午','width'=>20),
 ),
 ),
);
$data = array(
 array('php开发工程师','12','吃饭1','睡觉1','起床刷牙2','吃饭睡觉2'),
 array('php开发工程师','25','吃饭1','睡觉1','起床刷牙2','吃饭睡觉2'),
 array('php开发工程师','50','吃饭1','睡觉1','起床刷牙2','吃饭睡觉2'),
 array('php开发工程师','99','吃饭1','睡觉1','起床刷牙2','吃饭睡觉2'),
 array('php开发工程师','10','吃饭1','睡觉1','起床刷牙2','吃饭睡觉2'),
 );
$node = phpexeclcore::recursioncreateexecl($head,$data);