整合EasyExcel实现灵活的导入导出java

1. 引入pom依赖

         <dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId></dependency>

2. 实现功能

    结合Vue前端，实现浏览器页面直接导出日志文件实现文件的灵活导入
   

文件导出
3. 实体类

实体类里有自定义转换器：用于Java类型数据和Excel类型数据的转换，非常使用。结合注解，可以非常方便的进行Excel文件导出。

/*** <p>* 操作日志信息* </p>** home.php?mod=space&uid=686208 horse* home.php?mod=space&uid=441028 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;
}

4. 接口和具体实现
   4.1 接口

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

4.2 具体实现

    自定义导出策略HorizontalCellStyleStrategy自定义导出拦截器CellWriteHandler，更加精确的自定义导出策略

    /*** 导出操作日志(可以考虑分页导出)** @param logIds* @param response*/@Overridepublic void operLogExport(List<String> logIds, HttpServletResponse response) {OutputStream outputStream = null;try {List<OperationalLog> operationalLogs;LambdaQueryWrapper<OperationalLog> queryWrapper = new LambdaQueryWrapper<OperationalLog>().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);@Overridepublic 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*/@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,Head head, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic 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*/@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,List<CellData> 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);}}
}

4.3 前端请求

前端在基于Vue+Element的基础上实现了点击导出按钮，在浏览器页面进行下载。

// 批量导出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操作日志' // 下载文件的名字// 对于<a>标签，只有 Firefox 和 Chrome（内核）支持 download 属性if ('download' in document.createElement('a')) {const link = document.createElement('a')link.href = pdfUrllink.setAttribute('download', fileName)document.body.appendChild(link)link.click()window.URL.revokeObjectURL(pdfUrl) // 释放URL 对象} else {// IE 浏览器兼容方法window.navigator.msSaveBlob(blob, fileName)}})}

测试结果：还行，基本实现了页面下载的功能
Excel文件导入
5. 文件读取配置

本配置基于泛型的方式编写，可扩展性较强。

/*** @author Mr.Horse* @version 1.0* @description: EasyExcel文件读取配置(不能让spring管理)* @date 2021/4/27 13:24*/public class MyExcelImportConfig<T> extends AnalysisEventListener<T> {private static Logger logger = LoggerFactory.getLogger(MyExcelImportConfig.class);/*** 每次读取的最大数据条数*/private static final int MAX_BATCH_COUNT = 10;/*** 泛型bean属性*/private T dynamicService;/*** 可接收任何参数的泛型List集合*/List<T> list = new ArrayList<>();/*** 构造函数注入bean(根据传入的bean动态注入)** @param dynamicService*/public MyExcelImportConfig(T dynamicService) {this.dynamicService = dynamicService;}/*** 解析每条数据都进行调用** @param data* @param context*/@Overridepublic void invoke(T data, AnalysisContext context) {logger.info(" ==> 解析一条数据: {}", JacksonUtils.objToString(data));list.add(data);if (list.size() > MAX_BATCH_COUNT) {// 保存数据saveData();// 清空listlist.clear();}}/*** 所有数据解析完成后,会来调用一次* 作用: 避免最后集合中小于 MAX_BATCH_COUNT 条的数据没有被保存** @param context*/@Overridepublic 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*/@Overridepublic 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());}}}

6. 读取测试

    @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("文件导入成功");}

7. 附上自定义属性转换器

   转换器的属性内容转换，需要根据自己的实际业务需求而定，这里仅作为简单示例

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