Community Learn Tools Library Leisure
search
English
Home > Backend Development > PHP Tutorial > phpexcel 生成报表

phpexcel 生成报表

WBOY
Release： 2016-06-13 13:19:57
Original
1215 people have browsed it

phpexcel 生成表格

Copy after login


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

?
 

                 
              
              
        
            
                

            

            

                                

                    Related labels：
                    

                        gt                    

                

                                
source：php.cn

                            

            

                                    
                        Previous article： ppframe有关问题 
                    
                                    
                        Next article： php压缩文件后出现的解压异常 
                    
                            

            

       
                
Statement of this Website

                
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

            

            

            

                
Latest Articles by Author

                
            

            

                
Latest Issues

                

                                        

                        

                            function_exists() cannot determine the custom function
                            Function test () {return true;} if (function_exists ('test')) {echo "test is function...
                            

                                

                                     From 2024-04-29 11:01:01
                                

                                

                                    
  0 

                                    
3

                                    
2347

                                

                            

                        

                    

                    

                                        

                        

                            How to display the mobile version of Google Chrome
                            Hello teacher, how can I change Google Chrome into a mobile version?
                            

                                

                                     From 2024-04-23 00:22:19
                                

                                

                                    
  0 

                                    
11

                                    
2476

                                

                            

                        

                    

                    

                                        

                        

                            The child window operates the parent window, but the output does not respond.
                            The first two sentences are executable, but the last sentence cannot be implemented.
                            

                                

                                     From 2024-04-19 15:37:47
                                

                                

                                    
  0 

                                    
1

                                    
2099

                                

                            

                        

                    

                    

                                        

                        

                            There is no output in the parent window
                            document.onclick = function(){ window.opener.document.write('I am the output of the child ...
                            

                                

                                     From 2024-04-18 23:52:34
                                

                                

                                    
  0 

                                    
1

                                    
1978

                                

                            

                        

                    

                    

                                        

                        

                            Where is the courseware about CSS mind mapping?
                            Courseware
                            

                                

                                     From 2024-04-16 10:10:18
                                

                                

                                    
  0 

                                    
0

                                    
2049

                                

                            

                        

                    

                    

                                    

            


            

                

                    
Related Topics

                    More>
                    
                

                

                    
                

            

        

    


    

    
    
        

            
Popular Recommendations

            

                
            

        

        
        

            

                
Popular Tutorials

                More>
                
            

            

                

                    
Related Tutorials 

                    
Popular Recommendations

                    
Latest courses

                

                
                
                
            

            
        


        

            

                
Latest Downloads

                More>
                
            

                        
            

                

                    
Web Effects 

                    
Website Source Code

                    
Website Materials

                    
Front End Template