SpringBoot整合EasyExcel實(shí)現(xiàn)文件導(dǎo)入導(dǎo)出

更新時(shí)間：2021年05月03日 09:11:43 作者：JackHorse

這篇文章主要介紹了SpringBoot整合EasyExcel實(shí)現(xiàn)文件導(dǎo)入導(dǎo)出的方法，幫助大家更好的理解和學(xué)習(xí)使用SpringBoot，感興趣的朋友可以了解下

準(zhǔn)備工作

1. 引入pom依賴(lài)

        <!--easyExcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
        </dependency>

2. 實(shí)現(xiàn)功能

結(jié)合Vue前端，實(shí)現(xiàn)瀏覽器頁(yè)面直接導(dǎo)出日志文件
實(shí)現(xiàn)文件的導(dǎo)入

Excel文件下載

3. 日志實(shí)體類(lèi)

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

/**
 * <p>
 * 操作日志信息
 * </p>
 *
 * @author horse
 * @since 2020-09-08
 * 注意: 實(shí)體類(lèi)中如果使用@Accessory（chain=true）,那么導(dǎo)入的數(shù)據(jù)無(wú)法填充到實(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({"操作日志", "操作類(lèi)型"})
    @ApiModelProperty(value = "操作類(lèi)型")
    private String operType;

    @ExcelProperty({"操作日志", "操作描述"})
    @ApiModelProperty(value = "操作描述")
    private String operDesc;

    @ExcelProperty({"操作日志", "操作員ID"})
    @ApiModelProperty(value = "操作員ID")
    private String operUserId;

    @ExcelProperty({"操作日志", "操作員名稱(chēng)"})
    @ApiModelProperty(value = "操作員名稱(chēng)")
    private String operUserName;

    @ExcelProperty({"操作日志", "操作方法"})
    @ApiModelProperty(value = "操作方法")
    private String operMethod;

    @ExcelProperty({"操作日志", "請(qǐng)求方法"})
    @ApiModelProperty(value = "請(qǐng)求方法")
    private String operRequWay;

    @ExcelProperty(value = {"操作日志", "請(qǐng)求耗時(shí)：?jiǎn)挝?ms"}, converter = CustomRequestTimeConverter.class)
    @ApiModelProperty(value = "請(qǐng)求耗時(shí)：?jiǎn)挝?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)出操作日志,寫(xiě)出到響應(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)出操作日志(可以考慮分頁(yè)導(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查詢(xún)信息,否則查詢(xún)?nèi)?
            if (!CollectionUtils.isEmpty(logIds)) {
                operationalLogs = this.listByIds(logIds);
            } else {
                operationalLogs = this.list(queryWrapper);
            }
            outputStream = response.getOutputStream();

            // 獲取單元格樣式
            HorizontalCellStyleStrategy strategy = MyCellStyleStrategy.getHorizontalCellStyleStrategy();

            // 寫(xiě)入響應(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)出策略簡(jiǎn)單如下：

/**
 * @author Mr.Horse
 * @version 1.0
 * @description: 單元格樣式策略
 * @date 2021/4/30 8:43
 */

public class MyCellStyleStrategy {

    /**
     * 設(shè)置單元格樣式(僅用于測(cè)試)
     *
     * @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 不然無(wú)法顯示背景顏色.頭默認(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)出攔截器簡(jiǎn)單如下：

/**
 * @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)建之后(沒(méi)有寫(xiě)入值)
     *
     * @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) {

    }

    /**
     * 單元格處理后(已寫(xiě)入值): 設(shè)置第一行第一列的頭超鏈接到EasyExcel的官網(wǎng)(本系統(tǒng)的導(dǎo)出的excel 0,1兩行都是頭,所以只設(shè)置第一行的超鏈接)
     * 這里再進(jìn)行攔截的單元格樣式設(shè)置的話(huà),前面該樣式將全部失效
     *
     * @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)出按鈕，在瀏覽器頁(yè)面進(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類(lèi)型可以設(shè)置為文本類(lèi)型，這里是新版excel類(lèi)型
        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)
        }
      })
    }

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

Excel文件導(dǎo)入

5. 文件讀取配置

本配置基于泛型的方式編寫(xiě)，可擴(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();
            // 清空l(shuí)ist
            list.clear();
        }
    }

    /**
     * 所有數(shù)據(jù)解析完成后,會(huì)來(lái)調(diào)用一次
     * 作用: 避免最后集合中小于 MAX_BATCH_COUNT 條的數(shù)據(jù)沒(méi)有被保存
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        logger.info(" ==> 數(shù)據(jù)解析完成 <==");
    }

    /**
     * 保存數(shù)據(jù): 正式應(yīng)該插入數(shù)據(jù)庫(kù),這里用于測(cè)試
     */
    private void saveData() {
        logger.info(" ==> 數(shù)據(jù)保存開(kāi)始: {}", 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. 讀取測(cè)試

    @ApiOperation(value = "數(shù)據(jù)導(dǎo)入測(cè)試", notes = "操作日志導(dǎo)入測(cè)試[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)換器的屬性?xún)?nèi)容轉(zhuǎn)換，需要根據(jù)自己的實(shí)際業(yè)務(wù)需求而定，這里僅作為簡(jiǎn)單示例

/**
 * @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ù)類(lèi)型
     *
     * @return
     */
    @Override
    public Class<Long> supportJavaTypeKey() {
        return Long.class;
    }

    /**
     * 寫(xiě)入數(shù)據(jù)時(shí): excel內(nèi)部的數(shù)據(jù)類(lèi)型,因?yàn)檎?qǐng)求耗時(shí)是long類(lèi)型,對(duì)應(yīng)excel是NUMBER類(lèi)型,但是加上"ms后對(duì)應(yīng)的是STRING類(lèi)型"
     *
     * @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類(lèi)型
        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ǎn)單使用，到此結(jié)束，打完收功。

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

您可能感興趣的文章: