1
2
3
4
5
6
7
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.4</version>
</dependency>

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
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
package com.jay.easyexcel.simple;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
/**
* @version 0.0.1
* @program: spring-poi-demo
* @description:
*
* 参考：
* https://blog.csdn.net/lzp492782442/article/details/106860383
*
* 通过本篇文章，我们演示了如何使用easyexcel进行一些excel的操作，在实际的项目应用中，
* 可以对以上示例代码进行进一步的封装，使其不管是读取、导出等操作都能几行代码搞定，这个就得根据情况大家自由发挥了。
* @author: huangzq
* @create: 2021-01-12 19:25
*/
@RestController
@RequestMapping("/user")
public class UserController {
/**
* 网页地址栏访问接口：http://localhost:8066/springPoiDemo/user/downloadTemplate
* 下载模板
加载资源->读取资源->写入响应流
*/
@GetMapping("/downloadTemplate")
public void downloadTemplate(HttpServletResponse response) throws Exception {
ClassPathResource classPathResource = new ClassPathResource("excelTemplate/easyexcel.xls");
InputStream inputStream = classPathResource.getInputStream();
Workbook workbook = new HSSFWorkbook(inputStream);
response.setContentType("application/vnd.ms-excel");
response.setHeader("content-Disposition", "attachment;filename=" + URLEncoder.encode("easyexcel.xlsx", "utf-8"));
response.setHeader("Access-Control-Expose-Headers", "content-Disposition");
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
}
/**
* 访问接口：http://localhost:8066/springPoiDemo/user/exportData
* 导出数据
定义列标题->创建sheet->自定义字体和风格->构造数据->写入数据->写入到浏览器响应流
*/
@GetMapping("/exportData")
public void exportData(HttpServletResponse response) throws Exception {
XSSFWorkbook workbook = new XSSFWorkbook();
String []columnNames = {"用户名","年龄","手机号","性别"};
Sheet sheet = workbook.createSheet();
Font titleFont = workbook.createFont();
titleFont.setFontName("simsun");
titleFont.setBold(true);
titleFont.setColor(IndexedColors.BLACK.index);
XSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
titleStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
titleStyle.setFont(titleFont);
Row titleRow = sheet.createRow(0);
for (int i = 0; i < columnNames.length; i++) {
Cell cell = titleRow.createCell(i);
cell.setCellValue(columnNames[i]);
cell.setCellStyle(titleStyle);
}
//模拟构造数据
List<UserExcelModel> dataList = new ArrayList<>();
dataList.add(new UserExcelModel("张三",12,"13867098765","男"));
dataList.add(new UserExcelModel("张三1",12,"13867098765","男"));
dataList.add(new UserExcelModel("张三2",12,"13867098765","男"));
dataList.add(new UserExcelModel("张三3",12,"13867098765","男"));
//创建数据行并写入值
for (int j = 0; j < dataList.size(); j++) {
UserExcelModel userExcelModel = dataList.get(j);
int lastRowNum = sheet.getLastRowNum();
Row dataRow = sheet.createRow(lastRowNum + 1);
dataRow.createCell(0).setCellValue(userExcelModel.getName());
dataRow.createCell(1).setCellValue(userExcelModel.getAge());
dataRow.createCell(2).setCellValue(userExcelModel.getMobile());
dataRow.createCell(3).setCellValue(userExcelModel.getSex());
}
response.setContentType("application/vnd.ms-excel");
response.setHeader("content-Disposition", "attachment;filename=" + URLEncoder.encode("easyexcel.xls", "utf-8"));
response.setHeader("Access-Control-Expose-Headers", "content-Disposition");
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
}
/**
* 读取数据
调用接口：http://localhost:8066/springPoiDemo/user/readExcel
* PostMan模拟调用
* 使用上面方法生成的excel文件来测试
只能读取单个的sheet表
*
* @param file
* @return
*/
@PostMapping("/readExcel")
public List<UserExcelModel> readExcel(@RequestParam("file") MultipartFile file){
List<UserExcelModel> list = new ArrayList<>();
try {
list = EasyExcel.read(file.getInputStream(),UserExcelModel.class,new ModelExcelListener()).sheet().doReadSync();
//
也可以下面方法读取
//
String fileName = "demo.xlsx";
//
list = EasyExcel.read(fileName,UserExcelModel.class,new ModelExcelListener()).sheet().doReadSync();
} catch (IOException e) {
e.printStackTrace();
}
return list;
}
/**
* 读取多个Sheet的示例代码,并不知道到底有几个sheet，看我写的那个复杂的excel读取sheet模块，哪里有
*
* 读多个或者全部sheet,这里注意一个sheet不能读取多次，多次读取需要重新读取文件
* <p>
* 1. 创建excel对应的实体对象 参照{@link UserExcelModel}
* <p>
* 2. 由于默认异步读取excel，所以需要创建excel一行一行的回调监听器，参照{@link ModelExcelListener}
* <p>
* 3. 直接读即可
*/
@PostMapping("/readExcelMore")
public void repeatedRead(@RequestParam("file") MultipartFile file) throws IOException {
//
String fileName = "demo.xlsx";
// 读取全部sheet
// 这里需要注意 DemoDataListener的doAfterAllAnalysed 会在每个sheet读取完毕后调用一次。然后所有sheet都会往同一个DemoDataListener里面写
//
EasyExcel.read(fileName, UserExcelModel.class, new ModelExcelListener()).doReadAll();
EasyExcel.read(file.getInputStream(), UserExcelModel.class, new ModelExcelListener()).doReadAll();
// 读取部分sheet
//
ExcelReader excelReader = EasyExcel.read(fileName).build();
ExcelReader excelReader = EasyExcel.read(file.getInputStream()).build();
// 这里为了简单 所以注册了 同样的head 和Listener 自己使用功能必须不同的Listener
//读取所有的sheet页码
List<ReadSheet> readSheetList = EasyExcel.read(file.getInputStream()).build().excelExecutor().sheetList();
readSheetList.forEach(sheet->{
ReadSheet readSheet1 = EasyExcel.readSheet(sheet.getSheetName()).head(UserExcelModel.class).registerReadListener(new ModelExcelListener()).build();
excelReader.read(readSheet1);
});
//这个是写死的，上面的是写活的
//
ReadSheet readSheet1 = EasyExcel.readSheet(0).head(UserExcelModel.class).registerReadListener(new ModelExcelListener()).build();
//
ReadSheet readSheet2 = EasyExcel.readSheet(1).head(UserExcelModel.class).registerReadListener(new ModelExcelListener()).build();
// 这里注意 一定要把sheet1 sheet2 一起传进去，不然有个问题就是03版的excel 会读取多次，浪费性能
//
excelReader.read(readSheet1, readSheet2);
// 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
excelReader.finish();
}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
package com.jay.easyexcel.simple;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* @version 0.0.1
* @program: spring-poi-demo
* @description:
* @author: huangzq
* @create: 2021-01-13 09:33
*/
@Service
@Slf4j
public class handleExcelService {
public void saveData(List<Object> datas) {
log.info("业务层处理的数据：{}",JSON.toJSONString(datas));
}
}

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
package com.jay.easyexcel.simple;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
/**
* @version 0.0.1
* @program: spring-poi-demo
* @description: 定义模型映射对象 UserExcelModel
* @author: huangzq
* @create: 2021-01-12 19:33
*/
@Data
@ToString
@AllArgsConstructor
@NoArgsConstructor
public class UserExcelModel{
@ExcelProperty(value = "用户名", index = 0)
private String name;
@ExcelProperty(value = "年龄", index = 1)
private Integer age;
@ExcelProperty(value = "手机号", index = 2)
private String mobile;
@ExcelProperty(value = "性别", index = 3)
private String sex;
}

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
package com.jay.easyexcel.simple;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.jay.config.SpringContextUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import java.util.ArrayList;
import java.util.List;
/**
* @version 0.0.1
* @program: spring-poi-demo
* @description: 读取监听类
想对比用poi工具处理的，这个是不是相当简洁了
*
* 这是一个读取数据监听类，有特殊业务需求的都可以在这个类里面自定义实现，比如边读边写库啊，数据过滤和处理等等，用的好了绝对是一把利剑。
*
* @author: huangzq
* @create: 2021-01-12 19:43
*/
@Slf4j
public class ModelExcelListener extends AnalysisEventListener<UserExcelModel> {
//注入不进来
下面的方法可以注入，也可以用构造器注入
//
@Autowired
//
private handleExcelService handleExcelService;
@Autowired
private handleExcelService handleExcelService = SpringContextUtils.getBean(handleExcelService.class);
/**
* 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 5;
private List<Object> datas = new ArrayList<>();
/**
* 通过 AnalysisContext 对象还可以获取当前 sheet，当前行等数据
*/
//
@Override
//
public void invoke(Object data, AnalysisContext context) {
//
//数据存储到list，供批量处理，或后续自己业务逻辑处理。
//
log.info("读取到数据{}",data);
//
datas.add(data);
//
//根据业务自行处理，可以写入数据库等等
//
}
@Override
public void invoke(UserExcelModel userExcelModel, AnalysisContext analysisContext) {
//数据存储到list，供批量处理，或后续自己业务逻辑处理。
log.info("读取到数据{}",userExcelModel);
datas.add(userExcelModel);
//根据业务自行处理，可以写入数据库等等
handleExcelService.saveData(datas);
// 存储完成清理 list，保证内存不溢出
datas.clear();
}
//所以的数据解析完了调用
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
log.info("所有数据解析完成，内存中的数据为：{}", JSON.toJSONString(datas));
}
}