数据导入,前端使用layui excel 插件
按钮
<div class="btn-upload customer-list-doImport-btn">
<a class="layui-btn layui-btn-primary">选择文件</a>
<input type="file" title=""
class="input-file"
id="customer-black-excel-file"
accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet">
</div>
<button
class="layui-btn disabled customer-list-doImport-btn"
lay-submit lay-filter="customer-import-list-upload-btn-submit" disabled
id="customer-import-list-upload-btn-submit">开始导入
</button>
JS 关键代码
var files = '', returnData = "";
$('#customer-black-excel-file').change(function (e) {
// 注意:这里直接引用 e.target.files 会导致 FileList 对象在读取之前变化,导致无法弹出文件
files = Object.values(e.target.files);
// 变更完清空,否则选择同一个文件不触发此事件
e.target.value = '';
$uploadBtn.removeClass('disabled').prop('disabled', false);
});
form.on('submit(customer-import-list-upload-btn-submit)', function (data) {
$uploadBtn.addClass('disabled').prop('disabled', true);
uploadExcel(files, data.field);
return false;
});
// 上传excel的处理函数,传入文件对象数组
function uploadExcel(files, formData) {
console.log(files)
layer.load(2);
try {
excel.importExcel(files, {
// 导入:读取数据的同时梳理数据
fields: {
name: 'A',
mobile: 'B',
gender: 'C',
email: 'D',
weixin: 'E',
qq: 'F',
remark: 'G',
}
}, function (data) {
var count = data[0]['sheet1'].length;
if ((count -1) > importTotalLimit) {
layer.closeAll('loading');
return layer.msg("导入条数("+count+")大于单次限制条数")
}
admin.req({
url: url + '/do',
data: {fileData: data[0]['sheet1'], ...formData},
success: function (res) {
layer.closeAll('loading');
layer.msg(res.msg, {time: 4000})
table.reload(tableId, {
page: {curr: 1},
where: admin.getTableDefOrderBy()
}, 'data');
// 显示导入返回的结果
$('#customer-import-list-return-box').html(laytpl($('#customer-import-list-return-tpl').html()).render({
data: res,
files: files
}));
// 下载上传数据清单
if (res.data) {
returnData = res.data;
downloadReturnData(returnData, true)
}
}, error: function (e) {
layer.closeAll('loading');
}
})
})
} catch (e) {
layer.closeAll('loading');
layer.msg(e.message)
}
}
$("body").on("click", "#customer-import-list-return-download-btn", function () {
if (returnData) {
downloadReturnData(returnData)
}
});
function downloadReturnData(returnData, addHead) {
var colConf = excel.makeColConfig({ 'A': 50, 'Z': 100 }, 110), rowConf = excel.makeRowConfig({ 1: 24 }, 20);
var dd = new Date(), date = dd.getFullYear() +'-'+ (dd.getMonth()+1) +'-'+ dd.getDate();
if (addHead) {
returnData.unshift({
name: '姓名',
mobile: '手机号',
gender: '性别',
email: '邮箱',
weixin: '微信',
qq: 'QQ',
remark: '备注',
createTime: '导入时间',
importResult: '导入结果'
})
}
excel.setExportCellStyle(returnData, 'A1:Z1', {
s: { alignment: {vertical: 'center'}, font: {bold: true} }
});
excel.exportExcel({
sheet1: returnData
}, '导入结果清单'+date+'.xlsx', 'xlsx', {
// 中可以指定某个 sheet 的属性,如果不指定 sheet 则所有 sheet 套用同一套属性
extend: {
sheet1: {
'!cols': colConf,
'!rows': rowConf
}
}
});
}