easypoi导入excel实现一对多导入

java使用easypoi操作excel实现数据一对多的导入

1.导入依赖 这里就不详细介绍了 网上特别多
2.创建实体类
2.1 创建 CertificateConfigCluster 实体类

@Data
public class CertificateConfigCluster {

    /** 
     *  @Excel(needMerge = true) 是否要合并单元格
     * @Excel(importFormat = "yyyy-MM-dd") importFormat 导入时候处理xls表格的日期格式
     * @Excel 注解中 还有很多的属性 比如字段判空 等等 大家可以上官网自行查看
     */

    /**
     * @Fields certificateName 证书名称
     */
    @Excel(needMerge = true, name = "证书名称", width = 20)
    private String certificateName;
    /**
     * @Fields certificateExpiryTime 证书到期时间
     */
    @Excel(needMerge = true, name = "到期时间", width = 20, importFormat = "yyyy-MM-dd")
    private LocalDateTime certificateExpiryTime;
    /**
     * 子集
     */
    @ExcelCollection(name = "应用信息")
    private List<CertificateApplicationConfigCluster> certificateApplicationConfigClusterList;
}

2.2 创建 一对多实体类 CertificateApplicationConfigCluster

实现IExcelDataModel接口 可获取行号 实现 IExcelModel接口可以获取错误数据

@Data
public class CertificateApplicationConfigCluster implements IExcelDataModel {


    /**
     * @Fields applicationName 应用名称
     */
    @Excel(name = "部署应用", width = 20)
    private String applicationName;
    /**
     * @Fields businessContact 业务联系人
     */
    @Excel(name = "业务联系人", width = 20)
    private String businessContact;
    /**
     * @Fields businessMobileNumber 手机号码(业务)
     */
    @Excel(name = "手机号码(业务)", width = 20)
    private String businessMobileNumber;
    /**
     * @Fields businessMailbox 邮箱(业务)
     */
    @Excel(name = "邮箱(业务)", width = 20)
    private String businessMailbox;
    /**
     * @Fields technicalContact 技术联系人
     */
    @Excel(name = "技术联系人", width = 20)
    private String technicalContact;
    /**
     * @Fields technicalPhoneNumber 手机号(技术)
     */
    @Excel(name = "手机号码(技术)", width = 20)
    private String technicalPhoneNumber;
    /**
     * @Fields technicalMailbox 邮箱(技术)
     */
    @Excel(name = "邮箱(技术)", width = 20)
    private String technicalMailbox;

    /**
     * 行数 注意: 使用了 lombok 中 @data注解 可以不重写 下面两个方法 没有影响
     */
    private Integer rowNum;

    /**
     * 获取行数
     * @return
     */
    @Override
    public Integer getRowNum() {
        return rowNum;
    }

    /**
     * 设置行数
     * @param rowNum
     */
    @Override
    public void setRowNum(Integer rowNum) {
        this.rowNum = rowNum;
    }
}

3.编写导入工具类

@Slf4j
public class ExcelUtils {
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows,
                                          Class<T> pojoClass, HttpServletResponse response) {
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setNeedVerify(true);
        ExcelImportResult<T> result = null;
        try {
            result = ExcelImportUtil.importExcelMore(file.getInputStream(), pojoClass, params);
        } catch (NoSuchElementException e) {
        	// 日志记录错误
            log.error("excel cannot empty:", e.getMessage());
            BusinessException.throwBusinessException(MsgEnum.EXCEL_CANNOT_EMPIT);
        } catch (Exception e) {
            // 日志记录错误
            log.error("excel import failed:", e.getMessage());
            BusinessException.throwBusinessException(MsgEnum.EXCEL_IMPORT_FAILED);
        }
        if (JudgeUtils.isNull(result)) {
            return null;
        }
        if (result.isVerifyFail()) {
            // 如果有错误，把错误数据返回到前端(让前端下载一个错误的excel)
            Workbook failWorkbook = result.getFailWorkbook();
            downLoadExcel("error_record.xls", response, failWorkbook);
            // 一个自定义枚举 错误信息的
            BusinessException.throwBusinessException(MsgEnum.EXCEL_RECORD_ILLEGAL);
        }
        return result.getList();
    }

    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
            response.setCharacterEncoding("utf-8");
            response.setHeader("content-Type", "application/octet-stream");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
        	// 一个自定义枚举 错误信息的
            BusinessException.throwBusinessException(MsgEnum.IO_EXCEPTION);
        }
    }
}

4.编写Controller

controller写法1：

 @PostMapping("/batch/import")
 public void batchImport(@RequestParam("file") MultipartFile file){
     List<CertificateConfigCluster> certificateConfigClustersList = ExcelUtils.importExcel(file, 0, 2, CertificateConfigCluster.class, response);
 }

controller写法2：

 @GetResource(path = "/importExcel", name = "解析excel", requiredPermission = false, requiredLogin = false)
    @ApiOperation(value = "解析 excel")
    public ResponseData importExcel(@RequestParam(name = "filePath") String filePath) throws ParseException {
        ImportParams params = new ImportParams();
        //设置表头
        params.setHeadRows(2);
        //开启验证
        params.setNeedVerify(true);
        params.setVerifyHandler(verifyHandler);
        ExcelImportResult<BookVo> result = null;
        InputStream inputStream = ObsUtils.downloadInputStream(filePath);
        try {
            result = ExcelImportUtil.importExcelMore(inputStream, BookVo.class, params);
        } catch (Exception e) {
            log.error("解析文件错误：", e.toString());
            return ResponseData.error("解析文件错误！");
        }
        //校验成功的集合
        List<BookVo> successList = result.getList();
        //校验失败的集合
        List<BookVo> failList = result.getFailList();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        for (BookVo bookVo : failList) {
            bookVo.setRowNumTitle("第" + (bookVo.getRowNum() + 1) + "行");
            bookVo.setCreateTime(sdf.parse(sdf.format(new Date())));
        }
        Map map = new HashMap();
        map.put("successList", successList);
        map.put("failList", failList);
        return ResponseData.success(map);
    }

同时也可参考easypoi的一对多导出：https://blog.youkuaiyun.com/qq_26144365/article/details/125999884