java反射(二)-基于反射和注解的Excel解析工具Demo

基于反射和注解的Excel解析工具Demo

原理

反射的运用部分

通过Class的Field，可以给对应Class的Field字段对应的属性设值

注解的运用部分

通过给实体类的属性加上注解，和Excel中的表头做关联，就可以在解析Excel的时候，更加灵活

需要的poi的包

需要依赖的解析Excel的jar包有：
poi-3.10-FINAL.jar
poi-ooxml-3.10-FINAL.jar
poi-ooxml-schemas-3.10-FINAL.jar
xmlbeans-2.6.0.jar

代码实现

创建注解

import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;@Retention(RetentionPolicy.RUNTIME)public @interface CellMapping {    String cellName() default "";}

创建Excel文件

当然，需要注意的是，保存的时候，要存成.xls格式的，不要存.xlsx格式的。可能因为问题，解析不了。

对应的实体类:

public class Car {    @CellMapping(cellName = "汽车品牌")    public String name;    @CellMapping(cellName = "汽车类型")    public String type;    @Override    public String toString() {        return "[name=" + name + ", type=" + type + "]";    }}

解析工具类：

import java.io.File;import java.io.IOException;import java.io.InputStream;import java.lang.reflect.Field;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Set;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.ss.usermodel.WorkbookFactory;public class FastExcel {    private Workbook workbook = null;    private Sheet sheet = null;    public FastExcel(String path) throws InvalidFormatException, IOException {        File file = new File(path);        workbook = WorkbookFactory.create(file);        sheet = workbook.getSheetAt(0);    }    public FastExcel(InputStream is) throws InvalidFormatException, IOException {        workbook = WorkbookFactory.create(is);        sheet = workbook.getSheetAt(0);    }    public <T> List<T> praseExcel(Class<T> clazz) {        List<T> rst = new ArrayList<>();        if (sheet == null)            return rst;        int firstRowNum = sheet.getFirstRowNum();        Row row = sheet.getRow(firstRowNum);        short lastCellNum = row.getLastCellNum();        // key:表头,value:对应的列数        Map<String, Integer> cellNames = getCellMapping(row, lastCellNum);        // key:映射的表头名字,value:对应的字段        Map<String, Field> annotations = getFeildMapping(clazz);        int lastRowNum = sheet.getLastRowNum();        Set<String> keys = cellNames.keySet();        try {            for (int rowIndex = (++firstRowNum); rowIndex <= lastRowNum; rowIndex++) {                T inst = clazz.newInstance();                Row r = sheet.getRow(rowIndex);                for (String key : keys) {                    Field field = annotations.get(key);                    if (field == null)                        continue;                    Integer col = cellNames.get(key);                    Cell cel = r.getCell(col);                    if (cel == null)                        continue;                    field.setAccessible(true);                    String val = cel.getStringCellValue();                    field.set(inst, val);                }                rst.add(inst);            }        } catch (InstantiationException e) {            e.printStackTrace();        } catch (IllegalAccessException e) {            e.printStackTrace();        }        return rst;    }    /**     * 获取表头和列的映射关系     *      * @param row     * @param lastCellNum     * @return     */    private Map<String, Integer> getCellMapping(Row row, short lastCellNum) {        // key:表头,value:对应的列数        Map<String, Integer> cellNames = new HashMap<>();        Cell cell;        for (int col = 0; col < lastCellNum; col++) {            cell = row.getCell(col);            String val = cell.getStringCellValue();            cellNames.put(val, col);        }        return cellNames;    }    /**     * 获取对象字段和Excel表头的字段映射关联     *      * @param clazz     * @return     */    private <T> Map<String, Field> getFeildMapping(Class<T> clazz) {        // key:映射的表头名字,value:对应的字段        Map<String, Field> annotations = new HashMap<>();        Field[] fields = clazz.getDeclaredFields();        if (fields == null || fields.length < 1)            return annotations;        for (Field field : fields) {            CellMapping mapping = field.getAnnotation(CellMapping.class);            if (mapping == null) {                annotations.put(field.getName(), field);            } else {                annotations.put(mapping.cellName(), field);            }        }        return annotations;    }}

运行结果：

import java.io.IOException;import java.io.InputStream;import java.util.List;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;public class CarTest {    public static void main(String[] args) {        try {            InputStream is = CarTest.class.getResourceAsStream("汽车.xls");            FastExcel fastExcel = new FastExcel(is);            List<Car> rst = fastExcel.praseExcel(Car.class);            for (Car car : rst) {                System.out.println(car.toString());            }        } catch (InvalidFormatException e) {            e.printStackTrace();        } catch (IOException e) {            e.printStackTrace();        }    }}

结论：

最终解析结果为：
[name=宝马, type=轿跑]
[name=奔驰, type=suv]
[name=兰博基尼, type=超跑]
[name=特斯拉, type=电动轿跑]
[name=长安, type=面包车]

将实体类字段和Excel表头做绑定，可以不用再写非常多的硬转换代码，提高解析Excel的效率。而且易于维护。
当然，当前只是一个Demo，还没有做到各种数据类型的支持，比如double，时间等等类型的支持。
先记录一下，再优化