背景需求:在后台管理系统中批量导入教师信息,如图:
下载的excel模板如图所示:
前端代码:
点击下载
提示:导入的时候,默认跳过标题栏,所以不用手动去掉。手机号是唯一字段,如果重复导入会更新原来的数据。
如果导入数据包含系统未录入的院系、专业、班级,则会跳过
Java代码:
首先在maven中导入excel包
org.springblade blade-starter-excel
导出模板代码:
excel模板类:
@Data
@ColumnWidth(25)
@HeadRowHeight(20)
@ContentRowHeight(18)
public class TeacherTemplate implements Serializable {
private static final long serialVersionUID = 1L;
@ColumnWidth(30)
@ExcelProperty("院系")
private String collegeName;
@ColumnWidth(30)
@ExcelProperty("专业")
private String majorName;
@ColumnWidth(30)
@ExcelProperty("教师名字")
private String teacherName;
@ColumnWidth(30)
@ExcelProperty("手机号")
private String mobile;
}
导入数据:
教师数据导入类:
@RequiredArgsConstructor public class TeacherImporter implements ExcelImporter{ private final ITeacherService teacherService; @Override public void save(List data) { teacherService.importResult(data); } }
将数据存入数据库:
@Override public void importResult(Listdata) { data.forEach(teacherExcel -> { // 获取院系id Long collegeId = collegeService.getCollegeId(teacherExcel.getCollegeName()); // 获取专业id Long majorId = majorService.getMajorId(teacherExcel.getMajorName()); if (Func.isEmpty(collegeId)) { return; } if (Func.isEmpty(majorId)) { return; } // 判断某院系下是否存在该专业 if (!majorService.isExitMajor(collegeId, majorId)) { return; } // 根据手机号码和教师姓名查询是否该教师存在 Teacher teacher = this.getOne(Wrappers. lambdaQuery() .eq(Teacher::getMobile, teacherExcel.getMobile()) .eq(Teacher::getTeacherName, teacherExcel.getTeacherName())); if (Func.isNotEmpty(teacher)) {// 如果教师存在,更新院系和专业 teacher.setCollegeId(collegeId); teacher.setMajorId(majorId); this.updateById(teacher);// 更新数据 } else { Teacher teacherNew = new Teacher(); teacherNew.setCollegeId(collegeId); teacherNew.setMajorId(majorId); teacherNew.setTeacherName(teacherExcel.getTeacherName()); teacherNew.setMobile(teacherExcel.getMobile()); this.save(teacherNew);// 新增数据 } }); }
导入导出大概流程如上所述,完成!!!!!!
参考资料:
写excel · 语雀EasyExcel写Excel的示例https://www.yuque.com/easyexcel/doc/write



