最新版easyExcel工具类
2019-06-17 00:12:34

最新版的easyexcel2.0升级后原有的导入导出书写方法就已经过时了,本文记录了最新版的EasyExcel工具类的整合与使用。
最新版书写比原来要简单不少,性能也有所提高,推荐换到最新版。

引入最新的依赖

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.4.0</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
<scope>provided</scope>
</dependency>

主要用到了easyexcelhutool工具类和lombok插件

导入导出的实体类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
package org.geekboy.bean;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.Data;

/**
* @ClassName ExportModel
* @Description 导出模型
* @Author zhangwei
* @Version 1.0.0
* @Date 2020/4/1 20:55
*/
@ContentRowHeight(20)
@HeadRowHeight(25)
@ColumnWidth(25)
@Data
public class ExportModel {

@ExcelProperty(value = "姓名" ,index = 0)
private String name;

@ExcelProperty(value = "性别" ,index = 1)
private String sex;

@ExcelProperty(value = "年龄" ,index = 2)
private Integer age;

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
package org.geekboy.bean;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

/**
* @ClassName ImportModel
* @Description 导入模型
* @Author zhangwei
* @Version 1.0.0
* @Date 2020/4/1 20:54
*/
@Data
public class ImportModel {

@ExcelProperty(index = 0)
private String date;

@ExcelProperty(index = 1)
private String author;

@ExcelProperty(index = 2)
private String book;

}

导入导出工具类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
package org.geekboy.common;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.util.ArrayList;
import java.util.List;

/**
* @Author zhang wei
* @Description 监听类
* @Date 2020-08-13
*/
public class ExcelListener extends AnalysisEventListener {
/**
* 可以通过实例获取该值
*/
private List<Object> dataList = new ArrayList<>();

@Override
public void invoke(Object object, AnalysisContext context) {
//数据存储到list,供批量处理,或后续自己业务逻辑处理。
dataList.add(object);
handleBusinessLogic();
/*
如数据过大,可以进行定量分批处理
if(dataList.size()>=200){
handleBusinessLogic();
dataList.clear();
}
*/
}

@Override
public void doAfterAllAnalysed(AnalysisContext context) {
//非必要语句,查看导入的数据
System.out.println("导入的数据条数为: " + dataList.size());
}

//根据业务自行实现该方法,例如将解析好的dataList存储到数据库中
private void handleBusinessLogic() {

}

public List<Object> getDataList() {
return dataList;
}

public void setDataList(List<Object> dataList) {
this.dataList = dataList;
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
package org.geekboy.common;

import cn.hutool.core.convert.Convert;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.metadata.WriteSheet;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
* @Author zhang wei
* @Description Excel读写工具类
* @Date 2020-08-13
*/
public class ExcelUtil {


/**
* 读取Excel(多个sheet可以用同一个实体类解析)
* @param excelInputStream
* @param fileName
* @param clazz
* @param <T>
* @return
*/
public static <T> List<T> readExcel(InputStream excelInputStream, String fileName,Class<T> clazz) {
ExcelListener excelListener = new ExcelListener();
ExcelReader excelReader = getReader(excelInputStream, fileName,clazz, excelListener);
if (excelReader == null) {
return new ArrayList<>();
}
List<ReadSheet> readSheetList = excelReader.excelExecutor().sheetList();
for (ReadSheet readSheet : readSheetList) {
excelReader.read(readSheet);
}
excelReader.finish();
return Convert.toList(clazz, excelListener.getDataList());
}

/**
* 导出Excel(一个sheet)
*
* @param response HttpServletResponse
* @param list 数据list
* @param fileName 导出的文件名
* @param sheetName 导入文件的sheet名
* @param clazz 实体类
*/
public static <T> void writeExcel(HttpServletResponse response, List<T> list, String fileName, String sheetName, Class<T> clazz) {

OutputStream outputStream = getOutputStream(response, fileName);
ExcelWriter excelWriter = EasyExcel.write(outputStream, clazz).build();
WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
excelWriter.write(list, writeSheet);
excelWriter.finish();
}


/**
* 导出时生成OutputStream
*/
private static OutputStream getOutputStream(HttpServletResponse response, String fileName) {
//创建本地文件
String filePath = fileName + ".xlsx";
File file = new File(filePath);
try {
if (!file.exists() || file.isDirectory()) {
file.createNewFile();
}
fileName = new String(filePath.getBytes(), "ISO-8859-1");
response.addHeader("Content-Disposition", "filename=" + fileName);
return response.getOutputStream();
} catch (IOException e) {
e.printStackTrace();
}
return null;
}

/**
* 返回ExcelReader
*
* @param excel 文件
* @param clazz 实体类
* @param excelListener
*/
private static <T> ExcelReader getReader(InputStream inputStream, String filename, Class<T> clazz, ExcelListener excelListener) {
try {
if (filename == null ||
(!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
return null;
}
ExcelReader excelReader = EasyExcel.read(inputStream, clazz, excelListener).build();
inputStream.close();
return excelReader;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}

测试类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
package org.geekboy.test;

import org.geekboy.bean.ExportModel;
import org.geekboy.bean.ImportModel;
import org.geekboy.common.ExcelUtil;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

@Controller
public class ExcelTestController {

@PostMapping(value = "/import")
public List<ImportModel> read(MultipartFile excel) throws IOException {
return ExcelUtil.readExcel(excel.getInputStream(), excel.getOriginalFilename(), ImportModel.class);
}

@GetMapping(value = "/export")
public void writeExcel(HttpServletResponse response) {
List<ExportModel> list = getList();
String fileName = "Excel导出测试";
String sheetName = "sheet1";
ExcelUtil.writeExcel(response, list, fileName, sheetName, ExportModel.class);
}

private List<ExportModel> getList() {
List<ExportModel> modelList = new ArrayList<>();
ExportModel firstModel = new ExportModel();
firstModel.setName("李明");
firstModel.setSex("男");
firstModel.setAge(20);
modelList.add(firstModel);
ExportModel secondModel = new ExportModel();
secondModel.setName("珍妮");
secondModel.setSex("女");
secondModel.setAge(19);
modelList.add(secondModel);
return modelList;
}
}

镜像地址

https://www.cnblogs.com/coderzhw/p/13580023.html

pay

上一页
2019-06-17 00:12:34