function
excelTime($date, $time = false) {
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;
}
function upload_excel(){
$upload = new \Think\Upload(); // 实例化上传类
$upload->maxSize = 31457280 ; // 设置附件上传大小
$upload->exts = array('jpg', 'gif', 'png', 'jpeg','xls','docx','xlsx'); // 设置附件上传类型
$upload->savePath = './data/upload/';
$upload->autoSub = false ; // 设置附件上传目录 // 上传文件
$info = $upload->upload();
if(!$info) { // 上传错误提示错误信息
$this->error($upload->getError());
}else{
$filename=$info['import']['savepath'].$info['import']['savename'];
$this->success('上传成功');
}
Vendor('PHPExcel.PHPExcel');
$PHPExcel = new \PHPExcel();
$PHPReader = new \PHPExcel_Reader_Excel2007();
$filepath = 'Uploads/'.$filename;
if(!$PHPReader->canRead($filepath)){
$PHPReader = new \PHPExcel_Reader_Excel5();
if(!$PHPReader->canRead($filepath)){
$this->ajaxReturn(array('status'=>0,'message'=>'文件格式错误'));
}
$info = pathinfo($filepath);
if($info['extension'] == 'xls' || $info['extension'] == 'xlsx' || $info['extension'] == 'xlsm' || $info['extension'] == 'xltx'){
}else{
$this->ajaxReturn(array('status'=>0,'message'=>'文件格式错误'));
}
}
$PHPExcel = $PHPReader->load($filepath); //读取文件
$currentSheet = $PHPExcel->getSheet(0); //读取第一个工作簿
$allColumn = $currentSheet->getHighestColumn(); // 所有列数
$allRow = $currentSheet->getHighestRow(); // 所有行数
// if($allColumn != 'M'){
// $this->ajaxReturn(array('status'=>0,'message'=>'数据格式错误'));
// }
for($currentRow=1;$currentRow<=$allRow;$currentRow++){
// $currentColumn== 'A' && $column['house_num'] = $currentSheet->getCell($currentColumn.$currentRow)->getValue();
// $currentColumn== 'B' && $column['orientation'] = $currentSheet->getCell($currentColumn.$currentRow)->getValue();
// $exlcedata['addtime']=$currentSheet->getCell('A'.$currentRow)->getValue();
$exlcedata['addtime']=$this->excelTime($currentSheet->getCell('A'.$currentRow)->getValue());//从excel读取的时间转换
$exlcedata['num']=$currentSheet->getCell('B'.$currentRow)->getValue();
$exlcedata['user_account']=$currentSheet->getCell('C'.$currentRow)->getValue();
// $exlcedata['address']=$currentSheet->getCell('C'.$currentRow)->getValue();
// $exlcedata['age']=$currentSheet->getCell('D'.$currentRow)->getValue();
// $exlcedata['weight']=$currentSheet->getCell('E'.$currentRow)->getValue();
$Users = M('User_position');
$Users->add($exlcedata);
}
}
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;
}
function upload_excel(){
$upload = new \Think\Upload(); // 实例化上传类
$upload->maxSize = 31457280 ; // 设置附件上传大小
$upload->exts = array('jpg', 'gif', 'png', 'jpeg','xls','docx','xlsx'); // 设置附件上传类型
$upload->savePath = './data/upload/';
$upload->autoSub = false ; // 设置附件上传目录 // 上传文件
$info = $upload->upload();
if(!$info) { // 上传错误提示错误信息
$this->error($upload->getError());
}else{
$filename=$info['import']['savepath'].$info['import']['savename'];
$this->success('上传成功');
}
Vendor('PHPExcel.PHPExcel');
$PHPExcel = new \PHPExcel();
$PHPReader = new \PHPExcel_Reader_Excel2007();
$filepath = 'Uploads/'.$filename;
if(!$PHPReader->canRead($filepath)){
$PHPReader = new \PHPExcel_Reader_Excel5();
if(!$PHPReader->canRead($filepath)){
$this->ajaxReturn(array('status'=>0,'message'=>'文件格式错误'));
}
$info = pathinfo($filepath);
if($info['extension'] == 'xls' || $info['extension'] == 'xlsx' || $info['extension'] == 'xlsm' || $info['extension'] == 'xltx'){
}else{
$this->ajaxReturn(array('status'=>0,'message'=>'文件格式错误'));
}
}
$PHPExcel = $PHPReader->load($filepath); //读取文件
$currentSheet = $PHPExcel->getSheet(0); //读取第一个工作簿
$allColumn = $currentSheet->getHighestColumn(); // 所有列数
$allRow = $currentSheet->getHighestRow(); // 所有行数
// if($allColumn != 'M'){
// $this->ajaxReturn(array('status'=>0,'message'=>'数据格式错误'));
// }
for($currentRow=1;$currentRow<=$allRow;$currentRow++){
// $currentColumn== 'A' && $column['house_num'] = $currentSheet->getCell($currentColumn.$currentRow)->getValue();
// $currentColumn== 'B' && $column['orientation'] = $currentSheet->getCell($currentColumn.$currentRow)->getValue();
// $exlcedata['addtime']=$currentSheet->getCell('A'.$currentRow)->getValue();
$exlcedata['addtime']=$this->excelTime($currentSheet->getCell('A'.$currentRow)->getValue());//从excel读取的时间转换
$exlcedata['num']=$currentSheet->getCell('B'.$currentRow)->getValue();
$exlcedata['user_account']=$currentSheet->getCell('C'.$currentRow)->getValue();
// $exlcedata['address']=$currentSheet->getCell('C'.$currentRow)->getValue();
// $exlcedata['age']=$currentSheet->getCell('D'.$currentRow)->getValue();
// $exlcedata['weight']=$currentSheet->getCell('E'.$currentRow)->getValue();
$Users = M('User_position');
$Users->add($exlcedata);
}
}