POI 4.1导入Excel

一、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 () {
       //错误提示
    });
}