易用宝项目记录day8-Excel的导入导出
1.EasyPOI集成SpringMVC完成导入导出
-
导包
注意:今天使用EasyPOI需要把之前的poi导包去掉
<!-- easypoi的支持 --> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-base</artifactId> <version>3.2.0</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-web</artifactId> <version>3.2.0</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-annotation</artifactId> <version>3.2.0</version> </dependency>
-
修改domian
employee
@Entity//表示一个由jpa管理的持久对象,对应数据库的一个表 @Table(name = "employee")//table数据库的表名 public class Employee extends BaseDomain { @Excel(name = "用户名") @NotNull(message = "用户名不能为空!") private String username; private String password; @Excel(name = "邮箱", width = 25) private String email; @Excel(name = "头像", type = 2, width = 10, height = 20) private String headImage; @Excel(name = "年龄") @Max(value = 100, message = "年龄不能超过100岁!") @Min(value = 0, message = "年龄不能小于0岁!") @Column(length = 11) private Integer age; @Column(length = 1) private Integer isdelete; @ManyToMany @JoinTable( name = "employee_role", joinColumns = @JoinColumn(name = "employee_id"), inverseJoinColumns = @JoinColumn(name = "role_id") ) private List<Role> roles = new ArrayList<>(); @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "department_id") private Department department;department
@Entity//表示一个由jpa管理的持久对象,对应数据库的一个表 @Table(name = "department")//table数据库的表名 public class Department extends BaseDomain { @Excel(name = "部门名称") private String name;
###2.在MVC里扫描view
<!--扫描easypoi中的结果视图-->
<context:component-scan base-package="cn.afterturn.easypoi.view" />
3. 配置视图解析器
p:order=“1” 先找这个bean的解析，再找其它的
<bean class="org.springframework.web.servlet.view.BeanNameViewResolver" p:order="1" />
4. 前台传入相应的查询数据
<%--查询条--%>
<form id="searchForm" action="/employee/download">
用户名:<input name="username" class="easyui-textbox" style="width:80px">
邮箱:<input name="email" class="easyui-textbox" style="width:80px">
部门:<input id="cc" name="departmentId" class="easyui-combobox" panelHeight="auto"
data-options="valueField:'id',textField:'name',url:'/util/dept'"/>
<a href="#" data-method="search" class="easyui-linkbutton" iconCls="icon-search">查询</a>
<button type="submit" class="easyui-linkbutton" iconCls="icon-search">导出Excel</button>
</form>
5. 后台接收参数进行导出
注意：头像路径必需是真实路径
//下载Excel文件
@RequestMapping("/download")
public String download(ModelMap map, HttpServletRequest request, EmployeeQuery query) {
List<Employee> list = employeeService.findByQuery(query);
//获得头像真实路径
String realPath = request.getSession().getServletContext().getRealPath("");
list.forEach(e -> {
e.setHeadImage(realPath + e.getHeadImage());
});
ExportParams params = new ExportParams("员工数据", "测试", ExcelType.XSSF);
//冻结相应的行
//params.setFreezeCol(2);
// 数据集合
map.put(NormalExcelConstants.DATA_LIST, list);
//导出实体
map.put(NormalExcelConstants.CLASS, Employee.class);
//参数
map.put(NormalExcelConstants.PARAMS, params);
//文件名称
map.put(NormalExcelConstants.FILE_NAME, "employee");
//View名称
return NormalExcelConstants.EASYPOI_EXCEL_VIEW;
}
导入功能
1. 访问页面
ImportController
@Controller
@RequestMapping("/import")
public class ImportController {
@Autowired
private IEmployeeService employeeService;
@Autowired
private IDepartmentService departmentService;
@Autowired
private EmployeeExcelVerifyHandler excelVerifyHandler;
@RequestMapping("/index")
public String index() {
return "import";
}
import.jsp
<form method="post" action="/import/empxlsx" enctype="multipart/form-data">
<input class="easyui-filebox" name="empFile"
data-options="prompt:'请选择一个excel文件',
accept:'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.ms-excel',
validType:'isExcel'"
style="width:80%">
 
<button id="sub" type="submit" disabled="disabled">导入Excel</button>
</form><!-- 注意:上传需要加enctype -->
<form method="post" action="/import/empXlsx" enctype="multipart/form-data">
<input class="easyui-filebox" name="empFile" data-options="prompt:'选择一个excel文件..'" style="width:80%">
<button class="easyui-linkbutton">导入</button>
</form>
2. 导入实现
上传解析器一定要有名字：multipartResolver 必需加一个默认密码
员工对应的部门必需有id(根据名称拿到对应的部门)
@RequestMapping("/empxlsx")
public String empxlsx(MultipartFile empFile, HttpServletResponse response) throws Exception {
/*
System.out.println(empFile);
System.out.println(empFile.getName()); //empFile:上传控件名称
System.out.println(empFile.getOriginalFilename()); //emp.xlsx:上传的文件名称
System.out.println(empFile.getSize()); //文件大小
System.out.println(empFile.getContentType());//文件的memi类型
*/
InputStream inputStream = empFile.getInputStream();
//准备一些导入的参数
ImportParams params = new ImportParams();
//要求导入的时候做验证
params.setNeedVerfiy(true);
//设置自定义的验证处理器
params.setVerifyHandler(excelVerifyHandler);
//params.setTitleRows(1);
//params.setHeadRows(1);
//把上传的excel文件中的数据变成Employee数据
ExcelImportResult<Employee> result = ExcelImportUtil.importExcelMore(
inputStream,
Employee.class, params);
//获取到引入成功的数据
List<Employee> list = result.getList();
//把员工进行保存
list.forEach(e -> {
//设置一个默认密码
e.setPassword("123456");
//设置默认为不删除
e.setIsdelete(1);
//根据名称到数据库中拿到部门
Department department = e.getDepartment();
if (department != null) {
Department dbDept = departmentService.findByName(department.getName());
e.setDepartment(dbDept);
}
employeeService.save(e);
System.out.println(e);
});
return "import";
}
导入验证
使用的是JSR 303 规范
###1.导入验证包
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-validator</artifactId>
<version>5.2.4.Final</version>
</dependency>
###2.加上验证的注解
-
employee
@Entity//表示一个由jpa管理的持久对象,对应数据库的一个表 @Table(name = "employee")//table数据库的表名 public class Employee extends BaseDomain { @Excel(name = "用户名") @NotNull(message = "用户名不能为空!") private String username; private String password; @Excel(name = "邮箱", width = 25) private String email; @Excel(name = "头像", type = 2, width = 10, height = 20) private String headImage; @Excel(name = "年龄") @Max(value = 100, message = "年龄不能超过100岁!") @Min(value = 0, message = "年龄不能小于0岁!") @Column(length = 11) private Integer age; @Column(length = 1) private Integer isdelete; @ManyToMany @JoinTable( name = "employee_role", joinColumns = @JoinColumn(name = "employee_id"), inverseJoinColumns = @JoinColumn(name = "role_id") ) private List<Role> roles = new ArrayList<>(); @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "department_id") private Department department;
###3. 加上自定义注解
这个类型是需要Spring扫描
@Component
public class EmployeeExcelVerifyHandler implements IExcelVerifyHandler<Employee> {
@Autowired
private IEmployeeService employeeService;
@Override
public ExcelVerifyHandlerResult verifyHandler(Employee employee) {
//如果用户存在，我们就返回一个false
// checkUsername:这个用户名是否可以使用
if(!employeeService.checkUsername(employee.getUsername())){
ExcelVerifyHandlerResult result = new ExcelVerifyHandlerResult(false);
result.setMsg("用户名已经存在！");
return result;
}
return new ExcelVerifyHandlerResult(true);
}
}
###4.导入功能修改
要验证，必需设置 params.setNeedVerfiy(true);
自定义验证:params.setVerifyHandler(employeeExcelVerifyHandler);
result.getList(); 拿到所有通过验证的数据 result.getFailList():拿到所有错误的数据 result.getFailWorkbook();拿到错误的文本
@Autowired
private EmployeeExcelVerifyHandler employeeExcelVerifyHandler;
...
@RequestMapping("/empXlsx")
public String empXlsx(MultipartFile empFile, HttpServletResponse response) throws Exception{
//准备一些导入的参数
ImportParams params = new ImportParams();
params.setTitleRows(1);
params.setHeadRows(1);
params.setNeedVerfiy(true); //需要做验证
//设置验证处理器
params.setVerifyHandler(employeeExcelVerifyHandler);
//把上传的excel文件中的数据变成Employee
ExcelImportResult<Employee> result = ExcelImportUtil.importExcelMore(
empFile.getInputStream(),
Employee.class, params);
//拿到相应的值
List<Employee> list = result.getList();
//把员工进行保存
list.forEach(e -> {
//设置一个默认密码
e.setPassword("123456");
//根据名称到数据库中拿到部门
Department department = e.getDepartment();
if(department!=null){
Department dbDept = departmentService.findByName(department.getName());
e.setDepartment(dbDept);
}
employeeService.save(e);
});
//拿到错误的值
// List<Employee> errList = result.getFailList();
// errList.forEach(e -> System.out.println("错误的："+e));
//有错误的情况进行导出
if(result.isVerfiyFail()){
//拿到错误的文件
Workbook failWorkbook = 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();
failWorkbook.write(ouputStream);
ouputStream.flush();
ouputStream.close();
}
return "import";
}