easypoi实现简单的导入导出
概况
今天做Excel导出时,发现了一款非常好用的POI框架EasyPoi,其 使用起来简洁明了。现在我们就来介绍下EasyPoi,首先感谢EasyPoi 的开发者 Lemur开源
easypoi 简介
easypoi 是为了让开发者快速的实现excel,word,pdf的导入导出,基于Apache poi基础上的一个工具包。
特性
- 基于注解的导入导出,修改注解就可以修改Excel
- 支持常用的样式自定义
- 基于map可以灵活定义的表头字段
- 支持一对多的导出,导入
- 支持模板的导出,一些常见的标签,自定义标签
- 支持HTML/Excel转换
- 支持word的导出,支持图片,Excel
常用注解
- @Excel 这个是最基本常用的注解,注解在模型字段上,可添加列名、列的排序、列宽、格式等属性
- @ExcelTarget 用于外层的模型实体,可注解行高、字体大小等属性
- @ExcelEnity 用于标记实体内部类是否继续穿透
- @ExcelCollection 用于注解集合字段
- @ExcelIgnore 忽略这个属性
导入依赖
<!--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>
<!--lombok 为自动生成get set 方法的依赖 -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.18</version>
</dependency>
<!--测试-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>RELEASE</version>
<scope>test</scope>
</dependency>
创建实体类
POIDepartment 部门实体类
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.Data;
@Data
@ExcelTarget("department")//目标对象
public class POIDepartment {
@Excel(name = "部门编号_department")
private Long id;
@Excel(name = "所属部门_employee,部门名称_department")//表示如果是员工那边导出,则该字段的表头为所属部门,如果是部门这边导出,表头为部门名称
private String name;
public POIDepartment(Long id, String name) {
this.id = id;
this.name = name;
}
public POIDepartment() {
}
}
POIEmployee 员工实体类
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelEntity;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.Data;
import java.util.Date;
@Data
@ExcelTarget("employee")//目标对象
public class POIEmployee {
@Excel(name = "编号")//该字段对应的表头设置
private Long id;
@Excel(name = "用户名")
private String username;
@Excel(name = "邮箱", width = 30)
private String email;
@Excel(name = "年龄")
private Integer age;
@Excel(name = "性别", replace = {"男_true", "女_false"})
private Boolean gender;
@Excel(name = "生日", format = "yyyy-MM-dd", width = 20)
private Date birthday = new Date();
/**
* @ExcelEntity表示导出当前对象数据的时候还要顺便导出这个关联对象中加了@Excel注解的那些属性值
*/
@ExcelEntity
private POIDepartment department;
public POIEmployee() {
}
public POIEmployee(Long id, String username, String email, Integer age, Boolean gender) {
this.id = id;
this.username = username;
this.email = email;
this.age = age;
this.gender = gender;
}
public POIEmployee(Long id, String username, String email, Integer age, Boolean gender, POIDepartment department) {
this.id = id;
this.username = username;
this.email = email;
this.age = age;
this.gender = gender;
this.department = department;
}
}
导出测试
普通导出
package test;
import Entity.POIDepartment;
import Entity.POIEmployee;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;
public class test1 {
/**
* 测试普通导出
*/
@Test
public void testExport02() throws Exception {
List<POIEmployee> list = new ArrayList<POIEmployee>();
list.add(new POIEmployee(1L, "admin1", "admin1@qq.com", 20, true));
list.add(new POIEmployee(2L, "admin2", "admin2@qq.com", 21, true));
list.add(new POIEmployee(3L, "admin3", "admin3@qq.com", 22, false));
list.add(new POIEmployee(4L, "admin4", "admin4@qq.com", 23, false));
list.add(new POIEmployee(5L, "admin5", "admin5@qq.com", 24, true));
/**
* easypoi导出数据到excel
* 第一个参数:ExportParams entity, 导出参数[包含导出的表格标题以及sheet名称]
* 第二个参数:Class<?> pojoClass POIEmployee.class 导出数据domain实体类的字节码对象
* 第三个参数:Collection<?> dataSet 导出的数据集合
*/
Workbook workbook = ExcelExportUtil.exportExcel(
new ExportParams("员工信息列表", "员工信息"),
POIEmployee.class,
list);
//输出流输出数据
OutputStream out = new FileOutputStream("D:/excel/员工信息列表.xls");
workbook.write(out);
out.flush();
out.close();
}
}
导出成功
测试导出员工关联的部门信息
/**
* 测试导出员工关联的部门信息
*/
@Test
public void testExport04() throws Exception {
POIDepartment department01 = new POIDepartment(1L, "销售部");
POIDepartment department02 = new POIDepartment(2L, "小卖部");
POIDepartment department03 = new POIDepartment(3L, "零售部");
POIDepartment department04 = new POIDepartment(4L, "人事部");
List<POIEmployee> list = new ArrayList<POIEmployee>();
list.add(new POIEmployee(1L, "admin1", "admin1@qq.com", 20, true, department01));
list.add(new POIEmployee(2L, "admin2", "admin2@qq.com", 21, true, department03));
list.add(new POIEmployee(3L, "admin3", "admin3@qq.com", 22, false, department04));
list.add(new POIEmployee(4L, "admin4", "admin4@qq.com", 23, false, department02));
list.add(new POIEmployee(5L, "admin5", "admin5@qq.com", 24, true, department01));
Workbook workbook = ExcelExportUtil.exportExcel(
new ExportParams("员工信息列表", "员工信息"),
POIEmployee.class,
list);
//输出流输出数据
OutputStream out = new FileOutputStream("D:/excel/员工信息列表.xls");
workbook.write(out);
out.flush();
out.close();
}
测试成功
测试只导出部门信息
/**
* 测试只导出部门信息
*/
@Test
public void testExport05() throws Exception {
POIDepartment department01 = new POIDepartment(1L, "销售部");
POIDepartment department02 = new POIDepartment(2L, "小卖部");
POIDepartment department03 = new POIDepartment(3L, "零售部");
POIDepartment department04 = new POIDepartment(4L, "人事部");
List<POIDepartment> list = new ArrayList<POIDepartment>();
list.add(department01);
list.add(department02);
list.add(department03);
list.add(department04);
Workbook workbook = ExcelExportUtil.exportExcel(
new ExportParams("部门信息列表", "部门信息"),
POIDepartment.class,
list);
//输出流输出
OutputStream out = new FileOutputStream("D:/excel/部门信息列表.xls");
workbook.write(out);
out.flush();
out.close();
}
测试成功
导入测试
@Test
public void testImport() throws Exception {
//导入参数
ImportParams params = new ImportParams();
params.setTitleRows(1); //导入数据的时候排除标题行
params.setHeadRows(1); //导入数据的时候排除表头行
FileInputStream in = new FileInputStream("D:/excel/部门信息列表.xls");
List<POIDepartment> list = ExcelImportUtil.importExcel(
in,
POIDepartment.class, params);
list.forEach(e -> System.out.println(e));
}
导入成功