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");// 写ExcelEasyExcel.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");// 写ExcelEasyExcel.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 = "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);
}

效果:

2.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方法
}

写入

@Test
public void testWrite11() {String filename = "D:\\study\\excel\\user11.xlsx";// 构建数据List<WidthAndHeightData> dataList = new ArrayList<>();WidthAndHeightData data = WidthAndHeightData.builder().string("字符串").date(new Date()).doubleData(888.88).build();dataList.add(data);// 向Excel中写入数据EasyExcel.write(filename, WidthAndHeightData.class).sheet("行高和列宽测试").doWrite(dataList);
}

效果:

2.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:\\study\\excel\\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);}

效果:

2.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:\\study\\excel\\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);
}

效果

@ContentLoopMerge

@OnceAbsoluteMerge

2.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@Overridepublic Class supportJavaTypeKey() {return Integer.class;}// Excel文件中单元格的数据类型  string@Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}// 读取Excel文件时将string转换为integer@Overridepublic 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@Overridepublic 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;
}

效果

2.3 读Excel

2.3.1 读API的拆分

在读取Excel表格数据时, 将读取的每行记录映射成一条LinkedHashMap记录, 而没有映射成实体类.

@Test
public void testRead() {String filename = "D:\\study\\excel\\read.xlsx";// 创建ExcelReaderBuilder对象ExcelReaderBuilder readerBuilder = EasyExcel.read();// 获取文件对象readerBuilder.file(filename);// 指定映射的数据模板
//  readerBuilder.head(DemoData.class);// 指定sheetreaderBuilder.sheet(0);// 自动关闭输入流readerBuilder.autoCloseStream(true);// 设置Excel文件格式readerBuilder.excelType(ExcelTypeEnum.XLSX);// 注册监听器进行数据的解析readerBuilder.registerReadListener(new AnalysisEventListener() {// 每解析一行数据,该方法会被调用一次@Overridepublic void invoke(Object demoData, AnalysisContext analysisContext) {// 如果没有指定数据模板, 解析的数据会封装成 LinkedHashMap返回// demoData instanceof LinkedHashMap 返回 trueSystem.out.println("解析数据为:" + demoData.toString());}// 全部解析完成被调用@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {System.out.println("解析完成...");// 可以将解析的数据保存到数据库}});readerBuilder.doReadAll();/*  // 构建读取器ExcelReader excelReader = readerBuilder.build();// 读取ExcelexcelReader.readAll();// 关闭流excelReader.finish();*/}

2.3.2 最简单的读(方式一)

Excel数据类型

字符串标题	日期标题	数字标题
小明	2020-05-05 10:10:10	888.88

数据模板

注意: Java类中的属性字段顺序和Excel中的表头字段顺序一致, 可以不写@ExcelProperty

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public class DemoData {// 根据Excel中指定列名或列的索引读取@ExcelProperty(value = "字符串标题", index = 0)private String name;@ExcelProperty(value = "日期标题", index = 1)private Date hireDate;@ExcelProperty(value = "数字标题", index = 2)private Double salary;// lombok 会生成getter/setter方法
}

读取excel代码

关键是写一个监听器,实现AnalysisEventListener, 每解析一行数据会调用invoke方法返回解析的数据, 当全部解析完成后会调用doAfterAllAnalysed方法. 我们重写invoke方法和doAfterAllAnalysed方法即可.

@Test
public void testReadExcel() {// 读取的excel文件路径String filename = "D:\\study\\excel\\read.xlsx";// 读取excelEasyExcel.read(filename, DemoData.class, new AnalysisEventListener<DemoData>() {// 每解析一行数据,该方法会被调用一次@Overridepublic void invoke(DemoData demoData, AnalysisContext analysisContext) {System.out.println("解析数据为:" + demoData.toString());}// 全部解析完成被调用@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {System.out.println("解析完成...");// 可以将解析的数据保存到数据库}}).sheet().doRead();
}

效果:

2.3.3 最简单的读(方式二)

读excel的方式二代码

@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>() {// 每解析一行数据,该方法会被调用一次@Overridepublic void invoke(DemoData demoData, AnalysisContext analysisContext) {System.out.println("解析数据为:" + demoData.toString());}// 全部解析完成被调用@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {System.out.println("解析完成...");// 可以将解析的数据保存到数据库}}).build();// 创建sheet对象,并读取Excel的第一个sheet(下标从0开始), 也可以根据sheet名称获取ReadSheet sheet = EasyExcel.readSheet(0).build();// 读取sheet表格数据, 参数是可变参数,可以读取多个sheetexcelReader.read(sheet);// 需要自己关闭流操作,在读取文件时会创建临时文件,如果不关闭,会损耗磁盘,严重的磁盘爆掉excelReader.finish();
}

2.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代码同上面读取方式一样.

效果:

2.3.5 读取多个sheet表格

2.3.5.1 读所有sheet

读方式一, 使用ExcelReaderBuilder#doReadAll方法

@Testpublic void testReadExcel() {// 读取的excel文件路径String filename = "D:\\study\\excel\\read.xlsx";// 读取excelEasyExcel.read(filename, DemoData.class, new AnalysisEventListener<DemoData>() {// 每解析一行数据,该方法会被调用一次@Overridepublic void invoke(DemoData demoData, AnalysisContext analysisContext) {System.out.println("解析数据为:" + demoData.toString());}// 全部解析完成被调用@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {System.out.println("解析完成...");// 可以将解析的数据保存到数据库}})
//         .sheet(0).doRead();.doReadAll(); // 读取全部sheet}

读方式二, 使用ExcelReader#readAll方法

@Testpublic 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>() {// 每解析一行数据,该方法会被调用一次@Overridepublic void invoke(DemoData demoData, AnalysisContext analysisContext) {System.out.println("解析数据为:" + demoData.toString());}// 全部解析完成被调用@Overridepublic 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();}

2.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>() {// 每解析一行数据,该方法会被调用一次@Overridepublic void invoke(DemoData demoData, AnalysisContext analysisContext) {System.out.println("解析数据为:" + demoData.toString());}// 全部解析完成被调用@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {System.out.println("解析完成...");// 可以将解析的数据保存到数据库}}).build();// 读取sheet,有几个就构建几个sheet进行读取excelReader.read(sheet0);// 需要自己关闭流操作,在读取文件时会创建临时文件,如果不关闭,会损耗磁盘,严重的磁盘爆掉excelReader.finish();
}

2.3.6 读的数据转换器

上面的写已经提到了转换器, 读也是一样. 将Excel文件中的字符串枚举值转换成要存入db的整数类型的枚举.

代码就不贴了, 和上面 2.2.14一样, 直接看效果.

2.4 填充Excel

2.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";// 填充完成之后的excelString 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);
}

效果

2.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";// 填充完成之后的excelString 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;}

效果

2.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";// 填充完成之后的excelString 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();// 多组填充excelexcelWriter.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();
```

2.4.4 水平填充

创建Excel填充模板

数据模板

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public class FillData {private String name;private double number;// lombok 会生成getter/setter方法
}

水平填充代码

@Testpublic void testFillExcel4() {// 根据哪个模板进行填充String template = "D:\\study\\excel\\template4.xlsx";// 填充完成之后的excelString 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();// 多组填充excelexcelWriter.fill(getFillData(), fillConfig, sheet);// 关闭流excelWriter.finish();}

效果

2.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";// 填充完成之后的excelString 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*****/// 多组填充excelexcelWriter.fill(dateMap, sheet);excelWriter.fill(memberMap, sheet);excelWriter.fill(curMonthMemberMap, sheet);excelWriter.fill(memberVips, fillConfig, sheet);// 关闭流excelWriter.finish();
}

效果

2.5 Web操作(Excel上传/下载)

2.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);// 写入数据到excelEasyExcel.write(response.getOutputStream(), UserExcel.class).sheet("用户信息").doWrite(userExcels);}
}

编写jsp页面 excelPage.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head><title>测试excel文件下载</title>
</head>
<body>
<h3>点击下面链接, 进行excel文件下载</h3>
<a href="<c:url value='/xlsx/downloadExcel'/>">Excel文件下载</a>
</body>
</html>

启动tomcat测试

访问 http://localhost:8080/mvc/xlsx/toExcelPage 跳转到excel文件下载界面

点击"Excel文件下载", 查看下载文件.

2.5.2 Excel文件上传

数据模板跟上面下载一样

编写上传handler

@RequestMapping("/uploadExcel")
public void uploadExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {DiskFileItemFactory factory = new DiskFileItemFactory();ServletFileUpload fileUpload = new ServletFileUpload(factory);// 设置单个文件大小为3M 2的10次幂=1024fileUpload.setFileSizeMax((long) (3 * Math.pow(2, 20)));// 总文件大小为30MfileUpload.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>() {// 每解析一行数据,该方法会被调用一次@Overridepublic void invoke(UserExcel data, AnalysisContext analysisContext) {System.out.println("解析数据为:" + data.toString());}// 全部解析完成被调用@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {System.out.println("解析完成...");// 可以将解析的数据保存到数据库}}).sheet().doRead();}}
}

上面方式不知道啥原因, 通过FileItem获取不到文件, 改为下面方式Part获取上传的文件

@RequestMapping("/uploadExcel")
@ResponseBody
public String uploadExcel(@RequestParam("file") Part part) throws Exception {// 获取上传的文件流InputStream inputStream = part.getInputStream();// 读取ExcelEasyExcel.read(inputStream, UserExcel.class, new AnalysisEventListener<UserExcel>() {// 每解析一行数据,该方法会被调用一次@Overridepublic void invoke(UserExcel data, AnalysisContext analysisContext) {System.out.println("解析数据为:" + data.toString());}// 全部解析完成被调用@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {System.out.println("解析完成...");// 可以将解析的数据保存到数据库}}).sheet().doRead();return "上传Excel文件成功";
}

编写jsp页面

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head><title>测试excel文件下载</title>
</head>
<body>
<h3>点击下面链接, 进行excel文件下载</h3>
<a href="<c:url value='/xlsx/downloadExcel'/>">Excel文件下载</a>
<hr/>
<hr/>
<h3>点击下面按钮, 进行excel文件上传</h3>
<form action="<c:url value='/xlsx/uploadExcel'/>" method="post" enctype="multipart/form-data"><input type="file" name="file"/><br/><input type="submit" value="上传Excel"/>
</form>
</body></html>

启动tomcat, 测试

访问 http://localhost:8080/mvc/xlsx/toExcelPage ,跳转到Excel文件上传页面

读取前端页面上传的Excel是成功了 , 但是中文乱码问题有待解决.

中文乱码解决参考: https://blog.csdn.net/gaogzhen/article/details/107307459

3 EasyExcel使用优化

3.1 监听器优化

上面章节的读取Excel的程序弊端:

每次解析不同数据模型都要新增一个监听器, 重复工作量大;
即使用了匿名内部类,程序也显得臃肿;
数据处理一般都会存在于项目的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*/@Overridepublic void invoke(T data, AnalysisContext context) {dataList.add(data);// 达到阈值就处理一次存储的数据if (dataList.size() >= threshold) {consumer.accept(dataList);dataList.clear();}}/*** excel文件解析完成后,事件调度中心会通知到该方法, 订阅者2* @param context*/@Overridepublic 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";// 读取excelEasyExcel.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;
}