Java根据指定Excel位置读取数据到类中

最新推荐文章于 2024-07-20 23:59:39 发布

li_chunsheng_vip

最新推荐文章于 2024-07-20 23:59:39 发布

阅读量433

点赞数

本文链接：https://blog.csdn.net/dadawdawdadadw/article/details/127771291

版权

一、需要使用到的注解

此注解标注在类上，主要是读取那个sheet页面，如果不使用默认读取第一页

@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface PesExcelEntity {
    int sheetIndex() default 0;
}

此注解用于标注在属性上，怎么读取，读取那个单元格


@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface PesExcel {

    //    列
    int columnIndex() default 0;

    //    行
    int rowIndex() default 0;

    //    跳过指定字符
    int offset() default 0;

    //    数据类型 string int等等。。。。。。
    String dataType() default "";

    //    字段名称
    String fieldName() default "";

    //    错误提示
    String errMsg() default "";

    //    执行的方法
    String invokeMethodName() default "";

    Class<?>[] parameterTypes() default {};
}

类型类

/**
 * excel 数据类型
 *
 * @author l
 */
public class ExcelDataType {
    public static final String INT = "int";
    public static final String METHOD = "method";
}

使用方式

    @PesExcel(columnIndex = 7, rowIndex = 3, dataType = ExcelDataType.METHOD, invokeMethodName = "cn.ac.nercis.pes.core.utils.ExcelCheckUtil#isContainChinese", parameterTypes = {String.class}, fieldName = "项目编号", errMsg = "不能包含中文")

二、工具类如下

代码

/**
 * excel工具类
 *
 * @author lcs
 * @date 2022-5-17
 */
@Slf4j
public class ExcelUtil {

    /**
     * @param inputstream 文件流
     * @param objeType    转换的对象
     * @param <T>         返回的对象
     * @return
     */
    public static <T> T importExcel(InputStream inputstream, Class<T> objeType) {
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        ArrayList<String> errList = new ArrayList<>(objeType.getDeclaredFields().length * 2);
        try {
            Workbook workbook = getWorkbook(inputstream, baos);

            Object bean = Class.forName(objeType.getName()).getDeclaredConstructor().newInstance();

//            获取要读取的sheet
            int sheetIndex = 0;
            PesExcelEntity objeTypeAnnotation = objeType.getAnnotation(PesExcelEntity.class);
            if (objeTypeAnnotation != null) {
                sheetIndex = objeTypeAnnotation.sheetIndex();
            }
            Sheet sheetAt = workbook.getSheetAt(sheetIndex);
            if (sheetAt == null) {
                throw new RuntimeException("您读取的sheet 不存在！！");
            }
            for (Field declaredField : objeType.getDeclaredFields()) {
                PesExcel annotation = declaredField.getAnnotation(PesExcel.class);
                NotBlank notBlank = declaredField.getAnnotation(NotBlank.class);

                if (null != annotation) {
                    Cell cell = sheetAt.getRow(annotation.rowIndex()).getCell(annotation.columnIndex());
                    if (cell != null) {
                        String cellValue = PoiCellUtil.getCellValue(cell);
//                      数据类型校验
                        dataTypeCheck(annotation, cellValue);

//                        跳过字符
                        int offset = annotation.offset();

                        if (offset > cellValue.length()) {
                            offset = cellValue.length();
                        }
                        cellValue = cellValue.substring(offset);

                        if (notBlank != null) {
//                            有非空校验
                            if (StringUtils.isBlank(cellValue)) {
                                errList.add(notBlank.message());
                            }
                        }
                        Field field = bean.getClass().getDeclaredField(declaredField.getName());
                        if (!field.canAccess(bean)) {
                            field.setAccessible(true);
                        }
                        field.set(bean, cellValue);
                    }
                }
            }
//            异常校验，如果数据存在异常则抛出
            errCheck(errList);
            return (T) bean;
        } catch (Exception e) {
            log.error("解析execl异常： {}", e.getMessage());
            throw new RuntimeException(e.getMessage());
        } finally {
            IOUtils.closeQuietly(baos);
        }
    }

    private static void dataTypeCheck(PesExcel annotation, String cellValue) {
        //                        校验数据类型
        if (cn.ac.nercis.pes.core.utils.StringUtils.isNotBlank(annotation.dataType())) {

            log.info("读取的字段信息： {}", cellValue);

            log.info("数据字段： {}", annotation.fieldName());

            try {
                switch (annotation.dataType()) {
                    case ExcelDataType.INT:
                        boolean number = NumberUtil.isNumber(cellValue);
                        if (!number) {
                            throw new RuntimeException();
                        }
                        break;
                    case ExcelDataType.METHOD:
                        String invokeMethodName = annotation.invokeMethodName();
                        if (StringUtils.isBlank(invokeMethodName)) {
                            throw new ExcelCheckUtil.ExcelCheckErr("调用方法不存在");
                        }
//                        执行方法
                        String[] classAndMethod = invokeMethodName.split("#");
                        if (classAndMethod.length != 2) {
                            throw new ExcelCheckUtil.ExcelCheckErr("调用参数异常，请检查");
                        }
                        Class<?> aClass = Class.forName(classAndMethod[0]);
                        Method method = aClass.getMethod(classAndMethod[1], annotation.parameterTypes());
                        try {
                            method.invoke(aClass, cellValue);
                        } catch (InvocationTargetException e) {
                            throw new ExcelCheckUtil.ExcelCheckErr(e.getTargetException().getMessage());
                        }
                        break;
                }
            } catch (ClassNotFoundException e) {
                log.error("错误信息: {},{}", e.getMessage(), e.toString());
                throw new RuntimeException("未找到相关实体");
            } catch (NoSuchMethodException e) {
                log.error("错误信息: {},{}", e.getMessage(), e.toString());
                throw new RuntimeException("未找到相关可执行的方法");
            } catch (ExcelCheckUtil.ExcelCheckErr e) {
                log.error("错误信息：{},{}", e.getMessage(), e.toString());
                throw new RuntimeException(annotation.fieldName() + e.getMessage());
            } catch (Exception e) {
                log.error("必要字段数据类型异常： {}《《《《《《《《《《《《{}", e.getMessage(), e.toString());
                throw new RuntimeException(annotation.fieldName() + (cn.ac.nercis.pes.core.utils.StringUtils.isNotBlank(annotation.errMsg()) ? annotation.errMsg() : "数据格式异常"));
            }
        }
    }

    public static void errCheck(List<String> errList) {
        if (CollUtil.isNotEmpty(errList)) {
            throw new RuntimeException(String.join(";", errList));
        }
    }

    public static Workbook getWorkbook(InputStream inputstream, ByteArrayOutputStream baos) throws IOException {
        byte[] buffer = new byte[1024];
        int len;
        while ((len = inputstream.read(buffer)) > -1) {
            baos.write(buffer, 0, len);
        }
        baos.flush();

        InputStream userIs = new ByteArrayInputStream(baos.toByteArray());
        try {
            return WorkbookFactory.create(userIs);
        } catch (Exception e) {
            log.error("异常信息：  {}，{}", e.getMessage(), e.toString());
            throw new RuntimeException("请检查您的文件格式是否正确！！");
        } finally {
            IOUtils.closeQuietly(baos);
        }

    }


    /**
     * 获取合并单元格的值
     *
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static String getMergedRegionValue(Sheet sheet, int row, int column) {
        if (!isMergedRegion(sheet, row, column)) {
            return PoiCellUtil.getCellValue(sheet.getRow(row).getCell(column));
        }


        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return PoiCellUtil.getCellValue(fCell);
                }
            }
        }

        return null;
    }

    /**
     * 判断指定的单元格是否是合并单元格
     *
     * @param sheet
     * @param row    行下标
     * @param column 列下标
     * @return
     */
    public static boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }
}