描述
使用Java代码封装的一个Excel批量导入框架，使用EasyExcel完成excel文件方面的交互，重在整合功能，仅需少量代码就可以在自己的项目中快速引入该功能。可以流式分批读取Excel中的数据，避免OOM，使用多线程提高处理速度，异步线程处理便于前端查看导入进度与处理结果。
框架代码采取了设计模式中的策略模式，只需要实现其中的策略类（针对自己的业务来处理每行数据），就可以快速使用导入功能，并且给出了前端的配套代码示例（vue）。
demo项目地址：https://github.com/destinyol/excel-multi-import-progress
（demo项目不包括数据库交互部分，所以无法运行，只是演示代码如何使用）
框架项目文件结构：
ps：修改ExcelImportMainTool 中的 BATCH_COUNT 可自定义每次从excel中读取多少行数据，默认1000行
效果展示
可调参数
ExcelImportMainTool类中有一些可以根据实际情况自行调整的参数
1
2
3
4
5
| public static final int BATCH_COUNT = 1000;
public int BATCH_INSERT_COUNT = 200;
public static final boolean PROCESS_SAVE_RESULT_LIST = true;
|
使用方法
1、继承EasyExcelReadDataAbstract抽象类并实现其中EasyExcelReadData接口的方法，该子类是对应EasyExcel导入的实体类，用来存储单行数据，需要实现其中的方法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| public interface EasyExcelReadData {
public void trimAllFields();
public boolean dataIsAllEmpty();
}
|
例如：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| @Data
public class CustomerImportDto extends EasyExcelReadDataAbstract {
@ExcelProperty(value = "客户名称")
private String name;
@ExcelProperty(value = "手机号")
private String phone;
@Override
public boolean dataIsAllEmpty(){
return (name == null || name.isEmpty()) &&
(phone == null || phone.isEmpty());
}
@Override
public void trimAllFields() {
if (name != null) name = name.trim();
if (phone != null) phone = phone.trim();
}
}
|
2、实现DataDealHandler接口及对应方法，该实现类是用于自定义的数据处理与保存，便于衔接不同的业务场景
（要用批量插入就实现handleMultiDataAndSave方法，要用逐条插入就实现handleOneDataAndSave）
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
| public interface DataDealHandler {
public Integer getHeadRows();
@Transactional(rollbackFor = Exception.class)
default public ExcelResDto handleOneDataAndSave(EasyExcelReadData importData){
throw new RuntimeException("未实现逐条插入数据处理函数handleOneDataAndSave");
}
@Transactional(rollbackFor = Exception.class)
default public List<ExcelResDto> handleMultiDataAndSave(List<EasyExcelReadData> importDataList){
throw new RuntimeException("未实现批量插入数据处理函数handleMultiDataAndSave");
}
}
|
3、直接调用
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
DataDealHandlerImpl handler = new DataDealHandlerImpl(xxxxx,xxxxx自定义构造);
String processKey = ExcelImportMainTool.buildImport(
EasyExcelReadDataAbstract的子类.class,
handler,
redisTemplate
).runAsyncSingle(multipartFile);
String processKey = ExcelImportMainTool.buildImport(
EasyExcelReadDataAbstract的子类.class,
handler,
redisTemplate
).setBatchInsertCount(250)
.runAsyncMulti(multipartFile);
|
1
2
3
|
ImportProgress progress = ExcelImportMainTool.getProgress(redisTemplate, processKey);
|
至此整个功能就跑起来了
使用示例
数据实体类实现类
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
|
@Data
public class CustomerImportDto extends EasyExcelReadDataAbstract {
@ExcelProperty(value = "销售")
private String saleUserName;
@ExcelProperty(value = "客户名称")
private String name;
@ExcelProperty(value = "联系人")
private String contacts;
@ExcelProperty(value = "联系电话")
private String phone;
@ExcelProperty(value = "统一社会信用代码")
private String sucCode;
@ExcelProperty(value = "法人")
private String legalPerson;
@ExcelProperty(value = "注册资本(单位万元)")
private String registerMoney;
@ExcelProperty(value = "客户地址")
private String address;
@Override
public boolean dataIsAllEmpty(){
return (saleUserName == null || saleUserName.isEmpty()) &&
(name == null || name.isEmpty()) &&
(contacts == null || contacts.isEmpty()) &&
(phone == null || phone.isEmpty()) &&
(sucCode == null || sucCode.isEmpty()) &&
(legalPerson == null || legalPerson.isEmpty()) &&
(registerMoney == null || registerMoney.isEmpty()) &&
(address == null || address.isEmpty());
}
@Override
public void trimAllFields() {
if (saleUserName != null){
saleUserName = saleUserName.replaceAll("\\s+", "");
}
if (name != null) name = name.trim();
if (contacts != null) contacts = contacts.trim();
if (phone != null) phone = phone.trim();
if (sucCode != null) sucCode = sucCode.trim();
if (legalPerson != null) legalPerson = legalPerson.trim();
if (registerMoney != null) registerMoney = registerMoney.trim();
if (address != null) address = address.trim();
}
}
|
策略类实现类
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
|
public class CustomerImportDataHandler implements DataDealHandler {
private UserMapper userMapper;
private TdCustomerMapper tdCustomerMapper;
public CustomerImportDataHandler(UserMapper userMapper, TdCustomerMapper tdCustomerMapper) {
this.userMapper = userMapper;
this.tdCustomerMapper = tdCustomerMapper;
}
@Override
public Integer getHeadRows() {
return 2;
}
@Override
@Transactional(rollbackFor = Exception.class)
public ExcelResDto handleOneDataAndSave(EasyExcelReadData importData) {
CustomerImportDto customerImportDto = (CustomerImportDto) importData;
ExcelResDto resDto = null;
TdUser user = userMapper.queryOneByUserName(customerImportDto.getSaleUserName());
if (user == null){
resDto = ExcelResDto.build(customerImportDto,"该姓名用户不存在：（"+customerImportDto.getSaleUserName()+"） ，对应客户名："+customerImportDto.getName());
return resDto;
}
TdCustomer step = new TdCustomer();
step.setName(customerImportDto.getName());
step.setContacts(customerImportDto.getContacts());
step.setAddress(customerImportDto.getAddress());
step.setPhone(customerImportDto.getPhone());
step.setCreateBy(user.getUserId());
step.setLegalPerson(customerImportDto.getLegalPerson());
step.setSucCode(customerImportDto.getSucCode());
step.setRegisterMoney(customerImportDto.getRegisterMoney());
if (isBlank(step.getName())){
resDto = ExcelResDto.build(customerImportDto,"客户名不能为空");
return resDto;
}
TdCustomer customerByName = tdCustomerMapper.getCustomerByName(customerImportDto.getName());
if (customerByName != null) {
resDto = ExcelResDto.build(customerImportDto,"客户名重复："+customerImportDto.getName());
return resDto;
}
if (isBlank(step.getContacts())){
resDto = ExcelResDto.build(customerImportDto,"客户联系人不能为空，客户名："+customerImportDto.getName());
return resDto;
}
if (isBlank(step.getPhone())){
resDto = ExcelResDto.build(customerImportDto,"客户联系方式不能为空，客户名："+customerImportDto.getName());
return resDto;
}
boolean save = tdCustomerMapper.insert(step) != 0;
if (!save){
resDto = ExcelResDto.build(customerImportDto,"客户添加失败，客户名："+customerImportDto.getName());
}
return resDto;
}
@Override
@Transactional(rollbackFor = Exception.class)
public List<ExcelResDto> handleMultiDataAndSave(List<EasyExcelReadData> importDataList) {
List<ExcelResDto> res = new ArrayList<>();
List<CustomerImportDto> customerImportDtoList = new ArrayList<>();
for (EasyExcelReadData easyExcelReadData : importDataList) {
customerImportDtoList.add((CustomerImportDto)easyExcelReadData);
}
List<TdCustomer> tdCustomerList = new ArrayList<>();
for (CustomerImportDto customerImportDto : customerImportDtoList) {
TdCustomer step = new TdCustomer();
step.setName(customerImportDto.getName());
step.setContacts(customerImportDto.getContacts());
step.setAddress(customerImportDto.getAddress());
step.setPhone(customerImportDto.getPhone());
step.setLegalPerson(customerImportDto.getLegalPerson());
step.setSucCode(customerImportDto.getSucCode());
step.setRegisterMoney(customerImportDto.getRegisterMoney());
}
Boolean insetCheck = tdCustomerMapper.saveBatch(tdCustomerList);
if (!insetCheck){
for (CustomerImportDto customerImportDto : customerImportDtoList) {
res.add(ExcelResDto.build(customerImportDto,customerImportDto.getName()+"添加失败"));
}
}
return res;
}
}
|
接口示例
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
|
@PostMapping("/importExcel")
public Response importExcel(MultipartFile file){
Response response = new Response();
response.ret(0,"成功了");
try {
CustomerImportDataHandler handler = new CustomerImportDataHandler(userMapper,tdCustomerMapper);
String processKey = ExcelImportMainTool.buildImport(CustomerImportDto.class, handler, redisTemplate).runAsyncSingle(file);
response.setData(processKey);
} catch (Exception e) {
e.printStackTrace();
response.ret(111000,"出错了");
}
return response;
}
@GetMapping("/getProgress")
public Response getProgress(String key){
Response response = new Response();
response.ret(0,"成功了");
try {
ImportProgress progress = ExcelImportMainTool.getProgress(redisTemplate, key);
response.setData(progress);
} catch (Exception e) {
e.printStackTrace();
response.ret(111001,"出错了");
}
return response;
}
|
