/** 批量导出功能* pid 项目id* yq 抓取平台* object_title 监测对象名称* keyword 关键词* title 评论里面的标题* status 状态* timetype 筛选的时间类型 1 最近1天 2最近7天 3最近30天*/
public function CommentExcel(){set_time_limit(0);ini_set('memory_limit', '3072M');$map['pid'] = $pid = I('pid');// $pid = 1822;$projectName = M('project')->where(array('id' => $pid))->find()['title'];//高频词获取$high_words_count = M('project')->where(array('id' => $pid))->find()['high_words_count'];$high_words_count = unserialize($high_words_count);// $high_words_count = [// "day"=> [[//"keyword"=> "测试7",// "num"=> 123// ],//[//"keyword"=> "测试6",//"num"=> 123//]//// ],// "week"=> [[//"keyword"=> "测试2",//"num"=> 123// ],//[//"keyword"=> "测试3",//"num"=> 123//]//// ],// "month"=> [[//"keyword"=> "测试4",//"num"=> 123// ],//[//"keyword"=> "测试5",//"num"=> 123//]//// ],//];$word = $high_words_count['day'];//时间筛选$timeType=I('timetype');if(1 == $timeType){$startTime = time()-3600*24;$endTime = time();$word = $high_words_count['day'];$map['screen_time'] = array('between',$startTime.','.$endTime);}elseif (2 == $timeType){$startTime = time()-3600*7*24;$endTime = time();$word = $high_words_count['week'];$map['screen_time'] = array('between',$startTime.','.$endTime);}elseif (3 == $timeType){$startTime = time()-3600*24*30;$endTime = time();$word = $high_words_count['month'];$map['screen_time'] = array('between',$startTime.','.$endTime);}//处理高频词格式$newWord = [];foreach ($word as $kkk => $vvv) {$newWord[$kkk]['index'] = $kkk+1;$newWord[$kkk]['keyword'] = $vvv['keyword'];$newWord[$kkk]['num'] = $vvv['num'];}foreach ($newWord as $kkkk => $vvvv) {$newWord[$kkkk] = array_values($vvvv);}$yq=I('yq');if($yq){$map['yq'] = $yq;}else{$map['yq'] = -1;}$objectTitle=I('object_title');if($objectTitle){if($objectTitle != '全部'){$objectTitle = explode(',',$objectTitle);$map['object_title'] = array('in',$objectTitle);}}else{$map['object_title'] = 888888800;}//关键词选择全部的时候，不传值给我$keyword=I('keywords');if($keyword){$map['keyword'] = $keyword;}//评论筛选$title=I('title');if($title){$map['title'] = array('like',"%{$title}%");}//状态筛选，默认传值1，已经删除的时候传值-1$status=I('status');if($status){$map['status'] = $status;}$list = M('project_info')->field('id,object_title,url,title,keyword,screen_name,fens_count,content,dzs,screen_time')->where($map)->select();foreach ($list as $k=>$v){$list[$k]['id'] = $v['id'];$list[$k]['object_title'] = $v['object_title'];$list[$k]['title'] = $v['title'];$list[$k]['url'] = $v['url'];$list[$k]['keyword'] = $v['keyword'];$list[$k]['screen_name'] = $v['screen_name'];$list[$k]['fens_count'] = $v['fens_count'];$list[$k]['content'] = $v['content'];$list[$k]['dzs'] = $v['dzs'];$list[$k]['screen_time'] = date('Y-m-d',$v['screen_time']);}$newList = [];foreach ($list as $kk => $vv) {$newList[$kk] = array_values($vv);}// myLog($newList);die;$expCellName = array('序号','采集对象', '监测链接','微博正文', '关键词', '评论人', '粉丝数', '评论内容', '点赞数', '评论时间');$fileName = $projectName.date('mdHis');$cellNum = count($expCellName); //多少列$dataNum = count($newList);vendor("PHPExcel.PHPExcel");vendor("PHPExcel.PHPExcel.Writer.Excel5");$allnums = $dataNum+1;$objPHPExcel = new \PHPExcel(); //初始化$this->objPHPExcel = new \PHPExcel();$objPHPExcel->getActiveSheet()->setTitle('评论列表');$objPHPExcel->getDefaultStyle()->getFont()->setName('微软雅黑');$this->objActiveSheet=$this->objPHPExcel->getActiveSheet();$objActiveSheet=$objPHPExcel->getActiveSheet();$objActiveSheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);$objActiveSheet->getDefaultStyle()->getFont()->setSize(10);$objPHPExcel->getActiveSheet()->getstyle("F2:H".$allnums)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);$objActiveSheet->freezePane('B2');$cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J');$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(18);//设置第一行为蓝色背景，字体颜色为白色$objPHPExcel->getActiveSheet()->getStyle("A1:J1")->getFont()->setBold(TRUE)->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_WHITE);$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(15);$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);// $this->create_header();//functions.php里面的函数cellcolorcellColor('A1:J1', '4472C4', $objPHPExcel);$objPHPExcel->getActiveSheet()->getStyle("Q38")->getFont()->setBold(TRUE)->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_WHITE);// $objPHPExcel->getDefaultStyle()->getFont()->setColor(new \PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_WHITE));for ($i = 0; $i < $cellNum; $i++) {//表头(列名)$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '1', $expCellName[$i]);}// Miscellaneous glyphs, UTF-8for ($i = 0; $i < $dataNum; $i++) {for ($j = 0; $j < $cellNum; $j++) {$objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + 2),$newList[$i][$j]);}}//第二个sheet$sheet2 = $objPHPExcel->createSheet();//创建第二个sheet$objPHPExcel->setactivesheetindex(1);//区别于第一个sheet，$objPHPExcel->getActiveSheet(1)->setTitle('高频词');$objPHPExcel->getActiveSheet(1)->getColumnDimension('A')->setWidth(5);$objPHPExcel->getActiveSheet(1)->getColumnDimension('B')->setWidth(12);$objPHPExcel->getActiveSheet(1)->getColumnDimension('C')->setWidth(12);$cellName2 = array('A', 'B', 'C');$expCellName2 = array('序号','高频词', '数量');//设置第一行为蓝色背景，字体颜色为白色$objPHPExcel->getActiveSheet(1)->getStyle("A1:C1")->getFont()->setBold(TRUE)->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_WHITE);cellColor1('A1:C1', '4472C4', $objPHPExcel);$cellNum2 = count($expCellName2); //多少列$dataNum2 = count($newWord);for ($i = 0; $i < $cellNum2; $i++) {//表头(列名)$objPHPExcel->setActiveSheetIndex(1)->setCellValue($cellName2[$i] . '1', $expCellName2[$i]);}// Miscellaneous glyphs, UTF-8for ($i = 0; $i < $dataNum2; $i++) {for ($j = 0; $j < $cellNum2; $j++) {$objPHPExcel->getActiveSheet(1)->setCellValue($cellName2[$j] . ($i + 2),$newWord[$i][$j]);}}$objPHPExcel->setActiveSheetIndex(0);if(ob_get_length() > 0) {ob_clean();} //清除缓冲区,避免乱码//直接页面输出xlsx文件header('Content-Type: application/vnd.ms-excel');header('pragma:public');//attachment新窗口打印inline本窗口打印header("Content-Disposition:attachment;filename=$fileName.xlsx");$objWriter = new \Excel5($objPHPExcel);$objWriter->save('php://output');exit;}
