1殖演、引入依賴

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.1</version>
        </dependency>
        <dependency>
            <groupId>com.monitorjbl</groupId>
            <artifactId>xlsx-streamer</artifactId>
            <version>2.1.0</version>
        </dependency>

2熄浓、編寫ExcelDataDO類

@Data
public class ExcelDataDO<T> {
    public Class<T> clazz;

    public ExcelData(Class<T> clazz) {
        this.clazz = clazz;
    }

    public String sheetName;
    public List<T> data;
}

3情臭、編寫自動(dòng)封裝@ExcelField注解類

@Retention(RetentionPolicy.RUNTIME)
@Target({java.lang.annotation.ElementType.FIELD})
public @interface ExcelField {

    /**
     * 導(dǎo)出到Excel中的名字.
     */
    public abstract String name();


    /**
     * 配置列的名稱,對(duì)應(yīng)A,B,C,D....
     */
    public abstract String column();


    /**
     * 必填項(xiàng)
     */
    public abstract boolean required() default false;

    /**
     * 校驗(yàn)規(guī)則（正則）
     */
    public abstract String regex() default "";

    /**
     * 是否導(dǎo)出數(shù)據(jù)
     */
    public abstract boolean isExport() default true;
}

4、編寫自動(dòng)封裝ExcelBridge實(shí)體類

@Data
public class ExcelBridgeDO{

    /**
     * 項(xiàng)目序號(hào)
     */
    @ExcelField(name = "序號(hào)", column = "A", required = true)
    private String projectNumber;

    /**
     * 地市名稱
     */
    @ExcelField(name = "地市名稱", column = "B", required = true)
    private String cityName;

    /**
     * 路線名稱
     */
    @ExcelField(name = "路線名稱", column = "C", required = true)
    private String roadName;

    /**
     * 項(xiàng)目類型
     */
    @ExcelField(name = "工程項(xiàng)目", column = "D", required = true)
    private String projectType;

    /**
     * 工程所在地區(qū)間樁號(hào)
     */
    @ExcelField(name = "工程所在地區(qū)間樁號(hào)", column = "E", required = true)
    private String sectionNo;

    /**
     * 建設(shè)性質(zhì)
     */
    @ExcelField(name = "建設(shè)性質(zhì)", column = "F", required = true)
    private String buildNature;

    /**
     * 技術(shù)標(biāo)準(zhǔn)結(jié)構(gòu)
     */
    @ExcelField(name = "技術(shù)標(biāo)準(zhǔn)結(jié)構(gòu)", column = "G", required = true)
    private String buildStructure;

    /**
     * 單位
     */
    @ExcelField(name = "單位", column = "H", required = true)
    private String projectUnit;

    /**
     * 數(shù)量
     */
    @ExcelField(name = "數(shù)量", column = "I", required = true)
    private String projectNum;

    /**
     * 座
     */
    @ExcelField(name = "座", column = "J", required = true)
    private String zuo;

    /**
     * 總投資金額
     */
    @ExcelField(name = "總投資金額", column = "K", required = true)
    private BigDecimal projectTotalMoney;


    /**
     * 部投資金額
     */
    @ExcelField(name = "部投資金額", column = "L", required = true)
    private BigDecimal countyOutMoney;

    /**
     * 省投資金額
     */
    @ExcelField(name = "省投資金額", column = "M", required = true)
    private BigDecimal provinceOutMoney;

    /**
     * 備注
     */
    @ExcelField(name = "備注", column = "R", required = true)
    private String remark;
}

5赌蔑、編寫ExcelUtil類

/**
 * Excel 工具類.
 */
@SuppressWarnings("all")
public class ExcelUtil<T> {
        public Class<T> clazz;
    boolean v2007 = true;

    public ExcelUtil(Class<T> clazz) {
        this.clazz = clazz;
    }

    public Workbook getWorkbook(InputStream in, boolean v2007) throws Exception {
        Workbook workbook = null;
        this.v2007 = v2007;
        if (v2007) {
            workbook = StreamingReader.builder().rowCacheSize(100).bufferSize(4096).open(in);
        } else {
            workbook = new HSSFWorkbook(in);
        }
        return workbook;
    }

    public boolean validity(Workbook workbook) throws Exception {
        Sheet sheet = workbook.getSheetAt(0);
        List<Field> allFields = getMappedFiled(clazz, null);
        if (sheet.getLastRowNum() <= 0) {
            return false;
        }
        Row topRow = null;
        boolean flag = false;
        for (Row row : sheet) {
            if (flag) {
                break;
            }
            topRow = row;
            flag = true;
        }
        int colNum = 0;
        for (Field field : allFields) {
            if (field.isAnnotationPresent(ExcelField.class)) {
                ExcelField attr = field.getAnnotation(ExcelField.class);
                if (topRow.getCell(colNum) == null) {

                    return false;
                } else {
                    String headName = topRow.getCell(colNum).getStringCellValue();
                    if (!headName.equals(attr.name())) {
                        return false;
                    }
                }
                colNum++;

            }
        }
        return true;
    }


    public List<ExcelDataDO<T>> getList(Workbook workbook, int headRow) throws Exception {
        List<ExcelDataDO<T>> result = new ArrayList<ExcelDataDO<T>>();
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            ExcelDataDO<T> exceData = new ExcelDataDO<T>(clazz);
            List<T> list = getList(sheet, headRow);
            exceData.setSheetName(sheet.getSheetName());
            exceData.setData(list);
            result.add(exceData);
        }
        return result;
    }

    public List<ExcelDataDO<T>> getList(Workbook workbook, int sheetIndex, int headRow) throws Exception {
        List<ExcelDataDO<T>> result = new ArrayList<ExcelDataDO<T>>();
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        ExcelDataDO<T> exceData = new ExcelDataDO<T>(clazz);
        List<T> list = getList(sheet, headRow);
        exceData.setSheetName(sheet.getSheetName());
        exceData.setData(list);
        result.add(exceData);
        return result;
    }

    public List<T> getList(Sheet sheet, int headRow) throws Exception {
        List<T> list = new ArrayList<>();
        int maxCol = 0;
        List<Field> allFields = getMappedFiled(clazz, null);
        Map<Integer, Field> fieldsMap = new HashMap<>();
        for (Field field : allFields) {
            if (field.isAnnotationPresent(ExcelField.class)) {
                ExcelField attr = field.getAnnotation(ExcelField.class);
                int col = getExcelCol(attr.column());
                maxCol = Math.max(col, maxCol);
                field.setAccessible(true);
                fieldsMap.put(col, field);
            }
        }
        int index = 0;
        boolean flag = false;
        for (Row row : sheet) {
            int cellNum = maxCol;
            index = index + 1;
            if (index <= headRow) {
                continue;
            }
            if (row.getCell(0).getStringCellValue().equals("1") || flag) {
                flag = true;
            } else {
                continue;
            }

            T entity = null;
            for (int j = 0; j <= cellNum; j++) {
                Cell cell = row.getCell(j);
                if (cell == null) {
                    continue;
                }
                if (!v2007) {
                    cell.setCellType(CellType.STRING);
                }
                String c = cell.getStringCellValue();
                if (c == null || c.equals("")) {
                    continue;
                }
                entity = (entity == null ? clazz.newInstance() : entity);

                Field field = fieldsMap.get(j);
                if (field == null) {
                    continue;
                }
                Class<?> fieldType = field.getType();
                if (String.class == fieldType) {
                    field.set(entity, String.valueOf(c));
                } else if ((Integer.TYPE == fieldType)
                        || (Integer.class == fieldType)) {
                    field.set(entity, Integer.parseInt(c));
                } else if ((Long.TYPE == fieldType)
                        || (Long.class == fieldType)) {
                    field.set(entity, Long.valueOf(c));
                } else if ((Float.TYPE == fieldType)
                        || (Float.class == fieldType)) {
                    field.set(entity, Float.valueOf(c));
                } else if ((Short.TYPE == fieldType)
                        || (Short.class == fieldType)) {
                    field.set(entity, Short.valueOf(c));
                } else if ((Double.TYPE == fieldType)
                        || (Double.class == fieldType)) {
                    field.set(entity, Double.valueOf(c));
                } else if ((BigDecimal.class == fieldType)
                        || (BigDecimal.class == fieldType)) {
                    c = c.replaceAll("\\\\(.*?\\\\)", "");
                    field.set(entity, new BigDecimal(c));
                } else if (Character.TYPE == fieldType) {
                    if ((c != null) && (c.length() > 0)) {
                        field.set(entity, Character.valueOf(c.charAt(0)));
                    }
                }
            }
            if (entity != null) {
                list.add(entity);
            }
            if (!flag) {
                throw new Exception("未找到有效數(shù)據(jù).");
            }
        }
        return list;
    }

    public int getExcelCol(String col) {
        col = col.toUpperCase();
        int count = -1;
        char[] cs = col.toCharArray();
        for (int i = 0; i < cs.length; i++) {
            count += (cs[i] - 64) * Math.pow(26, cs.length - 1 - i);
        }
        return count;
    }


    private List<Field> getMappedFiled(Class clazz, List<Field> fields) {
        if (fields == null) {
            fields = new ArrayList<>();
        }

        Field[] allFields = clazz.getDeclaredFields();
        for (Field field : allFields) {
            if (field.isAnnotationPresent(ExcelField.class)) {
                fields.add(field);
            }
        }

        if (clazz.getSuperclass() != null && !clazz.getSuperclass().equals(Object.class)) {
            getMappedFiled(clazz.getSuperclass(), fields);
        }
        return fields;
    }

    private boolean notEmpty(Object object) {
        if (object == null || String.valueOf(object).trim().equals("")) {
            return false;
        }
        return true;
    }

    private boolean match(String regex, String str) {
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(str);
        return matcher.matches();
    }
}

6俯在、測試代碼

本地測試

    public static void main(String agrs[]) throws Exception {
        ExcelUtil excelUtil = new ExcelUtil(ExcelBridgeDO.class);
        File file = new File("D:\\traffic-project-new\\開發(fā)文檔\\2020-危橋改造計(jì)劃-錄入表總.xlsx");
        FileInputStream inv = new FileInputStream(file);
        Workbook workbook = excelUtil.getWorkbook(inv, "XLSX".equals("XLSX"));
        List<ExcelDataDO<ExcelBridgeDO>> list = excelUtil.getList(workbook, 1, 8);
    }

接口測試

    @RequestMapping(value = "/upload")
    public R upload(@RequestParam("file") MultipartFile file) {
        ExcelUtil excelUtil = new ExcelUtil(ExcelBridgeDO.class);
        InputStream inputStream = file.getInputStream();
        Workbook workbook = excelUtil.getWorkbook(inputStream, suffix.toUpperCase().equals("XLSX"));
        List<ExcelDataDO<ExcelBridgeDO>> excelDataList = excelUtil.getList(workbook, 1, 8);
        return R.ok();
    }