SpringBoot整合EasyExcel實現文件導入導出

準備工作

注意：點擊查看官網Demo

1. 引入pom依賴

<!--easyExcel--><dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId></dependency>2. 實現功能 結合Vue前端，實現瀏覽器頁面直接導出日志文件 實現文件的導入 Excel文件下載3. 日志實體類

實體類里有自定義轉換器：用于Java類型數據和Excel類型數據的轉換，非常使用。結合注解，可以非常方便的進行Excel文件導出。

/** * <p> * 操作日志信息 * </p> * * @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;}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 */ @Override public 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); @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<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 = pdfUrl link.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 */ @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());} }}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 */ @Override public Class<Long> 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<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> { @Override public Class<Date> 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<Date> convertToExcelData(Date value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {return new CellData<>(DateUtil.format(value, DatePattern.NORM_DATETIME_PATTERN)); }}

EasyExcel簡單使用，到此結束，打完收功。

以上就是SpringBoot整合EasyExcel實現文件導入導出的詳細內容，更多關于SpringBoot整合EasyExcel的資料請關注好吧啦網其它相關文章！