EasyExcel导入

Controller层

 @PostMapping("/import")
    public Result<String> importDemo(HttpServletRequest request, @RequestParam("file") MultipartFile file) {
        log.info("import.request:{}", file.getOriginalFilename());
        if (file.getOriginalFilename() == null || !file.getOriginalFilename().endsWith("xlsx")) {
            throw new Exception("只支持.xlsx类型的文件导入!");
        }
        // 读取Excel内容
        List<DemoPo> demoList;
        try {
            ExcelImportPoSoAdapter adapter = new ExcelImportPoSoAdapter();
            EasyExcelFactory.read(file.getInputStream(), DemoPo.class, adapter).doReadAll();
            Map<String, String> map = adapter.getMap();
            if (map.size() != 0){
                throw  new BizException(map.get("msg"));
            }
            demoList= adapter.demoList();
        } catch (Exception e) {
            log.error("import.error:", e);
            throw new Exception("您上传的文件格式与模板格式不一致，请检查后重新上传");
        }
        if(demoList.size() > 500){
            throw new Exception("Excel数据超出限制, 最大支持导入500条!");
        }
        if (ObjectUtil.isEmpty(demoList)) {
            throw new Exception("导入数据为空，请添加数据后再次导入");
        }
        demoUserCase.save(request, demoList);
        log.info("import:success");
        return Result.buildResult(null);
    }

Excel适配器类

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Excel适配器
 */
@Slf4j
@Getter
public class ExcelImportPoSoAdapter extends AnalysisEventListener<ExcelPayload> {
    
    /**
     * 数据集合
     */
    List<DemoPo> demoPoList = new ArrayList<>();

    // 此map用来存储错误提示
    protected Map<String, String> map = new HashMap<>();

     /**
     * 校验表头
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        // headMap.containsKey(下标)，先判空
        // headMap.get(0).equals() 判内容是否相符
        if (!headMap.containsKey(0) || !headMap.containsKey(1)
                || !headMap.get(0).equals("enmu1") || !headMap.get(1).equals("enmu2")) {
            // 这里给demoPoList 加1条空数据，是因为doAfterAllAnalysed方法最后有判是否是空列表
            demoPoList.add(new DemoPo());
            map.put("msg", "您上传的文件格式与模板格式不一致，请检查后重新上传");
        }
    }
    @Override
    public void invoke(ExcelPayload excelPayload, AnalysisContext analysisContext) {
        demoPoList .add(excelPayload.excelPayloadToDemoPo(excelPayload));
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("ExcelImportAdapter.doAfterAllAnalysed.success");
    }
}

service

接着写业务的逻辑校验

执行批量导入

注：注意List切片，因为sql传入参数最大限制2100个

import com.google.common.collect.Lists;
上方为该Lists导包
List<List<DemoPo>> partition = Lists.partition(demoList, 100);