layui+poi实现批量导入

背景：自己的毕业设计需要用到批量导入，网上搜索需要用poi，自己捣鼓了半天，实现成功。

poi详解：https://blog.youkuaiyun.com/qq_44037690/article/details/84937003（转载）

 

1 layui部分：

 <button class="layui-btn layui-btn-sm" id="import">批量导入</button>



 //文件上传
                upload.render({ //允许上传的文件后缀
                elem: '#import'
                ,url: '/member/upload/'
                ,accept: 'file' //普通文件
                ,exts: 'xlsx' //上传文件格式说
                ,done: function(res){
                    layer.msg(res.msg,{
                        btn:['确定'],
                        btnAlign: 'c',
                        time:20000,
                        btn1:function (index,layero) {
                            location.reload();
                        }
                    });
                }
            });

 

2  需要导入的文件内容：

4导入依赖

<!--读取excel-->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.17</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.17</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-scratchpad</artifactId>
			<version>3.17</version>
		</dependency>

 

4java代码

//控制器
    @RequestMapping("/upload")
    @ResponseBody
    public Result uploadPartMember(@RequestParam("file") MultipartFile file){
            try {
                if (file!=null){
                    Result result = partMemberService.uploadPartMember(file);
                    return result;
                }else {
                    return Result.build(201,"表格为空");
                }
            }catch (Exception e){
                e.printStackTrace();
                return Result.build(202,"上传出现异常，请稍后再试");
            }
     }
}


//service

 @Override
    public Result uploadPartMember(MultipartFile file) throws IOException {
            InputStream in = file.getInputStream();
            XSSFWorkbook wb = new XSSFWorkbook(in);
            XSSFSheet sheet = wb.getSheetAt(0);
            int lastRowNum = sheet.getLastRowNum();
            List<String > nameList = new ArrayList <>();
            for (int i = 2;i<=lastRowNum;i++){
                PartMemberInfo partMember = new PartMemberInfo();
                XSSFRow row = sheet.getRow(i);
                //验证身份证 手机号 email
                String name = row.getCell(0).getStringCellValue();
                String idNo = row.getCell(7).getStringCellValue();
                String phoneNo = row.getCell(10).getRawValue();
                String email = row.getCell(12).getStringCellValue();
                Result validate = uniquenessValidate.validate(idNo, phoneNo, email);
                if (validate.getStatus()!=200){
                    nameList.add(name);
                    continue;
                }
                partMember.setName(name);
                if (row.getCell(1).getStringCellValue().equals("男")){
                    partMember.setSex(1);
                }else{
                    partMember.setSex(0);
                }
                partMember.setNation(row.getCell(2).getStringCellValue());
                partMember.setNativePlace(row.getCell(3).getStringCellValue());
                                                                                                                            partMember.setBirthday(DateUtil.string2Date(row.getCell(4).toString(),"yyyy.MM.dd"));                
                partMember.setEducation(row.getCell(6).getStringCellValue());
                partMember.setStatus(1);
                partMember.setIdNo(idNo);
                partMember.setPhoneNo(phoneNo);
                partMember.setEmail(email);
                partMember.setDuty(Integer.valueOf(row.getCell(15).toString().substring(0,1)));
                partMember.setJoinPartDate(DateUtil.string2Date(row.getCell(17).toString(),"yyyy.MM.dd"));
                if (idNo.charAt(17)== 'x'||idNo.charAt(17)=='X'){
                    partMember.setPartMemberCode(idNo.substring(11,17));
                }else{
                    partMember.setPartMemberCode(idNo.substring(12));
                }
                partMemberInfoMapper.insert(partMember);
            }
            String mes = null;
            if (nameList!=null&&nameList.size()>0){
                mes = nameList.toString()+"信息出错，请更改后重新上传";
            }else {
                mes = "全部上传成功";
            }
             return Result.build(200,mes);
    }

注意的点：

1 excel 的单元格的索引行和列都是从0开始。

2如果有合并的单元格该单元格的索引是合并的第一个单元格的索引。下一个单元格跳过合并的索引。

3其中DateUtil是封装的时间工具类，uniquenessValidate是封装的校验身份证邮箱和电话号码的工具类

4开头创建一个集合 存储上传失败人的姓名，上传全部成功返回全部成功的消息，如果有失败，返回失败人姓名的集合