首先导入Excel数据需要几样东西
第一需要两个依赖包，这里直接是在pom注入依赖
<!--excel-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
第二就是需要一个工具类，就和分页的工具类一个性质，这里就不贴出来了太长 。百度搜索 “ExcelUtil”
代码中用到的js文件 jquery-3.1.1.min.js、layer.js
下面开始贴js代码
//h5的话，写一个文件域就好了
//上传Excel文件
uploadFile() {
var file = $("#upload").val();
file = file.substring(file.lastIndexOf('.'), file.length);
if (file == '') {
layer.open({
content: '上传文件不能为空！'
, skin: 'msg'
, time: 2 //2秒后自动关闭
});
} else if (file != '.xlsx' && file != '.xls') {
layer.open({
content: '请选择正确的excel类型文件！'
, skin: 'msg'
, time: 2 //2秒后自动关闭
});
} else {
this.ajaxFileUpload();
}
},
ajaxFileUpload() {
var formData = new FormData();
formData.append("file", document.getElementById("upload").files[0]);
$.ajax({
url: "${ctx}/bookcase/InputExcel",
type: "POST",
async: true,
data: formData,
processData: false,
contentType: false,
beforeSend: function () {
layer.open({
type: 2
, content: '文件上传中，请稍候'
});
},
success: function (data) {
layer.closeAll();
data = JSON.parse(data);
if (data.state == 200) {
layer.open({
content: data.message
, skin: 'msg'
, time: 1 //2秒后自动关闭
});
window.location.reload();
} else {
layer.open({
content: data.message
, skin: 'msg'
, time: 2 //2秒后自动关闭
});
}
}
});
}
},
到这里页面的的操作就差不多了，下面是java的操作
@RequestMapping("InputExcel")
@ResponseBody
public ResultEntity InputExcel(@RequestParam("file") MultipartFile file, HttpServletRequest request) {
ResultEntity result = new ResultEntity();
if (!file.isEmpty()) {
try {
//获取原始的文件名
String originalFilename = file.getOriginalFilename();
String fileType = originalFilename.substring(originalFilename.lastIndexOf(".") + 1, originalFilename.length());
//默认从第一行开始读取
Integer startRows = 1;
//获取输入流
InputStream is = file.getInputStream();
List<DoorAntRel> bindingList = new ArrayList<>();
List<Bookcase> bookcaseList = new ArrayList<>();
List<String[]> strings = ExcelUtil.readData(fileType, startRows, true, is);//这里使用输入流把数据拿到
//遍历Excel表每一行的数据
for (String[] str : strings) {//这里比较特殊，肯定有更简便的办法，暂时我就只有这个水平、、、尴尬
Bookcase bookcase = new Bookcase();
DoorAntRel doorAntRel = new DoorAntRel();
bookcase.setName(str[1]);
bookcase.setType(str[2]);
bookcase.setCom(Integer.parseInt(str[3]));
doorAntRel.setDoorName(str[4]);
doorAntRel.setDoorNo(Integer.parseInt(str[5]));
doorAntRel.setAntennaNo(Integer.parseInt(str[6]));
doorAntRel.setReadWriterId(Integer.parseInt(str[7]));
doorAntRel.setBookcaseId(Integer.parseInt(str[8]));
doorAntRel.setBadFlag(Integer.parseInt(str[9]));
doorAntRel.setDoorSlot(Integer.parseInt(str[10]));
bindingList.add(doorAntRel);
bookcaseList.add(bookcase);
}
boolean bookState = bookcaseService.insertOrUpdateBatch(bookcaseList);
boolean doorState = doorAntRelService.insertOrUpdateBatch(bindingList);
if(bookState){
if(doorState){
result.setState(HttpCode.SUCCESS);
result.setMessage("上传文件成功！");
return result;
}
}
} catch (IOException e) {
e.printStackTrace();
}
}
result.setState(HttpCode.FAILED);
result.setMessage("上传文件失败！");
return result;
}
到这里差不多导入功能就差不多了，包括业务层、数据访问的接口我就不写了，也就是一条insert的事情