使用自定义注解 + 反射 + 泛型 + 封装,实现统一读取
自定义注解,Excel 作用于属性-Excel 列名,ExcelTarget 作用于类-ExcelSheet名
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();
}
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());
}
}
使用
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();
}
}
实体定义文章来源:https://www.toymoban.com/news/detail-758952.html
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;
}
源码:https://gitee.com/zhaomingjian/workspace_luoan_demo/tree/master/excel-tool/src/main/java/com/excel/tool/putuo文章来源地址https://www.toymoban.com/news/detail-758952.html
到了这里,关于【工具类】Excel 多 Sheet 导入工具类的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!