做excel文件的导入实现步骤:
1.前台的from表单必须是post请求,并有enctype="multipart/form-data"属性,
2.input标签的类型为file,name必须和后台接收的MultipartFile变量名一致
3.设置初始密码
4.excel中有对应的关联对象名称时(如部门名称)无法直接导入到Employee中,需要通过部门的名称从数据库中查询到相应的部门对象,然后放入到员工对象中
5.获得部门对象从repository层开始操作数据库
6.在domain中设置验证:用户名不能为空,email不能为空,age的最大值和最小值->需要导入JSR 303 规范验证包
7.验证导入对象的用户名不能在数据库中存在
1)配置自定义的视图解析器
2)自定义验证类实现IExcelVerifyHandler接口(excel验证处理器)
3)验证参数时设置验证处理器:params.setVerifyHandler(employeeIExcelVerifyHandler);
- 首先需要导入验证包
<!-- JSR 303 规范验证包 -->
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-validator</artifactId>
<version>5.2.4.Final</version>
</dependency>
- 实体类中加上注解的验证
@NotNull:不能为空(比如用户名,邮箱上面的注解)
@Max:最大数字值(比如年龄上面的注解)
@Min:最小数字值(比如年龄上面的注解)
@Excel(name = "用户名")
@NotNull(message = "导入员工的用户名必须存在")
private String username;
@Excel(name = "年龄")
@Max(value = 80,message = "导入员工的年龄不能超过65岁")
@Min(value = 20,message = "导入员工的年龄不能低于20岁")
private Integer age;
- 自定义验证类
1.前提得有用户名验证方法
Service实现中方法checkUserName()
@Override
public Boolean checkUserName(String username) {
//return !(employeeRepository.getCountByUsername(username)>0);
return employeeRepository.getCountByUsername(username) > 0 ? false : true;
}
2.自定义类实现IExcelVerifyHandler接口
打上@Component注解,Controller层才能注入EmployeeIExcelVerifyHandler
@Component
public class EmployeeIExcelVerifyHandler implements IExcelVerifyHandler<Employee> {
@Autowired
IEmployeeService employeeService;
@Override
public ExcelVerifyHandlerResult verifyHandler(Employee employee) {
//设置初始化的验证值为true
ExcelVerifyHandlerResult excelVerifyHandlerResult = new ExcelVerifyHandlerResult(true);
if (!employeeService.checkUsername(employee.getUsername())){
excelVerifyHandlerResult.setSuccess(false);
excelVerifyHandlerResult.setMsg("此用户名已经存在");
}
return excelVerifyHandlerResult;
}
}
导入的数据中途如果有失败的应该继续导入,成功的数据导入到数据库,失败数据提示客户查看失败数据和失败的原因:
- 通过部门名称查询部门对象
Repository层
//通过部门的名称从数据库中获得相应的部门对象
Department findByName(String deptName);
Service层及其实现
@Autowired
private DepartmentRepository departmentRepository;
@Override
public Department findByName(String deptName) {
return departmentRepository.findByName(deptName);
}
- mvc.xml中扫描view
<!--扫描easypoi中的结果视图-->
<context:component-scan base-package="cn.afterturn.easypoi.view" />
- mvc.xml中配置自定义验证视图解析器
<!--配置自定义的验证视图解析器:p:order="1"表示SpringMVC优先读取这个视图解析器-->
<bean class="org.springframework.web.servlet.view.BeanNameViewResolver" p:order="1"/>
Controller层代码实现
@Controller
@RequestMapping("/import")
public class ImportController {
@Autowired
private IEmployeeService employeeService;
@Autowired
private IDepartmentService departmentService;
@Autowired
private EmployeeIExcelVerifyHandler employeeIExcelVerifyHandler;
public String empImport(MultipartFile empFile, HttpServletResponse response) throws Exception {
//获得输入的流
InputStream inputStream = empFile.getInputStream();
//获得基本的参数配置
ImportParams params = new ImportParams();
//设置开启参数的验证
params.setNeedVerfiy(true);
//参数开启自定义的验证
params.setVerifyHandler(employeeIExcelVerifyHandler);
//得到excel中employee对象的集合
ExcelImportResult<Employee> result = ExcelImportUtil.importExcelMore(inputStream, Employee.class, params);
//getList:获取到引入成功的数据,getFailList:获取到引入失败的数据
List<Employee> list = result.getList();
list.forEach(e -> {
e.setPassword("123");//设置初始密码
//excel中的部门名称查出部门对象,放入到员工对象中,在持久化保存到数据库
if (e.getDepartment() != null) {
Department dept = departmentService.findByName(e.getDepartment().getName());
e.setDepartment(dept);
}
employeeService.save(e);
});
System.out.println("----------------------------------------------");
//如果导入失败的文件应该从前台导出excel,并告知失败的原因
if (result.isVerfiyFail()) {
//错误的文件
Workbook wb = result.getFailWorkbook();
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //mime类型
response.setHeader("Content-disposition", "attachment;filename=error.xlsx");
response.setHeader("Pragma", "No-cache");//设置不要缓存
OutputStream ouputStream = response.getOutputStream();
wb.write(ouputStream);
ouputStream.flush();
ouputStream.close();
}
return "import";
}