Gemeinschaft Lernen Tools-Bibliothek Freizeit
suchen
Deutsch
Heim > Backend-Entwicklung > PHP-Tutorial > phpexcel 生成报表

phpexcel 生成报表

WBOY
Freigeben： 2016-06-13 13:19:57
Original
1215 Leute haben es durchsucht

phpexcel 生成表格

Nach dem Login kopieren


	/*
 	 * 导出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;
 	}
 	
Nach dem Login kopieren

?
 

                 
              
              
        
            
                

            

            

                                

                    Verwandte Etiketten：
                    

                        gt                    

                

                                
Quelle：php.cn

                            

            

                                    
                        Vorheriger Artikel： ppframe有关问题 
                    
                                    
                        Nächster Artikel： php压缩文件后出现的解压异常 
                    
                            

            

       
                
Erklärung dieser Website

                
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn

            

            

            

                
Neueste Artikel des Autors

                
            

            

                
Aktuelle Ausgaben

                

                                        

                        

                            function_exists() kann die benutzerdefinierte Funktion nicht ermitteln
                            Funktionstest () {Verwendung der Verwendung durch -Durch -Durch -Durch -Durch -Durch -Durc...
                            

                                

                                     Aus 2024-04-29 11:01:01
                                

                                

                                    
  0 

                                    
3

                                    
2346

                                

                            

                        

                    

                    

                                        

                        

                            So zeigen Sie die mobile Version von Google Chrome an
                            Hallo Lehrer, wie kann ich Google Chrome in eine mobile Version umwandeln?
                            

                                

                                     Aus 2024-04-23 00:22:19
                                

                                

                                    
  0 

                                    
11

                                    
2475

                                

                            

                        

                    

                    

                                        

                        

                            Das untergeordnete Fenster bedient das übergeordnete Fenster, aber die Ausgabe antwortet nicht.
                            Die ersten beiden Sätze sind ausführbar, der letzte Satz jedoch nicht.
                            

                                

                                     Aus 2024-04-19 15:37:47
                                

                                

                                    
  0 

                                    
1

                                    
2098

                                

                            

                        

                    

                    

                                        

                        

                            Im übergeordneten Fenster erfolgt keine Ausgabe
                            document.onclick = function(){ window.opener.document.write('Ich bin die Ausgabe des unter...
                            

                                

                                     Aus 2024-04-18 23:52:34
                                

                                

                                    
  0 

                                    
1

                                    
1976

                                

                            

                        

                    

                    

                                        

                        

                            Wo gibt es die Kursunterlagen zum CSS-Mindmapping?
                            Kursunterlagen
                            

                                

                                     Aus 2024-04-16 10:10:18
                                

                                

                                    
  0 

                                    
0

                                    
2048

                                

                            

                        

                    

                    

                                    

            


            

                

                    
verwandte Themen

                    Mehr>
                    
                

                

                    
                

            

        

    


    

    
    
        

            
Beliebte Empfehlungen

            

                
            

        

        
        

            

                
Beliebte Tutorials

                Mehr>
                
            

            

                

                    
Verwandte Tutorials 

                    
Beliebte Empfehlungen

                    
Aktuelle Kurse

                

                
                
                
            

            
        


        

            

                
Neueste Downloads

                Mehr>
                
            

                        
            

                

                    
Web-Effekte 

                    
Quellcode der Website

                    
Website-Materialien

                    
Frontend-Vorlage