Java封装excel工具类

个人分享个java读取excel文件工具类，各位大佬看看有哪里可以改进的。

1.添加依赖

    <dependencies>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.0</version>
        </dependency>
    </dependencies>

2.创建注解

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
@Documented
public @interface ExcelStr {
    String value() default "";

    /**
     * 是否必填
     */
    boolean required() default false;

    /**
     * json存储
     */
    boolean rowNum() default false;

    /**
     * 错误类型存储
     */
    boolean notNullStr() default false;

}

1. value exlce中文昵称映射

2. required 是否开启必填判断

3. rowNum 是否开启映射json数据存储

4. notNullStr 错误数据存储

 3. 创建实体类

@Data
public class User {

  private String id;

  @ExcelStr("name")
  private String name;

  @ExcelStr(value = "username", required = true)
  private String username;

  private String password;

  @ExcelStr(value = "age", required = true)
  private Integer age;

  @ExcelStr("phone")
  private Double phone;

  @ExcelStr(value = "date", required = true)
  private Date date;

  @ExcelStr(rowNum = true)
  private String rowNum;

  @ExcelStr(notNullStr = true)
  private String notNullStr;
}

4. excel工具类

public class ExcelModel {


    public  <T> List<T> getFileBean(MultipartFile multipartFile, String sheet, Class<T> c) throws Exception {
//        文件上传使用
//        InputStream inputStream = multipartFile.getInputStream();
//        POIFSFileSystem poifsFileSystem = new POIFSFileSystem(inputStream);
//        HSSFWorkbook wb = new XSSFWorkbook(poifsFileSystem);

        Workbook workbook = null;

        String fileName = "C://Users/Administrator/Desktop/新建 XLSX 工作表.xls";

        //创建工作簿
//        workbook = new XSSFWorkbook(multipartFile.getInputStream()); // 文件上传使用
        workbook = new XSSFWorkbook(Files.newInputStream(Paths.get(fileName)));


        Map<String, Integer> map = new HashMap<>();
        List<T> tList = new ArrayList<>();

        Field[] declaredFields = c.getDeclaredFields();

        // 2.读取页脚sheet
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            String sheetName = workbook.getSheetAt(i).getSheetName();
            Sheet sheets = workbook.getSheet(sheetName);
            if (sheet.equals(sheets.getSheetName())){
                // 处理第一行数据
                int firstRowNum = sheets.getFirstRowNum();

                Row lastRow = sheets.getRow(firstRowNum);
                short firstLastRow = lastRow.getLastCellNum();
                for (int j = 0; j < firstLastRow; j++) {
                    Cell cell = lastRow.getCell(j);
                    map.put(cell.getStringCellValue(), j);
                }

                // 处理剩下数据
                int lastRowNum = sheets.getLastRowNum();
                for (int j = 0 + 1 ; j <= lastRowNum; j++) {
                    Row row = sheets.getRow(j);

                    // 排除空行
                    boolean filtration = filtration(row);
                    if(!filtration)
                        continue;

                    Map<String, Object> rowNumMap = new HashMap<>(); // 存储json
                    String notNullStr = ""; // 存储错误

                    T newInstance = c.newInstance();
                    for (Field declaredField : declaredFields) {
                        declaredField.setAccessible(true); // 设置权限
                        // 获取注解
                        ExcelStr annotation = declaredField.getAnnotation(ExcelStr.class);

                        if (annotation != null) {
                            Integer col = map.get(annotation.value());
                            if (col != null) {
                                Cell cell = row.getCell(col);

                                // 校验必填
                                if (!annotation.rowNum() && !annotation.notNullStr()){  // 排除rowNum、notNullStr为空
                                    if ((cell == null || "".equals(cell.toString())) && annotation.required()) {
                                        rowNumMap.put(annotation.value(), null);
                                        notNullStr += "[" + annotation.value() + "]为空,";
                                        continue;
                                    }
                                    if (cell == null || "".equals(cell.toString())) {
                                        declaredField.set(newInstance, null);
                                        continue;
                                    }
                                }

                                Class<?> fieldType = declaredField.getType();
                                // 保存数据
                                try {
                                    if (fieldType == String.class) {
                                        declaredField.set(newInstance, cell.toString());
                                    } else if (fieldType == Integer.class) {
                                        double v = Double.parseDouble(cell.toString());
                                        int intValue = (int) v;

                                        declaredField.set(newInstance, intValue);
                                    } else if (fieldType == Double.class) {
                                        declaredField.set(newInstance, new Double(cell.toString()));
                                    } else if (fieldType == Date.class) {
                                        Date validDateFormat = isValidDateFormat(cell.toString());
                                        if (validDateFormat != null) {
                                            declaredField.set(newInstance, validDateFormat);
                                        } else {
                                            try {
                                                // excel时间文本格式
                                                Date javaDate = HSSFDateUtil.getJavaDate(Double.valueOf(cell.toString()));
                                                declaredField.set(newInstance, javaDate);
                                            } catch (Exception e) {
                                                notNullStr += "[" + annotation.value() + "]数据错误,";
                                            }
                                        }
                                    }
                                    // 存储备份
                                    rowNumMap.put(annotation.value(), cell.toString());

                                } catch (Exception e) {
                                    notNullStr += "[" + annotation.value() + "]数据错误,";
                                }
                            }
                            // 判断是否开启json存储
                            if (annotation.rowNum()) {
                                declaredField.set(newInstance, JSONObject.toJSONString(rowNumMap));
                            }
                            System.out.println(annotation.notNullStr());
                            if (annotation.notNullStr()) {
                                declaredField.set(newInstance, JSONObject.toJSONString(notNullStr));
                            }
                        }
                    }
                    tList.add(newInstance);
                }
            }


        }
        return tList;
    }


    /**
     * 校验日期
     * @param dateStr
     * @return
     */
    public static Date isValidDateFormat(String dateStr) {
        String[] patterns = {
                "yyyy-MM-dd",
                "yyyy/MM/dd",
                "yyyy-MM-dd HH:mm:ss",
                "yyyy/MM/dd HH:mm:ss",
                "dd-MMM-yyyy"
        };
        DateTime parse = null;

        for (String pattern : patterns) {
            try {
                // 尝试使用每种格式解析日期字符串\
                return DateUtil.parse(dateStr, pattern); // 如果解析成功，返回true
            } catch (DateException e) {
                // 如果解析失败，继续尝试下一个格式
            }
        }
        return parse; // 所有格式均解析失败，返回false
    }

    /**
     * 判断当前行是否全为空
     * @param row
     */
    public boolean filtration(Row row) {
        if (row == null) {
            return false;
        }
        short lastCellNum = row.getLastCellNum();

        for (int i = 0; i < lastCellNum; i++) {
            Cell cell = row.getCell(i);
            if (cell != null && !"".equals(cell.toString())) {
                return true;
            }
        }
        return false;
    }
}

5.使用方法

5.1 调用方法

通过注解映射数据 @ExcelString("name")

开启保存映射数据 @ExcelString(rowNum =  true) 

开启错误类型存储 @ExcelString(notNullStr=  true) 

 5.2 测试

public static void main(String[] args) throws Exception {
    /**
     * 第一个参数 用来传递文件上传 MultipartFile
     * 第二个参数 对应要读取的sheet
     * 第三个参数 对应映射实体
     */
    ExcelModel excelModel = new ExcelModel();
    List<User> fileBean = excelModel.getFileBean(null, "Sheet1", User.class);
    
    fileBean.forEach(System.out::println);
    System.out.println(fileBean.get(0).getDate());
}