/*public function export(){ $m = M(); $ulist = getPuid($_POST['uid']); if($_POST['action']){ $sql = "select u.uid,u.user,u.upath,p.padnum from uc_users as u inner join uc_users_pad as p on u.uid = p.uid where u.uid in ($ulist) order by u.upath,u.uid"; $res = $m->query($sql); if(!empty($res)){ foreach($res as $k=>$v){ $arr = explode('-',$v['upath']); $id = $arr[count($arr)-1]; $user = M("uc_users")->where("uid = $id")->getField("user"); $res[$k]['fuser'] = $user; for($i=$_POST['starttime'];$i<=$_POST['endtime'];$i++){ $sqls = "select count(*) as c from ph_sinstall where uid ='$v[uid]' and padnum = '".$v['padnum']."' and itime like '%$i%'"; $res1 = $m->query($sqls); $res[$k]['actnum'][$i] = $res1[0]['c']; } } } require_once './ThinkPHP/Vendor/PHPExcel.php'; require_once './ThinkPHP/Vendor/PHPExcel/IOFactory.php'; require_once './ThinkPHP/Vendor/PHPExcel/Reader/Excel5.php'; $arr_time = explode('-',$_POST['starttime']); //设置文件的保存名称 $title = $arr_time[0].'年'.$arr_time[1].'月'; $key_word = $arr_time[0].'-'.$arr_time[1].'-'; $month = $arr_time[1]; $objPHPExcel = new PHPExcel(); //设置excel 的标题 $objPHPExcel->getProperties()->setTitle("$month月份门店软件安装明细"); //冻结表头 $objPHPExcel->getActiveSheet()->FreezePane('D4'); //合并表格 $objPHPExcel->getActiveSheet()->mergeCells('A2:AI2'); //设置宽度 $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(22); $t = $month."月份门店软件安装明细"; $arr = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI'); $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A2', $t) ->setCellValue('A3', '大区') ->setCellValue('B3', '门店') ->setCellValue('C3', '平板'); $count = count($arr); foreach($arr as $a => $b){ if(($a >2) && ($a != ($count - 1))){ $objPHPExcel->setActiveSheetIndex(0)->setCellValue($b.'3', $a-2); $objPHPExcel->getActiveSheet()->getColumnDimension($b)->setWidth(5); } } $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AI3', '总计'); $fuser = $res[0]['fuser']; $user = $res[0]['user']; $count_pad = 0; $count_stores = 1; $i = 0; $user_num = 0; $total_stores = 0; $total_pad = 0; foreach($res as $key => $nTem){ $n=$key+4; if(($nTem['fuser'] == $fuser)){ $count_pad++; if($nTem['user']!=$user){ $objPHPExcel->getActiveSheet()->mergeCells("B".($n+$i-$user_num).":B".($n+$i-1)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue("B".($n+$i-$user_num),$user); $objPHPExcel->getActiveSheet()->mergeCells("C".($n+$i-$user_num).":C".($n+$i-1)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue("C".($n+$i-$user_num),$user_num); $count_stores ++; $user = $nTem['user']; $user_num = 1; }else{ $user_num ++; } }else{ $objPHPExcel->getActiveSheet()->mergeCells("B".($n+$i-$user_num).":B".($n+$i-1)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue("B".($n+$i-$user_num),$user); $objPHPExcel->getActiveSheet()->mergeCells("C".($n+$i-$user_num).":C".($n+$i-1)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue("C".($n+$i-$user_num),$user_num); $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.($n+$i), '区域小计') ->setCellValue('B'.($n+$i), $count_stores) ->setCellValue('C'.($n+$i), $count_pad); $total_stores += $count_stores; $total_pad += $count_pad; foreach($arr as $a => $b){ if(($a >2)){ $objPHPExcel->setActiveSheetIndex(0)->setCellValue($b.($n+$i), "=SUM(".$b.($n+$i-$count_pad).":".$b.($n+$i-1).")"); } } //填充颜色 $objPHPExcel->getActiveSheet()->getStyle('A'.($n+$i).':AI'.($n+$i))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A'.($n+$i).':AI'.($n+$i))->getFill()->getStartColor()->setARGB('#FF9900'); $objPHPExcel->getActiveSheet()->getStyle('A'.($n+$i).':AI'.($n+$i))->getFont()->setBold(true); //合并表格 $objPHPExcel->getActiveSheet()->mergeCells("A".($n+$i-$count_pad).":A".($n+$i-1)); //$objPHPExcel->getActiveSheet()->mergeCells("A".($n+$i-$count_pad).":A".($n+$i-1)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue("A".($n+$i-$count_pad),$fuser); $user_num = 1; $fuser = $nTem['fuser']; $user = $nTem['user']; $i++; $count_pad = 1; $count_stores = 1; } $objPHPExcel->setActiveSheetIndex(0) //->setCellValue('A'.($n+$i), $nTem['fuser']) ->setCellValue('B'.($n+$i), $nTem['user']) ->setCellValue('C'.($n+$i), $nTem['padnum']); foreach($arr as $a => $b){ if(($a >2) && ($a != $count)){ if(($a-2)<10){ $word = $key_word.'0'.($a-2); }else{ $word = $key_word.($a-2); } if($nTem['actnum'][$word] == 0){ $nTem['actnum'][$word]=''; } $objPHPExcel->setActiveSheetIndex(0)->setCellValue($b.($n+$i), $nTem['actnum'][$word]); } } $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AI'.($n+$i),"=SUM(D".($n+$i).":AH".($n+$i).")" ); } $objPHPExcel->getActiveSheet()->mergeCells("B".($n+$i-$user_num+2).":B".($n+$i+1)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue("B".($n+$i-$user_num+2),$user); $objPHPExcel->getActiveSheet()->mergeCells("C".($n+$i-$user_num+1).":C".($n+$i)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue("C".($n+$i-$user_num+1),$user_num); $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.($n+$i+1), '区域小计') ->setCellValue('B'.($n+$i+1), $count_stores) ->setCellValue('C'.($n+$i+1), $count_pad); $total_stores += $count_stores; $total_pad += $count_pad; //合并表格 $objPHPExcel->getActiveSheet()->mergeCells("A".($n+$i+1-$count_pad).":A".($n+$i)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue("A".($n+$i+1-$count_pad),$fuser); foreach($arr as $a => $b){ if(($a >2)){ $objPHPExcel->setActiveSheetIndex(0)->setCellValue($b.($n+$i+1), "=SUM(".$b.($n+$i+1-$count_pad).":".$b.($n+$i).")"); } } //填充颜色 $objPHPExcel->getActiveSheet()->getStyle('A'.($n+$i+1).':AI'.($n+$i+1))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A'.($n+$i+1).':AI'.($n+$i+1))->getFill()->getStartColor()->setARGB('#FF9900'); $objPHPExcel->getActiveSheet()->getStyle('A'.($n+$i+1).':AI'.($n+$i+1))->getFont()->setBold(true); //********全部区域合计****** $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.($n+$i+2), '全部区域合计') ->setCellValue('B'.($n+$i+2), $total_stores) ->setCellValue('C'.($n+$i+2), $total_pad); foreach($arr as $a => $b){ if(($a >2)){ $objPHPExcel->setActiveSheetIndex(0)->setCellValue($b.($n+$i+2), "=SUM(".$b."4:".$b.($n+$i+1).")/2"); } } //**********结束************ //最后一行 备注 *****开始******* $objPHPExcel->getActiveSheet()->mergeCells("A".($n+$i+3).":A".($n+$i+4)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue("A".($n+$i+3),'备注'); $objPHPExcel->getActiveSheet()->mergeCells("B".($n+$i+3).":AI".($n+$i+4)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue("B".($n+$i+3),'可能有部分平板数据未上传，导致数据不准确，只要平板连上WIFI即会自动上传到后台进行统计（为保证数据的准确性，请务必每天连接WIFI）'); //填充颜色 $objPHPExcel->getActiveSheet()->getStyle('A'.($n+$i+2).':AI'.($n+$i+4))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A'.($n+$i+2).':AI'.($n+$i+4))->getFill()->getStartColor()->setARGB('#99CC00'); $objPHPExcel->getActiveSheet()->getStyle('A'.($n+$i+2).':AI'.($n+$i+2))->getFont()->setBold(true); //******结束******* $k = count($res); //设置居中 $objPHPExcel->getActiveSheet()->getStyle('A1:AI'.($k+7+$i))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A1:AI'.($k+7+$i))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); //设置边框 $objPHPExcel->getActiveSheet()->getStyle('A1:AI'.($k+7+$i))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); //设置字体样式 $objPHPExcel->getActiveSheet()->getStyle('A2:AI3')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('A4:A'.($k+4+$i))->getFont()->setBold(true); //填充颜色 $objPHPExcel->getActiveSheet()->getStyle('A2:AI3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A2:AI3')->getFill()->getStartColor()->setARGB('#FFCC00'); $objPHPExcel->getActiveSheet()->getStyle('B4:C'.($k+4+$i))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('B4:C'.($k+4+$i))->getFill()->getStartColor()->setARGB('#FFCC00'); $objPHPExcel->getActiveSheet()->getStyle('AI4:AI'.($k+4+$i))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('AI4:AI'.($k+4+$i))->getFill()->getStartColor()->setARGB('#99CC00'); $objPHPExcel->getActiveSheet()->setTitle($title); $objPHPExcel->setActiveSheetIndex(0); spl_autoload_register(array('Think','autoload')); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename='.$title.'.xls'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); exit; } $this->assign('endtime',date('Y-m-d')); $this->assign('starttime',date('Y-m').'-01'); $this->display('Pad/export'); }*/ /** +---------------------------------------------------------- * 按照门店 导出pad用户安装量 +---------------------------------------------------------- */ /*public function export_byStores(){ $m = M(); $ulist = getPuid($_SESSION['uid']); if($_POST['action']){ $sql = "select u.uid,u.user,u.upath,p.padnum from uc_users as u inner join uc_users_pad as p on u.uid = p.uid where u.uid in ($ulist) order by u.upath,u.uid"; $res = $m->query($sql); if(!empty($res)){ foreach($res as $k=>$v){ $arr = explode('-',$v['upath']); $id = $arr[count($arr)-1]; $user = M("uc_users")->where("uid = $id")->getField("user"); $res[$k]['fuser'] = $user; for($i=$_POST['starttime'];$i<=$_POST['endtime'];$i++){ $sqls = "select count(*) as c from ph_sinstall where uid ='$v[uid]' and padnum = '".$v['padnum']."' and itime like '%$i%'"; $res1 = $m->query($sqls); $res[$k]['actnum'][$i] = $res1[0]['c']; } } } //导出数据 $filename = date('YmdHis').'用户报表'; header( "Cache-Control: public" ); header( "Pragma: public" ); header("Content-type:application/vnd.ms-excel"); header("Content-Disposition:attachment;filename=$filename.csv"); header('Content-Type:APPLICATION/OCTET-STREAM'); ob_start(); $header_str = "大区,店名,平板,"; //行 foreach($res[0]['actnum'] as $k =>$rs){ $header_str.= $k.','; } $header_str .= "合计,"; $header_str .= "\n"; $header_str= iconv("utf-8",'gbk',$header_str); $file_str="";//列 $fuser = ''; $user = ''; $count_stores = 0; $count_pad = 0; $count_num_hang = 0; $count_num_lie = 0; foreach($res as $k => $nTem){ if(($nTem['fuser'] != $fuser) && ($k != 0)){ $fuser = $nTem['fuser']; $user = $nTem['user']; $file_str.= '区域小计'.','.$count_stores.','.$count_pad.','; $file_str.= "\n"; $count_stores = 1; $count_pad = 1; }else{ if(($nTem['user'] != $user)){ $user = $nTem['user']; $count_stores ++; } //$count_stores++; $count_pad++; } $file_str.= $nTem['fuser'].','.$nTem['user'].','.$nTem['padnum'].','; foreach($nTem['actnum'] as $k => $rs){ $file_str.= $rs.','; $count_num_hang += $rs; } $file_str.= $count_num_hang.','; $count_num_hang = 0; $file_str.= "\n"; } $file_str.= '区域小计'.','.$count_stores.','.$count_pad.','; $file_str= iconv("utf-8",'gbk',$file_str); ob_end_clean(); echo $header_str; echo $file_str; exit; } $this->assign('endtime',date('Y-m-d')); $this->assign('starttime',date('Y-m').'-01'); $this->display('Pad/export_byStores'); }*/ public function export_byStores(){ $m = M(); if($_POST['uid']){ $ulist = getPuid($_POST['uid']); }else{ $ulist = getPuid($_SESSION['uid']); } if($_POST['action']){ $sql = "select u.uid,u.user,u.upath,p.padnum from uc_users as u inner join uc_users_pad as p on u.uid = p.uid where u.uid in ($ulist) order by u.upath,u.uid"; $res = $m->query($sql); if(!empty($res)){ foreach($res as $k=>$v){ $arr = explode('-',$v['upath']); $id = $arr[count($arr)-1]; $user = M("uc_users")->where("uid = $id")->getField("user"); $res[$k]['fuser'] = $user; for($i=$_POST['starttime'];$i<=$_POST['endtime'];$i++){ $sqls = "select count(*) as c from ph_sinstall where `distinct` = 1 and uid ='$v[uid]' and padnum = '".$v['padnum']."' and itime like '%$i%'"; $res1 = $m->query($sqls); $res[$k]['actnum'][$i] = $res1[0]['c']; } } } require_once './ThinkPHP/Vendor/PHPExcel.php'; require_once './ThinkPHP/Vendor/PHPExcel/IOFactory.php'; require_once './ThinkPHP/Vendor/PHPExcel/Reader/Excel5.php'; $arr_time = explode('-',$_POST['starttime']); //设置文件的保存名称 $title = $arr_time[0].'年'.$arr_time[1].'月'; $key_word = $arr_time[0].'-'.$arr_time[1].'-'; $month = $arr_time[1]; $objPHPExcel = new PHPExcel(); //设置excel 的标题 $objPHPExcel->getProperties()->setTitle("$month月份门店软件安装明细"); //冻结表头 $objPHPExcel->getActiveSheet()->FreezePane('D4'); //合并表格 $objPHPExcel->getActiveSheet()->mergeCells('A2:AI2'); //设置宽度 $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(22); $t = $month."月份门店软件安装明细"; $arr = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI'); $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A2', $t) ->setCellValue('A3', '大区') ->setCellValue('B3', '门店') ->setCellValue('C3', '平板'); $count = count($arr); foreach($arr as $a => $b){ if(($a >2) && ($a != ($count - 1))){ $objPHPExcel->setActiveSheetIndex(0)->setCellValue($b.'3', $a-2); $objPHPExcel->getActiveSheet()->getColumnDimension($b)->setWidth(5); } } $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AI3', '总计'); $fuser = $res[0]['fuser']; $user = $res[0]['user']; $count_pad = 0; $count_stores = 1; $i = 0; $user_num = 0; $total_stores = 0; $total_pad = 0; foreach($res as $key => $nTem){ $n=$key+4; if(($nTem['fuser'] == $fuser)){ $count_pad++; if($nTem['user']!=$user){ $objPHPExcel->getActiveSheet()->mergeCells("B".($n+$i-$user_num).":B".($n+$i-1)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue("B".($n+$i-$user_num),$user); $objPHPExcel->getActiveSheet()->mergeCells("C".($n+$i-$user_num).":C".($n+$i-1)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue("C".($n+$i-$user_num),$user_num); $count_stores ++; $user = $nTem['user']; $user_num = 1; }else{ $user_num ++; } }else{ $objPHPExcel->getActiveSheet()->mergeCells("B".($n+$i-$user_num).":B".($n+$i-1)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue("B".($n+$i-$user_num),$user); $objPHPExcel->getActiveSheet()->mergeCells("C".($n+$i-$user_num).":C".($n+$i-1)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue("C".($n+$i-$user_num),$user_num); $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.($n+$i), '区域小计') ->setCellValue('B'.($n+$i), $count_stores) ->setCellValue('C'.($n+$i), $count_pad); $total_stores += $count_stores; $total_pad += $count_pad; foreach($arr as $a => $b){ if(($a >2)){ $objPHPExcel->setActiveSheetIndex(0)->setCellValue($b.($n+$i), "=SUM(".$b.($n+$i-$count_pad).":".$b.($n+$i-1).")"); } } //填充颜色 $objPHPExcel->getActiveSheet()->getStyle('A'.($n+$i).':AI'.($n+$i))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A'.($n+$i).':AI'.($n+$i))->getFill()->getStartColor()->setARGB('#FF9900'); $objPHPExcel->getActiveSheet()->getStyle('A'.($n+$i).':AI'.($n+$i))->getFont()->setBold(true); //合并表格 $objPHPExcel->getActiveSheet()->mergeCells("A".($n+$i-$count_pad).":A".($n+$i-1)); //$objPHPExcel->getActiveSheet()->mergeCells("A".($n+$i-$count_pad).":A".($n+$i-1)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue("A".($n+$i-$count_pad),$fuser); $user_num = 1; $fuser = $nTem['fuser']; $user = $nTem['user']; $i++; $count_pad = 1; $count_stores = 1; } $objPHPExcel->setActiveSheetIndex(0) //->setCellValue('A'.($n+$i), $nTem['fuser']) ->setCellValue('B'.($n+$i), $nTem['user']) ->setCellValue('C'.($n+$i), $nTem['padnum']); foreach($arr as $a => $b){ if(($a >2) && ($a != $count)){ if(($a-2)<10){ $word = $key_word.'0'.($a-2); }else{ $word = $key_word.($a-2); } if($nTem['actnum'][$word] == 0){ $nTem['actnum'][$word]=''; } $objPHPExcel->setActiveSheetIndex(0)->setCellValue($b.($n+$i), $nTem['actnum'][$word]); } } $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AI'.($n+$i),"=SUM(D".($n+$i).":AH".($n+$i).")" ); } $objPHPExcel->getActiveSheet()->mergeCells("B".($n+$i-$user_num+1).":B".($n+$i)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue("B".($n+$i-$user_num+2),$user); $objPHPExcel->getActiveSheet()->mergeCells("C".($n+$i-$user_num+1).":C".($n+$i)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue("C".($n+$i-$user_num+1),$user_num); $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.($n+$i+1), '区域小计') ->setCellValue('B'.($n+$i+1), $count_stores) ->setCellValue('C'.($n+$i+1), $count_pad); $total_stores += $count_stores; $total_pad += $count_pad; //合并表格 $objPHPExcel->getActiveSheet()->mergeCells("A".($n+$i+1-$count_pad).":A".($n+$i)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue("A".($n+$i+1-$count_pad),$fuser); foreach($arr as $a => $b){ if(($a >2)){ $objPHPExcel->setActiveSheetIndex(0)->setCellValue($b.($n+$i+1), "=SUM(".$b.($n+$i+1-$count_pad).":".$b.($n+$i).")"); } } //填充颜色 $objPHPExcel->getActiveSheet()->getStyle('A'.($n+$i+1).':AI'.($n+$i+1))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A'.($n+$i+1).':AI'.($n+$i+1))->getFill()->getStartColor()->setARGB('#FF9900'); $objPHPExcel->getActiveSheet()->getStyle('A'.($n+$i+1).':AI'.($n+$i+1))->getFont()->setBold(true); //********全部区域合计****** $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.($n+$i+2), '全部区域合计') ->setCellValue('B'.($n+$i+2), $total_stores) ->setCellValue('C'.($n+$i+2), $total_pad); foreach($arr as $a => $b){ if(($a >2)){ $objPHPExcel->setActiveSheetIndex(0)->setCellValue($b.($n+$i+2), "=SUM(".$b."4:".$b.($n+$i+1).")/2"); } } //**********结束************ //最后一行 备注 *****开始******* $objPHPExcel->getActiveSheet()->mergeCells("A".($n+$i+3).":A".($n+$i+4)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue("A".($n+$i+3),'备注'); $objPHPExcel->getActiveSheet()->mergeCells("B".($n+$i+3).":AI".($n+$i+4)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue("B".($n+$i+3),'可能有部分平板数据未上传，导致数据不准确，只要平板连上WIFI即会自动上传到后台进行统计（为保证数据的准确性，请务必每天连接WIFI）'); //填充颜色 $objPHPExcel->getActiveSheet()->getStyle('A'.($n+$i+2).':AI'.($n+$i+4))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A'.($n+$i+2).':AI'.($n+$i+4))->getFill()->getStartColor()->setARGB('#99CC00'); $objPHPExcel->getActiveSheet()->getStyle('A'.($n+$i+2).':AI'.($n+$i+2))->getFont()->setBold(true); //******结束******* $k = count($res); //设置居中 $objPHPExcel->getActiveSheet()->getStyle('A1:AI'.($k+7+$i))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A1:AI'.($k+7+$i))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); //设置边框 $objPHPExcel->getActiveSheet()->getStyle('A1:AI'.($k+7+$i))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); //设置字体样式 $objPHPExcel->getActiveSheet()->getStyle('A2:AI3')->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('A4:A'.($k+4+$i))->getFont()->setBold(true); //填充颜色 $objPHPExcel->getActiveSheet()->getStyle('A2:AI3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A2:AI3')->getFill()->getStartColor()->setARGB('#FFCC00'); $objPHPExcel->getActiveSheet()->getStyle('B4:C'.($k+4+$i))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('B4:C'.($k+4+$i))->getFill()->getStartColor()->setARGB('#FFCC00'); $objPHPExcel->getActiveSheet()->getStyle('AI4:AI'.($k+4+$i))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('AI4:AI'.($k+4+$i))->getFill()->getStartColor()->setARGB('#99CC00'); $objPHPExcel->getActiveSheet()->setTitle($title); $objPHPExcel->setActiveSheetIndex(0); spl_autoload_register(array('Think','autoload')); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename='.$title.'.xls'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); exit; } $this->assign('endtime',date('Y-m-d')); $this->assign('starttime',date('Y-m').'-01'); if($_GET['type']){ $upath = $m->table('uc_users')->where('uid='.$_SESSION['uid'])->getField("upath").'-'.$_SESSION['uid']; $user = $m->table('uc_users')->where("upath = '$upath'")->select(); $this->assign('user',$user); $this->display('Pad/export'); }else{ $this->display('Pad/export_byStores'); } } public function all_sort(){ $m = M(); if($_POST['uid']){ $uid = intval($_POST['uid']); }else{ $uid = intval($_SESSION['uid']); } $ulist = getPuid($uid); $starttime = strtotime($_POST['starttime']); $endtime = strtotime($_POST['endtime']); if($_POST['action']){ //大区的uid $ulist = get_puid($uid); $list = M("uc_users")->where("uid in ($ulist)")->field("user,uid")->select(); $install = array(); $install_avg = array(); foreach($list as $k => $v){ $u_list = getPuid($v['uid']); //平板数 $list[$k]['pad_num'] = M("uc_users_pad")->where("uid in ($u_list) and status = 1")->count(); $install[$v['user']] = M("ph_sinstall")->where("uid in ($u_list) and time between $starttime and $endtime and `distinct` = 1")->count(); $install_avg[$v['user']] = number_format($install[$v['user']]/$list[$k]['pad_num'],2); } //排序 arsort($install); arsort($install_avg); $sort_array = array(); $i = 0; foreach($install as $k => $v){ $sort_array[$i]['install_name'] = $k; $sort_array[$i]['install_num'] = $v; $i++; } $i = 0; foreach($install_avg as $k => $v){ $sort_array[$i]['install_avg_name'] = $k; $sort_array[$i]['install_avg_num'] = $v; $i++; } require_once './ThinkPHP/Vendor/PHPExcel.php'; require_once './ThinkPHP/Vendor/PHPExcel/IOFactory.php'; require_once './ThinkPHP/Vendor/PHPExcel/Reader/Excel5.php'; //设置文件的保存名称 $arr_time = explode('-',$_POST['starttime']); $title_word = $arr_time[0].'年'.$arr_time[1].'月'.$arr_time[2].'日-'; $titleword = $arr_time[1].'月'.$arr_time[2].'日-'; $arr_time = explode('-',$_POST['endtime']); $title_word .= $arr_time[1].'月'.$arr_time[2].'日'; $titleword .= $arr_time[1].'月'.$arr_time[2].'日'; $objPHPExcel = new PHPExcel(); $user = M("uc_users")->where("uid = ".$uid)->getField("user"); //设置excel 的标题 $objPHPExcel->getProperties()->setTitle($user."数据分析表($title_word)"); //冻结表头 $objPHPExcel->getActiveSheet()->FreezePane('B3'); //合并表格 $objPHPExcel->getActiveSheet()->mergeCells('B1:J1'); //设置宽度 $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(22); $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(22); $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10); //设置高度 $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(40); $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(27); //自动换行 $objPHPExcel->getActiveSheet()->getStyle('H2')->getAlignment()->setWrapText(true); $objPHPExcel->getActiveSheet()->getStyle('J2')->getAlignment()->setWrapText(true); $t = $user."数据分析表($title_word)"; $row_num = count($sort_array); /****************设置头部 start****************/ $objPHPExcel->getActiveSheet()->getStyle( 'B1')->getFont()->setSize(18); $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('B1', $t) ->setCellValue('B2', '序号') ->setCellValue('C2', '门店') ->setCellValue('D2', '平板数') ->setCellValue('E2', '排名') ->setCellValue('F2', '名次') ->setCellValue('G2', '安装手机总数排名') ->setCellValue('H2', '安装手机总数') ->setCellValue('I2', '单台平板均安装量排名') ->setCellValue('J2', '单台平板均安装量'); /**********************设置头部 end***********************/ /***********************设置内容数据 start********************/ foreach($list as $k => $v){ $n = $k+3; $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$n, $k+1); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$n, $v['user']); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$n, $v['pad_num']); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F'.$n, $k+1); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G'.$n, $sort_array[$k]['install_name']); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H'.$n, $sort_array[$k]['install_num']); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I'.$n, $sort_array[$k]['install_avg_name']); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J'.$n, $sort_array[$k]['install_avg_num']); } $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.($row_num+3),'总数'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.($row_num+3),"=SUM(D3:D".($row_num+2).")"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G'.($row_num+3),'安装总数'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H'.($row_num+3),"=SUM(H3:H".($row_num+2).")"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I'.($row_num+3),'单台平板均产量'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J'.($row_num+3),"=SUM(H3:H".($row_num+2).")/SUM(D3:D".($row_num+2).")"); /***********************设置内容数据 end*********************/ //设置表格体和底部的高度 for($i=3;$i<=$row_num+2;$i++){ $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(22); } $objPHPExcel->getActiveSheet()->getRowDimension($row_num+3)->setRowHeight(27); //设置居中 $objPHPExcel->getActiveSheet()->getStyle('A1:J'.($row_num+3))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A1:J'.($row_num+3))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); //设置边框 $objPHPExcel->getActiveSheet()->getStyle('A1:J'.($row_num+3))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); //设置字体样式 $objPHPExcel->getActiveSheet()->getStyle('A1:J'.($row_num+3))->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('C3:D'.($row_num+2))->getFont()->setBold(false); //填充颜色 $objPHPExcel->getActiveSheet()->getStyle('A2:J2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A2:J2')->getFill()->getStartColor()->setARGB('#00CCFF'); $objPHPExcel->getActiveSheet()->getStyle('A3:A'.($row_num+2))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A3:A'.($row_num+2))->getFill()->getStartColor()->setARGB('#99CC00'); $objPHPExcel->getActiveSheet()->getStyle('E3:F'.($row_num+2))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('E3:F'.($row_num+2))->getFill()->getStartColor()->setARGB('#00CCFF'); $objPHPExcel->getActiveSheet()->getStyle('A'.($row_num+3).':J'.($row_num+3))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A'.($row_num+3).':J'.($row_num+3))->getFill()->getStartColor()->setARGB('#FF6600'); //合并表格 $objPHPExcel->getActiveSheet()->mergeCells('A3:A'.($row_num+2)); $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setWrapText(true); $objPHPExcel->setActiveSheetIndex(0)->setCellValue("A3",$titleword); $objPHPExcel->getActiveSheet()->mergeCells('E3:E'.($row_num+2)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue("E3",'排名'); $objPHPExcel->getActiveSheet()->setTitle($title_word); $objPHPExcel->setActiveSheetIndex(0); spl_autoload_register(array('Think','autoload')); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename='.$title_word.'.xls'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); exit; } $this->assign('endtime',date('Y-m-d')); $this->assign('starttime',date('Y-m').'-01'); if($_GET['type']){ $upath = $m->table('uc_users')->where('uid='.$_SESSION['uid'])->getField("upath").'-'.$_SESSION['uid']; $user = $m->table('uc_users')->where("upath = '$upath'")->select(); $this->assign('user',$user); $this->display('Pad/all_sortByStores'); }else{ $this->display('Pad/all_sort'); } } public function zong_sort(){ $m = M(); $uid = intval($_SESSION['uid']); $starttime = strtotime($_POST['starttime']); $endtime = strtotime($_POST['endtime']); if($_POST['action']){ //大区的uid $ulist = get_puid($uid); $list = M("uc_users")->where("uid in ($ulist)")->field("user,uid")->select(); $install = array(); $install_avg = array(); foreach($list as $k => $v){ $u_list = getPuid($v['uid']); //平板数 $list[$k]['pad_num'] = M("uc_users_pad")->where("uid in ($u_list) and status = 1")->count(); $list[$k]['stores_num'] = count(M("uc_users_pad")->where("uid in ($u_list) and status = 1")->group("uid")->select()); $install[$v['user']] = M("ph_sinstall")->where("uid in ($u_list) and time between $starttime and $endtime and `distinct` = 1")->count(); $install_avg[$v['user']] = number_format($install[$v['user']]/$list[$k]['pad_num'],2); } //排序 arsort($install); arsort($install_avg); $sort_array = array(); $i = 0; foreach($install as $k => $v){ $sort_array[$i]['install_name'] = $k; $sort_array[$i]['install_num'] = $v; $i++; } $i = 0; foreach($install_avg as $k => $v){ $sort_array[$i]['install_avg_name'] = $k; $sort_array[$i]['install_avg_num'] = $v; $i++; } require_once './ThinkPHP/Vendor/PHPExcel.php'; require_once './ThinkPHP/Vendor/PHPExcel/IOFactory.php'; require_once './ThinkPHP/Vendor/PHPExcel/Reader/Excel5.php'; //设置文件的保存名称 $arr_time = explode('-',$_POST['starttime']); $title_word = $arr_time[0].'年'.$arr_time[1].'月'.$arr_time[2].'日-'; $arr_time = explode('-',$_POST['endtime']); $title_word .= $arr_time[1].'月'.$arr_time[2].'日'; $objPHPExcel = new PHPExcel(); //设置excel 的标题 $objPHPExcel->getProperties()->setTitle("增值平板数据周分析表"); //冻结表头 $objPHPExcel->getActiveSheet()->FreezePane('A3'); //合并表格 $objPHPExcel->getActiveSheet()->mergeCells('A1:F1'); $objPHPExcel->getActiveSheet()->mergeCells('A2:F2'); $objPHPExcel->getActiveSheet()->mergeCells('A3:F3'); //设置高度 $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(61); $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(35); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', '增值平板数据周分析表'); $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(24); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A2', '各大区数据周汇总与排名'); $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(18); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A3', '时间:'.$title_word); //设置居中 $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); //设置宽度 $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(9); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(16); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(17.5); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(16.5); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(21.5); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(18); $row_num = count($sort_array); $objPHPExcel->getActiveSheet()->mergeCells('A'.($row_num+7).':D'.($row_num+7)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.($row_num+7), '平板铺设情况'); $objPHPExcel->getActiveSheet()->getStyle('A'.($row_num+7))->getFont()->setSize(18); $objPHPExcel->getActiveSheet()->getRowDimension($row_num+7)->setRowHeight(25); $objPHPExcel->getActiveSheet()->getRowDimension($row_num+8)->setRowHeight(20); $objPHPExcel->getActiveSheet()->getStyle('A'.($row_num+7).':D'.($row_num+$row_num+9))->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getStyle('C'.($row_num+9).':D'.($row_num+$row_num+8))->getFont()->setBold(FALSE); //设置居中 $objPHPExcel->getActiveSheet()->getStyle('A'.($row_num+7).':D'.($row_num+$row_num+9))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A'.($row_num+7).':D'.($row_num+$row_num+9))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); //设置边框 $objPHPExcel->getActiveSheet()->getStyle('A'.($row_num+7).':D'.($row_num+$row_num+9))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); //填充颜色 $objPHPExcel->getActiveSheet()->getStyle('A'.($row_num+7).':D'.($row_num+7))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A'.($row_num+7).':D'.($row_num+7))->getFill()->getStartColor()->setARGB('#FF6600'); $objPHPExcel->getActiveSheet()->getStyle('A'.($row_num+8).':D'.($row_num+8))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A'.($row_num+8).':D'.($row_num+8))->getFill()->getStartColor()->setARGB('#00CCFF'); $objPHPExcel->getActiveSheet()->getStyle('A'.($row_num+$row_num+9).':D'.($row_num+$row_num+9))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A'.($row_num+$row_num+9).':D'.($row_num+$row_num+9))->getFill()->getStartColor()->setARGB('#C48C35'); /****************设置头部 start****************/ $objPHPExcel->getActiveSheet()->getStyle( 'B1')->getFont()->setSize(18); $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('B4', '名次') ->setCellValue('C4', '安装手机总数排名') ->setCellValue('D4', '安装量') ->setCellValue('E4', '单台平板均产量排名') ->setCellValue('F4', '均产量') ->setCellValue('A'.($row_num+8), '序号') ->setCellValue('B'.($row_num+8), '区域') ->setCellValue('C'.($row_num+8), '平板数') ->setCellValue('D'.($row_num+8), '已铺门店数'); /**********************设置头部 end***********************/ /***********************设置内容数据 start********************/ foreach($list as $k => $v){ $n = $k+5; $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$n, $k+1); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$n, $sort_array[$k]['install_name']); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$n, $sort_array[$k]['install_num']); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.$n, $sort_array[$k]['install_avg_name']); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F'.$n, $sort_array[$k]['install_avg_num']); $n = $k+$row_num+9; $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$n, $k+1); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.$n, $v['user']); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.$n, $v['pad_num']); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.$n, $v['stores_num']); } $objPHPExcel->getActiveSheet()->mergeCells('A'.($row_num+5).':B'.($row_num+5)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.($row_num+5),'总计'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.($row_num+5),'总安装数'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.($row_num+5),"=SUM(D5:D".($row_num+4).")"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.($row_num+5),'单平板产量'); $objPHPExcel->getActiveSheet()->mergeCells('A'.($row_num+$row_num+9).':B'.($row_num+$row_num+9)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.($row_num+$row_num+9), '总计'); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.($row_num+$row_num+9),"=SUM(C".($row_num+9).":C".($row_num+$row_num+8).")"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.($row_num+$row_num+9),"=SUM(D".($row_num+9).":D".($row_num+$row_num+8).")"); $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F'.($row_num+5),"=D".($row_num+5)."/C".($row_num+$row_num+9).")"); /***********************设置内容数据 end*********************/ //设置表格体和底部的高度 for($i=3;$i<=$row_num+3;$i++){ $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(22); } $objPHPExcel->getActiveSheet()->getRowDimension($row_num+5)->setRowHeight(35); //设置居中 $objPHPExcel->getActiveSheet()->getStyle('A4:F'.($row_num+5))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('A4:F'.($row_num+5))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); //设置边框 $objPHPExcel->getActiveSheet()->getStyle('A1:F'.($row_num+5))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); //设置字体样式 $objPHPExcel->getActiveSheet()->getStyle('A1:F'.($row_num+5))->getFont()->setBold(true); //填充颜色 $objPHPExcel->getActiveSheet()->getStyle('A4:B'.($row_num+4))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A4:B'.($row_num+4))->getFill()->getStartColor()->setARGB('#00CCFF'); $objPHPExcel->getActiveSheet()->getStyle('C4:F4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('C4:F4')->getFill()->getStartColor()->setARGB('#00CCFF'); $objPHPExcel->getActiveSheet()->getStyle('A3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A3')->getFill()->getStartColor()->setARGB('#99CC00'); $objPHPExcel->getActiveSheet()->getStyle('A'.($row_num+5).':F'.($row_num+5))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A'.($row_num+5).':F'.($row_num+5))->getFill()->getStartColor()->setARGB('#C48C35'); //合并表格 $objPHPExcel->getActiveSheet()->mergeCells('A5:A'.($row_num+4)); $objPHPExcel->setActiveSheetIndex(0)->setCellValue("A5",'排名'); $objPHPExcel->getActiveSheet()->setTitle($title_word); $objPHPExcel->setActiveSheetIndex(0); spl_autoload_register(array('Think','autoload')); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename='.$title_word.'.xls'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); exit; } $this->assign('endtime',date('Y-m-d')); $this->assign('starttime',date('Y-m').'-01'); $this->display('Pad/zong_sort'); }
