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);
}
}
}