EasyExcel导入存在合并单元格的Excel

发表于 更新于 分类于 ， 阅读次数： 本文字数： 11k 阅读时长 ≈ 10 分钟

Excel表格

下面是Excel表格，成果简介前可以作为主表数据存储(存在合并)，成果简介后的数据作为从表数据存储。
在这里插入图片描述

实现思路

  • 通过EasyExcel读取Excel数据，用List<AwardsDetailField> list接收
    因为POI/EasyExcel对合并单元格的数据只读取一次，需要把为空的单元格也赋值，这是核心技术难点。

  • 循环使用hibernate-validator对数据进行校验
    存在校验不通过的数据返回给前端,所有数据校验通过才写入数据库

  • 封装然后写入数据库

    • 用Java8 对List< DetailFeiled> list根据多字段进行分组，得到Map<String,List> map
    • 循环map，封装主表和详细表插入list
    • 批量插入

  • 导入时要求同一年数据可以分多次导入，但同一年内，成果名称不允许重复
    这个可以在数据库中根据获奖类型+获奖年份+成果名称建立联合约束

    1
    ALTER TABLE t_awards ADD CONSTRAINT award_type_year_product UNIQUE (award_type,award_year,award_product);

    参考链接

相关代码

下面贴出的是通过EasyExcel读取Excel数据的测试代码

pom依赖：

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
<dependencies>
   <dependency>
       <groupId>com.alibaba</groupId>
       <artifactId>easyexcel</artifactId>
       <version>2.2.6</version>
   </dependency>
   <dependency>
       <groupId>com.alibaba</groupId>
       <artifactId>fastjson</artifactId>
       <version>1.2.76</version>
   </dependency>
   <!--lombok-->
   <dependency>
       <groupId>org.projectlombok</groupId>
       <artifactId>lombok</artifactId>
       <version>1.18.20</version>
       <scope>provided</scope>
   </dependency>
   <!--junit测试-->
   <dependency>
       <groupId>junit</groupId>
       <artifactId>junit</artifactId>
       <version>4.12</version>
       <scope>test</scope>
   </dependency>
   <!--slf4j简单实现-->
   <dependency>
       <groupId>org.slf4j</groupId>
       <artifactId>slf4j-simple</artifactId>
       <version>1.7.25</version>
       <scope>compile</scope>
   </dependency>
   <!--用于给泛型对象的属性设置值-->
   <dependency>
    <groupId>commons-beanutils</groupId>
    <artifactId>commons-beanutils</artifactId>
    <version>1.9.3</version>
</dependency>
</dependencies>

Excel行号类:

1
2
3
4
5
@Data
public class RowIndex {
    @ExcelIgnore
    private Integer lineNo;
}

AwardsDetailField类(需继承RowIndex)：

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
package com.importexcel;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import java.math.BigDecimal;

/**
 * Excel对应的Java实体类
 */
@Data
public class AwardsDetailField extends RowIndex {

    @ExcelProperty(value="奖项等级（一级分类）",index = 0)
    private String topClass;

    @ExcelProperty(value="奖项等级（二级分类）",index = 1)
    private String secondClass;

    @ExcelProperty(value="获奖年份",index = 2)
    private String awardYear;

    @ExcelProperty(value="获奖产品/成果名称",index = 3)
    private String awardProduct;

    @ExcelProperty(value="成果简介",index = 4)
    private String briefIntroduction;

    @ExcelProperty(value="获奖单位",index = 5)
    private String company;

    @ExcelProperty(value="获奖个人姓名",index = 6)
    private String name;

    @ExcelProperty(value="员工编号",index = 7)
    private String employeeNo;

    @ExcelProperty(value="获奖金额（元）",index = 8)
    private BigDecimal money;
}

ImportExcelHelper类(导入Excel入口、对合并单元格处理)：

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
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
package com.importexcel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.enums.CellExtraTypeEnum;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.util.CollectionUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.Field;
import java.util.List;

public class ImportExcelHelper<T> {

    private static final Logger LOGGER = LoggerFactory.getLogger(ImportExcelHelper.class);

    /**
     * 返回解析后的List
     *
     * @param: fileName 文件名
     * @param: clazz Excel对应属性名
     * @param: sheetNo 要解析的sheet
     * @param: headRowNumber 正文起始行
     * @return java.util.List<T> 解析后的List
     */
    public List<T> getList(String fileName, Class<T> clazz, Integer sheetNo, Integer headRowNumber) {
        ImportExcelListener<T> listener = new ImportExcelListener<>(headRowNumber);
        try {
            EasyExcel.read(fileName, clazz, listener).extraRead(CellExtraTypeEnum.MERGE).sheet(sheetNo).headRowNumber(headRowNumber).doRead();
        } catch (Exception e) {
            LOGGER.error(e.getMessage());
        }
        List<CellExtra> extraMergeInfoList = listener.getExtraMergeInfoList();
        if (CollectionUtils.isEmpty(extraMergeInfoList)) {
            return listener.getData();
        }
        List<T> data = explainMergeData(listener.getData(), extraMergeInfoList, headRowNumber);
        return data;
    }

    /**
     * 处理合并单元格
     *
     * @param data               解析数据
     * @param extraMergeInfoList 合并单元格信息
     * @param headRowNumber      起始行
     * @return 填充好的解析数据
     */
    private List<T> explainMergeData(List<T> data, List<CellExtra> extraMergeInfoList, Integer headRowNumber) {
        //循环所有合并单元格信息
        extraMergeInfoList.forEach(cellExtra -> {
            int firstRowIndex = cellExtra.getFirstRowIndex() - headRowNumber;
            int lastRowIndex = cellExtra.getLastRowIndex() - headRowNumber;
            int firstColumnIndex = cellExtra.getFirstColumnIndex();
            int lastColumnIndex = cellExtra.getLastColumnIndex();
            //获取初始值
            Object initValue = getInitValueFromList(firstRowIndex, firstColumnIndex, data);
            //设置值
            for (int i = firstRowIndex; i <= lastRowIndex; i++) {
                for (int j = firstColumnIndex; j <= lastColumnIndex; j++) {
                    setInitValueToList(initValue, i, j, data);
                }
            }
        });
        return data;
    }

    /**
     * 设置合并单元格的值
     *
     * @param filedValue  值
     * @param rowIndex    行
     * @param columnIndex 列
     * @param data        解析数据
     */
    public void setInitValueToList(Object filedValue, Integer rowIndex, Integer columnIndex, List<T> data) {
        T object = data.get(rowIndex);

        for (Field field : object.getClass().getDeclaredFields()) {
            //提升反射性能，关闭安全检查
            field.setAccessible(true);
            ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
            if (annotation != null) {
                if (annotation.index() == columnIndex) {
                    try {
                        field.set(object, filedValue);
                        break;
                    } catch (IllegalAccessException e) {
                        LOGGER.error("设置合并单元格的值异常："+e.getMessage());
                    }
                }
            }
        }
    }


    /**
     * 获取合并单元格的初始值
     * rowIndex对应list的索引
     * columnIndex对应实体内的字段
     *
     * @param firstRowIndex    起始行
     * @param firstColumnIndex 起始列
     * @param data             列数据
     * @return 初始值
     */
    private Object getInitValueFromList(Integer firstRowIndex, Integer firstColumnIndex, List<T> data) {
        Object filedValue = null;
        T object = data.get(firstRowIndex);
        for (Field field : object.getClass().getDeclaredFields()) {
            //提升反射性能，关闭安全检查
            field.setAccessible(true);
            ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
            if (annotation != null) {
                if (annotation.index() == firstColumnIndex) {
                    try {
                        filedValue = field.get(object);
                        break;
                    } catch (IllegalAccessException e) {
                        LOGGER.error("设置合并单元格的初始值异常："+e.getMessage());
                    }
                }
            }
        }
        return filedValue;
    }
}

Excel模板的读取监听类：

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
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
package com.importexcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.fastjson.JSON;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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

/**
 * Excel模板的读取监听类
 *
 * @author wangwei
 */
public class ImportExcelListener<T> extends AnalysisEventListener<T> {
    private static final Logger LOGGER = LoggerFactory.getLogger(ImportExcelListener.class);
    /**
     * 解析的数据
     */
    List<T> list = new ArrayList<>();

    /**
     * 正文起始行
     */
    private Integer headRowNumber;
    /**
     * 合并单元格
     */
    private List<CellExtra> extraMergeInfoList = new ArrayList<>();

    public ImportExcelListener(Integer headRowNumber) {
        this.headRowNumber = headRowNumber;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context context
     */
    @Override
    public void invoke(T data, AnalysisContext context) {
        // 如果一行Excel数据均为空值，则不装载该行数据
        if(isLineNullValue(data)){
            return;
        }
        LOGGER.info("解析到一条数据: {}", gson.toJson(data));
	    // 获取Excle行号(从0开始)
	    ReadRowHolder readRowHolder = context.readRowHolder();
	    Integer rowIndex = readRowHolder.getRowIndex();
	    try {
	        BeanUtils.setProperty(data, "lineNo", rowIndex+1);
	    } catch (IllegalAccessException e) {
	        LOGGER.error("ImportExcelListener.invoke 设置行号异常: ", e);
	    } catch (InvocationTargetException e) {
	        LOGGER.error("ImportExcelListener.invoke 设置行号异常: ", e);
	    }
	    list.add(data);
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        LOGGER.info("所有数据解析完成！");
    }

    /**
     * 返回解析出来的List
     */
    public List<T> getData() {
        return list;
    }

    /**
     * 读取额外信息：合并单元格
     */
    @Override
    public void extra(CellExtra extra, AnalysisContext context) {
        LOGGER.info("读取到了一条额外信息:{}", JSON.toJSONString(extra));
        switch (extra.getType()) {
            case MERGE: {
                LOGGER.info(
                        "额外信息是合并单元格,而且覆盖了一个区间,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{}",
                        extra.getFirstRowIndex(), extra.getFirstColumnIndex(), extra.getLastRowIndex(),
                        extra.getLastColumnIndex());
                if (extra.getRowIndex() >= headRowNumber) {
                    extraMergeInfoList.add(extra);
                }
                break;
            }
            default:
        }
    }

    /**
     * 返回解析出来的合并单元格List
     */
    public List<CellExtra> getExtraMergeInfoList() {
        return extraMergeInfoList;
    }

    /**
	 * 判断整行单元格数据是否均为空 true是 false否
	 */
	private boolean isLineNullValue(T data) {
		if (data instanceof String) {
			return Objects.isNull(data);
		}
		try {
			List<Field> fields = Arrays.stream(data.getClass().getDeclaredFields())
					.filter(f -> f.isAnnotationPresent(ExcelProperty.class))
					.collect(Collectors.toList());
			List<Boolean> lineNullList = new ArrayList<>(fields.size());
			for (Field field : fields) {
				field.setAccessible(true);
				Object value = field.get(data);
				if (Objects.isNull(value)) {
					lineNullList.add(Boolean.TRUE);
				} else {
					lineNullList.add(Boolean.FALSE);
				}
			}
			return lineNullList.stream().allMatch(Boolean.TRUE::equals);
		} catch (Exception e) {
			LOGGER.error("读取数据行[{}]解析失败: {}", data, e.getMessage());
		}
		return true;
	}
}

测试类：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
package com.importexcel;
import com.alibaba.fastjson.JSON;
import java.util.List;

/**
 * 测试类
 * @createTime 2021/08/06
 */
public class ImportTest {


    public static void main(String[] args) {
        String PATH = "E:\\Downloads\\";
        String fileName = PATH + "各奖励网站导入模版.xlsx";
        ImportExcelHelper<AwardsDetailField> helper = new ImportExcelHelper<>();
        List<AwardsDetailField> list = helper.getList(fileName,AwardsDetailField.class,0,1);
        System.out.println(JSON.toJSONString(list));
    }
}

运行后打印的JSON字符串,用JSON工具解析发现完全是想要的效果，成功搞定！
在这里插入图片描述
参考链接、参考链接