如下实现了在用户点击或拖拽excel到上传窗口后,在layer.open弹窗中预览excel表格中每个sheet的内容。
如何修改实现在预览窗点击确定上传后将excel数据通过后台php接口写入mysql数据库?目前下述确认按钮和取消按钮及关闭按钮都无效。
<script>
layui.config({
base: '../module/soulTable/ext/',
version: 'v1.8.0'
}).extend({
soulTable: 'soulTable'
});
layui.use(['table', 'dropdown', 'soulTable'], function () {
var table = layui.table;
var soulTable = layui.soulTable;
var form = layui.form;
var dropdown = layui.dropdown;
var upload = layui.upload;
var excel = layui.excel;
var laytpl = layui.laytpl;
var $ = layui.$;
function openSmtbomUpload() {
layer.open({
type: 1,
title: '上传窗口',
content:
`<div class="layui-upload-drag" style="display: block;" id="LAY-excel-upload">
<i class="layui-icon layui-icon-upload"></i>
<div>点击上传,或将文件拖拽到此处</div>
<div class="layui-hide" id="ID-upload-demo-preview"><hr> <img src="" alt="上传成功后渲染" style="max-width: 100%">
</div>
</div>
`,
})
var uploadInst = upload.render({
elem: '#LAY-excel-upload'
, url: '/upload/'
, auto: false
, accept: 'file'
, choose: function (obj) {
var files = obj.pushFile()
var fileArr = Object.values(files)
for (var index in files) {
if (files.hasOwnProperty(index)) {
delete files[index]
}
}
$('#LAY-excel-upload').next().val('');
uploadExcel(fileArr)
}
});
}
function uploadExcel(files) {
try {
excel.importExcel(files, {}, function (data, book) {
data = excel.filterImportData(data, {
'id': 'A'
, 'username': 'B'
, 'experience': 'C'
, 'sex': 'D'
, 'score': 'E'
, 'city': 'F'
, 'classify': 'G'
, 'wealth': 'H'
, 'sign': 'I'
})
layer.open({
title: '文件转换结果'
, area: ['80%', '60%']
, tipsMore: true
, content: laytpl($('#LAY-excel-export-ans').html()).render({ data: data, files: files })
, btn: ['确认上传', '取消']
, success: function () {
element.render('tab')
layui.code({})
// 处理合并
for (var file_index in book) {
if (!book.hasOwnProperty(file_index)) {
continue
}
// 遍历每个Sheet
for (var sheet_name in book[file_index].Sheets) {
if (!book[file_index].Sheets.hasOwnProperty(sheet_name)) {
continue
}
var sheetObj = book[file_index].Sheets[sheet_name]
// 仅在有合并参数时进行操作
if (!sheetObj['!merges']) {
continue
}
// 遍历每个Sheet中每个 !merges
for (var merge_index = 0; merge_index < sheetObj['!merges'].length; merge_index++) {
var mergeObj = sheetObj['!merges'][merge_index]
// 每个合并参数的 s.c 表示左上角单元格的列,s.r 表示左上角单元格的行,e.c 表示右下角单元格的列,e.r 表示右下角单元格的行,计算时注意 + 1
$('#table-export-' + file_index + '-' + sheet_name + '-' + mergeObj.s.r + '-' + mergeObj.s.c)
.prop('rowspan', mergeObj.e.r - mergeObj.s.r + 1)
.prop('colspan', mergeObj.e.c - mergeObj.s.c + 1)
for (var r = mergeObj.s.r; r <= mergeObj.e.r; r++) {
for (var c = mergeObj.s.c; c <= mergeObj.e.c; c++) {
// 排除左上角
if (r === mergeObj.s.r && c === mergeObj.s.c) {
continue
}
$('#table-export-' + file_index + '-' + sheet_name + '-' + r + '-' + c).remove()
}
}
}
}
}
}
})
})
} catch (e) {
layer.alert(e.message)
}
}
)}
<script type="text/html" id="LAY-excel-export-ans">
{{# layui.each(d.data, function(file_index, item){ }}
<blockquote class="layui-elem-quote">{{d.files[file_index].name}}</blockquote>
<div class="layui-tab">
<ul class="layui-tab-title">
{{# layui.each(item, function(sheet_name, content) { }}
<li>{{sheet_name}}</li>
{{# }); }}
</ul>
<div class="layui-tab-content">
{{# layui.each(item, function(sheet_name, content) { }}
<div class="layui-tab-item">
<table class="layui-table">
{{# layui.each(content, function(row_index, value) { }}
{{# var col_index = 0 }}
<tr>
{{# layui.each(value, function(col_key, val) { }}
<td id="table-export-{{file_index}}-{{sheet_name}}-{{row_index}}-{{col_index++}}">{{val}}</td>
{{# });}}
</tr>
{{# });}}
</table>
<!-- <pre class="layui-code">{{JSON.stringify(content, null, 2)}}</pre> -->
</div>
{{# }); }}
</div>
</div>
{{# }) }}
</script>