Springboot integrates easyexcel to implement Excel import and export of arbitrary tables on an interface

<!--EasyExcelRelated dependencies-->
<dependency>
    <groupId></groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.5</version>
</dependency>

@Data
@NoArgsConstructor
@AllArgsConstructor
@TableName("stu_info")
@ApiModel("Student Information")
//@ExcelIgnoreUnannotated fields without annotation are not convertedpublic class StuInfo {

    private static final long serialVersionUID = 1L;
​
    /**
      * Name
      */
    // Set the font, here italics are used//    @ContentFontStyle(italic = )
    // Annotation for setting the column width. There is only one parameter value in the annotation. The unit of value is the character length, and the maximum number of 255 characters can be set.    @ColumnWidth(10)
    // @ExcelProperty Annotation has three parameters: value, index, and converter, respectively representing the table name, column number, and data conversion method.    @ApiModelProperty("Name")
    @ExcelProperty(value = "Name",order = 0)
    @ExportHeader(value = "Name",index = 1)
    private String name;
​
    /**
      * age
      */
// @ExcelIgnore does not convert this field to Excel    @ExcelProperty(value = "age",order = 1)
    @ApiModelProperty("age")
    @ExportHeader(value = "age",index = 2)
    private Integer age;
​
    /**
      * height
      */
    //Custom format-number of bits//    @NumberFormat("#.##%")
    @ExcelProperty(value = "height",order = 2)
    @ApiModelProperty("height")
    @ExportHeader(value = "height",index = 4)
    private Double tall;
​
    /**
      * Self-introduction
      */
    @ExcelProperty(value = "Self-introduction",order = 3)
    @ApiModelProperty("Self-introduction")
    @ExportHeader(value = "Self-introduction",index = 3,ignore = true)
    private String selfIntroduce;
​
    /**
      * Picture information
      */
    @ExcelProperty(value = "Picture Information",order = 4)
    @ApiModelProperty("Picture Information")
    @ExportHeader(value = "Picture Information",ignore = true)
    private Blob picture;
​
    /**
      * gender
      */
    @ExcelProperty(value = "gender",order = 5)
    @ApiModelProperty("gender")
    private Integer gender;
​
    /**
      * Enrollment time
      */
    //Custom format-time format    @DateTimeFormat("yyyy-MM-dd HH:mm:ss:")
    @ExcelProperty(value = "Enrollment time",order = 6)
    @ApiModelProperty("Enrollment time")
    private String intake;
​
    /**
      * Date of birth
      */
    @ExcelProperty(value = "Date of Birth",order = 7)
    @ApiModelProperty("Date of Birth")
    private String birthday;
}

@Slf4j
public class UploadDataListener<T> implements ReadListener<T> {
​
    /**
      * Every 5 databases can be stored, 100 can be stored in actual use, and then clean the list to facilitate memory recycling
      */
    private static final int BATCH_COUNT = 100;
​
    /**
      * Cache data
      */
    private List<T> cachedDataList = (BATCH_COUNT);
​
    /**
      * Predicate is used to filter data
      */
    private Predicate<T> predicate;
​
    /**
      * Call the persistence layer to save in batches
      */
    private Consumer<Collection<T>> consumer;
​
    public UploadDataListener(Predicate<T> predicate, Consumer<Collection<T>> consumer) {
         = predicate;
         = consumer;
    }
​
    public UploadDataListener(Consumer<Collection<T>> consumer) {
         = consumer;
    }
​
    /**
      * If spring is used, please use this constructor.  Every time you create a Listener, you need to pass the spring managed class in.
      *
      * @param demoDAO
      */
​
    /**
      * Every data analysis will be called
      *
      * @param data one row value. Is is same as {@link AnalysisContext#readRowHolder()}
      * @param context
      */
    @Override
    public void invoke(T data, AnalysisContext context) {
​
        if (predicate != null && !(data)) {
            return;
        }
        (data);
​
        // It has reached BATCH_COUNT, and it is necessary to store the database once to prevent tens of thousands of pieces of data from being in memory, making it easy to OOM        if (() >= BATCH_COUNT) {
            try {
                // Execute specific consumption logic                (cachedDataList);
​
            } catch (Exception e) {
​
                ("Failed to upload data!data={}", cachedDataList);
                throw new BizException("Import failed");
            }
            // Storage is cleaned up list            cachedDataList = (BATCH_COUNT);
        }
    }
​
    /**
      * All data parsing will be called
      *
      * @param context
      */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
​
        // You must also save the data here to ensure that the last remaining data is also stored in the database        if ((cachedDataList)) {
​
            try {
                // Execute specific consumption logic                (cachedDataList);
                ("All data parsing is completed!");
            } catch (Exception e) {
​
                ("Failed to upload data!data={}", cachedDataList);
​
                // Throw a custom prompt message                if (e instanceof BizException) {
                    throw e;
                }
​
                throw new BizException("Import failed");
            }
        }
    }
}

@ApiOperation("Only one readListener is needed to solve all the problems")
    @PostMapping("/update")
    @ResponseBody
    public R<String> aListener4AllExcel(MultipartFile file) throws IOException {
        try {
            ((),
                            ,
                            new UploadDataListener<StuInfo>(
                                    list -> {
                                        // Verify data//                                        (list);
                                        // Save...                                        //It is best to write one by hand, and do not use the new logic added by mybatis-plus                                        (list);
                                        ("fromExcelImport data in total {} OK ", ());
                                    }))
                    .sheet()
                    .doRead();
        } catch (IOException e) {
​
            ("Import failed", e);
            throw new BizException("Import failed");
        }
        return ("SUCCESS");
    }

@ApiOperation("Only one readListener is needed to solve all the problems")
    @PostMapping("/listenMapDara")
    @ResponseBody
    public R<String> listenMapDara(@ApiParam(value = "Table encoding", required = true)
                                   @NotBlank(message = "Table encoding cannot be empty")
                                   @RequestParam("tableCode") String tableCode,
                                   @ApiParam(value = "Uploaded file", required = true)
                                   @NotNull(message = "Uploading file cannot be empty") MultipartFile file) throws IOException {
        try {
            //Get the fields of this table based on tableCode, which can be used as insert and information in the drama            ((),
                            new NonClazzOrientedListener(
                                    list -> {
                                        // Verify data//                                        (list);
​
                                        // Save...                                        ("fromExcelImport data in total {} OK ", ());
                                    }))
                    .sheet()
                    .doRead();
        } catch (IOException e) {
            ("Import failed", e);
            throw new BizException("Import failed");
        }
        return ("SUCCESS");
    }

@Slf4j
public class NonClazzOrientedListener implements ReadListener<Map<Integer, String>> {
​
    /**
      * Every 5 databases can be stored, 100 can be stored in actual use, and then clean the list to facilitate memory recycling
      */
    private static final int BATCH_COUNT = 100;
​
    private List<List<Object>> rowsList = (BATCH_COUNT);
​
    private List<Object> rowList = new ArrayList<>();
    /**
      * Predicate is used to filter data
      */
    private Predicate<Map<Integer, String>> predicate;
​
    /**
      * Call the persistence layer to save in batches
      */
    private Consumer<List> consumer;
​
    public NonClazzOrientedListener(Predicate<Map<Integer, String>> predicate, Consumer<List> consumer) {
         = predicate;
         = consumer;
    }
​
    public NonClazzOrientedListener(Consumer<List> consumer) {
         = consumer;
    }
​
    /**
      * Add deviceName ID
      */
    private boolean flag = false;
​
    @Override
    public void invoke(Map<Integer, String> row, AnalysisContext analysisContext) {
        (rowsList);
        ();
        ((k, v) -> {
            ("key is {},value is {}", k, v);
            (v == null ? "" : v);
        });
        (rowList);
        if (() > BATCH_COUNT) {
            ("Execute stored programs");
            ("rowsList is {}", rowsList);
            ();
        }
    }
​
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        (rowsList);
        if ((rowsList)) {
            try {
                ("Execute the last program");
                ("rowsList is {}", rowsList);
            } catch (Exception e) {
​
                ("Failed to upload data!data={}", rowsList);
​
                // Throw a custom prompt message                if (e instanceof BizException) {
                    throw e;
                }
​
                throw new BizException("Import failed");
            } finally {
                ();
            }
        }
    }

 /**
      * Here will return to the header line
      *
      * @param headMap
      * @param context
      */
    @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
        //This method will inevitably be performed before reading the data        Map<Integer, String> columMap = (headMap, context);
        //Get the header of this table through data interaction//        Map<String,String> columnList=();
        Map<String, String> columnList = new HashMap();
        ((key, value) -> {
            if ((value)) {
                (key);
            }
        });
        //Filter the data that only exists in the table, so don't worry about the order. You can directly use the filterList data for sorting. You can do a dynamic SQL application based on mybatis​
        ("Parsing to a header data:{}", (columMap));
        // If you want to convert it to Map<Integer,String>        // Solution 1: Don't implements ReadListener but extends AnalysisEventListener        // Scheme 2: Calling (headMap, context) will automatically convert    }

    public <T> void commonExport(String fileName, List<T> data, Class<T> clazz, HttpServletResponse response) throws IOException {
        if ((data)) {
            data = new ArrayList<>();
        }
        //Set the title        fileName = (fileName, "UTF-8");
        ("application/-excel");
        ("utf-8");
        ("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        (()).head(clazz).sheet("sheet1").doWrite(data);
    }

public void exportFreely(String fileName, List<List<Object>> data, List<List<String>> head, HttpServletResponse response) throws IOException {
        if ((data)) {
            data = new ArrayList<>();
        }
        //Set the title        fileName = (fileName, "UTF-8");
        ("application/-excel");
        ("utf-8");
        ("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        (()).head(head).sheet("sheet1").doWrite(data);
    }