JAVA poi 读取excel 通用模板

来源：互联网发布：unity3d 粒子系统编辑：程序博客网时间：2024/06/06 14:18

简单说明

将excel中的数据通过apache poi和java反射读取数据封装到指定的bean里面

所需依赖

        <dependency>            <groupId>org.projectlombok</groupId>            <artifactId>lombok</artifactId>            <optional>true</optional>        </dependency>        <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi</artifactId>            <version>3.13</version>        </dependency>        <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi-ooxml</artifactId>            <version>3.13</version>        </dependency>

示例代码(java 1.8)

import lombok.Data;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.File;import java.io.FileInputStream;import java.lang.annotation.*;import java.lang.reflect.Field;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.concurrent.atomic.AtomicInteger;public class ExcelDemo {        public static void main(String[] args) throws Exception{            List<ExcelBean> excelBeans = readExcel("d:/本周开发需求.xlsx", ExcelBean.class);            //List<ExcelBean> excelBeans = readExcel("d:/本周开发需求.xls", ExcelBean.class);            excelBeans.forEach(System.out::println);        }        @Data        static class ExcelBean{            @ExcelColumn("需求名称")            private String name;            @ExcelColumn("完成天数")            private String day;            @ExcelColumn("完成天数")            private String day2;            private String other;            @Override            public String toString() {                return "ExcelBean{" + "name='" + name + '\'' + ", day='" + day + '\'' + ", day2='" + day2 + '\'' + ", other='" + other + '\'' + '}';            }        }    /**     * excel字段对应注解     */    @Target({ElementType.FIELD})    @Retention(RetentionPolicy.RUNTIME)    @Documented    @interface ExcelColumn {        String value() default "";    }    /**     * 获取excel数据  将之转换成bean     * @param path     * @param cls     * @param <T>     * @return     */    public static <T> List<T> readExcel(String path, Class<T> cls){        List<T> dataList = new ArrayList<>();        Workbook workbook = null;        try {            if(path.endsWith("xlsx")){                FileInputStream is = new FileInputStream(new File(path));                workbook = new XSSFWorkbook(is);            }            if(path.endsWith("xls")){                FileInputStream is = new FileInputStream(new File(path));                workbook = new HSSFWorkbook(is);            }            if(workbook != null){                //类映射                Map<String, List<Field>> classMap = new HashMap<>();                Field[] fields = cls.getDeclaredFields();                for (Field field : fields) {                    ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);                    if(annotation != null){                        String value = annotation.value();                        if(!classMap.containsKey(value)){                            classMap.put(value, new ArrayList<>());                        }                        field.setAccessible(true);                        classMap.get(value).add(field);                    }                }                Map<Integer, List<Field>> reflectionMap = new HashMap<>();                Sheet sheet = workbook.getSheetAt(0);                AtomicInteger ai = new AtomicInteger();                sheet.forEach(row->{                    int i = ai.incrementAndGet();                    AtomicInteger aj = new AtomicInteger();                    if(i == 1){//首行  提取注解                        row.forEach(cell -> {                            int j = aj.incrementAndGet();                            String cellValue = getCellValue(cell);                            if(classMap.containsKey(cellValue)){                                reflectionMap.put(j, classMap.get(cellValue));                            }                        });                    }else{                        try {                            T t = cls.newInstance();                            row.forEach(cell -> {                                int j = aj.incrementAndGet();                                if(reflectionMap.containsKey(j)){                                    String cellValue = getCellValue(cell);                                    List<Field> fieldList = reflectionMap.get(j);                                    for (Field field : fieldList) {                                        try {                                            field.set(t, cellValue);                                        }catch (Exception e){                                            e.printStackTrace();                                        }                                    }                                }                            });                            dataList.add(t);                        }catch (Exception e){                            e.printStackTrace();                        }                    }                    //System.out.println();                });            }        }catch (Exception e){            e.printStackTrace();        }finally {            if(workbook != null){                try {                    workbook.close();                }catch (Exception e){                }            }        }        return dataList;    }    /**     * 获取excel 单元格数据     * @param cell     * @return     */    public static String getCellValue(Cell cell){        if(cell.getCellType() == cell.CELL_TYPE_BOOLEAN){            return String.valueOf(cell.getBooleanCellValue()).trim();        }else        if(cell.getCellType() == cell.CELL_TYPE_NUMERIC){            return String.valueOf(cell.getNumericCellValue()).trim();        }else{            return String.valueOf(cell.getStringCellValue()).trim();        }    }}

excel数据

输出结果

ExcelBean{name='需求1', day='1.0', day2='1.0', other='null'}ExcelBean{name='需求2', day='2.0', day2='2.0', other='null'}ExcelBean{name='需求3', day='3.0', day2='3.0', other='null'}ExcelBean{name='需求4', day='4.0', day2='4.0', other='null'}ExcelBean{name='需求5', day='5.0', day2='5.0', other='null'}ExcelBean{name='需求6', day='6.0', day2='6.0', other='null'}ExcelBean{name='需求7', day='7.0', day2='7.0', other='null'}ExcelBean{name='需求8', day='8.0', day2='8.0', other='null'}ExcelBean{name='需求9', day='9.0', day2='9.0', other='null'}ExcelBean{name='需求10', day='10.0', day2='10.0', other='null'}ExcelBean{name='需求11', day='11.0', day2='11.0', other='null'}ExcelBean{name='需求12', day='12.0', day2='12.0', other='null'}ExcelBean{name='需求13', day='13.0', day2='13.0', other='null'}ExcelBean{name='需求14', day='14.0', day2='14.0', other='null'}ExcelBean{name='需求15', day='15.0', day2='15.0', other='null'}ExcelBean{name='需求16', day='16.0', day2='16.0', other='null'}ExcelBean{name='需求17', day='17.0', day2='17.0', other='null'}ExcelBean{name='需求18', day='18.0', day2='18.0', other='null'}ExcelBean{name='需求19', day='19.0', day2='19.0', other='null'}ExcelBean{name='需求20', day='20.0', day2='20.0', other='null'}

总结

本实例只是简单了封装了一些基本操作,其他复杂操作暂未实现
可以将单列的数据读取到不同的字段上面
同时若有需要可以在自定义注解完成更复杂的组装多个字段/拆分取部分数据/处理特殊数据等逻辑
欢迎一起转载和探讨

0 0