对PHPExcel的封装，更便捷的输出Excel文件

require_once FULL_ADMIN_DIR.'include/PHPEXCEL/Classes/PHPExcel.php';
require_once FULL_ADMIN_DIR.'include/PHPEXCEL/Classes/PHPExcel/IOFactory.php';
require_once FULL_ADMIN_DIR.'include/PHPEXCEL/Classes/PHPExcel/RichText.php';

class ExcelWriter{
	private $objPHPExcel=NULL;
	private $objReader=NULL;
	private $activeSheet=NULL;
	private $db=NULL;
	private $xlstpl='';
	private $outfilename = '';
	protected $valuelist = NULL;
	private $startrow = 1;
	private $currentrow = 1;
	private $lastsave = '';
	private $sqlstr = '';
	private $valuemap = array();

	function __construct($xlstpl=''){
		$this->objPHPExcel = new PHPExcel();
		$this->xlstpl = $xlstpl;
	}
	
	function loadTPL($tpl,$startrow = 1,$activesheet=0){
		$tpl = $tpl?$tpl:$this->xlstpl;
		if($this->objPHPExcel && $tpl){
			$this->objReader = PHPExcel_IOFactory::createReader('Excel2007');
			$this->objPHPExcel = $this->objReader->load($tpl);
			$this->objPHPExcel->setActiveSheetIndex($activesheet);
			$this->activeSheet = $this->objPHPExcel->getActiveSheet();
			$this->startrow = $startrow;
			$this->currentrow = $startrow;
		}
	}
	
	function setValueList($valuelist){
		$this->valuelist = $valuelist;
	}
	
	function setSQL($value){//设置要查询的语句
		$this->sqlstr = $value;
	}
	
	function setDb($value){
		$this->db = $value;
	}
	function loadFromDb(){
		if(!empty($this->db) && !empty($this->sqlstr)){//如果数据库存在，并且查询语句也存在
			$this->valuelist = array();//清空数据列表
			$query = $this->db->query($this->sqlstr);
			while($value = $this->db->fetch_array($query)){
				$this->valuelist[] = $value;
			}
		}
	}
	
	function alterValuelist(){
		//需要改写的函数，对已经导入的数据列的数据进行整理，以达到所需要的输出效果
	}
	
	function setStartRow($startrow){
		$this->startrow = $startrow;
	}
	
	function setValueMap($valueMapArr){//以数组形式规定Excel哪一列对应数据的哪一列,例如array('A'=>'id','B'=>'name'),如果'A'=>'_autoincrement'，那么A列应该填充序号
		$this->valuemap = $valueMapArr;
	}
	
	function fillExcel(){//将数组数据填充到Excel
		if(!empty($this->valuemap) && !empty($this->valuelist)){
			$num = 0;
			foreach($this->valuelist as $value){
				$num++;
				foreach($this->valuemap as $excelcol=>$valuecol){
					if($valuecol == '_autoincrement')$value[$valuecol] = $num;
					if(isset($value[$valuecol])){						
						$this->setCellValue($excelcol.$this->currentrow,$value[$valuecol]);
					}
				}
				$this->currentrow ++;
			}
			return $this->currentrow - $this->startrow + 1;
		}else{
			return 0;
		}
	}
	
	function setActiveSheet($active){//改变当前激活sheet
		$this->objPHPExcel->setActiveSheetIndex($active);
		$this->activeSheet = $this->objPHPExcel->getActiveSheet();
	}
	
	function setCellValue($cellxy,$value){
		$this->activeSheet->setCellValue($cellxy,$value);
	}
	
	function setRowHeight($row,$height){
		$this->activeSheet->getRowDimension("row")->setRowHeight($height);
	}
	
	function mergCells($fromCell,$toCell){
		$this->activeSheet->mergeCells($fromCell.':'.$toCell);
	}
	
	function setFontSize($fromCell,$toCell,$size){
		$this->activeSheet->getStyle($fromCell.':'.$toCell)->getFont()->setSize($size);
	}
	
	function setFontName($fromCell,$toCell,$fontName){
		$this->activeSheet->getStyle($fromCell.':'.$toCell)->getFont()->setName($fontName);
	}
	
	function setCellBorder($fromCell,$toCell,$borderType=\PHPExcel_Style_Border::BORDER_THIN){
		$styleThinBlackBorderOutline = array(
			'borders' => array(
				'allborders' => array( //设置全部边框
					'style' => $borderType //粗的是thick
				),

			),
		);
		$this->activeSheet->getStyle($fromCell.':'.$toCell)->applyFromArray($styleThinBlackBorderOutline);

	}
	
	function saveExcel($target){
		$objWriter = PHPExcel_IOFactory::createWriter($this->objPHPExcel, 'Excel2007');
		$objWriter->save($target);
		$this->lastsave = $target;
	}
	
	function getLastSave(){
		return $this->lastsave;
	}
};

//用法简介
//首先派生一个类，改写成员函数，对部分原始数据进行处理
class ItemListExcel extends ExcelWriter{
	function alterValuelist(){
		global $_DCACHE;
		if(is_array($this->valuelist)){
			foreach($this->valuelist as $key=>$row){
				$row['IntroduceUnit'] = $row['IntroduceUnit']?$_DCACHE['members'][$row['IntroduceUnit']]['username']:'';//成员单位
				$row['IntroduceUnit2'] = $row['IntroduceUnit2']?$_DCACHE['members'][$row['IntroduceUnit2']]['username']:'';//成员单位2
				$row['ItemName'] = $row['ItemName'];//项目名称
				$row['AggregateInvestment'] = $row['AggregateInvestment']?round($row['AggregateInvestment']/10000,2):'';//协议投资总额
				$row['TIFA'] = $row['TIFA']>0?round($row['TIFA']/10000,2):'';
				$row['MainBusiness'] = $row['MainBusiness'];//主营业务
				$row['Park'] = $row['Park']?$_DCACHE['parks'][$row['Park']]['Name']:'';//园区
				$row['hylb'] = $row['hylb']?$_DCACHE['industries'][$row['hylb']]:'';
				$row['AgreedAcreage'] = $row['AgreedAcreage']>0?$row['AgreedAcreage']:'';
				$row['NewContract'] = $row['NewContract']==1?'是':'';
				$row['NewProject'] = $row['NewProject']==1?'是':'';
				$row['kgrk'] = $row['kgrk']==1?'是':'';
				$row['NewComplete'] = $row['NewComplete']==1?'是':'';
				$row['tcrk'] = $row['tcrk']==1?'是':'';
				$row['Investor'] = $row['Investor'];
				$this->valuelist[$key] = $row;
			}
		}
	}
};

	$ixls = new ItemListExcel();
	$ixls->loadTPL($tpl,$start_row);
	$ixls->setDb($_USERVALUES['database']);
	$ixls->setSQL($sql);
	$ixls->loadFromDb();
	$ixls->alterValuelist();
	$ixls->setValueMap(//以数组形式规定Excel哪一列对应数据的哪一列,例如array('A'=>'id','B'=>'name')
		array(
			'A'=>'_autoincrement',
			'B'=>'IntroduceUnit',
			'C'=>'IntroduceUnit2',
			'D'=>'ItemName',
			'E'=>'AggregateInvestment',
			'F'=>'TIFA',
			'G'=>'MainBusiness',
			'H'=>'Park',
			'I'=>'hylb',
			'J'=>'AgreedAcreage',
			'K'=>'NewContract',
			'L'=>'NewProject',
			'M'=>'kgrk',
			'N'=>'NewComplete',
			'O'=>'tcrk',
			'P'=>'Investor',
		)
	);
	$ixls->fillExcel();
	$ixls->setCellValue('A1',$xlstitle);
	$targetpath = SYS_REPORT_DIR.$filename;
	$ixls->saveExcel($targetpath);