[PHP]Phpexcel导入时间格式数据处理

excel时间格式得到的是一串数字，

//转换excel日期,date是数字
private function excelTime($date, $time = false) {
    if(is_numeric($date)){
        if(function_exists('GregorianToJD')){
            if (is_numeric( $date )) {
                $jd = GregorianToJD( 1, 1, 1970 );
                $gregorian = JDToGregorian( $jd + intval ( $date ) - 25569 );
                $date = explode( '/', $gregorian );
                $date_str = str_pad( $date [2], 4, '0', STR_PAD_LEFT )

                    ."-". str_pad( $date [0], 2, '0', STR_PAD_LEFT )

                    ."-". str_pad( $date [1], 2, '0', STR_PAD_LEFT )

                    . ($time ? " 00:00:00" : '');

                return $date_str;
            }
        }else{
            $date=$date>25568?$date+1:25569;
            /*There was a bug if Converting date before 1-1-1970 (tstamp 0)*/
            $ofs=(70 * 365 + 17+2) * 86400;
            $date = date("Y-m-d",($date * 86400) - $ofs).($time ? " 00:00:00" : '');
        }
    }
    return $date;
}

//获取excel数据

private function getExcelDate($file_path ='',$sheet = 'Sheet1',$start_row = '2',$cloumn = array()){
    //设置超时时间
    set_time_limit(0);
    \think\Loader::import('PHPExcel/PHPExcel',VENDOR_PATH ,EXT);
    $data = array();
    if(trim($file_path) == '' || empty($cloumn)){
        return $data;
    }
    if(trim($sheet) == ''){$sheet='Sheet1';}
    if(!is_numeric($start_row) && $start_row < 1){$start_row='2';}
    //判断文件格式
    $extension = strtolower(pathinfo($file_path,PATHINFO_EXTENSION)) ;
    if ( !in_array($extension, array('xls','xlsx'))) {
        return $data;
    }
    $PHPExcel = new \PHPExcel() ;
    $PHPReader = new \PHPExcel_Reader_Excel2007();
    if( !$PHPReader->canRead($file_path) ){
        $PHPReader = new \PHPExcel_Reader_Excel5();
        if( !$PHPReader->canRead($file_path) ){
            return $data;
        }
    }
    //读取excel
    $PHPExcel = $PHPReader->load( $file_path );
    //获取指定的工作集
    $_currentSheet = $PHPExcel ->getSheetByName($sheet);

    $_allRow = $_currentSheet->getHighestRow(); //获取Excel中信息的行数
    $_allColumn = $_currentSheet->getHighestColumn();//获取Excel的列数
    $highestRow = intval( $_allRow ) ;
    //从第二行开始读取
    for( $row = $start_row; $row <= $highestRow; $row++ ) {
        foreach($cloumn as $c_key => $c_val){
            $data[$row][$c_key] = trim((string)$_currentSheet->getCellByColumnAndRow($c_val,$row)->getValue());
            $data[$row][$c_key] = $this -> filter_emoji($data[$row][$c_key]);//过滤emoji表情（见：http://www.cnblogs.com/hhao321/p/8440776.html）
            if($c_key == 'date_time' ){
                $data[$row][$c_key] = $this->excelTime($data[$row][$c_key]);
            }
            if($c_key =='interpreter_name'){
                $data[$row][$c_key] = $this -> sbc2Dbc($data[$row][$c_key]);//替换全角符号（见：http://www.cnblogs.com/hhao321/p/8441121.html）
            }

        }
    }
    return $data;
}

//读取excel

public function test(){
    set_time_limit(0);//不限制执行时间
    $file_dir = str_replace('\\','/','D:\tpm_data.xlsx');
    $cloumn = ['mingzi'=>'1','zhanghu'=>'0','shouji'=>'3','youxiang'=>'4','hezuozhuangtai' =>'23'];
    $file_arr = $this -> getExcelDate($file_dir,'可导入译搜的合作类型',2,$cloumn);
    if(is_array($file_arr) && !empty($file_arr)){
        foreach($file_arr as $value){
            $this -> local_db -> table('tpm_data') ->insert($value);
        }
    }
}