Excel动态表头导入

引入需要的依赖

<!--Excel导入 使用注解方式 可选-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.3.0</version>
        </dependency>
        <!-- 动态导入需要的依赖 少了报错 java.lang.NoClassDefFoundError: org/apache/poi/poifs/filesystem/FileMagic-->
        <dependency>
            <groupId>com.auth0</groupId>
            <artifactId>java-jwt</artifactId>
            <version>3.9.0</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

Controller层

public HashMap<String, Object> upload(@RequestParam(value = "file") MultipartFile file,
                                          @RequestParam(value = "ztId") int ztId,
                                          @RequestParam(value = "year") int year,
                                          @RequestParam(value = "month") int month) throws IOException {

        //初始化监听器
        ZwmxzListener zwmxzListener = new ZwmxzListener(zwmxzService, ztId, year, month);
        //解析数据
        EasyExcel.read(file.getInputStream(), zwmxzListener).sheet(0).doReadSync();

        HashMap<String, Object> hashMap = new HashMap<>();
        //获取校验错误信息，并返回给前端
        List<String> errMessage = zwmxzListener.getErrMessage();
        if (errMessage.isEmpty()) {
            hashMap.put("success", true);
        } else {
            hashMap.put("success", false);
            hashMap.put("errMessage", errMessage);
        }
        return hashMap;
    }

Listener监听器

//继承AnalysisEventListener<Map<Integer, String>>
@Data
@Slf4j
public class ZwmxzListener extends AnalysisEventListener<Map<Integer, String>> {
    //定义每多少条数据进行数据库保存
    private static final int BATCH_COUNT = 128;
    private int ztId;
    private int year;
    private int month;
    private ZwmxzService zwmxzService;
    //建一个errMessage集合保存校验有问题的结果
    private List<String> errMessage;
    //用list集合保存解析到的结果
    private List<Map<Integer, Map<Integer, String>>> list;


    //重构，把传来的值赋给对应的属性
    public ZwmxzListener(ZwmxzService zwmxzService, int ztId, int year, int month) {
        this.ztId = ztId;
        this.year = year;
        this.month = month;
        this.zwmxzService = zwmxzService;
        list = new ArrayList<>();
        errMessage = new ArrayList<>();
    }

    /**
     * 重写invokeHeadMap方法，获去表头，如果有需要获取第一行表头就重写这个方法，不需要则不需要重写
     *
     * @param headMap Excel每行解析的数据为Map<Integer, String>类型，Integer是Excel的列索引,String为Excel的单元格值
     * @param context context能获取一些东西，比如context.readRowHolder().getRowIndex()为Excel的行索引，表头的行索引为0，0之后的都解析成数据
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        logger.info("解析到一条头数据：{}, currentRowHolder: {}", headMap.toString(), context.readRowHolder().getRowIndex());
        Map<Integer, Map<Integer, String>> map = new HashMap<>();
        map.put(context.readRowHolder().getRowIndex(), headMap);
        list.add(map);
    }

    /**
     * 重写invoke方法获得除Excel第一行表头之后的数据，
     * 如果Excel第二行也是表头，那么也会解析到这里，如果不需要就通过判断context.readRowHolder().getRowIndex()跳过
     *
     * @param data    除了第一行表头外，数据都会解析到这个方法
     * @param context 和上面解释一样
     */
    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) {
        logger.info("解析到一条数据：{}, currentRowIndex: {}----", data.toString(), context.readRowHolder().getRowIndex());
        Map<Integer, Map<Integer, String>> map = new HashMap<>();
        map.put(context.readRowHolder().getRowIndex(), data);
        list.add(map);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }

    /**
     * 解析到最后会进入这个方法，需要重写这个doAfterAllAnalysed方法，然后里面调用自己定义好保存方法
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        logger.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        logger.info("{}条数据，开始存储数据库！", list.size());
        //用errMessage集合保存zwmxzService.save()返回校验错误信息，根据自己需要来写就行了
        errMessage.addAll(zwmxzService.save(list, ztId, year, month, errMessage.size() > 0));
    }

}

Service层

@Transactional(rollbackFor = Exception.class)
    @Override
    public List<String> save(List<Map<Integer, Map<Integer, String>>> list, int ztId, int year, int month, boolean hasErr) {
        logger.info("--start---导入判断----");
        List<String> error = new ArrayList<>();
        List<Zwmxz> newZwmxz = new ArrayList<>();
        String pattern = "^[-]?\\d+(\\.\\d+)?$";
        //遍历传过来的数据集合
        for (var item : list) {
            //每一行
            for (var row : item.entrySet()) {
                //获取行的索引
                Integer rowIndex = row.getKey();
                //索引为0表示为表头
                if (rowIndex == 0) {
                    String leftYear = row.getValue().get(1);
                    String rightYear = row.getValue().get(2);
                    logger.info("---leftYear={}--------rightYear={}----------", leftYear, rightYear);
                    String left = judgeYear(leftYear, year, "B");
                    if (left != null) {
                        error.add(left);
                    }
                    String right = judgeYear(rightYear, year, "C");
                    if (right != null) {
                        error.add(right);
                    }
                } else {
                    rowIndex++;
                    logger.info("---rowIndex={}--------", rowIndex);
                    Zwmxz it = new Zwmxz();
                    //遍历每一行的每一列数据，并校验
                    for (var col : row.getValue().entrySet()) {
                        switch (col.getKey()) {
                            case 1:
                                if (col.getValue() == null) {
                                    error.add("序号为【" + rowIndex + "】的数据的B列【月份】不可为空!");
                                } else {
                                    if (Pattern.matches(pattern, col.getValue().trim())) {
                                        if (Integer.parseInt(col.getValue().trim()) == month) {
                                            it.setMonth(month);
                                        } else {
                                            error.add("序号为【" + rowIndex + "】的数据的B列【月份】与当前期间的月份不一致!");
                                        }
                                    } else {
                                        error.add("序号为【" + rowIndex + "】的数据的B列【月份】需要是正常的阿拉伯数字月份!");
                                    }
                                }
                                break
                           ......................................................case 16:
                                if (col.getValue() == null) {
                                    it.setYe(BigDecimal.ZERO);
                                } else {
                                    if (Pattern.matches(pattern, col.getValue().trim())) {
                                        it.setYe(new BigDecimal(col.getValue()));
                                    } else {
                                        it.setYe(BigDecimal.ZERO);
                                    }
                                }
                                break;
                            case 17:
                                it.setSdocId(col.getValue());
                                break;
                            default:
                                break;
                        }
                    }
                    it.setZtId(ztId);
                    it.setYear(year);
                    Date date = new Date();
                    it.setUpdatedAt(date);
                    it.setCreatedAt(date);
                    newZwmxz.add(it);
                }
            }
        }
        // 以上数据 若存在一条报错的数据，则全部导入失败！
        if (error.size() > 0 || hasErr) {
            zwmxzRepository.deleteAllByZtIdAndYearAndMonth(ztId, year, month);
        } else {
            for (var it : newZwmxz) {
                zwmxzRepository.save(it);
            }
        }
        return error;
    }


 private String judgeYear(String year1, int year2, String position) {
        String pattern = "^[0-9]+$";
        if (year1 == null) {
            return "Excel的" + position + "列的表头年份不可为空！";
        } else {
            String trim = year1.trim();
            if (trim.length() >= 4) {
                String substring = trim.substring(0, 4);
                logger.info("-----year1:{}", substring);
                if (Pattern.matches(pattern, substring)) {
                    if (Integer.parseInt(substring) != year2) {
                        return "Excel的" + position + "列的表头年份与当前期间年份不一致！";
                    }
                } else {
                    return "Excel的" + position + "列的表头年份不规范！";
                }
            } else {
                return "Excel的" + position + "列的表头年份不规范！";
            }
        }
        return null;
    }

//可以先存储表头，然后再根据动态表头获取表格中数据