实现“下载模板”，“导出下载”，“导入上传”功能

1.工具类

package com.sjzl.util;

import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.extension.service.IService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

@Slf4j
@Component
public class ExcelTransfer<T> {

    /**
     * 上传excel 对用实体类不允许使用链式调用注解
     *
     * <p>@Accessors(chain = true) 对应实体类实现equals和hashCode方法会自动过滤与数据库重复的数据
     *
     * @param file 文件
     * @param service 对应实体的service
     * @return 成功与否
     * @throws ClassNotFoundException
     */
    public boolean importExcel(MultipartFile file, IService<T> service,Class<?> aClass)
            throws ClassNotFoundException {

        try {
            EasyExcel.read(file.getInputStream(), aClass, new DataListener<>(service))
                    .sheet()
                    .doRead();
        } catch (IOException e) {
            log.error(e.getMessage(), e);
            return false;
        }
        return true;
    }

    /**
     * 上传excel 对用实体类不允许使用链式调用注解 自定义格式转换
     *
     * <p>@Accessors(chain = true) 对应实体类实现equals和hashCode方法会自动过滤与数据库重复的数据
     *
     * @param file 文件
     * @param service 对应实体的service
     * @param list 实现自定义转换方法
     * @return 成功与否
     * @throws ClassNotFoundException 为找到对应的类
     */
    public boolean importExcel(MultipartFile file, IService<T> service, Class<?> aClass, ConvertList<T> list)
            throws ClassNotFoundException {
        try {
            EasyExcel.read(file.getInputStream(), aClass, new DataListener<>(service, list))
                    .sheet()
                    .doRead();
        } catch (IOException e) {
            log.error(e.getMessage(), e);
            return false;
        }
        return true;
    }

    /**
     * 下载excel
     *
     * @param response http
     * @param list 需要下载的数据
     * @param name 文件名称
     * @param sheet 表名
     */
    public void exportExcel(
            HttpServletResponse response,
            List<T> list,
            String name,
            String sheet,
            IService<T> service)
            throws ClassNotFoundException {
        String className = service.getClass().getName();
        String s = className.substring(21, className.length() - 11);
        Class<?> aClass = Class.forName("com.esb.entity." + s);
        extracted(response, list, name, sheet, aClass);
    }

    /**
     * 下载excel
     *
     * @param response http
     * @param list 需要下载的数据
     * @param name 文件名称
     * @param sheet 表名
     * @param aClass 实体类
     */
    public void exportExcel(
            HttpServletResponse response, List<T> list, String name, String sheet, Class<?> aClass)
            throws ClassNotFoundException {
        extracted(response, list, name, sheet, aClass);
    }

    private void extracted(
            HttpServletResponse response,
            List<T> list,
            String name,
            String sheet,
            Class<?> aClass) {
        try {
            //response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode(name, "UTF-8");
            //response.setHeader("Content-disposition", "attachment;filename="+fileName+";"+"filename*=utf-8''"+fileName);
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), aClass).sheet(sheet).doWrite(list);
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        }
    }

    public void exportExcel2(
            HttpServletResponse response,
            List<T> list,
            String name,
            String sheet,
            IService<T> service)
            throws ClassNotFoundException {
        String className = service.getClass().getName();
        String s = className.substring(21, className.length() - 11);
        Class<?> aClass = Class.forName("com.esb.entity." + s);
        extracted2(response, list, name, sheet, aClass);
    }

    private void extracted2(
            HttpServletResponse response,
            List<T> list,
            String name,
            String sheet,
            Class<?> aClass) {
        try {
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode(name, "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), aClass).sheet(sheet).doWrite(list);
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        }
    }
}

package com.sjzl.util;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.extension.service.IService;
import lombok.extern.slf4j.Slf4j;

import java.lang.reflect.Field;
import java.util.*;
import java.util.concurrent.atomic.AtomicLong;

/**
 * @author: QTX
 * @date: 2022/4/27
 */
@Slf4j
public class DataListener<E> implements ReadListener<E> {
    /** 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收 */
    private static final int BATCH_COUNT = 10000;
    /** 缓存的数据 */
    private List<E> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    /** 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。 */
    private final IService<E> service;
    /** excel数据与table不一致，手动实现转换方法 */
    private ConvertList<E> convert;

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

    /**
     * 需要转换excel数据，请使用这个构造方法。
     *
     * @param service
     * @param convert
     */
    public DataListener(IService<E> service, ConvertList<E> convert) {
        this.service = service;
        this.convert = convert;
    }

    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {}

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(E data, AnalysisContext context) {
        // 哈哈 log.info("解析到一条数据:{}", JSON.toJSONString(data));
        try {
            if (isNull(data)) {
                log.info("添加一条数据到备选集合:{}", JSON.toJSONString(data));
                cachedDataList.add(data);
            }
        } catch (IllegalAccessException e) {
            throw new RuntimeException(e);
        }
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析完成！");
    }

    @Override
    public boolean hasNext(AnalysisContext context) {
        return true;
    }

    /** 加上存储数据库 */
    private void saveData() {
        Set<E> set = new HashSet<>(service.list());
        List<E> saveList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        List<E> asList;
        if (convert != null) {
            log.info("{}条数据，开始转换数据格式。", cachedDataList.size());
            saveList.addAll(new HashSet<>(convert.convert(cachedDataList)));
        } else {
            asList = cachedDataList;
            asList.forEach(
                    s -> {
                        if (!set.contains(s)) {
                            s