前段时间在做某行的项目时,需要实现一个上传Excel文件的功能,在过程中走了不少弯路,特在此记录整理,希望可以帮助到大家!
项目对应结构:
HTML代码如下:
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="X-UA-Compatible" content="IE=Edge,chrome=1">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<script type="text/javascript" src="../js/jquery-1.10.2.js"></script>
<script type="text/javascript" src="../static/layui/layui.all.js"></script>
<script type="text/javascript" src="../js/jquery.jSelectDate.js"></script>
<script type="text/javascript" src="../static/script/common.bohai.js"></script>
<link rel="stylesheet" type="text/css" href="../static/css/font-awesome/font-awesome.min.css">
<link href="../css/bh_dingding_style.css" rel="stylesheet" />
<link href="../static/layui/css/layui.css" rel="stylesheet" />
<title>文件上传</title>
</head>
<body>
<head>
<script type="text/javascript">
layui.config({
base: 'layui_exts/'
}).extend({
excel: 'excel'
});
</script>
<!-- IE需要把JS放在上边,否则onclick失效 -->
<script type="text/javascript" src="js/upload.js"></script>
</head>
<body>
<div class="layui-container">
<div class="layui-row">
<div class="layui-col-md11 layui-col-md-offset1">
<div class="layui-form">
<div class="layui-form-item">
<div class="layui-form-label" style="width:30%;text-align:center;">零售个贷客户对应关系表</div>
<div class="layui-form-block">
<button type="button" class="layui-btn" id="LAY-excel-upload">
<i class="layui-icon"></i>上传关系表
</button>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
<script type="text/html" id="LAY-excel-export-ans">
{{# layui.each(d.data, function(index, item){ }}
<blockquote class="layui-elem-quote">{{d.files[index].name}}</blockquote>
<div class="layui-tab">
<ul class="layui-tab-title">
{{# layui.each(item, function(sheetname, content) { }}
<li>{{sheetname}}</li>
{{# }); }}
</ul>
<div class="layui-tab-content">
{{# layui.each(item, function(sheetname, content) { }}
<div class="layui-tab-item">
<table class="layui-table">
{{# layui.each(content, function(index, value) { }}
<tr>
{{# layui.each(value, function(key, val) { }}
<td>{{val}}</td>
{{# });}}
</tr>
{{# });}}
</table>
<pre class="layui-code">{{JSON.stringify(content, null, 2)}}</pre>
</div>
{{# }); }}
</div>
</div>
{{# }) }}
</script>
</html>
</body>
js代码如下:
layui.use(['jquery', 'layer', 'upload', 'excel', 'laytpl', 'element', 'code'], function(){
var $ = layui.jquery;
var layer = layui.layer;
var upload = layui.upload;
var excel = layui.excel;
var laytpl = layui.laytpl;
var element = layui.element;
function uploadExcel(files) {
try {
// excel.importExcel(files, {}, function(data) {
// console.log(data);
// data = excel.filterImportData(data, {
// 'xuhao': 'A',
// 'kehuhao': 'B',
// 'kehujingliGH': 'C',
// 'kehujingli': 'D',
// 'jydwjgh': 'E',
// 'jingyingdanwei': 'F'
// })
// console.log(data[0].Sheet1[0].xuhao);
// });
excel.importExcel(files, {
// 读取数据的同时梳理数据
fields: {
'xuhao': 'A',
'kehuhao': 'B',
'kehujingliGH': 'C',
'kehujingli': 'D',
'jydwjgh': 'E',
'jingyingdanwei': 'F'
}
}, function(data) {
// 如果不需要展示直接上传,可以再次 $.ajax() 将JSON数据通过 JSON.stringify() 处理后传递到后端即可
console.log(data[0].Sheet1[1].xuhao);
$.ajax({
type:'POST',
url:'uploadExcel.do',
data:{"xuhao":data[0].Sheet1[1].xuhao,
"kehuhao":data[0].Sheet1[1].kehuhao,
"kehujingliGH":data[0].Sheet1[1].kehujingliGH,
"kehujingli":data[0].Sheet1[1].kehujingli,
"jydwjgh":data[0].Sheet1[1].jydwjgh,
"jingyingdanwei":data[0].Sheet1[1].jingyingdanwei},
dataType:'json',
success(suc){
layer.open({
type: 1,
content: "上传成功" ,btn: ['确定'],
title:'5秒后自动关闭',
offset: 'auto',
btnAlign: 'c',
time:5000,
anim:'4',
yes: function(index, layero){
location.reload()
}
});
}
});
/*
layer.open({
title: '文件转换结果',
area: ['799px', '399px'],
tipsMore: true,
content: laytpl($('#LAY-excel-export-ans').html()).render({data: data, files: files}),
success: function() {
element.render('tab');
layui.code({
});
}
});*/
return false;
});
} catch (e) {
layer.alert(e.message);
}
};
//upload上传实例
var uploadInst = upload.render({
elem: '#LAY-excel-upload', //绑定元素
url: 'uploadExcel.do', //上传接口(PS:这里不用传递整个 excel)
auto: false, //选择文件后不自动上传
accept: 'file',
ext:'xls',
choose: function(obj) {// 选择文件回调
var files = obj.pushFile();
files = Object.values(files);// 注意这里的数据需要是数组,所以需要转换一下
console.log("files:"+files);
uploadExcel(files);
}
});
// $(function(){
// // 监听上传文件的事件
// $('#LAY-excel-import-excel').change(function(e) {
// var files = e.target.files;
// uploadExcel(files);
// });
// });
});