phpexcel 生成报表

phpexcel 生成表格

	/* 	 * 导出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; 	} 	

?