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使用得mapMap<String, Object> headExportMap = new HashMap<>();// title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetNameheadExportMap.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>*/@Overridepublic 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;@Overridepublic Integer getRowNum() {return rowNum;}@Overridepublic void setRowNum(Integer integer) {this.rowNum = integer;}@Overridepublic String getErrorMsg() {return errorMsg;}@Overridepublic void setErrorMsg(String s) {this.errorMsg = s;}}