準(zhǔn)備工作

注意：點(diǎn)擊查看官網(wǎng)Demo

1. 引入pom依賴

<!--easyExcel--><dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId></dependency>2. 實(shí)現(xiàn)功能 結(jié)合Vue前端，實(shí)現(xiàn)瀏覽器頁面直接導(dǎo)出日志文件 實(shí)現(xiàn)文件的導(dǎo)入 Excel文件下載3. 日志實(shí)體類

實(shí)體類里有自定義轉(zhuǎn)換器：用于Java類型數(shù)據(jù)和Excel類型數(shù)據(jù)的轉(zhuǎn)換，非常使用。結(jié)合注解，可以非常方便的進(jìn)行Excel文件導(dǎo)出。

/** * <p> * 操作日志信息 * </p> * * @author horse * @since 2020-09-08 * 注意: 實(shí)體類中如果使用@Accessory（chain=true）,那么導(dǎo)入的數(shù)據(jù)無法填充到實(shí)例中,導(dǎo)出數(shù)據(jù)不受影響 */@Data@EqualsAndHashCode(callSuper = false)@TableName('tb_operational_log')@ApiModel(value = 'OperationalLog對(duì)象', 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({'操作日志', '請(qǐng)求方法'}) @ApiModelProperty(value = '請(qǐng)求方法') private String operRequWay; @ExcelProperty(value = {'操作日志', '請(qǐng)求耗時(shí)：單位-ms'}, converter = CustomRequestTimeConverter.class) @ApiModelProperty(value = '請(qǐng)求耗時(shí)：單位-ms') private Long operRequTime; @ExcelProperty({'操作日志', '請(qǐng)求參數(shù)'}) @ApiModelProperty(value = '請(qǐng)求參數(shù)') private String operRequParams; @ExcelProperty({'操作日志', '請(qǐng)求Body'}) @ApiModelProperty(value = '請(qǐng)求Body') private String operRequBody; @ExcelProperty({'操作日志', '請(qǐng)求IP'}) @ApiModelProperty(value = '請(qǐng)求IP') private String operRequIp; @ExcelProperty({'操作日志', '請(qǐng)求URL'}) @ApiModelProperty(value = '請(qǐng)求URL') private String operRequUrl; @ExcelProperty(value = {'操作日志', '日志標(biāo)識(shí)'}, converter = CustomLogFlagConverter.class) @ApiModelProperty(value = '日志標(biāo)識(shí): 1-admin,0-portal') private Boolean logFlag; @ExcelProperty({'操作日志', '操作狀態(tài)'}) @ApiModelProperty(value = '操作狀態(tài):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 = {'操作日志', '創(chuàng)建時(shí)間'}, converter = CustomTimeFormatConverter.class) @ApiModelProperty(value = '創(chuàng)建時(shí)間') private Date gmtCreate;}4. 接口和具體實(shí)現(xiàn)

4.1 接口

@OperatingLog(operType = BlogConstants.EXPORT, operDesc = '導(dǎo)出操作日志,寫出到響應(yīng)流中') @ApiOperation(value = '導(dǎo)出操作日志', hidden = true) @PostMapping('/oper/export') public void operLogExport(@RequestBody List<String> logIds, HttpServletResponse response) {operationalLogService.operLogExport(logIds, response); }

4.2 具體實(shí)現(xiàn)

自定義導(dǎo)出策略HorizontalCellStyleStrategy 自定義導(dǎo)出攔截器CellWriteHandler，更加精確的自定義導(dǎo)出策略

/** * 導(dǎo)出操作日志(可以考慮分頁導(dǎo)出) * * @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查詢信息,否則查詢?nèi)? if (!CollectionUtils.isEmpty(logIds)) {operationalLogs = this.listByIds(logIds); } else {operationalLogs = this.list(queryWrapper); } outputStream = response.getOutputStream(); // 獲取單元格樣式 HorizontalCellStyleStrategy strategy = MyCellStyleStrategy.getHorizontalCellStyleStrategy(); // 寫入響應(yīng)輸出流數(shù)據(jù) EasyExcel.write(outputStream, OperationalLog.class).excelType(ExcelTypeEnum.XLSX).sheet('操作信息日志') // .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 自適應(yīng)列寬(不是很適應(yīng),效果并不佳) .registerWriteHandler(strategy) // 注冊(cè)上面設(shè)置的格式策略 .registerWriteHandler(new CustomCellWriteHandler()) // 設(shè)置自定義格式策略 .doWrite(operationalLogs);} catch (Exception e) { log.error(ExceptionUtils.getMessage(e)); throw new BlogException(ResultCodeEnum.EXCEL_DATA_EXPORT_ERROR);} finally { IoUtil.close(outputStream);} }

自定義導(dǎo)出策略簡單如下：

/** * @author Mr.Horse * @version 1.0 * @description: 單元格樣式策略 * @date 2021/4/30 8:43 */public class MyCellStyleStrategy { /** * 設(shè)置單元格樣式(僅用于測試) * * @return 樣式策略 */ public static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {// 表頭策略WriteCellStyle headerCellStyle = new WriteCellStyle();// 表頭水平對(duì)齊居中headerCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 背景色headerCellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());WriteFont headerFont = new WriteFont();headerFont.setFontHeightInPoints((short) 14);headerCellStyle.setWriteFont(headerFont);// 自動(dòng)換行headerCellStyle.setWrapped(Boolean.FALSE);// 內(nèi)容策略WriteCellStyle contentCellStyle = new WriteCellStyle();// 設(shè)置數(shù)據(jù)允許的數(shù)據(jù)格式,這里49代表所有可以都允許設(shè)置contentCellStyle.setDataFormat((short) 49);// 設(shè)置背景色: 需要指定 FillPatternType 為FillPatternType.SOLID_FOREGROUND 不然無法顯示背景顏色.頭默認(rèn)了 FillPatternType所以可以不指定contentCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);contentCellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());// 設(shè)置內(nèi)容靠左對(duì)齊contentCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);// 設(shè)置字體WriteFont contentFont = new WriteFont();contentFont.setFontHeightInPoints((short) 12);contentCellStyle.setWriteFont(contentFont);// 設(shè)置自動(dòng)換行contentCellStyle.setWrapped(Boolean.FALSE);// 設(shè)置邊框樣式和顏色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); }}

自定義導(dǎo)出攔截器簡單如下：

/** * @author Mr.Horse * @version 1.0 * @description 實(shí)現(xiàn)CellWriteHandler接口, 實(shí)現(xiàn)對(duì)單元格樣式的精確控制 * @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) { } /** * 單元格創(chuàng)建之后(沒有寫入值) * * @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) { } /** * 單元格處理后(已寫入值): 設(shè)置第一行第一列的頭超鏈接到EasyExcel的官網(wǎng)(本系統(tǒng)的導(dǎo)出的excel 0,1兩行都是頭,所以只設(shè)置第一行的超鏈接) * 這里再進(jìn)行攔截的單元格樣式設(shè)置的話,前面該樣式將全部失效 * * @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) {// 設(shè)置超鏈接if (isHead && cell.getRowIndex() == 0 && cell.getColumnIndex() == 0) { logger.info(' ==> 第{}行,第{}列超鏈接設(shè)置完成', 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);}// 精確設(shè)置單元格格式boolean bool = isHead && cell.getRowIndex() == 1 &&(cell.getStringCellValue().equals('請(qǐng)求參數(shù)') || cell.getStringCellValue().equals('請(qǐng)求Body'));if (bool) { logger.info('第{}行，第{}列單元格樣式設(shè)置完成。', 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 前端請(qǐng)求

前端在基于Vue+Element的基礎(chǔ)上實(shí)現(xiàn)了點(diǎn)擊導(dǎo)出按鈕，在瀏覽器頁面進(jìn)行下載。

// 批量導(dǎo)出 batchExport() { // 遍歷獲取id集合列表 const logIds = [] this.multipleSelection.forEach(item => {logIds.push(item.id) }) // 請(qǐng)求后端接口 axios({url: this.BASE_API + ’/admin/blog/log/oper/export’,method: ’post’,data: logIds,responseType: ’arraybuffer’,headers: { ’token’: getToken() } }).then(response => {// type類型可以設(shè)置為文本類型，這里是新版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操作日志’ // 下載文件的名字// 對(duì)于<a>標(biāo)簽，只有 Firefox 和 Chrome（內(nèi)核）支持 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 對(duì)象} else { // IE 瀏覽器兼容方法 window.navigator.msSaveBlob(blob, fileName)} }) }

測試結(jié)果：還行，基本實(shí)現(xiàn)了頁面下載的功能

Excel文件導(dǎo)入5. 文件讀取配置

本配置基于泛型的方式編寫，可擴(kuò)展性較強(qiáng)。

/** * @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); /** * 每次讀取的最大數(shù)據(jù)條數(shù) */ private static final int MAX_BATCH_COUNT = 10; /** * 泛型bean屬性 */ private T dynamicService; /** * 可接收任何參數(shù)的泛型List集合 */ List<T> list = new ArrayList<>(); /** * 構(gòu)造函數(shù)注入bean(根據(jù)傳入的bean動(dòng)態(tài)注入) * * @param dynamicService */ public MyExcelImportConfig(T dynamicService) {this.dynamicService = dynamicService; } /** * 解析每條數(shù)據(jù)都進(jìn)行調(diào)用 * * @param data * @param context */ @Override public void invoke(T data, AnalysisContext context) {logger.info(' ==> 解析一條數(shù)據(jù): {}', JacksonUtils.objToString(data));list.add(data);if (list.size() > MAX_BATCH_COUNT) { // 保存數(shù)據(jù) saveData(); // 清空list list.clear();} } /** * 所有數(shù)據(jù)解析完成后,會(huì)來調(diào)用一次 * 作用: 避免最后集合中小于 MAX_BATCH_COUNT 條的數(shù)據(jù)沒有被保存 * * @param context */ @Override public void doAfterAllAnalysed(AnalysisContext context) {saveData();logger.info(' ==> 數(shù)據(jù)解析完成 <=='); } /** * 保存數(shù)據(jù): 正式應(yīng)該插入數(shù)據(jù)庫,這里用于測試 */ private void saveData() {logger.info(' ==> 數(shù)據(jù)保存開始: {}', list.size());list.forEach(System.out::println);logger.info(' ==> 數(shù)據(jù)保存結(jié)束 <=='); } /** * 在轉(zhuǎn)換異常 獲取其他異常下會(huì)調(diào)用本接口。我們?nèi)绻蹲讲⑹謩?dòng)拋出異常則停止讀取。如果這里不拋出異常則 繼續(xù)讀取下一行。 * * @param exception * @param context * @throws Exception */ @Override public void onException(Exception exception, AnalysisContext context) throws Exception {logger.error(' ==> 數(shù)據(jù)解析失敗,但是繼續(xù)讀取下一行:{}', exception.getMessage());// 如果是某一個(gè)單元格的轉(zhuǎn)換異常 能獲取到具體行號(hào)if (exception instanceof ExcelDataConvertException) { ExcelDataConvertException convertException = (ExcelDataConvertException) exception; logger.error('第{}行，第{}列數(shù)據(jù)解析異常', convertException.getRowIndex(), convertException.getColumnIndex());} }}6. 讀取測試

@ApiOperation(value = '數(shù)據(jù)導(dǎo)入測試', notes = '操作日志導(dǎo)入測試[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('文件導(dǎo)入成功'); }7. 附上自定義屬性轉(zhuǎn)換器

轉(zhuǎn)換器的屬性內(nèi)容轉(zhuǎn)換，需要根據(jù)自己的實(shí)際業(yè)務(wù)需求而定，這里僅作為簡單示例

/** * @author Mr.Horse * @version 1.0 * @description: 自定義excel轉(zhuǎn)換器: 將操作日志的請(qǐng)求耗時(shí)加上單位 'ms' * @date 2021/4/27 10:25 */public class CustomRequestTimeConverter implements Converter<Long> { /** * 讀取數(shù)據(jù)時(shí): 屬性對(duì)應(yīng)的java數(shù)據(jù)類型 * * @return */ @Override public Class<Long> supportJavaTypeKey() {return Long.class; } /** * 寫入數(shù)據(jù)時(shí): excel內(nèi)部的數(shù)據(jù)類型,因?yàn)檎?qǐng)求耗時(shí)是long類型,對(duì)應(yīng)excel是NUMBER類型,但是加上'ms后對(duì)應(yīng)的是STRING類型' * * @return */ @Override public CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING; } /** * 讀取回調(diào) * * @param cellData * @param contentProperty * @param globalConfiguration * @return * @throws Exception */ @Override public Long convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {// 截取字符串: 'ms',轉(zhuǎn)換為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')); }}

格式化時(shí)間

/** * @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簡單使用，到此結(jié)束，打完收功。

以上就是SpringBoot整合EasyExcel實(shí)現(xiàn)文件導(dǎo)入導(dǎo)出的詳細(xì)內(nèi)容，更多關(guān)于SpringBoot整合EasyExcel的資料請(qǐng)關(guān)注好吧啦網(wǎng)其它相關(guān)文章！