利用poi 读取excel通用工具类

poi excel导出通用工具

| 这一行是废话 根据上一篇利用poi 导出excel通用工具类去年写的一个工具类,同样根据业务需求重新封装了一个读excel 工具类,感觉还算通用,分享到博客,欢迎各位大神来吐槽;

1. maven pom.xml 导入poi依赖

            <!-- 2007-->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>${poi.version}</version> 
            </dependency>
            <!-- 2003-->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>${poi.version}</version> 
            </dependency>

1.读取excel配置类

/**
 * @author liweigao
 * @date 2019/2/28 下午8:37
 */
@Data
@Builder
public class ReadConfig {

    private List<Column> columns = Lists.newArrayList();

    /**
     * 读取sheet index
     */
    @Builder.Default
    private int sheetIndex[] = new int[]{0};

    /**
     * 反射对象
     */
    private Class objClass;

    /**
     * 默认从1开始读数据
     */
    @Builder.Default
    private int beginRow = 1;


    @Builder.Default
    private ExcelVersion version = ExcelVersion.XLSX;

    @Data
    @Builder
    public static class Column {


        /**
         * 时间类型格式
         */
        private String dataFormat = "yyyy-MM-dd HH:mm:ss";

        /**
         * 索引值
         */
        private int index;

        /**
         * 字段名称
         */
        private String field;

        /**
         * 字段类型
         */
        private FieldsType type = FieldsType.STRING;
    }
}

2. 字段类型枚举类

/**
 * @author liweigao
 * @date 2019/2/28 下午8:45
 */
@Getter
@AllArgsConstructor
public enum FieldsType {

    /**
     * 时间类型
     */
    DATE(Date.class),

    /**
     * 字符串
     */
    STRING(String.class),

    /**
     * Integer
     */
    INTEGER(Integer.class),
    ;

    private Class type;

}

3.读取类

/**
 * @author liweigao
 * @date 2019/2/28 下午8:21
 */
@Slf4j
public class ReadExcel {


    public <T> List<T> read(InputStream inputStream, ReadConfig readConfig) throws IOException {


        if (Objects.isNull(inputStream) || CollectionUtils.isEmpty(readConfig.getColumns())) {
            return Lists.newArrayList();
        }
        Workbook workbook;
        try {
            switch (readConfig.getVersion()) {
                case XLS:
                    workbook = new HSSFWorkbook(inputStream);
                    break;
                case XLSX:
                    workbook = new XSSFWorkbook(inputStream);
                    break;
                default:
                    workbook = new XSSFWorkbook(inputStream);
                    break;
            }
        } finally {
            try {
                inputStream.close();
            } catch (IOException e) {
                log.error(e.getMessage());
            }
        }
        List<T> dataList;

        try {
            dataList = read(workbook, readConfig);
        } catch (Exception e) {
            log.error("read excel error msg:{}", e.getMessage());

            throw new IOException("read excel error msg " + e.getMessage(), e);
        }
        return dataList;

    }

    /**
     * 读取excel
     *
     * @param workbook   wb
     * @param readConfig config
     * @param <T>        t
     * @return arrayList
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws NoSuchFieldException
     * @throws SecurityException
     * @throws ParseException
     */
    private <T> List<T> read(Workbook workbook, ReadConfig readConfig) throws InstantiationException,
            IllegalAccessException, NoSuchFieldException, ParseException {

        List<T> dataList = Lists.newArrayList();
        for (int i = 0, j = readConfig.getSheetIndex().length; i < j; i++) {

            Sheet sheet = workbook.getSheetAt(readConfig.getSheetIndex()[i]);
            //sheet  总数
            int sheetTotal = sheet.getPhysicalNumberOfRows();

            for (int n = readConfig.getBeginRow(); n < sheetTotal; n++) {
                Row row = sheet.getRow(n);
                //获取对象实例
                T t = (T) readConfig.getObjClass().newInstance();
                //读取数据 映射对象字段
                for (ReadConfig.Column column : readConfig.getColumns()) {
                    Field field = t.getClass().getDeclaredField(column.getField());
                    field.setAccessible(true);

                    field.set(t, getValue(row.getCell(column.getIndex()), column.getDataFormat(),
                            column.getType()));
                }
                dataList.add(t);
            }
        }
        return dataList;
    }

    /**
     * 获取对象值
     *
     * @param cell       cell 单元格
     * @param dataFormat 时间格式
     * @param fieldsType 字段类型
     * @return obj
     * @throws ParseException
     */
    private Object getValue(Cell cell, String dataFormat, FieldsType fieldsType) throws ParseException {
        if (Objects.isNull(cell)) {
            return null;
        }
        cell.setCellType(CellType.STRING);
        if (Objects.isNull(cell.getRichStringCellValue())) {
            return null;
        }
        switch (fieldsType) {
            case DATE:
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat(dataFormat);
                return simpleDateFormat.parse(cell.getStringCellValue());
            case STRING:
                return cell.getStringCellValue();
            case INTEGER:
                return Integer.valueOf(cell.getStringCellValue());
            default:
                return cell.getStringCellValue();
        }
    }


    public static void main(String[] args) throws Exception {
        File file = new File("/data/logs/test.xlsx");
        FileInputStream fileInputStream = new FileInputStream(file);
        ReadExcel readExcel = new ReadExcel();

        List<ReadConfig.Column> columns = Lists.newArrayList();
        columns.add(ReadConfig.Column.builder().index(0).type(FieldsType.STRING).field("field").build());
        columns.add(ReadConfig.Column.builder().index(1).type(FieldsType.STRING).field("name").build());
        ReadConfig readConfig = ReadConfig.builder().columns(columns).objClass(ExportConfig.Column.class).build();
        List<ReadConfig.Column> list = readExcel.read(fileInputStream, readConfig);

        log.info(JSON.toJSONString(list));

    }
}

运行main 方法即可 过程中测试类和excel version 相关类用到了上一篇利用poi 导出excel通用工具类 当然不麻烦可以把两个类全部copy 下来。先执行导出，在执行导入 进行测试。

| getDeclaredField(str) :获得某个类的所有声明的字段，即包括public、private和protect，但是不包括父类的申明字段。so 获取字段失败；可以写个util方法 利用clazz.getSuperclass();遍历获取字段数据，为了不影响性能 可以用threadLocal对对象进行缓存。

两个方法大同小异，工具类封装的比较简单。欢迎吐槽