需求说明:
管理系统可以下载Excel模板,编辑Excel文件后,解析该文件实现批量插入。
一、按钮效果
二、HTML文件
三、JS文件
new AjaxUpload('#insert', {
action: baseURL + "apadchannel/insert",
name: 'file',
autoSubmit:true,
responseType:"json",
onSubmit:function(file, extension){
if (!(extension && /^(xlsx)$/.test(extension.toLowerCase()))){
alert('只支持xlsx格式的文件!');
return false;
}
},
onComplete : function(file, r){
if(r.code === 0){
alert('操作成功', function(index){
vm.reload();
});
}else{
alert(r.msg);
}
}
});
四、JAVA代码
public R insertBatch(MultipartFile file) {
if (file.isEmpty()) {
return R.error("文件为空!");
}
InputStream is = null;
try {
is = file.getInputStream();
Workbook wb = new XSSFWorkbook(is);
Sheet sheet = wb.getSheetAt(0);
/**
* 行数校验:
* 1. 第一行为表头,数据不可为空
* 2. 最多一次插入1000条数据
* 3. poi 并不能很好的处理空行的问题,需要对ap_channel进行业务校验
*/
int totalRows = sheet.getPhysicalNumberOfRows();
if (totalRows <= 1) {
return R.error("数据为空!");
}
int totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
int realRows = 1;
for (int r = 1; r < totalRows; r++) {
Row row = sheet.getRow(r);
for (int c = 0; c < totalCells; c++) {
Cell cell = row.getCell(c);
if (null != cell && !cell.equals("") && cell.getCellType() != XSSFCell.CELL_TYPE_BLANK) {
realRows++;
break;
}
}
}
if (realRows > 1001) {
return R.error("内存溢出,只限1000条数据!");
}
/**
* 文件解析:
* 1. 循环每行每列(第二行始)
* 2. 校验每列数据是否为空,为空则抛错
* 3. 校验键值是否重复
* 4. 解析完成放入集合中
*/
List<ApAdChannelEntity> apAdChannelList = new ArrayList<>();
Map<String, Integer> apChannelMap = new HashMap<>();
for (int r = 1; r < realRows; r++) {
ApAdChannelEntity apAdChannel = new ApAdChannelEntity();
apAdChannel.setStatus(1);
apAdChannel.setCreateTime(new Date());
apAdChannel.setUpdateTime(new Date());
Row row = sheet.getRow(r);
for (int c = 0; c < totalCells; c++) {
Cell cell = row.getCell(c);
if (null == cell || cell.equals("") || cell.getCellType() == XSSFCell.CELL_TYPE_BLANK) {
return R.error("第" + ++r + "行第" + ++c + "列字段值为空!");
}
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
switch (c) {
case 0:
apAdChannel.setChannelName(cell.getStringCellValue());
break;
case 1:
String apChannelNo = cell.getStringCellValue();
if (!apChannelMap.isEmpty() && apChannelMap.containsKey(apChannelNo)) {
int i = apChannelMap.get(apChannelNo) + 1;
return R.error("第" + i + "行,第" + ++r + "行存在ap_channel字段值重复!请重新导入!");
}
apChannelMap.put(apChannelNo, r);
apAdChannel.setApChannelNo(apChannelNo);
break;
case 2:
apAdChannel.setAdChannelNo(Integer.parseInt(cell.getStringCellValue()));
break;
}
}
apAdChannelList.add(apAdChannel);
}
/**
* 数据校验:
* 1. 校验键值是否已存在于数据库中
* 2. 批量输入入库
*/
List<String> list = apAdChannelDao.selectBatch(new ArrayList<>(apChannelMap.keySet()));
if (!list.isEmpty()) {
return R.error("ap_channel值为" + JSON.toJSONString(list) + "已存在,请重新导入!");
}
apAdChannelDao.saveBatch(apAdChannelList);
return R.ok();
} catch (Exception e) {
logger.error("系统异常!原因:", e);
return R.error("系统异常!");
} finally {
try {
if (is != null) is.close();
} catch (IOException e) {
logger.error("IO异常!原因:", e);
}
}
}
知识点梳理
知识点1:图标字体库Font Awesome
https://www.cnblogs.com/7mile/p/3156942.html
知识点2:a标签下载excel文件
https://blog.csdn.net/z1729734271/article/details/79026037
知识点3:使用JQuery插件 Ajaxupload实现文件上传
https://www.cnblogs.com/7mile/p/3156942.html
知识点4:java poi读取excel文件
https://www.cnblogs.com/SimonHu1993/p/8202391.html
https://blog.csdn.net/IBLiplus/article/details/82314384
https://blog.csdn.net/MAILLIBIN/article/details/86628663
知识点5:list转json
https://www.cnblogs.com/panxuejun/p/6148592.html
知识点6:mybatis批量插入
https://www.cnblogs.com/shuaifing/p/9327465.html
知识点7:注意poi有坑,空数据行会被当做物理行出来