POI:用来处理 Excel、Word 等文件的技术。EasyPoi: 让没有接触过 POI 的人也能方便的写出 Excel 的导入导出功能。说白了就是用了 EasyPoi,文件的导入导出变得 so easy!
官网地址: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 文件关掉,不然会报错!
今天的文章导入导出这么丝滑,你用的是 EasyPoi 吗?分享到此就结束了,感谢您的阅读。
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
如需转载请保留出处:https://bianchenghao.cn/23564.html