文章目录
一.EasyPoi使用入门
1.1 maven项目导入依赖
<!-- 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>
1.2 准备一个类(导入)
public class Employee implements Serializable {
private Long id;
@Excel(name = "用户名称")
private String username;
@Excel(name = "邮件",width = 20)
private String email;
// 省略了getter,setter
}
1.2 功能测试
@Test
public void testExcel() throws Exception{
//准备员工数据
Employee e1 = new Employee();
e1.setId(1L);
e1.setUsername("张三");
e1.setEmail("zhang@qq.com");
Employee e2 = new Employee();
e2.setId(2L);
e2.setUsername("李四");
e2.setEmail("li@qq.com");
List<Employee> list = new ArrayList<>();
list.add(e1);
list.add(e2);
/**
* 进行相应的展出
* 参数1:一些基本配置(表头等)
* 参数2:导出的类型
* 参数3:导出的数据
*/
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(),
Employee.class, list);
//保存数据
FileOutputStream fos = new FileOutputStream("emp.xls");
workbook.write(fos);
fos.close();
}
1.3 最后效果
1.4 其他基础配置
刚才咱们只是加了两个简单的String
那么,如果我们有如下要求呢?
- sex是boolean值,我怎么让它true显示男,false展示女
- bornDate是日期类型,我怎么完成它的格式化展示
- 头像是图片,我们可以直接把图片进行导出嘛?
- 如果员工有一个对应的部门(多对一)字段,怎么怎么进行相应的展示
1.5 扩展配置实体类
准备的字段与配置如下:
注意:类上面有一个@ExcelTarget(“emp”)的注解
在这里插入代码片`@Excel(name = "年龄_emp")
private Integer age = 18;
@Excel(name = "生日",format = "yyyy-MM-dd")
private Date bornDate = new Date();
@Excel(name = "性别",replace={"男_true","女_false"})
private Boolean sex = true;
@ExcelEntity
private Department department;
@Excel(name="头像",type = 2)
private String headImage;`
这里注意:我们需要加一个Department类的支持
public class Department {
private Long id;
@Excel(name = "部门名称_emp")
private String name;
}
我们下面说一下相应的配置的含义:
- 1.年龄_emp
必需保证类的ExcelTarget的id是emp才会展示 - 2.format = “yyyy-MM-dd”:日期的格式
注:如果数据库是varchar,还需要配置databaseFormat - 3.replace={“男_true”,“女_false”}
如果值是true,那么展示男,如果是false,则在页面展示女 - 4.@ExcelEntity(id=“emp”):对应另一个关连对象
id是为这个实体取一个名称,和关连的导出对应 - 5.type = 2 :代表这个一个图片展示
1.6 代码测试
Department department1 = new Department();
department1.setId(1L);
department1.setName("教学部");
Department department2 = new Department();
department2.setId(2L);
department2.setName("IT部");
//准备员工数据
Employee e1 = new Employee();
e1.setId(1L);
e1.setUsername("张三");
e1.setEmail("zhang@qq.com");
e1.setDepartment(department1);
e1.setHeadImage("user.png");
Employee e2 = new Employee();
e2.setId(2L);
e2.setUsername("李四");
e2.setEmail("li@qq.com");
e2.setDepartment(department2);
e2.setHeadImage("user-red.png");
List<Employee> list = new ArrayList<>();
list.add(e1);
list.add(e2);
/**
* 进行相应的展出
* 参数1:一些基本配置(表头等)
* 参数2:导出的类型
* 参数3:导出的数据
*/
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("员工信息","员工数据"),
Employee.class, list);
//保存数据
FileOutputStream fos = new FileOutputStream("emp.xls");
workbook.write(fos);
fos.close();
1.7 最终效果图
二.导出功能
刚才咱们完成的功能都是导入,现在咱们来试一下导出功能:
2.1 官方介绍:
有导出就有导入,基于注解的导入导出,配置配置上是一样的,只是方式反过来而已,比如类型的替换 导出的时候是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)));
}
2.2完成代码:
@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也直接提供了对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 就可以使用了
3.1 注解导出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>
@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;
}
3.2前端导入功能
导入页面:
<%@ 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功能
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
@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";
}
}
四. 导入验证功能
4.1引入相应的jar包支持:
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-validator</artifactId>
<version>5.2.4.Final</version>
</dependency>
4.2在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;
...
4.3 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";
}
4.4 自定义验证(用户名重复)
自定义验证需要实现IExcelVerifyHandler接口
(注:让Spring来扫描到这个类)
@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;
}
}
@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";
}