基于PHPexecl类生成复杂的报表表头示例
程序员文章站
2024-04-02 12:19:40
本文实例讲述了基于phpexecl类生成复杂的报表表头。分享给大家供大家参考,具体如下:
以前一直有需求,能把execl里面的数据导入数据库,并且把数据库里面的数据导出到...
本文实例讲述了基于phpexecl类生成复杂的报表表头。分享给大家供大家参考,具体如下:
以前一直有需求,能把execl里面的数据导入数据库,并且把数据库里面的数据导出到execl中。
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("\r\n"); $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);
得到的效果也基本符合需求:
更多关于php相关内容感兴趣的读者可查看本站专题:《php操作office文档技巧总结(包括word,excel,access,ppt)》、《php数组(array)操作技巧大全》、《php排序算法总结》、《php常用遍历算法与技巧总结》、《php数据结构与算法教程》、《php程序设计算法总结》、《php数学运算技巧总结》、《php正则表达式用法总结》、《php运算与运算符用法总结》、《php字符串(string)用法总结》及《php常见数据库操作技巧汇总》
希望本文所述对大家php程序设计有所帮助。