【工具类】Excel 多 Sheet 导入工具类

使用自定义注解 + 反射 + 泛型 + 封装，实现统一读取

自定义注解，Excel 作用于属性-Excel 列名，ExcelTarget 作用于类-ExcelSheet名

java">import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface Excel {
    String name();
}

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE})
public @interface ExcelTarget {
    String name();
}

java">import com.excel.tool.putuo.annotation.Excel;
import com.excel.tool.putuo.annotation.ExcelTarget;
import cn.hutool.core.annotation.AnnotationUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 【工具类】Excel 多 Sheet 导入工具类
 *
 * @author jason
 */
public class ExcelSheetUtil {

    /**
     * 获取 Sheet 名字
     */
    public static <T> String getSheetName(Class<T> clazz) {
        return AnnotationUtil.getAnnotationValue(clazz, ExcelTarget.class, "name");
    }

    /**
     * 读取多个sheet
     *
     * @param inputStream
     * @param classList
     * @return
     */
    public static Map<String, List<?>> readExcelList(InputStream inputStream, List<Class<?>> classList) {
        Map<String, List<?>> sheetMap = new HashMap<>();

        // 解决 InputStream 流只能读一次的问题，复制一个 ByteArrayOutputStream
        ByteArrayOutputStream cacheInputStream = new ByteArrayOutputStream();
        IoUtil.write(cacheInputStream, true, IoUtil.readBytes(inputStream));
        classList.forEach(tClass -> {
            String sheetName = AnnotationUtil.getAnnotationValue(tClass, ExcelTarget.class, "name");
            InputStream inputStreamSingleton = new ByteArrayInputStream(cacheInputStream.toByteArray());
            List<?> excelData3List = readExcelList(sheetName, inputStreamSingleton, tClass);
            sheetMap.put(sheetName, excelData3List);
        });
        return sheetMap;
    }

    /**
     * 读取单个sheet
     *
     * @param sheetName
     * @param inputStream
     * @param clazz
     * @param <T>
     * @return
     */
    public static <T> List<T> readExcelList(String sheetName, InputStream inputStream, Class<T> clazz) {
        ExcelReader excelReader = ExcelUtil.getReader(inputStream, sheetName);
        List<Map<String, Object>> mapList = excelReader.read(0, 0, Integer.MAX_VALUE);
        excelReader.close();
        return Optional.ofNullable(mapList)
                .orElse(new ArrayList<>())
                .stream()
                .map(itemMap -> {
                    T excelData = ReflectUtil.newInstance(clazz);
                    Field[] fields = ReflectUtil.getFields(clazz);
                    for (Field field : fields) {
                        String key = AnnotationUtil.getAnnotationValue(field, Excel.class, "name");
                        String value = StrUtil.toStringOrNull(itemMap.get(key));
                        ReflectUtil.setFieldValue(excelData, field, value);
                    }
                    return excelData;
                })
                .collect(Collectors.toList());
    }

}

使用

java">import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.io.FileUtil;
import com.excel.tool.putuo.excel.ExcelData0;
import com.excel.tool.putuo.excel.ExcelData1;
import com.excel.tool.putuo.excel.ExcelData2;
import com.excel.tool.putuo.excel.ExcelData3;
import com.excel.tool.putuo.util.ExcelSheetUtil;
import lombok.SneakyThrows;

import java.io.File;
import java.io.InputStream;
import java.util.List;
import java.util.Map;

public class TestMain {

    public static void main(String[] args) {
        File xlsx = FileUtil.file("数据11.21.xlsx");

        TestMain.startExcel(FileUtil.getInputStream(xlsx));
    }

    @SneakyThrows
    public static <T> void startExcel(InputStream inputStream) {
        List<Class<?>> classList = CollectionUtil.newArrayList(
                ExcelData0.class,
                ExcelData1.class,

                ExcelData2.class,
                ExcelData3.class
        );

        Map<String, List<?>> sheetMap = ExcelSheetUtil.readExcelList(inputStream, classList);

        List<ExcelData0> excelData0List = (List<ExcelData0>) sheetMap.get(ExcelSheetUtil.getSheetName(ExcelData0.class));
        List<ExcelData1> excelData1List = (List<ExcelData1>) sheetMap.get(ExcelSheetUtil.getSheetName(ExcelData1.class));
        List<ExcelData2> excelData2List = (List<ExcelData2>) sheetMap.get(ExcelSheetUtil.getSheetName(ExcelData2.class));
        List<ExcelData3> excelData3List = (List<ExcelData3>) sheetMap.get(ExcelSheetUtil.getSheetName(ExcelData3.class));

        System.out.println();
    }

}

实体定义

java">import com.excel.tool.putuo.annotation.Excel;
import com.excel.tool.putuo.annotation.ExcelTarget;
import lombok.Data;
import lombok.experimental.Accessors;

@Data
@Accessors(chain = true)
@ExcelTarget(name = "整表")
public class ExcelData0 {

    @Excel(name = "序号")
    private String num;

    @Excel(name = "来源")
    private String mediaType;

    @Excel(name = "发布时间")
    private String publishTime;

    @Excel(name = "作者")
    private String author;

    @Excel(name = "标题")
    private String title;

    @Excel(name = "摘要")
    private String digest;

    @Excel(name = "原文链接")
    private String link;

}