在網(wǎng)站制作中，經(jīng)常會遇到的功能就是數(shù)據(jù)的導(dǎo)入和導(dǎo)出，PHP導(dǎo)入導(dǎo)出功能的實(shí)現(xiàn)不算很復(fù)雜，因?yàn)橛幸粋€(gè)比較好的插件PHPEXCEL。

最近遇到一個(gè)問題，用phpExcel 導(dǎo)出數(shù)據(jù)的時(shí)候發(fā)現(xiàn)報(bào)錯(cuò) Invalid cell coordinate  一直在用這個(gè)插件沒有遇到過這樣的問題，網(wǎng)上找了一下原來是當(dāng)數(shù)據(jù)大于26列的時(shí)候 26個(gè)字母用完了需要對列數(shù)進(jìn)行處理 大于26應(yīng)該AA,AB,AC,AD…排列下去。PHPExcel數(shù)據(jù)導(dǎo)出方式是一行一行的循環(huán)處理
比如：第一行是標(biāo)題，輸出的時(shí)候以A1,B1,C1,D1…AA1,BB1,CC1順序循環(huán)。
**
 * PHPExcel使用類
 * +------------------------------------使用------------------------------------+
 * 導(dǎo)入excel表
 * vendor('PHPExcel.PHPExcelUser');
 * $file='./cs.xlsx';
 * $excel=new \PHPExcelUser($file);
 * $data=$excel->excelImport(1);//從第一行讀取,如果有非字符串和函數(shù)的話則會返加一個(gè)錯(cuò)誤的數(shù)組
 * 導(dǎo)出excel表
 * vendor('PHPExcel.PHPExcelUser');
 * $file='./cs.xlsx';
 * $excel=new \PHPExcelUser($file);
 * $excel->excelExport($data=array(),$title=array());
 * +------------------------------------使用------------------------------------+
 * Class FileTemp
 */
class  PHPExcelUser {
    public $excelFilename; //excel導(dǎo)入或?qū)С龅奈募拿Q
    public $strCheck; //是否進(jìn)行導(dǎo)入全部字符串驗(yàn)證
    public $funCheck; //是否進(jìn)行導(dǎo)入函數(shù)驗(yàn)證
    public $errorArr = array(); //當(dāng)需要檢查并且有錯(cuò)誤時(shí)返回的錯(cuò)誤統(tǒng)計(jì)數(shù)組
 
    /**
     * 構(gòu)造函數(shù)
     * @param $excelFilename 文件名稱
     * @param bool $strCheck 是否進(jìn)行字符串驗(yàn)證檢測
     * @param bool $funCheck 是否進(jìn)行函數(shù)驗(yàn)證檢測
     */
    public function __construct($excelFilename, $strCheck = false, $funCheck = false) {
        if (!$excelFilename) {
            try {
                $error = '請輸入Excel文件名稱';
                throw new Exception($error);
            } catch (Exception $e) {
                echo $e->getMessage();
            }
        }
        $this->excelFilename = $excelFilename;;
        $this->strCheck = $strCheck;
        $this->funCheck = $funCheck;
    }
/**
     * 導(dǎo)出成Excel文件
     * @param array $data 一個(gè)二維數(shù)組,結(jié)構(gòu)如同從數(shù)據(jù)庫查出來的數(shù)組
     * @param array $title excel的第一行標(biāo)題,一個(gè)數(shù)組,如果為空則沒有標(biāo)題
     * @exapme
     * $arr = $Model -> select();
     * excelExport($arr,array('id','賬戶','密碼','昵稱'),'文件名');
     */
    function excelExport($data = array(), $title = array()) {
        require_once('PHPExcel.php');
        error_reporting(E_ALL);
        date_default_timezone_set('Europe/London');
        $objPHPExcel = new PHPExcel();
 
        /*以下是一些設(shè)置 ，什么作者  標(biāo)題啊之類的*/
        $objPHPExcel->getProperties()->setCreator($this->excelFilename)
            ->setLastModifiedBy($this->excelFilename)
            ->setTitle("數(shù)據(jù)EXCEL導(dǎo)出")
            ->setSubject("數(shù)據(jù)EXCEL導(dǎo)出")
            ->setDescription("備份數(shù)據(jù)")
            ->setKeywords("excel")
            ->setCategory("result file");
        /*以下就是對處理Excel里的數(shù)據(jù)， 橫著取數(shù)據(jù)，主要是這一步，其他基本都不要改*/
        $model = $objPHPExcel->setActiveSheetIndex(0);
        $key = 65; //只有A-Z
        $key2 = ord("@");//@--64
        foreach ($title as $k => $v) {
        if($key>ord("Z")){
            $key2 += 1;
            $key = ord("A");
            $colum = chr($key2).chr($key);//超過26個(gè)字母時(shí)才會啟用  
        }else{
            if($key2>=ord("A")){
                $colum = chr($key2).chr($key);//超過26個(gè)字母時(shí)才會啟用  
            }else{
                $colum = chr($key);
            }
        }
 
            $model->setCellValue($colum . '1', $v);
            $key++;
        }
        $column = 2;  //從第二行開始
      foreach($data as $key => $rows){ //行寫入  
        $span = ord("A");  
        $span2 = ord("@");
        foreach ($rows as $m => $n) {
             foreach($title as $k=>$v){  
             if($span>ord("Z")){  
                $span2 += 1;  
                $span = ord("A");  
           $j = chr($span2).chr($span);//超過26個(gè)字母時(shí)才會啟用  
            }else{  
                if($span2>=ord("A")){  
                    $j = chr($span2).chr($span);  
                }else{  
                    $j = chr($span);  
                }  
            }
            }   
                //dump($j.$column.'|'.$n);
                $model->setCellValue($j . $column, $n);
               //數(shù)據(jù)從第二行開始 A2,B2,C2,D2… 開始循環(huán)
                $span++;         
         }
 
  
        $column++;  
    }  
    //exit;
        $objPHPExcel->getActiveSheet()->setTitle('siteape');
        $objPHPExcel->setActiveSheetIndex(0);
        header('Content-Type: applicationnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $this->excelFilename . '.xls"');
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }
 
    /**
     * Excel讀取
     * @param $begin 開始讀取的行數(shù)
     * @return array|string
     */
    function excelImport($begin) {
        require_once('PHPExcel.php');
        $filename = $this->getRealFile($this->excelFilename);
        //建立reader對象
        $PHPReader = new PHPExcel_Reader_Excel2007();
        if (!$PHPReader->canRead($filename)) {
            $PHPReader = new PHPExcel_Reader_Excel5();
            if (!$PHPReader->canRead($filename)) {
                return array();
            }
        }
        //建立excel對象，此時(shí)你即可以通過excel對象讀取文件，也可以通過它寫入文件
        $PHPExcel = $PHPReader->load($filename);
        /*讀取excel文件中的第一個(gè)工作表*/
        $currentSheet = $PHPExcel->getSheet(0);
        /*取得最大的列號*/
        $allColumn = $currentSheet->getHighestColumn();
        /*取得一共有多少行*/
        $allRow = $currentSheet->getHighestRow();
        $returnCell = '';
        //循環(huán)讀取每個(gè)單元格的內(nèi)容。注意行從1開始，列從A開始
        for ($rowIndex = $begin; $rowIndex <= $allRow; $rowIndex++) {
            for ($colIndex = 'A'; $colIndex <= $allColumn; $colIndex++) {
                $addr = $colIndex . $rowIndex;
                $cell = $currentSheet->getCell($addr)->getCalculatedValue();
                if ($cell instanceof PHPExcel_RichText) {
                    //富文本轉(zhuǎn)換字符串
                    $returnCell[$rowIndex][$colIndex] = $cell->__toString();
                } else {
                    $returnCell[$rowIndex][$colIndex] = $cell;
                }
            }
        }
        return $returnCell;
    }
 
    /**
     * 獲取正確的路徑
     * @param $file
     * @return string
     */
    public function getRealFile($file) {
        $file = $file ? $file : $this->excelFilename;
        $error = '';
        if (substr($file, 0, 4) == 'http') {
            $realfile = $file;
        } elseif (!$file) {
            $error = '請指定文件路徑';
        } elseif (file_exists($file)) {
            $realfile = $file;
        } elseif (file_exists('.' . $file)) {
            $realfile = '.' . $file;
        } elseif (file_exists('..' . $file)) {
            $realfile = '..' . $file;
        } else {
            $error = '您輸入的文件不存在';
        }
        if ($error != '') {
            try {
                throw new Exception($error);
            } catch (Exception $e) {
                echo $e->getMessage();
            }
        } else {
            return $realfile;
        }
    }
 
    /**
     * 根據(jù)數(shù)據(jù)與字段匹配內(nèi)容生成標(biāo)準(zhǔn)的表頭
     * @param $titleArr 二維數(shù)組，field=>title
     * @param $data
     * @return array
     */
    public function getArrayTitle($titleArr, $data) {
        $reData = array();
        $firstData = $data[0]; //只需要取第一條數(shù)據(jù)即可
        if ($firstData) {
            foreach ($firstData as $k => $v) {
                $title = $titleArr[$k];
                $reData[] = $title;
            }
        } else {
            //直接輸入默認(rèn)順序的title
            foreach ($titleArr as $k => $v) {
                $reData[] = $v;
            }
        }
        return $reData;
    }
}