1 EasyExcel简介
EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。
github地址: https://github.com/alibaba/easyexcel
官方文档: https://www.yuque.com/easyexcel/doc/easyexcel
B站视频: https://www.bilibili.com/video/BV1Ff4y1U7Qc
Excel解析流程图:
EasyExcel读取Excel的解析原理:
2 EasyExcel使用
2.1 EasyExcel相关依赖
添加maven依赖, 依赖的poi最低版本3.17
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.3</version>
</dependency>
2.2 写Excel
2.2.1 最简单的写(方式一)
创建实体类,下面也用这个数据模型
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public class User {
@ExcelProperty(value = "用户编号")
private Integer userId;
@ExcelProperty(value = "姓名")
private String userName;
@ExcelProperty(value = "性别")
private String gender;
@ExcelProperty(value = "工资")
private Double salary;
@ExcelProperty(value = "入职时间")
private Date hireDate;
// lombok 会生成getter/setter方法
}
写入
// 根据user模板构建数据
private List<User> getUserData() {
List<User> users = new ArrayList<>();
for (int i = 1; i <= 10; i++) {
User user = User.builder()
.userId(i)
.userName("admin" + i)
.gender(i % 2 == 0 ? "男" : "女")
.salary(i * 1000.00)
.hireDate(new Date())
.build();
users.add(user);
}
return users;
}
@Test
public void testWriteExcel() {
String filename = "D:\study\excel\user1.xlsx";
// 向Excel中写入数据 也可以通过 head(Class<?>) 指定数据模板
EasyExcel.write(filename, User.class)
.sheet("用户信息")
.doWrite(getUserData());
}
效果:
2.2.2 最简单的写(方式二)
@Test
public void testWriteExcel2() {
String filename = "D:\study\excel\user2.xlsx";
// 创建ExcelWriter对象
ExcelWriter excelWriter = EasyExcel.write(filename, User.class).build();
// 创建Sheet对象
WriteSheet writeSheet = EasyExcel.writerSheet("用户信息").build();
// 向Excel中写入数据
excelWriter.write(getUserData(), writeSheet);
// 关闭流
excelWriter.finish();
}
效果:
2.2.3 排除模型中的属性字段
指定字段不写入excel
@Test
public void testWriteExcel3() {
String filename = "D:\study\excel\user3.xlsx";
// 设置排除的属性 也可以在数据模型的字段上加@ExcelIgnore注解排除
Set<String> excludeField = new HashSet<>();
excludeField.add("hireDate");
excludeField.add("salary");
// 写Excel
EasyExcel.write(filename, User.class)
.excludeColumnFiledNames(excludeField)
.sheet("用户信息")
.doWrite(getUserData());
}
效果:
2.2.4 向表格中导出指定属性
@Test
public void testWriteExcel4() {
String filename = "D:\study\excel\user4.xlsx";
// 设置要导出的字段
Set<String> includeFields = new HashSet<>();
includeFields.add("userName");
includeFields.add("hireDate");
// 写Excel
EasyExcel.write(filename, User.class)
.includeColumnFiledNames(includeFields)
.sheet("用户信息")
.doWrite(getUserData());
}
效果:
2.2.5 插入指定的列
将Java对象中指定的属性, 插入到Eexcel表格中的指定列(在Excel表格中进行列排序), 使用index属性指定列顺序.
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public class User {
@ExcelProperty(value = "用户编号", index = 0)
private Integer userId;
@ExcelProperty(value = "姓名", index = 1)
private String userName;
@ExcelProperty(value = "性别", index = 3)
private String gender;
@ExcelProperty(value = "工资", index = 4)
private Double salary;
@ExcelProperty(value = "入职时间", index = 2)
private Date hireDate;
// lombok 会生成getter/setter方法
}
@Test
public void testWriteExcel5() {
String filename = "D:\study\excel\user5.xlsx";
// 向Excel中写入数据
EasyExcel.write(filename, User.class)
.sheet("用户信息")
.doWrite(getUserData());
}
效果:
2.2.6 复杂头数据写入
@ExcelProperty注解的value属性是一个数组类型, 设置多个head时会自动合并.
数据模板:
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public class ComplexHeadUser {
@ExcelProperty(value = {"group1", "用户编号"}, index = 0)
private Integer userId;
@ExcelProperty(value = {"group1", "姓名"}, index = 1)
private String userName;
@ExcelProperty(value = {"group2", "入职时间"}, index = 2)
private Date hireDate;
// lombok 会生成getter/setter方法
}
写excel代码
@Test
public void testWriteExcel6() {
String filename = "D:\study\excel\user6.xlsx";
List<ComplexHeadUser> users = new ArrayList<>();
for (int i = 1; i <= 10; i++) {
ComplexHeadUser user = ComplexHeadUser.builder()
.userId(i)
.userName("大哥" + i)
.hireDate(new Date())
.build();
users.add(user);
}
// 向Excel中写入数据
EasyExcel.write(filename, ComplexHeadUser.class)
.sheet("用户信息")
.doWrite(users);
}
效果:
2.2.7 重复写到Excel的同一个Sheet中
代码:
@Test
public void testWriteExcel7() {
String filename = "D:\study\excel\user7.xlsx";
// 创建ExcelWriter对象
ExcelWriter excelWriter = EasyExcel.write(filename, User.class).build();
// 创建Sheet对象
WriteSheet writeSheet = EasyExcel.writerSheet("用户信息").build();
// 向Excel的同一个Sheet重复写入数据
for (int i = 0; i < 2; i++) {
excelWriter.write(getUserData(), writeSheet);
}
// 关闭流
excelWriter.finish();
}
2.2.8 写到Excel的不同Sheet中
代码:
@Test
public void testWriteExcel8() {
String filename = "D:\study\excel\user8.xlsx";
// 创建ExcelWriter对象
ExcelWriter excelWriter = EasyExcel.write(filename, User.class).build();
// 向Excel的同一个Sheet重复写入数据
for (int i = 0; i < 2; i++) {
// 创建Sheet对象
WriteSheet writeSheet = EasyExcel.writerSheet("用户信息" + i).build();
excelWriter.write(getUserData(), writeSheet);
}
// 关闭流
excelWriter.finish();
}
2.2.9 日期/数字类型格式化
对于日期和数字,有时候需要对其展示的样式进行格式化, EasyExcel提供了以下注解
@DateTimeFormat 日期格式化
@NumberFormat 数字格式化(小数或百分数)
数据模板对象:
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public class User {
@ExcelProperty(value = "用户编号", index = 0)
private Integer userId;
@ExcelProperty(value = "姓名", index = 1)
private String userName;
@ExcelProperty(value = "性别", index = 3)
private String gender;
@ExcelProperty(value = "工资", index = 4)
@NumberFormat(value = "###.#") // 数字格式化,保留1位小数
private Double salary;
@ExcelProperty(value = "入职时间", index = 2)
@DateTimeFormat(value = "yyyy年MM月dd日 HH时mm分ss秒") // 日期格式化
private Date hireDate;
// lombok 会生成getter/setter方法
}
写入
@Test
public void testWriteExcel9() {
String filename = "D:\study\excel\user9.xlsx";
// 向Excel中写入数据
EasyExcel.write(filename, User.class)
.sheet("用户信息")
.doWrite(getUserData());
}
效果:
2.2.10 写入图片到Excel
数据模板(Java对象)
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@ContentRowHeight(value = 100) // 内容行高
@ColumnWidth(value = 20) // 列宽
public class ImageData {
//使用抽象文件表示一个图片
@ExcelProperty(value = "File类型")
private File file;
// 使用输入流保存一个图片
@ExcelProperty(value