phpExcel在封裝

<?php
/**
 * 數組生成Excel
 * @author zouhao  [email protected]
 * 使用示例
 * $excel =new Excel();
 $data=array(
   array('id'=>1,'name'=>'天命1'),
   array('id'=>2,'name'=>'天命2')
  );
 $header=array('ID','角色名');
 $excel->setFileName('aaa');
 $excel->setTitle('工作區1');
 $excel->create($data,$header);
 */
class Excel {
 private $excelObj;
 private $fileName='download.xls';
 /**
  * 設置下載時文件名
  * @param string $fileName
  */
 public function setFileName($fileName){
  $this->fileName=$fileName.'.xls';
 }
 /**
  * 設置標題
  * @param string $title         
  */
 public function setTitle($title) {
  $this->excelObj->getActiveSheet ()->setTitle ( $title );
 }
 public function __construct() {
  // 先取消原框架的自動注冊機制,避免和excel自動加載機制沖突
  spl_autoload_unregister ( 'autoload' );
  require LIBRARY_PATH . '/PHPExcel/PHPExcel.php';
  $this->excelObj = new PHPExcel ();
 }
 /**
  * 根據總數,返回列數組
  *
  * @param int $count         
  * @return array
  */
 private function getCharByNumber($data) {
  // 自動減去頭部
  $count = count ( $data ['0'] );
  $keys=array();
  for($number = 1; $number <=$count; $number ++) {
   $divisor= intval($number / 26);
   $char = chr ( 64 + $number % 26 );
   $char = $divisor == 0 ? $char : chr ( 64 +$divisor) . $char;
   $keys [] = $char;
  }
  return $keys;
 }
 /**
  * 生成Excel表格
  * @param array $data  二維數組
  * @param array $replace 需要替換的數組
  */
 public function create($data,$header=array(),$replace = null) {
  empty($data) and exit('沒有數據');
  $keys = $this->getCharByNumber ( $data );
  $this->createHeader ( $header, $keys );
  $j=0;
  foreach ( $data as $i=>$vo ) {
   $j=0;
   foreach ( $vo as $key => $item ) {
    if (isset ( $replace [$key] )){
     $this->excelObj->setActiveSheetIndex ( 0 )->setCellValue ( $keys [$j] . ($i + 2), $replace [$key] [$item] );
    }else{
     $this->excelObj->setActiveSheetIndex ( 0 )->setCellValue ( $keys [$j] . ($i + 2), $item );
    }
    ++$j;
   }
  }
  //輸出到臨時緩沖區  提供下載
  header ( "Content-Type: application/force-download" );
  header ( "Content-Type: application/octet-stream" );
  header ( "Content-Type: application/download" );
  header ( 'Content-Disposition:inline;filename="'.$this->fileName.'"' );
  header ( "Content-Transfer-Encoding: binary" );
  header ( "Cache-Control: must-revalidate, post-check=0, pre-check=0" );
  header ( "Pragma: no-cache" );
  $objWriter = PHPExcel_IOFactory::createWriter ( $this->excelObj, 'Excel5' );
  $objWriter->save ( 'php://output' );
 }
 /**
  * 創建頭部
  *
  * @param array $data         
  */
 private function createHeader($header, $keys) {
  $header = array_combine ( $keys, $header );
  foreach ( $header as $key => $vo ) {
   $this->excelObj->setActiveSheetIndex ( 0 )->setCellValue ( "{$key}1", $vo );
  }
 }
}

使用示例:

$excel =new Excel();
 $data=array(
   array('id'=>1,'name'=>'天命1'),
   array('id'=>2,'name'=>'天命2')
  );
 $header=array('ID','角色名');
 $excel->setFileName('aaa');
 $excel->setTitle('工作區1');
 $excel->create($data,$header);也就是說,從數據庫裡拿出的二維數組可以直接創建,

另外,有的字段為type,保存為int類型的,代表某一個狀態的.可以參考create第三個參數

舉例:

$excel =new Excel();
 $data=array(
   array('id'=>1,'name'=>'天命1','type'=>1),
   array('id'=>2,'name'=>'天命2','type'=>2)
  );
        $replace['type']=array(1=>'猛男',2=>'萌女');
 $header=array('ID','角色名');
 $excel->setFileName('aaa');
 $excel->setTitle('工作區1');
 $excel->create($data,$header,$replace);