使用 Easypoi一对多数据 导出

使用 Easypoi一对多数据 导出

最近在项目上实现了导出，导出的模板如下：

解决方案：

1.导入依赖 这里就不详细介绍了 网上特别多

2.创建可以完成此标题的实体类

2.1 创建 CertificateListBO 实体类

@Data
public class CertificateListBO {
    /**
     * @Fields id 主键
     */
    private String id;
    /**
     * @Fields certificateName 证书名称
     */
    @Excel(needMerge = true, name = "证书名称", width = 20)
    private String certificateName;

    /**
     * @Fields certificateStatus 证书状态:0正常,1异常，2停用
     */
    @Excel(needMerge = true, name = "证书状态", width = 10)
    private String certificateStatus;
    /**
     * @Fields certificateExpiryTime 证书到期时间
     */
    @Excel(needMerge = true, name = "到期时间", width = 20, exportFormat = "yyyy-MM-dd")
    private LocalDate certificateExpiryTime;
    /**
     * @Fields lastTimeCertificateExpired 证书上一次到期时间
     */
    @Excel(needMerge = true, name = "上一次到期时间", width = 20, exportFormat = "yyyy-MM-dd")
    private LocalDate lastTimeCertificateExpired;

    /**
     * @Fields delFlag 逻辑删除状态(0在用，1删除)
     */
    private Boolean delFlag;

    /**
     * 应用信息
     */
    @ExcelCollection(name = "应用信息")
    private List<CertificateApplicationConfigListBO> certificateApplicationConfigListBO;
}

2.2 创建 一对多实体类 CertificateApplicationConfigListBO

@Data
public class CertificateApplicationConfigListBO {
    /**
     * @Fields applicationName 应用名称
     */
    @Excel(name = "部署应用", width = 30)
    private String applicationName;
    /**
     * @Fields businessContact 业务联系人
     */
    @Excel(name = "业务联系人", width = 15)
    private String businessContact;
    /**
     * @Fields businessMobileNumber 手机号码(业务)
     */
    @Excel(name = "手机号码(业务)", width = 20)
    private String businessMobileNumber;
    /**
     * @Fields businessMailbox 邮箱(业务)
     */
    @Excel(name = "邮箱(业务)", width = 30)
    private String businessMailbox;
    /**
     * @Fields technicalContact 技术联系人
     */
    @Excel(name = "技术联系人", width = 15)
    private String technicalContact;
    /**
     * @Fields technicalPhoneNumber 手机号(技术)
     */
    @Excel(name = "手机号码(技术)", width = 20)
    private String technicalPhoneNumber;
    /**
     * @Fields technicalMailbox 邮箱(技术)
     */
    @Excel(name = "邮箱(技术)", width = 30)
    private String technicalMailbox;
    /**
     * @Fields feedbackResults 反馈结果
     */
    @Excel(name = "反馈结果", width = 10)
    private String feedbackResults;
    /**
     * @Fields feedbackTime 反馈时间
     */
    @Excel(name = "反馈时间", width = 25, exportFormat = "yyyy-MM-dd HH:mm:ss")
    private LocalDateTime feedbackTime;

}

3.编写导出工具类 网上有很多

public class ExcelUtils {

    public static void exportExcel(String fileName, HttpServletResponse response, Class<?> pojoClass, List<?> list, String title) {
        ExportParams params = new ExportParams();
        //设置样式
        params.setStyle(ExcelStyleUtil.class);
        //设置sheet名
        params.setSheetName(title);
        Workbook workbook = ExcelExportUtil.exportExcel(params, pojoClass, list);
        downLoadExcel(fileName, response, workbook);
    }
    
    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

@PostMapping("/batch/downLoad")
public void downLoadExcel(HttpServletResponse response) {
	    // service 实现了处理数据得到 list
        List<CertificateListBO> list = certificateConfigService.getExportList(certificateConfigPageBO);
        ExcelUtils.exportExcel("CertificateConfig_downLoadExcel.xlsx", response, CertificateListBO.class, list, "证书导出");
}