该博客围绕大量数据导入数据库的需求展开，介绍了直接拉取Excel表格读取数据并存入数据库的方法，且不存储文件，利用上传缓存文件。同时给出了前端（layui+php）和服务端代码。
需求：大量数据需要导入数据库，直接拉取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;
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
