thinkphp5导入excel表
后端
private function checkFileType($file){
if(empty($file['name'])) return false;
if($this->getExtension($file) != 'xlsx' && $this->getExtension($file) != 'xls') return false;
return true;
}
private function getExtension($file)
{
$info = pathinfo($file['name']);
return $info['extension'];
}
public function import(){
set_time_limit (0);
if (IS_POST) {
$file = $_FILES['filename'];
if(!$this->checkFileType($file)){
$this->error('您上传的文件错误！');
}
//上传文件
$file_name = './uploads/excel/'.rand(10000,99999).'.xlsx';
$f = move_uploaded_file($file['tmp_name'],$file_name);
//获取表单上传文件
if($f){
// 引入文件
vendor("PHPExcel.PHPExcel.Reader.Excel2007");
$objPHPExcel = new \PHPExcel_Reader_Excel2007();
$objReader = $objPHPExcel ->load($file_name);
//加载文件内容,编码utf-8
$obj_PHPExcel = $objPHPExcel->load($file_name, $encode = 'utf-8');
//删除文件
@unlink($file_name);
$excel_array = $obj_PHPExcel->getsheet(0)->toArray(); //转换为数组格式
array_shift($excel_array); //删除第一个数组(标题);
$data = [];
foreach ($excel_array as $k => $v) {
if(intval($v[7]) == 1 && intval($v[8]) == 1){
$level = 5;
}elseif(intval($v[7]) == 1){
$level = 1;
}elseif(intval($v[8]) == 1) {
$level = 4;
}
$username = $v[5];
$pass = $v[6];
$area = $v[1];
$market = $v[2];
$salename = $v[3];
$code = $v[4];
//检测手机号，存在的话修改信息，否则新增
$checkUserName = M('users')->where([
'username' => $username,
'lang' => 'cn'
])->find();
if(empty($checkUserName)) {
$id = M('users')->insertGetId([
'username' => $username,
'password' => func_encrypt($pass),
'password_show' => $pass,
'level' => $level,
'lan' => 'cn',
'reg_time' => time()
]);
M('users_list')->insert([
'users_id' => $id,
'para_id' => 3,
'info' => $area,
'lang' => 'cn',
'add_time' => time(),
'update_time' => time()
]);
M('users_list')->insert([
'users_id' => $id,
'para_id' => 4,
'info' => $market,
'lang' => 'cn',
'add_time' => time(),
'update_time' => time()
]);
M('users_list')->insert([
'users_id' => $id,
'para_id' => 5,
'info' => $salename,
'lang' => 'cn',
'add_time' => time(),
'update_time' => time()
]);
M('users_list')->insert([
'users_id' => $id,
'para_id' => 6,
'info' => $code,
'lang' => 'cn',
'add_time' => time(),
'update_time' => time()
]);
}else{
$id = $checkUserName['users_id'];
M('users')->where([
'username' => $username,
'lang' => 'cn'
])->update([
'password' => func_encrypt($pass),
'password_show' => $pass,
'level' => $level,
'update_time' => time()
]);
M('users_list')->where([
'users_id' => $id,
'lang' => 'cn',
'para_id' => 3
])->update([
'info' => $area,
'update_time' => time()
]);
M('users_list')->where([
'users_id' => $id,
'lang' => 'cn',
'para_id' => 4
])->update([
'info' => $market,
'update_time' => time()
]);
M('users_list')->where([
'users_id' => $id,
'lang' => 'cn',
'para_id' => 5
])->update([
'info' => $salename,
'update_time' => time()
]);
M('users_list')->where([
'users_id' => $id,
'lang' => 'cn',
'para_id' => 6
])->update([
'info' => $code,
'update_time' => time()
]);
}
}
//批量插入数据
$this->success('导入成功');
}
}
return $this->fetch();
}前端
<div class="page">
<div class="fixed-bar">
<div class="item-title"><a class="back" href="{:url('Member/level_index')}" title="返回列表"><i class="fa fa-arrow-circle-o-left"></i></a>
<div class="subject">
<h3>导入会员数据</h3>
<h5></h5>
</div>
</div>
</div>
<!-- 操作说明 -->
<div id="explanation" class="explanation" style="color: rgb(44, 188, 163); background-color: rgb(237, 251, 248); width: 99%; height: 100%;">
<form class="form-horizontal" id="postForm" action="{:url('Member/import')}" method="post" enctype="multipart/form-data">
<div class="ncap-form-default">
<dl class="row">
<dt class="tit">
<label for="level_name"><em>*</em>导入文件</label>
</dt>
<dd class="opt">
<input type="file" name="filename">
<p class="notic"></p>
</dd>
</dl>
<dl class="row">
<a href="JavaScript:void(0);" onclick="checkForm();" class="ncap-btn-big ncap-btn-green" id="submitBtn" style="margin-left: 135px;">确认提交</a>
</div>
</div>
</form>
</div>
<script type="text/javascript">
// 判断输入框是否为空
function checkForm(){
// if($('input[name=amount]').val() == ''){
// showErrorMsg('消费额度不能为空！');
// $('input[name=amount]').focus();
// return false;
// }
// if($('input[name=discount]').val() == ''){
// showErrorMsg('折扣率不能为空！');
// $('input[name=discount]').focus();
// return false;
// }
layer_loading('正在处理');
$('#postForm').submit();
}
</script>
