EasyExcel实现Excel文件导入导出_crysw_easyexcel

irpas 02-07 4222

1 EasyExcel简介

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。

github地址: https://github.com/alibaba/easyexcel

官方文档: https://·/easyexcel/doc/easyexcel

B站视频: https://·/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 = "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 @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; }

效果

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); // 指定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();*/ } 2.3.2 最简单的读(方式一)

Excel数据类型

字符串标题日期标题数字标题小明2020-05-05 10:10:10888.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"; // 读取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().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>() { // 每解析一行数据,该方法会被调用一次 @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.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方法

读方式二, 使用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(); } 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>() { // 每解析一行数据,该方法会被调用一次 @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(); } 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"; // 填充完成之后的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); }

效果

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

效果

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"; // 填充完成之后的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();

2.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(); }

效果

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

效果

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

编写jsp页面 excelPage.jsp

启动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次幂=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(); } } }

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

@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文件成功"; }

编写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 */ @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; } 3.2 遇到的问题

文件有数据, EasyExcel读取的数据全为null的坑, 看图.

原因及解决方案: https://blog.csdn.net/qq_19309473/article/details/111322185