说在前面
在我的上一篇文章说道 ,大家已经掌握了EasyPOI的基本使用 也就是基本的导出功能 这篇将是EasyPOI的进阶 告诉大家如何使用EasyPOI的导入功能以及跟SpringMVC的集成
导入功能
官方介绍
有导出就有导入,基于注解的导入导出,配置配置上是一样的,只是方式反过来而已,比如类型的替换 导出的时候是1替换成男,2替换成女,导入的时候则反过来,男变成1 ,女变成2,时间也是类似 导出的时候date被格式化成 2017-8-25 ,导入的时候2017-8-25被格式成date类型 下面说下导入的基本代码,注解啥的都是上面讲过了,这里就不累赘了
@Test
public void test2() {
ImportParams params = new ImportParams();
params.setTitleRows(1);
params.setHeadRows(1);
long start = new Date().getTime();
List<MsgClient> list = ExcelImportUtil.importExcel(
new File(PoiPublicUtil.getWebRootPath("import/ExcelExportMsgClient.xlsx")),
MsgClient.class, params);
System.out.println(new Date().getTime() - start);
System.out.println(list.size());
System.out.println(ReflectionToStringBuilder.toString(list.get(0)));
}
完成代码
上面的代码只是给大家看看官方是怎么使用的
@Test
public void testImport() throws Exception{
ImportParams params = new ImportParams();
// params.setTitleRows(1);
List<PoiUser> list = ExcelImportUtil.importExcel(
new File("员工.xlsx"),
PoiUser.class, params);
list.forEach(u -> System.out.println(u));
}
看到没有,我们只需要写非常简单的代码,就可以完成以后一些很麻烦的功能。当然,我们只给大家展示了easypoi通过注解的导出功能。它还有相应的导出,通过模板生成等实用的功能。另外,对于SpringMVC也有专门的支持!
与springMVC集成
官方介绍
easypoi view 项目是为了更简单的方便搭建在导出时候的操作,利用spring mvc 的view 封装,更加符合spring mvc的风格 view下面包括多个 view的实现
• EasypoiBigExcelExportView 大数据量导出
• EasypoiMapExcelView map 列表导出
• EasypoiPDFTemplateView pdf导出
• EasypoiSingleExcelView 注解导出
• EasypoiTemplateExcelView 模板导出
• EasypoiTemplateWordView word模板导出
• MapGraphExcelView 图表导出
view的是使用方法大同小异,都有一个对应的bean,里面保护指定的参数常量 同意用modelmap.put(‘常量参数名’,‘值’)就可以,最后返回这个view名字
注解目录扫描的时候加上 cn.afterturn.easypoi.view 就可以使用了
注解导出
扫描包
<!-- 扫描easypoi中所有的view -->
<context:component-scan base-package="cn.afterturn.easypoi.view" />
<!-- Bean解析器,级别高于默认解析器,寻找bean对象进行二次处理 -->
<bean id="beanNameViewResolver"
class="org.springframework.web.servlet.view.BeanNameViewResolver" p:order="0">
</bean>
导出的Controller
@RequestMapping("/download")
public String download(EmployeeQuery query,ModelMap map, HttpServletRequest request) {
List<Employee> list = employeeService.findByQuery(query);
//搞定路径问题
list.forEach(e -> {
String realPath = request.getServletContext().getRealPath("");
e.setHeadImage(realPath+e.getHeadImage());
});
ExportParams params = new ExportParams("员工数据", "测试", ExcelType.XSSF);
params.setFreezeCol(5); //这个不是知道是什么意思
map.put(NormalExcelConstants.DATA_LIST, list); // 数据集合
map.put(NormalExcelConstants.CLASS, Employee.class);//导出实体
map.put(NormalExcelConstants.PARAMS, params);//参数
map.put(NormalExcelConstants.FILE_NAME, "员工信息");//文件名称
return NormalExcelConstants.EASYPOI_EXCEL_VIEW;
}
前端导入功能
导入页面:
前端页面我是用了EasyUI 框架 你们也可以不用
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
<%@include file="/WEB-INF/views/head.jsp" %>
</head>
<body>
<span style="color: red">${count}</span>
<!-- 上传请配置enctype -->
<form action="/import/xlsx" method="post" enctype="multipart/form-data">
<input class="easyui-filebox" name="xlsxFile" data-options="prompt:'选择一个文件...'" style="width:80%">
<button class="easyui-linkbutton" type="submit">确定</button>
</form>
</body>
</html>
Controller功能
@Controller
@RequestMapping("/import")
public class ImportController extends BaseController {
@Autowired
private IEmployeeService employeeService;
@Autowired
private IDepartmentService departmentService;
//跳转到导入页面
@RequestMapping("/index")
public String index(){
return "import";
}
//跳转到导入页面
@RequestMapping("/xlsx")
public String importXlsx(MultipartFile xlsxFile, HttpServletRequest request, HttpServletResponse response) throws Exception{
ImportParams params = new ImportParams();
params.setTitleRows(1); //注意:这里有两个表头
List<Employee> list = ExcelImportUtil.importExcel(
xlsxFile.getInputStream(),
Employee.class, params);
for (Employee employee : list) {
employee.setPassword("123"); //默认密码123
if(employee.getDepartment()!=null) {
Department department = departmentService.findByName(employee.getDepartment().getName());
employee.setDepartment(department);
}
employeeService.save(employee);
}
return "import";
}
}
导入验证功能
引入相关的Jar包支持
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-validator</artifactId>
<version>5.2.4.Final</version>
</dependency>
在domain类中加上验证提示
@Entity
@Table(name="employee")
@ExcelTarget("emp")
public class Employee extends BaseDomain implements IExcelModel,IExcelDataModel {
@Excel(name = "用户名")
@NotBlank(message = "用户名不能为空")
private String username;
private String password;
@Excel(name="邮件",width = 30)
private String email;
@Excel(name="年纪")
@Max(value = 80,message = "max 最大值不能超过15")
private Integer age;
//头像
@Excel(name = "头像", type = 2,height = 20)
private String headImage;
//部门
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name="department_id")
@ExcelEntity
private Department department;
...
Controller 完成验证
//跳转到导入页面
@RequestMapping("/xlsx")
public String importXlsx(MultipartFile xlsxFile, HttpServletRequest request, HttpServletResponse response) throws Exception{
ImportParams params = new ImportParams();
params.setNeedVerfiy(true); //代表这里是需要验证的
params.setTitleRows(1); //注意:这里有两个表头
//拿到错误的值
ExcelImportResult<Employee> result = ExcelImportUtil.importExcelMore(
xlsxFile.getInputStream(),
Employee.class, params);
// 把正确的员工进行保存
for (Employee employee : result.getList()) {
employee.setPassword("123"); //默认密码123
if(employee.getDepartment()!=null) {
Department department = departmentService.findByName(employee.getDepartment().getName());
employee.setDepartment(department);
}
employeeService.save(employee);
}
if (result.isVerfiyFail()) { //验证是否失败(把抢购的xlsx拿出去)
ServletOutputStream fos = response.getOutputStream();
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //mime类型
response.setHeader("Content-disposition", "attachment;filename=errorx.xlsx");
response.setHeader("Pragma", "No-cache");
result.getFailWorkbook().write(fos);
fos.close();
}
return "import";
}
自定义验证功能
注意 一定要要spring扫描这个类 需要实现IExcelVerifyHandler接口
@Component
public class MyVerifyHandler implements IExcelVerifyHandler<Employee> {
@Autowired
private IEmployeeService employeeService;
@Override
public ExcelVerifyHandlerResult verifyHandler(Employee employee) {
ExcelVerifyHandlerResult result = new ExcelVerifyHandlerResult();
if (!employeeService.checkUsername(employee.getUsername())) {
result.setMsg("该用户已存在");
result.setSuccess(false);
return result;
}
result.setSuccess(true);
return result;
}
}
controller
@Autowired
private MyVerifyHandler myVerifyHandler;
...
//跳转到导入页面
@RequestMapping("/xlsx")
public String importXlsx(MultipartFile xlsxFile, HttpServletRequest request, HttpServletResponse response) throws Exception{
ImportParams params = new ImportParams();
params.setNeedVerfiy(true); //代表这里是需要验证的
params.setVerifyHandler(myVerifyHandler); //我自己定义的校验器
params.setTitleRows(1); //注意:这里有两个表头
//拿到错误的值
ExcelImportResult<Employee> result = ExcelImportUtil.importExcelMore(
xlsxFile.getInputStream(),
Employee.class, params);
// 把正确的员工进行保存
for (Employee employee : result.getList()) {
employee.setPassword("123"); //默认密码123
if(employee.getDepartment()!=null) {
Department department = departmentService.findByName(employee.getDepartment().getName());
employee.setDepartment(department);
}
employeeService.save(employee);
}
if (result.isVerfiyFail()) { //验证是否失败(把抢购的xlsx拿出去)
ServletOutputStream fos = response.getOutputStream();
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //mime类型
response.setHeader("Content-disposition", "attachment;filename=errorx.xlsx");
response.setHeader("Pragma", "No-cache");
result.getFailWorkbook().write(fos);
fos.close();
}
return "import";
}
总结
到这里 EasyPOI的使用大家就真正的入门了!
如果大家还想使用跟多的功能 可以到http://easypoi.mydoc.io/查看更详细的使用方法