丝滑的 EasyPoi
POI：用来处理 Excel、Word 等文件的技术。EasyPoi：让没有接触过 POI 的人也能方便的写出 Excel 的导入导出功能。说白了就是用了 EasyPoi，文件的导入导出变得 so easy！
官网地址：http://easypoi.mydoc.io/
1. SpringBoot整合EasyPoi
引入依赖
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--easy-poi-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.0.0</version>
</dependency>
2. 相关注解
2.1 @ExcelTarget
作用在实体类上，表明这是一个可以用 EasyPoi 操作导入导出的实体类
常用属性:
- value：唯一标识，不能重复。但是可以省略不写，例如 "user"
使用方法：
@ExcelTarget("user")
public class User implements Serializable {
}
2.2 @Excel
作用在实体类中的属性上面，表明这是 Excel 中的一列。
常用属性：
- name：Excel表中的列名，例如 name="姓名"
- orderNum：Excel表中列的排列顺序，例如 orderNum="1"
- type：导出类型，1 文本，2 图片，3 函数，10 数字，默认是文本
- width：列的宽度
- height：列的高度
- savePath：图片的保存位置
- exportformat：Excel 导出的时间格式，例如 exportformat="yyyy-MM-dd HH:mm:ss"
- importFormat：Excel 导入的时间格式
- format：同时设置导入导出的时间格式
- isStatistics：是否合计该列？默认 false
- needMerge：是否纵向合并单元格，默认false
- replace：替换值，例如 replace = {"女_0", "男_1"}，表示如果值为 1，则导出 ‘男’，如果值为 0，则导出 ‘女’。
使用方法：
@ExcelTarget("user")
public class User implements Serializable {
@Excel(name = "姓名", width = 30, orderNum = "1")
private String name;
@Excel(name = "年龄", width = 10, orderNum = "2",isSta tistics = true)
private Integer age;
@Excel(name = "性别", width = 10, orderNum = "3", repl ace = {"女_0", "男_1"})
private Integer sex;
@Excel(name = "生日", width = 30, orderNum = "4", form at = "yyyy-MM-dd HH:mm:ss")
private Date birthday;
@Excel(name = "头像信息", type = 2, width = 20,height = 30,savePath = "D:\\WorkSpace\\easy-poi\\src\\main\\resources")
private String head;
}
2.3 @ExcelEntity
作用在实体类中的类上面，表明这是导出的类中的类。
常用属性：
- name：唯一标识，不能重复。
使用方法：
@ExcelTarget("user")
public class User implements Serializable {
@ExcelEntity(name = "address")
private Address address;
}
@ExcelTarget("address")
public class Address implements Serializable {
@Excel(name = "省", width = 30, orderNum = "6")
private String province;
@Excel(name = "市", width = 30, orderNum = "7")
private String city;
@Excel(name = "县", width = 30, orderNum = "8")
private String county;
}
2.4 @ExcelCollection
表明这是一个集合，一对多的关系
常用属性：
- name：集合名，唯一标识
- orderNum：Excel 表中列的排列顺序
使用方法：
@ExcelTarget("user")
public class User implements Serializable {
@ExcelCollection(name = "购买的水果", orderNum = "9")
private List<Fruit> fruits;
}
@ExcelTarget("fruit")
public class Fruit implements Serializable {
@Excel(name = "水果名")
private String name;
@Excel(name = "价格/元")
private Double price;
@Excel(name = "数量")
private Integer count;
}
2.5 @ExcelIgnore
作用在属性上面，导出时跳过这一列
使用方法：
@ExcelIgnore
@Excel(name = "荣誉", width = 60, orderNum = "5")
private List<String> honors;
3. 导出 Excel
3.1 导出基本属性
定义用户类：
/**
* @Description 导入导出实体类
* @Author 一颗雷布斯
* @Date 2021/5/24 15:38
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("user")
public class User implements Serializable {
@Excel(name = "姓名", width = 30, orderNum = "1" )
private String name;
@Excel(name = "年龄", width = 10, orderNum = "2")
private Integer age;
@Excel(name = "性别", width = 10, orderNum = "3", replace = {"女_0", "男_1"})
private Integer sex;
@Excel(name = "生日", width = 30, orderNum = "4", format = "yyyy-MM-dd HH:mm:ss")
private Date birthday;
}
测试方法：
@Test
void exportBasic() throws IOException {
//新建测试数据
List<User> userList = new ArrayList<>();
for (int i = 0; i < 10; i++) {
User user = new User();
user.setName("周芷若" + i);
user.setAge(18 + i);
user.setBirthday(new Date());
user.setSex(new Random().nextInt(2));
userList.add(user);
}
/*---------------------下面是使用EasyPoi导出的核心方法----------------------------*/
// ExportParams() 两个参数，一个是title 表格标题, 一个是sheetName
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户表","sheet1"),User.class,userList);
//导出文件到指定位置
FileOutputStream outputStream = new FileOutputStream("D:\\WorkSpace\\easy-poi\\src\\main\\resources\\用户表.xls");
workbook.write(outputStream);
outputStream.close();
workbook.close();
}
执行结果：
3.2 导出图片
定义用户类（包含用户头像）：
/**
* @Description 导入导出实体类
* @Author 一颗雷布斯
* @Date 2021/5/24 15:38
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("user")
public class User implements Serializable {
@Excel(name = "姓名", width = 30, orderNum = "1" )
private String name;
@Excel(name = "年龄", width = 10, orderNum = "2")
private Integer age;
@Excel(name = "性别", width = 10, orderNum = "3", replace = {"女_0", "男_1"})
private Integer sex;
@Excel(name = "生日", width = 30, orderNum = "4", format = "yyyy-MM-dd HH:mm:ss")
private Date birthday;
//切记图片的 type = 2
@Excel(name = "头像信息", type = 2, width = 20,height = 30)
private String head;
}
测试方法：
@Test
void exportBasic() throws IOException {
//新建测试数据
List<User> userList = new ArrayList<>();
for (int i = 0; i < 10; i++) {
User user = new User();
user.setName("周芷若" + i);
user.setAge(18 + i);
user.setBirthday(new Date());
user.setSex(new Random().nextInt(2));
//指定图像所在的位置
user.setHead("D:\\WorkSpace\\easy-poi\\src\\main\\resources\\static\\image\\head.jpeg");
userList.add(user);
}
/*---------------------下面是使用EasyPoi导出的核心方法----------------------------*/
// ExportParams() 两个参数，一个是title 表格标题, 一个是sheetName
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户表","sheet1"),User.class,userList);
//导出文件到指定位置
FileOutputStream outputStream = new FileOutputStream("D:\\WorkSpace\\easy-poi\\src\\main\\resources\\用户表.xls");
workbook.write(outputStream);
outputStream.close();
workbook.close();
}
执行结果：
3.3 导出对象中的对象
定义用户类（包含用户地址对象）：
/**
* @Description 导入导出实体类
* @Author 一颗雷布斯
* @Date 2021/5/24 15:38
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("user")
public class User implements Serializable {
@Excel(name = "姓名", width = 30, orderNum = "1")
private String name;
@Excel(name = "年龄", width = 10, orderNum = "2")
private Integer age;
@Excel(name = "性别", width = 10, orderNum = "3", replace = {"女_0", "男_1"})
private Integer sex;
@Excel(name = "生日", width = 30, orderNum = "4", format = "yyyy-MM-dd HH:mm:ss")
private Date birthday;
@ExcelEntity(name = "address")
private Address address;
}
地址类：
/**
* @Description 地址
* @Author 一颗雷布斯
* @Date 2021/5/24 16:32
*/
@Data
@AllArgsConstructor
@ExcelTarget("address")
public class Address implements Serializable {
@Excel(name = "省", width = 30, orderNum = "5")
private String province;
@Excel(name = "市", width = 30, orderNum = "6")
private String city;
@Excel(name = "县", width = 30, orderNum = "7")
private String county;
}
测试方法：
@Test
void exportUserTest() throws IOException {
List<User> userList = new ArrayList<>();
for (int i = 0; i < 5; i++) {
User user = new User();
user.setName("周芷若" + i);
user.setAge(18 + i);
user.setBirthday(new Date());
user.setSex(new Random().nextInt(2));
//设置地址
user.setAddress(new Address("湖南","长沙","宁乡"));
userList.add(user);
}
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户表","sheet1"),User.class,userList);
//导出文件到指定位置
FileOutputStream outputStream = new FileOutputStream("D:\\WorkSpace\\easy-poi\\src\\main\\resources\\用户表.xls");
workbook.write(outputStream);
outputStream.close();
workbook.close();
}
执行结果：
3.4 导出对象中的list集合
定义用户类（包含用户所得荣誉信息）：
/**
* @Description 导入导出实体类
* @Author 一颗雷布斯
* @Date 2021/5/24 15:38
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("user")
public class User implements Serializable {
@Excel(name = "姓名", width = 30, orderNum = "1")
private String name;
@Excel(name = "年龄", width = 10, orderNum = "2")
private Integer age;
@Excel(name = "性别", width = 10, orderNum = "3", replace = {"女_0", "男_1"})
private Integer sex;
@Excel(name = "生日", width = 30, orderNum = "4", format = "yyyy-MM-dd HH:mm:ss")
private Date birthday;
@Excel(name = "荣誉", width = 60, orderNum = "5")
private List<String> honors;
}
测试方法：
@Test
void exportUserTest() throws IOException {
List<User> userList = new ArrayList<>();
String honorList="吃饭比赛一等奖、国际睡觉大赛二等奖";
List<String> honors = Arrays.asList(honorList.split("、"));
for (int i = 0; i < 5; i++) {
User user = new User();
user.setName("周芷若" + i);
user.setAge(18 + i);
user.setBirthday(new Date());
user.setSex(0);
//设置用户所得荣誉
user.setHonors(honors);
userList.add(user);
}
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户表","sheet1"),User.class,userList);
//导出文件到指定位置
FileOutputStream outputStream = new FileOutputStream("D:\\WorkSpace\\easy-poi\\src\\main\\resources\\用户表.xls");
workbook.write(outputStream);
outputStream.close();
workbook.close();
}
执行结果：
3.5 导出一对多关系
定义用户类（包含用户购买的水果信息，这里一个用户购买多个水果）：
@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("user")
public class User implements Serializable {
@Excel(name = "姓名", width = 30, orderNum = "1",needMerge = true)
private String name;
@Excel(name = "年龄", width = 10, orderNum = "2",needMerge = true)
private Integer age;
@Excel(name = "性别", width = 10, orderNum = "3", replace = {"女_0", "男_1"},needMerge = true)
private Integer sex;
@Excel(name = "生日", width = 30, orderNum = "4", format = "yyyy-MM-dd HH:mm:ss",needMerge = true)
private Date birthday;
@ExcelCollection(name = "购买的水果", orderNum = "5")
private List<Fruit> fruits;
}
水果类：
/**
* @Description 水果实体类
* @Author 一颗雷布斯
* @Date 2021/5/24 16:43
*/
@Data
@AllArgsConstructor
@ExcelTarget("fruit")
public class Fruit implements Serializable {
@Excel(name = "水果名")
private String name;
@Excel(name = "价格/元")
private Double price;
@Excel(name = "数量")
private Integer count;
}
测试方法：
@Test
void exportUserTest() throws IOException {
List<User> userList = new ArrayList<>();
for (int i = 0; i < 5; i++) {
User user = new User();
user.setName("周芷若" + i);
user.setAge(18 + i);
user.setBirthday(new Date());
user.setSex(0);
//设置用户购买的水果信息
user.setFruits(Arrays.asList(new Fruit("苹果",23.5,11),new Fruit("香蕉",4.98,21)));
userList.add(user);
}
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户表","sheet1"),User.class,userList);
//导出文件到指定位置
FileOutputStream outputStream = new FileOutputStream("D:\\WorkSpace\\easy-poi\\src\\main\\resources\\用户表.xls");
workbook.write(outputStream);
outputStream.close();
workbook.close();
}
执行结果：
4. 导入Excel 4.1 导入基本属性 准备文件：
定义用户类:
@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("user")
public class User implements Serializable {
@Excel(name = "姓名", width = 30, orderNum = "1")
private String name;
@Excel(name = "年龄", width = 10, orderNum = "2")
private Integer age;
@Excel(name = "性别", width = 10, orderNum = "3", replace = {"女_0", "男_1"})
private Integer sex;
@Excel(name = "生日", width = 30, orderNum = "4", format = "yyyy-MM-dd HH:mm:ss")
private Date birthday;
}
测试方法：
@Test
void importUserTest() {
ImportParams importParams = new ImportParams();
//表格标题行数,默认0
importParams.setTitleRows(1);
//表头行数,默认1
importParams.setHeadRows(1);
//导出数据 参数1:当如excel文件 参数2:导入对象的类型 参数3:导入参数配置
List<User> userList = ExcelImportUtil.importExcel(new File("D:\\WorkSpace\\easy-poi\\src\\main\\resources\\用户表.xls"), User.class,importParams);
if(!CollectionUtils.isEmpty(userList))
userList.forEach(user->{
System.out.println(user);
});
}
执行结果：
4.2 导入图片 准备文件：
定义用户类（包含用户头像，savePath 指定用户头像保存位置）：
@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("user")
public class User implements Serializable {
@Excel(name = "姓名", width = 30, orderNum = "1")
private String name;
@Excel(name = "年龄", width = 10, orderNum = "2")
private Integer age;
@Excel(name = "性别", width = 10, orderNum = "3", replace = {"女_0", "男_1"})
private Integer sex;
@Excel(name = "生日", width = 30, orderNum = "4", format = "yyyy-MM-dd HH:mm:ss")
private Date birthday;
@Excel(name = "头像信息", type = 2, width = 20,height = 30,savePath = "D:\\WorkSpace\\easy-poi\\src\\main\\resources\\head")
private String head;
}
测试方法：
@Test
void importUserTest() {
ImportParams importParams = new ImportParams();
//表格标题行数,默认0
importParams.setTitleRows(1);
//表头行数,默认1
importParams.setHeadRows(1);
//导出数据 参数1:当如excel文件 参数2:导入对象的类型 参数3:导入参数配置
List<User> userList = ExcelImportUtil.importExcel(new File("D:\\WorkSpace\\easy-poi\\src\\main\\resources\\用户表.xls"), User.class,importParams);
if(!CollectionUtils.isEmpty(userList))
userList.forEach(user->{
System.out.println(user);
});
}
执行结果：
头像保存位置：
5. 注意事项 导出 Excel 文件时，切记先把原来导出的 Excel 文件关掉，不然会报错！
-END-