PHP写excel

PHP写excel包括标题

/**
	 * 将联系人导出为excel
	 * @param $filename string 导出文件名
	 * @param $titles array 标题数组
	 * @param $elements array(array()) 元素数组
	 */
	public function exportFileContacts($filename, $titles, $elements){
		try {
			$objPHPExcel = new PHPExcel();
			$objPHPExcel->setActiveSheetIndex(0); //设置活动页面
			$activeSheet = $objPHPExcel->getActiveSheet(); //获取当前活动页面
			$activeSheet->getDefaultColumnDimension()->setWidth(16); //设置列宽16
			$activeSheet->setTitle("联系人"); //设置sheet的名称
			
			//设置标题，单元格第一列为0，第一行为1
			$colNum = count($titles);
			for($i = 0; $i < $colNum; $i++){
				$activeSheet->getStyleByColumnAndRow($i, 1)->getFont()->setBold(true); //设置标题加粗
				$activeSheet->setCellValueByColumnAndRow($i, 1, $titles[$i]);
			}
			
			//设置 body，row从2开始，第一行已经被设置为标题了
			$rowNum = count($elements);
			for($row = 0; $row < $rowNum; $row++){
				for($col = 0; $col < $colNum; $col++){
					$activeSheet->getStyle($this->numToEn($col).($row+2))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
					$activeSheet->setCellValue($this->numToEn($col).($row+2)," ".$elements[$row][$col]);  //在写入Excels单元格的内容之前加一个空格，防止长数字被转化成科学计数法
					$activeSheet->getStyle($this->numToEn($col).($row+2))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				}
			}
			
			//获取保存文件名称
			$filetype = $this->getFileType($filename);
            //根据文件类型读取excel文件
            ob_end_clean();
            $objWriter = null;
            if ($filetype == "xlsx") {
                $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
				header('Content-Disposition: attachment;filename='.urlencode($filename));
				header('Cache-Control: max-age=0');
                               ob_end_clean();
				$objWriter->save('php://output');
				$objPHPExcel->disconnectWorksheets();
        		unset($objPHPExcel);
				exit;
            } elseif ($filetype == "xls") {
                $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
                header('Content-Type: application/vnd.ms-excel');
				header('Content-Disposition: attachment;filename='.urlencode($filename));
				header('Cache-Control: max-age=0');
                                ob_end_clean();
				$objWriter->save('php://output');
				$objPHPExcel->disconnectWorksheets();
        		unset($objPHPExcel);
				exit;
            } else {
                return false;
            }
		} catch (Exception $e) {
			 HWS::logErr('EXCEL ERROR:'.$e->getMessage());
             $errText = "创建错误文件失败，请联系管理员，稍后再试！";
             return array('createFileErr'=>$errText);
		}
	}

/**
	 * 获取文件类型
	 * @param $filenamePath 文件路径或者文件名
	 */
	private function getFileType($filenamePath){
	    if (!$filenamePath){
	        return false;
	    }
        $filenameArr = explode('/', $filenamePath);
	    $count = count($filenameArr);
	    $filename = $filenameArr[$count-1];
	    
	    $filetypeArr = explode('.', $filename);
	    $count = count($filetypeArr);
	    $filetype = $filetypeArr[$count-1];
	    return $filetype;   
	}
	
	/**
	根据给定的数字生成至多两位对应EXCEL文件列的字母
	*/
	private function numToEn($num)
	{
		$asc = 0;
		$en = "";
		$num =(int)$num+1;
		if($num<26)                      //判断指定的数字是否需要用两个字母表示
		{
			if((int)$num<10)
			{
				$asc = ord($num);
				$en =chr($asc+16);
			}
			else
			{
				$num_g = substr($num,1,1);
				$num_s = substr($num,0,1);
				$asc = ord($num_g);
				$en =chr($asc+16+10*$num_s);
			}
		}
		else
		{
			$num_complementation = floor($num/26);
			$en_q = $this->numToEn($num_complementation);
			$en_h = $num%26 != 0 ? $this->numToEn($num-$num_complementation*26):"A";
			$en = $en_q.$en_h;
		}
		return $en;
	}

 

注：exportFileContacts方法中的两个ob_end_clean();$objPHPExcel->disconnectWorksheets(); unset($objPHPExcel);和是为了清楚缓冲区内容，防止导出的excel出错，打不开，报什么格式化错误。