easyexcel操作之名称匹配

简单说下需求

现在我有一个excel表格，里面有两张表，分别是a_name表，b_name表，我要在这两张表的基础上新建一张a_b_name表，这张表匹配a,b表的名称，品牌名一样则放在同一行。

示例：

a_name表

奥迪 奥迪A4L 2019款 35 TFSI 进取版 国V	
奥迪 奥迪A4L 2016款 35 TFSI 自动 运动型	
奥迪 奥迪A4L 2016款 30 TFSI 手动 舒适型	
奥迪 奥迪A4L 2016款 30 TFSI 自动 舒适型	
奥迪 奥迪A4L 2016款 35 TFSI 自动 标准型	
奥迪 奥迪A4L 2016款 35 TFSI 自动 舒适型	
奥迪 奥迪A4L 2015款 35 TFSI 纪念舒享版	
1
2
3
4
5
6
7

b_name表

奥迪 奥迪A4L 2016款 奥迪A4L 45 TFSI quattro个性运动型
奥迪 奥迪A4L 2016款 奥迪A4L 45 TFSI quattro运动型
奥迪 奥迪A4L 2015款 奥迪A4L 50 TFSI quattro旗舰型
奥迪 奥迪A4L 2016款 奥迪A4L 30 TFSI 典藏版 自动舒适型
奥迪 奥迪A4L 2016款 奥迪A4L 35 TFSI 典藏版 自动标准型
奥迪 奥迪A4L 2016款 奥迪A4L 35 TFSI 典藏版 S line舒适型
1
2
3
4
5
6

a_b_name表

奥迪 奥迪A4L 2019款 35 TFSI 进取版 国V	奥迪 奥迪A4L 2019款 奥迪A4L 35 TFSI 进取型 国V
奥迪 奥迪A4L 2016款 35 TFSI 自动 运动型	奥迪 奥迪A4L 2016款 奥迪A4L 35 TFSI 自动运动型
奥迪 奥迪A4L 2016款 30 TFSI 手动 舒适型	奥迪 奥迪A4L 2016款 奥迪A4L 30 TFSI 手动舒适型
奥迪 奥迪A4L 2016款 30 TFSI 自动 舒适型	奥迪 奥迪A4L 2016款 奥迪A4L 30 TFSI 自动舒适型
奥迪 奥迪A4L 2016款 35 TFSI 自动 标准型	奥迪 奥迪A4L 2016款 奥迪A4L 35 TFSI 自动标准型
奥迪 奥迪A4L 2016款 35 TFSI 自动 舒适型	奥迪 奥迪A4L 2016款 奥迪A4L 35 TFSI 自动舒适型
奥迪 奥迪A4L 2015款 35 TFSI 纪念舒享版	奥迪 奥迪A4L 2015款 奥迪A4L 35 TFSI 百万纪念舒享版型

1
2
3
4
5
6
7
8

需要注意的是a和b并不需要完全一样，只需要大部分相同就放在同一行，也就是说直接==比较没用

OK，了解需求后直接开始，我想的是web网页，上传excel表后点击匹配生成，然后生成a_b_name表

1、创建一个maven项目，导入包

    <dependencies>
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starterartifactId>
        dependency>

        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-testartifactId>
            <scope>testscope>
        dependency>
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-webartifactId>
        dependency>

        
        <dependency>
            <groupId>org.projectlombokgroupId>
            <artifactId>lombokartifactId>
        dependency>

        
        <dependency>
            <groupId>com.alibabagroupId>
            <artifactId>easyexcelartifactId>
            <version>3.1.1version>
        dependency>
    dependencies>
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

2、yml配置文件

# 服务端口
server:
  port: 8010
  # 服务名
spring:
  main:
    main:
    banner-mode: off  #关闭banner
  application:
    name: NameMatching
  # 环境设置：dev、test、prod
  profiles:
    active: dev
  #返回json的全局时间格式
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss
    time-zone: GMT+8
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

3、创建实体类

abData

package com.example.namematching.dao;

import lombok.Data;
@Data
public class abData {
    private String abName;
}
1
2
3
4
5
6
7

abName

@Data
@NoArgsConstructor
@AllArgsConstructor
public class abData {
    @ExcelProperty({"a_name"})  //excel的表头
    private String aName;
    @ExcelProperty({"b_name"})
    private String bName;
}
1
2
3
4
5
6
7
8
9

4、业务代码

先说下解题思路：

1、首先从excel表中获取a，b表的name集合
2、再将这两个集合利用双循环遍历判断，字符串匹配
3、最后将匹配好的集合存入excel表中
1
2
3

1、获取excel表格数据

    //从excel中读取数据
    public List<aData> getExcelData(String fileName, List<aData> dataList, int sheet) {

        ReadListener<aData> listener = new ReadListener<aData>() {
            @Override
            public void onException(Exception exception, AnalysisContext context) {
                // 异常处理逻辑
            }

            @Override
            public void invoke(aData data, AnalysisContext context) {
                dataList.add(data); // 将数据添加到List集合中
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                // 所有数据读取完成后的逻辑
            }
        };

        //sheet()读哪个工作表
        EasyExcel.read(fileName, aData.class, listener).sheet(sheet).doRead();

        return dataList;

    }
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

这里要注意的是 aData.class这个对应excel表格的列

而aData为

@Data
@NoArgsConstructor
@AllArgsConstructor
public class aData {
    private String aName;
}
1
2
3
4
5
6

2、通过匹配得到a_b_name集合

匹配思路：
    1、获取两个字符串的Jaccard 相似度（这个Jaccard 相似度指两个集合交集和并集的比值）
    2、Jaccard 相似度最大的添加到集合中  
1
2
3

将字符串切割为单词集合
Hashset集合：无序且不重复，提供了两个方法来获取交并集，分别是retainAll和addAll

​ retainAll：交集

​ addAll：并集

举例：

Set<Integer> set1 = new HashSet<>(Arrays.asList(1, 2, 3, 4));
Set<Integer> set2 = new HashSet<>(Arrays.asList(3, 4, 5, 6));

boolean changed = set1.retainAll(set2);
System.out.println(set1);      // 输出: [3, 4]
System.out.println(changed);   // 输出: true


Set<Integer> set1 = new HashSet<>(Arrays.asList(1, 2, 3));
Set<Integer> set2 = new HashSet<>(Arrays.asList(3, 4, 5, 6));

boolean changed = set1.addAll(set2);
System.out.println(set1);      // 输出: [1, 2, 3, 4, 5, 6]
System.out.println(changed);   // 输出: true
1
2
3
4
5
6
7
8
9
10
11
12
13
14

匹配相似字符串的的代码

// 计算 Jaccard 相似度
    private static double calculateJaccardSimilarity(String str1, String str2) {
        Set<String> set1 = new HashSet<>(Arrays.asList(splitString(str1)));
        Set<String> set2 = new HashSet<>(Arrays.asList(splitString(str2)));

        Set<String> intersection = new HashSet<>(set1);
        intersection.retainAll(set2);

        Set<String> union = new HashSet<>(set1);
        union.addAll(set2);

        return (double) intersection.size() / union.size();
    }

    // 切分字符串为单词集合
    private static String[] splitString(String str) {
        return str.split("\\s+");
    }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

3、创建表并导入匹配后的数据

如果fileName不存在表则会自动创建excel表

 public void buildSheet(String fileName, List<abData> abData, String sheetName) {
        // 这里 需要指定写用哪个class去写
        try (ExcelWriter excelWriter = EasyExcel.write(fileName, abData.class).build()) {
            // 这里注意 如果同一个sheet只要创建一次
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
            excelWriter.write(abData, writeSheet);
        }
    }
1
2
3
4
5
6
7
8

4、最后调用方法

注意这里的fileName如果你是部署到服务其中要改为

    void text(){
        //如果不存在则创建
        String fileName = "D:\\spring-blp\\NameMatching\\src\\main\\resources\\static\\车型名称映射.xlsx"; // Excel文件路径

        List<aData> aDataList = new ArrayList<>(); // 存储数据的List集合
        List<aData> bDataList = new ArrayList<>(); // 存储数据的List集合

        aDataList = getExcelData(fileName, aDataList, 0);
        bDataList = getExcelData(fileName, bDataList, 1);

        List<abData> dataList = new ArrayList<>();

        for (int j = 0; j < aDataList.size(); j++) {
            double max=0;
            int index = 0;
            String aName = aDataList.get(j).getAName();

            for (int i = 0; i < bDataList.size(); i++) {
                String bName = bDataList.get(i).getAName();
                double v = calculateJaccardSimilarity(aName, bName);
                if (v==1){
                    dataList.add(new abData(aName,bDataList.get(i).getAName()));
                    break;
                }
                if (max<v){
                    max=v;
                    index=i;
                }

            }
            dataList.add(new abData(aName,bDataList.get(index).getAName()));
        }

        String fileNameAB = "D:\\spring-blp\\NameMatching\\src\\main\\resources\\static\\abName.xlsx"; // Excel文件路径
        buildSheet(fileNameAB,dataList,"a_b_name");

    }
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

5、运行获得结果