一、pom.xml配置
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi fx-->
<!-- 使用xls格式时、只要导入poi-version-yyyymmdd.jar -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<!-- 使用xlsx格式 要导入poi-ooxml-version-yyyymmdd.jar -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
二、代码部分
Controller层:
//导入excel -- 将excel中数据导入到页面table表格中
@RequestMapping(value = "/importFile", method = RequestMethod.POST)
@ResponseBody
public CommonResponse importFile(HttpServletRequest request, HttpServletResponse response, SelectParam selectParam) {
if (EmptyUtil.isEmpty(selectParam)) {
return new CommonResponse(false, "数据导入参数为空");
}
CommonResponse commonResponse = new CommonResponse();
try {
commonResponse = layerSpecialService.importFile(request, tabInfoSelectParam);
} catch (Exception e) {
logger.error("数据导入异常", e);
return new CommonResponse(false, "数据导入异常");
}
return commonResponse;
}
Service实现:
@Transactional(readOnly=false,propagation=Propagation.REQUIRED, rollbackFor = Exception.class)
public CommonResponse importFile(HttpServletRequest request,SelectParam selectParam) throws Exception {
CommonResponse commonResponse = new CommonResponse();
if (EmptyUtil.isEmpty(tabInfoSelectParam.getType())) {
return new CommonResponse(false, "数据导入参数类型为空");
}
String type = selectParam.getType();
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
InputStream in =null;
List<List<Object>> list = null;
MultipartFile file = multipartRequest.getFile("file");
if(file.isEmpty()){
return new CommonResponse(false,"文件不存在");
}
in = file.getInputStream();
list = commonService.importFile(in,file.getOriginalFilename());
in.close();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
if(LayerTypeEnum.SG.getCode().equals(type)){//测试数据
List<Test> tests = new ArrayList<>();
//该处可调用service相应方法进行数据保存到数据库中,现只对数据输出
for (int i = 0; i < list.size(); i++) {
List<Object> lo = list.get(i);
//对取到的数据逐个做数据校验
if(EmptyUtil.isEmpty(lo.get(0).toString())){
return new CommonResponse(false,"第"+(i+1)+"条数据,"+"时间不能为空");
}
if(EmptyUtil.isEmpty(lo.get(1).toString())){
return new CommonResponse(false,"第"+(i+1)+"条数据,"+"等级不能为空");
}
Test test= new Test ();
test.setFindTime(sdf.parse(lo.get(0).toString()));
//根据字典名称与字典内容名称查询字典code,及ID
try {
int levelCode = commonService.selectByDictNameAndName("等级",lo.get(1).toString());
if(levelCode == 0){
test.setLevel("");
}else{
test.setLevel(accLevelCode+"|"+lo.get(1).toString());
}
} catch (Exception e) {
logger.error("第"+(i+1)+"条数据,"+"等级错误",e);
return new CommonResponse(false,"第"+(i+1)+"条数据,"+"等级错误");
}
test.setRemark(lo.get(2).toString());
tests.add(test);
}
commonResponse.setData(tests);
}
commonResponse.setSuccess(true);
return commonResponse;
}
三、前端部分
js:
//绑定导入文件按钮
$('#test_import').before('<input type="file" id="load_1" name="file" style="display:none" onchange ="importTest()">');
function importTest(){
var myform = new FormData();
myform.append('file',$('#load_1')[0].files[0]);
myform.append('type','111');
$.ajax({
type: 'post',
url: "/xm/xx/importFile.htm",
data: myform,
cache: false,
processData: false,
contentType: false,
}).success(function (res) {
if(res.success){
//为表格赋值
else{
//错误提示
}
}).error(function () {
//错误提示
});
}