EasyExcel的简单使用
EasyExcel使用
官方文档：关于EasyExcel
1.1EasyExcel相关依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.11</version>
</dependency>
1.2 写Excel
1.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方法
}
写入
@SpringBootTest
class DemoApplicationTests {
@Test
void testWriteExcel1() {
String filename = "D:\\code\\ex\\user1.xlsx";
// 向Excel中写入数据 也可以通过 head(Class<?>) 指定数据模板
EasyExcel.write(filename, User.class)
.sheet("用户信息")
.doWrite(getUserData());
}
// 根据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 testWriteExcel2() {
String filename = "D:\\code\\ex\\user2.xlsx";
// 创建ExcelWriter对象
ExcelWriter excelWriter = EasyExcel.write(filename, User.class).build();
// 创建Sheet对象
WriteSheet writeSheet = EasyExcel.writerSheet("用户信息").build();
// 向Excel中写入数据
excelWriter.write(getUserData(), writeSheet);
// 关闭流
excelWriter.finish();
}
}
最终效果：
1.2.2 最简单的写(方式二)
@Test
public void testWriteExcel2() {
String filename = "D:\\code\\ex\\user2.xlsx";
// 创建ExcelWriter对象
ExcelWriter excelWriter = EasyExcel.write(filename, User.class).build();
// 创建Sheet对象
WriteSheet writeSheet = EasyExcel.writerSheet("用户信息").build();
// 向Excel中写入数据
excelWriter.write(getUserData(), writeSheet);
// 关闭流
excelWriter.finish();
}
1.2.3 排除模型中的属性字段
指定字段不写入excel
@Test
public void testWriteExcel3() {
String filename = "D:\\code\\ex\\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());
}
1.2.4 向表格中导出指定属性
@Test
public void testWriteExcel4() {
String filename = "D:\\code\\ex\\user4.xlsx";
// 设置要导出的字段
Set<String> includeFields = new HashSet<>();
includeFields.add("userName");
includeFields.add("hireDate");
// 写Excel
EasyExcel.write(filename, User.class)
.includeColumnFiledNames(includeFields)
.sheet("用户信息")
.doWrite(getUserData());
}
1.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
void testWriteExcel1() {
String filename = "D:\\code\\ex\\user1.xlsx";
// 向Excel中写入数据 也可以通过 head(Class<?>) 指定数据模板
EasyExcel.write(filename, User.class)
.sheet("用户信息")
.doWrite(getUserData());
}
1.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方法
}
@Test
public void testWriteExcel6() {
String filename = "D:\\code\\ex\\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);
}
1.2.7 重复写到Excel的同一个Sheet中
@Test
public void testWriteExcel7() {
String filename = "D:\\code\\ex\\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();
} 
1.2.8 写到Excel的不同Sheet中
@Test
public void testWriteExcel8() {
String filename = "D:\\code\\ex\\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();
} 
1.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:\\code\\ex\\user9.xlsx";
// 向Excel中写入数据
EasyExcel.write(filename, User.class)
.sheet("用户信息")
.doWrite(getUserData());
}
1.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 = "InputStream类型")
private InputStream inputStream;
// 当使用String类型保存一个图片的时候需要使用StringImageConverter转换器
@ExcelProperty(value = "String类型", converter = StringImageConverter.class)
private String str;
// 使用二进制数据保存为一个图片
@ExcelProperty(value = "二进制数据(字节)")
private byte[] byteArr;
// 使用网络链接保存为一个图片
@ExcelProperty(value = "网络图片")
private URL url;
// lombok 会生成getter/setter方法
}
@Test
public void testWriteImageToExcel() throws IOException {
String filename = "D:\\study\\excel\\user10.xlsx";
// 图片位置
String imagePath = "D:\\study\\excel\\me.jpg";
// 网络图片
URL url = new URL("https://cn.bing.com/th?id=OHR.TanzaniaBeeEater_ZH-CN3246625733_1920x1080.jpg&rf=LaDigue_1920x1080.jpg&pid=hp");
// 将图片读取到二进制数据中
byte[] bytes = new byte[(int) new File(imagePath).length()];
InputStream inputStream = new FileInputStream(imagePath);
inputStream.read(bytes, 0, bytes.length);
List<ImageData> imageDataList = new ArrayList<>();
// 创建数据模板
ImageData imageData = ImageData.builder()
.file(new File(imagePath))
.inputStream(new FileInputStream(imagePath))
.str(imagePath)
.byteArr(bytes)
.url(url)
.build();
// 添加要写入的图片模型
imageDataList.add(imageData);
// 写数据
EasyExcel.write(filename, ImageData.class)
.sheet("帅哥")
.doWrite(imageDataList);
}
1.2.11 设置写入Excel的列宽和行高
@HeadRowHeight(value = 30) // 头部行高
@ContentRowHeight(value = 25) // 内容行高
@ColumnWidth(value = 20) // 列宽, 可以作用在类或字段上
数据模板
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@HeadRowHeight(value = 30) // 头部行高
@ContentRowHeight(value = 25) // 内容行高
@ColumnWidth(value = 20) // 列宽
public class WidthAndHeightData {
@ExcelProperty(value = "字符串标题")
private String string;
@ExcelProperty(value = "日期标题")
private Date date;
@ExcelProperty(value = "数字标题")
@ColumnWidth(value = 25)
private Double doubleData;
// lombok 会生成getter/setter方法
} 
1.2.12 通过注解形式设置写入Excel样式
数据模板
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@HeadRowHeight(value = 30) // 头部行高
@ContentRowHeight(value = 25) // 内容行高
@ColumnWidth(value = 20) // 列宽
// 头背景设置成红色 IndexedColors.RED.getIndex()
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 10)
// 头字体设置成20, 字体默认宋体
@HeadFontStyle(fontName = "宋体", fontHeightInPoints = 20)
// 内容的背景设置成绿色 IndexedColors.GREEN.getIndex()
@ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 17)
// 内容字体设置成20, 字体默认宋体
@ContentFontStyle(fontName = "宋体", fontHeightInPoints = 20)
public class DemoStyleData {
// 字符串的头背景设置成粉红 IndexedColors.PINK.getIndex()
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 14)
// 字符串的头字体设置成20
@HeadFontStyle(fontHeightInPoints = 30)
// 字符串的内容背景设置成天蓝 IndexedColors.SKY_BLUE.getIndex()
@ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 40)
// 字符串的内容字体设置成20,默认宋体
@ContentFontStyle(fontName = "宋体", fontHeightInPoints = 20)
@ExcelProperty(value = "字符串标题")
private String string;
@ExcelProperty(value = "日期标题")
private Date date;
@ExcelProperty(value = "数字标题")
private Double doubleData;
// lombok 会生成getter/setter方法
}
@Test
public void testWrite12() {
String filename = "D:\\code\\ex\\user12.xlsx";
// 构建数据
List<DemoStyleData> dataList = new ArrayList<>();
DemoStyleData data = DemoStyleData.builder()
.string("字符串")
.date(new Date())
.doubleData(888.88)
.build();
dataList.add(data);
// 向Excel中写入数据
EasyExcel.write(filename, DemoStyleData.class)
.sheet("样式设置测试")
.doWrite(dataList);
}
1.2.13 合并单元格
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@HeadRowHeight(value = 25) // 头部行高
@ContentRowHeight(value = 20) // 内容行高
@ColumnWidth(value = 20) // 列宽
/**
* @OnceAbsoluteMerge 指定从哪一行/列开始,哪一行/列结束,进行单元格合并
* firstRowIndex 起始行索引,从0开始
* lastRowIndex 结束行索引
* firstColumnIndex 起始列索引,从0开始
* lastColumnIndex 结束列索引
*/
// 例如: 第2-3行,2-3列进行合并
@OnceAbsoluteMerge(firstRowIndex = 1, lastRowIndex = 2, firstColumnIndex = 1, lastColumnIndex = 2)
public class DemoMergeData {
// 每隔两行合并一次(竖着合并单元格)
// @ContentLoopMerge(eachRow = 2)
@ExcelProperty(value = "字符串标题")
private String string;
@ExcelProperty(value = "日期标题")
private Date date;
@ExcelProperty(value = "数字标题")
private Double doubleData;
// lombok 会生成getter/setter方法
}
@Test
public void testWrite13() {
String filename = "D:\\code\\ex\\user13.xlsx";
// 构建数据
List<DemoMergeData> dataList = new ArrayList<>();
DemoMergeData data = DemoMergeData.builder()
.string("字符串")
.date(new Date())
.doubleData(888.88)
.build();
dataList.add(data);
// 向Excel中写入数据
EasyExcel.write(filename, DemoMergeData.class)
.sheet("单元格合并测试")
.doWrite(dataList);
}
// 例如: 第2-3行,2-3列进行合并 @OnceAbsoluteMerge (firstRowIndex = 1, lastRowIndex = 2, firstColumnIndex = 1, lastColumnIndex = 2)
// 每隔两行合并一次(竖着合并单元格) @ContentLoopMerge(eachRow = 2)
1.2.14 写的数据转换器
在实际应用场景中, 我们系统db存储的数据可以是枚举, 在界面或导出到Excel文件需要展示为对于的枚举值形式.
比如: 性别, 状态等. EasyExcel提供了转换器接口Converter供我们使用, 我们只需要自定义转换器实现接口, 并将自定义转换器类型传入要转换的属性字段中. 以下面的性别gender字段为例:
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public class UserModel {
@ExcelProperty(value = "用户编号", index = 0)
private Integer userId;
@ExcelProperty(value = "姓名", index = 1)
private String userName;
// 性别添加了转换器, db中存入的是integer类型的枚举 0 , 1 ,2
@ExcelProperty(value = "性别", index = 3, converter = GenderConverter.class)
private Integer gender;
@ExcelProperty(value = "工资", index = 4)
@NumberFormat(value = "###.#")
private Double salary;
@ExcelProperty(value = "入职时间", index = 2)
@DateTimeFormat(value = "yyyy年MM月dd日 HH时mm分ss秒")
private Date hireDate;
// lombok 会生成getter/setter方法
}
自定义转换器
/**
* 类描述：性别字段的数据转换器
* @Author wang_qz
* @Date 2021/8/15 19:16
* @Version 1.0
*/
public class GenderConverter implements Converter<Integer> {
private static final String MALE = "男";
private static final String FEMALE = "女";
private static final String NONE = "未知";
// Java数据类型 integer
@Override
public Class supportJavaTypeKey() {
return Integer.class;
}
// Excel文件中单元格的数据类型 string
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
// 读取Excel文件时将string转换为integer
@Override
public Integer convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration)
throws Exception {
String value = cellData.getStringValue();
if (Objects.equals(FEMALE, value)) {
return 0; // 0-女
} else if (Objects.equals(MALE, value)) {
return 1; // 1-男
}
return 2; // 2-未知
}
// 写入Excel文件时将integer转换为string
@Override
public CellData convertToExcelData(Integer value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
if (value == 1) {
return new CellData(MALE);
} else if (value == 0) {
return new CellData(FEMALE);
}
return new CellData(NONE); // 不男不女
}
}
导出到Excel的代码
@Test
public void testWriteExcel() {
String filename = "D:\\study\\excel\\user1.xlsx";
// 向Excel中写入数据
EasyExcel.write(filename, UserModel.class)
.sheet("用户信息")
.doWrite(getUserData());
}
// 根据user模板构建数据
private List<UserModel> getUserData() {
List<UserModel> users = new ArrayList<>();
for (int i = 1; i <= 10; i++) {
UserModel user = UserModel.builder()
.userId(i)
.userName("admin" + i)
.gender(i % 2 == 0 ? 0 : 2) // 性别枚举
.salary(i * 1000 + 8.888)
.hireDate(new Date())
.build();
users.add(user);
}
return users;
}
1.3 读Excel
1.3.1 读API的拆分
在读取Excel表格数据时, 将读取的每行记录映射成一条LinkedHashMap记录, 而没有映射成实体类.
@Test
public void testRead0() {
String filename = "D:\\code\\ex\\user1.xlsx";
// 创建ExcelReaderBuilder对象
ExcelReaderBuilder readerBuilder = EasyExcel.read();
// 获取文件对象
readerBuilder.file(filename);
// 指定映射的数据模板
// readerBuilder.head(DemoData.class);
// 指定sheet
readerBuilder.sheet(0);
// 自动关闭输入流
readerBuilder.autoCloseStream(true);
// 设置Excel文件格式
readerBuilder.excelType(ExcelTypeEnum.XLSX);
// 注册监听器进行数据的解析
readerBuilder.registerReadListener(new AnalysisEventListener() {
// 每解析一行数据,该方法会被调用一次
@Override
public void invoke(Object demoData, AnalysisContext analysisContext) {
// 如果没有指定数据模板, 解析的数据会封装成 LinkedHashMap返回
// demoData instanceof LinkedHashMap 返回 true
System.out.println("解析数据为:" + demoData.toString());
}
// 全部解析完成被调用
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("解析完成...");
// 可以将解析的数据保存到数据库
}
});
readerBuilder.doReadAll();
/* // 构建读取器
ExcelReader excelReader = readerBuilder.build();
// 读取Excel
excelReader.readAll();
// 关闭流
excelReader.finish();*/
}1.3.2 最简单的读(方式一)
Excel数据类型
注意: Java类中的属性字段顺序和Excel中的表头字段顺序一致, 可以不写@ExcelProperty
@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方法
}读取excel代码
关键是写一个监听器,实现AnalysisEventListener, 每解析一行数据会调用invoke方法返回解析的数据, 当全部解析完成后会调用doAfterAllAnalysed方法. 我们重写invoke方法和doAfterAllAnalysed方法即可.
@Test
public void testReadExcel() {
// 读取的excel文件路径
String filename = "D:\\code\\ex\\user1.xlsx";
// 读取excel
EasyExcel.read(filename, User.class, new AnalysisEventListener<User>() {
// 每解析一行数据,该方法会被调用一次
@Override
public void invoke(User user, AnalysisContext analysisContext) {
System.out.println("解析数据为:" + user.toString());
}
// 全部解析完成被调用
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("解析完成...");
// 可以将解析的数据保存到数据库
}
}).sheet().doRead();
}效果:
1.3.3 最简单的读(方式二)
代码
@Test
public void testReadExcel2() {
// 读取的excel文件路径
String filename = "D:\\code\\ex\\user1.xlsx";
// 创建一个数据格式来装读取到的数据
Class<User> head = User.class;
// 创建ExcelReader对象
ExcelReader excelReader = EasyExcel.read(filename, head, new AnalysisEventListener<User>() {
// 每解析一行数据,该方法会被调用一次
@Override
public void invoke(User demoData, AnalysisContext analysisContext) {
System.out.println("解析数据为:" + demoData.toString());
}
// 全部解析完成被调用
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("解析完成...");
// 可以将解析的数据保存到数据库
}
}).build();
// 创建sheet对象,并读取Excel的第一个sheet(下标从0开始), 也可以根据sheet名称获取
ReadSheet sheet = EasyExcel.readSheet(0).build();
// 读取sheet表格数据, 参数是可变参数,可以读取多个sheet
excelReader.read(sheet);
// 需要自己关闭流操作,在读取文件时会创建临时文件,如果不关闭,会损耗磁盘,严重的磁盘爆掉
excelReader.finish();
}
1.3.4 格式化Excel中的数据格式
要读取的源数据, 日期格式是yyyy年MM月dd日 HH时mm分ss秒, 数字带小数点
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public class DemoData {
@ExcelProperty(value = "字符串标题", index = 0)
private String name;
@ExcelProperty(value = "日期标题", index = 1)
// 格式化日期类型数据
@DateTimeFormat(value = "yyyy年MM月dd日 HH时mm分ss秒")
private Date hireDate;
@ExcelProperty(value = "数字标题", index = 2)
// 格式化数字类型数据,保留一位小数
@NumberFormat(value = "###.#")
private String salary;
//注意: @NumberFormat对于Double类型的数据格式化会失效,建议使用String类型接收数据进行格式化
// private Double salary;
// lombok 会生成getter/setter方法
}
读取excel代码同上面读取方式一样.
1.3.5 读取多个sheet表格
1.3.5.1 读所有sheet
读方式一, 使用ExcelReaderBuilder#doReadAll方法
@Test
public void testReadExcel() {
// 读取的excel文件路径
String filename = "D:\\study\\excel\\read.xlsx";
// 读取excel
EasyExcel.read(filename, DemoData.class, new AnalysisEventListener<DemoData>() {
// 每解析一行数据,该方法会被调用一次
@Override
public void invoke(DemoData demoData, AnalysisContext analysisContext) {
System.out.println("解析数据为:" + demoData.toString());
}
// 全部解析完成被调用
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("解析完成...");
// 可以将解析的数据保存到数据库
}
})
// .sheet(0).doRead();
.doReadAll(); // 读取全部sheet
}
读方式二, 使用ExcelReader#readAll方法
@Test
public void testReadExcel2() {
// 读取的excel文件路径
String filename = "D:\\study\\excel\\read.xlsx";
// 创建一个数据格式来装读取到的数据
Class<DemoData> head = DemoData.class;
// 创建ExcelReader对象
ExcelReader excelReader = EasyExcel.read(filename, head,
new AnalysisEventListener<DemoData>() {
// 每解析一行数据,该方法会被调用一次
@Override
public void invoke(DemoData demoData, AnalysisContext analysisContext) {
System.out.println("解析数据为:" + demoData.toString());
}
// 全部解析完成被调用
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("解析完成...");
// 可以将解析的数据保存到数据库
}
}).build();
// 创建sheet对象,并读取Excel的第一个sheet(下标从0开始), 也可以根据sheet名称获取
ReadSheet sheet = EasyExcel.readSheet(0).build();
// 读取sheet表格数据 , 参数是可变参数,可以读取多个sheet
// excelReader.read(sheet);
excelReader.readAll(); // 读所有sheet
// 需要自己关闭流操作,在读取文件时会创建临时文件,如果不关闭,会损耗磁盘,严重的磁盘爆掉
excelReader.finish();
}
1.3.5.2 读指定的多个sheet
不同sheet表格的数据模板可能不一样,这时候就需要分别构建不同的sheet对象,分别为其指定对于的数据模板.
@Test
public void testReadExcel3() {
// 读取的excel文件路径
String filename = "D:\\study\\excel\\read.xlsx";
// 构建ExcelReader对象
ExcelReader excelReader = EasyExcel.read(filename).build();
// 构建sheet对象
ReadSheet sheet0 = EasyExcel.readSheet(0)
.head(DemoData.class) // 指定sheet0的数据模板
.registerReadListener(new AnalysisEventListener<DemoData>() {
// 每解析一行数据,该方法会被调用一次
@Override
public void invoke(DemoData demoData, AnalysisContext analysisContext) {
System.out.println("解析数据为:" + demoData.toString());
}
// 全部解析完成被调用
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("解析完成...");
// 可以将解析的数据保存到数据库
}
}).build();
// 读取sheet,有几个就构建几个sheet进行读取
excelReader.read(sheet0);
// 需要自己关闭流操作,在读取文件时会创建临时文件,如果不关闭,会损耗磁盘,严重的磁盘爆掉
excelReader.finish();
}
1.3.6 读的数据转换器
上面的写已经提到了转换器, 读也是一样. 将Excel文件中的字符串枚举值转换成要存入db的整数类型的枚举.
1.4 填充Excel
1.4.1 简单填充
创建Excel模板格式
填充单个属性使用{}作为占位符, 在大括号里面定义属性名称, 如果{}想不作为占位符展示出来,可以使用反斜杠进行转义
填充数据的Java类(数据模板)
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public class FillData {
private String name;
private double number;
// lombok 会生成getter/setter方法
}
填充的代码
@Test
public void testFillExcel() {
// 根据哪个模板进行填充
String template = "D:\\study\\excel\\template.xlsx";
// 填充完成之后的excel
String fillname = "D:\\study\\excel\\fill.xlsx";
// 构建数据
FillData fillData = FillData.builder()
.name("小米")
.number(888.888)
.build();
// 填充excel 单组数据填充
EasyExcel.write(fillname).withTemplate(template).sheet(0).doFill(fillData);
}
效果
1.4.2 列表填充
创建Excel模板格式
填充的数据模板
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public class FillData {
private String name;
private double number;
// lombok 会生成getter/setter方法
}
填充Excel代码
@Test
public void testFillExcel2() {
// 根据哪个模板进行填充
String template = "D:\\study\\excel\\template2.xlsx";
// 填充完成之后的excel
String fillname = "D:\\study\\excel\\fill2.xlsx";
// 填充excel 多组数据重复填充
EasyExcel.write(fillname)
.withTemplate(template)
.sheet(0)
.doFill(getFillData());
}
// 构建数据
private List<FillData> getFillData() {
List<FillData> fillDataList = new ArrayList<>();
for (int i = 1; i <= 10; i++) {
// 构建数据
FillData fillData = FillData.builder()
.name("小米" + i)
.number(i * 1000 + 88.88)
.build();
fillDataList.add(fillData);
}
return fillDataList;
}
1.4.3 组合填充
创建Excel填充模板
填充的数据模板
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public class FillData {
private String name;
private double number;
// lombok 会生成getter/setter方法
}
组合填充Excel代码
@Test
public void testFillExcel3() {
// 根据哪个模板进行填充
String template = "D:\\study\\excel\\template3.xlsx";
// 填充完成之后的excel
String fillname = "D:\\study\\excel\\fill3.xlsx";
// 创建填充配置 换行填充
FillConfig fillConfig = FillConfig.builder().forceNewRow(true).build();
// 创建写对象
ExcelWriter excelWriter =
EasyExcel.write(fillname).withTemplate(template).build();
// 创建Sheet对象
WriteSheet sheet = EasyExcel.writerSheet(0).build();
// 多组填充excel
excelWriter.fill(getFillData(), fillConfig, sheet);
// 单组填充
HashMap<String, Object> unitData = new HashMap<>();
unitData.put("nickname", "张三");
unitData.put("salary", 8088.66);
excelWriter.fill(unitData, sheet);
// 关闭流
excelWriter.finish();
}
如果没有设置填充配置换行FillConfig为true , 效果将是单组填充的数据会覆盖所在行的多组数据填充效果.
FillConfig fillConfig = FillConfig.builder().forceNewRow(false).build();
1.4.4 水平填充
创建Excel填充模板
数据模板
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public class FillData {
private String name;
private double number;
// lombok 会生成getter/setter方法
}
水平填充代码
@Test
public void testFillExcel4() {
// 根据哪个模板进行填充
String template = "D:\\study\\excel\\template4.xlsx";
// 填充完成之后的excel
String fillname = "D:\\study\\excel\\fill4.xlsx";
// 创建填充配置 水平填充
FillConfig fillConfig = FillConfig.builder()
// .forceNewRow(true)
.direction(WriteDirectionEnum.HORIZONTAL).build();
// 创建写对象
ExcelWriter excelWriter = EasyExcel.write(fillname,
FillData.class).withTemplate(template).build();
// 创建Sheet对象
WriteSheet sheet = EasyExcel.writerSheet(0).build();
// 多组填充excel
excelWriter.fill(getFillData(), fillConfig, sheet);
// 关闭流
excelWriter.finish();
}
效果
1.4.5 报表导出案例
创建Excel填充模板
会员数据模板
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public class MemberVip {
private Integer id;
private String name;
private String gender;
private String birthday;
// lombok 会生成getter/setter方法jav
}
组合填充报表代码
@Test
public void testFillExcel5() {
// 根据哪个模板进行填充
String template = "D:\\study\\excel\\template5.xlsx";
// 填充完成之后的excel
String fillname = "D:\\study\\excel\\fill5.xlsx";
// 创建填充配置
FillConfig fillConfig = FillConfig.builder().forceNewRow(true).build();
// 创建写对象
ExcelWriter excelWriter = EasyExcel.write(fillname)
.withTemplate(template).build();
// 创建Sheet对象
WriteSheet sheet = EasyExcel.writerSheet(0).build();
/***准备数据 start*****/
HashMap<String, Object> dateMap = new HashMap<>();
dateMap.put("date", "2021-08-08");
HashMap<String, Object> memberMap = new HashMap<>();
memberMap.put("increaseCount", 500);
memberMap.put("totalCount", 999);
HashMap<String, Object> curMonthMemberMap = new HashMap<>();
curMonthMemberMap.put("increaseCountWeek", 100);
curMonthMemberMap.put("increaseCountMonth", 200);
List<MemberVip> memberVips = getMemberVips();
/***准备数据 end*****/
// 多组填充excel
excelWriter.fill(dateMap, sheet);
excelWriter.fill(memberMap, sheet);
excelWriter.fill(curMonthMemberMap, sheet);
excelWriter.fill(memberVips, fillConfig, sheet);
// 关闭流
excelWriter.finish();
}
1.5 Web操作(Excel上传/下载)
1.5.1 Excel文件下载
-
数据模板
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@HeadRowHeight(value = 30)
@ContentRowHeight(value = 25)
@ColumnWidth(value = 30)
public class UserExcel {
@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;
}
编写controller及下载handler
**
* 使用EasyExcel操作excel文件上传/下载
*/
@Controller
@RequestMapping(value = "/xlsx")
public class EasyExcelController {
@RequestMapping("/toExcelPage")
public String todownloadPage() {
return "excelPage";
}
/**
* 下载Excel
* @param request
* @param response
*/
@RequestMapping(value = "/downloadExcel")
public void downloadExcel(HttpServletRequest request,
HttpServletResponse response) throws Exception {
// 设置响应头
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 设置防止中文名乱码
String filename = URLEncoder.encode("员工信息", "utf-8");
// 文件下载方式(附件下载还是在当前浏览器打开)
response.setHeader("Content-disposition", "attachment;filename=" +
filename + ".xlsx");
// 构建写入到excel文件的数据
List<UserExcel> userExcels = new ArrayList<>();
UserExcel userExce1 = new UserExcel(1001, "张三", "男", 1333.33,
new Date());
UserExcel userExce2 = new UserExcel(1002, "李四", "男", 1356.83,
new Date());
UserExcel userExce3 = new UserExcel(1003, "王五", "男", 1883.66,
new Date());
UserExcel userExce4 = new UserExcel(1004, "赵六", "男", 1393.39,
new Date());
userExcels.add(userExce1);
userExcels.add(userExce2);
userExcels.add(userExce3);
userExcels.add(userExce4);
// 写入数据到excel
EasyExcel.write(response.getOutputStream(), UserExcel.class)
.sheet("用户信息")
.doWrite(userExcels);
}
}
1.5.2 Excel文件上传
-
数据模板跟上面下载一样
-
编写上传handler
1：
@RequestMapping("/uploadExcel")
public void uploadExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {
DiskFileItemFactory factory = new DiskFileItemFactory();
ServletFileUpload fileUpload = new ServletFileUpload(factory);
// 设置单个文件大小为3M 2的10次幂=1024
fileUpload.setFileSizeMax((long) (3 * Math.pow(2, 20)));
// 总文件大小为30M
fileUpload.setSizeMax((long) (30 * Math.pow(2, 20)));
List<FileItem> list = fileUpload.parseRequest(request);
for (FileItem fileItem : list) {
// 判断是否为附件
if (!fileItem.isFormField()) {
// 是附件
InputStream inputStream = fileItem.getInputStream();
EasyExcel.read(inputStream, UserExcel.class,
new AnalysisEventListener<UserExcel>() {
// 每解析一行数据,该方法会被调用一次
@Override
public void invoke(UserExcel data,
AnalysisContext analysisContext) {
System.out.println("解析数据为:" + data.toString());
}
// 全部解析完成被调用
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("解析完成...");
// 可以将解析的数据保存到数据库
}
}).sheet().doRead();
}
}
}
2：
@RequestMapping("/uploadExcel")
@ResponseBody
public String uploadExcel(@RequestParam("file") Part part) throws Exception {
// 获取上传的文件流
InputStream inputStream = part.getInputStream();
// 读取Excel
EasyExcel.read(inputStream, UserExcel.class,
new AnalysisEventListener<UserExcel>() {
// 每解析一行数据,该方法会被调用一次
@Override
public void invoke(UserExcel data, AnalysisContext analysisContext) {
System.out.println("解析数据为:" + data.toString());
}
// 全部解析完成被调用
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("解析完成...");
// 可以将解析的数据保存到数据库
}
}).sheet().doRead();
return "上传Excel文件成功";
}
3 EasyExcel使用优化
3.1 监听器优化
每次解析不同数据模型都要新增一个监听器, 重复工作量大;
即使用了匿名内部类,程序也显得臃肿;
数据处理一般都会存在于项目的service中, 监听器难免会依赖dao层, 导致程序耦合度高.
解决方案:
通过泛型指定数据模型类型, 针对不同类型的数据模型只需要定义一个监听器即可;
使用jdk8新特性中的函数式接口, 将数据处理从监听器中剥离出去, 进行解耦.
监听器代码:
/**
* 类描述：easyexcel工具类
* @Author wang_qz
* @Date 2021/8/15 18:15
* @Version 1.0
*/
public class EasyExcelUtils<T> {
/**
* 获取读取Excel的监听器对象
* 为了解耦及减少每个数据模型bean都要创建一个监听器的臃肿, 使用泛型指定数据模型类型
* 使用jdk8新特性中的函数式接口 Consumer
* 可以实现任何数据模型bean的数据解析, 不用重复定义监听器
* @param consumer 处理解析数据的函数, 一般可以是数据入库逻辑的函数
* @param threshold 阈值,达到阈值就处理一次存储的数据
* @param <T> 数据模型泛型
* @return 返回监听器
*/
public static <T> AnalysisEventListener<T> getReadListener(Consumer<List<T>> consumer, int threshold) {
return new AnalysisEventListener<T>() {
/**
* 存储解析的数据 T t
*/
// ArrayList基于数组实现, 查询更快
// List<T> dataList = new ArrayList<>(threshold);
// LinkedList基于双向链表实现, 插入和删除更快
List<T> dataList = new LinkedList<>();
/**
* 每解析一行数据事件调度中心都会通知到这个方法, 订阅者1
* @param data 解析的每行数据
* @param context
*/
@Override
public void invoke(T data, AnalysisContext context) {
dataList.add(data);
// 达到阈值就处理一次存储的数据
if (dataList.size() >= threshold) {
consumer.accept(dataList);
dataList.clear();
}
}
/**
* excel文件解析完成后,事件调度中心会通知到该方法, 订阅者2
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 最后阈值外的数据做处理
if (dataList.size() > 0) {
consumer.accept(dataList);
}
}
};
}
/**
* 获取读取Excel的监听器对象, 不指定阈值, 默认阈值为 2000
* @param consumer
* @param <T>
* @return
*/
public static <T> AnalysisEventListener<T> getReadListener(Consumer<List<T>>
consumer) {
return getReadListener(consumer, 2000);
}
}
再来看读取Excel的 代码:
/**
* 采用解耦的自定义监听器读取Excel, 可以实现任何数据模型bean的读取
*/
@Test
public void testReadExcelN() {
// 读取的excel文件路径
String filename = "D:\\study\\excel\\user1.xlsx";
// 读取excel
EasyExcel.read(filename, UserModel.class,
EasyExcelUtils.getReadListener(dataProcess()))
.doReadAll(); // 读取全部sheet
}
/**
* 传给监听器的是一个处理解析数据的函数, 当调用consumer的accept方法时就会调用传递的函数逻辑
* 这里传递的函数是对解析结果集的遍历打印操作, 也可以是数据入库操作
* @return
*/
public Consumer<List<UserModel>> dataProcess() {
Consumer<List<UserModel>> consumer = users -> users.forEach(System.out::println);
return consumer;
}