SpringBoot整合EasyExcel实现文件导入导出

  com.alibabaeasyexcel

 /** * 
 * 操作日志信息 * 
 * * @author horse * @since 2020-09-08 * 注意: 实体类中如果使用@Accessory（chain=true）,那么导入的数据无法填充到实例中,导出数据不受影响 */ @Data @EqualsAndHashCode(callSuper = false) @TableName("tb_operational_log") @ApiModel(value = "OperationalLog对象", description = "操作日志信息") public class OperationalLog implements Serializable { private static final long serialVersionUID = 1L; @ExcelProperty({"操作日志", "日志ID"}) @ApiModelProperty(value = "日志ID") @TableId(value = "id", type = IdType.ASSIGN_ID) private String id; @ExcelProperty({"操作日志", "操作类型"}) @ApiModelProperty(value = "操作类型") private String operType; @ExcelProperty({"操作日志", "操作描述"}) @ApiModelProperty(value = "操作描述") private String operDesc; @ExcelProperty({"操作日志", "操作员ID"}) @ApiModelProperty(value = "操作员ID") private String operUserId; @ExcelProperty({"操作日志", "操作员名称"}) @ApiModelProperty(value = "操作员名称") private String operUserName; @ExcelProperty({"操作日志", "操作方法"}) @ApiModelProperty(value = "操作方法") private String operMethod; @ExcelProperty({"操作日志", "请求方法"}) @ApiModelProperty(value = "请求方法") private String operRequWay; @ExcelProperty(value = {"操作日志", "请求耗时：单位-ms"}, converter = CustomRequestTimeConverter.class) @ApiModelProperty(value = "请求耗时：单位-ms") private Long operRequTime; @ExcelProperty({"操作日志", "请求参数"}) @ApiModelProperty(value = "请求参数") private String operRequParams; @ExcelProperty({"操作日志", "请求Body"}) @ApiModelProperty(value = "请求Body") private String operRequBody; @ExcelProperty({"操作日志", "请求IP"}) @ApiModelProperty(value = "请求IP") private String operRequIp; @ExcelProperty({"操作日志", "请求URL"}) @ApiModelProperty(value = "请求URL") private String operRequUrl; @ExcelProperty(value = {"操作日志", "日志标识"}, converter = CustomLogFlagConverter.class) @ApiModelProperty(value = "日志标识: 1-admin,0-portal") private Boolean logFlag; @ExcelProperty({"操作日志", "操作状态"}) @ApiModelProperty(value = "操作状态:1-成功,0-失败") @TableField(value = "is_success") private Boolean success; @ExcelIgnore @ApiModelProperty(value = "逻辑删除 1-未删除， 0-删除") @TableField(value = "is_deleted") @TableLogic(value = "1", delval = "0") private Boolean deleted; @ExcelProperty(value = {"操作日志", "创建时间"}, converter = CustomTimeFormatConverter.class) @ApiModelProperty(value = "创建时间") private Date gmtCreate; } 

 @OperatingLog(operType = BlogConstants.EXPORT, operDesc = "导出操作日志,写出到响应流中") @ApiOperation(value = "导出操作日志", hidden = true) @PostMapping("/oper/export") public void operLogExport(@RequestBody List logIds, HttpServletResponse response) { operationalLogService.operLogExport(logIds, response); } 

 /** * 导出操作日志(可以考虑分页导出) * * @param logIds * @param response */ @Override public void operLogExport(List logIds, HttpServletResponse response) { OutputStream outputStream = null; try { List operationalLogs; LambdaQueryWrapper queryWrapper = new LambdaQueryWrapper() .orderByDesc(OperationalLog::getGmtCreate); // 如果logIds不为null,按照id查询信息,否则查询全部 if (!CollectionUtils.isEmpty(logIds)) { operationalLogs = this.listByIds(logIds); } else { operationalLogs = this.list(queryWrapper); } outputStream = response.getOutputStream(); // 获取单元格样式 HorizontalCellStyleStrategy strategy = MyCellStyleStrategy.getHorizontalCellStyleStrategy(); // 写入响应输出流数据 EasyExcel.write(outputStream, OperationalLog.class).excelType(ExcelTypeEnum.XLSX).sheet("操作信息日志") // .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 自适应列宽(不是很适应,效果并不佳) .registerWriteHandler(strategy) // 注册上面设置的格式策略 .registerWriteHandler(new CustomCellWriteHandler()) // 设置自定义格式策略 .doWrite(operationalLogs); } catch (Exception e) { log.error(ExceptionUtils.getMessage(e)); throw new BlogException(ResultCodeEnum.EXCEL_DATA_EXPORT_ERROR); } finally { IoUtil.close(outputStream); } } 

 /** * @author Mr.Horse * @version 1.0 * @description: 单元格样式策略 * @date 2021/4/30 8:43 */ public class MyCellStyleStrategy { /** * 设置单元格样式(仅用于测试) * * @return 样式策略 */ public static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() { // 表头策略 WriteCellStyle headerCellStyle = new WriteCellStyle(); // 表头水平对齐居中 headerCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); // 背景色 headerCellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex()); WriteFont headerFont = new WriteFont(); headerFont.setFontHeightInPoints((short) 14); headerCellStyle.setWriteFont(headerFont); // 自动换行 headerCellStyle.setWrapped(Boolean.FALSE); // 内容策略 WriteCellStyle contentCellStyle = new WriteCellStyle(); // 设置数据允许的数据格式,这里49代表所有可以都允许设置 contentCellStyle.setDataFormat((short) 49); // 设置背景色: 需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定 contentCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND); contentCellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); // 设置内容靠左对齐 contentCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT); // 设置字体 WriteFont contentFont = new WriteFont(); contentFont.setFontHeightInPoints((short) 12); contentCellStyle.setWriteFont(contentFont); // 设置自动换行 contentCellStyle.setWrapped(Boolean.FALSE); // 设置边框样式和颜色 contentCellStyle.setBorderLeft(MEDIUM); contentCellStyle.setBorderTop(MEDIUM); contentCellStyle.setBorderRight(MEDIUM); contentCellStyle.setBorderBottom(MEDIUM); contentCellStyle.setTopBorderColor(IndexedColors.RED.getIndex()); contentCellStyle.setBottomBorderColor(IndexedColors.GREEN.getIndex()); contentCellStyle.setLeftBorderColor(IndexedColors.YELLOW.getIndex()); contentCellStyle.setRightBorderColor(IndexedColors.ORANGE.getIndex()); // 将格式加入单元格样式策略 return new HorizontalCellStyleStrategy(headerCellStyle, contentCellStyle); } } 

 /** * @author Mr.Horse * @version 1.0 * @description 实现CellWriteHandler接口, 实现对单元格样式的精确控制 * @date 2021/4/29 21:11 */ public class CustomCellWriteHandler implements CellWriteHandler { private static Logger logger = LoggerFactory.getLogger(CustomCellWriteHandler.class); @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) { } /** * 单元格创建之后(没有写入值) * * @param writeSheetHolder * @param writeTableHolder * @param cell * @param head * @param relativeRowIndex * @param isHead */ @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { } /** * 单元格处理后(已写入值): 设置第一行第一列的头超链接到EasyExcel的官网(本系统的导出的excel 0,1两行都是头,所以只设置第一行的超链接) * 这里再进行拦截的单元格样式设置的话,前面该样式将全部失效 * * @param writeSheetHolder * @param writeTableHolder * @param cellDataList * @param cell * @param head * @param relativeRowIndex * @param isHead */ @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { // 设置超链接 if (isHead && cell.getRowIndex() == 0 && cell.getColumnIndex() == 0) { logger.info(" ==> 第{}行,第{}列超链接设置完成", cell.getRowIndex(), cell.getColumnIndex()); CreationHelper helper = writeSheetHolder.getSheet().getWorkbook().getCreationHelper(); Hyperlink hyperlink = helper.createHyperlink(HyperlinkType.URL); hyperlink.setAddress("https://github.com/alibaba/easyexcel"); cell.setHyperlink(hyperlink); } // 精确设置单元格格式 boolean bool = isHead && cell.getRowIndex() == 1 && (cell.getStringCellValue().equals("请求参数") || cell.getStringCellValue().equals("请求Body")); if (bool) { logger.info("第{}行，第{}列单元格样式设置完成。", cell.getRowIndex(), cell.getColumnIndex()); // 获取工作簿 Workbook workbook = writeSheetHolder.getSheet().getWorkbook(); CellStyle cellStyle = workbook.createCellStyle(); Font cellFont = workbook.createFont(); cellFont.setBold(Boolean.TRUE); cellFont.setFontHeightInPoints((short) 14); cellFont.setColor(IndexedColors.SEA_GREEN.getIndex()); cellStyle.setFont(cellFont); cell.setCellStyle(cellStyle); } } } 

 // 批量导出 batchExport() { // 遍历获取id集合列表 const logIds = [] this.multipleSelection.forEach(item => { logIds.push(item.id) }) // 请求后端接口 axios({ url: this.BASE_API + '/admin/blog/log/oper/export', method: 'post', data: logIds, responseType: 'arraybuffer', headers: { 'token': getToken() } }).then(response => { // type类型可以设置为文本类型，这里是新版excel类型 const blob = new Blob([response.data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8' }) const pdfUrl = window.URL.createObjectURL(blob) const fileName = 'HorseBlog操作日志' // 下载文件的名字 // 对于标签，只有 Firefox 和 Chrome（内核）支持 download 属性 if ('download' in document.createElement('a')) { const link = document.createElement('a') link.href = pdfUrl link.setAttribute('download', fileName) document.body.appendChild(link) link.click() window.URL.revokeObjectURL(pdfUrl) // 释放URL 对象 } else { // IE 浏览器兼容方法 window.navigator.msSaveBlob(blob, fileName) } }) } 

 /** * @author Mr.Horse * @version 1.0 * @description: EasyExcel文件读取配置(不能让spring管理) * @date 2021/4/27 13:24 */ public class MyExcelImportConfig extends AnalysisEventListener { private static Logger logger = LoggerFactory.getLogger(MyExcelImportConfig.class); /** * 每次读取的最大数据条数 */ private static final int MAX_BATCH_COUNT = 10; /** * 泛型bean属性 */ private T dynamicService; /** * 可接收任何参数的泛型List集合 */ List list = new ArrayList<>(); /** * 构造函数注入bean(根据传入的bean动态注入) * * @param dynamicService */ public MyExcelImportConfig(T dynamicService) { this.dynamicService = dynamicService; } /** * 解析每条数据都进行调用 * * @param data * @param context */ @Override public void invoke(T data, AnalysisContext context) { logger.info(" ==> 解析一条数据: {}", JacksonUtils.objToString(data)); list.add(data); if (list.size() > MAX_BATCH_COUNT) { // 保存数据 saveData(); // 清空list list.clear(); } } /** * 所有数据解析完成后,会来调用一次 * 作用: 避免最后集合中小于 MAX_BATCH_COUNT 条的数据没有被保存 * * @param context */ @Override public void doAfterAllAnalysed(AnalysisContext context) { saveData(); logger.info(" ==> 数据解析完成 <=="); } /** * 保存数据: 正式应该插入数据库,这里用于测试 */ private void saveData() { logger.info(" ==> 数据保存开始: {}", list.size()); list.forEach(System.out::println); logger.info(" ==> 数据保存结束 <=="); } /** * 在转换异常 获取其他异常下会调用本接口。我们如果捕捉并手动抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。 * * @param exception * @param context * @throws Exception */ @Override public void onException(Exception exception, AnalysisContext context) throws Exception { logger.error(" ==> 数据解析失败,但是继续读取下一行:{}", exception.getMessage()); //  如果是某一个单元格的转换异常 能获取到具体行号 if (exception instanceof ExcelDataConvertException) { ExcelDataConvertException convertException = (ExcelDataConvertException) exception; logger.error("第{}行，第{}列数据解析异常", convertException.getRowIndex(), convertException.getColumnIndex()); } } } 

 @ApiOperation(value = "数据导入测试", notes = "操作日志导入测试[OperationalLog]", hidden = true) @PostMapping("/import") public R excelImport(@RequestParam("file") MultipartFile file) throws IOException { EasyExcel.read(file.getInputStream(), OperationalLog.class, new MyExcelImportConfig<>(operationalLogService)) .sheet().doRead(); return R.ok().message("文件导入成功"); } 

 /** * @author Mr.Horse * @version 1.0 * @description: 自定义excel转换器: 将操作日志的请求耗时加上单位 "ms" * @date 2021/4/27 10:25 */ public class CustomRequestTimeConverter implements Converter { /** * 读取数据时: 属性对应的java数据类型 * * @return */ @Override public Class supportJavaTypeKey() { return Long.class; } /** * 写入数据时: excel内部的数据类型,因为请求耗时是long类型,对应excel是NUMBER类型,但是加上"ms后对应的是STRING类型" * * @return */ @Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; } /** * 读取回调 * * @param cellData * @param contentProperty * @param globalConfiguration * @return * @throws Exception */ @Override public Long convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception { // 截取字符串: "ms",转换为long类型 String value = cellData.getStringValue(); return Long.valueOf(value.substring(0, value.length() - 2)); } @Override public CellData convertToExcelData(Long value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception { // 添加字符串: "ms" return new CellData<>(String.valueOf(value).concat("ms")); } } 

 /** * @author Mr.Horse * @version 1.0 * @description: {description} * @date 2021/4/27 14:01 */ public class CustomTimeFormatConverter implements Converter { @Override public Class supportJavaTypeKey() { return Date.class; } @Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; } @Override public Date convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception { String value = cellData.getStringValue(); return DateUtil.parse(value, DatePattern.NORM_DATETIME_PATTERN); } @Override public CellData convertToExcelData(Date value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception { return new CellData<>(DateUtil.format(value, DatePattern.NORM_DATETIME_PATTERN)); } }