使用poi解析excel 返回对象

使用poi加自定义注解使用反射机制实现把excel中的row转换成entity。
支持外键查询找
本人公司内部使用jpa框架，所以代码中从SpringContextHolder对象中获取的注解标识的JpaRepository对象,其他持久层框架也可以使用，需要修改查询接口。
这部分为解析excel 对单元格数据格式进行判断

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import xxx.ExcelCheck;
import xxx.BaseEntityStatus;
import xxx.FieldReflectUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * @author yaku
 */
public class ExcelUtil {

    private final static String XLS = ".xls";
    private final static String XLSX = ".xlsx";

    /**
     * 读入excel文件，解析内容后返回结果
     *
     * @param file    excel文件
     * @param handler 解析excel的
     */
    public static ParseExcelResult readExcel(MultipartFile file, Class<?> clazz, ExcelRowsHandler<Object> handler) {
        ParseExcelResult parseExcelResult = new ParseExcelResult();
        parseExcelResult.setErrorRowList(new ArrayList<>());
        // 获得Workbook工作薄对象
        Workbook workbook;
        try {
            workbook = getWorkBook(Objects.requireNonNull(file.getOriginalFilename()), Objects.requireNonNull(file.getInputStream()));
            // 创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
            if (workbook.getNumberOfSheets() < 1 || workbook.getSheetAt(0).getLastRowNum() == 0) {
                throw new RuntimeException("无法解析excel数据!");
            }
        } catch (IOException e) {
            throw new RuntimeException("无法解析该Excel");
        }
        // 获得当前sheet工作表
        Sheet sheet = workbook.getSheetAt(0);
        // 获得当前sheet的开始行
        int firstRowNum = sheet.getFirstRowNum();
        // 获取头行数据
        Row headRow = sheet.getRow(0);
        // 获得当前sheet的结束行
        int lastRowNum = sheet.getLastRowNum();
        // 循环除了第一行的所有行
        for (int rowNum = firstRowNum + 1; rowNum < lastRowNum; rowNum++) {
            // 获得当前行
            Row row = sheet.getRow(rowNum);
            ErrorRow errorRow;
            Object o;
            try {
                errorRow = check(headRow, row, clazz);
            } catch (ClassNotFoundException | InstantiationException | IllegalAccessException e) {
                throw new RuntimeException("无法解析该Excel");
            }
            if (null != errorRow) {
                parseExcelResult.getErrorRowList().add(errorRow);
                parseExcelResult.setProcessedRowNum(parseExcelResult.getProcessedRowNum() + 1);
                continue;
            } else {
                o = createEntity(headRow, row, clazz);
            }
            // 改行执行sql 发生错误记录改行
            ErrorRow exceptionRow = handler.execute(rowNum, o);
            if (exceptionRow != null) {
                parseExcelResult.getErrorRowList().add(exceptionRow);
                parseExcelResult.setProcessedRowNum(parseExcelResult.getProcessedRowNum() + 1);
            }
        }
        return parseExcelResult;
    }

    /**
     * 根据当前行数据创建对象
     *
     * @param clazz 对象字节码文件
     * @return 检测通过返回null 检测错误返回ErrorRow对象
     * @throws ClassNotFoundException 找不到class字节码文件
     * @throws InstantiationException 无法创建对象 没有默认构造
     * @throws IllegalAccessException 该对象没有权限访问
     */
    private static ErrorRow check(Row headRow, Row currentRow, Class<?> clazz) throws ClassNotFoundException, InstantiationException, IllegalAccessException {
        Field[] fields = clazz.getDeclaredFields();
        boolean[] flag = new boolean[headRow.getLastCellNum()];
        for (Field field : fields) {
            ExcelCheck annotation = field.getAnnotation(ExcelCheck.class);
            for (int i = 0; i < currentRow.getLastCellNum(); i++) {
                if (flag[i]) {
                    continue;
                }
                if (annotation.cellName().equals(headRow.getCell(i).getStringCellValue())) {
                    flag[i] = true;
                    // 判断为空单元格不能为空
                    if (annotation.nullAble()) {
                        continue;
                    }
                    if (currentRow.getCell(i).getCellTypeEnum().equals(CellType.BLANK)) {
                        return new ErrorRow(i, currentRow.getRowNum(), annotation.cellName() + "不能为空！");
                    }
                    // 单元格类型校验
                    if (!typeCheck(annotation.type(), currentRow.getCell(i))) {
                        return new ErrorRow(i, currentRow.getRowNum(), annotation.cellName() + "数据格式错误！");
                    }
                }
            }
        }
        return null;
    }

    /**
     * excel每行数据校验
     *
     * @param type entity对象表明的类型
     * @param cell 单元格对象
     * @return 正确 true 错误 false
     */
    private static boolean typeCheck(ExcelCheck.Type type, Cell cell) {
        switch (type) {
            case NUMBER: {
                if (cell.getCellTypeEnum().equals(CellType.NUMERIC)) {
                    try {
                        MathUtils.objectConvertBigDecimal(cell.getStringCellValue());
                    } catch (Exception e) {
                        return false;
                    }
                    return true;
                }
                return false;
            }
            case STRING: {
                return cell.getCellTypeEnum().equals(CellType.STRING);
            }
            case DATETIME: {
                return cell.getCellTypeEnum().equals(CellType.NUMERIC);
            }
            case LINK: {
                if (cell.getCellTypeEnum().equals(CellType.STRING)) {
                    return StringUtils.isNotBlank(cell.getStringCellValue());
                }
                return false;
            }
            default:
                return false;
        }
    }

    /**
     * 根据注解 对象信息 来转换成对应值
     *
     * @param excelCheck 注解对象
     * @param cell       单元格对象
     * @return 对应类型
     */
    private static Object valueFormat(ExcelCheck excelCheck, Cell cell) {
        switch (excelCheck.type()) {
            case NUMBER: {
                return MathUtils.objectConvertBigDecimal(cell.getNumericCellValue());
            }
            case STRING: {
                return cell.getStringCellValue();
            }
            case DATETIME: {
                return new Timestamp(cell.getDateCellValue().getTime());
            }
            case LINK: {
                return linkQuery(excelCheck.repositoryClass(), excelCheck.methodName(), cell.getStringCellValue());
            }
            default:
                return null;
        }
    }

    private static String linkQuery(Class<? extends JpaRepository> clazz, String methodName, String value) {
        try {
            JpaRepository jpaRepository = SpringContextHolder.getBean(clazz);
            Method method = jpaRepository.getClass().getDeclaredMethod(methodName, String.class, Integer.class);
            Object invoke = method.invoke(jpaRepository, value, BaseEntityStatus.ACTIVE);
            if (null == invoke) {
                return null;
            }
            return (String) FieldReflectUtil.getFieldValue(invoke, "id");
        } catch (NoSuchMethodException | InvocationTargetException | IllegalAccessException | NoSuchFieldException e) {
            throw new RuntimeException("无法处理该外键值");
        }
    }

    private static Object createEntity(Row headRow, Row currentRow, Class<?> clazz) {
        try {
            boolean[] flag = new boolean[headRow.getLastCellNum()];
            Object entity = clazz.newInstance();
            Field[] fields = entity.getClass().getDeclaredFields();
            for (int i = 0; i < headRow.getLastCellNum(); i++) {
                for (Field field : fields) {
                    if (flag[i]) {
                        continue;
                    }
                    if (headRow.getCell(i).getStringCellValue().equals(field.getAnnotation(ExcelCheck.class).cellName())) {
                        flag[i] = true;
                        Cell cell = currentRow.getCell(i);
                        //如果当前单元格为空时，跳过该单元格
                        if (null == cell || cell.getCellTypeEnum().equals(CellType.BLANK)) {
                            continue;
                        }
                        if (!currentRow.getCell(i).getCellTypeEnum().equals(CellType.BLANK)) {
                            FieldReflectUtil.setFieldValue(entity, field.getName(), valueFormat(field.getAnnotation(ExcelCheck.class), cell));
                        }
                    }
                }
            }
            return entity;
        } catch (InstantiationException | IllegalAccessException | InvocationTargetException | NoSuchMethodException | NoSuchFieldException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 根据文件名判断excel版本 获取Workbook对象
     *
     * @param fileName    文件名
     * @param inputStream 输入流
     * @return WorkBook对象
     * @throws IOException 文件名称错误抛出异常
     */
    private static Workbook getWorkBook(String fileName, InputStream inputStream) throws IOException {
        // 创建Workbook工作薄对象
        // 根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
        if (fileName.endsWith(XLS)) {
            // 2003
            return new HSSFWorkbook(inputStream);
        } else if (fileName.endsWith(XLSX)) {
            // 2007
            return new XSSFWorkbook(inputStream);
        } else {
            throw new IOException("文件名后缀错误");
        }
    }

    /**
     * 每读取一行，就调用execute方法
     */
    public interface ExcelRowsHandler<T> {

        /**
         * 可以在此方法校验数据，校验通过后封装 T，再返回，读入完后，再批量insert/update
         *
         * @param lineNum 行号
         * @param t       解析excel每行后封装好的对象
         * @return 自定义封装成 T,返回null则不记录到list
         */
        ErrorRow execute(int lineNum, T t);
    }


    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class ErrorRow {

        private Integer column;

        private Integer row;

        private String cause;

    }

    @Data
    public static class ParseExcelResult {

        /**
         * 错误行列表
         */
        private List<ErrorRow> errorRowList;

        /**
         * 处理行数
         */
        private int processedRowNum;

        /**
         * 错误行数
         */
        private int errorRowNum;

        /**
         * 空行
         */
        private int emptyRowNum;

        /**
         * 总行数
         */
        private int totalRowNum;

    }
}

创建excel 中 row 对应的 entity ，解析后返回entity
cellName，为单元格头行名称
nullAble，为单元格是否可空
type，表明单元格的数据类型
method，为持久层框架需要调用的方法名称
repositoryClass，为持久层框架的类名

import org.springframework.data.jpa.repository.JpaRepository;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * @author yaku
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelCheck {

    String cellName();

    boolean nullAble() default false;

    Type type() default Type.STRING;

    String methodName() default "";

    Class<? extends JpaRepository> repositoryClass() default JpaRepository.class;

    enum Type{
        /**
         * 数字
         */
        NUMBER,
        /**
         * 日期时间
         */
        DATETIME,
        /**
         * 字符串
         */
        STRING,
        /**
         * 外键引用，需要配合持久层使用
         */
        LINK
    }

}

这部分为使用反射获取获取 get entity的值和 set entity的值

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;

public class FieldReflectUtil {

    public static final String GET_STR = "get";
    public static final String IS_STR = "is";
    public static final String SET_STR = "set";

    /**
     * 获得 t.fieldName 的值
     * @param t
     * 操作对象的实例
     * @param fieldName
     * 属性名
     * @param <T>
     * 操作对象的类型
     * @return
     * 获得到的值
     * @throws NoSuchFieldException
     * 找不到对应属性时会抛出该异常
     * @throws IllegalAccessException
     * 如果该工具类没有权限访问该类型对象的时候会抛出该异常
     * @throws NoSuchMethodException
     * 在没有权限访问，且没有getter的情况下会抛出该异常
     * @throws InvocationTargetException
     * 访问setter的时候如果setter抛出了异常则会抛出该异常
     *
     */
    public static <T> Object getFieldValue(T t, String fieldName) throws NoSuchFieldException, IllegalAccessException, NoSuchMethodException, InvocationTargetException {
        Class<?> tClass=t.getClass();
        Field declaredField = tClass.getDeclaredField(fieldName);

        int modifiers = declaredField.getModifiers();
        if (Modifier.isPublic(modifiers)) {
            return declaredField.get(t);
        }

        String name = declaredField.getName();
        String methodName;
        if (declaredField.getType().equals(boolean.class)) {
            methodName = IS_STR;
        } else {
            methodName = GET_STR;
        }
        methodName=buildMethodName(methodName,name);

        Method method = tClass.getMethod(methodName);
        return method.invoke(t);
    }

    /**
     * 设置 t.fieldName 的值
     * 操作对象的类型
     * @param t
     * 操作对象的实例
     * @param fieldName
     * 属性名
     * @param value
     * 设置的值
     * @param <T>
     * 操作对象的类型
     * @throws NoSuchFieldException
     * 找不到对应属性时会抛出该异常
     * @throws IllegalAccessException
     * 如果该工具类没有访问该类型对象的时候会抛出该异常
     * @throws NoSuchMethodException
     * 在没有权限访问，且没有setter的情况下会抛出该异常
     * @throws InvocationTargetException
     * 执行setter的时候如果setter抛出了异常则会抛出该异常
     */
    public static <T> void setFieldValue(T t, String fieldName, Object value) throws NoSuchFieldException, IllegalAccessException, NoSuchMethodException, InvocationTargetException {
        Class<?> tClass=t.getClass();
        Field declaredField = tClass.getDeclaredField(fieldName);
        int modifiers = declaredField.getModifiers();
        //是公共的且可修改
        if (Modifier.isPublic(modifiers)&&!Modifier.isFinal(modifiers)) {
            declaredField.set(t, value);
        }

        String name = declaredField.getName();
        String methodName = SET_STR;

        methodName=buildMethodName(methodName,fieldName);

        Method method = tClass.getMethod(methodName, declaredField.getType());
        method.invoke(t, value);
    }

}

最后这部分如何使用，使用lambda代理内部匿名类，获取到的lineNum为当前行号，o为当前成生成的实体，这里可以取出entity的值进行 持久化操作

    @Test
    public void checkExcelHandle() throws IOException {
        File file = new File("xxx");
        FileInputStream input = new FileInputStream(file);
        MultipartFile multipartFile = new MockMultipartFile("file", file.getName(), "text/plain", IOUtils.toByteArray(input));
        ExcelUtil.ParseExcelResult parseExcelResult = ExcelUtil.readExcel(multipartFile, MatchVehicleItem.class, (ExcelUtil.ExcelRowsHandler) (lineNum, o) -> {
            System.out.println(o);
            return null;
        });
        System.out.println(parseExcelResult);
    }

目前还没有整体写完，加上最后的异常处理，错误行记录生成错误行excel，并在错误cell中红色背景并提示错误原因