使用 EasyExcel 进行数据解析

一、添加pom.xml 导入相关依赖

 　　　　<dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.12</version>
        </dependency>

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

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
        </dependency>

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

 

二、EasyExcelUtil 工具类

package com.example.fengqing.EasyExcel.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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

/**
 * Excel表格数据解析工具类
 * @author FengQing
 * @program fengqing
 * @description
 * @date 2024/04/23
 */
public class EasyExcelUtil extends AnalysisEventListener<Map<Integer, String>> {
    private static final Logger LOGGER = LoggerFactory.getLogger(EasyExcelUtil.class);
    List<Map<Integer, String>> list = new ArrayList<>();

    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) {
        LOGGER.info("解析到的数据: {}", data);
        list.add(data);
    }

    @Override
    public void invokeHeadMap(Map headMap, AnalysisContext context) {
        LOGGER.info("解析到的表头数据: {}", headMap);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
    }

    /**
     * 如果有必要，存储到数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        LOGGER.info("存储数据库成功！");
    }

    public List<Map<Integer, String>> getTargetColumnData(){
        return list;
    }

    @Test
    public void contextLoads() {
        String fileName = "D:\\赤峰正太劳务派遣有限公司_A51940_38774.xlsx"; // Excel文件路径
        // 这里 只要，然后读取第一个sheet 同步读取会自动finish
        EasyExcelUtil listener = new EasyExcelUtil();
        EasyExcel.read(fileName, listener).sheet().doRead();
        List<Map<Integer, String>> targetColumnData = listener.getTargetColumnData();

        List<String> addList = new ArrayList<>();
        targetColumnData.forEach(v ->{
            LOGGER.info("姓名: {}", v.get(0));
            LOGGER.info("身份证: {}", v.get(1));
            LOGGER.info("开始时间: {}", v.get(2));
            LOGGER.info("结束时间: {}", v.get(3));
            addList.add(v.get(1));
        });
    }
}