/**
     * @param string $sql
     * @param array $columns
     * @param string $field
     * @param string $other
     * @param string $fileName
     * @param string $db
     * @throws CException
     * @throws PHPExcel_Exception
     * @throws PHPExcel_Reader_Exception
     * @throws PHPExcel_Writer_Exception  PHPExcel---- excel文件下载
     */
    public static function downLoad($sql=‘‘,$columns=[],$field=‘‘,$other=‘‘,$fileName=‘‘,$db=‘db‘){
        if(empty($fileName)){
            $fileName=date("YmdHis");
        }
        if(empty($sql)){
            exit(‘参数不存在‘);
        }
        if(!is_array($columns)){
            exit(‘格式错误‘);
        }
        Yii::$enableIncludePath = false;

        Yii::import(‘application.extensions.PHPExcel.PHPExcel‘, 1);
        $phpexcel = new PHPExcel;
        $phpexcel->getProperties()->setCreator("Maarten Balliauw")
            ->setLastModifiedBy("Maarten Balliauw")
            ->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");
        $i=0;
        foreach($columns as $key1=>$value1){
            $phpexcel->setActiveSheetIndex(0)->setCellValue(chr(65+$i).‘1‘, $value1);
            $i++;
        }
        $i=0;
        $res = Yii::app()->$db->createCommand($sql)->queryAll();
        foreach($res as $row){
            $j=0;
            $item = self::changeDataType($row,$field,$other); //这里需要根据调用的类具体修改
            foreach($columns as $key1=>$value1){
                $columnvalue=$item["$key1"];
                $phpexcel->setActiveSheetIndex(0)->setCellValueExplicit(chr(65+$j).($i+2),$columnvalue);
                $j++;
            }
            $i++;
        }

        $phpexcel->setActiveSheetIndex(0);
        header(‘Expires: ‘ . date(DATE_RFC1123));
        header(‘Cache-Control: no-store, no-cache, must-revalidate,‘. ‘  pre-check=0, post-check=0, max-age=0‘);
        header(‘Last-Modified: ‘ . date(DATE_RFC1123));
        header(‘Content-Type: application/vnd.ms-excel‘);
        header(‘Content-Disposition: attachment; filename="‘.$fileName.‘.xls"‘);
        $objWriter = PHPExcel_IOFactory::createWriter($phpexcel, ‘Excel5‘);
        $objWriter->save(‘php://output‘);
        exit;
    }
    /**
     * @param $row
     * @param $field [字段=>‘date|st‘]
     * @param array $other [] 和field对应
     * @return mixed
     *
     */
    public static function changeDataType($row, $field,$other=[])
    {
        foreach($field as $key=>$value){
            switch($value) {
                case ‘date‘:
                    $row[$key] = date("Y-m-d H:i:s",$row[$key]);
                    break;
                case ‘st‘:
                    $row[$key] = empty($other)? $row[$key]:$other[$key][$row[$key]]; break;
                case ‘eq‘:
                    $row[$key] = empty($other)? $row[$key]:($row[$key]==$other[$key][‘eqVal‘] ? $other[$key][‘yes‘]:$other[$key][‘no‘]);
                    break;
                case ‘filterEmoji‘:
                    $row[$key] = self::filterEmoji( $row[$key]);
                    break;
            }
        }
        return $row;
    } 
    
       /**
     * @param $filePath
     * @param $ext
     * @return array
     * @throws CException 
     * @throws PHPExcel_Reader_Exception  PHPExcel数据导出
     */
    public static function getExcelData($filePath,$ext){
        Yii::$enableIncludePath = false;
        Yii::import(‘application.extensions.PHPExcel.PHPExcel‘, 1);
        $ext= strtolower($ext)===‘xls‘ ?  ‘Excel5‘: ‘Excel2007‘;
        $objReader = \PHPExcel_IOFactory::createReader($ext);//创建读取实例
        $objPHPExcel = $objReader->load($filePath, $encode = ‘utf-8‘);//加载文件
        $sheet = $objPHPExcel->getSheet(0);//取得sheet(0)表
        $highestRow = $sheet->getHighestRow(); // 取得总行数
        $highestColumn = $sheet->getHighestColumn(); // 取得总列数
        $arr = array(‘A‘, ‘B‘, ‘C‘, ‘D‘, ‘E‘, ‘F‘, ‘G‘, ‘H‘, ‘I‘, ‘J‘, ‘K‘, ‘L‘, ‘M‘, ‘N‘, ‘O‘, ‘P‘, ‘Q‘, ‘R‘, ‘S‘, ‘T‘, ‘U‘, ‘V‘, ‘W‘, ‘X‘, ‘Y‘, ‘Z‘);
        // 一次读取一列
        $columnLen = array_search($highestColumn,$arr);
        $retArr = array();
        for ($row = 2; $row <= $highestRow; $row++) {
            $row_arr = array();
            for ($column = 0; $column<=$columnLen; $column++) {
                $val = $sheet->getCellByColumnAndRow($column, $row)->getValue();
                $row_arr[] = $val;
            }
            $retArr[] = $row_arr;
        }
        return $retArr;
    }
 /**
     * @param $mobile
     * @return bool
     * 验证手机号是否正确
     */
    function isMobile($mobile) {
        if (!is_numeric($mobile)) {
            return false;
        }
        return preg_match(‘#^1[3,4,5,7,8,9]{1}[\d]{9}$#‘, $mobile) ? true : false;
    }


    /**
     * 正则表达式验证email格式
     *
     * @param string $str 所要验证的邮箱地址
     * @return boolean
     */
    function isEmail($str) {
        if (!$str) {
            return false;
        }
        return preg_match(‘#[a-z0-9&\-_.]+@[\w\-_]+([\w\-.]+)?\.[\w\-]+#is‘, $str) ? true : false;
    }
    /** 获取客户端ip*/
    public static function getClientIP()
    {
        static $ip = NULL;
        if ( $ip !== NULL )
            return $ip;
        if ( isset( $_SERVER[‘HTTP_X_FORWARDED_FOR‘] ) ) {
            $arr = explode( ‘,‘, $_SERVER[‘HTTP_X_FORWARDED_FOR‘] );
            $pos = array_search( ‘unknown‘, $arr );
            if ( false !== $pos )
                unset( $arr[$pos] );
            $ip = trim( $arr[0] );
        } elseif ( isset( $_SERVER[‘HTTP_CLIENT_IP‘] ) ) {
            $ip = $_SERVER[‘HTTP_CLIENT_IP‘];
        } elseif ( isset( $_SERVER[‘REMOTE_ADDR‘] ) ) {
            $ip = $_SERVER[‘REMOTE_ADDR‘];
        }
        // IP地址合法验证
        $ip = ( false !== ip2long( $ip ) ) ? $ip : ‘0.0.0.0‘;
        return $ip;
    }
    /** 特殊字符的处理 主要用于微信昵称*/
    public static function filterEmoji($str){
         $str = preg_replace_callback(
           ‘/./u‘,
               function (array $match) {
                return strlen($match[0]) >= 4 ? ‘‘ : $match[0];
               },
           $str);
          return $str;
     }
    public static function urlEncode($str) {  
        if(is_array($str)) {  
            foreach($str as $key=>$value) {  
                $str[urlencode($key)] = self::urlEncode($value);  
            }  
        } else {
            $str = ($str!==false && $str!==true) ? urlencode($str) : $str;
        }
        return $str;
    }