当前位置 : 主页 > 网络编程 > PHP >

PHP导出EXCEL的类方法,一般通用方法

来源:互联网 收集:自由互联 发布时间:2021-06-28
PHP导出EXCEL的通用类方法 fieldnames = $val;}public function setTitle($val){$this-title = $val;}public function import($rows){$data = $rows;if (!is_array($data))return $data;if (empty($data))return '没有数据可导!';error_reporti
PHP导出EXCEL的通用类方法
 fieldnames = $val;
	}
	
	public function setTitle($val){
		$this->title = $val;
	}
	
	public function import($rows){				
		$data = $rows;
		
		if (!is_array($data))return $data;
		if (empty($data))return '没有数据可导!';	

		error_reporting(E_ALL); //开启错误
        set_time_limit(0); //脚本不超时         
		date_default_timezone_set('Etc/GMT-8'); //设置时区
		
        if (IMPORT_XLS::$objPHPExcel == null){
        	IMPORT_XLS::$objPHPExcel = new PHPExcel();	
        }		
		$this->toExcel($data);
	}	
	
	private function toExcel($data){
		
		if (is_array($this->fieldnames) && isset($this->fieldnames)){
			$this->General_Excel($data,$this->title,$this->fieldnames);
		}		
	}	
	
	private function General_Excel($data,$title,$fieldnames){
		if (IMPORT_XLS::$objPHPExcel == null)return 'PHPExcel对象没有创建成功!';
		
		$arr = null;
		$strs = '';
		foreach ($fieldnames as $k=>$v){
			$strs .= $k.',';
		}
		$strs = substr($strs,0,count($strs)-2);
		$arr = explode(',',$strs);
		
		$startchar = $arr[0];
		$lastchar = end($arr);
		
		$objPHPExcel = IMPORT_XLS::$objPHPExcel;
		$objPHPExcel->getProperties()->setCreator("autumn-deer")
		->setLastModifiedBy("autumn-deer")
		->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");				
		
		//设置行高度
		$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(22);
		
		$objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
		
		//set font size bold
		
		$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);
		$objPHPExcel->getActiveSheet()->getStyle($startchar.'2:'.$lastchar.'2')->getFont()->setBold(true);
		
		$objPHPExcel->getActiveSheet()->getStyle($startchar.'2:'.$lastchar.'2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
		$objPHPExcel->getActiveSheet()->getStyle($startchar.'2:'.$lastchar.'2')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
				
		$objPHPExcel->getActiveSheet()->getStyle($startchar.'1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
		foreach ($arr as $el){
			//set width
			$objPHPExcel->getActiveSheet()->getColumnDimension($el)->setWidth(20);
			//设置水平居中
			$objPHPExcel->getActiveSheet()->getStyle($el)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		}
		
		//合并单元格
		$objPHPExcel->getActiveSheet()->mergeCells($startchar.'1:'. $lastchar.'1');
		
		// set table header content		
		$objPHPExcel->setActiveSheetIndex(0)
		->setCellValue($startchar."1", $title.' 时间:' . date('Y-m-d H:i:s'));		
		foreach ($fieldnames as $k=>$v){
			$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue($k."2", $v);
		}
		
		//填充数据
		if(isset($data)){		
			$j = 3;
			foreach ($data as $row) {		
				$count = count($row);				
				$i = 0;				
				foreach ($row as $k=>$v){	
					$objPHPExcel->getActiveSheet(0)->setCellValue($arr[$i].$j, $v);
					$i++;
				}						
				$j++;
			}
		}
		
		$objPHPExcel->getActiveSheet()->setTitle($title);
		$objPHPExcel->setActiveSheetIndex(0);
		
		ob_end_clean();//清除缓冲区,避免乱码
		
		header('Content-Type: application/vnd.ms-excel;charset=utf-8');
		header('Content-Disposition: attachment;filename="'.$title.'(' . date('Y-m-d H:i:s') . ').xls"');
		header('Cache-Control: max-age=0');
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save('php://output');
		exit();
	}
}
网友评论