java 基于poi 根据指定范围获取Excel值并赋值Mode

    public final static String COLUMN_PRE = "col";
  /**
     * 根据工作表获取指定model集
     *
     * @param startCol      开始列
     * @param endCol        结束列
     * @param startRow      开始行
     * @param endRow        结束行
     * @param t             模型类
     * @param startProperty 开始赋值的属性编号
     * @param sheetAt       excel表
     * @param result        模型List
     * @param <T>           继承BaseModel的子类
     */
    public static <T extends BaseModel> void getSheetValue(int startCol, int endCol, int startRow, int endRow, Class<T> t,
                                                           int startProperty, Sheet sheetAt, List<T> result) {
        try {
            if (sheetAt == null) {
                return;
            }
            for (int i = startRow; i <= endRow; i++) {
                Class aClass = Class.forName(t.getName());
                Object o = aClass.newInstance();
                int initProperty = startProperty;

                // 该行是否为空
                Row row = sheetAt.getRow(i);
                boolean isEmpty = true;
                if (row == null) {
                    break;
                } else {
                    for (Cell cell : row) {
                        if (null != cell) {
                            isEmpty = false;
                            break;
                        }
                    }
                }
                if (isEmpty) {
                    continue;
                }

                // 赋值
                for (int j = startCol; j <= endCol; j++) {
                    Cell cell = row.getCell(j);
                    Field field = o.getClass().getField(COLUMN_PRE + initProperty);
                    field.setAccessible(true);
                    if (cell == null) {
                        field.set(o, null);
                        continue;
                    }
                    String typeName = field.getGenericType().toString();
                    switch (typeName) {
                        case "class java.lang.Integer":
                            field.set(o, (int) cell.getNumericCellValue());
                            break;
                        case "class java.util.Date":
                            field.set(o, cell.getDateCellValue());
                            break;
                        case "class java.lang.Double":
                            field.set(o, cell.getNumericCellValue());
                            break;
                        case "class java.lang.String":
                            field.set(o, cell.toString());
                            break;
                        default:
                            field.set(o, cell.toString());
                            break;
                    }
                    initProperty++;
                }
                result.add((T) o);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

BaseModel:

package cn.mindworking.bi.datav.util.excel;

import java.lang.reflect.Field;
import java.sql.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

import static cn.mindworking.bi.datav.util.ArithUtils.add;

/**
 * BaseModel
 *
 * @author will
 */
public class BaseModel {

    /**
     * 根据列范围获取和(必须为int)
     *
     * @param start 开始值
     * @param end   结束值
     * @return 和
     */
    public int addIntValue(int start, int end) {
        int result = 0;
        for (int i = start; i <= end; i++) {
            try {
                Object j = this.getClass().getField("col" + i).get(this);
                int k = j == null ? 0 : (int) j;
                result = result + k;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    /**
     * 根据列范围获取和(必须为int)
     *
     * @param start 开始值
     * @param end   结束值
     * @return 和
     */
    public double addDoubleValue(int start, int end) {
        double result = 0d;
        for (int i = start; i <= end; i++) {
            try {
                Object j = this.getClass().getField("col" + i).get(this);
                double k = j == null ? 0 : (double) j;
                result = add(result, k);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    /**
     * 根据列范围拼接sql值
     *
     * @param start 开始值
     * @param end   结束值
     * @return sql中value
     */
    public String addString(int start, int end) {
        StringBuilder str = new StringBuilder();
        for (int i = start; i <= end; i++) {
            try {
                Field field = this.getClass().getField("col" + i);
                Object o = field.get(this);
                String typeName = field.getGenericType().toString();
                switch (typeName) {
                    case "class java.lang.String":
                        str = str.append(o == null ? "''" : ("'" + o.toString() + "'")).append(",");
                        break;
                    case "class java.lang.Integer":
                    case "class java.util.Date":
                    case "class java.lang.Double":
                    default:
                        str = str.append(o == null ? "null" : ("'" + o.toString() + "'")).append(",");
                        break;
                }
            } catch (Exception e) {
                str = str.append("null,");
            }
        }
        return str.substring(0, str.length() - 1);
    }

    /**
     * 根据列范围拼接sql值
     *
     * @param start      开始值
     * @param end        结束值
     * @param dateFormat 指定日期格式
     * @return sql中value
     */
    public String addString(int start, int end, String dateFormat) {
        StringBuilder str = new StringBuilder();
        for (int i = start; i <= end; i++) {
            try {
                Field field = this.getClass().getField("col" + i);
                Object o = field.get(this);
                String typeName = field.getGenericType().toString();
                switch (typeName) {
                    case "class java.lang.String":
                        str = str.append(o == null ? "''" : ("'" + o.toString() + "'")).append(",");
                        break;
                    case "class java.util.Date":
                        if (o != null) {
                            DateFormat _dateFormat = new SimpleDateFormat(dateFormat);
                            String dateStr = _dateFormat.format(Date.valueOf(o.toString()));
                            str.append("'" + dateStr + "'").append(",");
                        } else {
                            str = str.append("null").append(",");
                        }
                        break;
                    case "class java.lang.Integer":
                    case "class java.lang.Double":
                    default:
                        str = str.append(o == null ? "null" : ("'" + o.toString() + "'")).append(",");
                        break;
                }
            } catch (Exception e) {
                str = str.append("null,");
            }
        }
        return str.substring(0, str.length() - 1);
    }
}

Demo Model:

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Model3 extends BaseModel {
    public String col0;
    public String col1;
    public Integer col2;
    public Integer col3;
    public Integer col4;
    public Integer col5;
    public Integer col6;
    public Integer col7;
    public Integer col8;
    public Integer col9;
    public Integer col10;
    public Integer col11;
    public Integer col12;
    public Integer col13;
    public Double col14;
    public Double col15;


    /**
     * 拼接sql格式value
     *
     * @return String
     */
    public String toInsertString(Integer row) {
        this.col0 = Model3RowEnum.MAP.get(row).getName();

        return "(" + addString(0, 15) + ")";
    }
}