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) + ")";
}
}
