php导入导出
/*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;
$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' and padnum = '".$v['padnum']."' anditime like '%$i%'";
$res1 = $m->query($sqls);
$res[$k]['actnum'][$i] = $res1['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.'年'.$arr_time.'月';
$key_word = $arr_time.'-'.$arr_time.'-';
$month = $arr_time;
$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['fuser'];
$user = $res['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;
$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' and padnum = '".$v['padnum']."' anditime like '%$i%'";
$res1 = $m->query($sqls);
$res[$k]['actnum'][$i] = $res1['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['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;
$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 anduid ='$v' and padnum = '".$v['padnum']."' anditime like '%$i%'";
$res1 = $m->query($sqls);
$res[$k]['actnum'][$i] = $res1['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.'年'.$arr_time.'月';
$key_word = $arr_time.'-'.$arr_time.'-';
$month = $arr_time;
$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['fuser'];
$user = $res['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.'年'.$arr_time.'月'.$arr_time.'日-';
$titleword = $arr_time.'月'.$arr_time.'日-';
$arr_time = explode('-',$_POST['endtime']);
$title_word .= $arr_time.'月'.$arr_time.'日';
$titleword .= $arr_time.'月'.$arr_time.'日';
$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.'年'.$arr_time.'月'.$arr_time.'日-';
$arr_time = explode('-',$_POST['endtime']);
$title_word .= $arr_time.'月'.$arr_time.'日';
$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');
}
页:
[1]