layui php+PHPExcel 拉取excel表格数据一键导入

需求：大量数据需要导入数据库，直接拉取excel表格进行读取数据并存入数据库，过程中不对文件进行存储（使用上传过程中的缓存文件）。

前端代码（layui+php）：

<?php
	if(!defined('MEMCACHE_HOST'))
	{
		define('MEMCACHE_HOST','1****5');
		define('MEMCACHE_PORT',1***2);
	}
	include_once('/opt*******memcache.php');
	$cpage	= 'add_uploads';
	$ask***_str	= M::Get('as***list');
	$ask_***_arr	= json_decode($ask_a***r,true);//获取客户名列表，存在缓存中
?>
<!DOCTYPE html>
<html>
<head>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
	<title>问答频道后台</title>
	<link rel="stylesheet" rev="stylesheet" href="http://www.qubaobei.com/badmin/city/style/style.css?v=4" type="text/css" />
	<link rel="stylesheet" href="//www.qubaobei.com/kadmin/zlmm/static/admin/lib/layui/css/layui.css" id="layui">
	<style type="text/css">
	a:visited {
		color: #fff;
		text-decoration: none;
	}
	a:link {
		color: #fff;
		text-decoration: none;
	}
	</style>
</head>
<body>
<?php include_once('global/nav.php');?>
<fieldset class="layui-elem-field layui-field-title" style="margin-top: 30px;">
  <legend>客户批量问题一键导入</legend>
</fieldset> 
<form class="layui-form" action="">
	<div class="layui-form-item">
		<label class="layui-form-label">类型</label>
		<div class="layui-input-inline" style="width: 115px">
			<input type="checkbox" name="base" lay-skin="switch" lay-filter="base" lay-text="新增|选择">
		</div>
		<div id="base" style="display: none;">
			<div class="layui-input-inline" style="width: 100px">
				<input type="text" id="new_ad" name="new_ad" style="width: 100px" class="layui-input" value="" placeholder="客户名">
			</div>
		</div>
		<div class="layui-input-inline" style="padding: 9px 15px;line-height: 20px;">
			<a class="layui-btn layui-btn-xs"  href="./document/template.xlsx" download="">导入数据表格模板下载</a>
		</div>
	</div>
	<div class="layui-form-item" id="base_1">
		<label class="layui-form-label">客户名</label>
		<div class="layui-input-block" style="width: 700px">
		<?php foreach($ask_adv_post_cat_arr as $v):?>
			<input type="radio" lay-filter="ad_id" name="cate" value="<?php echo $v['cat_id'];?>" title="<?php echo $v['new_ad'];?>">
		<?php endforeach;?>
		</div>
	</div>
	<div class="layui-form-item">
		<label class="layui-form-label">文件上传</label>
		<div class="layui-upload-drag" id="test10">
		  <i class="layui-icon"></i>
		  <p>点击上传，或将文件拖拽到此处</p>
		</div>
	</div>
</form>
<!-- 上传完之后服务器返回链接 -->
<div style="overflow-y:auto; overflow-x:auto;  width:450px; height:600px; position: fixed;right: 30px;top: 100px;">
<table class="layui-table">
  <colgroup>
    <col width="200"><col>
  </colgroup>
  <thead>
    <tr>
      <th>链接</th>
    </tr> 
  </thead>
  <tbody id="url_list">

  </tbody>
</table>
</div>
</body>

<script src="//www.qubaobei.com/kadmin/zlmm/static/admin/lib/layui/layui.js"></script>
<script>

  layui.use(['upload','jquery','form'], function () {
    var $ = layui.jquery,
		form  = layui.form,
		upload = layui.upload;

	var cat_id=0,new_ad='';
    //监听开关操作
	form.on('switch(base)', function(data){
		var obj    = $(data.elem);
		var change = this.checked
		if(change){
			$('#base').show();
			$('#base_1').hide();
			$('#new_ad').val('');
			new_ad = '';
		}else{
			$('#base').hide();
			$('#base_1').show();
			$('#new_ad').val('');
			new_ad = '';
		}
	});
    //由于不使用表单提交，监听单选项值
	form.on('radio(ad_id)', function(data){
		cat_id = data.value;
	}); 
	//获取新增客户名
	$('#new_ad').blur(function(){
		new_ad = $('#new_ad').val();

	});
	//拖拽上传（不可以直接使用默认data形式！！由于是reder在一开始就确定了data值，所以需要在befor中重定义data。）
	upload.render({
		elem: '#test10'
		,url: 'sub/ask_adv.php'
		,accept: 'file' //普通文件
		,before: function(obj){ //obj参数包含的信息，跟 choose回调完全一致，可参见上文。
			this.data = {
						  method: 'one_button_import',
						  cat_id:cat_id,
						  new_ad:new_ad
						}
		}
		,done: function(res){
			if(res.ret==1){
				var i = layer.msg(res.msg,{time: 500},function(){ layer.close(i);});
				res.data.forEach(function(v){  
					var str="<tr><td>"+v[1]+"</td></tr>";
					$("#url_list").append(str);
				});
			}else{
				var index = layer.alert(res.msg,{icon:2},function(){ layer.close(index);});
			}

		}
	});
});
</script>

服务端代码：

//导入PHPExcel类文件（git：https://github.com/PHPOffice/PHPExcel，压缩文件：https://codeload.github.com/PHPOffice/PHPExcel/zip/1.8）

include_once("../util/PHPExcel/Classes/PHPExcel/IOFactory.php");
include_once("../util/PHPExcel/Classes/PHPExcel.php");


	//自动化一键导入
	public function one_button_import(){
		$file	= $_FILES['file'];//获取上传的文件
		$cate	= isset($this->get['cat_id'])?$this->get['cat_id']:0;//获取额外的数据
		$new_ad	= isset($this->get['new_ad'])?$this->get['new_ad']:'';
		if(!$cate && !$new_ad){
			$data['ret']=0;
			$data['msg']='未选择分类或未新增';
			echo json_encode($data);die;

		}
        //新增客户选项
		if(!$cate && $new_ad){
            //查找库中目前最大客户id值
			$sql	= "select max(cat) as max_cat from `ask_post` where `state`=1 limit 1";
			$max_cat= $this->ms->getRow($sql);
			$cate	= (int)$max_cat['max_cat']+1;
			//保存分类到缓存
			$ask_adv_post_cat_str	= M::Get('ask****_list');
			$ask_adv_post_cat_arr	= json_decode($ask_adv_post_cat_str,true);
			$new_quit	= array('cat_id'=>$cate,'new_ad'=>$new_ad);
			$ask_adv_post_cat_arr[]	= $new_quit;
			//$ask_adv_post_cat_arr	= array(array('cat_id'=>9999,'new_ad'=>'伊***品线'));
			$str = json_encode($ask_adv_post_cat_arr);
			M::Set('ask_****_list',$str);
			//文件记录，以防缓存丢失
			$log_str = '=========='.date('Y-m-d H:i:s').'=========='.PHP_EOL.var_export($ask_adv_post_cat_arr,true).PHP_EOL;
			file_put_contents('/o*******/log/ask_ad_cat_post.txt',$log_str,FILE_APPEND);
		}
        //使用缓存文件
		$inputFileName = $file['tmp_name'];
		//读取excel文件
		try {
			$inputFileType	= PHPExcel_IOFactory::identify($inputFileName);
			$objReader		= PHPExcel_IOFactory::createReader($inputFileType);
			$objPHPExcel	= $objReader->load($inputFileName);
		} catch(Exception $e) {
			die('加载文件发生错误：”'.pathinfo($inputFileName,PATHINFO_BASENAME).'”: '.$e->getMessage());
		}
        //形成数组
		$excel_data = $objPHPExcel->getSheet(0)->toArray();
		$url_list = array();
		foreach($excel_data as $k=>$v){
			if($k>0){
				//入库
				$post_id = $this->add_advice_post($v[0],$v[1],1,$cate);
				if($post_id){
					$url_list[] = array($v[0],"http:********e/detail.php?id=".$post_id);
				}else{
					die;
				}
			}
		}
		if($url_list){
			$data['ret']=1;
			$data['msg']='导入成功';
			$data['data']=$url_list;
		}else{
			$data['ret']=1;
			$data['msg']='导入失败，联系程序员';
		}
		echo json_encode($data);die;
	}