/* * 导出excel表格 * 根据统计标题信息、日期信息和excel表格标示信息 * 先循环统计标题信息在循环每条统计下面按日期的具体信息 * 进行单元格设置 合并 添加样式 */ function _export($tipmsg = null, $date = null, $exceltitle = null){ $this->autorender = false; app::import('vendor', 'phpexcel', array('file' => 'phpexcel.php')); app::import('vendor', 'phpexcelwriter', array('file' => 'phpexcel'.ds.'writer'.ds.'excel2007.php')); // loads phpexcel/writer/excel2007.php $objphpexcel = new phpexcel(); $excelname = 'speiyou_'.date('y-m-d h:i:s').'.xls';//文件名字 // 设置属性 $objphpexcel->getproperties()->setcreator(maarten balliauw) ->setlastmodifiedby(maarten balliauw) ->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); //样式声明 $objactsheet = $objphpexcel->getactivesheet(); //设置样式字体 $sharedstyle1 = new phpexcel_style(); $sharedstyle1->applyfromarray( array('fill' => array('type' => phpexcel_style_fill::fill_solid,'color' => array('argb' => 'ffccffcc')), 'borders' => array('bottom'=> array('style' => phpexcel_style_border::border_thin), 'right'=> array('style' => phpexcel_style_border::border_thin), 'top' => array('style' => phpexcel_style_border::border_thin), 'left' => array('style' => phpexcel_style_border::border_thin)), 'font' => array('bold' => true,'color'=>array('argb' => '00000000')), )); // 添加头部数据 $objphpexcel->setactivesheetindex(0) ->setcellvalue('a1', '模块分类') ->setcellvalue('b1', '点击代码') ->setcellvalue('c1', '代码名称'); foreach ($date as $tdk => $tdv) { $objphpexcel->setactivesheetindex(0) ->setcellvalue($exceltitle[$tdk].'1', $date[$tdk]); } /*添加主要内容 * 分类处理 tou(头部导航) banji(班级分类) fonepage(首页一屏) ftwopage(首页二屏) fthreepage(首页三屏) * bottom(底部) teacher(名师) ad(广告) */ $num = 2;//循环开始标记位 $datenum = count($date) - 1; foreach($tipmsg as $k => $v) { if ($k == 'tou') { $tounum = $num + 1; $newnum = $num-1; $objphpexcel->setactivesheetindex(0) ->setcellvalue('a2','头部导航'); //合并单元格 $objactsheet->mergecells('a'.$num.':c'.$num); $objactsheet->mergecells('a2'.':a'.$newnum); $objphpexcel->getactivesheet()->setsharedstyle($sharedstyle1, a2:a.$newnum); $objphpexcel->setactivesheetindex(0) ->setcellvalue('a'.$num, '本类汇总'); $objphpexcel->getactivesheet()->setsharedstyle($sharedstyle1, a.$num.:.$exceltitle[count($date) - 1].$num); $objactsheet->getstyle('a'.$num)->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); //求和 foreach($date as $datek => $datev) { $objphpexcel->setactivesheetindex(0) ->setcellvalue($exceltitle[$datek].$num,'=sum('.$exceltitle[$datek].'2:'.$exceltitle[$datek].$newnum.')'); } } elseif ($k == 'banji') { $banjinum = $num + 1; $newnum = $num-1; $objphpexcel->setactivesheetindex(0) ->setcellvalue('a'.$tounum,'班级分类'); //合并单元格 $objactsheet->mergecells('a'.$num.':c'.$num); $objphpexcel->getactivesheet()->setsharedstyle($sharedstyle1, a.$tounum.:a.$newnum); $objphpexcel->setactivesheetindex(0) ->setcellvalue('a'.$num, '本类汇总'); $objphpexcel->getactivesheet()->setsharedstyle($sharedstyle1, a.$num.:.$exceltitle[count($date) - 1].$num); $objactsheet->getstyle('a'.$num)->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); //求和 foreach($date as $datek => $datev) { $objphpexcel->setactivesheetindex(0) ->setcellvalue($exceltitle[$datek].$num,'=sum('.$exceltitle[$datek].$tounum.':'.$exceltitle[$datek].$newnum.')'); } } elseif ($k == 'fonepage') { $fonepagenum = $num + 1; $newnum = $num-1; $objphpexcel->setactivesheetindex(0) ->setcellvalue('a'.$banjinum,'首页一屏'); //合并单元格 $objactsheet->mergecells('a'.$num.':c'.$num); $objphpexcel->getactivesheet()->setsharedstyle($sharedstyle1, a.$banjinum.:a.$newnum); $objphpexcel->setactivesheetindex(0) ->setcellvalue('a'.$num, '本类汇总'); $objphpexcel->getactivesheet()->setsharedstyle($sharedstyle1, a.$num.:.$exceltitle[count($date) - 1].$num); $objactsheet->getstyle('a'.$num)->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); //求和 foreach($date as $datek => $datev) { $objphpexcel->setactivesheetindex(0) ->setcellvalue($exceltitle[$datek].$num,'=sum('.$exceltitle[$datek].$banjinum.':'.$exceltitle[$datek].$newnum.')'); } } elseif ($k == 'ftwopage') { $ftwopagenum = $num + 1; $newnum = $num-1; $objphpexcel->setactivesheetindex(0) ->setcellvalue('a'.$fonepagenum,'首页二屏'); //合并单元格 $objactsheet->mergecells('a'.$num.':c'.$num); $objphpexcel->getactivesheet()->setsharedstyle($sharedstyle1, a.$fonepagenum.:a.$newnum); $objphpexcel->setactivesheetindex(0) ->setcellvalue('a'.$num, '本类汇总'); $objphpexcel->getactivesheet()->setsharedstyle($sharedstyle1, a.$num.:.$exceltitle[count($date) - 1].$num); $objactsheet->getstyle('a'.$num)->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); //求和 foreach($date as $datek => $datev) { $objphpexcel->setactivesheetindex(0) ->setcellvalue($exceltitle[$datek].$num,'=sum('.$exceltitle[$datek].$fonepagenum.':'.$exceltitle[$datek].$newnum.')'); } } elseif ($k == 'fthreepage') { $fthreepage = $num + 1; $newnum = $num-1; $objphpexcel->setactivesheetindex(0) ->setcellvalue('a'.$ftwopagenum,'首页三屏'); //合并单元格 $objactsheet->mergecells('a'.$num.':c'.$num); $objphpexcel->getactivesheet()->setsharedstyle($sharedstyle1, a.$ftwopagenum.:a.$newnum); $objphpexcel->setactivesheetindex(0) ->setcellvalue('a'.$num, '本类汇总'); $objphpexcel->getactivesheet()->setsharedstyle($sharedstyle1, a.$num.:.$exceltitle[count($date) - 1].$num); $objactsheet->getstyle('a'.$num)->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); //求和 foreach($date as $datek => $datev) { $objphpexcel->setactivesheetindex(0) ->setcellvalue($exceltitle[$datek].$num,'=sum('.$exceltitle[$datek].$ftwopagenum.':'.$exceltitle[$datek].$newnum.')'); } } elseif ($k == 'bottom') { $bottomnum = $num + 1; $newnum = $num-1; $objphpexcel->setactivesheetindex(0) ->setcellvalue('a'.$fthreepage,'底部'); //合并单元格 $objactsheet->mergecells('a'.$num.':c'.$num); $objphpexcel->getactivesheet()->setsharedstyle($sharedstyle1, a.$fthreepage.:a.$newnum); $objphpexcel->setactivesheetindex(0) ->setcellvalue('a'.$num, '本类汇总'); $objphpexcel->getactivesheet()->setsharedstyle($sharedstyle1, a.$num.:.$exceltitle[count($date) - 1].$num); $objactsheet->getstyle('a'.$num)->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); //求和 foreach($date as $datek => $datev) { $objphpexcel->setactivesheetindex(0) ->setcellvalue($exceltitle[$datek].$num,'=sum('.$exceltitle[$datek].$fthreepage.':'.$exceltitle[$datek].$newnum.')'); } } elseif ($k == 'teacher') { $teachernum = $num + 1; $newnum = $num-1; $objphpexcel->setactivesheetindex(0) ->setcellvalue('a'.$bottomnum,'名师频道'); //合并单元格 $objactsheet->mergecells('a'.$num.':c'.$num); $objphpexcel->getactivesheet()->setsharedstyle($sharedstyle1, a.$bottomnum.:a.$newnum); $objphpexcel->setactivesheetindex(0) ->setcellvalue('a'.$num, '本类汇总'); $objphpexcel->getactivesheet()->setsharedstyle($sharedstyle1, a.$num.:.$exceltitle[count($date) - 1].$num); $objactsheet->getstyle('a'.$num)->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); //求和 foreach($date as $datek => $datev) { $objphpexcel->setactivesheetindex(0) ->setcellvalue($exceltitle[$datek].$num,'=sum('.$exceltitle[$datek].$bottomnum.':'.$exceltitle[$datek].$newnum.')'); } } elseif ($k == 'ad') { $adnum = $num + 1; $newnum = $num-1; $objphpexcel->setactivesheetindex(0) ->setcellvalue('a'.$teachernum,'通栏广告'); //合并单元格 $objactsheet->mergecells('a'.$num.':c'.$num); $objphpexcel->getactivesheet()->setsharedstyle($sharedstyle1, a.$teachernum.:a.$newnum); $objphpexcel->setactivesheetindex(0) ->setcellvalue('a'.$num, '本类汇总'); $objphpexcel->getactivesheet()->setsharedstyle($sharedstyle1, a.$num.:.$exceltitle[count($date) - 1].$num); $objactsheet->getstyle('a'.$num)->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); //求和 foreach($date as $datek => $datev) { $objphpexcel->setactivesheetindex(0) ->setcellvalue($exceltitle[$datek].$num,'=sum('.$exceltitle[$datek].$teachernum.':'.$exceltitle[$datek].$newnum.')'); } } else { $objphpexcel->setactivesheetindex(0) ->setcellvalue('a'.$num, '') ->setcellvalue('b'.$num, $v['codetip']) ->setcellvalue('c'.$num, $v['codename']); foreach($date as $tdk => $tdv) { $objphpexcel->setactivesheetindex(0) ->setcellvalue($exceltitle[$tdk].$num, $v['nums'][$tdk]); } } ++$num; } //添加样式 $objphpexcel->getactivesheet()->setsharedstyle($sharedstyle1, a1:.$exceltitle[count($date) - 1].1);// $objphpexcel->getactivesheet()->setsharedstyle($sharedstyle1, a1:a.$num); //冻结列 $objphpexcel->getactivesheet()->freezepane('a1'); $objphpexcel->getactivesheet()->freezepane('b1'); $objphpexcel->getactivesheet()->freezepane('c1'); $objphpexcel->getactivesheet()->freezepane('d2'); //设置居中 $objactsheet->getstyle('a2')->getalignment()->setvertical(phpexcel_style_alignment::vertical_center); $objactsheet->getstyle('a'.$num)->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); //设置列宽 $objphpexcel->getactivesheet()->getcolumndimension('c')->setwidth(20); //设置底部总数统计信息 $ttotal = $tounum - 1; $banjitotal = $banjinum - 1; $fototal = $fonepagenum - 1; $fttotal = $ftwopagenum - 1; $frtotal = $fthreepage - 1; $btotal = $bottomnum - 1; $chtotal = $teachernum - 1; $adtotal = $adnum - 1; $objphpexcel->setactivesheetindex(0) ->setcellvalue('a'.$num, '总计') ->setcellvalue('b'.$num, '') ->setcellvalue('c'.$num, '日统计'); foreach ($date as $totalk => $totalv) { $objphpexcel->setactivesheetindex(0) ->setcellvalue($exceltitle[$totalk].$num, '=sum('.$exceltitle[$totalk].$ttotal.','.$exceltitle[$totalk].$banjitotal.','.$exceltitle[$totalk].$fototal.','.$exceltitle[$totalk].$fttotal.','.$exceltitle[$totalk].$frtotal.','.$exceltitle[$totalk].$btotal.','.$exceltitle[$totalk].$chtotal.','.$exceltitle[$totalk].$adtotal.')'); } $objphpexcel->getactivesheet()->setsharedstyle($sharedstyle1, 'a'.$num.':'.$exceltitle[$datenum].$num); $objphpexcel->setactivesheetindex(0) ->setcellvalue('b'.$num, '=sum(d'.$num.':'.$exceltitle[$datenum].$num.')'); $objactsheet->getstyle('a'.$num)->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); $objactsheet->getstyle('c'.$num)->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); // 设置切换标签的名字 $objphpexcel->getactivesheet()->settitle('培优网用户点击统计'); //直接输出到浏览器 header('content-type: application/vnd.ms-excel'); header('content-disposition: attachment;filename='.$excelname.''); header('cache-control: max-age=0'); $objwriter = phpexcel_iofactory::createwriter($objphpexcel, 'excel5'); $objwriter->save('php://output'); exit; }
?