700字范文 > EasyExcel实现Excel文件导入导出功能

EasyExcel实现Excel文件导入导出功能

时间：2021-08-06 21:48:07

一、EasyExcel简介

Java领域解析、生成Excel比较有名的框架有Apache poi、jxl等。但他们都存在一个严重的问题就是非常的耗内存。如果你的系统并发量不大的话可能还行，但是一旦并发上来后一定会OOM或者JVM频繁的full gc。

EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单、节省内存著称。EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析。

EasyExcel采用一行一行的解析模式，并将一行的解析结果以观察者的模式通知处理（AnalysisEventListener）

二、EasyExcel的具体使用

1、EasyExcel相关依赖

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.1.1</version></dependency>

2、写Excel

2.1、最简单的写（方式一）

创建实体类

@NoArgsConstructor@AllArgsConstructor@Data@Builderpublic class User {@ExcelProperty(value = "用户编号")private Integer userId;@ExcelProperty(value = "姓名")private String userName;@ExcelProperty(value = "性别")private String gender;@ExcelProperty(value = "工资")private Double salary;@ExcelProperty(value = "入职时间")@DateTimeFormat(value = "yyyy年MM月dd日 HH时mm分ss秒")private Date hireDate;}

写入

@PostMapping("/WriteExcel1")@ApiOperation(value="写excel")public void WriteExcel() {String filename = "D:\\excel\\write\\user.xlsx";// 向Excel中写入数据也可以通过 head(Class<?>) 指定数据模板EasyExcel.write(filename, User.class).sheet("用户信息").doWrite(getUserData());}

当然我们需要创建数据

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;}

效果：

2.2、最简单的写（方式二）

@PostMapping("/WriteExcel2")@ApiOperation(value="写excel")public void WriteExcel2() {String filename = "D:\\excel\\write\\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.3、排除模型中的属性字段

@PostMapping("/WriteExcel3")@ApiOperation(value="写excel")public void WriteExcel3() {String filename = "D:\\excel\\write\\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.4、向表格中导出指定属性

@PostMapping("/WriteExcel4")@ApiOperation(value="写excel")public void WriteExcel4() {String filename = "D:\\excel\\write\\user4.xlsx";// 设置要导出的字段Set<String> includeFields = new HashSet<>();includeFields.add("userName");includeFields.add("hireDate");// 写ExcelEasyExcel.write(filename, User.class).includeColumnFiledNames(includeFields).sheet("用户信息").doWrite(getUserData());}

效果：

2.5、插入指定的列

将Java对象中指定的属性, 插入到Eexcel表格中的指定列(在Excel表格中进行列排序), 使用index属性指定列顺序

@NoArgsConstructor@AllArgsConstructor@Data@Builderpublic class User {@ExcelProperty(value = "用户编号",index = 0)private Integer userId;@ExcelProperty(value = "姓名",index = 1)private String userName;@ExcelProperty(value = "性别",index = 2)private String gender;@ExcelProperty(value = "工资",index = 4)private Double salary;@ExcelProperty(value = "入职时间",index = 3)@DateTimeFormat(value = "yyyy年MM月dd日 HH时mm分ss秒")private Date hireDate;}

@PostMapping("/WriteExcel5")@ApiOperation(value="写excel")public void WriteExcel5() {String filename = "D:\\excel\\write\\user5.xlsx";// 向Excel中写入数据EasyExcel.write(filename, User.class).sheet("用户信息").doWrite(getUserData());}

效果：

2.6、复杂头数据写入

@ExcelProperty注解的value属性是一个数组类型, 设置多个head时会自动合并

@NoArgsConstructor@AllArgsConstructor@Data@Builderpublic 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;}

@PostMapping("/WriteExcel6")@ApiOperation(value="写excel")public void WriteExcel6() {String filename = "D:\\excel\\write\\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.7、重复写到Excel的同一个Sheet中

@PostMapping("/WriteExcel7")@ApiOperation(value="写excel")public void WriteExcel7() {String filename = "D:\\excel\\write\\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.8 写到Excel的不同Sheet中

@PostMapping("/WriteExcel8")@ApiOperation(value="写excel")public void WriteExcel8() {String filename = "D:\\excel\\write\\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.9、日期/数字类型格式化

在实体类加上这两个注解即可

@NumberFormat(value = "###.#") // 数字格式化,保留1位小数@DateTimeFormat(value = "yyyy年MM月dd日 HH时mm分ss秒") // 日期格式化

2.10、写入图片到Excel

@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方法}

@PostMapping("/WriteExcel9")@ApiOperation(value="写excel")public void WriteImageToExcel() throws IOException {String filename = "D:\\excel\\write\\user9.xlsx";// 图片位置String imagePath = "D:\\excel\\me.jpg";// 网络图片URL url = new URL("/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.11 设置写入Excel的列宽和行高

@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;}

@PostMapping("/WriteExcel10")@ApiOperation(value="写excel")public void Write10() {String filename = "D:\\excel\\write\\user10.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.12、通过注解形式设置写入Excel样式

@NoArgsConstructor@AllArgsConstructor@Data@Builder@HeadRowHeight(value = 30) // 头部行高@ContentRowHeight(value = 25) // 内容行高@ColumnWidth(value = 20) // 列宽// 头背景设置成红色 IndexedColors.RED.getIndex()@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 10)// 头字体设置成20, 字体默认宋体@HeadFontStyle(fontName = "宋体", fontHeightInPoints = 20)// 内容的背景设置成绿色 IndexedColors.GREEN.getIndex()@ContentStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 17)// 内容字体设置成20, 字体默认宋体@ContentFontStyle(fontName = "宋体", fontHeightInPoints = 20)public class DemoStyleData {// 字符串的头背景设置成粉红 IndexedColors.PINK.getIndex()@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 14)// 字符串的头字体设置成20@HeadFontStyle(fontHeightInPoints = 30)// 字符串的内容背景设置成天蓝 IndexedColors.SKY_BLUE.getIndex()@ContentStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)// 字符串的内容字体设置成20,默认宋体@ContentFontStyle(fontName = "宋体", fontHeightInPoints = 20)@ExcelProperty(value = "字符串标题")private String string;@ExcelProperty(value = "日期标题")private Date date;@ExcelProperty(value = "数字标题")private Double doubleData;}

@PostMapping("/WriteExcel11")@ApiOperation(value="写excel")public void Write11() {String filename = "D:\\excel\\write\\user11.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.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;}

@PostMapping("/WriteExcel12")@ApiOperation(value="写excel")public void Write12() {String filename = "D:\\excel\\write\\user12.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

3、读Excel

3.1、读API的拆分

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

@PostMapping("/ReadExcel1")@ApiOperation(value="读excel")public void Read() {String filename = "D:\\excel\\read\\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();

3.2、最简单的读(方式一)

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

@PostMapping("/ReadExcel2")@ApiOperation(value="读excel")public void testReadExcel() {// 读取的excel文件路径String filename = "D:\\excel\\read\\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();}

效果：

3.3、最简单的读(方式二)

@PostMapping("/ReadExcel3")@ApiOperation(value="读excel")public void testReadExcel2() {// 读取的excel文件路径String filename = "D:\\excel\\read\\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();}

效果:

3.4、格式化Excel中的数据格式

要读取的源数据, 日期格式是yyyy年MM月dd日 HH时mm分ss秒, 数字带小数点

// 格式化日期类型数据@DateTimeFormat(value = "yyyy年MM月dd日 HH时mm分ss秒")// 格式化数字类型数据,保留一位小数@NumberFormat(value = "###.#")

3.5、读取多个sheet表格

3.5.1 读所有sheet

方式一, 使用ExcelReaderBuilder#doReadAll方法

public void readExcel() {// 读取的excel文件路径String filename = "D:\\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("解析完成...");// 可以将解析的数据保存到数据库}}).doReadAll(); // 读取全部sheet}

方式二, 使用ExcelReader#readAll方法

public void readExcel2() {// 读取的excel文件路径String filename = "D:\\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();}

3.5.2 读指定的多个sheet

不同sheet表格的数据模板可能不一样,这时候就需要分别构建不同的sheet对象,分别为其指定对于的数据模板

public void readExcel3() {// 读取的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();}

4、填充Excel

4.1、简单填充

@NoArgsConstructor@AllArgsConstructor@Data@Builderpublic class FillData {private String name;private double number;}

@PostMapping("/FillExcel1")@ApiOperation(value="excel简单填充")public void testFillExcel() {// 根据哪个模板进行填充String template = "D:\\excel\\fill\\template1.xlsx";// 填充完成之后的excelString fillname = "D:\\excel\\fill\\fill1.xlsx";// 构建数据FillData fillData = FillData.builder().name("张三").number(666.888).build();// 填充excel 单组数据填充EasyExcel.write(fillname).withTemplate(template).sheet(0).doFill(fillData);}

模板：

效果：

4.2、列表填充

@PostMapping("/FillExcel2")@ApiOperation(value="excel列表填充")public void testFillExcel2() {// 根据哪个模板进行填充String template = "D:\\excel\\fill\\template2.xlsx";// 填充完成之后的excelString fillname = "D:\\excel\\fill\\fill2.xlsx";// 填充excel 多组数据重复填充EasyExcel.write(fillname).withTemplate(template).sheet(0).doFill(getFillData());}

模板：

效果：

4.3 水平填充

@PostMapping("/FillExcel3")@ApiOperation(value="excel水平填充")public void testFillExcel4() {// 根据哪个模板进行填充String template = "D:\\excel\\fill\\template3.xlsx";// 填充完成之后的excelString fillname = "D:\\excel\\fill\\fill3.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();}

模板：

效果：