easypoi 导出Excel 使用总结

easypoi 导出Excel

导出Excel需要设置标题，且标题是多行，标题下面是列表头

设置表格标题

 ExportParams headExportParams = new ExportParams();
 StringBuilder buffer = new StringBuilder("");
 buffer.append("1、课程名称：......\n")
         .append("2、课程标签：......\n")
         .append("5、适用人群：......");
 headExportParams.setTitle(buffer.toString());
 headExportParams.setTitleHeight((short) 50);

设置标题样式

headExportParams.setStyle(MyExcelExportStyler.class);

完整代码如下

@Override
@Async
public void downloadCoursewareDataAsync(List<Long> ids, FileDownloadHistory history, String sheetName) {
    try {
        history.setExportStatus(1);
        List<CoursewareManagement> list = coursewareManagementMapper.queryCoursewareByIdList(ids);
        List<CoursewareManagementDto> voList = new ArrayList<>();
        
        // 创建参数对象（用来设定excel得sheet得内容等信息）
        ExportParams headExportParams = new ExportParams();
        StringBuilder buffer = new StringBuilder("");
        buffer.append("1、课程名称：......，不允许重名\n")
                .append("2、课程标签：多个标签用 、 隔开\n")
                .append("5、适用人群-岗位：......");
        headExportParams.setTitle(buffer.toString());
        headExportParams.setTitleHeight((short) 50);
        headExportParams.setStyle(MyExcelExportStyler.class);
        // 设置sheet得名称
        headExportParams.setSheetName(sheetName);
        // 创建sheet1使用得map
        Map<String, Object> headExportMap = new HashMap<>();
        // title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName
        headExportMap.put("title", headExportParams);
        // 模版导出对应得实体类型
        headExportMap.put("entity", CoursewareManagementDto.class);
        // sheet中要填充得数据
        headExportMap.put("data", voList);

        // 将sheet1、sheet2、sheet3使用得map进行包装
        List<Map<String, Object>> sheetsList = new ArrayList<>();
        sheetsList.add(headExportMap);

        try (
                Workbook workbook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.XSSF);
                FileOutputStream outputStream = new FileOutputStream(history.getFilePath());
        ) {
            workbook.write(outputStream);
        } catch (Exception e) {
            throw e;
        }
    } catch (Exception e) {
        log.error("课件管理异步下载接口==>{}", e.getLocalizedMessage(), e);
        history.setExportStatus(0);
    }
    history.setModifyTime(new Date());
    fileDownloadHistoryMapper.updateByPrimaryKeySelective(history);
}

public class MyExcelExportStyler extends ExcelExportStylerDefaultImpl {

    public MyExcelExportStyler(Workbook workbook) {
        super(workbook);
    }

    /**
     * <p>
     * 设置表格标题样式
     * </p>
     */
    @Override
    public CellStyle getHeaderStyle(short color) {
        CellStyle titleStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        titleStyle.setFont(font);
        titleStyle.setWrapText(true);
        titleStyle.setAlignment(HorizontalAlignment.LEFT);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return titleStyle;
    }
}

源码里的 Excel 导出基本注释很重要，特别是按实体类导出的时候

可以看下这个类 cn.afterturn.easypoi.excel.annotation.Excel

@Excel needMerge 和 @ExcelCollection 配合使用，处理 Excel 一对多的关系

@Data
public class CoursewareManagementVo implements IExcelModel, IExcelDataModel {

    @Excel(name = "课件名称", needMerge = true, width = 25, orderNum = "1")
    private String coursewareName;
    @Excel(name = "课件格式", needMerge = true, width = 25, orderNum = "2")
    private String coursewareFormat;
    @Excel(name = "创建时间", needMerge = true, width = 25, orderNum = "3", format = "yyyy-MM-dd HH:mm:ss")
    private Date createTime;
    @Excel(name = "课程条线", needMerge = true, width = 25, orderNum = "4")
    private String classifyName;
    @ExcelCollection(name = "", orderNum = "5")
    private List<ColumnVo> columnVos;

    private String errorMsg;
    private Integer rowNum;

    @Override
    public Integer getRowNum() {
        return rowNum;
    }

    @Override
    public void setRowNum(Integer integer) {
        this.rowNum = integer;
    }

    @Override
    public String getErrorMsg() {
        return errorMsg;
    }

    @Override
    public void setErrorMsg(String s) {
        this.errorMsg = s;
    }

}