wisdomsell-day8(智能商贸系统第八天)
用java来操作文本文件,这里选用的是POI的封装easyPOI
1.easyPOI导入导出案列
1.1准备员工
@ExcelTarget("emp")
public class POIEmployee {
/**
* Excel:会导出的数据
* name:列名
*/
@Excel(name = "编号")
private Long id;
@Excel(name = "用户名")
private String username;
@Excel(name = "邮箱",width = 20)
private String email;
/**
* replace:替换
*/
@Excel(name = "性别",replace = {"男_true", "女_false" })
private Boolean sex = true;
@Excel(name = "出生日期",format="yyyy-MM-dd",width = 18)
private Date bornDate = new Date();
@Excel(name = "头像", type = 2,width = 40 , height = 30)
private String headImage;
/**
* 关连对象
*/
@ExcelEntity
private POIDepartment department;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Boolean getSex() {
return sex;
}
public void setSex(Boolean sex) {
this.sex = sex;
}
public Date getBornDate() {
return bornDate;
}
public void setBornDate(Date bornDate) {
this.bornDate = bornDate;
}
public String getHeadImage() {
return headImage;
}
public void setHeadImage(String headImage) {
this.headImage = headImage;
}
public POIDepartment getDepartment() {
return department;
}
public void setDepartment(POIDepartment department) {
this.department = department;
}
@Override
public String toString() {
return "POIEmployee{" +
"id=" + id +
", username='" + username + '\'' +
", email='" + email + '\'' +
", sex=" + sex +
", bornDate=" + bornDate +
", headImage='" + headImage + '\'' +
", department=" + department +
'}';
}
}
1.2准备部门
@ExcelTarget("dept")
public class POIDepartment {
private Long id;
@Excel(name = "部门_emp,名称_dept")
private String name;
@Excel(name = "地址_dept")
private String address;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
1.3测试头像、日期、性别
public class EasyPOITest {
@Test
public void testCreate() throws Exception{
//准备部门
POIDepartment department = new POIDepartment();
department.setId(1L);
department.setName("IT部");
department.setAddress("小小街坊");
//准备数据
POIEmployee e1 = new POIEmployee();
e1.setId(1L);
e1.setUsername("二狗子");
e1.setEmail("gougou@qq.com");
e1.setHeadImage("images/1.jpg");
e1.setDepartment(department);
POIEmployee e2 = new POIEmployee();
e2.setId(2L);
e2.setUsername("二脚子");
e2.setEmail("jiao@qq.com");
e2.setSex(false);
e2.setHeadImage("images/2.jpg");
e2.setDepartment(department);
//数据放到List中
List<POIEmployee> list = new ArrayList<>();
list.add(e1);
list.add(e2);
/**
* ExcelExportUtil:excel导出的工具类
* new ExportParams():导出的参数配置
* title:表头 sheetName:表名 ExcelType:表类型
* POIEmployee.class:导出的对象类型
* list:导出的数据
*/
Workbook wb = ExcelExportUtil.exportExcel(new ExportParams("123","456", ExcelType.XSSF), POIEmployee.class, list);
//文件输出流把excel写出去
FileOutputStream out = new FileOutputStream("emp.xlsx");
wb.write(out);
out.close();
}
1.4测试有关联表的时候
@Test
public void testCreate02() throws Exception{
//准备部门
POIDepartment d1 = new POIDepartment();
d1.setId(1L);
d1.setName("IT部");
d1.setAddress("天府神小长假");
POIDepartment d2 = new POIDepartment();
d2.setId(2L);
d2.setName("销售部");
d2.setAddress("蓬莱");
//数据放到List中
List<POIDepartment> list = new ArrayList<>();
list.add(d1);
list.add(d2);
/**
* ExcelExportUtil:excel导出的工具类
* new ExportParams():导出的参数配置
* title:表头 sheetName:表名 ExcelType:表类型
* POIEmployee.class:导出的对象类型
* list:导出的数据
*/
Workbook wb = ExcelExportUtil.exportExcel(new ExportParams("123","456", ExcelType.XSSF), POIDepartment.class, list);
//文件输出流把excel写出去
FileOutputStream out = new FileOutputStream("dept.xlsx");
wb.write(out);
out.close();
}
1.5导出测试
@Test
public void testImport() throws Exception{
//导入的参数
ImportParams params = new ImportParams();
params.setTitleRows(1);
params.setHeadRows(1);
List<POIEmployee> list = ExcelImportUtil.importExcel(
new File("emp.xlsx"),
POIEmployee.class, params);
list.forEach(e-> System.out.println(e));
}
}
2.应用到项目中
2.1准备
在domain里面的employee里面的字段上面打上注解@Excle,以及有关联的部门字段
然后在employee的controller里面加入
@RequestMapping("/export")
public String export(EmployeeQuery query,ModelMap map, HttpServletRequest request){
//获取相应的数据
List<Employee> list = employeeService.findAll();
}
别忘了在employee的jsp中引入导出按钮
<%--高级查询搜索框--%>
<form id="searchForm" method="post" action="/employee/export">
用户名: <input name="username" class="easyui-textbox" style="width:80px">
邮件: <input name="email" class="easyui-textbox" style="width:80px">
部门: <input class="easyui-combobox" name="departmentId"
data-options="valueField:'id',textField:'name',panelHeight:'auto',url:'/department/list'" />
<a href="#" data-method="search" class="easyui-linkbutton" iconCls="icon-search">查询</a>
<button type="submit" class="easyui-linkbutton" iconCls="icon-search">导出</button>
</form>
2.2导出
接着在上面代码下面写
//设置基本参数
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, "员工");//文件名称
//easypoiExcelView
return NormalExcelConstants.EASYPOI_EXCEL_VIEW;//View名称
}
在 spring-mvc中配置扫描的view
<!--扫描easypoi的view,配置了这个还需要让它优先-->
<context:component-scan base-package="cn.afterturn.easypoi.view"></context:component-scan>
<!-- bean的视图解析器 p:order="1":顺序在最前面 让它比上面的视图解析器优先-->
<!--p:order="1"这个是伪顺序-->
<bean id="beanNameViewResolver" class="org.springframework.web.servlet.view.BeanNameViewResolver" p:order="1" />
如何显示头像的图片
//拿到当前项目的真实路径
String realPath = request.getServletContext().getRealPath("");
list.forEach(e->{
// /images/head/1.jpg
//设置图片路径
e.setHeadImage(realPath+e.getHeadImage());
});
完整版的导出
@RequestMapping("/export")
public String export(EmployeeQuery query,ModelMap map, HttpServletRequest request){
//获取相应的数据
List<Employee> list = employeeService.queryAll(query);
//拿到当前项目的真实路径
String realPath = request.getServletContext().getRealPath("");
list.forEach(e->{
// /images/head/1.jpg
//设置图片路径
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, "员工");//文件名称
//easypoiExcelView
return NormalExcelConstants.EASYPOI_EXCEL_VIEW;//View名称
}
3.导入
3.1写导入的controller
因为专门有一个页面来做导入功能实现
@Controller
@RequestMapping("/import")
public class ImportController {
@Autowired
private IEmployeeService employeeService;
@Autowired
private IDepartmentService departmentService;
@Autowired
private EmployeeVerifyHandler employeeVerifyHandler;
@RequestMapping("/index")
public String index(){
return "import";
}
@RequestMapping("/employeeXlsx")
public String employeeXlsx(MultipartFile empFile, HttpServletResponse response) throws Exception {
//设置导入参数
ImportParams params = new ImportParams();
//设置需要验证为true
params.setNeedVerfiy(true);
//设计验证规则
params.setVerifyHandler(employeeVerifyHandler);
//设置标题和头的行数
//params.setTitleRows(1);
params.setHeadRows(1);
//把上传的文件变成数据 =》 导入时拿到更多的信息
ExcelImportResult<Employee> result = ExcelImportUtil.importExcelMore(
empFile.getInputStream(),
Employee.class, params);
//正确的数据直接保存
result.getList().forEach(e->{
//设置一个默认密码
e.setPassword("123456");
//获取部门对象
if(e.getDepartment()!=null) {
Department department = departmentService.findByName(e.getDepartment().getName());
e.setDepartment(department);
}
employeeService.save(e);
});
//有错误的数据返回工作薄给前台
// result.getFailList().forEach(e->{
// System.out.println(e);
// });
if(result.isVerfiyFail()){
Workbook workbook = result.getFailWorkbook();
//设置响应头
response.setHeader("content-disposition", "attachment;filename=error.xlsx");
ServletOutputStream out = response.getOutputStream();
workbook.write(out);
out.flush();
}
return "import";
}
3.2写导入的jsp页面
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
<%@include file="/WEB-INF/views/head.jsp" %>
</head>
<body>
<!-- 上传必需是:post,enctype="multipart/form-data"-->
<form action="/import/employeeXlsx" method="post" enctype="multipart/form-data">
<input class="easyui-filebox" name="empFile" style="width:80%"
data-options="prompt:'选择一个文件...',buttonText: '选择文件'" />
<button class="easyui-linkbutton">导入</button>
</form>
</body>
</html>
3.3引入验证
导包
<!-- JSR 303 规范验证包 -->
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-validator</artifactId>
<version>5.2.4.Final</version>
</dependency>
在domain里面打注解
这些都是引入规范包后可以使用 的
@NotNull
private String username;
@Max(60)
@Min(value = 18,message = "年龄必需大于18")
private Integer age;
3.4验证用户名的唯一性
写一个验证类
/**
* 用户的名称唯一性验证
*/
@Component
public class EmployeeVerifyHandler implements IExcelVerifyHandler<Employee> {
@Autowired
private IEmployeeService employeeService;
@Override
public ExcelVerifyHandlerResult verifyHandler(Employee employee) {
Employee emp = employeeService.findByUserName(employee.getUsername());
//如果员工存在,代表重复了,就应该返回false
if(emp!=null){
return new ExcelVerifyHandlerResult(false,"用户名已经存在");
}
return new ExcelVerifyHandlerResult(true);
}
}
将这个类让spring能够扫描到
<!--扫描common-->
<context:component-scan base-package="cn.itsource.aisell.common" />
3.5完成部门的导入
在部门的dao加入下面代码,并将他注入到service层中
//根据部门名称获取部门
Department findByName(String deptName);
4.代码中出现的错误
HTTP Status 500 - Servlet.init() for servlet dispatcherServlet threw exception
type Exception report
message Servlet.init() for servlet dispatcherServlet threw exception
description The server encountered an internal error that prevented it from fulfilling this request.
exception
javax.servlet.ServletException: Servlet.init() for servlet dispatcherServlet threw exception
org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)
原因:在打开tomcat的时候页面直接弹出这个,经检查发现是有两个contrler的跳转页面的路径一模一样导致