引入依赖

1
2
3
4
5
6
7
8
9
10
11
12
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.5</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>2.0.22</version>
</dependency>

实现过程

定义监听器

定义一个监听器继承 AnalysisEventListener 类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import lombok.Data;
import lombok.EqualsAndHashCode;

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

/**
 * @author LeDao
 */
@Data
@EqualsAndHashCode(callSuper = true)
public class ExcelListener extends AnalysisEventListener<Object> {

    /**
     * 自定义用于暂时存储data
     */
    private List<JSONObject> dataList = new ArrayList<>();

    /**
     * 导入表头
     */
    private Map<String, Integer> importHeads = new HashMap<>(16);

    /**
     * 这个每一条数据解析都会来调用
     */
    @Override
    public void invoke(Object data, AnalysisContext context) {
        String headStr = JSON.toJSONString(data);
        dataList.add(JSONObject.parseObject(headStr));
    }

    /**
     * 这里会一行行的返回头
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        for (Integer key : headMap.keySet()) {
            if (importHeads.containsKey(headMap.get(key))) {
                continue;
            }
            importHeads.put(headMap.get(key), key);
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {

    }
}

测试类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson.JSONObject;

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

/**
 * @author LeDao
 * @company
 * @create 2021-06-21 12:38
 */
public class Test {

    public static void main(String[] args) {
        //要读取的Excel的文件位置
        String fileName = "C:\\Users\\LeDao\\Desktop\\测试.xlsx";
        ExcelListener excelListener = new ExcelListener();
        EasyExcel.read(fileName, excelListener).sheet().doRead();
        //表格头数据
        Map<String, Integer> heads = excelListener.getImportHeads();
        System.out.println(heads);
        //获取全部数据
        List<JSONObject> dataList = excelListener.getDataList();
        //遍历每一行数据
        for (JSONObject jsonObject : dataList) {
            System.out.println(jsonObject);
        }
    }
}

结果

表格数据

读取的结果

PS.

上面是直接读取电脑磁盘上的 Excel 表，也可以读取前端上传的 Excel 表

1
2
3
4
5
6
7
8
9
10
@PostMapping("upload")
@ResponseBody
public String upload(MultipartFile file) throws IOException {
   ExcelListener excelListener = new ExcelListener();
   EasyExcel.read(file.getInputStream(), excelListener).sheet().doRead();
   //表格头数据
   Map<String, Integer> heads = excelListener.getImportHeads();
   //每一行数据
   List<JSONObject> dataList = excelListener.getDataList();
}