读取Excel，并使用自动义注解加反射直接把读取的数据封装为java对象

需求：

将Excel表中的数据直接转换为java的对象(转成Person)对象)

excel数据如下：

代码实现：

POJO类如下：

import java.util.Date;

/**
 * @作者 five-five
 * @创建时间 2021/2/17
 */
public class Person {
    @CellKV(key = "编号")
    private String id;
    @CellKV(key = "姓名")
    private String name;
    @CellKV(key = "性别")
    private String sex;
    @CellKV(key = "出生日期")
    private Date birthday;
    @CellKV(key = "家庭住址")
    private String address;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

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

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "Person{" +
                "id='" + id + '\'' +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", birthday=" + birthday +
                ", address='" + address + '\'' +
                '}';
    }
}

自定义注解如下：

import java.lang.annotation.*;

/**
 * @作者 five-five
 * @创建时间 2021/2/17
 */
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface CellKV {
    String key() default "列名";
//    Class<?> type() default String.class;
}

maven依赖添加：

<dependencies>
    <!--操作excel文件-->
    <dependency>
        <groupId>net.sourceforge.jexcelapi</groupId>
        <artifactId>jxl</artifactId>
        <version>2.6.12</version>
    </dependency>
	<!--核心依赖-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.9</version>
    </dependency>
	<!--简化一些繁琐的操作-->
	<dependency>
        <groupId>cn.hutool</groupId>
        <artifactId>hutool-all</artifactId>
        <version>5.4.2</version>
   </dependency>
</dependencies>

ExcelReader工具类代码如下：

import cn.hutool.core.convert.Convert;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @作者 five-five
 * @创建时间 2021/2/17
 */
public class ExcelReader {
    /**
     * 根据文件输入流来读取excel的内容
     *
     * @param inputStream
     * @return
     */
    public static List<Map<String, Object>> getDataToMapList(InputStream inputStream, int sheetNo) throws IOException {
        List<Map<String, Object>> mapList = new ArrayList<>();
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
//        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);//老版的office用这个
        //获取指定的sheet
        int numberOfSheets = xssfWorkbook.getNumberOfSheets();
        if (numberOfSheets < sheetNo) {
            return mapList;
        }
        XSSFSheet hssfSheet = xssfWorkbook.getSheetAt(sheetNo);

        // 循环行Row
        //拿出第一行的数据
        XSSFRow rowTitle = hssfSheet.getRow(0);
        short titleLength = rowTitle.getLastCellNum();
        String[] titles = new String[titleLength];
        for (int i = 0; i < titleLength; i++) {
            //储存起来
            titles[i] = getStringValue(rowTitle.getCell(i));
        }
        ;
        //从第二行开始
        for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
            XSSFRow hssfRow = hssfSheet.getRow(rowNum);
            if (hssfRow == null) {
                continue;
            }
            // 循环列Cell
            Map<String, Object> map = new LinkedHashMap<>();
            for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {
                XSSFCell xssfCell = hssfRow.getCell(cellNum);
                if (xssfCell == null) {
                    continue;
                }
//                System.out.print(titles[cellNum] +"\t"+ getStringValue(xssfCell));
                map.put(titles[cellNum], getStringValue(xssfCell));
            }
//            System.out.println();
            mapList.add(map);
        }
        return mapList;
    }

    /**
     * @param tClass
     * @param mapList
     * @param <T>
     * @return
     */
    public static <T> List<T> valuePoJO(Class<T> tClass, List<Map<String, Object>> mapList)
            throws IllegalAccessException, InstantiationException, NoSuchFieldException {
        List<T> list = new ArrayList<>();
        Field[] fields = tClass.getDeclaredFields();
        //key为列名，value为map
        Map<String, Field> preperPOJOMaps = new LinkedHashMap<>();
        if (fields != null && fields.length != 0) {
            for (Field field : fields) {
                //权限问题
                boolean accessible = field.isAccessible();
                if (!accessible) {
                    field.setAccessible(true);
                }
                //拿到注解
                CellKV annotation = field.getAnnotation(CellKV.class);
                String key = annotation.key();
                preperPOJOMaps.put(key,field);
            }
        }

        int i = 0;
        for (Map<String, Object> map : mapList) {
            Set<String> keySet = map.keySet();
            T target = tClass.newInstance();
            for (String key : keySet) {
                if (preperPOJOMaps.keySet().contains(key)){
                    //有这个列名
                    Field field1 = preperPOJOMaps.get(key);
                    //开始做数据处理
                    Field field = tClass.getDeclaredField(field1.getName().trim());
                    //授权
                    if (!field.isAccessible()) {
                        field.setAccessible(true);
                    }
                    field.set(target, Convert.convert(field1.getType(),map.get(key)));
                }else {
                    //计数器加一
                    i++;
                };
            }
            if (i == keySet.size()) {
                //一个都没有匹配上
                return list;
            }
            //添加
            list.add(target);
        }
        return list;
    }

    /**
     * 根据XSSFCell的toString方法进行仿造的
     *
     * @param xssfCell
     * @return
     */
    private static String getStringValue(XSSFCell xssfCell) {
        if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfCell.getBooleanCellValue());
        } else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
            if (DateUtil.isCellDateFormatted(xssfCell)) {
                DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                return sdf.format(xssfCell.getDateCellValue());
            }
            return String.valueOf(xssfCell.getNumericCellValue());
        } else {
            return String.valueOf(xssfCell.getStringCellValue());
        }
    }

}

测试类代码：

/**
 * @作者 five-five
 * @创建时间 2021/2/17
 */
public class ClientTest {
    public static void main(String[] args) throws Exception {
        List<Map<String, Object>> dataToMapList =
                ExcelReader.getDataToMapList(new FileInputStream("C:\\Users\\15713\\Desktop\\person测试.xlsx"), 0);
//        dataToMapList.forEach(System.out::println);
        ClientTest clientTest = new ClientTest();
        List<Person> peoples = ExcelReader.valuePoJO(Person.class, dataToMapList);
        peoples.forEach(System.out::println);
    }

}

测试结果如下：