EasyExcel导入excel的

前言

        本文大部分参考借鉴https://www.yuque.com/easyexcel/doc/read 

 一、导入jar

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

 二、监听器

1.先实现一个保存到数据库父类（后面监听器调用），可以是dao层，也可以service层，看情况而定

ExcelDao

package com.example.demo.common;

import java.util.List;

/**
 * @ProjectName: mydemo
 * @PackageName: com.example.demo.common
 * @ClassName: ExcelDao
 * @User: JiaWei
 * @Date: 2021/08/06 15:17
 * @Week: 星期五
 * @Description: Excel导入数据库dao层处理
 */
public class ExcelDao<T> {

    //需要用到Excel导入数据库时，重写此方法
    public int save(List<T> list) {
        System.out.println("父类执行");
        return 0;
    }

}

2.监听器实现

package com.example.demo.utils;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.example.demo.common.ExcelDao;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.List;

/**
 * @ProjectName: mydemo
 * @PackageName: com.example.demo.utils
 * @ClassName: ExcelListener
 * @User: JiaWei
 * @Date: 2021/08/06 14:56
 * @Week: 星期五
 * @Description: Excel读取监听器
 */
//ExcelListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class ExcelListener<T> extends AnalysisEventListener<T> {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelListener.class);

    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;

    List<T> list = new ArrayList<>();

    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     * 数据库交互dao层
     */
    private ExcelDao<T> excelDao;

    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param excelDao
     */
    public ExcelListener(ExcelDao excelDao) {
        this.excelDao = excelDao;
    }

    /**
     * 一行行读取表格内容
     * 这个每一条数据解析都会来调用
     *
     * @param t       one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(T t, AnalysisContext context) {
        LOGGER.info("解析到一条数据:{}", t);
        list.add(t);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }

    /**
     * 读取完成后的操作
     *
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据解析完成！");
    }

    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        //调用保存到数据库方法
        excelDao.save(list);
        LOGGER.info("存储数据库成功！");
    }

}

 三、Excel工具类

package com.example.demo.utils;

import com.alibaba.excel.EasyExcel;
import com.example.demo.common.ExcelDao;

import java.io.InputStream;
import java.util.List;

/**
 * @ProjectName: mydemo
 * @PackageName: com.example.demo.utils
 * @ClassName: Excel
 * @User: JiaWei
 * @Date: 2021/08/06 9:54
 * @Week: 星期五
 * @Description: Excel工具类
 */
public class ExcelUtil<T> {

    private Class<T> clazz;

    //监听器
    private ExcelListener<T> excelListener;// = new ExcelListener<T>(excelTestDao);

    public ExcelUtil(Class<T> clazz) {
        this.clazz = clazz;
    }

    public ExcelUtil(Class<T> clazz, ExcelDao excelDao) {
        this.clazz = clazz;
        this.excelListener = new ExcelListener(excelDao);
    }

    public List<T> importEasyExcel(InputStream is) throws Exception {
        return EasyExcel.read(is, excelListener).head(clazz).sheet().doReadSync();
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单（EasyExcel）
     *
     * @param list      导出数据集合
     * @param sheetName 工作表的名称
     * @return 结果
     */
    public String exportEasyExcel(List<T> list, String sheetName) {
        String fileName = "D:/excel_test/" + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
        EasyExcel.write(fileName, clazz).sheet(sheetName).doWrite(list);
        return "OK";
    }

}

四、调用读Excel方法，测试类

1.创建一个测试实体类

package com.example.demo.pojo;

import com.alibaba.excel.annotation.ExcelProperty;

/**
 * @ProjectName: mydemo
 * @PackageName: com.example.demo.pojo
 * @ClassName: LogSys
 * @User: JiaWei
 * @Date: 2021/08/06 9:50
 * @Week: 星期五
 * @Description:
 */
public class LogSys {

    private static final long serialVersionUID = 1L;

    public LogSys() {
    }

    @ExcelProperty(value = "操作序号")
    private int id;

    @ExcelProperty(value = "状态码")
    private String code;

    @ExcelProperty(value = "信息描述")
    private String msg;

    public LogSys(int id, String code, String msg) {
        this.id = id;
        this.code = code;
        this.msg = msg;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getMsg() {
        return msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }

    @Override
    public String toString() {
        return "LogSys{" +
                "id=" + id +
                ", code='" + code + '\'' +
                ", msg='" + msg + '\'' +
                '}';
    }
}

2.数据库交互（继承前面ExcelDao）

ExcelTestDao

package com.example.demo.test.dao;

import com.example.demo.common.ExcelDao;
import com.example.demo.pojo.LogSys;

import java.util.List;

/**
 * @ProjectName: mydemo
 * @PackageName: com.example.demo.test.dao
 * @ClassName: ExcelTestDao
 * @User: JiaWei
 * @Date: 2021/08/06 15:28
 * @Week: 星期五
 * @Description: 数据库交互
 */
public class ExcelTestDao extends ExcelDao<LogSys> {

    @Override
    public int save(List<LogSys> list) {
        //mybatis情况下，自己实现个批量插入方法
        int count = batchInsert(list);
        return count;
    }

    private int batchInsert(List<LogSys> list) {
        System.out.println("重写父类方法，将数据保存到数据库，批量插入数据成功");
        return 666;
    }

}

3.最后调用测试

package com.example.demo.test;

import com.example.demo.pojo.LogSys;
import com.example.demo.test.dao.ExcelTestDao;
import com.example.demo.utils.ExcelUtil;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @ProjectName: mydemo
 * @PackageName: com.example.demo.test
 * @ClassName: ExcelImportController
 * @User: JiaWei
 * @Date: 2021/08/06 9:59
 * @Week: 星期五
 * @Description:
 */
@RestController
@RequestMapping("/file")
public class ExcelImportController {

    @RequestMapping("/uploadExcel")
    public List<LogSys> uploadExcel(@RequestParam("file") MultipartFile file, HttpServletRequest request) {
        //spring中，使用@Autowired注入
        ExcelTestDao excelTestDao = new ExcelTestDao();
        try {
            InputStream is = file.getInputStream();
            ExcelUtil excelUtil = new ExcelUtil(LogSys.class, excelTestDao);
            List<LogSys> data = excelUtil.importEasyExcel(is);
            return data;
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @RequestMapping("/exportExcel")
    public void exportExcel(String sheetName) {
        List<LogSys> list = new ArrayList<LogSys>() {{
            add(new LogSys(1, "10", "测试1"));
            add(new LogSys(2, "20", "测试2"));
            add(new LogSys(3, "30", "测试3"));
            add(new LogSys(4, "40", "测试4"));
            add(new LogSys(5, "50", "测试5"));
            add(new LogSys(6, "60", "测试6"));
            add(new LogSys(7, "70", "测试7"));
            add(new LogSys(8, "80", "测试8"));
        }};
        ExcelUtil excelUtil = new ExcelUtil(LogSys.class);
        excelUtil.exportEasyExcel(list, sheetName);
    }

}