简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)
Rumah > pembangunan bahagian belakang > tutorial php > teks badan

phpexcel 生成报表

WBOY
Lepaskan： 2016-06-13 13:19:57
asal
1171 orang telah melayarinya

phpexcel 生成表格

Salin selepas log masuk


	/*
 	 * 导出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;
 	}
 	
Salin selepas log masuk

?
 

                 
              
              
        
            
                

            

            

                                

                    Label berkaitan：
                    

                        gt                    

                

                                
sumber：php.cn

                            

            

                                    
                        Artikel sebelumnya： ppframe有关问题 
                    
                                    
                        Artikel seterusnya： php压缩文件后出现的解压异常 
                    
                            

            

       
                
Kenyataan Laman Web ini

                
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn

            

           

            

                
Artikel terbaru oleh pengarang

                
            

            

                
Isu terkini

                

                                        

                        

                            function_exists() tidak boleh menentukan fungsi tersuai
                            Ujian fungsi () {return true;} jika (function_exists ('test')) {echo "test is functio...
                            

                                

                                     daripada 2024-04-29 11:01:01
                                

                                

                                    
  0 

                                    
2

                                    
1769

                                

                            

                        

                    

                    

                                        

                        

                            Bagaimana untuk memaparkan versi mudah alih Google Chrome
                            Hello cikgu, bagaimana saya boleh menukar Google Chrome kepada versi mudah alih?
                            

                                

                                     daripada 2024-04-23 00:22:19
                                

                                

                                    
  0 

                                    
10

                                    
1906

                                

                            

                        

                    

                    

                                        

                        

                            Tetingkap anak mengendalikan tetingkap induk, tetapi output tidak bertindak balas.
                            Dua ayat pertama boleh dilaksanakan, tetapi ayat terakhir tidak boleh dilaksanakan.
                            

                                

                                     daripada 2024-04-19 15:37:47
                                

                                

                                    
  0 

                                    
1

                                    
1624

                                

                            

                        

                    

                    

                                        

                        

                            Tiada output dalam tetingkap induk
                            document.onclick = function(){ window.opener.document.write('Saya adalah output tetingkap ...
                            

                                

                                     daripada 2024-04-18 23:52:34
                                

                                

                                    
  0 

                                    
1

                                    
1534

                                

                            

                        

                    

                    

                                        

                        

                            Di manakah perisian kursus tentang pemetaan minda CSS?
                            Perisian kursus
                            

                                

                                     daripada 2024-04-16 10:10:18
                                

                                

                                    
  0 

                                    
0

                                    
1570

                                

                            

                        

                    

                    

                                    

            

            

                

                    
Topik-topik yang berkaitan

                    Lagi>
                    
                

                

                    
                

            

        

    

    

        

            
Cadangan popular

            

                
            

        

        
        

            

                
Tutorial Popular

                Lagi>
                
            

            

                

                    
Tutorial berkaitan 

                    
Cadangan popular

                    
Kursus terkini

                

                
                
                
            

            
        


        

            

                
Muat turun terkini

                Lagi>
                
            

                        
            

                

                    
kesan web 

                    
Kod sumber laman web

                    
Bahan laman web

                    
Templat hujung hadapan

                

                
                
                
                
            

            
        

    





    

        

            

                

                    

                        Tentang kita
                        Penafian
                        Sitemap
                        

                    

                    
Laman web PHP Cina：Latihan PHP dalam talian kebajikan awam，Bantu pelajar PHP berkembang dengan cepat！