文章目录
需求
这种样子的表格导入,后端处理转为实体
这里每行代表一条数据,每条数据可能会有多个地理信息数据,多个的累加在每行的后面
说明
此工具目前只适用于先用工程,也就是上述所成列的表格导入样式导入,也适用于一级表头,
其他的话在实现时没有考虑,
此工具可供参考,有其他规则的自行更改,以下说明有不清楚的参数可以讨论(因为这个参数我写的都不清楚。。。。。。自己用,将就下)
涉及技术
easyexcel,反射,自定义注解,泛型类
实现
easy excel 导入数据工具
监听
package com.maphao.manage.utils;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
/**
* @Author: fxp
* @Date: 2022/12/7 9:41
* @Description
*/
@Slf4j
public class ExcelListener extends AnalysisEventListener {
/**
* 批处理阈值
*/
private static final int BATCH_COUNT = 100;
List<ArrayList<String>> list = new ArrayList<>(BATCH_COUNT);
List<ArrayList<String>> datas = new ArrayList<>();
@Override
public void invoke(Object excelItem, AnalysisContext analysisContext) {
ArrayList<String> ss = (ArrayList<String>) excelItem;
list.add(ss);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
importItemInfo(list);
}
public void importItemInfo(List<ArrayList<String>> infoList) {
}
public List<ArrayList<String>> getData(){
List<ArrayList<String>> datas = new ArrayList<>();
datas.addAll(list);
list.clear();
return datas;
}
public List<List<String>> getDataList(){
List<List<String>> datas = new ArrayList<>();
datas.addAll(list);
list.clear();
return datas;
}
}
控制层接收文件,并调用方法处理数据
这里调用的时候要给定一个泛型
NavigationalAidsExcel 这个就是后面我们建立的实体,这里作为泛型
public void portImport() throws IOException, InstantiationException, IllegalAccessException {
List<UploadFile> files = getFiles();
ExcelUtil excelUtil = new ExcelUtil<NavigationalAidsExcel>(){};
for (UploadFile file : files) {
InputStream fis = new FileInputStream(file.getFile());
List<ArrayList<String>> arrayLists = excelUtil.myImport(fis, 1, 0);
// List<NavigationalAidsExcel> list = excelUtil.listToBean(arrayLists, 2);
List list = excelUtil.multiObjectListToBean(arrayLists, "基础信息数据");
System.out.println("list = " + list);
}
}
通过下面代码获取数据,这部分代码在 ExcelUtil 中,后面会附上代码
FileInputStream fileInputStream=new FileInputStream(file);
InputStream inputStream = fileInputStream;
//实例化实现了AnalysisEventListener接口的类
ExcelListener listener = new ExcelListener();
//传入参数
ExcelReader excelReader = new ExcelReader(inputStream, ExcelTypeEnum.XLSX, null,listener);
//读取信息
excelReader.read(new Sheet(sheetNo,headLineMun));
//获取数据
List<List<String>> list = listener.getDataList();
return list;
实体类,加入自定义注解
新增自定义注解
作用于实体类的属性上
value 代表该属性对应的表头
target 代表该属性为应用类型,值为引用类型的calss,有这个字段方便后面通过反射生成该类型实体对象
package com.maphao.manage.utils.bean;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* @Author: fxp
* @Date: 2023/3/7 13:44
* @Description
*/
@Target({ElementType.METHOD, ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface TableAnnotation {
String value() default "";
Class target() default Object.class;
}
实体类
package com.maphao.manage.domain.excel;
import com.maphao.manage.utils.bean.TableAnnotation;
import lombok.Data;
import java.math.BigDecimal;
import java.util.List;
/**
* @Author: fxp
* @Date: 2023/3/7 13:38
* @Description
*/
@Data
public class NavigationalAidsExcel{
/**
* 所属部门
*/
@TableAnnotation("所属海事机构")
private String dept_guid;
/**
* 中文名
*/
@TableAnnotation("中文名称")
private String ch_name;
/**
* 英文名
*/
@TableAnnotation("英文名称")
private String en_name;
/**
* 备注
*/
@TableAnnotation("备注")
private String remark;
/**
* 种类
*/
@TableAnnotation("种类")
private String type;
/**
* 灯高(米)
*/
@TableAnnotation("灯高(米)")
private BigDecimal lampHeight;
/**
* 标高(米)
*/
@TableAnnotation("标高(米)")
private BigDecimal elevation;
/**
* 射程(海里)
*/
@TableAnnotation("射程(海里)")
private BigDecimal lampRange;
/**
* 灯质
*/
@TableAnnotation("灯质")
private String lampQuality;
/**
* 能源
*/
@TableAnnotation("能源")
private String energy;
/**
* 结构
*/
@TableAnnotation("结构")
private String structure;
/**
* 设置地点
*/
@TableAnnotation("设置地点")
private String address;
/**
* 地理信息数据
*/
@TableAnnotation(value = "地理信息数据",target = GeometryExcel.class)
private List<GeometryExcel> geometry;
}
表格工具,将监听的数据转为实体
这里中心方法就是 multiObjectListToBean,也就是上面所写的控制层调用的方法
这里同时也是支持了实体继承
在介绍下参数意思
List<List>list:
导入时,通过监听得到的数据集合,List<List> list,第一个 list 装的是每一行数据,第二个 list 装的是每一列数据*(这个很好理解吧,表格本身就是一个二维数组,这里也能用二维数组,之前在写导出的时候用的就是二维数组,所有这里选用这个)*
String beanKey:
这个字段就是告诉我,在哪个一级目录下的字段是该实体的基本属性
泛型 T:
这个就是我们前面写的实体
package com.maphao.manage.utils;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.jfinal.kit.StrKit;
import com.maphao.manage.domain.entity.metadata.gangkou.BerthImportVo;
import com.maphao.manage.utils.bean.TableAnnotation;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.*;
/**
* @Author: fxp
* @Date: 2022/12/7 9:36
* @Description
*/
public abstract class ExcelUtil<T> {
public static final List<String> TABLE_FORMAT = new ArrayList<String>(){
{
add("xlsx");
add("xls");
}
};
public Class<T> getTClass()
{
Class<T> tClass = (Class<T>)((ParameterizedType)getClass().getGenericSuperclass()).getActualTypeArguments()[0];
return tClass;
}
public List<List<String>> myImport(File file,int sheetNo,int headLineMun) throws IOException {
FileInputStream fileInputStream=new FileInputStream(file);
InputStream inputStream = fileInputStream;
//实例化实现了AnalysisEventListener接口的类
ExcelListener listener = new ExcelListener();
//传入参数
ExcelReader excelReader = new ExcelReader(inputStream, ExcelTypeEnum.XLSX, null,listener);
//读取信息
excelReader.read(new Sheet(sheetNo,headLineMun));
//获取数据
List<List<String>> list = listener.getDataList();
return list;
}
/**
*
* @param inputStream
* @param sheetNo 读取第几个页签
* @param headLineMun 从第几行开始读
* @return
* @throws IOException
*/
public List<List<String>> myImport(InputStream inputStream,int sheetNo,int headLineMun) throws IOException {
//实例化实现了AnalysisEventListener接口的类
ExcelListener listener = new ExcelListener();
//传入参数
ExcelReader excelReader = new ExcelReader(inputStream, ExcelTypeEnum.XLSX, null,listener);
//读取信息
excelReader.read(new Sheet(sheetNo,headLineMun));
//获取数据
List<List<String>> list = listener.getDataList();
return list;
}
/**
* 获取excel文件所有sheet页
* @date 2022/11/25 17:13
* @param inputStream 文件流
* @return java.util.List<com.alibaba.excel.read.metadata.ReadSheet>
*/
public static List<ReadSheet> listSheet(InputStream inputStream){
if(inputStream == null){
throw new RuntimeException("inputStream is null");
}
ExcelReader build = EasyExcel.read(inputStream).build();
List<ReadSheet> readSheets = build.excelExecutor().sheetList();
return readSheets;
}
public Boolean judgeFormat(String s){
return TABLE_FORMAT.contains(s);
}
/**
*
* @param list 表格解析的集合
* @param headIndex 表头的结束位置 (表头有三行就输入3)
* @throws IllegalAccessException
*/
public List<T> listToBean(List<List<String>> list,Integer headIndex) throws IllegalAccessException, InstantiationException {
List<T> ds = new ArrayList<>();
List<List<String>> headList = list.subList(0, headIndex-1);
List<List<String>> dataList = list.subList(headIndex-1,list.size());
Map<String, Integer> map = listGetHeadMap(headList);
Class cl = getTClass();
for (int i = 0; i < dataList.size(); i++) {
T o1 = (T) cl.newInstance();
List<String> strings = dataList.get(i);
Class<?> aClass = o1.getClass();
//给自己赋值
setValue(o1,aClass,map,strings);
Class<?> superClass = aClass.getSuperclass();
//给父类赋值
setValue(o1,superClass,map,strings);
ds.add(o1);
}
return ds;
}
/**
* 多对象集合(适用于二级表头,知道表头种类)
* @param list 表格解析的集合
* @param beanKey 主体的一级标题
* @throws IllegalAccessException
*/
public List<T> multiObjectListToBean(List<List<String>> list,String beanKey) throws IllegalAccessException, InstantiationException {
List<T> ds = new ArrayList<>();
List<String> firstHead = list.get(0);
List<List<String>> headList = list.subList(0, 2);
List<List<String>> dataList = list.subList(1,list.size());
Map<String,List<List<List<String>>>> beanMap = new HashMap<>(10);
int previousNumber = 0;
Map<String,Map<String,Integer>> maps = new HashMap<>(10);
String s = "";
Map<String,List<int[]>> orderMap = new HashMap<>(10);
for (int i = 0; i < firstHead.size(); i++) {
if (i == 0 && null != firstHead.get(i)){
s = firstHead.get(i);
continue;
}else {
if (null == firstHead.get(i)){
continue;
}else {
List<int[]> ints = orderMap.get(s);
if (null == ints){
ints = new ArrayList<>();
}
int[] order = new int[2];
order[0] = previousNumber;
order[1] = i;
ints.add(order);
orderMap.put(s,ints);
previousNumber = i;
s = firstHead.get(i);
if (i == firstHead.size()-1){
List<int[]> ints0 = orderMap.get(s);
if (null == ints0){
ints0 = new ArrayList<>();
}
int[] order0 = new int[2];
order0[0] = previousNumber;
order0[1] = dataList.get(0).size();
ints0.add(order0);
orderMap.put(s,ints0);
}
}
}
}
for (String s1 : orderMap.keySet()) {
List<int[]> ints = orderMap.get(s1);
List<List<List<String>>> lists = beanMap.get(s1);
if (null == lists){
lists = new ArrayList<>();
}
for (List<String> strings : dataList) {
List<List<String>> list1 = new ArrayList<>();
for (int[] anInt : ints) {
list1.add(strings.subList(anInt[0],anInt[1]));
}
lists.add(list1);
}
beanMap.put(s1,lists);
}
for (String s1 : beanMap.keySet()) {
List<List<List<String>>> lists = beanMap.get(s1);
Map<String, Integer> map = listGetHeadMap(lists.get(0));
maps.put(s1,map);
}
Class cl = getTClass();
for (int i = 1; i < dataList.size(); i++) {
T o1 = (T) cl.newInstance();
Class<?> aClass = o1.getClass();
//给自己赋值
setValueByMaps(o1,aClass,maps,beanKey,i,beanMap,0);
Class<?> superClass = aClass.getSuperclass();
//给父类赋值
setValueByMaps(o1,superClass,maps,beanKey,i,beanMap,0);
ds.add(o1);
}
return ds;
}
public void setValueByMaps(Object o1,Class aClass,Map<String,Map<String, Integer>> maps,String beanKey,int i,Map<String,List<List<List<String>>>> beanMap,int num) throws IllegalAccessException, InstantiationException {
// Set<String> strings1 = maps.keySet();
Field[] declaredFields = aClass.getDeclaredFields();
for (Field declaredField : declaredFields) {
TableAnnotation annotation = declaredField.getAnnotation(TableAnnotation.class);
if (null != annotation) {
String value = annotation.value();
Class target = annotation.target();
System.out.println("value() = " + value);
System.out.println("target() = " + target);
if (StrKit.isBlank(annotation.value())){
continue;
}
if (Object.class.equals(target)){
System.out.println("自有属性 value() = " + value);
List<List<List<String>>> Lists = beanMap.get(beanKey);
List<List<String>> strings = Lists.get(i);
Map<String,Integer> map = maps.get(beanKey);
Integer integer = map.get(annotation.value());
String s = strings.get(num).get(integer);
//取消私有方法限制
declaredField.setAccessible(true);
Class<?> type = declaredField.getType();
Object o = stringToObjectByType(s, type,declaredField);
System.out.println("o = " + o);
declaredField.set(o1,o);
}else {
System.out.println("对象属性 value() = " + value);
List<List<List<String>>> Lists = beanMap.get(value);
List<List<String>> strings = Lists.get(i);
//取消私有方法限制
declaredField.setAccessible(true);
Class<?> type = declaredField.getType();
if (List.class.equals(type)){
List<Object> list = new ArrayList<>();
for (int j = 0; j < strings.size(); j++) {
Object o = target.newInstance();
setValueByMaps(o,target,maps,value,i,beanMap,j);
System.out.println("o = " + o);
list.add(o);
}
declaredField.set(o1,list);
}else {
Object o = target.newInstance();
setValueByMaps(o,target,maps,value,i,beanMap,0);
System.out.println("o = " + o);
declaredField.set(o1,o);
}
}
}
}
}
public void setValue(Object o1,Class aClass,Map<String, Integer> map,List<String> strings) throws IllegalAccessException, InstantiationException {
Field[] declaredFields = aClass.getDeclaredFields();
for (Field declaredField : declaredFields) {
TableAnnotation annotation = declaredField.getAnnotation(TableAnnotation.class);
if (null != annotation) {
System.out.println("value() = " + annotation.value());
if (StrKit.isBlank(annotation.value())){
continue;
}
Integer integer = map.get(annotation.value());
String s = strings.get(integer);
//取消私有方法限制
declaredField.setAccessible(true);
Class<?> type = declaredField.getType();
Object o = stringToObjectByType(s, type,declaredField);
System.out.println("o = " + o);
declaredField.set(o1,o);
}
}
}
public Object stringToObjectByType(String s,Class type,Field field) throws IllegalAccessException, InstantiationException {
if (null == s){
return null;
}
if (String.class.equals(type)){
return s;
}
if (Integer.class.equals(type)){
return Integer.parseInt(s);
}
if (BigDecimal.class.equals(type)){
return new BigDecimal(s);
}
if (Boolean.class.equals(type)){
return "是".equals(s) || "t".equals(s) || "true".equals(s) || "yes".equals(s) || "Y".equals(s);
}
if (Date.class.equals(type)){
return new Date(s);
}
if (List.class.equals(type) && null != field){
// 当前集合的泛型类型
Type genericType = field.getGenericType();
System.out.println("genericType = " + genericType);
if (genericType instanceof ParameterizedType) {
ParameterizedType pt = (ParameterizedType) genericType;
// 得到泛型里的class类型对象
Class<?> actualTypeArgument = (Class<?>)pt.getActualTypeArguments()[0];
List<Object> curEleList = new ArrayList<>();
String[] split = s.split(",");
for (String s1 : split) {
Object o = stringToObjectByType(s1, actualTypeArgument, null);
curEleList.add(o);
}
return curEleList;
}
}
return null;
}
public Map<String,Integer> listGetHeadMap(List<List<String>> headList){
Map<String,Integer> map = new HashMap<>(10);
List<String> strings = headList.get(headList.size() - 1);
for (int i = 0; i < strings.size(); i++) {
map.put(strings.get(i),i);
}
return map;
}
public Map<String,Integer> listGetAllNodesHeadMap(List<List<String>> headList){
Map<String,Integer> map = new HashMap<>(10);
for (int i = 0; i < headList.size(); i++) {
List<String> strings = headList.get(i);
for (int j = 0; j < strings.size(); j++) {
if (i > 0 && j == 0 && null == strings.get(j)){
strings.set(j,headList.get(i-1).get(j));
}else if (j > 0 && null == strings.get(j)){
strings.set(j,strings.get(j-1));
}
}
}
List<String> strings = headList.get(headList.size() - 1);
for (int i = 0; i < strings.size(); i++) {
map.put(strings.get(i),i);
StringBuilder s = new StringBuilder("");
for (int j = 0; j < headList.size()-2; j++) {
s.append(headList.get(j).get(i) + ".");
}
s.append(strings.get(i));
map.put(s.toString(),i);
}
return map;
}
}