一行代码解决Excel内容转List

干过Excel文件解析的应该清楚，当excel列比较多，数据类型比较多的时候单元格数据转model的过程真是毫无成就感的体力劳动。作为一名专心写BUG的程序员我们要如何应对这样的情况呢？

上代码：

public abstract class BaseExcelReadHelper<T> {

    protected ValueConvert mValueConvert;

    protected FieldFilter mFieldFilter;

    protected ExcelFilter mExcelFilter;

    private Class<T> mClazz;

    private List<String> mProperties;

    public BaseExcelReadHelper() {
        initValueConvert();
        initExcelFilter();
        initFieldFilter();
    }

    public void getExcelContent(String filePath, ReadExcelResultCallback<T> readExcelResultCallback) throws Exception {
        getExcelContent(new File(filePath), readExcelResultCallback);
    }

    public void getExcelContent(File file, ReadExcelResultCallback<T> readExcelResultCallback) throws Exception {
        Class<T> trueClass = getTrueClass();
        List<String> properties = getProperties(trueClass);
        Workbook workbook = WorkbookFactory.create(new FileInputStream(file));
        try {
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {// 获取每个Sheet表
                Sheet sheet = workbook.getSheetAt(i);
                for (int j = 0; j < sheet.getLastRowNum() + 1; j++) {// getLastRowNum，获取最后一行的行标
                    Row row = sheet.getRow(j);
                    if (row != null) {
                        if (!mExcelFilter.rowNumFilter(j)) {
                            short lastCellNum = row.getLastCellNum();
                            if (mExcelFilter != null && mExcelFilter.columnsFilter(lastCellNum)) {
                                continue;
                            }
                            T object = getObject(row, properties, trueClass);
                            readExcelResultCallback.onResult(object);
                        }
                    }
                }
            }
        } finally {
            workbook.close();
        }

    }

    public List<T> getExcelContent(String filePath) throws Exception {
        return getExcelContent(new File(filePath));
    }

    public List<T> getExcelContent(File file) throws Exception {
        Class<T> trueClass = getTrueClass();
        List<String> properties = getProperties(trueClass);
        Workbook workbook = WorkbookFactory.create(new FileInputStream(file));
        List<T> list = new ArrayList<>();
        try {
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {// 获取每个Sheet表
                Sheet sheet = workbook.getSheetAt(i);
                for (int j = 0; j < sheet.getLastRowNum() + 1; j++) {// getLastRowNum，获取最后一行的行标
                    Row row = sheet.getRow(j);
                    if (row != null) {
                        if (!mExcelFilter.rowNumFilter(j)) {
                            short lastCellNum = row.getLastCellNum();
                            if (mExcelFilter != null && mExcelFilter.columnsFilter(lastCellNum)) {
                                continue;
                            }
                            T object = getObject(row, properties, trueClass);
                            list.add(object);
                        }
                    }
                }
            }
        } finally {
            workbook.close();
        }
        return list;
    }

    private Class<T> getTrueClass() {
        if (mClazz == null) {
            Type type = getClass().getGenericSuperclass();
            mClazz = (Class<T>) ((ParameterizedType) type).getActualTypeArguments()[0];
        }
        return mClazz;
    }

    private List<String> getProperties(Class clazz) {
        if (mProperties == null) {
            Field[] fields = clazz.getDeclaredFields();
            mProperties = new ArrayList<>();
            for (int i = 0; i < fields.length; i++) {
                String name = fields[i].getName();
                if (mFieldFilter != null && !mFieldFilter.filter(name)) {
                    mProperties.add(name);
                }
            }
        }
        return mProperties;
    }

    private T getObject(Row row, List<String> properties, Class clazz) {
        short lastCellNum = row.getLastCellNum();
        Map<String, Object> map = new HashMap<>();
        int count = 0;
        for (int k = 0; k < lastCellNum; k++) {
            if (mExcelFilter != null && mExcelFilter.columnNumFilter(k)) {
                continue;
            }
            Cell cell = row.getCell(k);
            String property = properties.get(count);
            if (mValueConvert == null || !mValueConvert.handler(map, property, cell.toString())) {
                map.put(property, cell.toString());
            }
            count++;
        }
        T object = (T) map2Bean(map, clazz);
        return object;
    }

    public ValueConvert getValueConvert() {
        return mValueConvert;
    }

    public void setValueConvert(ValueConvert valueConvert) {
        mValueConvert = valueConvert;
    }

    public FieldFilter getFieldFilter() {
        return mFieldFilter;
    }

    public void setFieldFilter(FieldFilter fieldFilter) {
        mFieldFilter = fieldFilter;
    }

    public ExcelFilter getExcelFilter() {
        return mExcelFilter;
    }

    public void setExcelFilter(ExcelFilter excelFilter) {
        mExcelFilter = excelFilter;
    }

    public static <T> T map2Bean(Map<String, Object> map, Class<T> class1) {
        T bean = null;
        try {
            bean = class1.newInstance();
            BeanUtils.populate(bean, map);
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        return bean;
    }

    protected abstract void initValueConvert();

    protected abstract void initExcelFilter();

    protected abstract void initFieldFilter();

}

解决思路：

使用POI
一个String数组：用来存储excel列的对应的model的属性
一个Map<String,Object>:key为String数据取的值，Value为对应列的值
通过BeanUtils将Map转换成Bean

1、使用反射获取model里面的属性，添加到String[]。当然model里面的字段有一些是excel里面没有的，那怎么办呢？当然是Filter，这样可以兼容所有的model,只需要根据项目情况实现自己的filter，过滤掉不想要的字段即可。这里有点要注意的，model里面的字段要跟excel里面的列的顺序一致，不然就出现张冠李戴了。
2、使用ValueConvert 进行数据类型的转换，model里面的字段不可能那么巧全都是String。所以需要一个数据类型转换类，根据业务需要作对应的转换

源码Github