JAVA poi 读取excel 通用模板

简单说明

将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'}

总结

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