SpringBoot integration EasyExcel implementation

The preparatory work

Note: Click on the Demo website

1. Introduce POM dependencies

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

2. Implement functions

1. Combined with the Vue front end, the log file can be exported directly from the browser page
2. Implement file import

Excel file download

3. Log entity class

There are custom converters in entity classes: for converting Java type data to Excel type data, very useful. With annotations, it is very convenient to export Excel files.

/** * <p> * Operation log information * </p> **@author horse
 * @since2020-09-08 * Note: entity class if used@Accessory(chain=true), then the imported data cannot be populated into the instance, and the exported data is not affected */
@Data
@EqualsAndHashCode(callSuper = false)
@TableName("tb_operational_log")
@apiModel (value = "OperationalLog object ", description = "operation log information ")
public class OperationalLog implements Serializable {

    private static final long serialVersionUID = 1L;

    @excelProperty ({" operation log ", "log ID"})
    @apiModelProperty (value = "log ID")
    @TableId(value = "id", type = IdType.ASSIGN_ID)
    private String id;

    @excelProperty ({" operation log ", "operation type "})
    @apiModelProperty (value = "operation type ")
    private String operType;

    @excelProperty ({" Operation log ", "operation description "})
    @apiModelProperty (value = "operation description ")
    private String operDesc;

    @excelProperty ({" operation log ", "operator ID"})
    @apiModelProperty (value = "operator ID")
    private String operUserId;

    @excelProperty ({" operation log ", "operator name "})
    @apiModelProperty (value = "operator name ")
    private String operUserName;

    @excelProperty ({" log ", "operation method "})
    @apiModelProperty (value = "method ")
    private String operMethod;

    @excelProperty ({" operation log ", "request method "})
    @apiModelProperty (value = "request method ")
    private String operRequWay;

    @ ExcelProperty (value = {" operation log ", "request time consuming: unit - ms"}, the converter = CustomRequestTimeConverter. Class)
    @apiModelProperty (value = "Request time: -ms")
    private Long operRequTime;

    @excelProperty ({" operation log ", "request parameters "})
    @apiModelProperty (value = "request parameter ")
    private String operRequParams;

    @excelProperty ({" Operation log ", "request Body"})
    @apiModelProperty (value = "request Body")
    private String operRequBody;

    @excelProperty ({" operation log ", "request IP"})
    @apiModelProperty (value = "request IP")
    private String operRequIp;

    @excelProperty ({" operation log ", "request URL"})
    @apiModelProperty (value = "request URL")
    private String operRequUrl;

    @ ExcelProperty (value = {" operation log ", "log logo"}, the converter = CustomLogFlagConverter. Class)
    @APIModelProperty (value = "Log ID: 1-admin,0-portal")
    private Boolean logFlag;

    @excelProperty ({" operation log ", "operation status "})
    @apiModelProperty (value = "operation status :1- successful,0- failed ")
    @TableField(value = "is_success")
    private Boolean success;

    @ExcelIgnore
    @apiModelProperty (value = "logical delete 1- not deleted, 0- deleted ")
    @TableField(value = "is_deleted")
    @TableLogic(value = "1", delval = "0")
    private Boolean deleted;

    @ ExcelProperty (value = {" operation log ", "creation time"}, the converter = CustomTimeFormatConverter. Class)
    @apiModelProperty (value = "create time ")
    private Date gmtCreate;
}
Copy the code

4. Interface and implementation

4.1 interface

    @operatinglog (operType = BlogConstants.EXPORT, operDesc = "EXPORT operation log to the response stream ")
    @apiOperation (value = "Export operation log ", hidden = true)
    @PostMapping("/oper/export")
    public void operLogExport(@RequestBody List<String> logIds, HttpServletResponse response) {
        operationalLogService.operLogExport(logIds, response);
    }
Copy the code

4.2 Implementation

• Customize export policiesHorizontalCellStyleStrategy
• Custom export interceptorCellWriteHandler, more precise custom export policy

    /** * Export operation logs (consider paging export) **@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);
            // If logIds are not null, query information by ID. Otherwise, query all information
            if(! CollectionUtils.isEmpty(logIds)) { operationalLogs =this.listByIds(logIds);
            } else {
                operationalLogs = this.list(queryWrapper);
            }
            outputStream = response.getOutputStream();

            // Get the cell style
            HorizontalCellStyleStrategy strategy = MyCellStyleStrategy.getHorizontalCellStyleStrategy();

            // Write response output stream data
            EasyExcel.write(outputStream, OperationalLog.class).excelType(ExcelTypeEnum.XLSX).sheet("Operation Information Log")
                    / /. RegisterWriteHandler (new LongestMatchColumnWidthStyleStrategy ()) / / adaptive column width (not very adapt to, the effect is not good)
                    .registerWriteHandler(strategy) // Register the format policy set above
                    .registerWriteHandler(new CustomCellWriteHandler()) // Set a custom format policy
                    .doWrite(operationalLogs);
        } catch (Exception e) {
            log.error(ExceptionUtils.getMessage(e));
            throw new BlogException(ResultCodeEnum.EXCEL_DATA_EXPORT_ERROR);
        } finally{ IoUtil.close(outputStream); }}Copy the code

The custom export policy is as follows:

/ * * *@author Mr.Horse
 * @version 1.0
 * @description: Cell style strategy *@date2021/4/30 why do * /

public class MyCellStyleStrategy {

    /** * Sets the cell style (for testing only) **@returnStyle strategy */
    public static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy(a) {
        // Table header policy
        WriteCellStyle headerCellStyle = new WriteCellStyle();
        // The header is horizontally centered
        headerCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        / / the background color
        headerCellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        WriteFont headerFont = new WriteFont();
        headerFont.setFontHeightInPoints((short) 14);
        headerCellStyle.setWriteFont(headerFont);
        // wrap
        headerCellStyle.setWrapped(Boolean.FALSE);

        // Content strategy
        WriteCellStyle contentCellStyle = new WriteCellStyle();
        // Set the format of data allowed. 49 indicates that all data can be allowed
        contentCellStyle.setDataFormat((short) 49);
        // Set the background color: You need to specify FillPatternType as FillPatternType. Otherwise, the background color cannot be displayed. The header defaults to FillPatternType so you can leave it unspecified
        contentCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        contentCellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
        // Align the content to the left
        contentCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        // Set the font
        WriteFont contentFont = new WriteFont();
        contentFont.setFontHeightInPoints((short) 12);
        contentCellStyle.setWriteFont(contentFont);
        // Set line wrap
        contentCellStyle.setWrapped(Boolean.FALSE);
        // Set the border style and color
        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());

        // Add formatting to the cell style policy
        return newHorizontalCellStyleStrategy(headerCellStyle, contentCellStyle); }}Copy the code

Custom export interceptors are simple as follows:

/ * * *@author Mr.Horse
 * @version 1.0
 * @descriptionImplement CellWriteHandler interface for precise control of cell style *@dateAnd great 2021/4/29 * /
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) {}/** * after the cell is created (no value is written) **@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) {}/** * after cell processing (already written value): set the header hyperlink of the first row and column to the official website of EasyExcel (excel 0,1 is exported by this system, so only set the hyperlink of the first row) *@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)
        {
        // Set the hyperlink
        if (isHead && cell.getRowIndex() == 0 && cell.getColumnIndex() == 0) {
            logger.info("==> Line {}, hyperlink set {} complete", 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);
        }
        // Format the cell exactly
        boolean bool = isHead && cell.getRowIndex() == 1 &&
                (cell.getStringCellValue().equals("Request parameters") || cell.getStringCellValue().equals("Request Body"));
        if (bool) {
            logger.info("Row {}, column {} cell style set.", cell.getRowIndex(), cell.getColumnIndex());
            // Get the workbook
            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); }}}Copy the code

4.3 Front-end Request

In the front end, based on Vue+Element, click the export button and download it in the browser page.

// Batch export
    batchExport() {
      // Iterate over the list of id sets
      const logIds = []
      this.multipleSelection.forEach(item= > {
        logIds.push(item.id)
      })
       // Request the back-end interface
      axios({
        url: this.BASE_API + '/admin/blog/log/oper/export'.method: 'post'.data: logIds,
        responseType: 'arraybuffer'.headers: { 'token': getToken() }
      }).then(response= > {
        // Type can be set to text type, here is the new Excel type
        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 Operation Log ' // Download the file name
        // For  tags, only Firefox and Chrome (kernel) support the Download attribute
        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) // Release the URL object
        } else {
          // Compatible with Internet Explorer
          window.navigator.msSaveBlob(blob, fileName)
        }
      })
    }
Copy the code

Test results: ok, the basic realization of the page download function

Excel file import

5. Configure file reading

This configuration is written based on generics and has strong scalability.

/ * * *@author Mr.Horse
 * @version 1.0
 * @descriptionEasyExcel file reads configuration (cannot be managed by Spring) *@date 2021/4/27 13:24
 */

public class MyExcelImportConfig<T> extends AnalysisEventListener<T> {

    private static Logger logger = LoggerFactory.getLogger(MyExcelImportConfig.class);

    /** * The maximum number of bytes read at a time */
    private static final int MAX_BATCH_COUNT = 10;

    /** * Generic bean properties */
    private T dynamicService;

    /** * a generic List collection that can accept any argument */
    List<T> list = new ArrayList<>();


    /** * Constructors inject beans (dynamically injected based on incoming beans) **@param dynamicService
     */
    public MyExcelImportConfig(T dynamicService) {
        this.dynamicService = dynamicService;
    }

    /** * calls ** to parse each piece of data@param data
     * @param context
     */
    @Override
    public void invoke(T data, AnalysisContext context) {
        logger.info("==> Parse a piece of data: {}", JacksonUtils.objToString(data));
        list.add(data);
        if (list.size() > MAX_BATCH_COUNT) {
            // Save data
            saveData();
            / / to empty the listlist.clear(); }}/** * if MAX_BATCH_COUNT is less than MAX_BATCH_COUNT in the final set, it will be called **@param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        logger.info("==> Data parsing completed <==");
    }

    /** * Save data: it should be formally inserted into the database, which is used for testing */
    private void saveData(a) {
        logger.info("==> Data save begins: {}", list.size());
        list.forEach(System.out::println);
        logger.info("==> End of data saving <==");
    }

    /** * this interface is called when the conversion exception gets another exception. We stop reading if we catch and manually throw an exception. Continue reading the next line if no exception is thrown here. * *@param exception
     * @param context
     * @throws Exception
     */
    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        logger.error("==> Data parsing failed, but continue reading the next line :{}", exception.getMessage());
        // If it is a conversion exception of a cell, the specific line number can be obtained
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException convertException = (ExcelDataConvertException) exception;
            logger.error("{} line, {} column data parsing exception", convertException.getRowIndex(), convertException.getColumnIndex()); }}}Copy the code

6. Read test

    @apiOperation (value = "data import test ", notes =" Operation log import test [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("File imported successfully");
    }
Copy the code

7. Attach custom property converter

The conversion of the property content of the converter depends on your actual business needs, and this is just a simple example

/ * * *@author Mr.Horse
 * @version 1.0
 * @description: Custom Excel converter: Add the operation log request time in units of "ms" *@date2021/4/27 assembling of * /

public class CustomRequestTimeConverter implements Converter<Long> {

    /** * When reading data: the property corresponds to the Java data type **@return* /
    @Override
    public Class<Long> supportJavaTypeKey(a) {
        return Long.class;
    }

    /** * Write data: data type inside Excel, because the request time is long, corresponding to excel is NUMBER, but "ms "corresponds to STRING" **@return* /
    @Override
    public CellDataTypeEnum supportExcelTypeKey(a) {
        return CellDataTypeEnum.STRING;
    }

    /** * read the fetch call **@param cellData
     * @param contentProperty
     * @param globalConfiguration
     * @return
     * @throws Exception
     */
    @Override
    public Long convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        // Take the string "ms" and convert it to 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 {
        // Add string: "ms"
        return new CellData<>(String.valueOf(value).concat("ms")); }}Copy the code

Formatting time

/ * * *@author Mr.Horse
 * @version 1.0
 * @description: {description}
 * @date2021/4/27 14:01 * /

public class CustomTimeFormatConverter implements Converter<Date> {

    @Override
    public Class<Date> supportJavaTypeKey(a) {
        return Date.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey(a) {
        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 newCellData<>(DateUtil.format(value, DatePattern.NORM_DATETIME_PATTERN)); }}Copy the code

EasyExcel simple use, this is the end, finished work.