使用EasyExcel实现数据导出成Excel&Excel数据导入数据库

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.6</version>
</dependency>

<dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.1</version>
        </dependency>

package com.zb.config;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.zb.entity.No2;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.function.Function;

/**
 * Excel解析批量存储
 * @param <T>
 */
@Slf4j
/**
 * 下面一行里面有一个No2,这是个实体类
 * No2的实体类是: 是数据库的,意思是Excel导入时,对应那个数据库实体类
 */

public class ExcelDataListener<T extends No2,E> extends AnalysisEventListener<E> {
    private Class<T> clazz;
    /**
     * 每隔500条存储数据库
     */
    private static final int BATCH_COUNT = 500;
    //存储的具体操作
    private Function<Collection<T>,Boolean> saveFunc;
    //批量存储的数据
    private List<T> list = new ArrayList<>();

    /**
     * 通过 AnalysisContext 对象还可以获取当前 sheet，当前行等数据
     */
    @Override
    public void invoke(E e, AnalysisContext analysisContext) {
        try {
           T t = clazz.getDeclaredConstructor(null).newInstance();
            BeanUtils.copyProperties(e,t);
            list.add(t);
            if (list.size() >= BATCH_COUNT) {
                this.saveFunc.apply(list);
                // 存储完成清理 list
                list.clear();
            }
        } catch (Exception ex) {
            log.error("出现错误：",ex);
        }


    }

    /**
     * 读取完之后的操作
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        this.saveFunc.apply(list);
    }

    /**
     *
     * @param saveFunc 持久化数据到数据库的方法
     * @param clazz 对应的实体类类型
     */
    public ExcelDataListener(Function<Collection<T>,Boolean> saveFunc,Class<T> clazz){
        this.saveFunc = saveFunc;
        this.clazz = clazz;
    }
}

@Data
@ContentRowHeight(15)
@HeadRowHeight(20)
@ColumnWidth(25)
public class BillExportDataVO {
    @DateTimeFormat("yyyy年MM月dd日")
    @ExcelProperty(value = "分账日期",index = 0)
    private Date date;
    @ColumnWidth(30)
    @ExcelProperty(value = "分账点位",index = 1)
    private String nodeName;
    @ExcelProperty(value = "订单数",index = 2)
    private Integer orderCount;
    @ExcelProperty(value = "分账金额",index = 3)
    private Integer amount;
}

 @RequestMapping("/excel")
    public  void excel(HttpServletResponse response){
        //查询所有数据
        List<No2> testex = no.list();

        //设置头部
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        //设置导出的表格名字叫: test.xlsx
        response.setHeader("Content-disposition", "attachment;filename=test.xlsx");
        try {
            //No2.class: 数据库实体类
            //分账数据: 表格底部的页名
            //testex: 要导出的数据来自那个变量
            EasyExcel.write(response.getOutputStream(), No2.class).sheet("分账数据").doWrite(testex);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @PostMapping("/upload")
    public String upload(@RequestParam("fileName") MultipartFile file) throws IOException {
        Function<List<No2>,Boolean> insertFunc = list-> no.saveBatch(list);
        ExcelDataListener<No2, up> dataListener = new ExcelDataListener(insertFunc,No2.class);
        EasyExcel.read(file.getInputStream(), No2.class,dataListener).sheet().doRead();
        return "上传成功";
    }