这里直接用了ApiModelProperty注解的position来作为相应列号、JsonFormat注解的pattern属性来设置日期格式。
- position 根据该属性来设置相应列的数据。该属性默认值是0。
- pattern 根据该属性来设置相应属性的日期格式。
最好是弄个自定义注解去统一去管理。
赖得写,暂时也满足业务需求就直接用已有的注解来整了。
- 工具类
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.gdgxkj.rep.communication.api.model.params.PoiSiteParam;
import io.swagger.annotations.ApiModelProperty;
import java.io.File;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
/**
* @Author: chenlf
* @CreateTime: 2023-07-20
* @Description: excel导入转具体对象
* @Version: 1.0
*/
public class ImportUtil{
public static void main(String[] args) throws Exception {
File file = new File("H://test.xls");
List<TestParam > list = ImportUtil.convertToList(file, 5, Arrays.asList(0), TestParam .class);
}
/**
* @title: convertToList
* @description: 转换为list
* @author: chenlf
* @date: 2023/7/21
* @param: inputStream 文件流
* @param: startRow 开始行号索引 (行号-1)
* @param: excludeColumns 剔除的列索引 (列号-1)
* @return: java.util.List<org.apache.poi.ss.formula.functions.T>
*/
public static List convertToList(InputStream inputStream, int startRow, List<Integer> excludeColumns, Class tClass) throws Exception {
List results = new ArrayList<>();
ExcelReader reader = ExcelUtil.getReader(inputStream);
List<List<Object>> lists = reader.read();
for (int i = startRow; i < lists.size(); i++) {
results.add(ImportUtil.setEntity(tClass.newInstance(), lists.get(i), excludeColumns));
}
return results;
}
/**
* @title: convertToList
* @description: 转换为list
* @author: chenlf
* @date: 2023/7/21
* @param: file
* @param: startRow 开始行号索引 (行号-1)
* @param: excludeColumns 剔除的列索引 (列号-1)
* @return: java.util.List<org.apache.poi.ss.formula.functions.T>
*/
public static List convertToList(File file, int startRow, List<Integer> excludeColumns, Class tClass) throws Exception {
List results = new ArrayList<>();
ExcelReader reader = ExcelUtil.getReader(file);
List<List<Object>> lists = reader.read();
for (int i = startRow; i < lists.size(); i++) {
results.add(ImportUtil.setEntity(tClass.newInstance(), lists.get(i), excludeColumns));
}
return results;
}
/**
* @title: setEntity
* @description: 设置对象属性值
* @author: chenlf
* @date: 2023/7/21
* @param: t
* @param: list
* @param: excludes 剔除掉不需要转换的列 (列号-1)
* @return: T
*/
private static <T>T setEntity(T t, List<Object> list, List<Integer> excludeColumns) {
try {
Field[] fields = t.getClass().getDeclaredFields();
for (Field field : fields) {
int position = field.getAnnotation(ApiModelProperty.class).position();
Object obj = list.get(position);
if ( CollectionUtil.isEmpty(excludeColumns) || !excludeColumns.contains(position)) {
ImportUtil.create(t, field, obj);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return t;
}
/**
* @title: create
* @description: 利用反射给对象赋值
* @author: chenlf
* @date: 2023/7/21
* @param: t 结果对象
* @param: field 属性
* @param: obj excel中拿到的值
* @return: T
*/
private static <T>T create(T t, Field field, Object obj){
try {
String fieldName = field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
Class<?> type = field.getType();
Method method = t.getClass().getMethod("set" + fieldName, type);
if (type.equals(Integer.class)) {
method.invoke(t, Integer.parseInt(obj.toString()));
} else if (type.equals(String.class)) {
method.invoke(t, obj.toString());
} else if (type.equals(Double.class)) {
method.invoke(t, Double.parseDouble(obj.toString()));
} else if (type.equals(Float.class)) {
method.invoke(t, Float.parseFloat(obj.toString()));
} else if (type.equals(Long.class)) {
method.invoke(t, ((Long) obj).longValue());
} else if (type.equals(Date.class)) {
SimpleDateFormat sdf = new SimpleDateFormat(field.getAnnotation(JsonFormat.class).pattern());
method.invoke(t, sdf.parse(obj.toString()));
}
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (ParseException e) {
e.printStackTrace();
}
return t;
}
}
- 实体对象
@Data
@NoArgsConstructor
public class TestParam {
@ApiModelProperty(value = "类型名称", position = 1)
private String typeName;
@ApiModelProperty(value = "类型")
private String type;
@ApiModelProperty(value = "日期", position = 3)
@JsonFormat(pattern = "yyyy-MM-dd")
private Date endDate;
@ApiModelProperty(value = "名称", position = 2)
private String name;
}