最近碰到需要将excel里的内容作为批量查询条件,然后使用jqGrid分页查询
<input type="button" value="Excel批量搜索" class="btn btn-info" id="doBatchSearch"/>
使用layui上传文件功能,在确认文件后在choose事件中解析excel并重新加载jqGrid表格数据,解析excel需要引入xlsx.core.js,地址:https://github.com/SheetJS/sheetjs/tree/master/dist
<script src="../../../statics/js/xlsx.core.js"></script>
layui.use('upload', function () {
var $ = layui.jquery, upload = layui.upload;
upload.render({
//绑定上传的元素id
elem: '#doBatchSearch',
type: 'file',
accept: 'file',
//上传的文件类型
exts: 'xlsx',
//是否自动上传
auto: false,
multiple: true,
choose: function(obj){
//获取Excel内容
obj.preview(function (index,file,result) {
var wb;
var reader = new FileReader();
var orderNoList = [];
reader.onload = function (ev) {
var data = ev.target.result;
wb = XLSX.read(data,{type:'binary'});
var fromTo = '';
var infoData = [];
//循环excel的sheet
for(var sheet in wb.Sheets){
if(wb.Sheets.hasOwnProperty(sheet)){
fromTo = wb.Sheets[sheet]['!ref'];
//拼接所有sheet数据
infoData = infoData.concat(XLSX.utils.sheet_to_json(wb.Sheets[sheet]))
}
}
var headStr = 'orderNo';
//解析出需要的表头数据
for(var i = 0; i < infoData.length;i++){
if(Object.keys(infoData[i]) != headStr){
infoData.slice(i,1);
}else{
//将excel数据拼接查询参数
orderNoList.push(infoData[i].orderNo);
}
}
$("#table_list").setGridParam({datatype: 'json', postData: null});
//使用jqGrid重新加载表格
$("#table_list").jqGrid('setGridParam', {
datatype: 'json',
postData: {"orderNoList":orderNoList.toString()},
page: 1
}).trigger("reloadGrid");
};
//读取文件
reader.readAsBinaryString(file);
});
}
});
});