EasyExcel 读取Excel 和 上传Excel 文件

1、导入maven 依赖

?
1
2
3
4
5
6
<!--excel 导入导出用到的依赖-->
 <dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>easyexcel</artifactId>
   <version>2.1.2</version>
 </dependency>

　　

2、 编写ExcelListener.java

?
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
package com.wlc.sheetConfig;
 
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
 
import java.util.ArrayList;
import java.util.List;
 
/**
 * @author 王立朝
 * @date 2020-11-14
 * @description:
 */
public class ExcelListener extends AnalysisEventListener<Object> {
 
    private List<Object> data = new ArrayList<>();
    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {
        data.add(o);
    }
 
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("所有数据解析完毕");
    }
 
    public List<Object> getData() {
        return data;
    }
 
    public void setData(List<Object> data) {
        this.data = data;
    }
}

　　

3、把要读取的Excel 对应的 sheet 的实体类

?
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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
package com.wlc.sheetConfig;
 
import com.alibaba.excel.annotation.ExcelProperty;
 
/**
 * @author 王立朝
 * @date 2020-11-14
 * @description: 要读取的Excel 对应的第一个sheet
 */
public class Sheet1 {
    @ExcelProperty(index = 0)
    private String no;
    @ExcelProperty(index = 1)
    private String field;
    @ExcelProperty(index = 2)
    private String lastDate;
    @ExcelProperty(index = 3)
    private String thisDate;
    @ExcelProperty(index = 4)
    private String fieldShow;
    @ExcelProperty(index = 5)
    private String require;
 
 
    public String getNo() {
        return no;
    }
 
    public void setNo(String no) {
        this.no = no;
    }
 
    public String getField() {
        return field;
    }
 
    public void setField(String field) {
        this.field = field;
    }
 
    public String getLastDate() {
        return lastDate;
    }
 
    public void setLastDate(String lastDate) {
        this.lastDate = lastDate;
    }
 
    public String getThisDate() {
        return thisDate;
    }
 
    public void setThisDate(String thisDate) {
        this.thisDate = thisDate;
    }
 
    public String getFieldShow() {
        return fieldShow;
    }
 
    public void setFieldShow(String fieldShow) {
        this.fieldShow = fieldShow;
    }
 
    public String getRequire() {
        return require;
    }
 
    public void setRequire(String require) {
        this.require = require;
    }
 
    @Override
    public String toString() {
        return "Sheet1{" +
                "no='" + no + '\'' +
                ", field='" + field + '\'' +
                ", lastDate='" + lastDate + '\'' +
                ", thisDate='" + thisDate + '\'' +
                ", fieldShow='" + fieldShow + '\'' +
                ", require='" + require + '\'' +
                '}';
    }
}

　　

?
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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
package com.wlc.sheetConfig;
 
import com.alibaba.excel.annotation.ExcelProperty;
 
/**
 * @author 王立朝
 * @date 2020-11-14
 * @description:要读取的Excel 对应的第一个sheet
 */
public class Sheet2 {
 
    @ExcelProperty(index = 0)
    private String no;
    @ExcelProperty(index = 1)
    private String field;
    @ExcelProperty(index = 2)
    private String lastDate;
    @ExcelProperty(index = 3)
    private String thisDate;
    @ExcelProperty(index = 4)
    private String fieldShow;
    @ExcelProperty(index = 5)
    private String require;
 
    public String getNo() {
        return no;
    }
 
    public void setNo(String no) {
        this.no = no;
    }
 
    public String getField() {
        return field;
    }
 
    public void setField(String field) {
        this.field = field;
    }
 
    public String getLastDate() {
        return lastDate;
    }
 
    public void setLastDate(String lastDate) {
        this.lastDate = lastDate;
    }
 
    public String getThisDate() {
        return thisDate;
    }
 
    public void setThisDate(String thisDate) {
        this.thisDate = thisDate;
    }
 
    public String getFieldShow() {
        return fieldShow;
    }
 
    public void setFieldShow(String fieldShow) {
        this.fieldShow = fieldShow;
    }
 
    public String getRequire() {
        return require;
    }
 
    public void setRequire(String require) {
        this.require = require;
    }
 
    @Override
    public String toString() {
        return "Sheet2{" +
                "no='" + no + '\'' +
                ", field='" + field + '\'' +
                ", lastDate='" + lastDate + '\'' +
                ", thisDate='" + thisDate + '\'' +
                ", fieldShow='" + fieldShow + '\'' +
                ", require='" + require + '\'' +
                '}';
    }
}

　　

?
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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
package com.wlc.sheetConfig;
 
import com.alibaba.excel.annotation.ExcelProperty;
 
/**
 * @author 王立朝
 * @date 2020-11-14
 * @description:
 */
public class Sheet3 {
 
    @ExcelProperty(index = 0)
    private String uuid;
    @ExcelProperty(index = 1)
    private String mecFlag;
    @ExcelProperty(index = 2)
    private String month;
    @ExcelProperty(index = 3)
    private String startDate;
    @ExcelProperty(index = 4)
    private String endDate;
    @ExcelProperty(index = 5)
    private String type;
    @ExcelProperty(index = 6)
    private String amount;
    @ExcelProperty(index = 7)
    private String delFlag;
 
    public String getUuid() {
        return uuid;
    }
 
    public void setUuid(String uuid) {
        this.uuid = uuid;
    }
 
    public String getMecFlag() {
        return mecFlag;
    }
 
    public void setMecFlag(String mecFlag) {
        this.mecFlag = mecFlag;
    }
 
    public String getMonth() {
        return month;
    }
 
    public void setMonth(String month) {
        this.month = month;
    }
 
    public String getStartDate() {
        return startDate;
    }
 
    public void setStartDate(String startDate) {
        this.startDate = startDate;
    }
 
    public String getEndDate() {
        return endDate;
    }
 
    public void setEndDate(String endDate) {
        this.endDate = endDate;
    }
 
    public String getType() {
        return type;
    }
 
    public void setType(String type) {
        this.type = type;
    }
 
    public String getAmount() {
        return amount;
    }
 
    public void setAmount(String amount) {
        this.amount = amount;
    }
 
    public String getDelFlag() {
        return delFlag;
    }
 
    public void setDelFlag(String delFlag) {
        this.delFlag = delFlag;
    }
 
    @Override
    public String toString() {
        return "Sheet3{" +
                "uuid='" + uuid + '\'' +
                ", mecFlag='" + mecFlag + '\'' +
                ", month='" + month + '\'' +
                ", startDate='" + startDate + '\'' +
                ", endDate='" + endDate + '\'' +
                ", type='" + type + '\'' +
                ", amount='" + amount + '\'' +
                ", delFlag='" + delFlag + '\'' +
                '}';
    }
}

　　

4、编写测试类

?
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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
package com.wlc.sheetConfig;
 
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;
 
import java.io.*;
import java.util.ArrayList;
import java.util.List;
 
/**
 * @author 王立朝
 * @date 2020-11-14
 * @description:
 */
public class Test {
    public static void main(String[] args) throws IOException {
        ExcelReader excelReader = null;
        InputStream inputStream = null;
        try {
          // 要读取的Excel的路径
            String filePath = "/Users/wanglichao/Downloads/确认版测试用.xlsx";
            File file = new File(filePath);
            inputStream = new FileInputStream(file);
 
            excelReader = EasyExcel.read(inputStream).build();
            ExcelListener sheet1Listener = new ExcelListener();
            ExcelListener sheet2Listener = new ExcelListener();
            ExcelListener sheet3Listener = new ExcelListener();
 
            //获取sheet对象
            ReadSheet readSheet1 =
                    EasyExcel.readSheet(0).head(Sheet1.class).registerReadListener(sheet1Listener).build();
            ReadSheet readSheet2 =
                    EasyExcel.readSheet(1).head(Sheet2.class).registerReadListener(sheet2Listener).build();
            ReadSheet readSheet3 =
                    EasyExcel.readSheet(2).head(Sheet3.class).registerReadListener(sheet3Listener).build();
 
            //读取数据
            excelReader.read(readSheet1, readSheet2,readSheet3);
            //业务处理
            System.out.println("sheet1->" + sheet1Listener.getData().size());
            System.out.println("sheet2->" + sheet2Listener.getData().size());
            System.out.println("sheet3->" + sheet3Listener.getData().size());
            List<Sheet1> sheet1List = new ArrayList<>();
            int sheet1Size = sheet1Listener.getData().size();
            for (int i = 0; i < sheet1Size; i++) {
                Sheet1 sheet1 = new Sheet1();
                sheet1.setNo(((Sheet1) sheet1Listener.getData().get(i)).getNo());
                sheet1.setField(((Sheet1) sheet1Listener.getData().get(i)).getField());
                sheet1.setLastDate(((Sheet1) sheet1Listener.getData().get(i)).getLastDate());
                sheet1.setThisDate(((Sheet1) sheet1Listener.getData().get(i)).getThisDate());
                sheet1.setFieldShow(((Sheet1) sheet1Listener.getData().get(i)).getFieldShow());
                sheet1.setRequire(((Sheet1) sheet1Listener.getData().get(i)).getRequire());
                sheet1List.add(sheet1);
            }
            List<Sheet2> sheet2List = new ArrayList<>();
            int sheet2Size = sheet2Listener.getData().size();
            for (int i = 0; i < sheet2Size; i++) {
                Sheet2 sheet2 = new Sheet2();
                sheet2.setNo(((Sheet2) sheet2Listener.getData().get(i)).getNo());
                sheet2.setField(((Sheet2) sheet2Listener.getData().get(i)).getField());
                sheet2.setLastDate(((Sheet2) sheet2Listener.getData().get(i)).getLastDate());
                sheet2.setThisDate(((Sheet2) sheet2Listener.getData().get(i)).getThisDate());
                sheet2.setFieldShow(((Sheet2) sheet2Listener.getData().get(i)).getFieldShow());
                sheet2.setRequire(((Sheet2) sheet2Listener.getData().get(i)).getRequire());
                sheet2List.add(sheet2);
            }
            List<Sheet3> sheet3List = new ArrayList<>();
            int sheet3Size = sheet3Listener.getData().size();
            for (int i = 0; i < sheet3Size; i++) {
                Sheet3 sheet3 = new Sheet3();
                sheet3.setUuid(((Sheet3) sheet3Listener.getData().get(i)).getUuid());
                sheet3.setMecFlag(((Sheet3) sheet3Listener.getData().get(i)).getMecFlag());
                sheet3.setMonth(((Sheet3) sheet3Listener.getData().get(i)).getMonth());
                sheet3.setStartDate(((Sheet3) sheet3Listener.getData().get(i)).getStartDate());
                sheet3.setEndDate(((Sheet3) sheet3Listener.getData().get(i)).getEndDate());
                sheet3.setType(((Sheet3) sheet3Listener.getData().get(i)).getType());
                sheet3.setAmount(((Sheet3) sheet3Listener.getData().get(i)).getAmount());
                sheet3.setDelFlag(((Sheet3) sheet3Listener.getData().get(i)).getDelFlag());
                sheet3List.add(sheet3);
            }
 
 
            System.out.println(sheet1List.toString()+"   ");
            System.out.println(sheet2List.toString()+"   ");
            System.out.println(sheet3List.toString()+"   ");
        } catch (Exception  e) {
            e.printStackTrace();
        } finally {
            inputStream.close();
            if (excelReader != null) {
                excelReader.finish();
            }
        }
 
    }
}

　　

posted @   消失的那两年  阅读(1721)  评论(0)  编辑  收藏  举报
（评论功能已被禁用）
编辑推荐：
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
· 浏览器原生「磁吸」效果！Anchor Positioning 锚点定位神器解析
· 没有源码，如何修改代码逻辑？
阅读排行：
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
点击右上角即可分享
微信分享提示