动态模板导入实体

最新推荐文章于 2024-08-06 10:22:43 发布

张含韵好可爱

最新推荐文章于 2024-08-06 10:22:43 发布

阅读量116

点赞数

分类专栏：前后端接口文章标签： java java-ee spring

本文链接：https://blog.csdn.net/weixin_44570196/article/details/125261149

版权

前后端接口专栏收录该内容

2 篇文章 0 订阅

订阅专栏

动态模板导入实体

涉及关键类

类名	描述
AdapterReturnMessage	字段校验返回结果类
AttributeName	字段对应列名注解
ExcelVoAdapter	字段校验超类
ReadExcel	对excel做校验的工具类（关键类）
StringUtils	对字符串做校验的工具类
TemplatePo	模板Vo，vo需要继承的类
User	实列PO
UserVo	实列Vo
UserVoAdapter	实列校验类

UserVoAdapter校验类，方法命名规则为：实列Vo属性名+Adapter,注意查看实列；

实现思路

在这里插入图片描述

涉及依赖

 <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.2</version>
        </dependency>

具体实现

1.AttributeName

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 AttributeName {
    public String value() default "";
}

2.AdapterReturnMessage

import java.io.Serializable;

/**
 * @ClassName AdapterReturnMessage
 * @Description TODO
 * @Version 1.0
 */

public class AdapterReturnMessage implements Serializable {
    private static final long serialVersionUID = -3582477728984396465L;

    /**
     * 返回状态
     */
    private int status;
    /**
     * 返回数据或者消息
     */
    private Object message;
    public int getStatus() {
        return status;
    }
    public void setStatus(int status) {
        this.status = status;
    }
    public Object getMessage() {
        return message;
    }
    public void setMessage(Object message) {
        this.message = message;
    }
}

3.ExcelVoAdapter

public class ExcelVoAdapter {
    AdapterReturnMessage adapterReturnMessage;
}

4.StringUtils


import java.io.Serializable;
import java.util.Objects;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @ClassName StringUtils
 * @Description 字符串工具类
 * @Version 1.0
 */

public class StringUtils implements Serializable {
    private static final long serialVersionUID = -8222775877681330255L;
    private static Pattern pattern = Pattern.compile("-?[0-9]+(\\\\.[0-9]+)?");

    /**
     * 通过正则表达式判断字符串是否为数字
     * @param str
     * @return
     */
    public static boolean isNumber(String str) {
        // 通过Matcher进行字符串匹配
        Matcher m = pattern.matcher(str);
        // 如果正则匹配通过 m.matches() 方法返回 true ，反之 false
        return m.matches();
    }

    /**
     * 判断字符串是否是空
     * @param str
     * @return
     */
    public static boolean isNull(String str) {
        return !(Objects.nonNull(str) && str.trim().length() > 0);
    }

}

5.TemplatePo

import java.io.Serializable;

/**
 * @ClassName TemplatePo
 * @Description TODO
 * @Version 1.0
 */

public abstract class TemplatePo<T> implements Serializable {
    private static final long serialVersionUID = 4396383350565943517L;
    private AdapterReturnMessage adapterReturnMessage;
    public  T po;

    public abstract T nwePo();
    public T getPo(){
        return po;
    }


    public abstract void setPo(T t);

    public AdapterReturnMessage getAdapterReturnMessage() {
        return adapterReturnMessage;
    }

    public void setAdapterReturnMessage(AdapterReturnMessage adapterReturnMessage) {
        this.adapterReturnMessage = adapterReturnMessage;
    }
}

6.User

package com.excellent.importExcel;

import java.io.Serializable;

/**
 * @ClassName User
 * @Description TODO
 * @Version 1.0
 */

public class User implements Serializable {
    private static final long serialVersionUID = -5735964634195165696L;
    private String name;
    private String code;
    private String loginName;
    private Integer sort;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getLoginName() {
        return loginName;
    }

    public void setLoginName(String loginName) {
        this.loginName = loginName;
    }

    public Integer getSort() {
        return sort;
    }

    public void setSort(Integer sort) {
        this.sort = sort;
    }
}

7.UserVo

package com.excellent.importExcel;

import java.io.Serializable;

/**
 * @ClassName UserVo
 * @Description TODO
 * @Version 1.0
 */

public class UserVo extends TemplatePo<User> implements Serializable {
    private static final long serialVersionUID = -5856364608311260336L;
    @AttributeName("姓名")
    private String name;
    @AttributeName("人员编号")
    private String code;
    @AttributeName("登录名")
    private String loginName;
    @AttributeName("人员排序号")
    private Integer sort;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getLoginName() {
        return loginName;
    }

    public void setLoginName(String loginName) {
        this.loginName = loginName;
    }

    public Integer getSort() {
        return sort;
    }

    public void setSort(Integer sort) {
        this.sort = sort;
    }

    @Override
    public User nwePo() {
        return new User();
    }

    @Override
    public void setPo(User user) {
       super.po = user;
    }

}

8.UserVoAdapter

import java.util.Objects;

/**
 * @ClassName UserVoAdapter
 * @Description 对UserVo的所有属性做数据校验的实现类
 * adapterReturnMessage status为0时认为校验成功，并且message 返回类型为传入po需要类型
 * @Version 1.0
 */

public class UserVoAdapter extends ExcelVoAdapter{

    {
        if (Objects.isNull(this.adapterReturnMessage)) {
            this.adapterReturnMessage = new AdapterReturnMessage();
        }
    }

    /**
     * 姓名校验
     * @param name
     * @return
     */
    private AdapterReturnMessage nameAdapter(String name) {
        if (StringUtils.isNull(name)) {
            this.adapterReturnMessage.setStatus(1);
            this.adapterReturnMessage.setMessage("姓名不能为空");
        }else {
            this.adapterReturnMessage.setStatus(0);
            this.adapterReturnMessage.setMessage(name);
        }
        return this.adapterReturnMessage;
    }

    /**
     * 姓名校验
     * @param code
     * @return
     */
    private AdapterReturnMessage codeAdapter(String code) {
        if (StringUtils.isNull(code)) {
            this.adapterReturnMessage.setStatus(1);
            this.adapterReturnMessage.setMessage("人员编号不能为空");
        }else {
            this.adapterReturnMessage.setStatus(0);
            this.adapterReturnMessage.setMessage(code);
        }
        return this.adapterReturnMessage;
    }

    /**
     * 登录名
     * @param loginName
     * @return
     */
    private AdapterReturnMessage loginNameAdapter(String loginName) {
        if (StringUtils.isNull(loginName)) {
            this.adapterReturnMessage.setStatus(1);
            this.adapterReturnMessage.setMessage("登录名");
        }else {
            this.adapterReturnMessage.setStatus(0);
            this.adapterReturnMessage.setMessage(loginName);
        }
        return this.adapterReturnMessage;
    }

    /**
     * 人员排序号
     * @param sort
     * @return
     */
    private AdapterReturnMessage sortAdapter(String sort) {
        if (StringUtils.isNull(sort)) {
            this.adapterReturnMessage.setStatus(1);
            this.adapterReturnMessage.setMessage("排序号不能为空");
        }else {
            if (StringUtils.isNumber(sort)) {
                int i = Integer.parseInt(sort);
                this.adapterReturnMessage.setStatus(0);
                this.adapterReturnMessage.setMessage(i);
            }else {
                this.adapterReturnMessage.setStatus(1);
                this.adapterReturnMessage.setMessage("sort不是数字");
            }
        }
        return this.adapterReturnMessage;
    }
}

9.ReadExcel

import org.apache.poi.ss.usermodel.*;
import org.springframework.util.SerializationUtils;


import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Objects;

/**
 * @ClassName ReadExcel
 * @Description TODO
 * @Version 1.0
 */

public class ReadExcel {

    public static <T> ArrayList<T> toList(Workbook workbook, String sheetName, int startNum, Class<T> clazz, ExcelVoAdapter excelVoAdapter) {

        ArrayList<T> ts = new ArrayList<T>();
        Sheet sheet = workbook.getSheet(sheetName);
        int lastRowNum = sheet.getLastRowNum()+1;
        if (lastRowNum <= startNum) {
            return ts;
        }
        //列名列
        Row row = sheet.getRow(startNum);
        HashMap<String, String> map = new HashMap<>();
        Field[] declaredFields = clazz.getDeclaredFields();
        for (Field field : declaredFields) {
            AttributeName annotation = field.getAnnotation(AttributeName.class);
            if (annotation != null) {
                map.put(annotation.value(),field.getName());
            }
        }
        ArrayList<String> rowHead = getRowHead(row, map);
        ts = getListBean(clazz,startNum,sheet,rowHead,excelVoAdapter);
        return ts;
    }

    /**
     * 返回封装后的对象
     * @param <T>
     * @param clazz
     * @param startNum
     * @param sheet
     * @param rowHead
     * @param excelVoAdapter
     * @return
     */
    private static <T> ArrayList<T> getListBean(Class<T> clazz, int startNum, Sheet sheet, ArrayList<String> rowHead, ExcelVoAdapter excelVoAdapter) {
        ArrayList<T> ts = new ArrayList<T>();
        int lastRowNum = sheet.getLastRowNum() + 1;
        if (lastRowNum <= startNum+1) {
            return ts;
        }
        for (int j = startNum+1; j < lastRowNum; j++) {
            Row row = sheet.getRow(j);
            short lastCellNum = row.getLastCellNum();
            T t = null;
            try {
                t = clazz.newInstance();
                Method getPo = clazz.getSuperclass().getDeclaredMethod("nwePo");
                getPo.setAccessible(true);
                Object invoke = getPo.invoke(t);
                Method setPo = clazz.getDeclaredMethod("setPo",invoke.getClass());
                setPo.setAccessible(true);
                for (int i = 0; i < rowHead.size(); i++) {
                    String cellValue;
                    if (lastCellNum < i) {
                        cellValue = "";
                    }else {
                        Cell cell = row.getCell(i);
                        cellValue = getCellValue(cell);
                    }
                    Field field = t.getClass().getDeclaredField(rowHead.get(i));
                    Field poField = invoke.getClass().getDeclaredField(rowHead.get(i));
                    poField.setAccessible(true);
                    field.setAccessible(true);
                    field.set(t, gettypeValue(cellValue,field));
                    String adapterFieldName = field.getName() + "Adapter";
                    Method adapterMethod = excelVoAdapter.getClass().getDeclaredMethod(adapterFieldName, String.class);
                    adapterMethod.setAccessible(true);
                    AdapterReturnMessage adapterReturnMessage = (AdapterReturnMessage) adapterMethod.invoke(excelVoAdapter, cellValue);
                    boolean b = setStatus(adapterReturnMessage, t, invoke, poField);
                    if (!b){
                        break;
                    }
                }
                setPo.setAccessible(true);
                setPo.invoke(t,invoke);
            } catch (Exception e) {
                e.printStackTrace();
            }
            ts.add(t);
        }
        return ts;
    }

    public static <T> boolean setStatus(AdapterReturnMessage adapterReturnMessage, T t, Object invoke, Field poField) {
        boolean b = false;
        try {
            Field adapterReturnMessageFile = t.getClass().getSuperclass().getDeclaredField("adapterReturnMessage");
            adapterReturnMessageFile.setAccessible(true);
            AdapterReturnMessage adapterReturnMessage2 = (AdapterReturnMessage) adapterReturnMessageFile.get(t);;
            int status = 0;
            boolean isClone = false;
            if (Objects.isNull(adapterReturnMessage2)) {
                adapterReturnMessage2 = objClone(adapterReturnMessage);
            }
            if (Objects.equals(status,adapterReturnMessage.getStatus())) {
                poField.set(invoke,adapterReturnMessage.getMessage());
                    adapterReturnMessage2.setMessage(null);
                b = true;
                adapterReturnMessageFile.set(t, adapterReturnMessage2);
            }else {
                adapterReturnMessageFile.set(t, objClone(adapterReturnMessage));
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return b;

    }

    /**
     * 根据字段类型返回val需要的类型
     * @param val
     * @param type
     * @param <T>
     * @return
     */
    public static  <T> T gettypeValue(String val, Class<T> type) {
        // 把val转换成type类型返回 比如说getVal("123",Integer.class) 返回一个123
        T value = null;
        try {
            Constructor<T> constructor = type.getConstructor(String.class);
            constructor.setAccessible(true);
            value = constructor.newInstance(val);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return value;

    }

    /**
     * 根据Filed类型返回val需要的类型
     *
     * @param val
     * @param field 属性field
     * @return
     */
    public static Object gettypeValue(String val, Field field) {
        Object value = null;
        try {
            Constructor<?> constructor = field.getType().getConstructor(String.class);
            constructor.setAccessible(true);
            value = constructor.newInstance(val);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return value;

    }

    /**
     * 根据表头和表头列名对应字段明map转换为字段名list
     * @param row
     * @param map
     * @return
     */
    private static ArrayList<String> getRowHead(Row row, HashMap<String,String> map) {
        ArrayList<String> attributeLists = new ArrayList<>(map.size());
        short lastCellNum = row.getLastCellNum();
        short firstCellNum = row.getFirstCellNum();
        for (short i = firstCellNum; i < lastCellNum;i++) {
            Cell cell = row.getCell(i);
            String cellValue = getCellValue(cell);
            String attributeName = map.get(cellValue);
            attributeLists.add(attributeName);
        }
        return attributeLists;
    }

    /**
     * 获取单个cell的值
     * @param cell
     * @return
     */
    private static String getCellValue(Cell cell) {
        String cellValue = "";
        DataFormatter formatter = new DataFormatter();
        if (cell != null) {
            switch (cell.getCellType()) {
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        cellValue = formatter.formatCellValue(cell);
                    } else {
                        double value = cell.getNumericCellValue();
                        int intValue = (int) value;
                        cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);
                    }
                    break;
                case STRING:
                    cellValue = cell.getStringCellValue();
                    break;
                case BOOLEAN:
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                    break;
                case FORMULA:
                    cellValue = String.valueOf(cell.getCellFormula());
                    break;
                case BLANK:
                    cellValue = "";
                    break;
                case ERROR:
                    cellValue = "";
                    break;
                default:
                    cellValue = cell.toString().trim();
                    break;
            }
        }
        return cellValue.trim();
    }

    /**
     * 克隆对象方法
     * @param t
     * @param <T>
     * @return
     */
    public static <T> T objClone(T t) {
        byte[] serialize = SerializationUtils.serialize(t);
        return  (T)SerializationUtils.deserialize(serialize);
    }

}

测试调用

try {
            String sheetName = "人员列表";//工作博名称
            int startNum = 2; //列名列
            String path = "E:\\MemberList_86474400-1.xls";
            File file = new File(path);
            if (!file.exists() && !file.canRead()) {
                return;
            }
            Workbook sheets = WorkbookFactory.create(file);
            sheets.close();
            UserVoAdapter userVoAdapter = new UserVoAdapter();
            ArrayList<UserVo> userVos = ReadExcel.toList(sheets, sheetName, startNum, UserVo.class, userVoAdapter);
            System.out.println(userVos.size());
        } catch (IOException e) {
            e.printStackTrace();
        }