EasyExcel 通用导入方法封装

1、编辑监听类

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.extension.service.IService;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;

import java.io.Serializable;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

/**
 * ClassName: ExcelDataListener
 * Package: com.xgxz.finance.excel.listener
 * Description:
 *
 * @Author: 习惯向左
 * @Create: 2024/3/22 - 17:54
 * @Version: v1.0
 */
@Slf4j
@NoArgsConstructor
public class ExcelDataListener<T> extends AnalysisEventListener<T> {

    IService<T> service;

    Field field;

    Long configId;

    String fieldName;

    List<T> list;

    public static final int BATCH_COUNT = 50;

	/**
     * 没有前置条件的调用此构造
     */
    public ExcelDataListener(IService<T> service) throws NoSuchFieldException {

        this.service = service;

        this.list = new ArrayList<>();
    }

    /**
     * 导入的数据有前置条件的调用此构造
     */
    public ExcelDataListener(IService<T> service, Class<? extends Serializable> clazz, Long configId, String fieldName) throws NoSuchFieldException {

        this.service = service;

        field = clazz.getDeclaredField(fieldName);

        this.configId = configId;

        this.fieldName = fieldName;

        this.list = new ArrayList<>();
    }

    @Override
    public void invoke(T data, AnalysisContext analysisContext) {

        if (configId != null && fieldName != null && field != null) {

            try {
                field.setAccessible(true);

                field.set(data, configId);
            } catch (IllegalAccessException e) {
                throw new RuntimeException(e);
            }
        }

        list.add(data);

        if (list.size() >= BATCH_COUNT) {

            saveData();

            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

        saveData();

        log.info("所有数据保存成功。。。。");
    }

    private void saveData() {

        service.saveBatch(list);

        log.info("{} 条数据批量插入成功。。。。", list.size());
    }
}

2、编写EasyExcel 工具类

@Slf4j
public class EasyExcelUtil {

	public static <T> void importData(InputStream inputStream, Class<? extends Serializable> clazz, ExcelDataListener<T> listener) {

        EasyExcel.read(inputStream, clazz, listener).sheet().doRead();
    }
}

3、编写接口

// 这里的configId 是保存到数据库时要将excel中的数据都关联上configId这个值，不需要这样的逻辑可以不带这个参数
@PostMapping(value = "/importData", consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
public String importData(@RequestParam("configId") Long configId, @RequestParam("file") MultipartFile file){

    dataService.importData(configId, file);

    return "success";
}

4、service 实现

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;

@Service
public class DataServiceImpl extends ServiceImpl<UserInfoMapper, UserInfoPO> implements DataService {
	
	@Override
    public void importData(Long configId, MultipartFile file) {

        Assert.notNull(configId, ResponseEnum.ID_NOT_NULL_ERROR);

        try {
        	// 这里的this是指 IService 对象，我这里用到了mybatis-plus
            EasyExcelUtil.importData(file.getInputStream(), UserInfoPO.class, new ExcelDataListener<>(this, UserInfoPO.class, configId, "parentId"));
        } catch (IOException | NoSuchFieldException e) {
            throw new RuntimeException(e);
        }
    }
}    

习惯向左，感觉至上