EasyExcel识别表头

本文将介绍使用EasyExcel实现识别单行表头的一种实现方式。主要是通过实现AnalysisEventListener 抽象类,并覆写其 hasNext 和 invokeHead 方法。

Jar依赖

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

<dependency>
	<groupId>org.reflections</groupId>
	<artifactId>reflections</artifactId>
	<version>0.10.2</version>
</dependency>

实现代码

一个表头基类和2个子类,一个用于表头字段的类。

注意:代码使用反射,使用了@ExcelProperty(value = “地址”, index = 0)注解,index用于表头字段排序,value用于表头显示的名称。

1.表头基类
package top.jyokiyi.my.test.excel;
import lombok.Data;

@Data
public class ExcelBaseModel {
}
2.表头实现类1

继承表头基类:

package top.jyokiyi.my.test.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class ExcelHeadTest1Model extends ExcelBaseModel {

    @ExcelProperty(value = "地址", index = 0)
    private String address;

    @ExcelProperty(value = "名称", index = 1)
    private String name;
}
3.表头实现类2

继承表头基类:

package top.jyokiyi.my.test.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class ExcelHeadTest2Model extends ExcelBaseModel {

    @ExcelProperty(value = "邮件",index = 0)
    private String email;

    @ExcelProperty(value = "内容", index = 1)
    private String content;
}
4. 表头字段类
package top.jyokiyi.my.test.excel;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@Builder
@NoArgsConstructor
@AllArgsConstructor
@Data
public class ExcelHeadFieldModel {
    /**
     * 列号
     */
    private Integer column;
    /**
     * 表头描述
     */
    private String headDesc;
}
5. 使用Reflections实现查找所有子类

注意:这里直接指定了包名,且子类和基类在同一个包中。(如有需要可自行修改,以实现自己的需求)


/**
 * 获取指定类的所有子类
 * @param clazz
 * @param <T>
 * @return
 */
public static <T>Set<Class<? extends T>> getAllSubTypes(Class<T> clazz) {
	Set<Class<? extends T>> subTypeClassSet = new HashSet<>();

	Reflections reflections = new Reflections("top.jyokiyi.my.test.excel");
	Set<Class<? extends T>> subTypesOf = reflections.getSubTypesOf(clazz);
	if (CollectionUtils.isNotEmpty(subTypesOf)) {
		subTypeClassSet.addAll(subTypesOf);
	}

	return subTypeClassSet;
}
6.表头类型与表头字段集合映射
/**
 * 将表头与对于的类映射起来
 * @param sets
 * @param <T>
 * @return
 */
public static <T>Map<Class<? extends T>, List<ExcelHeadFieldModel>> getHeadMapForExcelProperty(Set<Class<? extends T>> sets) {
	Map<Class<? extends T>, List<ExcelHeadFieldModel>> maps = new HashMap<>();

	for (Class<? extends T> clz : sets) {
		List<ExcelHeadFieldModel> fieldModelList = new ArrayList<>();
		try {
			Field[] fields = clz.getDeclaredFields();
			for (Field field : fields) {
				if (field.isAnnotationPresent(ExcelProperty.class)) {
					ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
					fieldModelList.add(ExcelHeadFieldModel.builder()
							.column(annotation.index()).headDesc(annotation.value()[0]).build());
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (CollectionUtils.isNotEmpty(fieldModelList)) {
			maps.put(clz, fieldModelList.stream()
                       .sorted(Comparator.comparing(ExcelHeadFieldModel::getColumn))
                       .collect(Collectors.toList()));
		}
	}
	return maps;
}
7.实现监听类

继承easy excel 的 AnalysisEventListener 抽象类,并覆写其 hasNext 和 invokeHead 方法。

package top.jyokiyi.my.test.excel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.util.StringUtils;

import java.util.Comparator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

public class ExcelHeaderIdentifyListener extends AnalysisEventListener<Map<Integer, String>> {

    private static ThreadLocal<Class<? extends ExcelBaseModel>> classResult = new ThreadLocal<>();

    public ThreadLocal<Class<? extends ExcelBaseModel>> getClassResult() {
        return classResult;
    }

    public void clear() {
        classResult.remove();
    }

    /**
     * 返回false,读取一行后,不再继续读取
     *
     * @param context
     * @return
     */
    @Override
    public boolean hasNext(AnalysisContext context) {
        return false;
    }

    /**
     * 根据表头解析获取对应的表头子类
     * @param headMap
     * @param context
     */
    @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
        List<ExcelHeadFieldModel> headFieldModels = headMap.values().stream().map(column -> ExcelHeadFieldModel.builder()
                .column(column.getColumnIndex()).headDesc(column.getStringValue()).build())
                .sorted(Comparator.comparing(ExcelHeadFieldModel::getColumn))
                .collect(Collectors.toList());
        // 将表头字段拼接起来
        String joinStr = headFieldModels.stream()
                .map(ExcelHeadFieldModel::getHeadDesc)
                .collect(Collectors.joining());

        // 获取表头基类的所有子类信息
        Set<Class<? extends ExcelBaseModel>> allSubTypes = ClassUtils.getAllSubTypes(ExcelBaseModel.class);
        // 将class和表头映射起来
        Map<Class<? extends ExcelBaseModel>, List<ExcelHeadFieldModel>> listMap = ClassUtils.getHeadMapForExcelProperty(allSubTypes);
        // 查找对应的子类
        for (Map.Entry<Class<? extends ExcelBaseModel>, List<ExcelHeadFieldModel>> entry : listMap.entrySet()) {
            if (entry.getValue().size() == headFieldModels.size() && StringUtils.equals(joinStr, entry.getValue()
                    .stream().map(ExcelHeadFieldModel::getHeadDesc).collect(Collectors.joining()))) {
                classResult.set(entry.getKey());
                break;
            }
        }
    }
    

    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) {

    }


    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {

    }
}
8.测试
package top.jyokiyi.my.test.excel;

import com.alibaba.excel.EasyExcel;

import java.io.File;
import java.io.FileInputStream;

public class ReadTest {


    public static void main(String[] args) throws Exception {
        ExcelHeaderIdentifyListener listener = new ExcelHeaderIdentifyListener();
        String dir = System.getProperty("user.dir");
        String filePath = dir + File.separator + "src" + File.separator + "test_excel.xlsx";

        EasyExcel.read(new FileInputStream(filePath), listener)
                 .sheet().doRead();

        Class<? extends ExcelBaseModel> aClass = listener.getClassResult();
        listener.clear();
        System.out.println(aClass.getName());
    }
}

测试结果:

top.jyokiyi.my.test.excel.ExcelHeadTest1Model

测试文件的表头如下:

地址名称
12

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注