java实现excel数据导入

文件导入

由于项目中常常用到批量导入数据，用到了excel导入，方便自己所以记录一下大概流程。

准备工作:

准备一个模版，用于数据校验和导入使用。

例如：xxxx.xls 或者xxxx.xlsx

下载模版代码实例:

fileName：文件名称
path：模版路径地址

public R mouldExport(HttpServletResponse response) {
        String fileName = "xxxx.xls";
        String path = "template/xxxx.xls";
        DownloadFile.exportFile(fileName, path, response);
        return R.ok();
}

文件下载代码实例:

import io.card.common.exception.RRException;
import org.springframework.core.io.ClassPathResource;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
public class DownloadFile {
    /**
     * 下载文件
     *
     */
    public static void exportFile(String fileName,String path,HttpServletResponse response) {
        ClassPathResource classPathResource = new ClassPathResource(path);
        byte[] buff = new byte[1024];
        BufferedInputStream bis = null;
        OutputStream os = null;
        try {
            //获取文件流
            InputStream stream = classPathResource.getInputStream();
            response.setHeader("content-type", "application/octet-stream");
            response.setContentType("application/json; charset=utf-8");
            String name = java.net.URLEncoder.encode(fileName, "UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + java.net.URLDecoder.decode(name, "ISO-8859-1") );
            os = response.getOutputStream();
            bis = new BufferedInputStream(stream);
            int i = bis.read(buff);
            while (i != -1) {
                os.write(buff, 0, buff.length);
                os.flush();
                i = bis.read(buff);
            }
        }catch (UnsupportedEncodingException e2) {
            e2.printStackTrace();
        } catch (FileNotFoundException e1) {
            throw new RRException("系统找不到指定的文件");
        }catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (bis != null) {
                try {
                    bis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

导入代码实例:

ReadExcel.readExcel(file, 3): 我的模版中是从第三行后开始读写判断的，前面三行写了文档注意事项

public R uploadFile(@ApiIgnore @LoginUser SysUserEntity user,@RequestParam MultipartFile file) {
        Map<Long, Map<Integer, String>> map = ReadExcel.readExcel(file, 3);
        if (null == map) {
            return R.error("上传失败");
        }
        sysUserService.sysUserImport(map, user);
        return R.ok();
}

具体导入过程中的读写判断实例:

public void sysUserImport(Map<Long, Map<Integer, String>> map, SysUserEntity user) {
        Long i = 4l; // **之前模版默认前三行是注释，所以从第四行开始**
        SysUserEntity sysUserEntity = new SysUserEntity();
        while (true) {
            if (map.get(i) == null || map.get(i).isEmpty()) { //判断文档是不是空
                break;
            }
            Map<Integer, String> paramMap = map.get(i);
            //姓名
            if (StringUtil.isNullOrEmpty(paramMap.get(0))) {
                throw new RRException("第" + i + "行，姓名不能为空");
            }
            sysUserEntity.setUsername(paramMap.get(0).trim());
            //手机号
            if (StringUtil.isNullOrEmpty(paramMap.get(1))) {
                throw new RRException("第" + i + "行，手机号不能为空");
            }
            if (paramMap.get(1).trim().length() != 11) {
                throw new RRException("第" + i + "行，手机号不正确");
            }
            //判断手机号是否存在
            int cnt = this.selectCount(new EntityWrapper<SysUserEntity>()
                    .eq(LambdaHelper.asVOFieldUL(SysUserEntity::getMobile), paramMap.get(1).trim()));
            if (cnt != 0) {
                throw new RRException("第" + i + "行，手机号已存在");
            }
            sysUserEntity.setMobile(paramMap.get(1).trim());
            //部门
            if (StringUtil.isNullOrEmpty(paramMap.get(2))) {
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                throw new RRException("第" + i + "行，部门不能为空");
            }
            String[] deptAllList = paramMap.get(2).split(",");
            String[] isLeaderList = paramMap.get(4).split(",");
            if (!StringUtil.isNullOrEmpty(paramMap.get(4)) && isLeaderList.length != deptAllList.length) {
                throw new RRException("第" + i + "行，部门和是否部门主管必须对应");
            }
            //性别
            if (StringUtil.isNullOrEmpty(paramMap.get(3))) {
                throw new RRException("第" + i + "行，性别不能为空");
            }
            if (paramMap.get(3).trim().equals("男")) {
                sysUserEntity.setSex(1);
            } else if (paramMap.get(3).trim().equals("女")) {
                sysUserEntity.setSex(0);
            } else {
                throw new RRException("第" + i + "行，性别不正确");
            }
            //是否有车
            if (StringUtil.isNullOrEmpty(paramMap.get(5))) {
                throw new RRException("第" + i + "行，是否有车字段不能为空");
            }
            if (paramMap.get(5).trim().equals("是")) {
                sysUserEntity.setCarflg(1);
            } else if (paramMap.get(5).trim().equals("否")) {
                sysUserEntity.setCarflg(0);
            } else {
                throw new RRException("第" + i + "行，是否有车字段不正确");
            }
            //车牌号
            if (StringUtil.isNullOrEmpty(paramMap.get(6)) && paramMap.get(5).trim().equals("是")) {
                throw new RRException("第" + i + "行，车牌号不能为空");
            }
            if (paramMap.get(6).trim().length() != 7 && paramMap.get(5).trim().equals("是")) {
                throw new RRException("第" + i + "行，车牌号格式不正确");
            }
            //判断车牌号是否存在
            int car = this.selectCount(new EntityWrapper<SysUserEntity>()
                    .eq(LambdaHelper.asVOFieldUL(SysUserEntity::getCarnumber), paramMap.get(6).trim()));
            if (car != 0) {
                throw new RRException("第" + i + "行，车牌号已存在");
            }
            sysUserEntity.setCarnumber(paramMap.get(6).trim());
            //工卡号
            if (!StringUtil.isNullOrEmpty(paramMap.get(7))) {
                SysUserEntity userCard = new SysUserEntity();
                userCard.setCompanyId(user.getCompanyId());
                userCard.setCard(paramMap.get(7).trim());
                //校验工卡号是否存在且未绑定
                boolean bool = this.checkCardNumber(userCard);
                if (!bool) {
                    throw new RRException("第" + i + "行，工卡号不存在或已绑定");
                }
                sysUserEntity.setCard(paramMap.get(7).trim());
            }
            //身份证
            if (!StringUtil.isNullOrEmpty(paramMap.get(8))) {
                if (paramMap.get(8).trim().length() != 18) {
                    throw new RRException("第" + i + "行，身份证格式不正确");
                }
                sysUserEntity.setCertificate(paramMap.get(8).trim());
            }
            //身高
            if (!StringUtil.isNullOrEmpty(paramMap.get(9))) {
                sysUserEntity.setStature(paramMap.get(9).trim());
            }
            //体重
            if (!StringUtil.isNullOrEmpty(paramMap.get(10))) {
                sysUserEntity.setWeight(paramMap.get(10).trim());
            }
            //血型
            if (!StringUtil.isNullOrEmpty(paramMap.get(11))) {
                String bloodType=paramMap.get(11).trim();
                if (!bloodType.equals("A型")
                    && !bloodType.equals("B型")
                    && !bloodType.equals("AB型")
                    && !bloodType.equals("O型")
                    && !bloodType.equals("稀有血型")) {
                    throw new RRException("第" + i + "行，血型格式不正确");
                }
                sysUserEntity.setBloodType(bloodType);
            }
            //疾病史
            if (!StringUtil.isNullOrEmpty(paramMap.get(12))) {
                String disease=paramMap.get(12).trim();
                if (!disease.equals("有") && !disease.equals("无")) {
                    throw new RRException("第" + i + "行，疾病史格式不正确");
                }
                sysUserEntity.setDisease(disease);
            }
            //sha256加密
            String salt = RandomStringUtils.randomAlphanumeric(20);
            sysUserEntity.setPassword(new Sha256Hash(sysUserEntity.getMobile(), salt).toHex());
            sysUserEntity.setSalt(salt);
            sysUserEntity.setStatus(1);//正常状态
            sysUserEntity.setCompanyId(user.getCompanyId());
            this.insert(sysUserEntity);
            //角色
            if (!StringUtil.isNullOrEmpty(paramMap.get(13))) {
                String[] roleList = paramMap.get(13).trim().split(",");
                for (String roleName : roleList) {
                    SysRoleEntity sysRoleEntity = sysRoleService.selectOne(new EntityWrapper<SysRoleEntity>()
                            .eq(LambdaHelper.asVOFieldUL(SysRoleEntity::getRoleName), roleName)
                            .eq(LambdaHelper.asVOFieldUL(SysRoleEntity::getCompanyId), user.getCompanyId()));
                    if (null == sysRoleEntity) {
                        throw new RRException("第" + i + "行，角色名称不存在");
                    } else {
                        SysUserRoleEntity sysUserRoleEntity = new SysUserRoleEntity();
                        sysUserRoleEntity.setRoleId(sysRoleEntity.getRoleId());
                        sysUserRoleEntity.setUserId(sysUserEntity.getUserId());
                        sysUserRoleService.insert(sysUserRoleEntity);
                    }
                }
            }
            //部门，没有就新增
            for (int deptAlli = 0; deptAlli < deptAllList.length; deptAlli++) {
                String[] dept = deptAllList[deptAlli].split("-");
                Integer deptParentId = 0;
                for (int depti = 0; depti < dept.length; depti++) {
                    SysDeptEntity sysDeptEntity = sysDeptService.selectOne(new EntityWrapper<SysDeptEntity>()
                            .eq(LambdaHelper.asVOFieldUL(SysDeptEntity::getName), dept[depti])
                            .eq(LambdaHelper.asVOFieldUL(SysDeptEntity::getCompanyId), user.getCompanyId()));
                    if (sysDeptEntity == null) {
                        sysDeptEntity = new SysDeptEntity();
                        sysDeptEntity.setCompanyId(user.getCompanyId());
                        sysDeptEntity.setName(dept[depti]);
                        sysDeptEntity.setParentId(deptParentId);
                        sysDeptEntity.setCreateUserId(user.getUserId().intValue());
                        sysDeptService.insert(sysDeptEntity);
                        sysDeptEntity.setSort(sysDeptEntity.getId());
                        sysDeptService.updateById(sysDeptEntity);
                    }
                    deptParentId = sysDeptEntity.getId();
                    if (depti == dept.length - 1) {
                        SysUserDeptEntity sysUserDeptEntity = new SysUserDeptEntity();
                        sysUserDeptEntity.setDeptId(sysDeptEntity.getId());
                        sysUserDeptEntity.setUserId(sysUserEntity.getUserId().intValue());
                        if (StringUtil.isNullOrEmpty(paramMap.get(4))) {
                            sysUserDeptEntity.setIsLeader(0);
                        } else {
                            if (isLeaderList[deptAlli].equals("是") && depti == (dept.length - 1)) {
                                sysUserDeptEntity.setIsLeader(1);
                            } else {
                                sysUserDeptEntity.setIsLeader(0);
                            }
                        }
                        sysUserDeptService.insert(sysUserDeptEntity);
                    }
                }
            }
            i++;
        }
    }