课程分类管理接口
# 一、依赖
guli-microservice-edu中添加依赖
# 1、parent锁定依赖版本
<poi.version>3.9</poi.version>
<commons-fileupload.version>1.3.1</commons-fileupload.version>
<!--xls-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi.version}</version>
</dependency>
<!--xlsx-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.version}</version>
</dependency>
<!--文件上传-->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>${commons-fileupload.version}</version>
</dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 2、common配置依赖
子项目可选
<!--xls-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<optional>true</optional>
</dependency>
<!--xlsx-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<optional>true</optional>
</dependency>
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
# 3、edu配置依赖
<!--xls-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
</dependency>
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
4、excel工具类
将ExcelImportUtil 放入common项目的utils包中
# 二、业务处理
# 1、SubjectAdminController
package com.guli.edu.controller.admin;
@Api(description="课程分类管理")
@CrossOrigin //跨域
@RestController
@RequestMapping("/admin/edu/subject")
public class SubjectAdminController {
@Autowired
private SubjectService subjectService;
@ApiOperation(value = "Excel批量导入")
@PostMapping("import")
public R addUser(
@ApiParam(name = "file", value = "Excel文件", required = true)
@RequestParam("file") MultipartFile file) throws Exception {
List<String> msg = subjectService.batchImport(file);
if(msg.size() == 0){
return R.ok().message("批量导入成功");
}else{
return R.error().message("部分数据导入失败").data("messageList", msg);
}
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
2、SubjectService
接口
List<String> batchImport(MultipartFile file);
1
实现V1：获取Excel记录并逐条导入
package com.guli.edu.service.impl;
@Service
public class SubjectServiceImpl extends ServiceImpl<SubjectMapper, Subject> implements SubjectService {
@Override
public List<String> batchImport(MultipartFile file) {
List<String> msg = new ArrayList<>();
try {
ExcelImportUtil excelHSSFUtil = new ExcelImportUtil(file.getInputStream());
Sheet sheet = excelHSSFUtil.getSheet();
int rowCount = sheet.getPhysicalNumberOfRows();
if (rowCount <= 1) {
msg.add("请填写数据");
return msg;
}
for (int rowNum = 1; rowNum < rowCount; rowNum++) {
Row rowData = sheet.getRow(rowNum);
if (rowData != null) {// 行不为空
//一级分类名称
String levelOneValue = "";
Cell levelOneCell = rowData.getCell(0);
if(levelOneCell != null){
levelOneValue = excelHSSFUtil.getCellValue(levelOneCell);
if (StringUtils.isEmpty(levelOneValue)) {
msg.add("第" + rowNum + "行一级分类为空");
continue;
}
}
//二级分类名称
String levelTwoValue = "";
Cell levelTwoCell = rowData.getCell(1);
if(levelTwoCell != null){
levelTwoValue = excelHSSFUtil.getCellValue(levelTwoCell);
if (StringUtils.isEmpty(levelTwoValue)) {
msg.add("第" + rowNum + "行二级分类为空");
continue;
}
}
Subject subject = new Subject();
subject.setTitle(levelOneValue);
subject.setSort(0);
baseMapper.insert(subject);//添加
}
}
}catch (Exception e){
//EXCEL_DATA_ERROR(false, 21005, "Excel数据导入错误");
throw new GuliException(ResultCodeEnum.EXCEL_DATA_IMPORT_ERROR);
}
return msg;
}
}
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
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
实现Final
service层辅助方法
/**
* 根据分类名称查询这个一级分类中否存在
* @param title
* @return
*/
private Subject getByTitle(String title) {
QueryWrapper<Subject> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("title", title);
queryWrapper.eq("parent_id", "0");
return baseMapper.selectOne(queryWrapper);
}
/**
* 根据分类名称和父id查询这个二级分类中否存在
* @param title
* @return
*/
private Subject getSubByTitle(String title, String parentId) {
QueryWrapper<Subject> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("title", title);
queryWrapper.eq("parent_id", parentId);
return baseMapper.selectOne(queryWrapper);
}
package com.guli.edu.service.impl;
@Service
public class SubjectServiceImpl extends ServiceImpl<SubjectMapper, Subject> implements SubjectService {
@Transactional
@Override
public List<String> batchImport(MultipartFile file) {
List<String> msg = new ArrayList<>();
try {
ExcelImportUtil excelHSSFUtil = new ExcelImportUtil(file.getInputStream());
Sheet sheet = excelHSSFUtil.getSheet();
int rowCount = sheet.getPhysicalNumberOfRows();
if (rowCount <= 1) {
msg.add("请填写数据");
return msg;
}
for (int rowNum = 1; rowNum < rowCount; rowNum++) {
Row rowData = sheet.getRow(rowNum);
if (rowData != null) {// 行不为空
//一级分类名称
String levelOneValue = "";
Cell levelOneCell = rowData.getCell(0);
if(levelOneCell != null){
levelOneValue = excelHSSFUtil.getCellValue(levelOneCell);
if (StringUtils.isEmpty(levelOneValue)) {
msg.add("第" + rowNum + "行一级分类为空");
continue;
}
}
Subject subject = this.getByTitle(levelOneValue);
Subject subjectLevelOne = null;
String parentId = null;
if(subject == null){//创建一级分类
subjectLevelOne = new Subject();
subjectLevelOne.setTitle(levelOneValue);
subjectLevelOne.setSort(0);
baseMapper.insert(subjectLevelOne);//添加
parentId = subjectLevelOne.getId();
}else{
parentId = subject.getId();
}
//二级分类名称
String levelTwoValue = "";
Cell levelTwoCell = rowData.getCell(1);
if(levelTwoCell != null){
levelTwoValue = excelHSSFUtil.getCellValue(levelTwoCell);
if (StringUtils.isEmpty(levelTwoValue)) {
msg.add("第" + rowNum + "行二级分类为空");
continue;
}
}
Subject subjectSub = this.getSubByTitle(levelTwoValue, parentId);
Subject subjectLevelTwo = null;
if(subjectSub == null){//创建二级分类
subjectLevelTwo = new Subject();
subjectLevelTwo.setTitle(levelTwoValue);
subjectLevelTwo.setParentId(parentId);
subjectLevelTwo.setSort(0);
baseMapper.insert(subjectLevelTwo);//添加
}
}
}
}catch (Exception e){
//EXCEL_DATA_ERROR(false, 21005, "Excel数据导入错误");
throw new GuliException(ResultCodeEnum.EXCEL_DATA_IMPORT_ERROR);
}
return msg;
}
}
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
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
编辑 (opens new window)
上次更新: 2024/06/15, 15:12:25