一、应用场景
导入excel，根据列号，转换成属性名，从而通过反射设置属性值。这里使用的是 mybatis 对反射的封装包！
**核心思想：**就是通过将实体类封装成 Reflector 对象，通过该对象获取属性对应的 setter 方法 和 参数类型，然后将值设置进去！
二、具体应用
1、excel
2、该 excel 对应的枚举类
public enum StudentEnum {
NAME("name", "姓名"),
ID_CARD("idCard", "学号"),
AGE("age", "年龄"),
GENDER("gender", "性别"),
PARENT_NAME("parentName", "家长姓名"),
PARENT_PHONE("parentPhone", "家长电话"),
SUBJECT_IDS("subjectIds", "报名科目"),
REGISTER_PRICE("registerPrice", "报名价格"),
REGISTER_DATE("registerDate", "报名日期"),
BEGIN_DATE("beginDate", "开始上课日期"),
END_DATE("endDate", "结束上课日期"),
STUDY_STATE("studyState", "学习状态"),
PAUSE_DATE("pauseDate", "暂停上课日期");
private String enName;
private String zhName;
private static Map<Integer, StudentEnum> indexMap = new HashMap<>();
static {
for (int i = 0; i < StudentEnum.values().length; i++) {
indexMap.put(StudentEnum.values()[i].ordinal(), StudentEnum.values()[i]);
}
}
StudentEnum(String enName, String zhName) {
this.enName = enName;
this.zhName = zhName;
}
public String getEnName() {
return enName;
}
public void setEnName(String enName) {
this.enName = enName;
}
public String getZhName() {
return zhName;
}
public void setZhName(String zhName) {
this.zhName = zhName;
}
public static StudentEnum getStudentEnum(Integer index) {
return indexMap.get(index);
}
}
3、该 excel 对应的实体类
(JsonInclude.Include.NON_NULL)
public class Student {
private Long id;
private String name;
private Integer age;
private String gender;
private String parentName;
private String parentPhone;
/**
* 学号
*/
private String idCard;
private Integer registerPrice;
private LocalDate registerDate;
private LocalDate beginDate;
private LocalDate endDate;
/**
* 是否正在学习
*/
private Boolean studyState;
private LocalDate pauseDate;
private LocalDate createDate;
private Long[] subjectIds;
}
4、该 excel 对应的实体类
public final class PoiUtils {
private static final short DATE_FORMAT = HSSFDataFormat.getBuiltinFormat("m/d/yy");
private static final int[] COLUMN_WIDTH = {12, 10, 5, 5, 12, 15, 25, 10, 12, 15, 15, 10, 15};
private static final String[] COLUMN_NAMES = {"姓名", "学号", "年龄", "性别", "家长姓名",
"家长电话", "报名科目", "报名价格", "报名日期", "开始上课日期",
"结束上课日期", "学习状态", "暂停上课日期"};
private static Map<String, Boolean> STUDY_STATE_MAP = new HashMap<>();
private static final String SHEET_NAME = "学生信息表.xls";
private static Map<Long, String> subjectIdMap;
private static Map<String, Long> subjectNameMap;
/**
* Reflector 工厂，可以实现对 Reflector 缓存（Reflector对象是对类进行封装，可以获取所有的属性、getter/setter 方法）
*/
private static ReflectorFactory reflectorFactory = new DefaultReflectorFactory();
static {
STUDY_STATE_MAP.put("正在学习", Boolean.TRUE);
STUDY_STATE_MAP.put("暂停学习", Boolean.FALSE);
}
/**
* 将上传的excel中的数据转换成{@link Student}集合
* @param file 上传的文件对象
* @param subjects 科目集合
* @return {@link Student}集合
*/
public static List<Student> importEmp2List(MultipartFile file, List<Subject> subjects) {
translateSubjects2Map(subjects);
//1、获取 Student 对应的 Reflector 对象
Reflector studentReflector = reflectorFactory.findForClass(Student.class);
List<Student> students = new ArrayList<>();
try {
HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));
int numberOfSheets = workbook.getNumberOfSheets();
for (int i = 0; i < numberOfSheets; i++) {
HSSFSheet sheet = workbook.getSheetAt(i);
int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
Student student;
for (int j = 0; j < physicalNumberOfRows; j++) {
if (j == 0) {
// 标题行
continue;
}
HSSFRow row = sheet.getRow(j);
if (row == null) {
// 没有数据
continue;
}
int physicalNumberOfCells = row.getPhysicalNumberOfCells();
student = new Student();
for (int k = 0; k < physicalNumberOfCells; k++) {
HSSFCell cell = row.getCell(k);
//2、根据所在列获取该列对应的枚举类，从而获取属性名
String propertyName = StudentEnum.getStudentEnum(k).getEnName();
//3、判断该类是否有 setter 方法，没有则无法设置属性值
if (studentReflector.hasSetter(propertyName)) {
//4、获取属性对应的 set 方法 对应的 MethodInvoker 对象
Invoker invoker = studentReflector.getSetInvoker(propertyName);
//5、获取属性对应的 set 方法 对应的 参数类型
Class<?> setterType = studentReflector.getSetterType(propertyName);
switch (cell.getCellTypeEnum()) {
case STRING:
String cellValue = cell.getStringCellValue();
if (StringUtils.isNoneBlank(cellValue)) {
if (Integer.class.equals(setterType)) {
//6、通过反射将值设置到对应的属性
invoker.invoke(student, new Object[]{Integer.valueOf(cellValue)});
} else {
if ("subjectIds".equals(propertyName)) {
List<Long> subjectIds = translateSubjectNames2Id(cellValue);
//6、通过反射将值设置到对应的属性
invoker.invoke(student, new Object[]{subjectIds == null ? null : subjectIds.toArray(new Long[subjectIds.size()])});
} else if ("studyState".equals(propertyName)) {
//6、通过反射将值设置到对应的属性
invoker.invoke(student, new Object[]{STUDY_STATE_MAP.get(cellValue)});
} else {
//6、通过反射将值设置到对应的属性
invoker.invoke(student, new Object[]{cellValue});
}
}
}
break;
default:
if (cell.getDateCellValue() != null) {
//6、通过反射将值设置到对应的属性
invoker.invoke(student, new Object[]{CommonUtils.date2LocalDate(cell.getDateCellValue())});
}
break;
}
}
}
students.add(student);
}
}
} catch (IOException e) {
log.error("读取文件报错：", e);
} catch (IllegalAccessException | InvocationTargetException e) {
e.printStackTrace();
}
return students;
}
private static List<Long> translateSubjectNames2Id(String cellValue) {
List<Long> subjectIds = null;
if (StringUtils.isNoneBlank(cellValue)) {
subjectIds = new ArrayList<>();
if (cellValue.contains(FinalName.SUBJECT_NAME_SEPARATOR)) {
String[] nameArray = cellValue.split(FinalName.SUBJECT_NAME_SEPARATOR);
for (String subjectName : nameArray) {
subjectIds.add(subjectNameMap.get(subjectName));
}
} else {
subjectIds.add(subjectNameMap.get(cellValue));
}
}
return subjectIds;
}
private static void translateSubjects2Map(List<Subject> subjects) {
if (subjectNameMap == null) {
synchronized (PoiUtils.class) {
if (subjectNameMap == null) {
subjectNameMap = new HashMap<>(subjects.size());
subjects.forEach(subject -> subjectNameMap.put(subject.getName(), subject.getId()));
}
}
}
}
