php 导入excel

if($_FILES['files']){
$uploadfile=$_FILES['files'];
$name=$uploadfile['name'];//文件原名
$type=$uploadfile['type'];
$tmp_name=$uploadfile['tmp_name'];
$size=$uploadfile['size'];
$error=$uploadfile['error'];
// $uploadurl='../../../d/uploads/'; //上传路径
$path='./uploads/'; //上传路径
if (!is_dir($path)){//判断目录是否存在
mkdir($path);
}
$max_file_size=2000000; //最大上传限制 2M
if($name){
$info = pathinfo($name);
$ext = $info['extension'];
}
$newname=$path.time().$info['basename'];//自定义名称
//允许上传类型
$allowType=array(
'xls','xlsx'
);
if($size>$max_file_size){$error=2;}
// echo $error;exit;
switch($error){
case 0:
if(!in_array($ext,$allowType)){
$msg = '文件类型错误';
printerror($msg,'',1,0,1);
// echo "<script>alert('文件类型错误');self.location.href='ImportMember.php';</script>";
}else{
move_uploaded_file($tmp_name,$newname);
}
break;
case 1: ?
$msg = '文件超过系统限定大小';
printerror($msg,'',1,0,1);
break;
case 2:
$msg = '最大文件不能超过2M';
printerror($msg,'',1,0,1);
break;
case 3:
$msg = '文件只有部分被上传';
printerror($msg,'',1,0,1);
break;
case 4:
$msg = '没有文件被上传';
printerror($msg,'',1,0,1);
break;
case 5:
$msg = '文件上传大小为0';
printerror($msg,'',1,0,1);
break;
default:
$msg = '非法上传';
printerror($msg,'',1,0,1);
break;
}
//查找文件
if(file_exists($newname)){
require_once '../../extend/PHPExcel/PHPExcel.php';
require_once '../../extend/PHPExcel/PHPExcel/IOFactory.php';
if($ext=='xls'){
require_once '../../extend/PHPExcel/PHPExcel/Reader/Excel5.php';// excel 2003
$objReader=PHPExcel_IOFactory::createReader('Excel5');//use excel2007 for 2007 format
}else{
require_once '../../extend/PHPExcel/PHPExcel/Reader/Excel2007.php';// excel 2007
$objReader=PHPExcel_IOFactory::createReader('Excel2007');//use excel2007 for 2007 format
}
// $objPHPExcel=$objReader->load($newname);//$newname即Excel文件的路径
// $objPHPExcel = $objReader->load($newname);//加载文件(这个容易造成httpd崩溃) ??
$objPHPExcel = PHPExcel_IOFactory::load($newname);//加载文件
$sheet=$objPHPExcel->getSheet(0);//获取第一个工作表
$highestRow=$sheet->getHighestRow();//取得总行数
$highestColumn=$sheet->getHighestColumn(); //取得总列数
// echo $highestRow;exit;

for($i=3;$i<=$highestRow;$i++){
$username=trim($objPHPExcel->getActiveSheet()->getCell("A".$i)->getValue());
$password=trim($objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue());
$rnd=make_password(20);
$email=trim($objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue());
$registertime = date('Y-m-d H:i:s',time());
$groupid = trim($objPHPExcel->getActiveSheet()->getCell("D".$i)->getValue());
$salt=eReturnMemberSalt();
$userkey=eReturnMemberUserKey();
$checked = 1;
$class_name=trim($objPHPExcel->getActiveSheet()->getCell("E".$i)->getValue());
$truename=trim($objPHPExcel->getActiveSheet()->getCell("F".$i)->getValue());
$gender=trim($objPHPExcel->getActiveSheet()->getCell("G".$i)->getValue());
$phone=trim($objPHPExcel->getActiveSheet()->getCell("H".$i)->getValue());
$address = trim($objPHPExcel->getActiveSheet()->getCell("I".$i)->getValue());
$username=strtolower($username);
$email=strtolower($email);
if($groupid=='学生'){
$groupid = 7;
}elseif($groupid=='老师'){
$groupid = 8;
}
empty($gender) ? $gender = '未知' : $gender = $gender;
empty($groupid)? $groupid = 7 : $groupid = $groupid;
empty($password)?$password=eDoMemberPw(123456,$salt):$password=eDoMemberPw($password,$salt);
if(empty($username)){
$flag = 0;
$msg='第'.($i-2).'条记录出错,用户名不能为空；请重新导入';
break;
}
if(empty($class_name)){
$flag = 0;
$msg = '第'.($i-2).'条记录出错，班级不能为空；请重新导入';
break;
}
if(empty($truename)){
$flag = 0;
$msg = '第'.($i-2).'条记录出错，真实姓名不能为空；请重新导入';
break;
}

$num=$empire->gettotal("select count(*) as total from ".eReturnMemberTable()." where ".egetmf('username')."='$username' limit 1");
if($num){
$flag = 0;
$msg='第'.($i-2)."条记录出错,该用户已存在；请重新导入";
break;
}
if(!empty($email)){
if(!chemail($email))
{
$flag = 0;
$msg='第'.($i-2)."条记录出错,邮箱格式不正确; 请重新导入";
break;
}
if($pr['regemailonly'])
{
$num=$empire->gettotal("select count(*) as total from ".eReturnMemberTable()." where ".egetmf('email')."='$email' limit 1");
if($num)
{
$flag = 0;
$msg='第'.($i-2)."条记录出错,邮箱已注册; 请重新导入";
break;
}
}
}
// else{
// $flag = 0;
// $msg='第'.($i-1)."条记录出错,邮箱不能为空; 请重新导入";
// break;
// }
// echo $member_r[0];exit;
$sql=$empire->query("insert into ".eReturnMemberTable()."(".eReturnInsertMemberF('username,password,rnd,email,registertime,groupid,userfen,userdate,money,zgroupid,havemsg,checked,salt,userkey').") values('$username','$password','$rnd','$email','$registertime','$groupid','$public_r[reggetfen]','0','0','0','0','$checked','$salt','$userkey');");
if($sql){//插入成功
$userid=$empire->lastid();
$ins_id[] = $userid;
$addr=$empire->fetch1("select * from {$dbtbpre}enewsmemberadd where userid='$userid'");
if(!$addr){
$spacestyleid = $groupid;
$sql1=$empire->query("insert into {$dbtbpre}enewsmemberadd(userid,truename,spacestyleid,regip,lasttime,lastip,loginnum,regipport,lastipport,class_name".$member_r[0].") values('$userid','$truename','$spacestyleid','$regip','$lasttime','$regip','1','$regipport','$regipport','$class_name'".$member_r[1].");");
if($sql1){
$flag = 1;
$msg = '导入成功';
$gotourl = 'ListMember.php';
$insids = implode(',',$ins_id);
// printerror($msg,'ListMember.php',1,0,1);
}else{
$flag = 0;
$msg = '导入失败';
$gotourl = 'ImportMember.php';
$insids = implode(',',$ins_id);
break;
}
}
}
}
@unlink($newname);
if($flag==1){
printerror($msg,$gotourl,1,0,1);
}else{
if($insids){
$sql2 = "delete from {$dbtbpre}enewsmember where userid in ($insids)";
$res1 = $empire->query($sql2);
$sql3 = "delete from {$dbtbpre}enewsmemberadd where userid in ($insids)";
$res2 = $empire->query($sql3);
}
printerror($msg,$gotourl,1,0,1);
}
}else{
echo "<script>alert('请选择要导入的文件')</script>";
}
}